【MYSQL】MYSQL 的学习教程（八）之 12 种慢 SQL 查询原因

迷南。 2024-02-05 18:59 51阅读 0赞

日常开发中，我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢？今天就跟大家聊聊导致 MySQL 慢查询的 12 个常见原因，以及对应的解决方法：

*  SQL 没加索引
 *  SQL 索引失效
 *  limit 深分页问题
 *  单表数据量太大
 *  join 或者子查询过多
 *  in元素过多
 *  数据库在刷脏页
 *  order by 文件排序
 *  拿不到锁
 *  delete + in 子查询不走索引
 *  group by 使用临时表
 *  系统硬件或网络资源

## 1. SQL 没加索引 ##

很多时候，我们的慢查询，都是因为没有加索引。如果没有加索引的话，会**导致全表扫描**的。因此，应考虑在 where 的条件列，建立索引，尽量避免全表扫描

## 2. SQL 索引失效 ##

[【MYSQL】MYSQL 的学习教程（四）之索引失效场景][MYSQL_MYSQL]

有时候我们明明加了索引了，但是索引却不生效。在哪些场景，索引会不生效呢？主要有以下十大经典场景：

1.  查询条件包含 or，可能导致索引失效
2.  隐式的类型转换，索引失效
3.  like 通配符 “%” 在关键词前面导致索引失效
4.  在索引列上使用 MYSQL 的内置函数，索引失效
5.  对索引列运算（如，+、-、\*、/），索引失效
6.  索引字段上使用负向查询（NOT、!=、<>、NOT IN、NOT LIKE）时，可能会导致索引失效
7.  索引字段可以为 null，使用 is null， is not null，可能导致索引失效
8.  违反了索引的最左匹配原则（联合索引，查询时的条件列不是联合索引中的第一个列，索引失效）
9.  左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
10. MYSQL 估计使用全表扫描要比使用索引快,则不使用索引

## 3. limit 深分页问题 ##

### 3.1 limit 深分页为什么会变慢 ###

limit深分页为什么会导致 SQL 变慢呢？假设我们有表结构如下：

CREATE TABLE account (
      id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
      name varchar(255) DEFAULT NULL COMMENT '账户名',
      balance int(11) DEFAULT NULL COMMENT '余额',
      create_time datetime NOT NULL COMMENT '创建时间',
      update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (id),
      KEY idx_name (name),
      KEY idx_create_time (create_time) //索引
    ) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

执行如下 SQL：

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

这个SQL的执行流程：

1.  通过普通二级索引树 `idx_create_time`，过滤 `create_time` 条件，找到满足条件的主键 id
2.  通过主键 id，回到 id 主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）
3.  扫描满足条件的 100010 行，然后扔掉前 100000 行，返回结果

如下图：

![在这里插入图片描述][2b07132cee144a17bd037dd8ab542e68.png]

limit 深分页，导致 SQL 变慢原因有两个：

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

### 3.2 如何优化深分页问题 ###

我们可以通过减少回表次数来优化。一般有：**标签记录法、延迟关联法**

#### 3.2.1 标签记录法 ####

标签记录法：标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描

假设上一次记录到 100000，则 SQL 可以修改为：

select  id,name,balance FROM account where id > 100000 limit 10;

这样的话，后面无论翻多少页，性能都会不错的，因为命中了 id 主键索引。但是这种方式有局限性：需要一种类似连续自增的字段

#### 3.2.2 延迟关联法 ####

延迟关联法：把条件转移到主键索引树，然后减少回表

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;

优化思路：先通过 `idx_create_time` 二级索引树查询到满足条件的主键 ID，再与原表通过主键 ID 内连接，这样后面直**接走了主键索引了，同时也减少了回表**

## 4. 单表数据量太大 ##

### 4.1 单表数据量太大为什么会变慢？ ###

一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的 `B+` 树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢

### 4.2 一棵 B+ 树可以存多少数据量 ###

[【MYSQL】MYSQL 的学习教程（五）之 MySQL 索引底层：B+ 树详解][MYSQL_MYSQL _ MySQL _B_]  
一棵高度为 3 的 B+ 树，能存放 `1170 *1170 *16 =21902400`，也就是说，可以存放两千万左右的记录。B+ 树高度一般为 1-3 层，已经满足千万级别的数据存储

如果 B+ 树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢

### 4.3 如何解决单表数据量太大，查询变慢的问题 ###

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

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

*  事务问题
 *  跨库问题
 *  排序问题
 *  分页问题
 *  分布式ID

在评估是否分库分表前，先考虑下是否可以把部**分历史数据归档**，如果可以的话，先不要急着分库分表。如果真的要分库分表，综合考虑和评估方案。比如可以考虑垂直、水平分库分表。水平分库分表策略的话，range范围、hash取模、range+hash取模混合等等

## 5. join 或者子查询过多 ##

一般来说，不建议使用子查询，可以把子查询改成 join 来优化

而数据库有个规范约定就是：**尽量不要有超过3个以上的表连接**

MySQL中，join的执行算法，分别是：`Index Nested-Loop Join`、`Block Nested-Loop Join`

*  `Index Nested-Loop Join（索引嵌套循环连接）`：这个 join 算法，跟我们写程序时的嵌套查询类似，并且可以用上**被驱动表的索引**
 *  `Block Nested-Loop Join（缓存块嵌套循环连接）`：**被驱动表上没有可用的索引**，它会先把驱动表的数据读入线程内存join\_buffer 中，再扫描被驱动表，把被驱动表的每一行取出来，跟 join\_buffer 中的数据做对比，满足join条件的，作为结果集的一部分返回

join 过多的问题：

1.  过多的表连接，会大大增加 SQL 复杂度
2.  如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在 join\_buffer 内存做的，如果匹配的数据量比较小或者 join\_buffer 设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql 会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联

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

## 6. in元素过多 ##

如果使用了 in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。**in 元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行哈**

如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑。如下这种子查询：

select * from user where user_id in (select author_id from artilce where type = 1);

正例是，分批进行，每批500个：

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

## 7. 数据库在刷脏页 ##

### 7.1 什么是脏页 ###

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

### 7.2 一条更新语句是如何执行的？ ###

以下的这个更新SQL，如何执行的呢？

update t set c=c+1 where id=666;

1.  对于这条更新SQL，执行器会先找引擎取 id = 666 这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回
2.  执行器拿到引擎给的行数据后，给这一行 C 的值加一，得到新的一行数据，再调用引擎接口写入这行新数据
3.  引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，但是此时 redo log 是处于 prepare 状态的哈
4.  执行器生成这个操作的 binlog，并把 binlog 写入磁盘
5.  执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

如下图：

![在这里插入图片描述][da464032186e4b239e6deee5f8e1685f.png]

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志）。平时更新 SQL 执行得很快，其实是因为它只是在写内存和 redo log 日志，等到空闲的时候，才把 redo log 日志里的数据同步到磁盘中

> redo log 日志不是在磁盘嘛？那为什么不慢？其实是因为写 redo log 的过程是顺序写磁盘的。**磁盘顺序**写会减少寻道等待时间，速度比随机写要快很多的。

### 7.3 为什么会出现脏页呢？ ###

更新 SQL 只是在写内存和 redo log 日志，等到空闲的时候，才把 redo log 日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致，就出现**脏页**

### 7.4 什么时候会刷脏页（flush）？ ###

InnoDB 存储引擎的 redo log 大小是固定，且是环型写入的，如下图：

![在这里插入图片描述][ca263e0854ba4d71955117f0c3458b3d.png]

那什么时候会刷脏页？有几种场景：

1.  redo log 写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住
2.  内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页

> InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用

1.  MySQL 认为系统空闲的时候，也会刷一些脏页
2.  MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上

### 7.5 为什么刷脏页会导致 SQL 变慢呢？ ###

1.  redo log 写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
2.  一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长

## 8. order by 文件排序 ##

order by 就一定会导致慢查询吗？不是这样的哈，因为 order by 平时用得多，并且数据量一上来，还是走**文件排序**的话，很容易有慢SQL的

### 8.1 order by 的 Using filesort 文件排序 ###

平时经常需要用到 order by ，主要就是用来给某些字段排序的。比如以下 SQL:

select name,age,city from staff where city = '深圳' order by age limit 10;

查看 explain 执行计划的时候，可以看到 Extra 这一列，有一个 Using filesort，它表示用到**文件排序**

### 8.2 order by文件排序效率为什么较低 ###

order by 用到文件排序时，为什么查询效率会相对低呢？

![在这里插入图片描述][e616805b966641eb8a30a4b727d2fea8.png]

order by 排序，分为全字段排序、rowid排序。它是拿 `max_length_for_sort_data` 和结果行数据长度对比，如果结果行数据长度超过 `max_length_for_sort_data` 这个值，就会走 **rowid 排序**，相反，则走**全字段排序**

#### 8.2.1 rowid 排序 ####

rowid 排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个 SQL，使用 rowid 排序，执行过程是这样：

select name,age,city from staff where city = '深圳' order by age limit 10;

1.  MySQL 为对应的线程初始化 `sort_buffer`，放入需要排序的 age 字段，以及主键 id
2.  从索引树 idx\_city， 找到第一个满足 city='深圳’ 条件的主键 id，也就是图中的 id=9
3.  到`主键 id索引树`拿到 id=9 的这一行数据， 取 age 和主键 id 的值，存到 sort\_buffer
4.  从`索引树 idx_city` 拿到下一个记录的主键 id，即图中的 id=13
5.  重复步骤 3、4 直到 city 的值不等于深圳为止
6.  前面 5 步已经查找到了所有 city 为深圳的数据，在 sort\_buffer 中，将所有数据根据 age 进行排序
7.  遍历排序结果，取前 10 行，并按照 id 的值回到原表中，取出 city、name 和 age 三个字段返回给客户端

![在这里插入图片描述][444d318b65c349ea940973b395343bf3.png]

#### 8.2.2 全字段排序 ####

同样的 SQL，如果是走全字段排序是这样的：

1.  MySQL 为对应的线程初始化 sort\_buffer，放入需要查询的 name、age、city 字段
2.  从`索引树 idx_city`， 找到第一个满足 city='深圳’ 条件的主键 id，也就是图中的 id=9
3.  到 `主键id索引树` 拿到 id=9 的这一行数据， 取 name、age、city 三个字段的值，存到 sort\_buffer
4.  从 `索引树idx_city` 拿到下一个记录的主键 id，即图中的 id=13
5.  重复步骤 3、4 直到 city 的值不等于深圳为止
6.  前面 5 步已经查找到了所有 city 为深圳的数据，在 sort\_buffer 中，将所有数据根据 age 进行排序
7.  按照排序结果取前 10 行返回给客户端

![在这里插入图片描述][a781fa1db95a4463848c47ea83074fa6.png]

`sort_buffer` 的大小是由一个参数控制的：`sort_buffer_size`

*  如果要排序的数据小于 sort\_buffer\_size，排序在 **sort\_buffer 内存**中完成
 *  如果要排序的数据大于 sort\_buffer\_size，则借助**磁盘文件**来进行排序

> 借助磁盘文件排序的话，**效率就更慢一点**。因为先把数据放入 sort\_buffer，当快要满时。会排一下序，然后把 sort\_buffer 中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件

### 8.3 如何优化 order by 的文件排序 ###

order by 使用**文件排序**，效率会低一点。我们怎么优化呢

*  因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化 order by 语句
 *  可以通过调整 `max_length_for_sort_data`、`sort_buffer_size` 等参数优化

## 9. 拿不到锁 ##

有时候，我们查询一条很简单的 SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是**表被锁住了，或者要查询的某一行或者几行被锁住了**。我们只能慢慢等待锁被释放

这时候，我们可以用 `show processlist` 命令，看看当前语句处于什么状态哈

## 10. delete + in 子查询不走索引 ##

当 delete 遇到 in 子查询时，即使有索引，也是不走索引的。而对应的 select + in 子查询，却可以走索引

实际执行的时候，MySQL 对 select in 子查询做了优化，把子查询改成 join 的方式，所以可以走索引。但是很遗憾，对于 delete in 子查询，MySQL 却没有对它做这个优化

## 11. group by 使用临时表 ##

group by 一般用于分组统计，它表达的逻辑就是**根据一定的规则，进行分组**

假设有表结构：

CREATE TABLE `staff` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `id_card` varchar(20) NOT NULL COMMENT '身份证号码',
      `name` varchar(64) NOT NULL COMMENT '姓名',
      `age` int(4) NOT NULL COMMENT '年龄',
      `city` varchar(64) NOT NULL COMMENT '城市',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';

我们查看一下这个SQL的执行计划：

explain select city ,count(*) as num from staff group by city;

![在这里插入图片描述][69013f7e3431467282046bb84b1ee123.png]

*  Extra 这个字段的 `Using temporary` 表示在执行分组的时候使用了临时表
 *  Extra 这个字段的 `Using filesort` 表示使用了文件排序

group by 是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程：

1.  创建内存临时表，表里有两个字段 city 和 num；
2.  全表扫描 staff 的记录，依次取出 city = ‘X’ 的记录
    
     *  判断临时表中是否有为 city='X '的行，没有就插入一个记录 (X,1);
     *  如果临时表中有 city=‘X’ 的行，就将X这一行的 num 值加 1；
3.  遍历完成后，再根据字段 city 做排序，得到结果集返回给客户端

这个流程的执行图如下：

![在这里插入图片描述][6589cd04ffea467485a63759418a5a7f.png]

> 临时表的排序是怎样的呢？

就是把需要排序的字段，放到 sort buffer，排完就返回。在这里注意一点哈，**排序分全字段排序和 rowid 排序**

*  如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
 *  如果是 rowid 排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回

### 11.2 group by 可能会慢在哪里？ ###

因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表

*  如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是`tmp_table_size`），会把内存临时表转成磁盘临时表。
 *  如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间

### 11.3 如何优化 group by 呢？ ###

从哪些方向去优化呢？

*  方向1：既然它默认会排序，我们不给它排是不是就行啦。
 *  方向2：既然临时表是影响 group by 性能的因素，我们是不是可以不用临时表？

> 执行 group by 语句为什么需要临时表呢？group by 的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?

可以有这些优化方案：

1.  group by 后面的字段加索引
2.  order by null 不用排序
3.  尽量只使用内存临时表
4.  使用 `SQL_BIG_RESULT`（直接用磁盘临时表。不需要从内存临时表转到磁盘临时表，这个过程很耗时）

## 12. 系统硬件或网络资源 ##

*  如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡，你可以加个内存条什么的一个道理
 *  如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器CPU占用很高或者IO利用率很高，这种情况下所有语句的执行都有可能变慢的哈

[MYSQL_MYSQL]: https://blog.csdn.net/sco5282/article/details/135081370?spm=1001.2014.3001.5501
[2b07132cee144a17bd037dd8ab542e68.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bf2a36853b6c405facbfd89d9d164af5.png
[MYSQL_MYSQL _ MySQL _B_]: https://blog.csdn.net/sco5282/article/details/135128616?spm=1001.2014.3001.5501
[da464032186e4b239e6deee5f8e1685f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/a2331de5c7654d6f949ed5dbdcc31189.png
[ca263e0854ba4d71955117f0c3458b3d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/26f1d201b2604187b58c805726d6abd0.png
[e616805b966641eb8a30a4b727d2fea8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0ed5294aae95485e83da36d31e1a6211.png
[444d318b65c349ea940973b395343bf3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3c99b9da8dde41789eb965d21bb5f8c4.png
[a781fa1db95a4463848c47ea83074fa6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/19cc8008c97d4e878561d62d86670266.png
[69013f7e3431467282046bb84b1ee123.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/abdc8cac5ef245a182408de7e541bb20.png
[6589cd04ffea467485a63759418a5a7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b593781ed8fa417aabb16f99c6eea7c8.png