如何定位并优化慢查询SQL

目录

• 开启慢查询日志
• 找到慢查询通过explain定位问题
• 优化手段
• 索引的成本
• 索引失效的情况
• 参考

 

---

回到顶部

开启慢查询日志

show variables like '%quer%'主要关注：

1. long_query_time
2. slow_query_log
3. slow_query_log_file

回到顶部

找到慢查询通过explain定位问题

我本机装的是MySQL8.0

1. type：NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL，越往右效率越低，则需要优化
   • ALL：全表扫描
   • index：全表扫描，扫描整颗索引树。用到了覆盖索引时会是index【1】
   • range：索引的范围查询
   • index_merge：表示查询使用了两个以上的索引，最后取交集或者并集
   • ref：根据索引来找
   • ...
2. extra
   • Using filesort：表示MySQL会对结果使用给一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为“文件排序”
   • Using temporary，表示MySQL在对查询结果排序时使用临时表。常见于order by、group by、join
   • Using index，使用了覆盖索引
3. possible_keys
   可能的索引
4. key
   实际走的索引

主要关注：ALL、index、Using filesort、Using temporary

回到顶部

优化手段

1. 组合索引，举个例子：创建组合索引(a, b, c) vs 创建三个单独的索引a，b，c【4】

   • 组合索引（√表示走了索引，×表示没有走索引，后面为where的查询条件）
     √ a
     √ a and b
     √ b and a
     √ a and c，这里虽然在确定a的范围后c是无序的，但是还是走了这个索引，大概是因为优化器觉得通过该方式成本更低（待补充）
     √ a and b and c
     × b
     × c
     × b and c
     × a or c
   • 三个单独的索引
     √ a and b and c，只有一个走索引，优化起来选择
     √ b and c，只有一个走索引，优化起来选择
     √ a or b，都走，且explain中的type变成了index_merge，5.0之前只有一个会走

2. 覆盖索引，简单来说就是将我们select中包含的字段都放到二级索引中，减少回表

   • select a, b, c from tb where between ? and ?;建一个组合索引(a, b, c)，这样二级索引中就有字段b和c了，不需要再次回表查询。（前提：使用InnoDB存储引擎）
   • 延迟关联【5】select * from tb where a = ? and b like '%haha%'，没有任何的索引能覆盖*，所以只能走索引a，找到满足a=?的行之后，再去过滤出b like %haha%的行，最终回表查询*。

3. ...

4. 针对Using filesort的情况

   • MySQL之order by

5. 针对Using temporary的情况

回到顶部

索引的成本

在InnoDB中使用的是B+树作为索引，B+的特点是高扇出性，在数据库中B+的高度一般在2~4层【6】。这是怎么计算出来的呢？
在InnoDb中最小的存储单元是page，一个page的大小默认为16kb，数据与索引都是通过page来组织的。首先来看两层的B+树能查询多少数据，总数 = 根结点指针的个数 * 一个page能存放的数据的个数，假设主键使用的bigint类型（讨论聚簇索引 or 使用bigint的二级索引），占8字节，而InnoDB中指针占6字节（源码），因此根结点指针的个数 = 16kb / (8 + 6)Byte ≈ 1170（其实指针应该比主键多一个），我们假设表中一行占用1kb（通常不会这么大）因此一个page可以存放：16kb / 1kb = 16，最终可以计算出总数 = 1170 * 16 = 18720，那么三层的B+树能查询的数据为：1170 * 1170 * 16 = 21,902,400 ≈ 2千万，对于一般的表这个量级也足够了。【7】

从上面可以看出，优化慢查询添加或修改索引是非常重要的手段，但引入一项技术必然也会增加对它的维护成本，主要成本在于增加和删除数据时对于B+的调整，且增加索引也意味着需要更多的空间，因此如果能提前预判某个表的数据量不会很大，那就不用增加索引了。

回到顶部

索引失效的情况

1. 等值匹配中有函数计算，例：left(name, 4) = 'haha'
2. 范围条件右边的列失效，例：表中有索引(a, b, c)，查询条件为a = ? and b > ? and c = ?，此时c就使用不了索引了，因为c有序是在b相等的情况下，而这个查询条件中b是一个范围，所以从整体上来看c是这样的：1,2,3,1,2,3...需要注意的是这里的“右”指的是组合索引中的位置，而不是查询条件的位置。若表中的索引为(a, c, b)那么该查询是可以走索引的
3. 索引字段上使用不等，例：name <> ?
4. 索引字段上判断null，例：name is not null
5. like，例：name like '%haha'
6. 类型不匹配，例：name为varchar类型，但查询条件为：name = 123
7. or的左右不都有索引，例：name = 'haha' or age = 10，只有一个字段有索引则会全表扫描

回到顶部

参考

1. https://blog.csdn.net/qq_41946557/article/details/103527964
2. https://segmentfault.com/a/1190000023565685
3. 极客时间MySQL实战45讲
4. https://blog.csdn.net/Abysscarry/article/details/80792876
5. 《高性能MySQL》(第3版)第五章
6. 《MySQL技术内幕（InnoDB存储引擎）》
7. https://zhuanlan.zhihu.com/p/86137284