代码改变世界

单表 delete 操作未能使用索引

2026-11-12 17:22  abce  阅读(7)  评论(0)    收藏  举报

因为底层存储问题,导致一个测试数据库的写操作特别慢,系统很卡。

分析慢日志的时候发现一条 SQL 运行状态有点不对:

# Query_time: 2.909883  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 125437 Thread_id: 1529014 Errno: 0 Killed: 0 Bytes_received: 78 Bytes_sent: 13 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 114948 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2026-11-11T10:34:17.162798+08:00 End: 2026-11-11T10:34:20.072681+08:00
SET timestamp=1794364457;
DELETE FROM myabc WHERE tday = '2026-11-11';

开发说,删除的数据应该只有几千条。但是从日志可以看出,在完成 delete 操作的时候,走的时候使用了全表扫描。因为业务的逻辑是先删除对应的数据,然后再插入新的业务数据。那就先看看表的定义和记录信息。


表的定义:

>show create table myabc\G
*************************** 1. row ***************************
       Table: myabc
Create Table: CREATE TABLE `myabc` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tday` date NOT NULL,
  `btag` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `symbol` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `a_before` decimal(24,6) NOT NULL DEFAULT '0.0',
  `a_after` decimal(24,6) NOT NULL DEFAULT '0.0',
  `a_cut` decimal(24,6) NOT NULL DEFAULT '0.0',
  `ca_ratio` decimal(20,8) DEFAULT NULL,
  `affected_count` bigint NOT NULL DEFAULT '0',
  `lprice` decimal(24,6) NOT NULL DEFAULT '0.0',
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_day_tag_a` (`tday`,`btag`,`a_cut`),
  KEY `idx_day_symbol` (`tday`,`symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=10855530 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

记录信息:

>select count(*) from myabc WHERE tday = '2026-11-11';
+----------+
| count(*) |
+----------+
|     4359 |
+----------+
1 row in set (0.01 sec)

>select count(*) from myabc;
+----------+
| count(*) |
+----------+
|   125437 |
+----------+
1 row in set (0.02 sec)

>

 

根据这些信息,可以看到的确走了全表扫描,没有使用索引。

因为业务逻辑是先删除满足条件的记录,然后再插入新的记录。插入的记录数量变化不会太大,而且隔一段时间就会再次发生。继续查看慢日志,发现里面确实还有好几条删除记录日志,走的都是全表扫描。

查看执行计划,评估出来的执行计划也是走的全表扫描

>EXPLAIN DELETE FROM myabc WHERE tday = '2026-11-11';
+----+-------------+------------------------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table                        | partitions | type | possible_keys                     | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------------------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+
|  1 | DELETE      |              myabc           | NULL       | ALL  | idx_day_tag_a,idx_day_symbol      | NULL | NULL    | NULL | 113343 |   100.00 | Using where |
+----+-------------+------------------------------+------------+------+-----------------------------------+------+---------+------+--------+----------+-------------+

官网有个类似的报告:https://bugs.mysql.com/bug.php?id=109107

现象:range DELETE,SELECT 正常走索引,DELETE 直接 ALL 全表扫描

官方工程师给出的点评:single-table DELETE/UPDATE 不会跑完整套优化器逻辑,单表 DML 优化路径和 SELECT 是两套,这就是根源;官方确认复现,但归类为性能短板,不是数据错误类 bug,不会优先修复。MySQL/Percona 短期内不会改动这个逻辑,这也可能是普遍推荐分批删除、分区表的原因。不过,工程师提到的规避方案:https://dev.mysql.com/blog-archive/multi-table-trick/](https://dev.mysql.com/blog-archive/multi-table-trick/

给出的方案是:改成多表 DELETE 语法 `DELETE t.* FROM t WHERE ...` 或者加 LIMIT。

按照官方的建议,测试了一下,发现可以使用索引了。

>EXPLAIN DELETE m.* FROM myabc m WHERE tday = '2026-11-11';
+----+-------------+-------+------------+------+-----------------------------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                     | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------------------------+----------------+---------+-------+------+----------+-------+
|  1 | DELETE      | m     | NULL       | ref  | idx_day_tag_a,idx_day_symbol      | idx_day_symbol | 3       | const | 8426 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------------------------+----------------+---------+-------+------+----------+-------+


今日尝试继续跟踪这个问题的时候,发现情况有了变化。因为存储问题已经解决,所有虚拟机运行已经恢复正常。出现问题的实例昨日最终也发生了OOM后重启。

今日资源使用正常的情况下,再次执行原先的 delete 操作,在慢日志中已经看不到对应的语句,使用 explain 命令查看,语句使用了索引了。感觉以上问题,在内存不足的环境更容易触发。既然存储已经修复,系统都运行正常了,不再继续跟踪了。