問題描述

前幾天同事問了我一個問題：業(yè)務(wù)A從MySQL遷移到MongoDB的原因是什么？

說實(shí)話，這個問題還真不好回答，為什么要遷移，一定是遇到了某種瓶頸，可能是數(shù)據(jù)量也可能是數(shù)據(jù)類型等，于是我咨詢了一下業(yè)務(wù)，最終得到了答案：這個業(yè)務(wù)中的某些表，要頻繁的加字段。mongodb中加字段的成本幾乎沒有，而MySQL低版本中加字段的成本還是挺高的。

那么常用的MySQL添加字段的方法有哪些呢？這里我簡單列舉一下：

1、percona的pt-osc工具

2、github開源項(xiàng)目gh-ost工具

3、MySQL原生Online DDL

MySQL Online DDL加列的歷史方法

01 Copy方法

MySQL5.5版本及之前的加列方法：Copy

它的執(zhí)行示意圖如下：

我們有一個原表A，只包含1個字段，它包含1、2、4、6這幾條記錄，當(dāng)我們使用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ù)更新。這可能是一個致命的缺點(diǎn)。

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應(yīng)用所有的row log，得到一個與表A數(shù)據(jù)相同的數(shù)據(jù)文件

4、利用數(shù)據(jù)文件tmp-file替換表A的數(shù)據(jù)文件即可。

這個過程中，由于row log的存在，使得在整個該表過程中，表A是可以進(jìn)行增刪改查的操作的，因?yàn)檫@些操作不會丟失。這也就是為什么把這個過程叫做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，結(jié)果如下：

 [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)

從結(jié)果不難看出，執(zhí)行時間上：

copy> inplace > instant

與此同時，copy算法的受影響行數(shù)是全部表，而inplace和instant的算法影響的行數(shù)都是0，說明他們是Online DDL操作。

最后，我們還可以通過下面的方法查看instant列的信息：

[test]23:53:01>SELECT*FROMinformation_schema.innodb_tableswherenamelike'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
1rowinset(0.00sec)

可以看到，test.t1這個表的instant列序號是6，代表它是這個表的第7個列(列編號從0開始)。

當(dāng)然，instant算法不支持刪除普通列、無法設(shè)置列的順序、還有一些其他的限制，詳情可以查看官方文檔：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

但這些限制并不影響它成為一個優(yōu)秀的DDL功能。相信通過MySQL版本的不斷迭代，在后面的版本中，有更多的變更操作可以用到instant這種高效的算法。

以上就是MySQL 8.0 Online DDL快速加列的相關(guān)總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于MySQL DDL快速加列的資料請關(guān)注本站其它相關(guān)文章！

海外服務(wù)器租用

版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。