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

首先，根據(jù)原表創(chuàng)建新表，但是并不會拷貝原表中的數(shù)據(jù)，_new結尾
在新表執(zhí)行 DDL 語句，因為是空表，執(zhí)行速度很快
給原表加3個觸發(fā)器，捕獲變更（insert/update/delete），避免遷移過程中，新表數(shù)據(jù)不實時同步原表
批量拷貝原表數(shù)據(jù)到新表
數(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操作前的事務沒有提交，就會一直鎖住：

# 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)容，更多關于MySQL大表DDL鎖表的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: