浅谈MySQL Online DDL (上)

本文首发于个人微信公众号《andyqian》,期待你的关注！

前言

  这个知识点，不瞒你说，我也是最近才了解到的，觉得非常有用！就决定记录在这里，希望对你也有所帮助。

索引创建

  如果你对DDL还不了解，可以先看看上一篇文章《MySQL 命令种类》来回顾一下。我们知道，在生产环境中做DDL操作成本是非常高的，为什么这样说呢？我们以(MySQL5.5之前版本）添加索引为例，需要经历的步骤有：

如上图所示: 经历步骤有:

1. 按照原始表的定义，重新定义一个空的临时表。

2. 对临时表进行添加索引。

3. 再将原始表中的数据逐条Copy到临时表中。

4. 当原始表中的所有记录都被Copy临时表后，将原始表进行删除。

5. 再将临时表命名为原始表表名。

从上面我们可以看出：索引创建一共经历了五个步骤，才能加上索引，当数据量比较大时，光复制就需要大量的时间。在复制过程中，还对原始表进行了写锁操作，导致 insert，delete，update 语句均不能执行。处于等待状态。对于高并发应用来说，这是不允许的。也就导致在MySQL 5.5之前版本，线上添加，修改索引是一个极其痛苦的操作。

注意：（图中括号内的关键字，并不代表MySQL内部采用该行为，只是我为了更容易理解，才添加。不要误解。)

快速索引创建

  快速索引创建原名为:『 fast index creation 』，主要是因为在MySQL 5.5及更高版本(InnoDB插件的MySQL 5.1)上创建，删除二级索引，不需要创建临时表，复制数据等操作，提高了操作效率。其内部实现步骤为:

1. 发起删除索引操作。

2. 通知InnoDB系统表和MySQL数据字典表，该索引已标识为失效状态。

3. InnoDB将索引所占空间回收至表空间中，以便后续索引，表记录使用。

在这里，我们知道由于二级索引只包含了索引键和主键值，所以在创建，删除时不需要进行数据复制，从而不会进行锁表。也就允许线上DML操作。不影响生产使用。

对于聚簇索引则不同，其包含了B树节点中的数据值，所以，创建修改时，就涉及到了临时表的创建，数据复制等，所以其成本也是非常高的。

聚簇索引&二级索引

  上面提到了聚簇索引和二级索引，其实呀，还是比较好理解的，下面分别介绍一下:

聚簇索引：

  其实就是我们常见的主键索引，是InnoDB存储引擎中对主键索引的别名而已。表中记录的存储都是按照主键列进行有序组织的，主键索引的添加，删除操作成本是非常高的。要经过: 创建临时表，复制数据等一系列操作。

二级索引：

  二级索引就是我们常见字段上的索引，由于二级索引的创建，删除，不涉及创建临时表，复制数据等操作，所以其修改成本相对低很多。效率也更高！
如下所示:

alter table t_base_user add idx_name(name);

今日命令

命令: show create table t_base_user;
作用: 显示表的创建语句。
例如:

mysql> show create table t_base_user \G
*************************** 1. row ***************************
       Table: t_base_user
Create Table: CREATE TABLE `t_base_user` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'name',
  `email` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'email',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `telephone` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'telephone',
  `status` tinyint(4) DEFAULT '0',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`oid`),
  UNIQUE KEY `idx_email` (`email`),
  KEY `idx_name` (`name`),
  KEY `idx_telephone` (`telephone`),
  KEY `idx_email_name` (`email`,`name`) COMMENT 'remark'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

从上面信息中，我们不仅可以知道每列属性(列名，字段长度，默认值，字符集)，还可以知道该表的索引信息，存储引擎信息，字符集信息。是不是很有帮助呢？

应用场景：我们可以使用该语句，来查看该表的建表信息，表的存储引擎信息，在实际工作中，非常有用。

小结

  也许会有人说，这篇文章说的都是 Fast Index Creation (在线索引创建)和在线DDL有什么关系？其实不然，在 MySQL 5.6 开始，Fast Index Creation (快速索引创建)已升级为Online DDL了，开放了更多的在线DDL操作，我们也不再为生产中执行DDL操作而犯愁了。不过，不是所有的DDL操作都支持在线操作的，我们在下一篇文章《浅谈MySQL Online DDL(下)》继续说，尽请期待！

 

相关阅读:

写会MySQL索引

读懂MySQL执行计划

MySQL表设计踩过的坑！

MySQL事务隔离级别

 

扫码关注，一起进步

个人博客: http://www.andyqian.com