likes
comments
collection

为什么有时候即使查询一条数据也很慢呢?

作者站长头像
站长
· 阅读数 16

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

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

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

一、flush 数据

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

具体步骤如下:

  • 首先判断该条数据是否存在于buffer pool之中。
  • 如果该条数据在buffer pool之中则直接修改;如果不存在,则将修改的逻辑记录到change bufferredo 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 数据而导致数据变慢。