由于業(yè)務(wù)隨著時間不停的改變，起初的表結(jié)構(gòu)設(shè)計已經(jīng)滿足不了如今的需求割以，這時你是不是想那就加字段唄金度！加字段也是個藝術(shù)活，接下來由本文的主人咔咔給你吹严沥。
試想一下這個場景
事務(wù)A在執(zhí)行一個非常大的查詢
事務(wù)B毫不猶豫的執(zhí)行了DDL操作
接下來會發(fā)生什么猜极，你如果已經(jīng)知道答案的話恭喜你又掌握了一個MySQL中重要的知識點。
事務(wù)A執(zhí)行查詢時會持有MDL鎖消玄，而事務(wù)B同樣也需要MDL鎖跟伏，但事務(wù)A在進行大查詢丢胚，所以導(dǎo)致事務(wù)B后的所有操作都會被堵塞。
這時你應(yīng)該知道了在MySQL中所有對表的增刪改查都需要申請MDL讀鎖受扳。
接下來聊聊如何安全的給表加個字段
最新文章
MySQL統(tǒng)計總數(shù)就用count(*)携龟，別花里胡哨的《死磕MySQL系列 十》
為什么MySQL字符串不加引號索引失效？《死磕MySQL系列 十一》
打開order by的大門勘高，一探究竟《死磕MySQL系列 十二》
重重封鎖峡蟋，讓你一條數(shù)據(jù)都拿不到《死磕MySQL系列 十三》
一、為什么不建議你在生產(chǎn)環(huán)境下進行DDL
上期文章跟大家聊過的MDL鎖的知識還記得吧华望！不記得也沒事蕊蝗，那就在這篇文章中再進行復(fù)習(xí)。
MDL鎖不需要顯示調(diào)用立美，當對一個表做增刪改查時會默認加MDL讀鎖匿又，而執(zhí)行修改表結(jié)構(gòu)時會默認加MDL寫鎖方灾。
這也就是文章開頭給大家說的事務(wù)B是不能在事務(wù)A沒釋放MDL讀鎖之前執(zhí)行建蹄。
事務(wù)B需要的是MDL寫鎖，MDL讀鎖與寫鎖是互斥關(guān)系裕偿，因此事務(wù)B的DDL操作會一直等待事務(wù)A提交并釋放MDL鎖
但你有沒有想過一個問題洞慎，此時的事務(wù)B執(zhí)行的在線DDL操作，需要的是MDL寫鎖嘿棘，上文也說了對一個表做增刪改查時會默認加MDL讀鎖劲腿，這不就意味著后續(xù)對這個表的所有操作都會堵塞嗎？
所以說堅決不要在生產(chǎn)環(huán)境進行在線DDL鸟妙，現(xiàn)在的客戶端都有會重試機制焦人，當堵塞的語句超時后會再起一個新的事務(wù)在請求，這張表假設(shè)是一個熱表重父，MySQL庫的線程會非郴ㄍ郑快就爆滿，等待的結(jié)果就是用戶那邊遲遲響應(yīng)不了結(jié)果房午。
這里給大家一個方案矿辽，當你十分緊急需要添加一個字段時，可以給語句設(shè)置一個時間郭厌，如果在這個設(shè)定的時間內(nèi)能拿到MDL寫鎖最好袋倔，拿不到也會阻塞后續(xù)的業(yè)務(wù)語句。
當設(shè)置的這個時間超過后折柠，這個指令就結(jié)束了宾娜，之后可以再次重復(fù)執(zhí)行這個指令即可。
執(zhí)行語法為alter table table_nam wait 10 add cloumn
這個方案也是在你的表不大的情況下才可以進行執(zhí)行的扇售，假設(shè)你的表就看第二種方案哈前塔！要不你會死的很慘贾陷。
咔咔在一張近9000W數(shù)據(jù)的開發(fā)表上做過一次DDL操作，大概用了23s這樣一個時間嘱根，這要是在線上想想都害怕
二髓废、如何安全給表加個字段
目前咔咔知道的第三方工具有gh-ost和pt-online-schema-change，咔咔所在的公司使用的是后者该抒，對于這兩個插件后者的使用還是多點慌洪。
還記得在前幾期文章中提到了表數(shù)據(jù)都刪完了，但表空間依然沒有縮小凑保，在那期就簡單的提了一下冈爹。
想要縮小表空間可以新建一模一樣的表結(jié)構(gòu)，然后根據(jù)主鍵ID的順序把數(shù)據(jù)從就舊表中逐行插入新表欧引，這樣就可以減少表空洞的問題频伤。
同理今天要說的pt-online-schema-change這個插件的工作流程大致如下
- 新建一模一樣的表，表名可以起為_new后綴
- 接著在這個新表執(zhí)行更改字段操作
- 接著在原表上加三個觸發(fā)器芝此，分別為delete憋肖、update、insert婚苹，將原表中要執(zhí)行的語句也在新表中執(zhí)行
- 最后將原表的數(shù)據(jù)拷貝到新表中岸更，替換掉原表
接下來咔咔將親自實戰(zhàn)一下此操作，可以跟著咔咔的步驟一起來
三膊升、使用pt-online-schema-change
安裝步驟
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`yum -y install perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm
rm -f percona-toolkit-3.1.0-2.el7.x86_64.rpm` </pre>
安裝完成后執(zhí)行./bin/pt-online-schema-change --help怎炊，出現(xiàn)以下界面證明你就安裝成功了
參數(shù)認識
參數(shù)是非常多的，咔咔簡單的說幾個足夠修改表結(jié)構(gòu)的
- --user 連接mysql用戶名
- --password 連接mysql密碼
- --host 連接msyql地址
- p 連接mysql端口號
- D 連接mysql庫名
- t 連接msyql表名
- --alter 修改表結(jié)構(gòu)的語句
- --execute 執(zhí)行修改表結(jié)構(gòu)
- --charset=utf8 使用utf8編碼廓译，避免中文亂碼
- --no-version-check 不檢查版本评肆，在阿里云服務(wù)器中一般加入此參數(shù)，否則會報錯
接下來使用pt-online-schema-change修改一下表結(jié)構(gòu)
這個是目前默認的數(shù)據(jù)結(jié)構(gòu)非区，現(xiàn)在想要添加郵箱字段
在這塊你大概率會遇到兩個錯誤瓜挽，一個是你的密碼中標點符號，另一個是pt版本問題
處理完密碼中的標點符號就會來到下面這個錯誤
執(zhí)行代碼./bin/pt-online-schema-change --charset=utf8 --no-version-check --user="root" --password="Fang1996" --host="127.0.0.1" D="kaka",t=evt_sms --alter "add column email varchar(255) not null default '' after phone" --print --execute
這個問題是pt-online-schema-change版本造成的
刪除之前的版本
yum remove percona-toolkit
重新安裝新版本的rpm包即可院仿，安裝步驟如下
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`wget https://downloads.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/x86_64/percona-toolkit-3.2.0-1.el7.x86_64.rpm
rpm -ivh percona-toolkit-3.2.0-1.el7.x86_64.rpm` </pre>
這一步部分小伙伴會遇到其它資料給的地址太過老舊秸抚，導(dǎo)致一直下載不了，咔咔提供的這個地址是官網(wǎng)提取的歹垫，非常好用剥汤，https://www.percona.com/downloads/percona-toolkit/3.2.0/binary/redhat/7/這里可以獲取各個版本的rpm包
咔咔親測虛擬機的下載速度要不服務(wù)器快，于是先將文件下載到了虛擬機
然后使用scp直接把虛擬機的文件傳輸?shù)街鳈C上排惨，執(zhí)行命令為scp percona-toolkit-3.2.0-1.el7.x86_64.rpm root@ip:/
使用語法非常簡單scp local_file remote_username@remote_ip:remote_folder
local_file ：本地文件
remote_username：遠程用戶名
remote_ip：遠程機器IP地址
remote_folder 遠程目錄
執(zhí)行完成后需要輸入服務(wù)器密碼吭敢，然后等待傳輸完成就可以在服務(wù)器上看到對應(yīng)的文件
這里分別在虛擬機、服務(wù)器查看了文件大小暮芭，文件是沒有一點問題的
到這里對于pt-online-schema-change的安裝才完成鹿驼，接下來重試之前執(zhí)行的命令
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">./bin/pt-online-schema-change --charset=utf8 --no-version-check --user="root" --password="Fang1996" --host="127.0.0.1" D="kaka",t=evt_sms --alter "add column email varchar(255) not null default '' after phone" --print --execute </pre>
以上就是修改成功的信息欲低，可以看到處理數(shù)據(jù)有46W，基本是毫無壓力畜晰，建議大家實測一下
可以看到要加的email字段已經(jīng)加上了砾莱，這個就是我們想要的結(jié)果
四、簡化pt-online-schema-change的執(zhí)行命令
在執(zhí)行修改表結(jié)構(gòu)命令時輸入了很多配置信息凄鼻， 這些信息可以放在腳本里邊復(fù)用的
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">`#!/bin/bash
database=2
alter_conment=$3
pt_host='127.0.0.1'
pt_user='root'
pt_pwd='Fang1996'
echo "$table"
echo "$alter_conment"
/bin/pt-online-schema-change --charset=utf8 --no-version-check --user={pt_pwd} --host=
database,t=
{alter_conment}" --print --execute` </pre>
那么使用pt-online-schema-change則可以這樣寫
<pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">sh pt.sh kaka evt_sms "add column kaka1 varchar(255) default '咔咔'" </pre>
這里給大家提供了添加字段的方法腊瑟，修改、刪除都是一致的块蚌，只需要把對應(yīng)的語句放到引號里邊即可
在這里你會發(fā)現(xiàn)只有進到腳本的目錄才能執(zhí)行對應(yīng)的腳本闰非，那么如何讓你的腳本可以在任意地方都可以執(zhí)行呢？
五峭范、總結(jié)
本期文章給大家詳細說明了為什么不建議大家直接在線DDL财松，因為會阻塞SQL導(dǎo)致業(yè)務(wù)停擺
給大家介紹了第三方腳本pt-online-schema-change的安裝、使用，使用還是非常簡單的，參數(shù)也只給大家說明了常用的明郭，如果要放到生產(chǎn)環(huán)境使用，你需要把所有的參數(shù)都簡單過一遍胚迫。
最后用一個腳本優(yōu)化了修改表信息時需要輸入的一大堆重復(fù)信息喷户。
“
堅持學(xué)習(xí)唾那、堅持寫作、堅持分享是咔咔從業(yè)以來所秉持的信念褪尝。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點幫助闹获，我是咔咔，下期見河哑。
”
