性能优化之MySQL优化

1、MySQL数据库优化

1.1 数据库优化的目的

1、避免出现页面访问错误

• 由于数据库连接timeout产生页面5XX错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据数据无法提交

2、增加数据库稳定性

• 很多数据库问题都是由于低效的查询引起的

3、优化用户体验

• 流畅页面的访问速度
• 良好的网站功能体验

1.2、可以从几个方面进行数据库优化

数据库优化从4个方向去优化

• 1、sql和索引，写出健壮的sql，索引不是越多越好
• 2、数据表结构（存储引擎，字段大小，字段类型，索引，第三规范）
• 3、系统配置（打开文件系统次数，文件安全性）
• 4、硬件（更合适的cup,更大的内存，更快的io：ssd等，cup并不是越大越好）

2、SQL语句优化

2.1 如何发现有问题的SQL？

• 使用sakila数据模拟
  1、下载sakila数据库
  http://dev.mysql.com/doc/index-other.html.
  2、解压：解压sakila-db.zip到Desktop，里面包含三个文件
  sakila-schema.sql //数据库结构文件
  sakila-data.sql //数据文件
  3、在MYSQL 命令下： SOURCE 下载路径/sakila-schema.sql #建立表结构
  4、在MYSQL 命令下： SOURCE 下载路径/sakila-data.sql #插入数据
• 使用MySQL慢查日志对有效率问题的SQL进行监控

//查看慢查询日志是否开启
show variables like 'slow_query_log';

//开启慢查询日志
set global slow_query_log=on;

//查看慢查询日志存储位置
show variables like 'slow_query_log_file';

//指定慢查询日志存储位置
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log';

//记录没有使用索引的sql
set global log_queries_not_using_indexes=on;

//记录查询超过1s的sql
set global long_query_time=1;

//模糊查询
show VARIABLES like '%log%'

//查询查询时间
show VARIABLES like 'long_query_time'

//linux查询慢查询日志
tail -50  /home/mysql/sql_log/mysql-slow.log（后面地址是慢查询的地址位置） 

• 慢查询日志所包含的内容

#User@Host:root[root] @localhost[]//执行sql的主机信息
#Query_time:0.0000024   Lock_time:0.00 Rows_sent:0 Rows_esamined:0//sql的执行信息
SET timestamp=1402389324//sql执行时间
select * from store; //sql的内容

2.2 mysql慢查分析工具-mysqldumpslow

• mysqldumpslow使用指令（如下图）
  

//查看参数列表
mysqldumpslow -h

//分析慢查询日志中前三条比较慢的sql
mysqldumpslow -t 3  /var/lib/mysql/centos2-slow.log | more     
命令注解：  mysqldumpslow -t 3 +慢查询的日志路径+ |+ more 

//输出样式
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.0 (2), root[root]@localhost
  select * from staff
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=20.0 (20), root[root]@localhost
  select * from film limit N

2.3 mysql慢查分析工具pt-query-digest

//查看参数列表
pt-query-digest --help

//分析慢查询日志
 pt-query-digest   /var/lib/mysql/centos2-slow.log | more 
 
//输出分为三部分
1.显示除了日志的时间范围，以及总的sql数量和不同的sql数量
2.Response Time:响应时间占比    Calls:sql执行次数
3.sql的具体日志

2.4 如何通过慢查询日志发现有问题的sql

• 如何通过慢查询日志发现有问题的SQL？

1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询

2.IO大的SQL（数据库主要瓶颈出现在IO层次）
注意pt-query-digest分析中的Rows examine项

3.未命中索引的SQL
注意pt-query-digest分析中的Rows examine和Rows Send的对比

2.5 通过explain查询和分析SQL的执行计划

• 通过explain查询和分析SQL的执行计划
  举个栗子：

explain select customer_id,first_name,last_name from customer;

查询结果：

参数含义：
table—customer : 该数据关于哪张表
type —ALL: 示连接使用了何种类型。从好到差const,eq_reg,ref,range,index和ALL。
possible_keys—NULL: 可能应用在该表的索引，空，没有可能的索引。
key—NULL: 实际使用的索引。空，没有使用索引。
key_len—NULL: 使用的索引长度。不损失精度下，越短越好。
ref—NULL: 显示索引的哪一列被使用了，常数。
rows—671: mysql认为必须检查的数据的行数。
Extra ’ 注意：Using filesort,Using tempoary, Using filesort:看到这个的时候，查询就需要优化了，Mysql需要进行额外的步骤来发现如何对返回的行进行排序，它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using temporary 看到这个的时候，查询需要优化了，这里MYSQL需要创建一个临时表来存储结果，这通常发现在对不同的列集进行ORDER BY上，而不是GROUP BY

2.6 Count（）和Max（） 的优化

示例：
//查询最后支付时间--优化max()函数
explain select max(payment_date) from payment;
create index idx_paydate on payment(payment_data);//给payment_date建立索引(覆盖索引)

//在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数
select count(release_year='2006' or null) as '2006年电影数量'，count(release_year='2007' or null) as '2007年电影数量' from film;
//有关count()函数
https://blog.youkuaiyun.com/wendychiang1991/article/details/70909958/

2.7 子查询的优化

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。(distinct去重)
//查询sandra出演的所有影片
explain select title,release_year,LENGTH from film
where film_id in (
select film_id from film_actor  where actor_id in (select actor_id from actor where first_name='sandra'));

改为join的连接方式
explain select title,release_year,LENGTH
from film T1 
join  film_actor T2 on T1.film_id =T2.film_id 
join  actor T3 on  T3.actor_id =T2.actor_id 
where T3.first_name='sandra'

join查询方式为什么比子查询的方式速度更快？
因为join查询不需要内建临时表处理

2.8 group by的优化

using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件;例如: select a.*, b.* from a left join b using(colA);
group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io，过滤条件最好写在子查询内，不要查询完成后，在外层过滤

改写前
explain select actor.first_name,actor.last_name,count(*)
from  sakila.film_actor 
inner join  sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;

优化后：
explain select actor.first_name,actor.last_name,c.cnt
from  sakila.actor 
inner join  (select actor_id,count(*) as cnt from sakila.film_actor GROUP BY actor_id)
 as c USING(actor_id);

2.9 limit 的优化

limit常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。

//文件排序，IO大
explain select film_id,description from sakila.film order by title limit 50,5;
1.优化：使用有索引的列或主键进行order by操作（order by film_id）
2.记录上次返回的主键，在下次查询的时候用主键过滤，避免了数据量大时扫描过多的记录
select film_id,description from sakila.film where film_id>55 and film_id<=60 order by film_id limit 1,5; 
使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了

3 索引优化

3.1如何选择合适的列建立索引？

• 1.在where从句，group by从句，order by从句，on从句中出现的列(select)
• 2.索引字段越小越好(表每页数据才会更多，IO效率会更高)
• 3.离散度大的列放到联合索引的前面

创建联合索引：
alter table payment add index(customer_id,staff_id);

select * from payment where staff_id=2 and customer_id=584;
index(staff_id,customer_id)好？还是index(customer_id,staff_id)好？
由于customer_id的离散度更大(重复率小,可选择性更大)，所以应该使用index(customer_id,staff_id)

3.2 索引优化SQL的方法

重复索引：是指相同的列以相同的顺序建立的同类型的索引
create table test(
	id int not null primary key,
	name varchar(20) not null,
	unique(id)
)engine=innodb

冗余索引：是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。如下：key(name,id)就是一个冗余索引
create table test(
id int not null primary key,
name varchar(10) not null,
key(name,id)
)engine=innodb;

//可以删除冗余索引，达到优化效果，使用pt-duplicate-key-checker工具检查重复及冗余索引
pt-duplicate-key-checker -u 用户名 -p 密码 -h ip

3.3 索引维护的方法–删除不用索引

目前mysql中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可通过INDEX_STATISTICS表来查看哪些索引未使用，但在mysql中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况分析。
pt-index-usage \
    -uroot -p 'mysql密码 ' \
    mysql-slow.log

4 数据库优化

4.1 选择合适的数据类型

• 1.使用可以存下你的数据的最小的数据类型
• 2.使用简单的数据类型。int要比varchar类型在mysql处理上更简单
• 3.尽可能的使用not null定义字段
• 4.尽量少用text类型，非用不可时最好考虑分表

使用int来存储日志时间，利用FROM_UNIXTINE()(得到日期),UNIX_TIMESTAMP()(得到时间戳)两个函数来进行转换
使用bigint来存ip地址，利用INET_ATON()（转化为bigint）,INET_NTOA()两个函数来进行转换

4.2 表的范式化和反范式化

• 范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。

不符合第三范式要求的表存在下列问题：
1.数据冗余：（分类，分类描述）对于每一个商品都会进行记录
2.数据的插入异常
3.数据的更新异常
4.数据的删除异常

4.3 表的拆分

所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行：
1.把不常用的字段单独存放到一个表中
2.把大字段独立存放到一个表中
3.把经常一起使用的字段放到一起

水平拆分
表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。
常用的水平拆分方法为：
1.对id进行hash运算，如果要拆分成5个表则使用mod(id,5)去除0-4个值
2.针对不同的hashID把数据存到不同的表中

5 数据库系统优化

5.1操作系统配置优化

• 数据库是基于操作系统的，目前大多数mysql都是安装在Linux系统之上，所以对于操作系统的一些参数配置也会影响到MYSQL的性能

网络方面的配置，要修改/etc/stysctl.conf文件
#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
#减少断开链接是，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10

打开文件数的限制，可以使用ulimit -a 查看目录的各位限制，可以修改/etcsecurity/limitsconf文件，增加一下内容以修改打开文件数量的限制
*soft nofile 65535
*hard nofile 65535
除此之外最好在mysql服务器上关闭iptables，selinux等防火墙软件。

5.2 MySQL数据库优化

mysql可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大数情况下配置文件位于/etc/my.cnf或是/etc/mysql/my.cnf在windows系统配置文件可以是位于C:/windows/my.ini文件，mysql查找配置文件的顺序可以通过一下方法获得
$ /usr/sbin/mysqld --verbose --help | grep -A 1 ' Default options '

• MySQL配置文件–常用参数说明

select engine,
round(sum(data_length+index_length)/1024/1024,1) as "Total MB"
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN 
("inoformation_schema","performance_schema")
Group by engine;

1.innodb_buffer_pool_size >= total MB
非常重要的一个参数，用于配置innodb的缓冲池，如果数据库中只有innodb表，则推荐配置量为总内存的75%,如果同时运行Myisam类型的表，那么适当减少一点比例。

2.innodb_buffer_pool__instances
MySQL5.5中新增加参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。
缓冲池分成多分，可以增加并发性，一般分成4份，8份

3.innodb_log_buffer_size
innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大。

4.innodb_flush_log_at_trx_commit
关键参数，对innodb的IO效率影响很大。默认值为1，可取0，1，2三个值，一般建议为2，但如果数据安全性要求比较高则使用默认值1.
数据变更怎么样从内存刷新到磁盘上:
0 每一秒刷一次
1 每一次提交刷一次
2 在内存缓区满了在刷新到磁盘

5.innodb_read_io_threads   innodb_write_io_threads
以上两个参数决定了Innodb读写的IO进程数，默认为4.

6.innodb_file_per_table
关键参数，控制innodb每一个表使用独立的表空间，默认为off，也就是所有表都会建立在共享表空间中。共享表空间，io成为瓶颈，顺序写，并发写入时，效率低。建议设为on

7.innodb_stats_on_metadata
决定了mysql在什么情况下会刷新innodb表的统计信息。设置为off需要认为刷新,自动刷新影响性能

第三方配置工具
?视频使用方法：https://www.imooc.com/video/4165
?链接地址：https://tools.percona.com/wizard

6 服务器硬件优化

• 如何选择cpu？

1.mysql有一些工作只能使用到单核cpu，Replicate,SQL...
2.mysql对cpu核数的支持并不是越多越快。mysql5.5使用的服务器不要超过30核

• 磁盘IO优化

常用RAID级别简介
RAID0：也称条带，就是把多个磁盘链接成一个硬盘使用，这个级别IO最好
RAID1：也称镜像，要求至少有两个磁盘，每组磁盘存储的数据相同
RAID5：也是把多个硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，分别存储在不同磁盘上。
RAID1+0：就是RAID1和RAID0的结合。同时具备两个级别的优缺点。一般建议数据库使用这个级别。

SAN和NAS是否适合数据库？
1.常用于高可用解决方案
2.顺序读写效率很高，但是随机读写不如人意
3.数据库随机读写比率很高

https://blog.youkuaiyun.com/u014465934/article/details/80576673
https://blog.youkuaiyun.com/u014465934/article/details/80591316