MySQL查询优化总结

MySql的性能优化其实有很多方面，比如从客户端层面，从应用层面等等，在这里暂只关注SQL的优化，后续遇到非SQL的MySQL优化的真实的场景，再做进一步介绍。

索引创建和使用的一些原则

除了前面case中或多或少提到过的一些原则，这里又简单整理了一些。

1. 代码先行，索引后上

SQL是根据业务而生的，索引也是根据业务而做的优化，如果开始没有非常明确的需求和较为严谨的设计，不建议上来就设计各种索引。可以先把主体业务开发完，回头从全局再从全局审视一遍哪些地方合适建立索引，哪些地方未来可能需要索引。

1. 考虑索引列中不重复值的比例或个数

尽量不在离散度太低的列上建立单个索引，比如没有强业务需求，不要在类似sex性别列上建索引，它的区分度不高，如果这个条件进行索引扫描后，还是回表，由于离散度很低，导致筛选出来记录数太多，要做回表的代价也就很大，MySQL查询优化器可能直接全表扫描了。因此需要考虑索引列中不重复值的比例或者个数，如果性能要求高，还可以对索引列进行统计，针对不同情况进行优化。

1. 索引列占用空间尽量小

比如索引列能用int就不用bigint，因为数据类型越小，索引占用的存储空间就越少，在一个数据业内就可以存放更多的索引记录，磁盘IO带来的性能损耗也就越小（一次页面IO可以讲更多的记录加载到内存），读写效率也就越高。这个建议对主键来说更加适用，因为不仅聚簇索引会存储主键，其他的辅助索引也会存主键。

再比如当列比较大的时候（可能是长字符串），为列前缀建立索引，类似：

alter table single_table add index idx_key1(key1(10));

这样，在查询key1='sdsdgfgfhghkljdfkj'的时候先在辅助索引中定位到前10个字符，然后在判断是否后面的字符相等。需要注意的是，这种定义的索引最好只应用于等值查询，对于范围查询，就捉襟见肘了，特别是排序，是无法使用的。

1. 业务允许情况下，where与order by冲突，优先where

一般情况下一条语句中只能使用一个索引，如果有where条件和order by同时出现，且都有索引，建议根据情况优先where的索引，因为往往where后会过滤记录，相比于使用order by后面列的索引，回表次数要少。另外如果记录数不多也可以在应用层进行排序，减小MySQL压力。当然MySQL也支持索引合并，意思就是可以使用一个以上的索引，把两个索引树查询的结果做交集或者并集等。想要了解的可以参考MySQL索引合并详解。

1. 几个条件几乎都一起使用，建联合索引

如果几个条件基本不分开使用，没必要分别建立索引，建一个索引相当于要存储一个索引树。

1. 使用联合索引时，尽量把索引中涉及到字段都用上

联合索引，注意最左前缀原则，最好能保证联合索引所有字段都在索引扫描中使用到，性能可能会更好。

1. 尽量使用索引覆盖

使用索引覆盖，能有效减少回表，特别是对于大量回表的场景，能较大幅度提升性能。

1. 避免重复索引

比如对于表

create table test(

id int not null primary key,

a int not null,

b int not null

)

建立索引(a,b) 和索引(a)，是重复建立索引；

建立索引(a)和索引(a，id) 也是重复索引。

查询慢的本质

其实从本质上来讲，查询慢除了MySQL软件本身的一些问题外，基本上都是因为我们在使用MySQL时，让其访问或者扫描了较多的数据。这里的数据并不一定是表中我们存放的数据，它还可能是MySQL的一些内部数据结构，比如辅助索引等。而出现这一问题的原因，一般都是由于使用不适当，主要是在业务上和MySQL执行上：在业务上可能是我们没有很好的使用MySQL；在MySQL执行角度上，可能是我们没有从原理去理解MySQL如何进行查询，从而导致我们写的SQL语句和设计的索引并不是很高效的执行。我们再稍微详细一点，看看都从哪些方面去考虑问题：

• 从业务角度，业务本身并不是强烈需要某种查询结果或者可以有更好的查询方式，但没有做过或者做好。比如：

• • 是否查询了不必要的记录
  • 是否查询了所有列
  • 是否重复相同的查询（可能条件会变）

• 从MySQL执行角度，由于创建、使用索引不用适当或者查询语句的不适当，导致查询效率不高。比如：

• • 是否实际需要查询的行数（返回的行数）与扫描的行数差距较大？

比如最简单的limit。

• • 是否实际访问类型（Explain执行计划中的tpye字段）级别较低？

从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等，速度是从慢到快，扫描的行数也是从大到小。对其中相关的扫描表、扫描索引、范围访问和单值访问的原理要非常熟悉。

重构查询的方法论

有时候我们也根据原理进行了SQL的优化，索引也进行优化，但业务就要求我们需要那样一个查询结果，因为查询效率低而直接放弃这种业务场景是极为不智的。其实我们应该想清楚我们要的到底是什么的性能？其实是业务的性能，而不是单纯MySQL的性能，目标应该是找到一个更优的方法获得实际需要的结果——而不一定总是需要从MySQL获取一模一样的结果。有时候，可以将查询转换一种写法让其返回一样的结果,但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

• 我们需要的是一个复杂的查询还是多个简单的查询？
• 是否需要切分查询，比如大范围查询切分为小范围查询？
• 是否可以分解关联查询？是否可以将部分数据缓存在应用层？

MySQL排查查询性能问题的工具

MySQL慢查询日志

了解了索引的数据结构，分析了MySQL在查询时的原理，介绍了Explain语句查看执行计划，还熟悉了一下成本分析与MySQL统计信息的来源，截止目前为止，我们基本上可以从原理上分析和优化MySQL的查询了。接下来简单聊聊如何发现这些性能问题。

MySQL本身提供了记录慢查询的功能，以日志形式，通过记录在MySQL中响应时间超过阀值的语句来完成此功能的。这个阈值为系统参数long_query_time，默认为10s，可改。慢查询日志默认关闭，可通过系统参数slow_query_log开启，1表示开启。慢查询日志默认被命名为${host_name}-slow.log，打开即可查看，但日志为文本格式，查看起来比较费劲，MySQL还提供了对慢查询日志进行分析的工具mysqldumpslow。

慢查询几个很重要的参数：

long_query_time：上面有提到，不多说。

log-queries-not-using-indexes：为1表示慢查询日志中记录所有没有走索引的语句，这对我们进行性能优化也有着非常大的作用。

log_throttle_queries_not_using_indexes：如果log_queries_not_using_indexes打开，该参数将限制每分钟写入日志的SQL数量。

min_examined_row_limit：对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中。这个参数也比较重要。但需要注意，对于此参数，如果扫描行数小于此参数，就算整体耗时查过long_query_time，也不会被记录在日志中。

需要注意的是慢查询会有CPU和日志IO的开销，线上尽量采取间断开启慢日志方式收集慢查询。

小总结

对于慢查询，我们需要多关注查询响应时间、是否走索引、扫描行数，由于有CPU和IO开销，线上需要间断开启慢日志。

MySQL日志分析工具mysqldumpslow

[root@DB-Server ~]# mysqldumpslow --help
 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
 
Parse and summarize the MySQL slow query log. Options are
 
  --verbose    verbose
  --debug      debug
  --help       write this text to standard output
 
  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default（排序方式）
                 al: average lock time（平均锁定时间）
                 ar: average rows sent（平均返回记录数）
                 at: average query time（平均查询时间）
                  c: count（访问计数）
                  l: lock time（锁定时间）
                  r: rows sent（返回记录）
                  t: query time（查询时间）
   -r           reverse the sort order (largest last instead of first)
   -t NUM       just show the top n queries（返回前面n条数据）
   -a           don't abstract all numbers to N and strings to 'S'
   -n NUM       abstract numbers with at least n digits within names
   -g PATTERN   grep: only consider stmts that include this string（正则匹配模式，大小写不敏感）
   -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                default is '*', i.e. match all
   -i NAME      name of server instance (if using mysql.server startup script)
   -l           don't subtract lock time from total time

常用的mysqldumpslow命令

取出使用最多的10条慢查询

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log 

取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log

按照扫描行数最多的

mysqldumpslow -s r -t 10 /var/run/mysqld/mysqld-slow.log

其他的例子在这里就不举了，大家可以自己下去试试。

第三方日志分析工具pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具，它不但可以分析slowlog，还可以分析binlog、General log。pt-query-digest把分析结果输出到文件中，分析过程大致是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

pt-query-digest的具体使用例子就不在这里聊了，

总结

• 索引是排好序的数据结构，“排好序”尤为关键，它决定着MySQL能否更好的进行范围查询和顺序扫描以及解决排序问题等。
• MySQL的InnoDB引擎的索引主要采用经过小小改造的B+Tree（叶子节点为双向链表，也是首尾相连的循环链表），跟其他数据结构相比，可以容纳更多的数据，且查询性能高。叶子节点双向链表，可以方便的从任意叶子节点向前或者向后扫描。
• 如果能让MySQL顺序扫描叶子节点，可能会利用顺序读，保证一定性能。因为在写数据的时候可能会一次性写很多页，在磁盘上可能是顺序写。

• • 对于聚簇索引，叶子节点按照主键排序，因此如果不是业务必须，自增主键是个良好选择，因为自增主键不需要对其重排序，B+Tree结构不需要总是调整，这就可能会顺序写。
  • 对于辅助索引，叶子节点按照索引列排序，也可能顺序写，但如果频繁更新和删除，会频繁更改B+Tree结构，读的时候就不一定能顺序读了。
  • 回表操作基本都是随机读，因为在辅助索引中，主键不是有序的，回表相当于随机访问指定的叶子节点。

• 所有的SQL出现问题，都要想到B+Tree这个结构，辅助索引数据结构是B+Tree，无辅助索引，即只有聚簇索引，也是B+Tree，从B+Tree上考虑问题，才能尝试去分析。
• 排序、分页、表连接的优化见文章的对应的小总结
• MySQL并不一定走索引，或者并不一定走你心中所想的索引，这取决与成本估算，MySQL会对全表扫描，各种不同的索引，各种不同的连接做成本估算，以成本代价最少的方案作为执行计划，当然MySQL的成本估算不是什么时候都那么准确的，如果MySQL进行全表扫描，或者使用了你不想使用的索引，你可以在语句中强制使用你想要使用的索引。
• 性能优化的思路：

• • 根据业务性能反馈或者MySQL慢查询进行分析，找到需要做性能优化的语句以及一些性能指标或者关键点。
  • 需要关注的性能方面的关键点包括但不限于返回结果数，扫描行数，执行type类型，是否走索引，是否较多的回表，是否文件排序等等。相应的，优化方式也是减少不必要结果返回，减少不必要的扫描，合理建立索引，减少回表等。
  • 上述需要关注的点可以通过慢日志的分析和执行计划以及成本的分析获取。
  • 除了对SQL语句的优化，考虑在应用或者业务层面进行优化，比如复杂查询拆解，范围查询分查，应用层做连接等等。

MySQL的性能优化除了SQL的优化、业务和应用层面的优化，还有系统参数的调优以及锁的优化，锁、MVCC等等相关知识也会在其他文章慢慢补充。

虽然业界说MySQL源码写的烂，但它毕竟是大多数互联网各厂用的最广泛的数据库，经过了长时间的考验，还是博大精深的，我们要学的是其中的思想，也到不了源码那个层次。

不管怎样，了解MySQL的一些原理除了满足现有工作所需，还有助于对比学习一些其他比较优秀的数据库，比如PostgreSQL，其索引结构也与MySQL有一定的相似。技术道路甚远，且行且学！