快捷導(dǎo)航

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

更新時(shí)間：2025年04月23日 09:56:08 作者：i人事_徐志斌

在系統(tǒng)研發(fā)過程中,隨著業(yè)務(wù)需求千變?nèi)f化,避免不了調(diào)整線上MySQL DDL數(shù)據(jù)表的操作,也就是ALTER TABLE操作,但是如果這張線上表是個(gè)大表,這時(shí)候DDL操作這個(gè)過程耗時(shí)非常久,會出現(xiàn)鎖表現(xiàn)象,所以本文介紹了避免線上鎖表的方案,需要的朋友可以參考下

1、鎖表問題

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

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

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

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

本次主要介紹pt-online-schema-change，因?yàn)镺nline DDL這個(gè)方案爭議還是比較多的，并不建議使用，具體原因參考其他文章，本文重點(diǎn)不在這里。

2、pt-online-schema-change 原理

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

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

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

3.1、準(zhǔn)備數(shù)據(jù)

本次我使用阿里云的 Ubuntu 22.04 服務(wù)器，上面安裝了一臺MySQL數(shù)據(jù)庫，MySQL最好設(shè)置為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 是一個(gè)集合了多個(gè)實(shí)用工具的工具包，專門用于 MySQL 數(shù)據(jù)庫的管理和優(yōu)化，而 pt-online-schema-change 是其中一個(gè)非常有用的工具，所以這里安裝 Percona Toolkit：

apt install percona-toolkit

3.3、模擬鎖表

先通過 Navicat 依次執(zhí)行下述幾個(gè)命令，模擬鎖表現(xiàn)象發(fā)生，DDL操作前的事務(wù)沒有提交，就會一直鎖住：

# 1.耗時(shí)查詢SQL（耗時(shí)幾分鐘）
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í)行，會導(dǎo)致生產(chǎn)環(huán)境SQL直接卡死。

3.4、解決鎖表

使用 pt-online-schema-change 進(jìn)行 DDL 模版大概如下：

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

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

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

# 2.給大表加字段（注意??：這個(gè)在服務(wù)器上執(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í)行會變慢一些），當(dāng)然pt-online-schema-change官方提供了許多參數(shù)細(xì)節(jié)，有興趣可以訪問官網(wǎng)自行查看。

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

您可能感興趣的文章: