大表场景下MariaDB DELETE查询的性能优化技术问询

环境与表结构 #

Linux环境下使用MariaDB 10.11，维护名为trades的股票行情交易数据表，每秒约有40次插入、40次删除操作。表结构如下：

CREATE TABLE `trades` (
  `instrument` smallint(5) unsigned NOT NULL DEFAULT 0,
  `duration` int(10) unsigned NOT NULL DEFAULT 0,
  `create_ts` int(10) unsigned NOT NULL DEFAULT 0,
  `amount` decimal(64,30) NOT NULL DEFAULT 0.000000000000000000000000000000,
  `price` decimal(64,30) NOT NULL DEFAULT 0.000000000000000000000000000000,
  KEY `duration` (`duration`,`create_ts`),
  KEY `instrument_2` (`instrument`,`duration`,`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

duration字段为秒级行情周期（如31557600代表一年、60代表一分钟），每笔新交易会按不同周期生成多条插入记录。

删除操作性能异常 #

每秒执行一次DELETE查询清理超出对应周期窗口的create_ts记录，语句如下：

DELETE FROM trades WHERE
    (duration = 60 && create_ts < 1718102199) ||
    (duration = 86400 && create_ts < 1718015859) ||
    (duration = 2628288 && create_ts < 1715473971) ||
    (duration = 31557600 && create_ts < 1686544659)
LIMIT 10;

当表中数据约3000万行时，该DELETE查询耗时约0.015秒，而相同WHERE条件的SELECT仅需0.001秒。即使使用单条件DELETE，耗时仍为0.015秒，对应SELECT仅0.001秒：
单条件DELETE语句：

DELETE FROM trades WHERE duration = 31557600 && create_ts < 1687170076 LIMIT 10;

对应SELECT语句：

SELECT instrument FROM trades WHERE duration = 31557600 && create_ts < 1687170076 LIMIT 10;

EXPLAIN结果对比 #

SIMPLE SELECT的EXPLAIN输出：

+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-----------------------+
| id   | select_type | table | type  | possible_keys      | key      | key_len | ref  | rows   | Extra                 |
+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-----------------------+
|    1 | SIMPLE      | tmp   | range | duration,create_ts | duration | 8       | NULL | 502248 | Using index condition |
+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-----------------------+

SIMPLE DELETE的EXPLAIN输出：

+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-------------+
| id   | select_type | table | type  | possible_keys      | key      | key_len | ref  | rows   | Extra       |
+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-------------+
|    1 | SIMPLE      | tmp   | range | duration,create_ts | duration | 8       | NULL | 502248 | Using where |
+------+-------------+-------+-------+--------------------+----------+---------+------+--------+-------------+

即使调整create_ts使EXPLAIN的rows值降至约1000，删除10行仍需约0.008秒，性能不符合预期。

约束与目标 #

• 必须保留instrument_2索引
• 最终目标支撑约4亿行数据
• 已尝试OPTIMIZE TABLE，未带来明显性能提升

优化方案请求 #

恳请针对上述场景提供有效的性能优化方案。

内容的提问来源于stack exchange，提问作者Segolene