MySQL:几个奇怪state和主从延迟问题（特例）

首先我们来看一个提交小BUG

• 8.0.31 版本慢查询不准的情况（BUG待确认） https://bugs.mysql.com/bug.php?id=109034

当前已经确认，官方定义为一个小BUG，查看慢查询可能需要注意一下。

一、几个奇怪state

这里的state就是我们show processlist出来看到的值，当前来看这个值有点类似Oracle的等待事件，但是实际上它是一个stage，也就是代码跑到某处时会置为一个合适的值，可以作为判定问题的一个初始化方向。

1、preparing和statistics状态的特例

先来看下面的state的值，我们可以看到：

我们可以发现里面有一个delete in语句处于preparing状态下面，其他两个select for update语句处于statistics状态下面，这个时候如果根据常规判定，preparing和statistics状态都处于语句执行计划生成阶段，语句还没有开始执行，如果按照这样去判断就出现了误判。

版本8.0.28，隔离级别RC，实际上在8.0.28触发这个问题已经很难了，需要设置优化选项，关闭半连接和物化，但是在8.0.19及一下环境很容易重现，因为DML使用半连接优化是8.0.21的特性，另外对于RC模式下面delete in select 加S锁的情况也有优化，会提前调用lock_rec_unlock解锁，老版本不会解锁。准备环境如下：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

mysql> show variables like '%transaction_isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
mysql> create table testout(id int auto_increment unique key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into testout(name) values('g');
Query OK, 1 row affected (0.01 sec)

mysql> insert into testout(name) select name from testout;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
...

mysql> insert into testout(name) select name from testout;
Query OK, 1048576 rows affected (8.38 sec)
Records: 1048576  Duplicates: 0  Warnings: 0


mysql> create table testin like testout;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into testin select * from testout;
Query OK, 2097152 rows affected (17.42 sec)
Records: 2097152  Duplicates: 0  Warnings: 0

mysql>  set  optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0.00 sec)

mysql> set  optimizer_switch='materialization=off';
Query OK, 0 rows affected (0.00 sec)

mysql> desc delete from testout where name in ( select name from testin );
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
| id | select_type        | table   | partitions | type           | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                           |
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
|  1 | DELETE             | testout | NULL       | ALL            | NULL          | NULL | NULL    | NULL | 3602700 |   100.00 | Using where                                     |
|  2 | DEPENDENT SUBQUERY | testin  | NULL       | index_subquery | name          | name | 83      | func | 8372870 |   100.00 | Using where; Using index; Full scan on NULL key |
+----+--------------------+---------+------------+----------------+---------------+------+---------+------+---------+----------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

接下来，我们跑如下流程，因为数据量不大，需要稍微快一点执行。

TRX1	TRX2	TRX3
begin
delete from testout where id not in ( select id from testin );(本语句执行10秒左右)
	select * from testin where id=1 for update;（堵塞）
		select * from testin where id=2 for update;（堵塞）

这样就出现了上面的情况，实际上delete not in 语句和select for update语句都是在执行状态如下：

• preparing：这里可以参考https://blog.csdn.net/weixin_54828627/article/details/129235538?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22129235538%22%2C%22source%22%3A%22weixin_54828627%22%7D
• statistics：对于唯一索引等值查询（const常量），在statistics状态下就已经完成了查询操作，这是一个比较特殊的例子，记住即可。

实际上这里就是由于delete not in 对子查询的数据加了S锁，然后堵塞了select for update语句，就是row lock 堵塞，但是这里的状态有一定的迷惑性，常规的堵塞应该是updating/insert/executing/sending data 等，需要特别注意。

2、AHI维护的特例

当进行DDL的时候经常会触发AHI维护，如果本表中的数据大量的生成AHI，则可能导致DDL异常缓慢，AHI本质上是指向行记录的HASH结构，包含如下：

在这个简图中，AHI查找表被根据参数innodb_adaptive_hash_index_parts参数分为了0-7 8个子表定义为hash_table_t,每个hash_table_t子表包含一个hash结构其中hash_cell_t就是一个hash桶（cell）。每个hash桶（cell）包含了一个ha_node_t指针的链表，每个元素都是一个ha_node_t指针类型，代表一行数据，这就是在show engine中看到的（Hash table size 1106407）中的size的单位。每行数据包含了3个元素，其中key为fold，value为rec_t* data，fold为每行数据算出来的hash值，data为实际指向记录在innodb buffer中的地址，另外一个元素ha_node_t* next则为链表结构的指针。
除此之外在每个子表hash_table_t中还包含了n_cells和heap内存信息，其中n_cells为hash桶（cell）的个数，heap内存信息为ha_node_t结构的实际存储的位置，分配内存的时候以buffer为单位每个buffer 16K，也就是我们在show engine中看到的（node heap has 0 buffer(s)）中的buffer。
当构建某行数据的AHI的信息的时候，首先通过这行数据所在的space_id和index_no等信息算一个hash值，然后根据innodb_adaptive_hash_index_parts参数取余，得到hash_table_t子表的位置。然后通过本行数据每个字段的值计算出一个hash值叫做fold，继而根据fold和n_cells取余计算出在哪个hash桶（cell）里面，最后加入到hash桶（cell）的链表中。

实际上每个hash结构元素就是24个字节（1个long类型，2个指针）。当DDL进行的时候会大量的清理本表相关的AHI，如下：

伪代码如下：
循环每个分区调用函数
->btr_drop_ahi_for_table
  循环表（或者分区）中的每个索引，如果索引都没有用到AHI，
则退出
  循环innodb buffer中的每个实例，根据LRU链表循环每个page
如果page建立了AHI信息，且是要删除表（或者分区）的相关索引
  则放入drop vector容器中
如果page没有建立AHI信息 
则跳过
如果drop verctor容器中填满1000个page
则清理一次，循环每个page，调用函数
->btr_search_drop_page_hash_index
  计算page所在AHI结构的slot信息，以便找到对应的hash_table_t结构
  循环page中所有的行
    循环行中访问到的索引字段（访问模式），计算出fold信息填入到fold[]数组中
    本循环中会通过函数rec_get_offsets进行字段偏移量的获取，为耗用CPU的函数
      循环fold[]数组，一个fold代表一行数据,调用函数
       ->ha_remove_all_nodes_to_page,为耗用CPU的函数
         ->ha_chain_get_fist
           根据fold信息找到hash结构的cell
         循环本cell中的链表信息
           如果行的地址在本要删除的page上，调用函数
           ->ha_delete_hash_node，为消耗CPU的函数
             进行链表和hash结构的维护
每次处理完1000个page后，yeild线程主动放弃CPU，避免长期占用CPU，醒来后继续处理

这种时间复杂度极高。

这里描述2个特殊的案例，

• 分区表增加和删除分区，AHI维护的state

   

  

• 普通表增加索引，AHI维护的state

这里我们看到状态是committing alter table to storage engine和waiting for handler commit，实际上它们都是在DDL快要结束的时候进行的，这也是特殊的例子。

3、关于本类问题的方法论

这类问题，所幸的是语句都在执行，那么他们耗用的CPU很高比如：

那么我们可以通过抓取perf信息来分析，对于paratition操作的AHI维护的火焰图如下：

 

实际上这已经很明显了，不过多描述。

二、主从延迟瞬间跳动问题

这个问题其实很简单，大概为有1主2从，其中1个从库的延迟，一下涨到1W多秒然后又恢复，循环往复，而另外一个从库确没什么问题。
那么简单分析，既然另外一个从库没有问题，且分析postition和gtid后发现没什么延迟，那么最大的可能就是这个问题从库的时间做过修改，因为延迟的计算很简单如下：

这个也算老生常谈吧，因为主从服务器之间的差是在启动从库的时候记录到内存的，以后就不会更改，如果修改了服务器时间它是不知道的。

而延迟降为0，那么说明应用完了嘛，因为主从延迟的伪代码如下：

一旦没有event，直接print 0。。