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 陳述句結果：

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/224345.html

標籤：其他

上一篇：MySQL學習筆記總結(小白必看+基礎篇)

下一篇：C#Datatable