MySQL Online DDL知識點有哪些

發布時間：2021-11-12 14:35:01 來源：億速云閱讀：222 作者：iii 欄目：MySQL數據庫

這篇文章主要介紹“MySQL Online DDL知識點有哪些”，在日常操作中，相信很多人在MySQL Online DDL知識點有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL Online DDL知識點有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

一：最初alter的復雜過程。

MySQL 5.5之前除了MySQL 5.1的 innodb plugin之外，對于索引的添加或刪除這類DDL操作，MySQL數據庫的操作過程為如下：

（1）首先創建新的臨時表，表結構通過命令ALTAR TABLE新定義的結構

（2）然后把原表中數據導入到臨時表（不能讀和寫）

（3）刪除原表

（4）最后把臨時表重命名為原來的表名

上述過程我們不難發現，若我們對一張大表進行索引的添加或者刪除，需要很長的時間，致命的是若有大量的訪問請求，意味著無法提供服務，5.5已經過時了，大家還是多關注5.6和5.7吧

二：快速索引創建：

官方文檔中說明

In MySQL 5.5 and higher, or in MySQL 5.1 with the InnoDB Plugin, creating and dropping secondary indexes does not copy the contents of the entire table, making this operation much more efficient than with prior releases.

翻譯：在mysql 5.5或者更高版本，或者是mysql 5.1的InnoDB Plugin中，創建和刪除二級索引不需要復制整個表的數據來創建臨時表了，和之前的版本相比這類操作變得更加高效了；

innodb存儲引擎從1.0.x版本開始支持Fast index Creation（快速索引創建）。簡稱FIC。對于輔助索引的創建，會對創建索引的表加一個S鎖。在創建的過程中，不需要重建表，因此速度有明顯提升。對于刪除輔助索引innodb存儲引擎只需要更新內部視圖，并將輔助索引的空間標記為可用，同時刪除MySQL 數據庫內部視圖上對該表的索引定義即可。由于在創建輔助索引時加的是S鎖，所以在這過程中只能對該表進行讀操作，若有事務需要對該表進行寫操作，那么數據庫服務同樣不可用。需要注意的是，FIC方式只限定于輔助索引，對于主鍵的創建和刪除同樣需要重建一張表；快速索引創建語句和正常alter語句沒有什么不同；

三：online ddl （注意是針對innodb引擎而言的）

mysql 5.6以及以后的版本中，對于大多數我們日常常用的DDL而言，是可以做到在線DDL的。

通常情況下，可以使用默認的語法來進行在線DDL，但你也可以通過選項來改變DDL的行為，有兩個選項

LOCK=

ALGORITHM=[INPLACE|COPY]

關于這兩個參數的介紹：

ALGORITHM=INPLACE，可以避免重建表帶來的IO和CPU消耗，保證ddl期間依然有良好的性能和并發。

ALGORITHM=COPY，需要拷貝原始表，所以不允許并發DML寫操作，可讀。這種copy方式的效率不如 inplace ，因為前者需要記錄undo和redo log，而且因為臨時占用buffer pool引起短時間內性能受影響。

LOCK 選項控制是否鎖表，根據不同的DDL操作類型有不同的表現：默認mysql盡可能不去鎖表，但是像修改主鍵這樣的昂貴操作不得不選擇鎖表，以下是具體的值的意義：

（1）NONE，執行索引創建或者刪除操作時，對目標表不添加任何鎖，即事務仍然可以進行讀寫操作，不會收到阻塞，該模式可以獲得最大的并發。

（2）SHARE，和Fast index Creation類似，執行索引創建或刪除操作時，對目標表加一個S鎖。對于并發讀事務，依然可以執行。但是遇到寫事務，將會發生等待操作，如果存儲引擎不支持SHARE模式，將返回一個錯誤信息。

（3）EXCLUSIVE，執行索引創建或刪除時，對目標表加上一個X鎖。讀寫事務均不能進行。會阻塞所有的線程。這和COPY方式類似，但是不需要像COPY方式那樣創建一張臨時表。

（4）DEFAULT，該模式首先會判斷當前操作是否可以使用NONE模式，若不能，則判斷是否可以使用SHARE模式，最后判斷是否可以使用EXCLUSIVE模式。也就是說DEFAULT會通過判斷事務的最大并發性來判斷執行DDL的模式。

online ddl的語句：

alter table | ALGORITHM [=] {DEFAULT|INPLACE|COPY}， | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

那么如何開啟online ddl？

由參數old_alter_table控制，

old_alter_table=0，不啟用舊的copy the table 的模式來進行ddl操作；

mysql 5.6默認 old_alter_table=0 ，就開啟了online ddl，可以使用默認的語法來進行在線DDL，

(題外話：關于set old_alter_table=0; 和 set global old_alter_table=0;的區別。前者只影響當前session，后者作為全局的修改方式，只會影響修改之后打開的session；注意后者不能改變當前session；）

實驗一：

1.1

session 1

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.45 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | ON |

+-----------------+-------+

1 row in set (0.00 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

session 2 執行dml操作，被阻塞。

mysql> update v_member_info set phone='1771002222' where id=1;

查看進程，發現果然是用舊的copy the table 的模式來進行ddl操作，然后update操作不能執行，等待一個metadata lock ；

mysql> show processlist;

+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+------------------------------------------------------------+

1.2

session 1

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | OFF |

+-----------------+-------+

1 row in set (0.15 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 0 rows affected, 2 warnings (13.42 sec)

Records: 0 Duplicates: 0 Warnings: 2

session 2 執行dml操作，并沒有被阻塞，

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.02 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> show processlist;

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------+

3 rows in set (0.07 sec)

實驗一表明：當old_alter_table=0的時候，就表示不用舊的那種copy the table 的模式來進行ddl操作，也就是開啟了online ddl。并且開啟online ddl之后，正常的alter命令添加索引，不會阻塞dml操作。由于不需要創建臨時表，online ddl效率很高；

實驗二：當old_alter_table=1的時候，ALGORITHM=INPLACE還有效嗎？

mysql> set old_alter_table=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | ON |

+-----------------+-------+

1 row in set (0.00 sec)

session 1 ：注意添加ALGORITHM =INPLACE參數后面有個逗號。

mysql> alter table v_member_info ALGORITHM =INPLACE,add index inde_register (register_ip) ;

session 2 并沒有阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

Query OK, 0 rows affected (0.40 sec)

Rows matched: 1 Changed: 0 Warnings: 0

查看進程，發現沒有使用copy temp table的方式執行ddl

mysql> show processlist;

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+----------------+---------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

實驗二：結果表明，在mysql5.6中，當當old_alter_table=1的時候，可以使用ALGORITHM=INPLACE來影響ddl的執行方式，也就是說ALGORITHM=INPLACE的參數的優先級高，依舊按著online ddl的方式創建索引，不建立臨時表（盡管old_alter_table=1）。lock參數也肯定一樣，實驗2本身就沒什么意義，因為沒用人會把old_alter_table設置成1，而不用online ddl新特性。

實驗三：

session 1 開啟 online ddl，然后執行用copy的方式執行添加索引的操作；

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'old_alter_table';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| old_alter_table | OFF |

+-----------------+-------+

1 row in set (0.03 sec)

mysql> alter table v_member_info ALGORITHM =copy ,add index inde_register (register_date) ;

session 2阻塞dml操作；

mysql> update v_member_info set phone='1771002222' where id=1;

mysql> show processlist; 查看進程發現確實在等待Waiting for table metadata lock

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------------------------+

3 rows in set (0.06 sec)

實驗三證明開啟online ddl之后，也可以使用copy to tmp table的方式創建索引，依舊會阻塞其他的dml操作。但是應該沒有這么無聊的dba吧；

實驗四：驗證myisam引擎是否可以 online ddl

session 1

mysql> alter table v_member_info engine=myisam;

Query OK, 1804082 rows affected (1 min 50.33 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

mysql> set old_alter_table=0;

Query OK, 0 rows affected (0.03 sec)

mysql> alter table v_member_info add index inde_register (register_ip);

Query OK, 1804082 rows affected (1 min 57.77 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

session 2 被阻塞

mysql> update v_member_info set phone='1771002222' where id=1;

session 3 查看進程狀態，會看到添加索引的過程，剛開始copy to tmp table（創建臨時表）

mysql> show processlist;

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

+----+------+-----------+----------+---------+------+---------------------------------+---------------------------------------------------------------------+

3 rows in set (0.00 sec)

實驗四證明：mysql 5.6中myisam引擎是不支持online ddl的，添加索引依舊會創建臨時表，阻塞其他session的dml操作；

關于online ddl的原理層面：

innodb存儲引擎實現Online DDL的原理是在執行創建或者刪除操作同時，將INSERT,UPDATE，DELETE這類DML操作日志寫入到一個緩存中，待完成索引創建后再將重做應用到表上，以此達到數據的一致性。這個緩存的大小由參數innodb_online_alter_log_max_size控制，默認大小為128MB。

需要注意的是：如果待更新的表比較大，并且創建過程中有大量的寫事務，如果遇到innodb_online_alter_log_max_size的空間不能存放日志時，會拋出相應的錯誤，如果遇到這個錯誤，我們可以調大該參數，以此獲得更大的日志緩存空間，或者我們可以設置ALTER TABLE的lock模式為SHARE，這樣在執行過程中不會有寫操作事務發生。因此不需要進行DML日志的記錄。

但是有一點需要說明，無論任何模式下，online ddl開始之前都需要一個短時間排它鎖(exclusive)來準備環境，所以alter命令發出后，會首先等待該表上的其它操作完成，在alter命令之后的請求會出現等待waiting meta data lock。同樣在ddl結束之前，也要等待alter期間所有的事務完成，也會堵塞一小段時間。所以盡量在ALTER TABLE之前確保沒有大事務在執行，否則一樣出現連環鎖表。你可以通過觀察執行完DDL后的輸出： XX rows affected，來判斷是IN-PLACE 還是COPY數據，為0的話就是inplace。

copy和inplace方式的具體過程（添加index為例）

copy方式

(1).新建帶索引的臨時表

(2).鎖原表，禁止DML，允許查詢

(3).將原表數據拷貝到臨時表(無排序，一行一行拷貝)

(4).進行rename，升級字典鎖，禁止讀寫

(5).完成創建索引操作

inplace方式（整個過程相對較快）

(1).新建索引的數據字典

(2).鎖表，禁止DML，允許查詢（這個過程非常短暫）

(3).讀取聚集索引，構造新的索引項，排序并插入新索引

(4).等待打開當前表的所有只讀事務提交

(5).創建索引結束

online ddl實現

online方式實質也包含了copy和inplace方式，對于不支持online的ddl操作采用copy方式，比如修改列類型，刪除主鍵，修改字符集等，這些操作都會導致記錄格式發生變化，無法通過簡單的全量+增量的方式實現online；對于inplace方式，mysql內部以“是否修改記錄格式”為基準也分為兩類，一類需要重建表(重新組織記錄)，比如optimize table、添加索引、添加/刪除列、修改列NULL/NOT NULL屬性等；另外一類是只需要修改表的元數據，比如刪除索引、修改列名、修改列默認值、修改列自增值等。Mysql將這兩類方式分別稱為rebuild方式和no-rebuild方式

2. 實現過程（注意這里的rebuild是指從新組織記錄，是相對于只修改表的元數據而言的）

online ddl主要包括3個階段，prepare階段，ddl執行階段，commit階段，rebuild(重新組織記錄)方式比no-rebuild（只需要修改表的元數據）方式實質多了一個ddl執行階段，prepare階段和commit階段類似。下面將主要介紹ddl執行過程中三個階段的流程。
Prepare階段:
1）創建新的臨時frm文件(與InnoDB無關)
2）持有EXCLUSIVE-MDL鎖，禁止讀寫
3）根據alter類型，確定執行方式(copy,online-rebuild,online-norebuild)

假如是Add Index，則選擇online-norebuild即INPLACE方式
1）更新數據字典的內存對象
2）分配row_log對象記錄增量(僅rebuild類型需要)
3）生成新的臨時ibd文件(僅rebuild類型需要)
ddl執行階段:
1）降級EXCLUSIVE-MDL鎖，允許讀寫
2）掃描old_table的聚集索引每一條記錄rec
3）遍歷新表的聚集索引和二級索引，逐一處理
4）根據rec構造對應的索引項
5）將構造索引項插入sort_buffer塊排序
6）將sort_buffer塊更新到新的索引上
7）記錄ddl執行過程中產生的增量(僅rebuild類型需要)
8）重放row_log中的操作到新索引上(no-rebuild數據是在原表上更新的)
9）重放row_log間產生dml操作append到row_log最后一個Block
commit階段:
1）當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL鎖
2）重做row_log中最后一部分增量
3）更新innodb的數據字典表
4）提交事務(刷事務的redo日志)
5）修改統計信息
6）rename臨時idb文件，frm文件
7）變更完成

從官方提供的這個表格來看，還是有很多操作不支持完全的在線DDL，包括增加一個全文索引，修改列的數據類型，刪除一個主鍵，修改表的字符集等。

Operation	In-Place?	是否重建表	允許并發DML	只修改元數據?	Notes
CREATE INDEX, ADD INDEX	Yes*	No*	Yes	No	Restrictions apply for FULLTEXT indexes; see next row.
ADD FULLTEXT INDEX	Yes*	No*	No	No	Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	Performed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXTindexes.
Set column default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-increment value	Yes	No	Yes	No*	Modifies a value stored in memory, not the data file.
Add foreign key constraint	Yes*	No	Yes	Yes	The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.
Drop foreign key constraint	Yes	No	Yes	Yes	foreign_key_checks can be enabled or disabled.
Rename column	Yes	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name.
Add column	Yes	Yes	Yes*	No	Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.
Drop column	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change ROW_FORMATproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change KEY_BLOCK_SIZEproperty	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make column NULL	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column NOT NULL	Yes*	Yes*	Yes	No	Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data type	No	Yes	No	No	Only supports ALGORITHM=COPY
Add primary key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.
Drop primary key and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop primary key	No	Yes	No	No	Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
Convert character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with FORCE option	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
“null” rebuild using ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.

從表看出，In-Place為No，DML一定是No，說明ALGORITHM=COPY一定會發生拷貝表，只讀。

ALGORITHM=INPLACEE也要可能發生拷貝表，但可以并發DML:

添加、刪除列，改變列順序
添加或刪除主鍵
改變行格式ROW_FORMAT和壓縮塊大小KEY_BLOCK_SIZE
改變列NULL或NOT NULL
優化表OPTIMIZE TABLE
強制 rebuild 該表

不允許并發DML的情況有：修改列數據類型、刪除主鍵、變更表字符集，即這些類型操作ddl是不能online的。

另外，更改主鍵索引與普通索引處理方式是不一樣的，主鍵即聚集索引，體現了表數據在物理磁盤上的排列，包含了數據行本身，需要拷貝表；而普通索引通過包含主鍵列來定位數據，所以普通索引的創建只需要一次掃描主鍵即可，而且是在已有數據的表上建立二級索引，更緊湊，將來查詢效率更高。

修改主鍵也就意味著要重建所有的普通索引。刪除二級索引更簡單，修改InnoDB系統表信息和數據字典，標記該索引不存在，標記所占用的表空間可以被新索引或數據行重新利用。

到此，關于“MySQL Online DDL知識點有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

MySQL Online DDL知識點有哪些

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

MySQL Online DDL知識點有哪些

猜你喜歡

最新資訊

相關推薦

相關標簽