MySQL8.0中Online DDL也要在業務低峰期執行

Mysql資料庫的安裝對於開發者來說，是我們必然會面對的問題，它的安裝過程其實並不複雜，並且網路上的安裝教程也非常多，但是對於新手來說，各種不同形式的安裝教程，又給新手們帶來了要選擇哪種方式進行安裝的難題，而且很多時候按照教程也沒有能夠安裝成功，安裝過程出現各種各樣的錯誤。下麵記錄了我在Linu... ...

一、背景

MySQL從5.6開始引入了Online DDL，alter操作不再阻塞dml。在MySQL 8.0中，針對Online DDL做了進一步優化，alter table加列操作支持INSTANT演算法，意思就是使用這個演算法進行加列操作只需要修改表的元數據信息，操作瞬間就完成了。在MySQL 8.0.30以後，instant演算法支持加列加到表的任一位置，並且也支持刪列、重命名錶等DDL操作。實際DDL中支持Online DDL的操作預設都會使用 ALGORITHM=INSTANT。

二、問題

那麼既然現在MySQL的DDL這麼快，我們是不是隨便什麼時候都可以去資料庫中對錶進行DDL呢？其實不是的，即使是Online DDL也要在業務低峰期進行。如果在對錶進行Online DDL的時候剛好這個表有個慢查詢在執行，那麼DDL語句將等待這個查詢的元數據鎖（metadata_lock），後續對這個表的所有DML語句都將被這個DDL阻塞，進而很容易造成連環堵塞和CPU飆升的狀況，對業務系統產生極大的影響。

三、實驗

下麵，將實際演示一下Online DDL引發的阻塞問題：

會話A：開啟一個事務，執行一條select不提交，那這個事務將一直持有表notest的元數據鎖。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.notest where id=1;
Empty set (0.00 sec)

會話B：對錶進行加列的DDL操作，可以看到該操作被堵塞，其實就是在等待會話A的元數據鎖。

mysql> alter table test.notest add age int;

會話C、會話D：再開啟兩個會話，對該表進行簡單的查詢，該查詢也在等待鎖，沒有返回結果。

mysql> select * from test.notest;

查看processlist，可以看到ddl和dml都在等待表的元數據鎖：

mysql> show processlist;
+-------+-----------------+--------------------+------+------------------+-------+-----------------------------------------------------------------+-------------------------------------+
| Id    | User            | Host               | db   | Command          | Time  | State                                                           | Info                                |
+-------+-----------------+--------------------+------+------------------+-------+-----------------------------------------------------------------+-------------------------------------+
|     5 | event_scheduler | localhost          | NULL | Daemon           | 15986 | Waiting on empty queue                                          | NULL                                |
|    33 | repl            | 10.2.111.193:33644 | NULL | Binlog Dump GTID | 15964 | Source has sent all binlog to replica; waiting for more updates | NULL                                |
| 17805 | root            | localhost          | test | Query            |    64 | Waiting for table metadata lock                                 | alter table test.notest add age int |
| 17814 | root            | localhost          | test | Sleep            |   346 |                                                                 | NULL                                |
| 17973 | root            | localhost          | NULL | Sleep            |   368 |                                                                 | NULL                                |
| 18370 | root            | localhost          | NULL | Query            |    42 | Waiting for table metadata lock                                 | select * from test.notest           |
| 18393 | root            | localhost          | NULL | Query            |    24 | Waiting for table metadata lock                                 | select * from test.notest           |
| 18418 | root            | localhost          | NULL | Query            |     0 | init                                                            | show processlist                    |
+-------+-----------------+--------------------+------+------------------+-------+-----------------------------------------------------------------+-------------------------------------+
8 rows in set (0.00 sec)

查看元數據鎖監控表performance_schema.metadata_locks表信息，可以看到當前資料庫中存在的元數據鎖以及元數據鎖的對象和鎖類型（在MySQL中，為了提高資料庫的併發度，元數據鎖被細分為了11種類型）。可以看到DDL語句給錶帶來的元數據鎖類型為EXCLUSIVE，元數據EXCLUSIVE鎖被持有期間任何其他的元數據鎖都不能被授予，所以就阻塞了後續對錶的所有DML操作，也包括select。

mysql> select * from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA      | OBJECT_NAME    | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE           | LOCK_DURATION | LOCK_STATUS | SOURCE             | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
| TABLE       | test               | notest         | NULL        |       140139226332208 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6085  |           18049 |              5 |
| GLOBAL      | NULL               | NULL           | NULL        |             132890928 | INTENTION_EXCLUSIVE | STATEMENT     | GRANTED     | sql_base.cc:5476   |           17881 |             21 |
| BACKUP LOCK | NULL               | NULL           | NULL        |             123469776 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5483   |           17881 |             21 |
| SCHEMA      | test               | NULL           | NULL        |             125839424 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | sql_base.cc:5463   |           17881 |             21 |
| TABLE       | test               | notest         | NULL        |             125839520 | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6085  |           17881 |             21 |
| TABLESPACE  | NULL               | test/notest    | NULL        |             130194048 | INTENTION_EXCLUSIVE | TRANSACTION   | GRANTED     | lock.cc:808        |           17881 |             21 |
| TABLE       | test               | #sql-5246_458d | NULL        |             124845680 | EXCLUSIVE           | STATEMENT     | GRANTED     | sql_table.cc:17024 |           17881 |             21 |
| TABLE       | test               | notest         | NULL        |             126124176 | EXCLUSIVE           | TRANSACTION   | PENDING     | mdl.cc:3754        |           17881 |             22 |
| TABLE       | test               | notest         | NULL        |       140138743169920 | SHARED_READ         | TRANSACTION   | PENDING     | sql_parse.cc:6085  |           18446 |              3 |
| TABLE       | performance_schema | metadata_locks | NULL        |       140138825181536 | SHARED_READ         | TRANSACTION   | GRANTED     | sql_parse.cc:6085  |           17890 |             26 |
| TABLE       | test               | notest         | NULL        |       140139414229984 | SHARED_READ         | TRANSACTION   | PENDING     | sql_parse.cc:6085  |           18469 |              3 |
+-------------+--------------------+----------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+
11 rows in set (0.00 sec)

同時，可以查詢sys.schema_table_lock_waits視圖，該視圖顯示了當前元數據鎖的鎖等待信息。可以看到等待和阻塞的會話ID，並且kill掉阻塞會話的語句也直接在sql_kill_blocking_connection這一列給出來了，非常方便。

mysql> select * from sys.schema_table_lock_waits;
+---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+
| object_schema | object_name | waiting_thread_id | waiting_pid | waiting_account | waiting_lock_type | waiting_lock_duration | waiting_query                       | waiting_query_secs | waiting_query_rows_affected | waiting_query_rows_examined | blocking_thread_id | blocking_pid | blocking_account | blocking_lock_type | blocking_lock_duration | sql_kill_blocking_query | sql_kill_blocking_connection |
+---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+
| test          | notest      |             17881 |       17805 | root@localhost  | EXCLUSIVE         | TRANSACTION           | alter table test.notest add age int |                 53 |                           0 |                           0 |              18049 |        17973 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 17973        | KILL 17973                   |
| test          | notest      |             18446 |       18370 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from test.notest           |                 47 |                           0 |                           0 |              18049 |        17973 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 17973        | KILL 17973                   |
| test          | notest      |             18469 |       18393 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from test.notest           |                 44 |                           0 |                           0 |              18049 |        17973 | root@localhost   | SHARED_READ        | TRANSACTION            | KILL QUERY 17973        | KILL 17973                   |
| test          | notest      |             17881 |       17805 | root@localhost  | EXCLUSIVE         | TRANSACTION           | alter table test.notest add age int |                 53 |                           0 |                           0 |              17881 |        17805 | root@localhost   | SHARED_UPGRADABLE  | TRANSACTION            | KILL QUERY 17805        | KILL 17805                   |
| test          | notest      |             18446 |       18370 | root@localhost  | SHARED_READ       | TRANSACTION           | select * from test.notest           |                 47 |                           0 |                           0 |              17881&n