Mysql Online DDL的使用詳解

Posted on by

正文

Online DDL在MySQL 5.6才開始支持的，在5.5及之前版本，使用alter table/create index等命令進行表結構修改操作均會鎖表，這在生產環境上明顯是不可接受的。

在MySQL 5.7，Online DDL在性能和穩定性上不斷得到優化，性能有顯著優勢，且對業務負載影響小，停寫時間可控，相對pt-osc/gh-ost來說，無需安裝第三方依賴包，同時支持Inplace算法的Online DDL，由於無需拷表，所需磁盤空間也更小。

先來看一個常見的DDL語句：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

其中，LOCK描述瞭DDL期間運行的並發程度，ALGORITHM描述瞭DDL的實現方式

LOCK參數

  1. LOCK=NONE:允許並發的查詢和DML操作
  2. LOCK=SHARED:允許並發的查詢，但阻塞DML操作
  3. LOCK=DEFAULT: 由系統決定，允許盡可能多的並發性(並發查詢、DML或兩者)。如果省略LOCK子句相當於指定LOCK=DEFAULT
  4. LOCK=EXCLUSIVE:阻塞並發查詢和DML操作。

ALGORITHM參數

  1. ALGORITHM=COPY：采用拷表方式進行表變更，與pt-osc/gh-ost類似；
  2. ALGORITHM=INPLACE：僅需要進行引擎層數據改動，不涉及Server層；

COPY TABLE流程

  1. 首先建立臨時表，表結構為ALTAR TABLE更改後的結構
  2. 將原表中數據導入到臨時表（server層創建臨時表，會有顯示的IBD文件）
  3. 刪除原表
  4. 將臨時表rename為原來的表名

同時這一過程中，為瞭保持數據的一致性，中間復制數據時（Copy Table）全程鎖表隻讀，如果有寫請求進來將無法提供服務，將導致連接數爆張。

IN-PLACE流程

  1. 建立一個臨時文件，掃描原表主鍵的所有數據頁
  2. 用數據頁中原表記錄生成B+樹，存儲到臨時文件中（innodb_temp_data_file_path臨時表空間下創建臨時文件）
  3. 生成臨時文件的過程中，將所有對原表的操作記在一個日志文件(rowlog)中
  4. 臨時文件生成後，將日志文件中的操作應用到臨時文件，得到一個輯數據上與原表相同
  5. 數據文件（日志文件記錄和重放操作）
  6. 用臨時文件替換原表數據文件

這一過程中，alter 語句在啟動的時候獲取MDL寫鎖，但是這個寫鎖在真正拷貝數據之前就退化成讀鎖，也就是說在最耗時的copy數據到臨時文件的過程中，原表是可以進行dml操作的，僅僅會在最後的新舊表切換階段加鎖，這個rename的時間就非常快瞭。

允許並發DML的DDL操作

  • 創建/新增二級索引
  • 重命名二級索引
  • 刪除二級索引
  • 改變索引類型(USING {BTREE | HASH})
  • 添加主鍵（expensive cost）
  • 刪除主鍵並增加另一個（expensive cost）(ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;)
  • 新增列 （expensive cost）
  • 刪除列 （expensive cost）
  • 重命名列
  • 列重新排序 （expensive cost）
  • 改變列默認值
  • 刪除列默認值
  • 改變列自增值
  • 設置列屬性null/not null （expensive cost）
  • 修改枚舉或集合列的定義
  • Change ROW_FORMAT
  • Change key block size

標記為expensive cost的操作雖然允許OnlineDDL,但本身對服務器IO,CPU都會造成較高負擔，同時會導致復制阻塞，造成另一種形式的從庫復制延遲，所以如果是大表，建議業務低峰期執行

不允許並發DML的DDL操作

  • 添加全文索引
  • 添加空間索引
  • 刪除主鍵
  • 改變列數據類型
  • 添加自增列（新增列->變為自增列）
  • 變更表字符集
  • 修改數據類型長度
    • 特例：varchar字符長度從10變更到小於255 采用inplace方式不會鎖表；從255變更到10會鎖表；

以上就是Mysql Online DDL的使用詳解的詳細內容，更多關於Mysql Online DDL的使用的資料請關註WalkonNet其它相關文章！

推薦閱讀：