MySQL 8.0 InnoDB對即時加欄位的支持（instant add column）（譯）

来源:https://www.cnblogs.com/wy123/archive/2020/05/12/12875778.html
-Advertisement-
Play Games

原文地址：https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/ 長期以來，即時DDL一直是最受歡迎的InnoDB功能之一。對於越來越大且快速增長的數據集，任何網路規模資料庫中必須具備立即執行DDL的 ...


原文地址：https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/

  長期以來，即時DDL一直是最受歡迎的InnoDB功能之一。對於越來越大且快速增長的數據集，任何網路規模資料庫中必須具備立即執行DDL的功能。 開發人員經常需要添加新列，以滿足不斷變化的業務需求。即時加欄位（add column）的功能是我們一系列instantly DDL語句中的第一個。 在MySQL 8.0中遷移到新的事務數據字典使我們的這項工作變得容易得多。在MySQL 8.0之前，元數據（數據字典）存儲在稱為.frm文件的平面文件中， .frm文件是一種不可思議的格式，已近過時很久了。   該即時加列補丁是由騰訊游戲資料庫管理員團隊提供的，我們要感謝並感謝騰訊游戲所做的重要而及時的貢獻。

 背景

MySQL 5.6是第一個支持INPLACE DDL的版本。在MySQL 5.6之前，執行DDL的唯一方法是逐行複製行。
INPLACE DDL主要由InnoDB處理，而逐行COPY在伺服器層處理。直到8.0（請參閱實驗版本），InnoDB甚至通過為INPLACE DDL演算法重建表來向表中添加列。

  • 對於大型表，可能要花費很長時間，尤其是在複製環境中。 
  • 磁碟空間需求將增加一倍以上，大小與現有表大致相同。 
  • DDL操作占用資源，並且對CPU，記憶體和IO提出了很高的要求，這從用戶事務中爭奪資源。 
  • 如果涉及複製，slave要一直要等待到DDL的完成，才能開始同步。
  新的即時（instant）演算法

許多用戶向我們詢問瞭如何避免耗時的schema changes。現在，可以通過（始終）指定ALGORITHM = INSTANT來實現，這將保證操作立即完成（如果不支持則無法完成）。
此外，如果根本未指定ALGORITHM，則伺服器將首先嘗試DEFAULT = INSTANT演算法，如果無法完成，則伺服器將嘗試INPLACE演算法；如果SE無法支持，伺服器將最終嘗試COPY演算法。
新語法如下：

ALTER TABLE table_name [alter_specification], ALGORITHM=INSTANT;
INSTANT演算法的優勢在於，僅在數據字典中進行元數據更改。 SE更改期間無需獲取元數據鎖定，也不會touch表中的數據。
此更改也會影響LOCK = ...語義。無需為即時演算法指定LOCK。如果使用ALGORITHM = INSTANT，則LOCK不能設置為DEFAULT以外的任何其他值，否則會出現錯誤：
 
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 11, ALGORITHM=INSTANT, LOCK=NONE;
ERROR HY000: Incorrect usage of ALGORITHM=INSTANT and LOCK=NONE/SHARED/EXCLUSIVE
# ALGORITHM=INSTANT and LOCK=DEFAULT are OK though.
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 13, ALGORITHM=INSTANT, LOCK=DEFAULT;
如果將ALGORITHM = INSTANT設置為無法不支持的DDL，則會出現錯誤，如下所示。這裡的想法是不支持的情況下會直接失敗，而不會預設轉換並切換到幕後的另一種演算法。
 
ALTER TABLE t1 ALTER COLUMN i SET DEFAULT 12, DROP COLUMN j, ALGORITHM=INSTANT;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE

當前,Innodb的即時DDL支持如下操作

  • Change index option
  • Rename table (in ALTER way)
  • SET/DROP DEFAULT
  • MODIFY COLUMN
  • Add/drop virtual columns
  • Add columns(non-generated) – 我們稱之為即時DDL
你可以在一個語句中指定不止一個即時（instant）操作，這裡是一下即時（instant）操作的示例 
mysql> CREATE TABLE t1 (a INT, b INT, KEY(b));
Query OK, 0 rows affected (0.70 sec)

mysql> # Modify the index can be instant if it's a trivial change
mysql> ALTER TABLE t1 DROP KEY b, ADD KEY b(b) USING BTREE, ALGORITHM = INSTANT; 
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # Rename the table through ALTER TABLE can be instant
mysql> ALTER TABLE t1 RENAME TO t2, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.26 sec)

mysql> # SET DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b SET DEFAULT 100, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # DROP DEFAULT to a column can be instant
mysql> ALTER TABLE t2 ALTER COLUMN b DROP DEFAULT, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # MODIFY COLUMN can be instant
mysql> ALTER TABLE t2 ADD COLUMN c ENUM('a', 'b', 'c'), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t2 MODIFY COLUMN c ENUM('a', 'b', 'c', 'd', 'e'), ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # ADD/DROP virtual column can be instant
mysql> ALTER TABLE t2 ADD COLUMN (d INT GENERATED ALWAYS AS (a + 1) VIRTUAL), ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t2 DROP COLUMN d, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> # Do two operations instantly in the same statement
mysql> ALTER TABLE t2 ALTER COLUMN a SET DEFAULT 20, ALTER COLUMN b SET DEFAULT 200, ALGORITHM = INSTANT;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP TABLE t2;
Query OK, 0 rows affected (0.36 sec)
 

它的工作原理是什麼

我們面臨的問題是，在立即添加列後元數據發生更改後，如何解析頁面上的物理記錄？ 請註意，此處的物理記錄是指存儲在聚集索引的葉頁中的記錄。聚簇索引的現有二級索引甚至非葉頁（B樹的內部節點）都不會受到影響。   InnoDB有兩種主要的行格式，即冗餘行和緊湊行格式。行格式動態是compact的一個較小變體。壓縮及其派生的行格式從冗餘行格式中刪除了一些元數據，以節省空間。 由於這種“節省空間”的更改，當我們必須對頁面上物理行中的數據進行反序列化時，我們總是需要從內部元數據結構中查找元數據。 為了使即時添加列起作用，我們需要為頁面上的DYNAMIC和COMPACT行格式的物理記錄添加一些元數據。 REDUNDANT行格式不需要此附加元數據，因為列數已存儲在物理記錄中。   額外的信息與數據字典中的一些元數據一起保留在物理記錄中。 這與基於相同騰訊補丁的一些下游黑客的做法非常不同，後者在表空間的模糊和未使用的部分存儲類似的元數據。 我們認為，將元數據存儲在適當的數據字典表中並使其在事務上保持一致將使其更健壯且更自然。此新的元數據存儲在物理記錄中。 這個新的元數據包括一個存儲在info_bits中的標誌。 info_bits中的此新信息用於跟蹤是否在第一個即時ADD COLUMN之後創建記錄。 我們還使用info_bits跟蹤物理記錄中的欄位/列數。當表經歷第一個即時ADD COLUMN時的列數以及新添加的列的所有預設值都存儲在數據字典中。 這兩條信息存儲在數據字典表的se_private_data列中。   有了這些額外的信息，現在可以立即執行ADD COLUMN操作，而無需修改表中的任何行。如果沒有即時的ADD COLUMN，則表中的所有行將採用與以前相同的格式。 即時發出ADD COLUMN後，對該表的任何更新都將以新格式寫入行。從數據字典中查找預設值（如果有）。
在每個即時ADD COLUMN中，都會分別跟蹤新添加的列的預設值。這些列的預設值可以隨時更改。因此，在重建或截斷表之後，可以丟棄即時列數和預設值，此外，可以像以前一樣將表中的行更改為舊格式。 如果該表是分區表，則不同的分區可能具有不同數量的即時列，並且需要不同數量的預設值。 如果某些分區被重建，截斷或重新創建，則分區中的行也可以像以前一樣更改為舊格式。  
譯者註：
即時DDL的原理實現描述的確實比較晦澀，其原理下麵兩張圖大概也能表述出來，簡單說就是：相對原始的新增欄位就將整張表重建相比，instant加欄位的方式進修改元數據來提升性能。   以下截圖來自於：https://opensource.actionsky.com/20190620-mysql-add-column/，侵刪 1，非“即時”加欄位的過程：基於行的存儲規則發生變化之後（增加欄位），整個表的所有行都需要做一次重建（重新生成）

2，“即時”加欄位的過程：基於行的存儲規則發生變化之後（增加欄位），僅修改元數據
,
3，“即時”加欄位之後，查詢的處理過程。

4，“即時”加欄位後，新增數據的處理

 

如何觀察（Instant column） 用戶可以通過information_schema中的視圖觀察即時添加列的結果。更具體地說，一些新欄位將添加到information_schema.innodb_tables和information_schema.innodb_columns。 請註意，對於可以立即完成的其他操作，無需提供新的觀察狀態。請參見下麵的示例： 
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            0 |
+----------+---------+--------------+
1 row in set (0.22 sec)
 
mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
+----------+------+-------------+---------------+
2 rows in set (0.38 sec)
可以看到，在innodb_tables中引入了一個新的名為“ instant_cols”的列，該列代表即時列的數量， 而在innodb_columns中引入了兩個有關預設值的新列，分別名為“ has_default”和“ default_value”。 
mysql> ALTER TABLE t1 ADD COLUMN c INT, ADD COLUMN d INT DEFAULT 1000, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
 
mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+---------------+
| table_id | name | has_default | default_value |
+----------+------+-------------+---------------+
|     1065 | a    |           0 | NULL          |
|     1065 | b    |           0 | NULL          |
|     1065 | c    |           1 | NULL          |
|     1065 | d    |           1 | 800003e8      |
+----------+------+-------------+---------------+
4 rows in set (0.36 sec)
請註意，table_id不變。這不再是table的重建！正如我們所看到的，'instant_cols'現在設置為2，這意味著在第一個即時ADD COLUMN發生時表中有a列和b列。 在innodb_columns中記住c和d列的預設值。現在，如果has_default為1，則用戶可以知道是否立即添加了列。 此外，如果“ has_default”為1，則此列的預設值存儲在“ default_value”欄位中。d的default_value設置為值1000的內部二進位格式。 
mysql> ALTER TABLE t1 ADD COLUMN e VARCHAR(100) DEFAULT 'Hello MySQL!';
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%t1%';
+----------+---------+--------------+
| table_id | name    | instant_cols |
+----------+---------+--------------+
|     1065 | test/t1 |            2 |
+----------+---------+--------------+
1 row in set (0.03 sec)
 
mysql> SELECT table_id, name, has_default, default_value FROM information_schema.innodb_columns WHERE table_id = 1065;
+----------+------+-------------+--------------------------+
| table_id | name | has_default | default_value            |
+----------+------+-------------+--------------------------+
|     1065 | a    |           0 | NULL                     |
|     1065 | b    |           0 | NULL                     |
|     1065 | c    |           1 | NULL                     |
|     1065 | d    |           1 | 800003e8                 |
|     1065 | e    |           1 | 48656c6c6f204d7953514c21 |
+----------+------+-------------+--------------------------+
5 rows in set (0.36 sec)
又過一會兒再添加一列，table_id依舊次保持不變。 “ instant_cols”將保持不變，並且還會記住e列的預設值。

Instant column的副作用和權衡 由於即時ADD COLUMN不會再重建表格，因此會有一些副作用：
  • 在較舊的版本中，將預先檢查行大小，因此ADD COLUMN在開始時將失敗。但是，使用新的即時ADD COLUMN，行大小將僅在以後對行進行更新時進行檢查。
  • 在早期版本中，如果表或索引已損壞，則可以通過重建表來“修複”問題。使用即時添加列會帶來更多挑戰，我們正在尋找減輕這種情況的方法。
  Instant column的局限性 當前存在一些局限性：
  • 僅支持在一條語句中添加列，也就是說，如果同一條語句中還有其他非INSTANT操作，則無法立即完成
  • 僅支持最後添加列，不支持在現有列中間
  • 不支持很少使用的COMPRESSED行格式
  • 不支持已經有全文索引的表
  • 不支持DD表空間中的任何表（？？？）
  • 不支持臨時表（隨COPY一起提供）

 如何用原始的方式增加欄位 儘管支持即時添加列，但用戶仍然可以使用舊的方式（非instant）添加列。 也就是說，如果用戶打算通過重建表或複製行來添加列，那麼他們仍然可以使用ALGORITHM = INPLACE / COPY發出ALTER TABLE，甚至可以指定FORCE關鍵字。通過這種方式，添加列將按照傳統方式進行。

總結 MySQL現在支持一種稱為INSTANT的ALTER TABLE的新演算法。與此相關的是，最令人興奮的部分是，現在可以在InnoDB中立即完成ADD COLUMN（最後一次添加）操作，因此不再是用戶的難題。 可以輕鬆觀察到新功能。請嘗試使用此令人興奮的新功能，期待反饋使用中遇到的任何問題！
感謝您使用MySQL！

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • linux搭建ntp伺服器-添加交換機客戶端，windows客戶端
    [root@localhost ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [root@localhost ~]# rpm -qa |grep ntp ntp-4.2.6p5-15.el6.centos.x86_64 fontpack ...
  • 053.集群管理-Helm部署及使用
    一 Helm概述 1.1 Helm介紹 Helm 是 Kubernetes 的軟體包管理工具。包管理器類似 Ubuntu 中使用的apt、Centos中使用的yum 或者Python中的 pip 一樣，能快速查找、下載和安裝軟體包。通常每個包稱為一個Chart，一個Chart是一個目錄（一般情況下會 ...
  • linux centos7搭建mysql-5.7.29
    1. 下載mysql 1.1 下載地址 https://downloads.mysql.com/archives/community/ 1.2 版本選擇 2. 管理組及目錄許可權 2.1 解壓mysql tar -zxf mysql-5.7.29-linux-glibc2.12-x86_64.tar. ...
  • 當你買了台新的ubuntu伺服器(1)
    安裝各種伺服器軟體 nginx 安裝完成之後，直接使用ip地址訪問伺服器，就可以看到nginx的預設歡迎頁面了。 如果安裝報錯比如80埠占用，說明此前安裝了其它網路伺服器，建議全都關掉再安裝，並且讓nginx獨占80埠作為反向代理伺服器 安裝完成之後配置反向代理，使用whereis nginx命 ...
  • 【漫畫】JAVA併發編程 J.U.C Lock包之ReentrantLock互斥鎖
    在如何解決原子性問題的最後，我們賣了個關子，互斥鎖不僅僅只有synchronized關鍵字，還可以用什麼來實現呢？ J.U.C包中還提供了一個叫做Locks的包，我好歹英語過了四級，聽名字我就能馬上大聲的說：Locks包必然也可以用作互斥！ ReentrantLock 我們可以通過從具體到抽象的方法 ...
  • Windows遠程登錄MySQL
    我的MySQL安裝在Linux上，如果我要想在Windows上連接linux上的MySQL，可以使用一些工具，如Navicat和SQLyog。我在這裡用SQLyog演示一下Windows遠程連接MySQL。 第一次連接MySQL 首先安裝SQLyog，這裡就不演示了。打開SQLyog，在菜單欄點擊【 ...
  • MySQL 8.0中與DBA和運維人員相關的10大特性（譯）
    原文地址:http://lefred.be/content/top-10-mysql-8-0-features-for-dbas-ops/ 臨時表的改進 MySQL 5.7中，所有內部臨時表都是在名為“ibtmp1”的惟一共用表空間中創建的。此外，臨時表的元數據也將存儲在記憶體中(不再存儲在.frm文 ...
  • 在Linux系統中通過rpm包安裝MySQL
    操作系統：CentOS 7 MySQL版本：5.7.29 通過rpm包方式安裝就像去服裝店買衣服，衣服的大小、顏色等樣式都是設計好的，我們只要買來就能穿，方便快捷。 一、下載rpm包 打開MySQL官網的下載頁面：https://downloads.mysql.com/archives/commun ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類