MySQL数据库优化
数据库优化的目的
1.避免出现页面访问错误
- 由于数据库连接 timeout 产生页面5xx错误
- 由于慢查询造成页面无法加载
- 由于阻塞造成数据无法提交
2.增加数据库的稳定性
- 很多数据库问题都是由低效的查询引起的
3.优化用户体验
- 流畅的页面访问速度
- 良好的网站功能体验
MySQL数据库优化
上图是数据库优化的金字塔结构。可以看出，SQL及索引优化位于金字塔的最低层，是数据库优化的基础，成本最低，效果却最好。
一 SQL语句优化
找出有问题的SQL
1.1 使用MySQL慢查询日志对低效率的SQL进行监控
1）查询是否开启了慢查询日志
show variables like 'slow_query_log';
2）设置记录未使用索引的查询
set global log_queries_not_using_indexes=on;
3）设置慢查询时间
set global long_query_time=0.1;
注：直接修改 global 的 long_query_time 在当前窗口是不生效的，在新打开的窗口才有效果。如果想让当前窗口生效，在设置时不用加 global 关键字。
4）设置慢查询日志地址
set global slow_query_log_file='/home/log/mysql/mysql-query.log';
5）开启慢查询日志
set global slow_query_log=on;
开启之后，就会在之前设置的 slow_query_log_file 文件中看到慢查询日志。
1.2 慢查询日志的格式
1）SQL的执行时间
# Time: 140712 8:32:58
2）执行SQL的主机信息
# User@Host: root[root] @ localhost []
3）SQL的执行信息（如：执行时间、锁定时间、发送行数、扫描行数）
# Query_time: 0.000282 Lock_time: 0.000092 Rows_sent: 3 Rows_examined: 3 SET timestamp=1405125199;
4）SQL的内容
select * from store limit 10;
1.3 MySQL慢查日志分析工具之 mysqldumpslow
mysqldumpslow 是MySQL官方自带的工具，可以满足平时简单统计。
查看使用帮助：
mysqldumpslow -h
用法：
mysqldumpslow 参数 日志文件路径
比如：想要得到按照发送行数排序的前3条数据
mysqldumpslow -t 3 -s r /home/log/mysql/mysql-query.log
mysqldumpslow 的不足之处：统计结果的信息不多，无法满足SQL优化的需要。
1.4 MySQL慢查日志分析工具之 pt-query-digest
pt-query-digest 是 Percona Toolkit 工具中的一个。需要自行安装 Percona Toolkit，再使用 pt-query-digest。
pt-query-digest 用法实例：
1）直接分析慢查询文件，并将分析结果存储到 slow.log:
pt-query-digest mysql-query.log > slow.log
2）分析指定范围内的查询：
pt-query-digest mysql-query.log --since '2017-05-28 23:28:00' --until '2017-05-28 23:59:59' > slow.log
3）分析含有 select 语句的慢查询：
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' mysql-query.log > slow.log
4）分析所有的全表扫描或者 full join 的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' mysql-query.log > slow.log
5）把分析结果保存到 query_review 表
pt-query-digest --user=root --password=123456 --review h=localhost,D=test,t=query_review --create-review-table slow.log
建议：当 mysql_query.log 很大时，最好还是将慢查询日志移到其他机器进行分析。使用 pt-query-digest 分析，比较耗费本地主机的资源。
1.5 根据慢查询日志的分析结果，找出有问题的SQL
1）查询次数多且每次查询占用时间长的SQL
通常为 pt-query-digest 分析的前几个查询
2）IO大的SQL
注意 pt-query-digest 分析中的 Rows examine 项
3）未命中索引的SQL
注意 pt-query-digest 分析中 Rows examine 和 Rows send 的对比
1.6 使用 explain 查询和分析 SQL 的执行计划
explain 返回结果中各列的含义
- table：显示这一行的数据是关于哪张表的
- type：显示连接使用了哪种类型，从最好到最差的连接类型为 const、eq_reg、ref、range、index 和 all
- possible_keys：显示可能应用在这张表中的索引。显示为空，则没有可能的索引
- key：实际使用的索引。如果为 NULL，则没有使用索引
- key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
- ref：显示索引的哪一列被使用了。如果可能的话，是一个常数
- rows：MySQL认为必须检查的用来返回请求数据的行数
- extra：显示 MySQL 在查询过程中的一些详细信息。若是 Using filesort 或者 Using temporary，就需要进行查询优化了
