MySQL(InnoDB剖析):25---B+树索引的管理（索引管理、Fast Index Creation、Online Schema Change、Online DDL）

一、索引管理

• 索引的创建和删除可以通过两种方法：
  • 一种是ALTER TABLE
  • 另一种是CREATE/ DROP INDEX

使用语法

• 例如下面有一个表，创建表时指定了一个主键索引

create table t(
    a int not null,
    b varchar(8000),
    primary key(a)
)engine=innodb;

• 现在添加一个字段c，并对字段c进行索引

alter table t add c int not null;

alter table t add key idx_c(c);

• 也可以只索引一个列的开头部分数据，例如只对b字段的前100个字段进行索引

alter table t add key idx_b(b(100));

• 当然也可以进行联合索引

alter table t add key idx_a_c(a,c);

SHOW INDEX命令

• 该命令可以用来查看索引的信息，可以看到表t上有4个索引，分别为：
  • 主键索引
  • c列上的辅助索引、b列的前100字节构成的辅助索引、以及(a、c)的联合辅助索引

• show index每列的含义如下：
  • Table：索引所在的表名
  • Non_unique：非唯一的索引，可以看到parimary key是0，因为必须是唯一的
  • Key_name：索引的名字，用户可以通过这个名字来执行DROP INDEX
  • Seq_in_index：索引中该列的位置，如果看联合索引idx_a_c就比较直观了
  • Column_name：索引列的名字
  • Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行索引
  • Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引
  • Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示只对b列的前100字符进行索引。如果索引整个列，则该字段为NULL
  • Packed：关键字如何被压缩。如果没有被压缩，则为NULL
  • Null：是否索引的列含有NULL值。可以看到idx_b这里为yes，因为定义的列允许NULL值
  • Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE
  • Comment：注释

Cardinality值与ANALYZE TABLE命令

• Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引
• 但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为代价太大了
• 这个值不是精确地，只是一个大概的值，如果想要更新索引Cardinality的信息，可以使用ANALYZE TABLE命令
• 例如，我们向表中插入4条记录

insert into t select 1,repeat('a',7000),-1;
insert into t select 2,repeat('a',7000),-2;
insert into t select 3,repeat('a',7000),-3;
insert into t select 4,repeat('a',7000),-4;

• 插入之前，在上面演示SHOW INDEX的图片可以看到Cardinality为0，但是插入数据之后，该值变为了4

• 当然如果没有更新的话，可以使用ANALYZE命令进行更新

• 不过，使用这个命令在每个系统上得到的结果可能不一样，因为ANALYZE TABLE命令还存在一些问题，可能会影响最后得到的结果
• 另一个问题是MySQL数据库对于Cardinality计数的问题，在运行一段时间后，可能会得到下面的结果：

• 此处Cardinality变为了NULL，在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行EXPLAIN，但是最终出来的结果不一样：一个使用索引，另一个使用全表扫描。这时最好的办法就是做一次ANALYZE TABLE操作
• 因此我建立在一个非高峰时期，对应用程序下的几张核心表做ANALYZE TABLE操作，这能使优化器和索引更好地为你工作

二、Fast Index Creation技术

MySQL 5.5版本之前

• MySQL 5.5版本之前（不包括5.5）存在一个普遍的问题是MySQL数据库对于索引的添加或者删除的这类DDL操作
• MySQL数据库的操作过程为：
  • 首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构
  • 然后把原表中数据导入到临时表
  • 接着删除原表
  • 最后把临时表重名为原来的表名
• 缺点：
  • 若用户对一张大表进行索引的添加和删除操作，那么这会需要很长的时间
  • 更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用
• 临时表的创建路径是通过参数tmpdir进行设置的。用户必须保证tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败

• Fast Index Creation技术：从InnoDB 1.0.X开始支持这种技术，称为“快速索引创建”，简称FIC
• 注意事项：
  • 由于FIC在索引的创建过程对表加了S锁，因此创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用
  • 另外，FIC只限定于辅助索引，对于主键的创建和删除同样需要重建一张表

Fast Index Creation工作原理

• 创建索引：对于辅助索引的创建，InnoDB会对创建索引的表加一个S锁。创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高
• 删除索引：删除辅助索引操作就更简单了，InnoDB只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可

三、Online Schema Change技术

• OSC（在线架构改变）最早是由Facebook实现的一种在线执行DDL的方式，并广泛地应用于Facebook的MySQL数据库。所谓“在线”是指在事务的创建过程中，可以有读写事务对表进行操作，这提高了原有MySQL数据库在DDL操作时的并发性

实现原理

• Facebook采用PHP脚本来实现OSC，而并不是通过修改InnoDB存储引擎源码的方式。OSC最初由Facebook的员工Vamsi Ponnekanti开发。此外，OSC借鉴了开源社区之前的工具The openarkkit toolkit oak-online-table
• 实现OSC步骤如下：

• 上数只是简单介绍了OSC的实现过程，实际脚本非常复杂，仅OSC的PHP核心代码就有2200多行，用到的MySQL InnoDB的知识点非常多，建议DBA和数据库开发人员尝试进行阅读，这有助于更好的理解INnoDB存储引擎的使用
• 由于OSC只是一个PHP脚本，因此其有一定的局限性。例如其要求进行修改的表一定要有主键，且表本身不能存在外键和触发器。此外，在进行OSC过程中，允许SET sql_bin_log=0，因此所做的操作不会同步slave服务器，可能导致主从不一致的情况

四、Online DDL技术

• 虽然FIC可以让InnoDB避免创建临时表，从而提高索引创建的效率。但正如前面小节所说的，索引创建时会阻塞表上的DML操作。OSC虽然解决了上述的部分问题，但是还有很大的局限性
• MySQL 5.6版本开始支持Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE、DELETE这类DML操作，这极大地提高了MySQL数据库在生产环境中的可用性
• 此外，不仅是辅助索引，以下这几类DDL操作都可以通过“在线”的方式进行操作：
  • 辅助索引的创建和删除
  • 改变自增长值
  • 添加或删除外键约束
  • 列的重命名

ALTER TABLE命令

• 通过新的ALTER TABLE语法，用户可以选择索引的创建方式：

ALGORITHM参数

• 该参数指定了创建或删除索引的算法
• 可选值如下：
  • COPY：表示按照MySQL 5.1版本之前的工作模式，即创建临时表的方式
  • INPLACE：表示索引创建或删除操作不需要创建临时表
  • DEFAULT：表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法。（该参数默认为OFF，表示采用INPLACE的方式）

LOCK参数

• 该参数为索引创建或删除时对表添加锁的情况
• 可选值如下：
  • NONE：执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会受到阻塞。因此这种模式可以获得最大的并发度
  • SHARE：这个之前的FIC类似，执行索引创建或删除操作时，对目标表加一个S锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持SHARE模式，会返回一个错误信息
  • EXCLUSIVE：在该模式下，执行索引创建或删除操作时，对目标表加上一个X锁。读写事务都不能进行，因此会阻塞所有的线程。这和COPY方式运行得到的状态类似，但是不需要像COPY方式那样创建一张临时表
  • DEFAULT：该模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式

Online DDL工作原理

• 在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性

日志缓存（innodb_online_alter_log_max_size参数）

• 在工作原理中我们介绍了，DML操作日志会被写入到一个缓存中，这个缓存的大小就是由innodb_online_alter_log_max_size参数控制的，默认为128MB
• 若用户更新的表比较大，并且在创见过程中伴有大量的写事务，可能缓存的空间不能存放日志时，会跑出如下的错误： 

• 如果缓存不够用，可以调整该参数以增大缓存空间
• 如果ALTER TABLE的模式为SHARE，那么在执行过程中就不会有写事务发生，因此不需要进行DML日志的记录

• 需要特别注意的是：由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL优化器不会选择正在创建中的索引