慢SQL调优-索引详解

---

前言

最新的 Java 面试题，技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等，会持续更新。

如果对老铁有帮助，帮忙免费点个赞，谢谢你的发财手！

一、慢查询日志设置

1、开启慢查询日志，设置超过几秒为慢SQL语句，抓取慢SQL语句；
（阿里的ARMS监控平台直接就能查询到慢SQL）

show variables like "%query%"

• 1）参数log_queries_not_using_indexes ：表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询日志中，OFF表示不记录，ON表示记录
• 2）参数long_query_time ：表示“多长时间的查询”被认定为“慢查询”，默认值为10秒，表示超过10秒的查询被认定为慢查询
• 3）参数 slow_query_log ：表示是否开启慢查询日志。开启：“> set global slow_query_log=on”关闭：“> set global slow_query_log=off ”
• 4）参数slow_query_log_file：指定慢查询日志存储于哪个日志文件中，默认的慢查询日志文件名为“主机名-slow.log”，慢查询日志的位置为datadir参数所对应的目录位置。
  慢查询日志中给出了账号、主机、运行时间、锁定时间、返回行等信息，当日志文件越来越大，通过vi或cat命令不能很直观地查看日志，这时就可以使用MySQL内置的mysqldumpslow命令来查询：

mysqldumpslow -s r -t 5 /data/mysql/mysql_slow.log

查询返回记录集最多的5个慢查询SQL。

二、explain查看执行计划

2、通过explain查看SQL执行计划，重点关注type、key、rows、extra指标；创建索引并调整语句，再查看执行计划，对比调优结果。

• 1）id：反映的是表的读取顺序或查询中执行select语句的顺序。
  ① id相同，可以认为是一组，从上往下顺序执行；
  ② id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。
• 2）select_type：表示select的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
  ① SIMPLE：表示查询语句不包含子查询或union。
  ② PRIMARY：查询中若包含任何复杂的子部分，最外层查询标记为primary。
  ③ SUBQUERY：select中的子查询语句
  ④ DEPENDENT SUBQUERY：select或where列表中的子查询。
  ⑤ DERIVED（衍生）：在from列表中包含的子查询，MySQL 会将结果存放在一个临时表中。
  ⑥ UNION：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为DERIVED。
• 3）table：表名称或别名（显示这一行的数据是关于哪张表的）。
• 4）type：表示MySQL在表中找到目标行的方式，又称“访问类型”;
  性能：system > const > eq_ref > ref > range > index > ALL
  ① const、system：表示通过索引一次就查询到了相关记录，一般为主键或唯一索引查询，system是const类型的特例，当查询的表只有一行的情况下使用。
  ②eq_ref：类似ref，区别在于使用的索引是唯一索引，对于每个索引键，表中只有一条记录与之匹配，常见于多表连接中，一般使用primary key或者unique key作为关联条件。
  ③ ref：非唯一性索引扫描，返回索引过滤的数据，可为多条，常出现在关联查询中。
  ④ range：使用索引进行范围扫描，一般就是在where语句中出现between、< 、>、in等。
  ⑤ index:：index与ALL的区别为index类型只遍历索引树。
  ⑥ ALL：扫描全表数据行。
• 5）possible_keys：表示查询时可能使用到的索引。
• 6）key：查询时真正使用到的索引，如果没有选择索引，则显示是NULL。
• 7）key_len：显示索引中使用的字节数，可以判断是否全部使用了组合索引。
• 8）ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
• 9）rows：显示MySQL根据表统计信息，估算找到所需的记录要读取的行数，越小越好。
• 10）Extra：该列包含MySQL解决查询的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
  ① Using where：表示查询需要通过where条件查询数据。
  ② Using temporary：使用临时表保存中间结果，常见的情况有使用distinct关键字，join语句中使用order by或group by无索引列、order by与group by字段不同、union子查询等。
  ③ Using filesort：表示有order by操作而且无法利用索引完成的排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化。
  ④ Using join buffer：表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值，应当注意，根据查询的具体情况可能需要添加索引来改进。
  ⑤ Using index：表示使用了索引覆盖（select要查询的字段少于或等于创建的索引字段），不需要访问表。如果与Using where一起出现，则表示索引用于查询过滤，还需回表查询出所需数据。
  ⑥ Using Index Condition：表示通过使用索引对存储引擎索引出的数据进行再过滤，减少回表查询的次数。

总结一下针对explain命令生成执行计划：

• 首先关注查询类型type列，如果出现all关键字，代表全表扫描，没有用到任何index；
• 再看key列，如果key列是NULL，代表没有使用索引；
• 然后看rows列，该列数值越大意味着需要扫描的行数越多，相应耗时越长；
• 最后看Extra列，要避免出现Using filesort或Using temporary这样的字眼，这是很影响性能的。
  对于没有走索引的查询，通过添加适当的索引，注意需对照原表上的索引，看看有没有需要合并成联合索引，避免构建过多的索引，会占用空间和影响插入/更新的效率。

三、索引失效

下面列出常见的一些索引失效的场景：

• 1. 索引列上加函数：在查询的索引列上使用内置函数都会让索引失效。
• 2. 对索引列运算：与使用函数相似，都是会使得索引列值发生变化，从而无法使用索引。
• 3. 联合索引最左匹配原则：不满足最左匹配原则，索引不生效。
• 4. 隐式类型转换：select * from t_user where tel = 123; tel字段是varchar类型，但数值是int类型，自动类型转换会使得索引失效。
• 5. 范围查询阻断后续字段不能走索引：范围查询包括 >=、<=、>、<、in、between。
• 6. 负向查询和is NULL判断可能导致索引失效：负向查询包括 NOT、<>、!>、!<、!= 等。
• 7. 使用like模糊查询，前后都加了%，”%李%”不会走索引, 而使用like “李%”会走索引。
• 8. asc和desc混用：select * from t order by a asc, b desc。
• 9. or：如果是单例索引，or会使用索引；如果是组合索引，or不会使用索引。

四、索引操作

• 1、添加PRIMARY KEY(主键索引)

ALTER TABLE 表名 ADD PRIMARY KEY ( 字段 )

• 2、添加UNIQUE(唯一索引)

ALTER TABLE 表名 ADD UNIQUE (字段)

• 3、添加INDEX(普通索引)

ALTER TABLE 表名 ADD INDEX 索引名 ( 字段 )

• 4、添加联合索引

ALTER TABLE 表名 ADD INDEX 索引名 ( 字段1, 字段2, ...)

• 5、删除索引：

ALTER TABLE 表名 DROP INDEX 索引名;

五、profile 分析执行耗时

• 1、查询profile开启状态
  show variables like ‘%profil%’
  

  1）have_profiling：确定是否支持 profile；
  2）profiling：是否开启profiling；
  3）profiling_history_size：定义MySQL服务器最近接收到的SQL条数。

• 2、开启profiling
  set profiling=ON
  set profiling_history_size=30

• 3、查看最近运行的SQL
  – 查询最近30条SQL

show profiles;

（查看数据库版本：show variables like ‘%version%’;）