在我们的生产环境中有一张表:courier_consume_fail_message,是存放消息消费失败的数据的,设计之初,这张表的数据量评估在万级别以下,因此没有建立索引。
但目前发现,该表的数据量已经达到百万级别,原因产生了大量的重试消费,这导致了该表的慢查询。
因此需要清理该表数据。而实际上,使用 DELETE 命令删除数据后,我们发现查询速度并没有显著提高,甚至可能会降低。为什么?
因为 DELETE 命令只是标记该行数据为“已删除”状态,并不会立即释放该行数据在磁盘中所占用的存储空间,这样就会导致数据文件中存在大量的碎片,从而影响查询性能。所以,除了删除表记录外,还需要清理磁盘碎片。
在表碎片清理前,我们关注以下四个指标。
- 指标一:表的状态:SHOW TABLE STATUS LIKE 'courier_consume_fail_message';
- 指标二:表的实际行数:SELECT count(*) FROM courier_consume_fail_message;
- 指标三:要清理的行数:SELECT count(*) FROM courier_consume_fail_message where created_at < '2023-04-19 00:00:00';
- 指标四:表查询的执行计划:EXPLAIN SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq';
sql复制代码 -- 清理磁盘碎片 OPTIMIZE TABLE courier_consume_fail_message;
以下是清理前后的指标对比。
一、清理前
指标一,表的状态:
指标二,表的实际行数:76986
指标三,要清理的行数:76813
指标四,表查询的执行计划:
二、清理数据
下面是执行 DELETE FROM courier_consume_fail_message WHERE created_at < '2023-04-19 00:00:00'; 后的统计。
指标一,表的状态:
指标二,表的实际行数:173
指标三,要清理的行数:0
指标四,表查询的执行计划:
通过指标四可以看到,清理表记录后,查询扫描的行数依然没变:8651048。
三、清理碎片
下面是执行 OPTIMIZE TABLE courier_consume_fail_message; 后的统计。
指标一,表的状态:
指标四,表查询的执行计划:
通过指标四可以看到,清理表记录后,查询扫描的行数变成了 100。
小结
可以看到,该表的数据行数和数据长度都被清理了,查询语句扫描的行数也减少了。
为了提升 SELECT * FROM courier_consume_fail_message WHERE service='courier-transfer-mq'; 语句的查询效率,还是应当建立索引。
perl复制代码 alter` `table` `ec_courier.courier_consume_fail_message ``add` `index` `idx_service(service);
来源地址:https://blog.csdn.net/weixin_64403990/article/details/131697592