1、鎖表問(wèn)題

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

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

現(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è)方案爭(zhēng)議還是比較多的，并不建議使用，具體原因參考其他文章，本文重點(diǎn)不在這里。

2、pt-online-schema-change 原理

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

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

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

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

本次我使用阿里云的 Ubuntu 22.04 服務(wù)器，上面安裝了一臺(tái)MySQL數(shù)據(jù)庫(kù)，MySQL最好設(shè)置為innodb_autoinc_lock_mode=2，否則在高并發(fā)的寫(xiě)入情況下，很容易產(chǎn)生鎖等待以及死鎖，我先通過(guò)下述 SQL 腳本新增 700w 測(cè)試數(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ù)庫(kù)的管理和優(yōu)化，而 pt-online-schema-change 是其中一個(gè)非常有用的工具，所以這里安裝 Percona Toolkit：

apt install percona-toolkit

3.3、模擬鎖表

先通過(guò) Navicat 依次執(zhí)行下述幾個(gè)命令，模擬鎖表現(xiàn)象發(fā)生，DDL操作前的事務(wù)沒(méi)有提交，就會(huì)一直鎖?。?/p>

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

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

# 3.分頁(yè)查詢
select * from user limit 1,10

接著就通過(guò)命令查看，出現(xiàn)鎖表現(xiàn)象：

SHOW FULL PROCESSLIST

DDL后續(xù)所有操作，都會(huì)被阻塞，沒(méi)辦法正常執(zhí)行，會(huì)導(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賬號(hào) --password=MySQL密碼 --alter "DDL語(yǔ)句" D=數(shù)據(jù)庫(kù)名,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.分頁(yè)查詢
select * from user limit 1,10

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

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

最新評(píng)論