该文着重介绍MySQL-存储过程-高效清理数据相关知识,作者举例代码和分析说明恰到好处,遇到问题的朋友可以研究一下。
使用存储过程清理数据,往往会引起全表扫,如果表内数据非常大,清理效率会很低。
本文讲解了如何在存储过程中合理利用索引清理数据。
一、准备表结构(测试数据量740W)
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`()
BEGIN
delete 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`()
BEGIN
DECLARE 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`()
BEGIN
DECLARE 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
四、原理解析
1、错误示范中,由于在 where条件中调用了函数(CURRENT_TIMESTAMP() + interval - 3 day),导致删除语句无法使用,引起了全表扫,下面为 explain 语句结果:
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEprocedure_testALLidx_data_time7403414Using where
2、错误解法,妄图使用变量的形式来执行,但是结果和示范一致,无法正确使用索引提高效率。
3、正确示范中,使用了 prepare来执行动态语句,成功解决了这个问题,explain 结果为:
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEprocedure_testrangeidx_data_timeidx_data_time303311888Using index condition
4、成功使用索引后,执行效率大大提高了,从全表扫(7403414),到使用索引(311888)。
5、结合 event 定时任务使用:
CREATE EVENT e_procedure_test_delete_120_minute
ON SCHEDULE EVERY 7200 SECOND
STARTS '2020-11-16 22:00:00.000'
ON COMPLETION PRESERVE
ENABLE
DO call p_procedure_test_delete_3day_ago()
关键词:mysql
数据库
存储过程
索引,存储,存储过程,过程,高效,清理,数据