数据库优化的目的
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: 3SET 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,就需要进行查询优化了