MySQL日常大表的DDL操作介紹

160次閱讀

共計 2659 個字符，預計需要花費 7 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 日常大表的 DDL 操作介紹”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“MySQL 日常大表的 DDL 操作介紹”吧！

大多數的 alter table 操作都會涉及 lock– copy to new table– rename– unlock 的過程，鎖表時間會很長, 而且 alter table 的 process 不可被 kill, 一旦執行就不可回退。
在 MySQL5.5 和之前版本，在運行的生產環境對大表（超過數百萬紀錄）執行 Alter 操作是一件很困難的事情。因為將重建表和鎖表，影響用戶者的使用。
從 MySQL5.6 開始，Online DDL 特性被引進。他增強了很多種類的 Alter
Table 操作避免拷貝表和鎖表，在運行 Alter 操作的同時允許運行 select,insert,update,delete 語句。因此在最新版本，我
們可以通過使用 ALGORITHM 和 LOCK 選項抑制文件拷貝和加鎖。
但是即使在 MySQL5.6，仍然有一些 Alter 操作（增加 / 刪除列，增加 / 刪除主鍵, 改變數據類型等）需要重建表。

雖然 mysql5.6 對 atler table 操作有了很大改進，但仍有很多限制，并不建議直接在線上使用。如果線上有 DDL 的需求我們建議使用以下兩種方式：
1、主從架構輪詢修改
2、使用在線修改工具 online-schema-change
對于第一種方式，使用的前提是，你的數據庫架構是一個集群，如果不是，也就無所謂的輪詢修改。修改的原理就是利用主從服務，在應用無感知的情況下，得到停機窗口，進行修改。
今天我們著重講第二種方式，利用第三方工具實現在線大表的 DDL 操作。這種方式在 alter 操作更改表結構的時候不用鎖定表，也就是說執行 alter 的時候不會阻塞寫和讀取操作。

工作原理：
創建一個和你要執行 alter 操作的表一樣的空表結構，執行表結構修改，然后從原表中 copy 原始數據到表結構修改后的表，當數據 copy 完成以后就會將
原表移走，用新表代替原表，默認動作是將原表 drop 掉。在 copy 數據的過程中，任何在原表的更新操作都會更新到新表，因為這個工具在會在原表上創建觸
發器，觸發器會將在原表上更新的內容更新到新表。如果表中已經定義了觸發器這個工具就不能工作了。

注意點：
1、操作的表必須有主鍵或唯一索引否則報錯。
2、如果表有外鍵，除非使用 –alter-foreign-keys-method 指定特定的值，否則工具不予執行。
3、當業務量較大時，修改操作會等待沒有數據修改后，執行最后的 rename 操作。因此，在修改表結構時，應該盡量選擇在業務相對空閑時，至少修改表上的數據操作較低時，執行較為妥當。
4、由于可能存在一定的風險，在操作之前，建議對數據表進行備份，可以使得操作更安全、可靠。
5、當是主從環境，不在乎從的延遲，則需要加 –recursion-method=none 參數。當需要盡可能的對服務產生小的影響，則需要加上 –max-load 參數。

環境搭建：
安裝依賴環境
##Install DBI
wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
tar -zxvf DBI-1.625.tar.gz
cd DBI-1.625
perl Makefile.PL
make
make install

##Install DBD::Mysql
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz
tar -zxvf DBD-mysql-4.023.tar.gz
cd DBD-mysql-4.023
perl Makefile.PL
make
make install

安裝 percona-toolkit：
##Install percona-toolkit
wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.16.tar.gz
cd percona-toolkit-2.2.16
perl Makefile.PL
make
make install

常用操作：
添加字段
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx –alter= add
column col1_test int –execute D=test,t=t_xxx_compensate
修改字段
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx
–alter= MODIFY COLUMN col1_test TINYINT NOT NULL DEFAULT 0 –execute
D=test,t=t_xxx_compensate
改字段名
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx
–alter= CHANGE COLUMN col1_test address varchar(30) –execute
D=test,t=t_xxx_compensate
刪除字段
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx
–alter= drop column address –execute D=test,t=t_xxx_compensate
添加索引
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx –alter= add
key indx_test(col1_test) –execute D=test,t=t_xxx_compensate
刪除索引
[root@rac1 bin]# ./pt-online-schema-change -uroot -pxxx –alter= DROP INDEX indx_test –execute D=test,t=t_xxx_compensate