B+树索引的管理

B 树索引的管理

MySQL 5.5版本之前，索引的添加或者删除，MYSQL数据库的操作过程：

1.创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构

2.把原表中的数据导入到临时表

3.删除原表

4.把临时表重命名为原来的表名

这个过程有一个明显的问题，如果是对于一张数据量很大的表进行索引的添加或者删除操作，那么会耗时很长，并且如果有大量事务需要访问正在被修改的表，此时数据库服务是不可用的。

Fast Index Creation（快速索引创建，只限于辅助索引） from InnoDB 1.0.x

对于辅助索引的创建，InnoDB存储引擎会对数据表加上一个S锁。在创建索引的过程中只能对该表进行读操作，如果有大量事务需要对表进行写操作，那么数据库服务同样不可用。

在索引创建过程中，不需要重建表，所以速度会比之前的方式有显著提升，并且创建索引期间数据库也可以提供服务。

删除索引的过程是，InnoDB存储引擎只需要更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图山对该表的索引定义即可。

Online Schema Change

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

Facebook采用PHP脚本来实现OSC，而并不是通过修改InnoDB存储引擎源码的方式。

Online DDL（在线数据定义） from MySQL 5.6

其允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE、DELETE这类DML操作，这极大地提高了MySQL数据库在生产环境中的可用性。

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

ALTER TABLE table_name

| ADD {INDEX|KEY} [indexname] [indextype] (indexcolname,…) [indexoption] ...

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM指定了创建或删除索引的算法：

COPY：表示按照MySQL 5.1版本之前的工作模式，即创建临时表的方式。

INPLACE：表示索引创建或删除操作不需要创建临时表。

DEFAULT：表示根据参数oldalter_table来判断是通过INPLACE还是COPY算法，默认值是OFF，表示INPLACE。

LOCK部分为索引创建或删除时对表添加锁的情况：

NONE：执行索引创建或删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。这种模式可以获得最大的并发度。

SHARE：和之前的FIC类似，执行索引创建或者删除操作时，对目标表加上一个S锁。读事务可以并发执行，写事务需要等待。

EXCLUSIVE：在该模式下，执行索引创建或删除操作时，对目标表加上一个X锁。读写事务都不行进行，即会阻塞所有的线程，这和COPY方式运行得到的状态类似，但是不需要像COPY方式那样创建一张临时表。

DEFAULT：该模式首先会判断当前操作是否可以使用NONE模式。如果不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。简而言之，DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。

InnoDB存储引擎实现Online DDL的原理

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

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

这篇好文章是转载于：学新通技术网