欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

浅谈MySQL Online DDL (上)

程序员文章站 2022-04-09 20:43:23
...

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

前言

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

索引创建

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

浅谈MySQL Online DDL (上)

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

  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事务隔离级别

 

浅谈MySQL Online DDL (上)

扫码关注,一起进步

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