MySQL線上大表DDL避免鎖表的解決方案
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結(jié)尾 - 在
新表
執(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操作前的事務
沒有提交,就會一直鎖?。?/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)文章
MySQL數(shù)據(jù)庫基礎(chǔ)篇SQL窗口函數(shù)示例解析教程
這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫基礎(chǔ)篇之窗口函數(shù)示例解析教程,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步2021-10-10MySQL group_concat函數(shù)使用方法詳解
GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個分組中的值連接起來,返回一個字符串結(jié)果,接下來就給大家簡單的介紹一下MySQL group_concat函數(shù)的使用方法,需要的朋友可以參考下2023-07-07Linux系統(tǒng)利用crontab定時備份Mysql數(shù)據(jù)庫方法
本文教你如果快速利用系統(tǒng)crontab來定時執(zhí)行備份文件,按日期對備份結(jié)果進行保存2021-09-09