MySQL调优

为什么要进行优化?

  • 避免由数据库链接timeout产生页面5xx的错误
  • 避免由于慢查询造成页面无法加载
  • 避免由于阻塞造成数据无法提交
  • 优化用户体验

可以从哪几个方面进行数据库优化?

从图中可以看出,SQL及索引的优化是最重要的,成本最低效果最好。

下面分别来看看如何优化SQL和索引。

SQL优化

慢查询日志配置

可以使用慢查询日志对有效率问题的SQL进行监控。下面是关于如何开启慢查询日志和慢查询日志的一些配置。

show variables like 'slow_query_log'; //查看是否开启了慢查询
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'; //设置慢查询日志的位置
set global log_queries_not_using_indexes=ON; //是否记录未使用索引的查询
set global long_query_time=1;//设置记录超过多长时间的SQL语句
set global slow_query_log=ON;//设置慢查询日志是否开启

慢查询日志的格式:

详细看一下每一行都是什么意思。
查询的执行时间 Time:140606 12:30:17
执行SQL的主机信息 User@Host:root[root] @ localhost []
SQL的执行信息 Query_time: 0.000024 Lock_time:0.000000 Rows_sent:0 Rows_examined: 0
SQL执行时间 SET timestamp=1402389328
SQL的内容:show tables

分析慢查日志的工具

  1. mysqldumpslow

可以使用MySQL自带的慢查询分析工具mysqldumpslow,可以通过mysqldumpslow -h来查看具体的使用方法。

eg:mysqldumpslow -t 3 /path/to/mysql-slow-query.log | more

上面的命令会列出查询时间top 3的SQL语句,具体格式如下图,会列出SQL执行的次数,SQL来执行的时间,锁定的时间,发送的函数,由谁在哪个服务器上执行的和具体的SQL内容。

mysqldumpslow是比较常用的慢查询日志分析工具,但是分析结果包含的信息比较少,对于SQL优化来说可能还不太够。下面看看另一种分析工具。

  1. pt-query-digest

pt-query-digest支持将分析结果保存到文件或数据表中。

输出到文件
pt-query-digest slow.log > slow_log.report
输出到数据库表
pt-query-digest slow.log -review \
h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
--creat-reviewtable \
--review-history t=hostname_slow

通过pt-query-digest --help可以查看具体的使用方式。

eg: pt-query-digest /home/mysql/data/mysql-slow.log | more

通过上面的命令,会列出慢查询日志的分析结果,分为三个部分。
第一部分中包含日志中有多少个SQL,多少个不同的SQL,SQL执行的时间范围,总的执行时间,最短的执行时间,最长的执行时间,平均执行时间,总锁定时间,总发送行数,总检索行数等等。

pt-query-digest格式

第二部分包含关于 表和执行语句的统计,可以看到哪个表的哪个操作的实行时间是最多的,也可以看到对应的响应时间和调用次数。

pt-query-digest格式

第三部分就是具体的SQL的分析,包括对应语句执行时间,锁定时间,发送行数,检索行数等等。

pt-query-digest格式

定位有问题的SQL

通过上面的慢查询日志分析我们可以定位需要优化的SQL,通常有三种:

  • 查询次数多且每次查询占用时间长的SQL:通常为pt-query-digest分析的前几个查询。
  • IO大的SQL:注意pt-query-digest分析中的Rows examine项
  • 未命中索引的SQL: 注意pt-query-digest分析中Rows examine和Rows Send的对比。

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

可以通过Explain查询SQL的执行计划,例子如下:

Explain explain返回的各列的含义:

含义
table显示查询是关于哪个表的
type很重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
possible_keys显示可能应用在这张表中的索引。如果为空,没有可能应用的索引
key实际使用的索引。如果为NULL,则没有使用索引
key_len使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref显示索引的哪一列被使用了
rowsMYSQL认为必须检查的用来返回请求的行数
extra当这一列的值是Using filesort或Using temporary时,说明查询需要优化了

索引优化

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

  • 在where从句,group by从句,order by从句,on从句中出现的列
  • 索引的字段越小越好
  • 在建立联合索引时,离散度大的列放大联合索引的前面

如何维护和优化索引

要避免重复及冗余索引,重复索引是指相同的列以相同的顺序建立的同类型的索引。冗余索引是指多个索引的前缀列相同,或是在联合索引中包含了主键的索引。

可以使用pt-duplicate-key-checker工具可以检查重复及冗余索引。

同时还要注意及时删除由于业务变更不再使用的索引。目前MySQL中还没有记录索引的使用情况,但在PerconMuSQL和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用,在MySQL中目前只能通过慢查询日志配合pt-index-usage工具来进行索引的使用情况的分析。

MySQL调优

  • qq_43638135
    妲己再美究为妃: 博主没有想过自己接一些私活干吗?我现在还没毕业,但是我也确实听说外挂市场自动化游戏脚本市场挺火热的,并且报酬也很丰厚,但是具体的我也不是很清楚,求解答。 (1个月前 #47楼) 查看回复(2) 举报 回复
    22