mysql 清理大量数据的存储过程_MySQL-存储过程-高效清理数据

- 日理万妓 2023-01-12 01:56 240阅读 0赞

使用存储过程清理数据，往往会引起全表扫，如果表内数据非常大，清理效率会很低。

本文讲解了如何在存储过程中合理利用索引清理数据。

一、准备表结构(测试数据量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=’存储过程清理数据测试表’;1

2

3

4

5

6

7

8

9

10

11

二、错误示范

-- 清理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);

END1

2

3

4

5

三、错误解法

-- 清理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;

END1

2

3

4

5

6

7

三、正确示范

-- 清理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;

END1

2

3

4

5

6

7

8

9

10

11

12

四、原理解析

1、错误示范中，由于在 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

7403414

Using where

2、错误解法，妄图使用变量的形式来执行，但是结果和示范一致，无法正确使用索引提高效率。

3、正确示范中，使用了 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

311888

Using 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()1

2

3

4

5

6

文章来源: blog.csdn.net，作者：阔海星沉，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/ManWZD/article/details/109730238

发表评论

表情：
评论列表 （有 0 条评论，240人围观）

还没有评论，来说两句吧...

相关阅读