本文共 2299 字，大约阅读时间需要 7 分钟。
在存储过程中清理数据时，往往会引起全表扫，如果表内数据非常大，清理效率会很低。以下将讲解如何在存储过程中合理利用索引清理数据。
以下是用于测试的表结构：
CREATE TABLE `test`.`procedure_test` ( `pk` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `data_time` varchar(100) DEFAULT NULL COMMENT '数据时间，格式：2020-07-21 23:00:36', `altitude` double DEFAULT NULL COMMENT '海拔高度 (单位米)', `lat` double DEFAULT NULL COMMENT '纬度', `lon` double DEFAULT NULL COMMENT '经度', `derection` bigint(20) DEFAULT NULL COMMENT '方向 0-359 单位° ；正北为0，顺时针', `statecode` varchar(100) DEFAULT NULL COMMENT '状态信息', PRIMARY KEY (`pk`), KEY `idx_data_time` (`data_time`)) COMMENT='存储过程清理数据测试表';
以下是一个常见的错误示范：
-- 清理3天前的数据CREATE DEFINER=`my_admin`@`%` PROCEDURE `test`.`p_procedure_test_delete_3day_ago`()BEGINdelete from test.procedure_test where data_time < (CURRENT_TIMESTAMP() + interval - 3 day);END
以下是一个常见的错误解法：
-- 清理3天前的数据CREATE DEFINER=`my_admin`@`%` PROCEDURE `test`.`p_procedure_test_delete_3day_ago`()BEGINDECLARE before_dt datetime;select (CURRENT_TIMESTAMP() + interval - 3 day) into before_dt;delete from test.procedure_test where data_time < before_dt;END
以下是一个正确的解决方案：
-- 清理3天前的数据CREATE DEFINER=`my_admin`@`%` PROCEDURE `test`.`p_procedure_test_delete_3day_ago`()BEGINDECLARE before_dt datetime;DECLARE delete_sql varchar(1024);select (CURRENT_TIMESTAMP() + interval - 3 day) into before_dt;set delete_sql = CONCAT("delete from test.procedure_test where data_time < '",before_dt,"'");set @dlt = delete_sql;prepare dlt from @dlt;execute dlt;deallocate prepare dlt;END where 条件中直接调用了函数 (CURRENT_TIMESTAMP() + interval - 3 day)，导致删除语句无法正确使用索引，从而引发了全表扫，以下是 explain 语句的结果：| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | procedure_test | ALL | idx_data_time | NULL | NULL | NULL | 7403414 | Using where |
错误解法，试图通过变量的形式来执行，但结果与上述一致，无法正确利用索引提高效率。
正确示范中，通过使用 prepare 来执行动态语句，成功解决了问题，以下是 explain 语句的结果：
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | procedure_test | range | idx_data_time | idx_data_time | 303 | NULL | 311888 | Using index condition |
成功使用索引后，执行效率大大提高，从全表扫 (7403414) 到使用索引 (311888)。
结合 event 定时任务使用：
CREATE EVENT e_procedure_test_delete_120_minuteON SCHEDULE EVERY 7200 SECONDSTARTS '2020-11-16 22:00:00.000'ON COMPLETION PRESERVE ENABLEDO call p_procedure_test_delete_3day_ago()
转载地址：http://ixzbz.baihongyu.com/