一、背景
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 | 17805 | root@localhost | SHARED_UPGRADABLE | TRANSACTION | KILL QUERY 17805 | KILL 17805 | | test | notest | 18469 | 18393 | root@localhost | SHARED_READ | TRANSACTION | select * from test.notest | 44 | 0 | 0 | 17881 | 17805 | root@localhost | SHARED_UPGRADABLE | TRANSACTION | KILL QUERY 17805 | KILL 17805 | +---------------+-------------+-------------------+-------------+-----------------+-------------------+-----------------------+-------------------------------------+--------------------+-----------------------------+-----------------------------+--------------------+--------------+------------------+--------------------+------------------------+-------------------------+------------------------------+ 6 rows in set (0.01 sec)
四、結論
通過以上的實驗，可以得出結論，如果在對表進行Online DDL時，該表上存在元資料鎖，那么DDL將一直等待元資料鎖釋放，直到超過引數lock_wait_timeout的超時時間，并且該DDL會阻塞后續對該表的所有操作，因此，即使現在支持instant演算法的Online DDL可以秒加列，也要在業務低峰期進行，并且執行DDL前，最好查詢一下performance_schema.metadata_locks，檢查是否存在元資料鎖，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551241.html
標籤：MySQL
上一篇：Linux(CentOS7)安裝與卸載MySQL8.0圖文詳解
下一篇：返回列表