做过 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;
此时事务二发生了阻塞，那么我们该如何解决这一问题呢？具体如下：
-- 查询 InnoDB存储引擎中事务之间相互等待锁的信息
mysql> select * from Information_schema.innodb_lock_waits ;
+-------------------+------------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+------------------------+-----------------+------------------+
| 422148857952992 | 422148857952992:56:3:6 | 23342 | 23342:56:3:6 |
+-------------------+------------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
在上面结果中：
- requesting_trx_id，申请锁的线程 id；
- blocking_trx_id，当前持有锁的线程 id。
我们可以根据 blocking_trx_id 查询出对应锁的具体情况。
mysql> SELECT TIMESTAMPDIFF(SECOND,trx_started,NOW()),innodb_trx.* FROM Information_schema.innodb_trx WHERE trx_id='23342'\G
*************************** 1. row ***************************
TIMESTAMPDIFF(SECOND,trx_started,NOW()): 843
trx_id: 23342
trx_state: RUNNING
trx_started: 2022-04-01 15:43:25
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 6494
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
在此之中，我们可以看出持有锁的线程 ID（trx_mysql_thread_id）为：6494。此时我们如果想释放该锁，我们可以执行 kill 6494 来释放该锁。这是因为kill掉这个线程之后，对应的会话也就结束了，事务因为没有提交同时会话结束，会直接回滚事务并释放该数据的锁。
如果我们在一次查询中，遇到锁阻塞的情况同样也会影响查询速度。
三、SQL 查询慢
除了上述两种可能导致数据库查询慢的情况，其实还有一种情况也会拖慢数据库的速度，那就是大量的数据。具体如下。
我们首先创建一个s1数据表，并且在其中创建 30 万条数据：
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<300000)do
insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));
set i=i+1;
select concat('shanhe',i,'_ok');
end while;
END$$ #$$结束
delimiter ;
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
此时我们来查询id = 1这条数据，结果如下：
mysql> select * from s1 where id = 1;
+------+--------+--------+--------------------+
| id | name | gender | email |
+------+--------+--------+--------------------+
| 1 | shanhe | male | shanhe1@helloworld |
+------+--------+--------+--------------------+
1 row in set (0.10 sec)
我们可以看到，本次查询总共花费 0.1 秒。有朋友就会有疑问了，这个时间并不长呀？其实不要忘记了，我们这个数据表中只有 30 万条数据，所以查询一条总共才花费了 0.1 秒的时间，如果这张表的数据增加，这个时间也会随之而增加。具体如下：
mysql> explain select * from s1 where id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 300000 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
我们可以看到，此次查询总共扫描了 30 万行数据才得出这个结果，所以类似于这种查询我们是需要尽量避免的。
为了对比，我们将id字段增加一条索引。具体如下：
ALTER TABLE `test01`.`s1`
MODIFY COLUMN `id` int(11) NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`id`);
增加完索引之后，我们再来尝试查询，看结果：
mysql> select * from s1 where id = 1;
+----+--------+--------+--------------------+
| id | name | gender | email |
+----+--------+--------+--------------------+
| 1 | shanhe | male | shanhe1@helloworld |
+----+--------+--------+--------------------+
1 row in set (0.00 sec)
此次查询，我们可以看到只花费了 0.00 秒钟。比上一次查询的结果快了很多。我们再来看看本次查询总共给扫描了多少行，具体如下：
复制mysql> explain select * from s1 where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
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 数据而导致数据变慢。
