MySQL -- Fast Index Creation

1.fast index creation简介

MySQL5.5之后,对innodb表创建或删除辅助索引的效率提升了很多,即增加了新的功能fast index creation。因为MySQL5.5之后,创建和删除辅助索引不在需要拷贝整个表的数据。

在5.5之前,在一个已经存在数据的表上增加或者删除索引是很耗时的。create index或drop index按照以下的方式进行工作:
-创建一个新的、空的临时表,表结构为使用alter table定义的新结构
-逐一拷贝数据到新表,插入数据行同时更新索引
-删除原表
-将新表的名字改为原表的名字

快速索引创建只是对辅助索引有效,对主键索引无效。innodb的表存储时是基于主键的聚集索引来组织的,在oracle中这种方式叫做“索引组织表”。因为表结构仅仅依赖于主键,所以重定义主键仍然要按照5.5之前的方式进行数据拷贝。

fast index creation机制也可以加快对索引组织表的load操作的效率。先创建只有聚集索引的表,数据load结束后,再创建辅助索引。

2.fast index creation的扩展
可以通过一条alter table语句在表上定义多个辅助索引。这样的效率会很高,因为聚集索引只需要被扫描一次。例如:

create table t1(a int primary key, b int, c char(1)) engine=innodb;
insert into t1 values(1,2,'a'),(2,3,'b'),(3,2,'c'),(4,3,'d'),(5,2,'e');
commit;
alter table t1 add index(b),add unique index(c);

如果在alter table之前,表中已经有大量的数据,这样会比先创建好所有的辅助索引后在加载数据的效率高。

也可分开创建辅助索引,但是分开创建每次都要执行一次聚集索引的扫描。效率会低。如:

create index b on t1 (b);
create unique index c on t1 (c);

删除辅助索引不需要拷贝数据。

innodb中重构聚集索引,无论是5.5之前还是之后,都需要拷贝表中的数据。如果用户创建表的时候,没有创建主键,innodb会自动提用户选择一个,通常是第一个唯一性非空索引或系统自动产生的键。
后期定义一个主键需要拷贝数据:

create table t2 (a int, b int) engine=innodb;
insert into t2 values (null, 1);
alter table t2 add primary key (b);

当创建一个唯一性或主键索引时,innodb需要做一些额外的工作。对于唯一性索引,innodb需要检查表是否包含重复的值;对于主键索引innodb除了检查是否有重复的值,还要检查是否有空值。
建议在创建表的时候就定义好主键,这样可以避免后期对表进行rebuild操作。

3.fast index creation的实现原理
innodb有两种类型的索引:聚集索引、辅助索引。
聚集索引包含Btree节点中数据,增加、删除聚集索引都要涉及拷贝数据、创建表新的拷贝;辅助索引只是包含索引键和主键的值,删除或新建都不要拷贝聚集索引中的数据。

当修改主键的时候,所有辅助索引都要被重新创建。

删除辅助索引比较简单。只会更新innodb内部系统表和mysql数据字典,来表明该索引已经不存在。innodb会归还该索引占用的存储空间。

增加辅助索引,innodb会扫描表,借助内存和临时文件排序辅助索引涉及的列,创建btree索引。

4.fast index creation对并发的考虑
在innodb创建、删除辅助索引的时候,表会被加上共享锁。任何写都会被阻止,但是可以读。
当修改聚集索引的时候,表会被排它锁锁住,因为要拷贝数据,在创建新的聚集索引的时候,所有操作都会被阻止。

create index、alter table都会等待表上的当前事务结束。alter table重新定义主键还会等待表上所有的select语句结束。在聚集索引重期间,任何请求都不支持,因为表要被删除和重建。

create index、alter table创建辅助索引的时候,对表的读操作可以执行,但是不能更新数据。

新创建的附注索引只是包含create index、alter table命令开始执行时候的数据。不包含任何未提交的值、老版本的值、以及被标记为删除但是尚未移除的值。

5.crash recovery和fast index creation如何工作
在mysql server 发生crash的时候,执行alter table 不会有数据丢失。但是,对聚集索引和辅助索引的crash recovery过程还是有区别的。

如果在创建辅助索引的时候发生了crash,recovery时,mysql会删除已经创建的部分索引。必须重新执行alter table、create index命令。

如果在创建聚集索引的时候发生了crash,recovery过程就比较复杂了,因为数据必须要全备拷贝到新建的聚集索引。innodb的表都是按照聚集索引存储的。

mysql创建新的聚集索引,通过将原表中的数据拷贝到一个临时表。一旦数据拷贝结束,原表就会被重命名成一个零时表,而新建的临时表会被改成原表的名字,接着删除原表。
如果在创建聚集索引的时候发生了crash,没有数据会被丢失,但是必须使用新建的临时表完成recovery。重建聚集索引和重定义主键都是很少发生,所以官方文档没有详细涉及如何recovery的过程,可能需要联系mysql service。

6.fast index creation的限制
在创建、删除过程要考虑下面因素:
-创建索引时,文件会被写到临时目录($tmpdir)。要确保临时目录空间足够大
-如果一条alter table对同一个表进行drop index,add index,无法使用fast index creation
-在临时表上创建索引,只能用数据拷贝,而不能用fast index creation
-为了避免innodb数据字典和mysql数据字典冲突,使用alter table ... change重命名列名的时候用表拷贝而不是fast index creation
-optimize table不支持fast index creation

上一篇:javascript进阶教程第二章对象案例实战


下一篇:UVa1601 - The Morning after Halloween [单向bfs]