MySQL 常见慢查询原因和处理方法

一、索引

在数据量不是很大时，大多慢查询可以用索引解决，大多慢查询也因为索引不合理而产生。

MySQL 索引基于 B+ 树，这句话相信面试都背烂了，接着就可以问最左前缀索引、 B+ 树和各种树了。

说到最左前缀，实际就是组合索引的使用规则，使用合理组合索引可以有效的提高查询速度，为什么呢？

因为索引下推。如果查询条件包含在了组合索引中，比如存在组合索引（a,b)，查询到满足 a 的记录后会直接在索引内部判断 b 是否满足，减少回表次数。同时，如果查询的列恰好包含在组合索引中，即为覆盖索引，无需回表。 索引规则估计都知道，实际开发中也会创建和使用。 问题可能更多的是：为什么建了索引还慢？

1.什么原因导致索引失效

建了索引还慢，多半是索引失效（未使用），可用 explain 分析。索引失效常见原因有 :

1. where 中使用 != 或 <> 或 or 或表达式或函数（左侧）
2. like 语句 % 开头
3. 字符串未加’’
4. 索引字段区分度过低，如性别
5. 未匹配最左前缀

(一张嘴就知道老面试题了) 为什么这些做法会导致失效，成熟的 MySQL 也有自己的想法。

2.这些原因为什么导致索引失效

如果要 MySQL 给一个理由，还是那棵 B+ 树。

函数操作

当在 查询 where = 左侧使用表达式或函数时，如字段 A 为字符串型且有索引, 有 where length(a) = 6查询，这时传递一个 6 到 A 的索引树，不难想象在树的第一层就迷路了。

隐式转换

隐式类型转换和隐式字符编码转换也会导致这个问题。

• 隐式类型转换对于 JOOQ 这种框架来说一般倒不会出现。
• 隐式字符编码转换在连表查询时倒可能出现，即连表字段的类型相同但字符编码不同。

破坏了有序性

至于 Like 语句 % 开头、字符串未加 ’’ 原因基本一致，MySQL 认为对索引字段的操作可能会破坏索引有序性就机智的优化掉了。

不过，对于如性别这种区分度过低的字段，索引失效就不是因为这个原因。

3.性别字段为什么不要加索引

为什么索引区分度低的字段不要加索引。盲猜效率低，效率的确低，有时甚至会等于没加。

对于非聚簇索引，是要回表的。假如有 100 条数据，在 sex 字段建立索引，扫描到 51 个 male，需要再回表扫描 51 行。还不如直接来一次全表扫描呢。

所以，InnoDB 引擎对于这种场景就会放弃使用索引，至于区分度多低多少会放弃，大致是某类型的数据占到总的 30% 左右时，就会放弃使用该字段的索引，有兴趣可以试一下。

4.有什么好用且简单的索引方法

前面说到大多慢查询都源于索引，怎么建立并用好索引。这里有一些简单的规则。

• 索引下推：性别字段不适合建索引，但确实存在查询场景怎么办？如果是多条件查询，可以建立联合索引利用该特性优化。

• 覆盖索引：也是联合索引，查询需要的信息在索引里已经包含了，就不会再回表了。

• 前缀索引：对于字符串，可以只在前 N 位添加索引，避免不必要的开支。假如的确需要如关键字查询，那交给更合适的如 ES 或许更好。

• 不要对索引字段做函数操作

• 对于确定的、写多读少的表或者频繁更新的字段都应该考虑索引的维护成本。

5.如何评价 MySQL 选错了索引

有时，建立了猛一看挺正确的索引，但事情却没按计划发展。就像“为啥 XXX 有索引，根据它查询还是慢查询”。

此刻没准要自信点：我的代码不可能有 BUG，肯定是 MySQL 出了问题。MySQL 的确可能有点问题。

这种情况常见于建了一大堆索引，查询条件一大堆。没使用你想让它用的那一个，而是选了个区分度低的，导致过多的扫描。造成的原因基本有两个：

• 信息统计不准确：可以使用 analyze table x重新分析。

• 优化器误判：可以 force index强制指定。或修改语句引导优化器，增加或删除索引绕过。

但根据我浅薄的经验来看，更可能是因为你建了些没必要的索引导致的。不会真有人以为 MySQL 没自己机灵吧？

除了上面这些索引原因外，还有下面这些不常见或者说不好判断的原因存在。

二、等MDL锁

在 MySQL 5.5 版本中引入了 MDL，对一个表做 CRUD 操作时，自动加 MDL 读锁；对表结构做变更时，加 MDL 写锁。读写锁、写锁间互斥。

当某语句拿 MDL 写锁就会阻塞 MDL 读锁，可以使用show processlist命令查看处于Waiting for table metadata lock状态的语句。

三、等flush

flush 很快，大多是因为 flush 命令被别的语句堵住，它又堵住了 select 。通过show processlist命令查看时会发现处于Waiting for table flush状态。

四、等行锁

某事物持有写锁未提交。

五、当前读

InnoDB 默认级别是可重复读。设想一个场景：事物 A 开始事务，事务 B 也开始执行大量更新。B 率先提交， A 是当前读，就要依次执行 undo log ，直到找到事务 B 开始前的值。

六、大表场景

在未二次开发的 MYSQL 中，上亿的表肯定算大表，这种情况即使在索引、查询层面做到了较好实现，面对频繁聚合操作也可能会出现 IO 或 CPU 瓶颈，即使是单纯查询，效率也会下降。

且 Innodb 每个 B+ 树节点存储容量是 16 KB，理论上可存储 2kw 行左右，这时树高为3层。我们知道，innodb_buffer_pool 用来缓存表及索引，如果索引数据较大，缓存命中率就堪忧，同时 innodb_buffer_pool 采用 LRU 算法进行页面淘汰，如果数据量过大，对老或非热点数据的查询可能就会把热点数据给挤出去。

所以对于大表常见优化即是分库分表和读写分离了。

1.分库分表

方案

是分库还是分表呢？这要具体分析。

• 如果磁盘或网络有 IO 瓶颈，那就要分库和垂直分表。
• 如果是 CPU 瓶颈，即查询效率偏低，水平分表。

水平即切分数据，分散原有数据到更多的库表中。

垂直即按照业务对库，按字段对表切分。

工具方面有 sharding-sphere、TDDL、Mycat。动起手来需要先评估分库、表数，制定分片规则选 key，再开发和数据迁移，还要考虑扩容问题。

问题

实际运行中，写问题不大，主要问题在于唯一 ID 生成、非 partition key 查询、扩容。

• 唯一 ID 方法很多，DB 自增、Snowflake、号段、一大波GUID算法等。

• 非 partition key 查询常用映射法解决，映射表用到覆盖索引的话还是很快的。或者可以和其他 DB 组合。

• 扩容要根据分片时的策略确定，范围分片的话就很简单，而随机取模分片就要迁移数据了。也可以用范围 + 取模的模式分片，先取模再范围，可以避免一定程度的数据迁移。

当然，如果分库还会面临事务一致性和跨库 join 等问题。

2.读写分离

为什么要读写分离

分表针对大表解决 CPU 瓶颈，分库解决 IO 瓶颈，二者将存储压力解决了。但查询还不一定。

如果落到 DB 的 QPS 还是很高，且读远大于写，就可以考虑读写分离，基于主从模式将读的压力分摊，避免单机负载过高，同时也保证了高可用，实现了负载均衡。

问题

主要问题有过期读和分配机制。

• 过期读，也就是主从延时问题。

• 分配机制，是走主还是从库。可以直接代码中根据语句类型切换或者使用中间件。

七、小结

以上列举了 MySQL 常见慢查询原因和处理方法，介绍了应对较大数据场景的常用方法。

分库分表和读写分离是针对大数据或并发场景的，同时也为了提高系统的稳定和拓展性。但也不是所有的问题都最适合这么解决。

 

原文