MySQL修改表結構到底會不會鎖表？

白菜1031發表於2021-12-21
MySql

〇、關於DDL、DML和DCL

DDL（Data Definition Languages）語句：資料定義語言，這些語句定義了不同的資料段、資料庫、表、列、索引等資料庫物件的定義。
常用的語句關鍵字主要包括 create、drop、alter 等。

DML（Data Manipulation Language）語句：資料操縱語句，用於新增、刪除、更新和查詢資料庫記錄，並檢查資料完整性。
常用的語句關鍵字主要包括 insert、delete、udpate 和 select 等。(增刪改查）

DCL（Data Control Language）語句：資料控制語句，用於控制不同資料段直接的許可和訪問級別的語句。這些語句定義了資料庫、表、欄位、使用者的訪問許可權和安全級別。
主要的語句關鍵字包括 grant、revoke 等。

一、DDL 實現方式

在 MySQL5.6 版本以前，執行 DDL 主要有兩種方式：Copy 方式 和 In-place 方式

Copy 方式執行DDL操作

  1. 建立與原表結構定義完全相同的臨時表
  2. 為原表加MDL（meta data lock，後設資料鎖）鎖，禁止對錶中資料進行增刪改，允許查詢
  3. 在臨時表上執行DDL語句
  4. 按照主鍵 ID 遞增的順序，把資料一行一行地從原表裡讀出來再插入到臨時表中
  5. 升級原表上的鎖，禁止對原表中資料進行讀寫操作
  6. 將原表刪除，將臨時表重新命名為原表名，DDL操作完成

In-place 方式執行DDL操作

In-place 方式 又稱為 Fast Index Creation 。與 Copy 方式相比，In-place方式不復制資料，因此大大加快了執行速度。但是這種方式僅支援對二級索引進行新增、刪除操作，而且與Copy方式一樣需要全程鎖表。下面以新增索引為例，簡單介紹In-place方式的實現流程：

  1. 建立新索引的資料字典
  2. 為原表加MDL（meta data lock，後設資料鎖）鎖，禁止對錶中資料進行增刪改，允許查詢
  3. 按照聚簇索引的順序，查詢資料，找到需要的索引列資料，排序後插入到新的索引頁中
  4. 等待開啟當前表的所有隻讀事務提交
  5. 建立索引結束

In-place 方式執行DDL操作

MySQL5.6 版本之後加入了 Online DDL 新特性，用於支援DDL執行期間DML語句的並行操作，提高資料庫的吞吐量。
與 Copy 方式和 In-place 方式相比，Online 方式在執行DDL的時候可以對錶中資料進行讀寫操作。
Online DDL可以有效改善DDL期間對資料庫的影響：

  • Online DDL期間，查詢和DML操作在多數情況下可以正常執行，對錶格的鎖時間也會大大減少，儘可能的保證資料庫的可擴充套件性；
  • 允許 In-place 操作的 DDL，避免重建表格佔用過多磁碟IO及CPU資源，減少對資料庫的整體負荷，使得在DDL期間，能夠維持資料庫的高效能及高吞吐量；
  • 允許 In-place 操作的 DDL，比需要COPY到臨時檔案的操作要更少佔用buffer pool，避免以往DDL過程中效能的臨時下降，因為以前需要拷貝資料到臨時表，這個過程會佔用到buffer pool ，導致記憶體中的部分頻繁訪問的資料會被清理出去。

Online DDL 實現實質上也可以分為2種方式：Copy 方式和 In-place 方式：

  • 對於不支援Online DDL的 SQL，則採用 Copy 方式，比如刪除主鍵，修改列資料型別，變更表字符集等。這些操作都會導致記錄格式發生變化，無法通過簡單的全量+增量的方式實現Online DDL。

  • 對於支援Online DDL的 SQL，則採用 In-place 方式，MySQL 內部以“是否修改行記錄格式”為標準，又將 In-place 方式分為兩類：

    • 如果修改了行記錄格式，則需要重建表，比如 新增主鍵，新增、刪除欄位，修行格式ROW_FORMAT，OPTIMIZE優化表 等操作，這種方式被稱為 rebuild方式。
    • 如果沒有修改行記錄格式，僅修改表的後設資料，則不需要重建表，比如 新增、刪除、重新命名二級索引，設定、刪除欄位的預設值，重新命名欄位，重新命名錶 等操作，這種方式被稱為 no-rebuild方式。

Onlive_DDL_實現方式

更多詳情可查閱官方文件：https://dev.mysql.com/doc/ref...

三、Online DDL 實現流程

Online DDL 主要包括3個階段，Prepare階段，Execute階段，Commit階段。
下面將主要介紹Online DDL執行過程中三個階段的流程。

Prepare 階段

  • 持有 EXCLUSIVE-MDL 鎖，禁止DML語句讀寫
  • 根據DDL型別，確定執行方式(Copy,Online-rebuild,Online-no-rebuild)
  • 建立新的 frm 和 ibd 臨時檔案（ibd臨時檔案僅rebuild型別需要）
  • 分配 row_log 空間，用來記錄 DDL Execute 階段產生的DML操作（僅rebuild型別需要）

Execute 階段

  • 降級 EXCLUSIVE-MDL 鎖，允許DML語句讀寫
  • 掃描原表主鍵以及二級索引的所有資料頁，生成B+樹，儲存到臨時檔案中
  • 將 DDL Execute 階段產生的DML操作記錄到 row_log（僅rebuild型別需要）

Commit 階段

  • 升級到 EXCLUSIVE-MDL 鎖，禁止DML語句讀寫
  • 將 row_log 中記錄的DML操作應用到臨時檔案，得到一個邏輯資料上與原表相同的資料檔案（僅rebuild型別需要）
  • 重新命名 frm 和 idb 臨時檔案，替換原表，將原表檔案刪除
  • 提交事務（刷事務的redo日誌），變更完成

由上面的流程可知，Prepare階段和Commit階段都禁止讀寫，只有Execute允許讀寫，那為什麼說Online DDL 方式在執行過程中可以對錶中資料進行讀寫操作？
其實是因為Prepare和Commit階段相對於Execute階段時間特別短，因此基本可以認為是全程允許讀寫的。
Prepare階段和Commit階段都禁止讀寫，主要是為了保證資料一致性。

四、Online DDL 的語法與可選引數

ALTER TABLE …. , ALGORITHM[=]{DEFAULT|INPLACE|COPY}, LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

例句：

ALTER TABLE tablename DROP COLUMN age,ALGORITHM=INPLACE,LOCK=DEFAULT;

ALGORITHM 選項

COPY：使用 Copy 方式 執行DDL操作，DDL 執行過程中，不允許DML操作。
INPLACE：使用 In-place 方式 執行DDL操作，DDL 執行過程中，允許DML操作。
DEFAULT：預設選項，根據DDL的操作型別，自動選擇DDL執行方式，優先選擇 In-place 方式，不滿足條件時選擇 Copy 方式。

LOCK 選項

EXCLUSIVE：對整個表新增排他鎖（X鎖），不允許DML操作
SHARED：對整個表新增共享鎖（S鎖），允許查詢操作，但是不允許資料變更操作
NONE：不對錶加鎖，既允許查詢操作，也支援資料變更操作，即允許所有的 DML 操作，該模式下併發最好
DEFAULT：預設選項，根據DDL的操作型別，最小程度的加鎖，儘可能支援DML操作

相關文章