11 表太大了，修改表结构太慢怎么解决？（上）
你好，我是俊达。
平时我们使用MySQL，或多或少都会遇到DDL的需求。比如有新业务上线，需要给现有的表添加新的字段；或者需要调整索引来优化性能；或者现有的表可能存在大量碎片，需要优化表，收缩空间。
那么对现有的业务表执行各类DDL操作时，需要多少时间才能执行完成？执行的过程中是否会锁表，应用程序在DDL执行的过程中是否能正常读写数据？DDL操作是否会消耗大量资源，影响数据库的性能？特别是当操作的表特别大，或者应用系统的可用性要求特别高的时候，这些问题就特别重要。
这一讲我们就来详细地分析MySQL中各类DDL操作具体是如何执行的。如何在完成DDL的同时，尽量减少对业务系统的影响。
有些DDL只需要修改元数据，不影响表中实际存储的数据，这些操作通常很快就能完成。有些DDL需要重建表，执行过程中需要复制整个表的数据，这些DDL的开销比较大。从MySQL 5.6开始，InnoDB存储引擎逐渐支持了Online DDL，很多DDL操作，在重建表的过程中可以尽量不锁表，减少对应用系统的影响。但也有一些DDL，在执行的整个过程中都需要锁表，应用程序只能读取数据，无法修改数据。还有一些DDL操作，主要是创建索引，在执行期间不需要重建表，但需要扫描整个表的数据，按索引字段对数据进行排序，构建新的索引。
很多MySQL DDL语句中，可以加上关键字ALGORITHM和LOCK，用于指定DDL的执行方式。比如在下面这个SQL中，我们指定了ALGORITHM为INPLACE，LOCK为NONE。由于这个SQL只是修改了字段的注释，因此执行很快。
mysql> desc salaries;
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| salary | int | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | YES | | NULL | |
+-----------+------+------+-----+---------+-------+
mysql> alter table salaries
modify emp_no int not null comment 'Employee Identity',
algorithm=inplace,
lock=none;
Query OK, 0 rows affected (6.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
如果DDL不支持以ALGORITHM指定的方式来执行，会直接报错。下面这个SQL中，修改了字段的not null属性，因此无法以INSTANT的方式执行。
mysql> alter table salaries modify salary int, algorithm=instant;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
关键字ALGORITHM可以指定为DEFAULT、INSTANT、INPLACE或COPY。
- DEFAULT：默认方式，不同的DDL类型，默认的执行方式可能不一样。MySQL会以该DDL开销最低的方式来选择默认的执行方式。
- INSTANT：MySQL 8.0新加的执行方式，使用这种执行方式时，添加字段和删除字段时不需要重建表。ALGORITHM指定为INSTANT时，不能再指定LOCK关键字。
- INPLACE：Online DDL。使用INPLACE时，默认不锁表。这里的不锁表，是指重建表时复制数据的过程中，或创建二级索引时读取全表数据进行排序、生成索引的过程中不锁表。但是在复制数据或生成索引的过程中，表上会有新的DML修改数据，这些修改会记录到一个在线的变更日志中。InnoDB需要将变更日志中的内容更新到新的表或索引中，而这个过程中是会锁表的，这一点后面会详细介绍。
- COPY：传统的DDL执行方式，执行过程中会锁表，默认锁模式为SHARED，应用程序可以读取表中的数据，但是不能写入数据。如果LOCK指定为EXCLUSIVE，那么读操作也会被阻塞。
关键字LOCK可以指定为NONE、SHARED或EXCLUSIVE，如果不指定，就会根据具体的DDL语句、指定的ALGORITHM来确定一个默认的锁级别。
- NONE：不锁表。
- SHARED：共享锁，允许读取数据，但是不允许修改数据。
- EXCLUSIVE：排它锁，不允许读取和修改数据。
接下来，我们分别来介绍MySQL中几种不同类型的DDL。
只修改元数据的DDL操作
有一些DDL只需要修改元数据，不需要修改表中实际存储的数据。修改表名、字段名或索引名就是这样的操作。
mysql> alter table employees rename to employees_v2;
Query OK, 0 rows affected (0.03 sec)
mysql> alter table dept_emp rename key dept_no to idx_dept_no;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dept_emp rename column to_date to end_date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改表名有一个重要的作用。比如某个业务表不再使用了，我们先不要急着DROP TABLE，而是先将表改一个名字，等过一段时间，确定确实没有任何业务会访问这个表了，再DROP表。因为如果DROP表之后，你再发现还有业务会访问这个表，恢复起来就比较麻烦了，可能需要比较长的时间，而改个表名，恢复起来就非常方便了。
DROP TABLE、DROP INDEX这样的操作也基本上也只需要修改元数据，执行速度一般也很快。当然DROP表和索引时，还需要回收它们占用的物理空间。如果开启了innodb_file_per_table，DROP表时需要删除对应的ibd文件。
删除索引也是一个需要特别注意的操作。虽然删除索引并不会影响表中的数据，但可能会影响一些查询的性能。一些关键索引删除后，可能会导致数据库CPU被打满，这个时候你想把索引再加回来，可能就需要比较长的时间了，而且可能需要先把业务停掉才行。MySQL 8.0支持不可见索引，删除索引前，你可以先把索引设置为不可见。这样，执行计划不会再考虑这个索引，但是执行DML操作时，会正常维护这个索引。
mysql> alter table dept_emp alter index idx_dept_no invisible;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from dept_emp where dept_no = 'd005' limit 1;
+----+-------------+----------+------+---------------+--------+-------------+
| id | select_type | table | type | possible_keys | rows | Extra |
+----+-------------+----------+------+---------------+--------+-------------+
| 1 | SIMPLE | dept_emp | ALL | NULL | 331143 | Using where |
+----+-------------+----------+------+---------------+--------+-------------+
如果你发现业务系统还需要使用这个索引，可以将索引改成可见，这个操作通常也很快能完成。
mysql> alter table dept_emp alter index idx_dept_no visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
此外，修改表和字段的注释，也只需要修改元数据，这应该容易理解。
mysql> alter table dept_emp comment='Department Employee Relationship';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table dept_emp modify from_date date not null comment 'From Date', algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
需要注意，修改字段的注释时，需要确保字段的类型和是否为NULL属性不变，否则就不能仅仅修改元数据了。下面这个例子中，由于修改了字段的是否可为NULL的约束，开销就比较大了。
mysql> alter table dept_emp modify from_date date comment 'From Date', algorithm=instant;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
Instant DDL
给表加字段是一个很常见的需求，但是在5.7及更早的版本中，每次加字段都需要重建整个表，开销实在是有点大，特别是当表的数据量比较大的时候。MySQL 8.0中新增了Instant DDL特性，主要解决了加字段需要重建表的这个问题。
实际上在8.0中，加字段时，即使不指定algorithm=instant，默认也是使用instant的方式。
mysql> alter table salaries add c1 int, algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL 8.0.29版本开始，你还可以将字段加到任意指定的位置。
mysql> alter table salaries add column c2 int after salary, algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc salaries;
+-----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| salary | int | NO | | NULL | |
| c2 | int | YES | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
| c1 | int | YES | | NULL | |
+-----------+------+------+-----+---------+-------+
删除字段也可以使用instant方式，也只需要修改元数据。
]mysql> alter table salaries drop column to_date, algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
当然，instant DDL也有一些限制，就是同一个表，能执行的instant加字段或删除字段的操作次数是有限制的，超过这个次数后，再执行时就会报错“Maximum row versions reached”。
mysql> alter table salaries add column cc int, algorithm=instant;
ERROR 4092 (HY000): Maximum row versions reached for table employees/salaries. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
每执行一次Instant DDL，表的行版本就会加1，当行版本达到64时，就无法再对这个表执行Instant DDL了。从information_schema.innodb_tables这个系统表中，可以查看表当前的行版本数。
mysql> select * from information_schema.innodb_tables where name = 'employees/salaries'\G
*************************** 1. row ***************************
TABLE_ID: 1510
NAME: employees/salaries
FLAG: 33
N_COLS: 8
SPACE: 243
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
INSTANT_COLS: 0
TOTAL_ROW_VERSIONS: 64
1 row in set (0.00 sec)
达到行版本限制后，你就只能使用inplace或copy的方式加字段了。
mysql> alter table salaries add column cc int, algorithm=inplace;
Query OK, 0 rows affected (4.96 sec)
Records: 0 Duplicates: 0 Warnings: 0
以inplace或copy的方式执行过后，表的行版本数会清零，然后你就可以继续开心地使用instant DDL了。
mysql> alter table salaries add column d10 int, algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table salaries add column d11 int, add column d12 int, algorithm=instant;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Instant DDL是怎么实现的
那么Instant DDL是怎么实现的呢？为什么要限制行版本的数量？执行过Instant DDL的表，查询数据和写入数据时有什么特殊的地方吗？这和InnoDB的元数据，以及InnoDB的行记录格式相关。
InnoDB元数据存储
元数据中记录了一个表有哪些字段，这些字段的数据类型是什么，字段的顺序是怎样的。MySQL 8.0将元数据存储在一系列的InnoDB表中。你需要使用Debug版本的MySQL，并且设置一个特殊的会话变量后，才能查询这些元数据表。
我们先创建一个测试表，执行一些instant DDL操作，然后再来看元数据中是怎么记录的。
create table t_instant(a int, b int, primary key(a));
alter table t_instant add c varchar(30), algorithm=instant;
alter table t_instant add c2 varchar(30), algorithm=instant;
alter table t_instant drop b, algorithm=instant;
mysql>desc t_instant;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int | NO | PRI | NULL | |
| c | varchar(30) | YES | | NULL | |
| c2 | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
要查看元数据，需要使用Debug版本的MySQL。官方提供的二进制包中包含了Debug版本的二进制。你只需要将mysql二进制包中的bin/mysqld替换成bin/mysqld-debug，将lib/plugin下的so文件都替换成lib/plugin/debug下的so文件，再启动MySQL就可以了。
登录到Debug版本的MySQL后，设置debug会话变量，然后就可以访问InnoDB的元数据了。
我们来看一下前面创建的那个测试表t_instant的元数据。
mysql> desc t_instant;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int | NO | PRI | NULL | |
| c | varchar(30) | YES | | NULL | |
| c2 | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select t2.name, t2.ordinal_position as ord_no, t2.se_private_data,
t2.hidden, t2.type, t2.char_length
from mysql.tables t1, mysql.columns t2
where t1.id = t2.table_id
and t1.name = 't_instant';
+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
| name | ord_no | se_private_data | hidden | type | char_length |
+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
| a | 1 | physical_pos=0;table_id=1086; | Visible | MYSQL_TYPE_LONG | 11 |
| c | 2 | default_null=1;physical_pos=4;table_id=1086;version_added=1; | Visible | MYSQL_TYPE_VARCHAR | 120 |
| c2 | 3 | default_null=1;physical_pos=5;table_id=1086;version_added=2; | Visible | MYSQL_TYPE_VARCHAR | 120 |
| DB_TRX_ID | 4 | physical_pos=1;table_id=1086; | SE | MYSQL_TYPE_INT24 | 6 |
| DB_ROLL_PTR | 5 | physical_pos=2;table_id=1086; | SE | MYSQL_TYPE_LONGLONG | 7 |
| !hidden!_dropped_v3_p3_b | 6 | physical_pos=3;version_dropped=3; | SE | MYSQL_TYPE_LONG | 11 |
+--------------------------+--------+--------------------------------------------------------------+---------+---------------------+-------------+
从元数据中，我们可以看到，这个表实际上有6个字段，其中有3个字段是不可见的，对用户可见的有3个字段。其中字段C是在版本1中添加的，字段C2是在版本2中添加的，字段B在版本3中被删除了。
InnoDB行格式
在InnoDB数据页面中，每一行记录的头部，有一个标识位，用来标记这行记录是不是在INSTANT DDL之后写入的。对于INSTANT DDL之后写入或修改过的记录，还会有一个version字节，用来标识这行记录修改时，表结构的对应版本。
InnoDB在解析数据页中的一行记录时，先获取记录头部的版本信息，假设这行记录的版本为K，那么只需从元数据中查出在版本K或K之前添加并且在版本K之前还没有被删除的字段，根据这个字段列表来解析这行记录。最后，在返回这行记录时，需要过滤掉当前不可见的字段。
能以INSTANT方式执行DDL的前提，是这个DDL操作不需要修改已有数据的行记录格式。因此，我们就可以这样理解，修改字段类型不能以INSTANT的方式执行，因为不同的字段类型，数据存储格式是不一样的。修改字段是否可以为NULL，也不能以INSTANT的方式执行。因为对于可以为NULL的字段，记录头部有一个对应的比特，用来标记这个列中存储的数据是否为NULL。
对于枚举类型，如果只是往类型最后添加新的选项，并且新加的选项不会导致字段存储长度发生变化，那么就可以使用INSTANT DDL。减少枚举值、修改枚举值的顺序、将新的枚举值添加到已有列表的中间，都无法使用instant DDL。
mysql> create table t_enum(a enum('A', 'B'));
Query OK, 0 rows affected (3.42 sec)
mysql> alter table t_enum modify a enum('A', 'B', 'C'), algorithm=instant;
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t_enum modify a enum('C', 'B', 'A'), algorithm=instant;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason:
Need to rebuild the table to change column type.
Try ALGORITHM=COPY/INPLACE.
mysql> alter table t_enum modify a enum('A', 'B', 'D', 'C'), algorithm=instant;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason:
Need to rebuild the table to change column type.
Try ALGORITHM=COPY/INPLACE.
修改varchar类型的长度不能使用INSTANT DDL。如果是增加长度，并且长度在变更前后都不超过255字节，或者长度在变更前后都超过255字节，那么可以使用INPLACE的方式执行，只需要修改元数据。如果变更前长度不到255字节，变更后长度超过255字节，那么就不能使用INPLACE的方式执行了，需要用COPY的方式执行。这主要是跟varchar字段的物理存储格式有关。
下面这个例子中，employees表使用了utf8mb4字符集，将last_name改成varchar(64)时，长度超过255字节了，因此需要使用COPY的方式来执行DDL。
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
mysql> alter table employees modify last_name varchar(64) not null, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table employees modify last_name varchar(64) not null, algorithm=copy;
Query OK, 300024 rows affected (1.38 sec)
Records: 300024 Duplicates: 0 Warnings: 0
字段长度超过255字节后，再增加长度就可以使用INPLACE方式执行，并且不需要重建表，执行速度很快。
mysql> alter table employees modify last_name varchar(128) not null, algorithm=inplace;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
如果你要缩减varchar字段的长度，那么就只能以COPY的方式执行DDL了。因为缩减长度时，表里面已有的数据可能会超长。
mysql> alter table employees modify last_name varchar(100) not null, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> alter table employees modify last_name varchar(10) not null, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
总结
这一讲中，我们讨论的是通常执行速度很快的这一类DDL，也就是只需要修改元数据的DDL。Instant DDL是MySQL 8.0增加的快速加列特性，可以快速地给大表增加字段。当然，还有一些DDL，执行期间的开销会比较大，这一讲的下篇中，我们再来详细讨论。
思考题
只修改元数据的DDL、INSTANT DDL执行速度通常都很快，但是这些DDL执行也是需要获取元数据锁的，比如下面这个例子：
- 会话1 开启一个事务，执行一个select for update操作。
mysql> desc t_ddl;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a | int | NO | PRI | NULL | |
| b | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_ddl limit 1 for update;
Empty set (1.12 sec)
- 会话2 执行一个instant DDL。
- 会话3 执行一个普通的select操作。
你会发现，会话2和会话3都被阻塞了。从processlist可以看到，它们都在等待元数据锁。（输出结果做了简化）
mysql> show processlist;
+----+---------+------+---------------------------------+------------------------------------------------+
| Id | Command | Time | State | Info |
+----+---------+------+---------------------------------+------------------------------------------------+
| 8 | Sleep | 116 | | NULL |
| 11 | Query | 105 | Waiting for table metadata lock | alter table t_ddl add c int, algorithm=instant |
| 12 | Query | 96 | Waiting for table metadata lock | select * from t_ddl limit 1 |
+----+---------+------+---------------------------------+------------------------------------------------+
从performance_schema.metadata_locks也可以看到t_ddl表的元数据持有和请求情况。
mysql> select * from metadata_locks where object_name = 't_ddl';
-------------+-------------------+-------------+-------------------+-----------------+
OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | SOURCE | OWNER_THREAD_ID |
-------------+-------------------+-------------+-------------------+-----------------+
t_ddl | SHARED_WRITE | GRANTED | sql_parse.cc:6093 | 49 |
t_ddl | SHARED_UPGRADABLE | GRANTED | sql_parse.cc:6093 | 50 |
t_ddl | EXCLUSIVE | PENDING | mdl.cc:3753 | 50 |
t_ddl | SHARED_READ | PENDING | sql_parse.cc:6093 | 51 |
-------------+-------------------+-------------+-------------------+-----------------+
还有哪些情况会导致DDL无法获取到元数据锁？怎么快速定位到元数据锁的阻塞源？
期待你的思考，欢迎在留言区中与我交流。如果今天的课程让你有所收获，也欢迎转发给有需要的朋友。我们下节课再见！