目錄
- 問題描述
- MySQL Online DDL加列的歷史方法
- MySQL8.0.12 引入的Instant方法
問題描述
前幾天同事問了我一個問題:業(yè)務A從MySQL遷移到MongoDB的原因是什么?
說實話,這個問題還真不好回答,為什么要遷移,一定是遇到了某種瓶頸,可能是數(shù)據(jù)量也可能是數(shù)據(jù)類型等,于是我咨詢了一下業(yè)務,最終得到了答案:這個業(yè)務中的某些表,要頻繁的加字段。mongodb中加字段的成本幾乎沒有,而MySQL低版本中加字段的成本還是挺高的。
那么常用的MySQL添加字段的方法有哪些呢?這里我簡單列舉一下:
1、percona的pt-osc工具
2、github開源項目gh-ost工具
3、MySQL原生Online DDL
MySQL Online DDL加列的歷史方法
01 Copy方法
MySQL5.5版本及之前的加列方法:Copy
它的執(zhí)行示意圖如下:
我們有一個原表A,只包含1個字段,它包含1、2、4、6這幾條記錄,當我們使用Copy算法加列時:
1、創(chuàng)建了一個新的表tmp-A,新表包含2個字段,
2、然后我們把表A的數(shù)據(jù)全部逐行拷貝到tmp-A這個新表里面,
3、然后用tmp-A表和A表做個交換,
這樣,我們的新表就包含2個字段了。同時需要注意,新表中的數(shù)據(jù)記錄比原表更加緊湊了。原表中可能由于刪除了3和5兩條記錄,使得表中間留下了空洞,或者叫空間碎片。
可以看到,Copy算法需要拷貝一遍數(shù)據(jù),需要額外的存儲空間來存儲tmp-A這個臨時表。另外,在拷貝數(shù)據(jù)的過程中,表A的寫入操作會丟失,也就是說,表A在alter table的過程中不能有數(shù)據(jù)更新。這可能是一個致命的缺點。
02 Inplace方法
MySQL5.6版本開始引入Online DDL,這個功能使得上面的過程變成了下面這樣:
它的過程和上面的Copy算法有些不同:
1、Online DDL過程中,從表A提取B+樹,并存儲到一個中間文件tmp-file,而不是中間表tmp-A
2、步驟1執(zhí)行過程中,對表A的寫入,都會記錄到row log中
3、步驟1執(zhí)行完畢后,對tmp-file應用所有的row log,得到一個與表A數(shù)據(jù)相同的數(shù)據(jù)文件
4、利用數(shù)據(jù)文件tmp-file替換表A的數(shù)據(jù)文件即可。
這個過程中,由于row log的存在,使得在整個該表過程中,表A是可以進行增刪改查的操作的,因為這些操作不會丟失。這也就是為什么把這個過程叫做Online DDL的原因。
另外,這里需要解釋下,Copy算法中生成的tmp-A臨時表是在Server層面創(chuàng)建的,而上述Online DDL操作中的tmp-file是在插件式存儲引擎Innodb內(nèi)部生成的,我們把這種在Innodb內(nèi)部完成的變更操作,稱之為Inplace(中文表示原地),也就是不需要將數(shù)據(jù)挪動到"server層的臨時表"。
MySQL8.0.12 引入的Instant方法
MySQL8.0.12版本引入了Instant的方法,它讓加列變得更加簡單。instant算法添加列時不再需要 rebuild 整個表,只需要在表的 metadata 中記錄新增列的基本信息即可。
我們來看它的優(yōu)勢,首先我們創(chuàng)建一個表t1,并插入26w條數(shù)據(jù),然后分別添加數(shù)據(jù)列col_1,col_2,col_3,并顯示指定加列的算法為copy、inplace、和instant,結果如下:
[test] 23:42:45> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 262144 |
+----------+
1 row in set (0.06 sec)
方案一:copy
[test] 23:43:29> alter table t1 add col_1 int,algorithm=copy;
Query OK, 262144 rows affected (1.48 sec)
Records: 262144 Duplicates: 0 Warnings: 0
方案二:inplace
[test] 23:43:46> alter table t1 add col_2 int,algorithm=inplace;
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
方案三:instant
[test] 23:44:08> alter table t1 add col_3 int,algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
m5480:mysqlha_common@10.41.28.124 [test] 23:44:14> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
`age` int DEFAULT NULL,
`score` int DEFAULT NULL,
`col_1` int DEFAULT NULL,
`col_2` int DEFAULT NULL,
`col_3` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_sco` (`score`)
) ENGINE=InnoDB AUTO_INCREMENT=458730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
從結果不難看出,執(zhí)行時間上:
copy> inplace > instant
與此同時,copy算法的受影響行數(shù)是全部表,而inplace和instant的算法影響的行數(shù)都是0,說明他們是Online DDL操作。
最后,我們還可以通過下面的方法查看instant列的信息:
[test] 23:53:01> SELECT * FROM information_schema.innodb_tables where name like 'test/t1'\G
*************************** 1. row ***************************
TABLE_ID: 1079
NAME: test/t1
FLAG: 33
N_COLS: 10
SPACE: 22
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
INSTANT_COLS: 6
1 row in set (0.00 sec)
可以看到,test.t1這個表的instant列序號是6,代表它是這個表的第7個列(列編號從0開始)。
當然,instant算法不支持刪除普通列、無法設置列的順序、還有一些其他的限制,詳情可以查看官方文檔:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
但這些限制并不影響它成為一個優(yōu)秀的DDL功能。 相信通過MySQL版本的不斷迭代,在后面的版本中,有更多的變更操作可以用到instant這種高效的算法。
以上就是MySQL 8.0 Online DDL快速加列的相關總結的詳細內(nèi)容,更多關于MySQL DDL快速加列的資料請關注腳本之家其它相關文章!
您可能感興趣的文章:- MySQL8.0 如何快速加列
- Mysql Online DDL的使用詳解
- MySQL DDL 引發(fā)的同步延遲該如何解決
- 詳解MySQL8.0原子DDL語法
- MySQL在線DDL工具 gh-ost的原理解析
- MySQL ddl語句的使用
- Mysql DDL常見操作匯總
- 解析MySQL8.0新特性——事務性數(shù)據(jù)字典與原子DDL
- MySQL數(shù)據(jù)定義語言DDL的基礎語句
- MySQL8.0 DDL原子性特性及實現(xiàn)原理
- MySQL在線DDL gh-ost使用總結
- 解決MySQL 5.7中定位DDL被阻塞的問題
- MySQL8.0新特性之支持原子DDL語句
- MySQL曝中間人攻擊Riddle漏洞可致用戶名密碼泄露的處理方法