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

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

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

1、鎖表問題

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

但是如果這張線上表是個大表,也就是說該表可能存在百萬、千萬、甚至上億條數(shù)據(jù),這時候DDL操作這個過程耗時非常久,并且這個執(zhí)行階段存在一個極端現(xiàn)象:鎖表,鎖表會帶來很大的問題,那就是直接導(dǎo)致線上大表讀寫阻塞。這對大部分系統(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、準(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 是一個集合了多個實用工具的工具包,專門用于 MySQL 數(shù)據(jù)庫的管理和優(yōu)化,而 pt-online-schema-change 是其中一個非常有用的工具,所以這里安裝 Percona Toolkit

apt install percona-toolkit

3.3、模擬鎖表

先通過 Navicat 依次執(zhí)行下述幾個命令,模擬鎖表現(xiàn)象發(fā)生,DDL操作前的事務(wù)沒有提交,就會一直鎖?。?/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í)行,會導(dǎo)致生產(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.給大表加字段(注意??:這個在服務(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ù)細節(jié),有興趣可以訪問官網(wǎng)自行查看。

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

相關(guān)文章

  • Mysql權(quán)限管理grant命令使筆記

    Mysql權(quán)限管理grant命令使筆記

    這篇文章主要介紹了Mysql權(quán)限管理grant命令使筆記,詳細記錄了常用權(quán)限管理命令,需要的朋友可以參考下
    2014-05-05
  • MySQL的存儲函數(shù)與存儲過程的區(qū)別解析

    MySQL的存儲函數(shù)與存儲過程的區(qū)別解析

    這篇文章主要介紹了MySQL的存儲函數(shù)與存儲過程的區(qū)別,本文給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-04-04
  • MySQL數(shù)據(jù)庫基礎(chǔ)篇SQL窗口函數(shù)示例解析教程

    MySQL數(shù)據(jù)庫基礎(chǔ)篇SQL窗口函數(shù)示例解析教程

    這篇文章主要為大家介紹了MySQL數(shù)據(jù)庫基礎(chǔ)篇之窗口函數(shù)示例解析教程,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步
    2021-10-10
  • MySQL group_concat函數(shù)使用方法詳解

    MySQL group_concat函數(shù)使用方法詳解

    GROUP_CONCAT函數(shù)用于將GROUP BY產(chǎn)生的同一個分組中的值連接起來,返回一個字符串結(jié)果,接下來就給大家簡單的介紹一下MySQL group_concat函數(shù)的使用方法,需要的朋友可以參考下
    2023-07-07
  • MySQL自增列解析(Auto_increment)

    MySQL自增列解析(Auto_increment)

    MySQL數(shù)據(jù)庫為列提供了一種自增屬性,本文主要介紹了MySQL自增列解析,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2024-09-09
  • 使用keras做SQL注入攻擊的判斷(實例講解)

    使用keras做SQL注入攻擊的判斷(實例講解)

    下面小編就為大家分享一篇使用keras做SQL注入攻擊的判斷(實例講解),具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2017-12-12
  • Mysql高效分頁詳解

    Mysql高效分頁詳解

    這篇文章主要為大家詳細介紹了Mysql高效分頁的相關(guān)資料,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2017-01-01
  • 解決mysql服務(wù)器在無操作超時主動斷開連接的情況

    解決mysql服務(wù)器在無操作超時主動斷開連接的情況

    這篇文章主要介紹了解決mysql服務(wù)器在無操作超時主動斷開連接的情況,具有很好的參考價值,希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-07-07
  • Linux系統(tǒng)利用crontab定時備份Mysql數(shù)據(jù)庫方法

    Linux系統(tǒng)利用crontab定時備份Mysql數(shù)據(jù)庫方法

    本文教你如果快速利用系統(tǒng)crontab來定時執(zhí)行備份文件,按日期對備份結(jié)果進行保存
    2021-09-09
  • Mybatis動態(tài)傳入order?by問題

    Mybatis動態(tài)傳入order?by問題

    這篇文章主要介紹了Mybatis動態(tài)傳入order?by問題,具有很好的參考價值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-12-12

最新評論