欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL線上大表DDL避免鎖表的解決方案

 更新時間:2025年04月23日 09:56:08   作者:i人事_徐志斌  
在系統(tǒng)研發(fā)過程中,隨著業(yè)務需求千變?nèi)f化,避免不了調(diào)整線上MySQL DDL數(shù)據(jù)表的操作,也就是ALTER TABLE操作,但是如果這張線上表是個大表,這時候DDL操作這個過程耗時非常久,會出現(xiàn)鎖表現(xiàn)象,所以本文介紹了避免線上鎖表的方案,需要的朋友可以參考下

1、鎖表問題

在系統(tǒng)研發(fā)過程中,隨著業(yè)務需求千變?nèi)f化,避免不了調(diào)整線上MySQL DDL數(shù)據(jù)表的操作,也就是ALTER TABLE操作,例如:加個索引、加個字段等…

但是如果這張線上表是個大表,也就是說該表可能存在百萬、千萬、甚至上億條數(shù)據(jù),這時候DDL操作這個過程耗時非常久,并且這個執(zhí)行階段存在一個極端現(xiàn)象:鎖表,鎖表會帶來很大的問題,那就是直接導致線上大表讀寫阻塞。這對大部分系統(tǒng)來說,是無法接受的。

現(xiàn)在有很多避免線上鎖表的方案,例如:

  • 停機執(zhí)行(直接系統(tǒng)停機維護…不推薦 )
  • Online DDL(MySQL 5.6版本以上支持,不推薦 )
  • pt-online-schema-change(推薦 )

本次主要介紹pt-online-schema-change,因為Online DDL這個方案爭議還是比較多的,并不建議使用,具體原因參考其他文章,本文重點不在這里。

2、pt-online-schema-change 原理

參考文檔:pt-online-schema-change — Percona Toolkit Documentation

  1. 首先,根據(jù)原表創(chuàng)建新表,但是并不會拷貝原表中的數(shù)據(jù),_new結(jié)尾
  2. 新表執(zhí)行 DDL 語句,因為是空表,執(zhí)行速度很快
  3. 原表加3個觸發(fā)器,捕獲變更(insert/update/delete),避免遷移過程中,新表數(shù)據(jù)不實時同步原表
  4. 批量拷貝原表數(shù)據(jù)到新表
  5. 數(shù)據(jù)一致后,會刪除原表,留下新表作為生產(chǎn)表。這個過程通常是瞬時的,新表此時已經(jīng)包含了所有的最新數(shù)據(jù)

3、pt-online-schema-change 實戰(zhàn)

3.1、準備數(shù)據(jù)

本次我使用阿里云的 Ubuntu 22.04 服務器,上面安裝了一臺MySQL數(shù)據(jù)庫,MySQL最好設置為innodb_autoinc_lock_mode=2,否則在高并發(fā)的寫入情況下,很容易產(chǎn)生鎖等待以及死鎖,我先通過下述 SQL 腳本新增 700w 測試數(shù)據(jù):

DELIMITER $$

CREATE PROCEDURE generate_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 7000000 DO
        INSERT INTO user (user_name, pass_word, create_time)
        VALUES 
        (CONCAT('user_', UUID()), 
         CONCAT('password_', FLOOR(RAND() * 10000)), 
         CURDATE());
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

CALL generate_data();

3.2、安裝工具

Percona Toolkit 是一個集合了多個實用工具的工具包,專門用于 MySQL 數(shù)據(jù)庫的管理和優(yōu)化,而 pt-online-schema-change 是其中一個非常有用的工具,所以這里安裝 Percona Toolkit

apt install percona-toolkit

3.3、模擬鎖表

先通過 Navicat 依次執(zhí)行下述幾個命令,模擬鎖表現(xiàn)象發(fā)生,DDL操作前的事務沒有提交,就會一直鎖?。?/p>

# 1.耗時查詢SQL(耗時幾分鐘)
select * from user

# 2.給大表加字段
ALTER TABLE user ADD COLUMN email30 VARCHAR (255) COMMENT '用戶郵箱'

# 3.分頁查詢
select * from user limit 1,10

接著就通過命令查看,出現(xiàn)鎖表現(xiàn)象:

SHOW FULL PROCESSLIST

DDL后續(xù)所有操作,都會被阻塞,沒辦法正常執(zhí)行,會導致生產(chǎn)環(huán)境SQL直接卡死。

3.4、解決鎖表

使用 pt-online-schema-change 進行 DDL 模版大概如下:

pt-online-schema-change --host=主機ip --user=MySQL賬號 --password=MySQL密碼 --alter "DDL語句" D=數(shù)據(jù)庫名,t=表名 --print --execute

重新模擬鎖表現(xiàn)象:

# 1.耗時查詢SQL(耗時幾分鐘)
select * from user

# 2.給大表加字段(注意??:這個在服務器上執(zhí)行,不要在Navicat上)
pt-online-schema-change --host=172.16.0.217 --user=root --password=root --alter "ADD COLUMN address VARCHAR(255) COMMENT '家庭住址'" D=pt-online-test,t=user --print --execute

# 3.分頁查詢
select * from user limit 1,10

最后會發(fā)現(xiàn),select * from user limit 1,10查詢操作不會被阻塞(但是執(zhí)行會變慢一些),當然pt-online-schema-change官方提供了許多參數(shù)細節(jié),有興趣可以訪問官網(wǎng)自行查看。

以上就是MySQL線上大表DDL避免鎖表的解決方案的詳細內(nèi)容,更多關(guān)于MySQL大表DDL鎖表的資料請關(guān)注腳本之家其它相關(guān)文章!

相關(guān)文章

最新評論