自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

聊一個 MySQL 插入死鎖問題

作者：寫代碼的SharkChili
數(shù)據(jù)庫 MySQL
本文就以一個簡單的功能示例為大家演示一下insert into ...... select 可能引發(fā)的問題和解決方案。

在進行MySQL數(shù)據(jù)備份遷移時，很多人為了避免網(wǎng)絡IO的開銷而選用insert into ...... select 進行數(shù)據(jù)遷移備份，但你是否知道這種做法會存在那些隱患呢？ 所以本文就以一個簡單的功能示例為大家演示一下insert into ...... select 可能引發(fā)的問題和解決方案。

問題復現(xiàn)

這里為了演示問題，筆者生成了一張帶有500w數(shù)據(jù)的數(shù)據(jù)表，對應DDL語句如下：

CREATE TABLE `batch_insert_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fileid_1` varchar(100) DEFAULT NULL,
  `fileid_2` varchar(100) DEFAULT NULL,
  `fileid_3` varchar(100) DEFAULT NULL,
  `fileid_4` varchar(100) DEFAULT NULL,
  `fileid_5` varchar(100) DEFAULT NULL,
  `fileid_6` varchar(100) DEFAULT NULL,
  `fileid_7` varchar(100) DEFAULT NULL,
  `fileid_8` varchar(100) DEFAULT NULL,
  `fileid_9` varchar(100) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2520001 DEFAULT CHARSET=utf8mb3 COMMENT='測試批量插入，一行數(shù)據(jù)1k左右';

使用count語句查看數(shù)據(jù)量：

select count(*)  from batch_insert_test;

稍微久等了一小會，輸出語句如下，可以看到一張表數(shù)據(jù)剛剛好達到500w:

count(*)|
--------+
 5000000|

同樣的我們給出備份遷移表的DDL，表結構是一樣的，唯一區(qū)別就是表名后綴多了個bak：

CREATE TABLE `batch_insert_test_bak` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fileid_1` varchar(100) DEFAULT NULL,
  `fileid_2` varchar(100) DEFAULT NULL,
  `fileid_3` varchar(100) DEFAULT NULL,
  `fileid_4` varchar(100) DEFAULT NULL,
  `fileid_5` varchar(100) DEFAULT NULL,
  `fileid_6` varchar(100) DEFAULT NULL,
  `fileid_7` varchar(100) DEFAULT NULL,
  `fileid_8` varchar(100) DEFAULT NULL,
  `fileid_9` varchar(100) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2520001 DEFAULT CHARSET=utf8mb3 COMMENT='測試批量插入，一行數(shù)據(jù)1k左右';

此時我們使用數(shù)據(jù)庫連接工具在會話窗口，執(zhí)行如下遷移語句：

insert into batch_insert_test_bak   select * from batch_insert_test;

然后我們再寫一段程序模擬插入：

 @Test
    public void insert() {
        while (true) {
            BatchInsertTest batchInsertTest = new BatchInsertTest();
            batchInsertTest.setFileid1(RandomUtil.randomString(1));
            batchInsertTest.setFileid2(RandomUtil.randomString(1));
            batchInsertTest.setFileid3(RandomUtil.randomString(1));
            batchInsertTest.setFileid4(RandomUtil.randomString(1));
            batchInsertTest.setFileid5(RandomUtil.randomString(1));
            batchInsertTest.setFileid6(RandomUtil.randomString(1));
            batchInsertTest.setFileid7(RandomUtil.randomString(1));
            batchInsertTest.setFileid8(RandomUtil.randomString(1));
            batchInsertTest.setFileid9(RandomUtil.randomString(1));
            batchInsertTest.setCreateDate(new Date());

            long begin = System.currentTimeMillis();
            batchInsertTestMapper.insert(batchInsertTest);
            long end = System.currentTimeMillis();

            log.info("插入耗時:{} ms", end - begin);

            ThreadUtil.sleep(10000L);
        }
    }

從輸出結果來看，一開始插入并不是很耗時，基本都是毫秒級完成，但是隨著時間的推移，插入的耗時逐漸增加，最慢的一次數(shù)據(jù)插入竟然花費了1分多鐘：

18.546 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:148 ms
28.778 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:221 ms
38.926 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:143 ms
49.588 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:652 ms
59.763 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:166 ms
09.820 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:56 ms
19.930 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:99 ms
30.027 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:86 ms
40.145 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:113 ms
50.238 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:79 ms
17.178 INFO  c.s.w.WebTemplateApplicationTests:117  main                    插入耗時:76927 ms

原因剖析

我們不妨使用如下語句查看一下執(zhí)行計劃：

explain insert into batch_insert_test_bak   select * from batch_insert_test;

可以看出無論是insert還是select都是走全表掃描，因為select查詢沒有走索引導致select子句的執(zhí)行過程會針對整張表從上到下的掃描進行一個逐步鎖(S鎖)的過程，隨著時間的推移它最終就會變?yōu)槿礞i。

而insert語句也因為對于插入數(shù)量未知而上全表鎖，進而長期持有auto-inc鎖，當然因為insert的表是用于遷移備份數(shù)據(jù)的，auto-inc鎖的長時間持有對于業(yè)務來說影響不大。

id|select_type|table                |partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra|
--+-----------+---------------------+----------+----+-------------+---+-------+---+-------+--------+-----+
 1|INSERT     |batch_insert_test_bak|          |ALL |             |   |       |   |       |        |     |
 1|SIMPLE     |batch_insert_test    |          |ALL |             |   |       |   |4692967|   100.0|     |

而select則不同，select查詢操作上的是讀鎖也就是S鎖，這使得其他事務針對掃描到的數(shù)據(jù)只能上S鎖不能上X鎖即寫鎖。

通過執(zhí)行計劃可以看到我們的操作是全表掃描ALL，這也就意味著該查詢逐步上S鎖，導致一段時間后，整張表都被鎖住，使得我們的新的會話的插入語句的事務無法提交。進而導致大量連接數(shù)阻塞積壓，各種超時問題也就隨之誕生，嚴重一點就很可能導致整個業(yè)務線癱瘓：

解決方案

所以如果我們希望遷移時不鎖住全表，可以在指定在每次遷移時指定一個范圍，所以我們針對時間字段增加索引，通過縮小范圍加索引查詢避免全表鎖：

ALTER TABLE db1.batch_insert_test DROP INDEX batch_insert_test_create_date_IDX;
CREATE INDEX batch_insert_test_create_date_IDX USING BTREE ON db1.batch_insert_test (create_date);

然后遷移的sql改為：

insert into batch_insert_test_bak   select * from batch_insert_test where create_date <now() ;

查看執(zhí)行計劃發(fā)現(xiàn)，select走了range索引，避免全表掃描，解決了上述的風險：

id|select_type|table                |partitions|type |possible_keys                    |key                              |key_len|ref|rows|filtered|Extra                |
--+-----------+---------------------+----------+-----+---------------------------------+---------------------------------+-------+---+----+--------+---------------------+
 1|INSERT     |batch_insert_test_bak|          |ALL  |                                 |                                 |       |   |    |        |                     |
 1|SIMPLE     |batch_insert_test    |          |range|batch_insert_test_create_date_IDX|batch_insert_test_create_date_IDX|6      |   |   1|   100.0|Using index condition|

小結

由此可以得出，再使用數(shù)據(jù)insert into ...... select進行數(shù)據(jù)遷移時，無比考慮讀寫鎖的工作機制，以及遷移可能導致的鎖的粒度和范圍，只有精確的評估風險點才能保證功能上限不影響正常業(yè)務的工作。

責任編輯：趙寧寧 來源： 寫代碼的SharkChili
MySQL數(shù)據(jù)遷移數(shù)據(jù)庫
相關推薦

2025-02-13 07:49:18

2016-10-20 15:27:18

MySQLredo死鎖

2024-08-27 22:04:37

2023-07-03 07:27:41

進程線程Win32

2020-10-15 06:56:51

MySQL排序

2022-01-28 08:47:25

軟件系統(tǒng)重構

2021-04-23 10:31:18

MySQLRole數(shù)據(jù)庫

2022-12-26 08:13:54

子查詢MySQL

2023-12-07 07:26:04

2022-11-02 08:51:01

2021-03-01 18:37:15

MySQL存儲數(shù)據(jù)

2021-07-30 10:33:57

MySQL觸發(fā)器數(shù)據(jù)

2017-12-26 10:19:14

大數(shù)據(jù)問題缺陷

2023-09-21 23:08:36

MySQL數(shù)據(jù)庫死鎖

2021-06-08 08:38:36

MySQL數(shù)據(jù)庫死鎖問題

2020-09-15 12:45:48

系統(tǒng)LinuxUnix

2022-01-11 15:44:15

JavaScript圖表庫數(shù)據(jù)

2022-09-19 16:24:33

數(shù)據(jù)可視化Matplotlib工具

2022-10-19 15:20:58

pandas數(shù)據(jù)處理庫技巧

2022-06-27 07:50:16

鏈表節(jié)點測試
點贊
收藏

51CTO技術棧公眾號