delete大表数据后，再查询同一张表时出现慢SQL

场景描述

一次性删除多条宽列数据（每条记录数据长度在1GB左右），再次对同一张表进行增删改查时均执行缓慢，20分钟左右后恢复正常。

场景案例

1. 假定max_allowed_packet参数大小为1073741824。
2. 创建表。

   CREATE TABLE IF NOT EXISTS zstest1 
   ( 
   id int  PRIMARY KEY not null, 
   c_longtext LONGTEXT 
   ); 

3. 向表中插入数据。

   insert into zstest1 values(1, repeat('a', 1073741800)); 
   insert into zstest1 values(2, repeat('a', 1073741800)); 
   insert into zstest1 values(3, repeat('a', 1073741800)); 
   insert into zstest1 values(4, repeat('a', 1073741800)); 
   insert into zstest1 values(5, repeat('a', 1073741800)); 
   insert into zstest1 values(6, repeat('a', 1073741800)); 
   insert into zstest1 values(7, repeat('a', 1073741800)); 
   insert into zstest1 values(8, repeat('a', 1073741800)); 
   insert into zstest1 values(9, repeat('a', 1073741800)); 
   insert into zstest1 values(10, repeat('a', 1073741800)); 

4. 删除数据。

   delete from zstest1; 

5. 执行查询语句。

   select id from zstest1;    //执行缓慢

原因分析

执行完delete操作后，后台purge线程会去清理标记为delete mark的记录。由于当前删除的数据量较大，purge遍历释放page的过程中会去获取page所在索引根节点的SX锁，导致select语句无法获取到根节点page的rw-lock，一直在等待。

解决方案

• 该场景为正常现象，等待purge操作完成后即可恢复正常。
• 扩大实例规格，提高purge效率。
• 调整优化业务，避免突然删除大量数据。如果需要删除表中所有数据，建议使用truncate table。