做过 DBA 的朋友应该都有印象，我们在慢日志中经常可以看到有时候明明SQL只查询一条数据，却仍然很慢进而被慢日志所记录。

我们将对应的SQL拿到数据库中去执行时，却又发现没有那么久的延时。这个时候我们一定特别地困惑，像一个情人节告白失败的舔狗一样，大喊道：“明明现在执行得很快，为什么还是会被慢日志所记录呢？”

不用着急，今天我们就来聊聊这个话题。

一、flush 数据

在聊这个话题之前，我们首先回顾一下我们在前面《InnoDB 存储引擎的底层逻辑架构》中介绍的 InnoDB 存储引擎修改数据的流程。

具体步骤如下：

  • 首先判断该条数据是否存在于buffer pool之中。
  • 如果该条数据在buffer pool之中则直接修改；如果不存在，则将修改的逻辑记录到change buffer和redo log之中。
  • 之后按照不同的场景，写入磁盘。

在此之中，有四种不同的情况，分别如下。

  • 情况一：当redo log的空间被脏数据占满时，此时数据库将不得不停止接收任何更新，把redo log中保存的脏数据flush进磁盘。
  • 情况二：当buffer pool的空间被占满时，这个时候就需要按照其淘汰策略淘汰一部分数据，为新数据空出一部分内存空间；如果此时淘汰的数据正好是脏数据，就需要把该部分数据flush进磁盘。
  • 情况三：当MySQL 数据库空闲的时候，如果数据库发现内存中有脏数据时，此时数据库会将脏数据flush进磁盘。
  • 情况四：当MySQL 数据库关闭的时候，如果数据库发现内存中有脏数据时，此时数据库也会将脏数据flush进磁盘。

在如上四种情况中，情况三和情况四是数据库空闲或者数据库关闭时发现的flush，因为此时对于数据库的性能来讲影响不大，所以我们暂且不深入讨论，我们主要来看看情况一和情况二。

在情况一中，最主要的就是redo log空间被占满，此时数据库不得不flush脏数据，对于 InnoDB 存储引擎来说，这一点是需要尽量去避免的。

在情况二中，最主要的就是buffer pool内存被占满，此时不得不为后面的新数据空出一点空间来，空出的这个空间有三种情况，分别是：

  • 空的数据页；
  • 普通数据页；
  • 脏数据页。

如果是前两种情况，对于查询来说影响不大，直接将该数据页中的数据释放掉即可；最主要的是第三种情况（脏数据页），如果遇见了，就必须将脏数据flush进磁盘。

所以，影响查询速度的就是刷脏数据。而根据上方的分析，刷脏数据是 MySQL 数据库的一个常态，我们可以通过调整 innodb_io_capacity 参数的方式来避免。

经过上面的分析，我们可以确定的是：无论是redo log存储空间被占满，还是buffer pool空间被占满，都会发生flush，也就是说无论我们是在查询数据的时候在buffer pool中淘汰脏数据，还是我们在更新数据的时候在redo log中的脏数据刷入磁盘，都可能会导致我们查询数据时很慢。如果我们要控制这种情况，就需要提高 MySQL 数据库的flush的速度。

控制 MySQL 数据库 flush 速度的参数是：innodb_io_capacity。它可以告诉数据库主机磁盘的 IO 能力，如果我们使用了一个 IO 性能很好的磁盘，但是我们将innodb_io_capacity设置得很低，那么 InnoDB 存储引擎会认为这个磁盘的 IO 能力很差，进而导致刷脏页flush很慢，甚至比生成脏数据的速度还慢，这就会导致脏数据堆积，进而导致查询阻塞。

二、锁机制

在前两讲中我们介绍了锁机制，其中我们着重介绍了 MySQL 数据库中的共享锁和排他锁。

当开启两个事务的时候，如果将一条数据同时加两种锁时，就会发生阻塞的情况。具体体现如下：

  • 开启事务一

    mysql> begin; Query OK, 0 rows affected (0.00 sec)

    mysql> update city set fid = 2 where id = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

  • 开启事务二

    mysql> begin; Query OK, 0 rows affected (0.00 sec)

    mysql> select * from city where id = 6 lock in share mode;

此时事务二发生了阻塞，那么我们该如何解决这一问题呢？具体如下：

  1. -- 查询 InnoDB存储引擎中事务之间相互等待锁的信息
  2. mysql> select * from  Information_schema.innodb_lock_waits ;
  3. +-------------------+------------------------+-----------------+------------------+
  4. | requesting_trx_id | requested_lock_id      | blocking_trx_id | blocking_lock_id |
  5. +-------------------+------------------------+-----------------+------------------+
  6. | 422148857952992   | 422148857952992:56:3:6 | 23342           | 23342:56:3:6     |
  7. +-------------------+------------------------+-----------------+------------------+
  8. 1 row in set, 1 warning (0.00 sec)

在上面结果中：

  • requesting_trx_id，申请锁的线程 id；
  • blocking_trx_id，当前持有锁的线程 id。

我们可以根据 blocking_trx_id 查询出对应锁的具体情况。

  1. mysql> SELECT TIMESTAMPDIFF(SECOND,trx_started,NOW()),innodb_trx.* FROM Information_schema.innodb_trx WHERE trx_id='23342'\G
  2. *************************** 1. row ***************************
  3. TIMESTAMPDIFF(SECOND,trx_started,NOW()): 843
  4.                                  trx_id: 23342
  5.                               trx_state: RUNNING
  6.                             trx_started: 2022-04-01 15:43:25
  7.                   trx_requested_lock_id: NULL
  8.                        trx_wait_started: NULL
  9.                              trx_weight: 3
  10.                     trx_mysql_thread_id: 6494
  11.                               trx_query: NULL
  12.                     trx_operation_state: NULL
  13.                       trx_tables_in_use: 0
  14.                       trx_tables_locked: 1
  15.                        trx_lock_structs: 2
  16.                   trx_lock_memory_bytes: 1136
  17.                         trx_rows_locked: 1
  18.                       trx_rows_modified: 1
  19.                 trx_concurrency_tickets: 0
  20.                     trx_isolation_level: REPEATABLE READ
  21.                       trx_unique_checks: 1
  22.                  trx_foreign_key_checks: 1
  23.              trx_last_foreign_key_error: NULL
  24.               trx_adaptive_hash_latched: 0
  25.               trx_adaptive_hash_timeout: 0
  26.                        trx_is_read_only: 0
  27.              trx_autocommit_non_locking: 0
  28. 1 row in set (0.00 sec)

在此之中，我们可以看出持有锁的线程 ID（trx_mysql_thread_id）为：6494。此时我们如果想释放该锁，我们可以执行 kill 6494 来释放该锁。这是因为kill掉这个线程之后，对应的会话也就结束了，事务因为没有提交同时会话结束，会直接回滚事务并释放该数据的锁。

如果我们在一次查询中，遇到锁阻塞的情况同样也会影响查询速度。

三、SQL 查询慢

除了上述两种可能导致数据库查询慢的情况，其实还有一种情况也会拖慢数据库的速度，那就是大量的数据。具体如下。

我们首先创建一个s1数据表，并且在其中创建 30 万条数据：

  1. #1. 准备表
  2. create table s1(
  3.   id int,
  4.   name varchar(20),
  5.   gender char(6),
  6.   email varchar(50)
  7. );
  9. #2. 创建存储过程，实现批量插入记录
  10. delimiter $$ #声明存储过程的结束符号为$$
  11. create procedure auto_insert1()
  12. BEGIN
  13.     declare i int default 1;
  14.     while(i<300000)do
  15.         insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));
  16.         set i=i+1;
  17.         select concat('shanhe',i,'_ok');
  18.     end while;
  19. END$$ #$$结束
  20. delimiter ;
  22. #3. 查看存储过程
  23. show create procedure auto_insert1\G 
  25. #4. 调用存储过程
  26. call auto_insert1();

此时我们来查询id = 1这条数据，结果如下：

  1. mysql> select * from s1 where id = 1;
  2. +------+--------+--------+--------------------+
  3. | id   | name   | gender | email              |
  4. +------+--------+--------+--------------------+
  5. |    1 | shanhe | male   | shanhe1@helloworld |
  6. +------+--------+--------+--------------------+
  7. 1 row in set (0.10 sec)

我们可以看到，本次查询总共花费 0.1 秒。有朋友就会有疑问了，这个时间并不长呀？其实不要忘记了，我们这个数据表中只有 30 万条数据，所以查询一条总共才花费了 0.1 秒的时间，如果这张表的数据增加，这个时间也会随之而增加。具体如下：

  1. mysql> explain select * from s1 where id = 1;
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  5. |  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 300000 |    10.00 | Using where |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
  7. 1 row in set, 1 warning (0.00 sec)

我们可以看到，此次查询总共扫描了 30 万行数据才得出这个结果，所以类似于这种查询我们是需要尽量避免的。

为了对比，我们将id字段增加一条索引。具体如下：

  1. ALTER TABLE `test01`.`s1` 
  2. MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST,
  3. ADD PRIMARY KEY (`id`);

增加完索引之后，我们再来尝试查询，看结果：

  1. mysql> select * from s1 where id = 1;
  2. +----+--------+--------+--------------------+
  3. | id | name   | gender | email              |
  4. +----+--------+--------+--------------------+
  5. |  1 | shanhe | male   | shanhe1@helloworld |
  6. +----+--------+--------+--------------------+
  7. 1 row in set (0.00 sec)

此次查询，我们可以看到只花费了 0.00 秒钟。比上一次查询的结果快了很多。我们再来看看本次查询总共给扫描了多少行，具体如下：

  1. mysql> explain select * from s1 where id = 1;
  2. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  3. | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  5. |  1 | SIMPLE      | s1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
  7. 1 row in set, 1 warning (0.00 sec)

我们可以看到，总共只扫描了 1 行。

所以说，有的情况下，查询慢是因为数据表索引没有利用好。

总结

本章节我们介绍了导致查询慢的几个方面的原因。

  • 原因一是 MySQL 数据库偶发性地需要flush数据，此时因为redo log空间的问题或者是buffer pool空间的问题导致数据库阻塞，进而导致数据库查询偶发性地变慢。
  • 原因二是锁机制导致的问题，如果上一个事务增加了排他锁而其他事务同时也加上了锁，那么就会导致阻塞，此时也会导致数据查询变慢。
  • 原因三是我们需要查询的数据表中的数据过多，同时我们不恰当地使用索引而导致的数据库需要全表扫描，进而导致数据库变慢。

我们在实际应用过程中要尽量避免这些情况的出现。

有朋友可能知道，innodb_io_capacity的默认是 200，但是在实际的数据库应用过程中，如果 MySQL 数据库底层的存储硬盘是机械硬盘，那么我们可以配置成 2000；如果底层的存储硬盘是固态硬盘，我们可以配置成 5000。这样就可以正确地告诉 MySQL 数据库底层存储的性能，进而可以降低 InnoDB 存储引擎因 flush 数据而导致数据变慢。