12个Mysql慢查询原因整理

---

学会用 explain 分析
explain select * from user_info where name =’xxx’ ;
平时我们用 explain 分析 SQL 的时候，如果 type=range, 要注意一下，可能因为数据量问题，导致索引无效

1、SQL没加索引

在 where 的条件列，建立索引，尽量避免全表扫描

2、SQL索引不生效

2.1 隐式的类型转换，索引失效
查询语句不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL 会做隐式的类型转换，把它们转换为浮点数再做比较

2.2 查询条件包含 or，可能导致索引失效
遇到不走索引的时候，考虑拆开两条 SQL

2.3 like 通配符可能导致索引失效
并不是用了 like 通配符，索引一定会失效，而是 like 查询是以 % 开头，才会导致索引失效。
优化：

• 使用覆盖索引
• 把 % 放后面

2.4 查询条件不满足联合索引的最左匹配原则
查询条件列 name 不是联合索引 idx_userid_name 中的第一个列，索引不生效
在联合索引中，查询条件满足最左匹配原则时，索引才正常生效。

2.5 在索引列上使用 mysql 的内置函数

优化
可以把内置函数的逻辑转移到右边

2.6 对索引进行列运算（如，+、-、*、/）, 索引不生效
不可以对索引列进行运算，可以在代码处理好，再传参进去。

2.7 索引字段上使用（！= 或者 < >），索引可能失效

2.8 索引字段上使用 is null， is not null，索引可能失效
单个not null 生效 多个not null用or连接会失效

2.9 左右连接，关联的字段编码格式不一样
做表关联时，注意一下关联字段的编码问题

2.10 优化器选错了索引

3、limit 深分页问题

原因:

• limit 语句会先扫描 offset+n 行，然后再丢弃掉前 offset 行，返回后 n 行数据。也就是说 limit 100000,10，就会扫描 100010 行，而 limit 0,10，只扫描 10 行。
• limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

优化:
可以通过减少回表次数来优化。一般有标签记录法和延迟关联法

4、单表数据量太大

原因
因为维护索引的 B+ 树结构层级变得更高了，查询一条数据时，需要经历的磁盘 IO 变多，因此查询性能变慢

分析
InnoDB 存储引擎最小储存单元是页，一页大小就是 16k

• 如果一行记录的数据大小为 1k，那么单个叶子节点可以存的记录数 =16k/1k =16.

• 非叶子节点内存放多少指针呢？我们假设主键 ID 为 bigint 类型，长度为 8 字节 (面试官问你 int 类型，一个 int 就是 32 位，4 字节)，而指针大小在 InnoDB 源码中设置为 6 字节，所以就是 8+6=14 字节，16k/14B =16*1024B/14B = 1170

一棵高度为 2 的 B + 树，能存放 1170 * 16=18720 条这样的数据记录
一棵高度为 3 的 B + 树，能存放 1170 *1170 *16 =21902400可以存放两千万左右的记录。
B + 树高度一般为 1-3 层，已经满足千万级别的数据存储

优化
一般超过千万级别，我们可以考虑分库分表了。

分库分表可能导致的问题：

• 事务问题
• 跨库问题
• 排序问题
• 分页问题
• 分布式 ID
• 方案：
  1、历史数据归档
  2、考虑垂直、水平分库分表。水平分库分表策略的话，range 范围、hash 取模、range+hash 取模混合等等

5、 join 或者子查询过多

业务需要的话，关联 2~3 个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，
在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成 map，然后在业务层进行数据的拼装

6、in 元素过多

in 元素一般建议不要超过 500 个，如果超过了，建议分组，每次 500 一组进行
分批进行，每批 500 个

select user_id,name from user where user_id in (1,2,3...500);

如果传参的 ids 太多，还可以做个参数校验

7、数据库在刷脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为 “脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为 “干净页”。一般有更新 SQL 才可能会导致脏页
深入了解分析

8、order by 文件排序

看一遍就理解：order by 详解

9、 拿不到锁

表被锁住了
可以用 show processlist 命令，看看当前语句处于什么状态

10、delete + in 子查询不走索引！

MySQL 对 select in 子查询做了优化，把子查询改成 join 的方式，所以可以走索引，对于 delete in 子查询，MySQL 却没有对它做这个优化

11、group by 使用临时表

group by 一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢 SQL

优化

• group by 后面的字段加索引
• order by null 不用排序
• 尽量只使用内存临时表
• 使用 SQL_BIG_RESULT

参考：[看一遍就理解：group by 详解](看一遍就理解：group by 详解)

12、系统硬件或网络资源

• 如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，这时候可以升级配置。
• 如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器 CPU 占用很高或者 IO利用率很高，这种情况下所有语句的执行都有可能变慢

13、其他

如果测试环境数据库的一些参数配置，和生产环境参数配置不一致的话，也容易产生慢 SQL 哈。之前见过一个慢 SQL 的生产案例，就是测试环境用了 index merge，所以查看 explain 执行计划时，是可以走索引的，但是到了生产，却全表扫描，最后排查发现是生产环境配置把 index merge 关闭

转自:

• 原文作者：PHPer技术栈;
• 转自链接：https://learnku.com/articles/68200#edce4a