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

- 日理万妓 2023-01-12 01:56 119阅读 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

二、错误示范

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

三、错误解法

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

三、正确示范

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

四、原理解析

1、错误示范中，由于在 where条件中调用了函数(CURRENT\_TIMESTAMP() + interval - 3 day)，导致删除语句无法使用，引起了全表扫，下面为 explain 语句结果：

select\_type

table

type

possible\_keys

key

key\_len

ref

rows

Extra

SIMPLE

procedure\_test

ALL

idx\_data\_time

7403414

Using where

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

3、正确示范中，使用了 prepare来执行动态语句，成功解决了这个问题，explain 结果为：

select\_type

table

type

possible\_keys

key

key\_len

ref

rows

Extra

SIMPLE

procedure\_test

range

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

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

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

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

发表评论取消回复

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

相关阅读

相关 MySQL - 利用存储过程生成数据

相关 MySQL利用存储过程创建数据

相关 mysql 存储过程 open,mysql 存储过程

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

相关【MySQL-存储过程创建测试数据】

相关 MySQL存储过程详解 mysql 存储过程

相关 MySQL存储过程详解 mysql 存储过程

相关 Oracle 存储过程导出大量数据

相关 mysql存储过程

相关 mysql 存储过程