MySQL的DDL注意事项，在Goldendb中有改进吗？
Oracle 数据库在在线维护操作方面引入了大量特性，旨在实现日常维护和升级时无需停机。相比之下，MySQL 在这方面的表现稍逊一筹。作为 Oracle 的 DBA 是一件很幸福的事情，因为日常的分区维护、发现性能问题时的紧急创建索引等操作都可以在线完成。而 MySQL 虽然也宣称支持 DDL，但这是真的吗？
我们有一个 GoldenDB 的客户要求以后所有的 DDL 操作只能在停所有业务的情况下执行。对于拥有大量数据库实例且需要频繁进行 DDL 维护的情况，请问你能提供多少停机时间？
我之前写过3篇文档《Oracle、MySQL、PostgreSQL、openGauss、达梦数据库比较系列(十九): 增加列default value会表重写吗? 》《 “alter table ” modify column in Oracle、MySQL、PostGreSQL(数据库比较系列十三) 》 《 MySQL8中ALGORITHM=INSTANT带来的风险小结》
1，Instant Table Rebuild
在 MySQL 8.0.12中，支持instant算法支持innodb存储引擎的表，在做一些DDL时可以只操作数据字典，不用重建表数据,第一个即时操作是在表末尾添加一列(来自鹅厂的贡献).在 MySQL 8.0.29中我们增加了在表中的任何位置添加（或删除）列的可能性,自 MySQL 8.0.12 起，对于任何受支持的 DDL，默认算法为INSTANT。
即使支持 INSTANT DDL 操作，也有一个限制：一张表最多支持 64 次即时更改。达到该计数器后，需要“重建”该表,并重置计数.从INFORMATION_SCHEMA.INNODB_TABLES的TOTAL_ROW_VERSIONS查看当前instant使用次数，监控所有表并决定何时需要重建表（重置该计数器）是一种很好的做法，避免当在生产中较小的停机窗口遇到了大表instant DDL失效的噩梦。在 MySQL InnoDB’s Instant Schema Changes: What DBAs Should Know记录了这一行为。
监控Instant 改变
SQL > SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 | 63 |
+---------+--------------------+
SQL > SELECT NAME, TOTAL_ROW_VERSIONS, 64-TOTAL_ROW_VERSIONS AS
"REMAINING_INSTANT_DDLs",
ROUND(TOTAL_ROW_VERSIONS/64 * 100,2) AS "DDLs %"
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE TOTAL_ROW_VERSIONS > 0 ORDER BY 2 DESC;
MySQL 8
— 我们创建一个批量增加列的存储过程
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
mysql> create table t1 (id int);
mysql> delimiter $$
mysql>
mysql> create procedure add_col(num int)
-> begin
-> declare i int default 1;
-> declare my_sqll varchar(500);
-> while i set my_sqll=concat('alter table t1 add c',i,' int , algorithm=INSTANT ');
-> set @cmd=my_sqll;
-> PREPARE s1 from @cmd;
-> EXECUTE s1;
-> deallocate prepare s1;
->
-> set i=i+1;
-> end while;
-> end $$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call add_col(63);
Query OK, 0 rows affected (0.64 sec)
mysql> desc t1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| c1 | int | YES | | NULL | |
| c2 | int | YES | | NULL | |
| c3 | int | YES | | NULL | |
| c4 | int | YES | | NULL | |
| c5 | int | YES | | NULL | |
| c6 | int | YES | | NULL | |
| c7 | int | YES | | NULL | |
| c8 | int | YES | | NULL | |
| c9 | int | YES | | NULL | |
| c10 | int | YES | | NULL | |
| c11 | int | YES | | NULL | |
| c12 | int | YES | | NULL | |
| c13 | int | YES | | NULL | |
| c14 | int | YES | | NULL | |
| c15 | int | YES | | NULL | |
| c16 | int | YES | | NULL | |
| c17 | int | YES | | NULL | |
| c18 | int | YES | | NULL | |
| c19 | int | YES | | NULL | |
| c20 | int | YES | | NULL | |
| c21 | int | YES | | NULL | |
| c22 | int | YES | | NULL | |
| c23 | int | YES | | NULL | |
| c24 | int | YES | | NULL | |
| c25 | int | YES | | NULL | |
| c26 | int | YES | | NULL | |
| c27 | int | YES | | NULL | |
| c28 | int | YES | | NULL | |
| c29 | int | YES | | NULL | |
| c30 | int | YES | | NULL | |
| c31 | int | YES | | NULL | |
| c32 | int | YES | | NULL | |
| c33 | int | YES | | NULL | |
| c34 | int | YES | | NULL | |
| c35 | int | YES | | NULL | |
| c36 | int | YES | | NULL | |
| c37 | int | YES | | NULL | |
| c38 | int | YES | | NULL | |
| c39 | int | YES | | NULL | |
| c40 | int | YES | | NULL | |
| c41 | int | YES | | NULL | |
| c42 | int | YES | | NULL | |
| c43 | int | YES | | NULL | |
| c44 | int | YES | | NULL | |
| c45 | int | YES | | NULL | |
| c46 | int | YES | | NULL | |
| c47 | int | YES | | NULL | |
| c48 | int | YES | | NULL | |
| c49 | int | YES | | NULL | |
| c50 | int | YES | | NULL | |
| c51 | int | YES | | NULL | |
| c52 | int | YES | | NULL | |
| c53 | int | YES | | NULL | |
| c54 | int | YES | | NULL | |
| c55 | int | YES | | NULL | |
| c56 | int | YES | | NULL | |
| c57 | int | YES | | NULL | |
| c58 | int | YES | | NULL | |
| c59 | int | YES | | NULL | |
| c60 | int | YES | | NULL | |
| c61 | int | YES | | NULL | |
| c62 | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
63 rows in set (0.00 sec)
mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1068 | anbob/t1 | 33 | 66 | 6 | Dynamic | 0 | Single | 0 | 62 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
mysql> alter table t1 add c63 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 add c64 int;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1068 | anbob/t1 | 33 | 68 | 6 | Dynamic | 0 | Single | 0 | 64 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
mysql> alter table t1 add c65 int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| 1069 | anbob/t1 | 33 | 69 | 7 | Dynamic | 0 | Single | 0 | 0 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)
Note：
使用instant算法DDL增加列64次以后，发生了重建(重置计数)
手动重置计数的方法
mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) mysql> create table t1(id int); Query OK, 0 rows affected (0.01 sec) mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1'; +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1072 | anbob/t1 | 33 | 4 | 10 | Dynamic | 0 | Single | 0 | 0 | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.00 sec) mysql> call add_col(63); Query OK, 0 rows affected (0.59 sec) mysql> OPTIMIZE TABLE t1; +----------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+-------------------------------------------------------------------+ | anbob.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | anbob.t1 | optimize | status | OK | +----------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.03 sec) mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1'; +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1073 | anbob/t1 | 33 | 66 | 11 | Dynamic | 0 | Single | 0 | 0 | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.00 sec) -- 或者 mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1'; +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1070 | anbob/t1 | 33 | 66 | 8 | Dynamic | 0 | Single | 0 | 62 | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.00 sec) mysql> alter table t1 force; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1'; +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1071 | anbob/t1 | 33 | 66 | 9 | Dynamic | 0 | Single | 0 | 0 | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
Note:
使用optimize table 和alter talble force都可以重置计数并重建表。
建议DDL时显示指定instance，如果不允许会报错,如下
mysql> call add_col(500); ERROR 4092 (HY000): Maximum row versions reached for table anbob/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE. mysql> select * from information_schema.INNODB_TABLES where name='anbob/t1'; +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ | 1075 | anbob/t1 | 33 | 68 | 13 | Dynamic | 0 | Single | 0 | 64 | +----------+----------+------+--------+-------+------------+---------------+------------+--------------+--------------------+ 1 row in set (0.00 sec)
GoldenDB V6.1
那我们看看基于MySQL系的国产库GoldenDB是否存在？
-- 8880 port
MySQL [anbob]> select * from v$version;
ERROR 942 (HY000): ORA-00942: table or view does not exist
-- 8881 port
MySQL [anbob]> select * from v$version;
+-----------------------+-----------------------+-----------------------+--------+
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
+-----------------------+-----------------------+-----------------------+--------+
| V_ALL-DBV6.1.03.07SP1 | V_ALL-DBV6.1.03.07SP1 | V_ALL-DBV6.1.03.07SP1 | 0 |
+-----------------------+-----------------------+-----------------------+--------+
1 row in set (0.01 sec)
MySQL [anbob]> select version();
+-----------+
| VERSION() |
+-----------+
| 8.9.99 |
+-----------+
1 row in set (0.02 sec)
MySQL [(none)]> desc INFORMATION_SCHEMA.INNODB_TABLES
-> ;
+---------------+-----------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+---------------+-----------------+------+-----+---------+-------+
| TABLE_ID | bigint unsigned | NO | | | |
| NAME | varchar(655) | NO | | | |
| FLAG | int | NO | | | |
| N_COLS | int | NO | | | |
| SPACE | bigint | NO | | | |
| ROW_FORMAT | varchar(12) | YES | | | |
| ZIP_PAGE_SIZE | int unsigned | NO | | | |
| SPACE_TYPE | varchar(10) | YES | | | |
| INSTANT_COLS | int | NO | | | |
| FLAG2 | int | NO | | | |
+---------------+-----------------+------+-----+---------+-------+
10 rows in set (0.15 sec)
MySQL [db01]> create procedure add_col(num int)
-> begin
-> declare i int default 1;
-> declare my_sqll varchar(500);
-> while i set my_sqll=concat('alter table t1 add c',i,' int , algorithm=INSTANT ');
-> set @cmd=my_sqll;
-> PREPARE s1 from @cmd;
-> EXECUTE s1;
-> deallocate prepare s1;
-> set i=i+1;
-> end while;
-> end $$
Query OK, 0 rows affected (0.01 sec)
MySQL [db01]> delimiter ;
MySQL [db01]> create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)
MySQL [db01]> call add_col(64);
Query OK, 0 rows affected (1.49 sec)
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 67 | 67 | Dynamic | 0 | Single | 1 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
3 rows in set (0.00 sec)
MySQL [db01]> alter table t1 add c64 int , algorithm=INSTANT;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 68 | 67 | Dynamic | 0 | Single | 1 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
3 rows in set (0.01 sec)
MySQL [db01]> alter table t1 add c65 int , algorithm=INSTANT;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 69 | 67 | Dynamic | 0 | Single | 1 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
3 rows in set (0.00 sec)
MySQL [db01]> alter table t1 add c66 int;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 70 | 67 | Dynamic | 0 | Single | 1 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
3 rows in set (0.00 sec)
MySQL [db01]> create table t3(id int,name varchar(10),addr varchar2(100));
Query OK, 0 rows affected (0.03 sec)
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 70 | 67 | Dynamic | 0 | Single | 1 | 16 |
| 1179 | db01/t3 | 33 | 6 | 69 | Dynamic | 0 | Single | 0 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
5 rows in set (0.01 sec)
MySQL [db01]> alter table t3 add c1 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [db01]> select * from INFORMATION_SCHEMA.INNODB_TABLES where name like 'db01/t%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | FLAG2 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
| 1177 | db01/t1 | 33 | 70 | 67 | Dynamic | 0 | Single | 1 | 16 |
| 1179 | db01/t3 | 33 | 7 | 69 | Dynamic | 0 | Single | 3 | 16 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+-------+
MySQL [db01]> drop table t1;
Query OK, 0 rows affected (0.07 sec)
MySQL [db01]> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
MySQL [db01]> call add_col(200);
Query OK, 0 rows affected (8.08 sec)
Note:
GoldenDB中不存在每个表instant算法 64次的限制， 且也没有MySQL中TOTAL_ROW_VERSIONS记数，INSTANT_COLS 似乎是第一次做instant时现有表上的列数。
2，MySQL Metadata lock
在oracle中存在一些情况，如select 查询效率较差，希望创建个索引紧急优化一下，但在GoldenDB(MySQL)中可能是一种奢望。通常会因为特有的metadata lock(MDL)导致事务挂起，甚至影响备份失败，如备份期间DDL操作导致备份失败. 下面演示一下默认事务隔离级别(RC)下，select 事务产生的MDL.
-- session 1 # mysql -udbproxy -pxxxxxx -h172.*.*.107 -P8880 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 201052 Server version: 8.9.99 Source distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [anbob]> drop table if exists t1; Query OK, 0 rows affected (0.01 sec) MySQL [anbob]> create table t1(id int); Query OK, 0 rows affected (0.02 sec) MySQL [anbob]> start transaction; Query OK, 0 rows affected (0.00 sec) MySQL [anbob]> select * from t1; Empty set (0.01 sec) -- session 2 MySQL [anbob]> set lock_wait_timeout=100; -- default 5 Query OK, 0 rows affected (0.00 sec) MySQL [anbob]> alter table t1 add name varchar2(20), algorithm=INSTANT; -- waited 100s ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- session 3 MySQL [anbob]> set lock_wait_timeout=100; Query OK, 0 rows affected (0.00 sec) MySQL [anbob]> select * from t1; -- wait Empty set (1 min 36.36 sec)
| session1 | session2 | session3 |
| CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; | ||
| START TRANSACTION; | ||
| SELECT * FROM t1; | ||
| ALTER TABLE t1 add name varchar2(20) ALGORITHM=INSTANT; | ||
| SELECT * FROM t1; |
The session 1 select statement takes a shared metadata lock on table t1
The online DDL operation in session 2, which requires an exclusive metadata lock on table t1 to commit table definition changes, must wait for the session 1 transaction to commit or roll back.
The select statement issued in session 3 is blocked waiting for the exclusive metadata lock requested by the alter table operation in session 2 to be granted.
查询堵塞
MySQL [anbob]> show processlist; +---------+-------------+---------------------+-------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +---------+-------------+---------------------+-------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ | 13 | dbagent | localhost | NULL | Sleep | 37 | | NULL | | 14 | dbagent | localhost | NULL | Sleep | 32 | | NULL | | 15 | dbagent | localhost | NULL | Sleep | 2 | | NULL | | 239 | repl | 172.20.22.108:10982 | NULL | Binlog Dump GTID | 2137316 | Master has sent all binlog to slave; waiting for more updates | NULL | | 387 | dbproxy | 172.20.22.107:54552 | NULL | Sleep | 0 | | NULL | | 388 | dbproxy | 172.20.22.107:54554 | NULL | Sleep | 1 | | NULL | | 389 | gdb_dba | 172.20.22.107:54560 | NULL | Sleep | 0 | | NULL | | 390 | gdb_dba | 172.20.22.107:54562 | NULL | Sleep | 1 | | NULL | | 391 | gdb_query | 172.20.22.107:54568 | NULL | Sleep | 0 | | NULL | | 392 | gdb_query | 172.20.22.107:54570 | NULL | Sleep | 1 | | NULL | | 393 | mds2proxy | 172.20.22.107:54576 | NULL | Sleep | 0 | | NULL | | 394 | mds2proxy | 172.20.22.107:54578 | NULL | Sleep | 1 | | NULL | | 395 | super | 172.20.22.107:54584 | NULL | Sleep | 0 | | NULL | | 396 | super | 172.20.22.107:54586 | NULL | Sleep | 1 | | NULL | | 3224627 | dbproxy | 172.20.22.107:60204 | anbob | Query | 26 | Waiting for table metadata lock | select * from t1 | | 3224276 | dbproxy | 172.20.22.107:54962 | anbob | Sleep | 272 | | NULL | | 3224329 | dbproxy | 172.20.22.107:55626 | anbob | Query | 73 | Waiting for table metadata lock | alter table t1 add name varchar2(20), algorithm=INSTANT | | 3223878 | gdbquery2db | 172.20.22.107:48788 | NULL | Sleep | 631 | | NULL | +---------+-------------+---------------------+-------+------------------+---------+---------------------------------------------------------------+---------------------------------------------------------+ 19 rows in set (0.05 sec) MySQL [anbob]> 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 | anbob | t1 | NULL | 1537226048 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:8295 | 3229215 | 26 | | SCHEMA | anbob | NULL | NULL | 1707963712 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dd_schema.cc:115 | 3229215 | 26 | | GLOBAL | NULL | NULL | NULL | 1655634336 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5743 | 3229268 | 26 | | BACKUP LOCK | NULL | NULL | NULL | 1655634736 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5750 | 3229268 | 26 | | SCHEMA | anbob | NULL | NULL | 1655633856 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5730 | 3229268 | 26 | | TABLE | anbob | t1 | NULL | 1655630656 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:8295 | 3229268 | 26 | | TABLESPACE | NULL | anbob/t1 | NULL | 1655632096 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:819 | 3229268 | 26 | | TABLE | anbob | #sql-19b7_313309 | NULL | 1655631056 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:18500 | 3229268 | 26 | | TABLE | anbob | t1 | NULL | 1655634096 | EXCLUSIVE | TRANSACTION | PENDING | mdl.cc:3919 | 3229268 | 27 | | TABLE | anbob | t1 | NULL | 2011630000 | SHARED_READ | TRANSACTION | PENDING | sql_parse.cc:8295 | 3229565 | 20 | | TABLE | performance_schema | metadata_locks | NULL | 1882050864 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:8295 | 3229783 | 17 | +-------------+--------------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+--------------------+-----------------+----------------+ 11 rows in set (0.01 sec) MySQL [anbob]> select * from information_schema.innodb_trx ; +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+----------------+--------------+ | TRX_ID | TRX_STATE | TRX_STARTED | TRX_REQUESTED_LOCK_ID | TRX_WAIT_STARTED | TRX_WEIGHT | TRX_MYSQL_THREAD_ID | TRX_QUERY | TRX_OPERATION_STATE | TRX_TABLES_IN_USE | TRX_TABLES_LOCKED | TRX_LOCK_STRUCTS | TRX_LOCK_MEMORY_BYTES | TRX_ROWS_LOCKED | TRX_ROWS_MODIFIED | TRX_CONCURRENCY_TICKETS | TRX_ISOLATION_LEVEL | TRX_UNIQUE_CHECKS | TRX_FOREIGN_KEY_CHECKS | TRX_LAST_FOREIGN_KEY_ERROR | TRX_ADAPTIVE_HASH_LATCHED | TRX_ADAPTIVE_HASH_TIMEOUT | TRX_IS_READ_ONLY | TRX_AUTOCOMMIT_NON_LOCKING | TRX_SCHEDULE_WEIGHT | TRX_SERIAL_NUM | TRX_GTM_GTID | +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+----------------+--------------+ | 281476067321896 | RUNNING | 2024-07-08 08:06:09 | NULL | NULL | 0 | 3224276 | NULL | NULL | 0 | 0 | 0 | 1136 | 0 | 0 | 0 | READ COMMITTED | 1 | 1 | NULL | 0 | 0 | 0 | 0 | NULL | NULL | NULL | +-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+---------------------+----------------+--------------+ 1 row in set (0.00 sec) MySQL [anbob]> create index idx_id on t1(id); -- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Note:
在读RC模式时，begin select在MySQL也是事务和oracle不同， 导致DDL堵塞，而session 3 select查询又被DDL堵塞， 这也就是为什么生产环境 DDL在MySQL\GoldenDB中同样受限。 可见我们在session 1 结束select的事务前，创建索引一样要等待MDL锁. 但是在GoldenDB中读隔离级别支持MVCC_RC级别，而修改为该模式时，即使session 1未提交，session2创建索引不受堵塞，但不确认该模式不是默认级别的原因，尤其是从oracle迁移到GoldenDB的数据库。
结论
总之，MySQL 8.0 引入了DDL 操作的INSTANT算法，通过避免阻塞更改，彻底改变了模式更改。但是，由于即时更改次数限制为64 次，因此在需要重建表之前，在ALTER语句中明确指定算法以避免意外行为至关重要。还建议通过Information_Schema监控即时更改次数，以避免在不知不觉中达到即时更改限制而导致意外，并仔细规划表重建，在GoldenDB中取消了该次数限制，且没有也不需要Instant VERSION记数；对于MDL lock在MySQL和GoldenDB中做DDL时同样需要注意，避免生产库业务时间的DDL操作。GoldenDB提供读使用MVCC_RC模式，在创建索引时可以不受堵塞。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。