MySQL Online DDL知识点有什么

发布时间：2021-12-20 20:23:40 所属栏目：MySql教程来源：互联网

导读：这篇文章主要介绍MySQL Online DDL知识点有哪些，在日常操作中，相信很多人在MySQL Online DDL知识点有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答MySQL Online DDL知识点有哪些的疑惑有所帮助！接下来，请跟着小编

这篇文章主要介绍“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;

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

| Id | User | Host      | db       | Command | Time | State                           | Info                                                       |

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

| 5 | root | localhost | liuwenhe | Query   | 107 |copy to tmp table| alter table v_member_info modify register_ip varchar(50) |

| 6 | root | localhost | liuwenhe | Query   |   17 |Waiting for table metadata lock| update v_member_info set phone='1771002222' where id=1   |

| 8 | root | localhost | NULL     | Query   |    0 | init                            | 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;

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

| Id | User | Host      | db       | Command | Time | State          | Info                                                                |

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

| 8 | root | localhost | NULL     | Query   |    0 | init           | show processlist                                                    |

| 14 | root | localhost | liuwenhe | Query   |    9 |altering table| alter table v_member_info add index inde_register (register_ip) |

| 18 | root | localhost | liuwenhe | Sleep   |    6 |                | NULL                                                                |

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

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还有效吗？

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;

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

| Id | User | Host      | db       | Command | Time | State          | Info                                                                                  |

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

| 20 | root | localhost | NULL     | Sleep   | 5053 |                | NULL                                                                                  |

| 21 | root | localhost | liuwenhe | Query   |    2 |altering table| alter table v_member_info ALGORITHM =INPLACE ,add index inde_register (register_ip) |

| 23 | root | localhost | NULL     | Query   |    0 | init           | 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新特性。

（编辑：云计算网_泰州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MySQL之日志管制	如何使用mysql索引
Anemometer图形化显示	mysql数据库explain命