MySQL: InnoDB表上的每个索引创建时间都比上一个索引长

Question

我有一个MySQL表，如下所示：

CREATE TABLE my_facts (
  `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, 
  `account_id` int(11) NOT NULL, 
  `asked_on` date NOT NULL, 
  `foo_id` int(11) NOT NULL, 
  `bar_id` int(11) NOT NULL, 
  `baz_id` int(11) NOT NULL, 
  `corge_id` int(11) NOT NULL, 
  `grault_id` int(11) NOT NULL, 
  `flob_id` int(11) NOT NULL, 
  `tag_id` int(11) NOT NULL) 
ENGINE=InnoDB;

并且有45万行。但是:我想给它添加几个索引：

CREATE INDEX `k_account_foo_id` ON `my_facts` (`account_id`, `asked_on`, `foo_id`, `tag_id`);

CREATE INDEX `k_account_bar_id` ON `my_facts` (`account_id`, `asked_on`, `bar_id`, `tag_id`);

CREATE INDEX `k_account_baz_id` ON `my_facts` (`account_id`, `asked_on`, `baz_id`, `tag_id`);

CREATE INDEX `k_account_corge_id` ON `my_facts` (`account_id`, `asked_on`, `corge_id`, `tag_id`);

CREATE INDEX `k_account_grault_id` ON `my_facts` (`account_id`, `asked_on`, `grault_id`, `tag_id`);

我的问题是，每个索引的创建时间都比上一个索引更长--而且它似乎处于几何轨迹上。按照顺序，创建索引需要11.6s、28.8s、44.4s、76s和128s。我想再添加几个索引。

当我将表创建为MyISAM时，不仅整个过程快了很多，而且创建每个后续索引所需的时间可能比前一个索引多出一秒。

怎么回事？这是预期的行为吗？我在创建索引时做了什么有趣的事情吗？

无论如何，我在本测试中使用的是MySQL 5.1.48/OS 10.6.8。

Answer 1

这是基于how index creation happens in InnoDB的预期行为。

在MySQL 5.0版之前的版本中，如果表有很多行，那么在包含现有数据的表上添加或删除索引的速度可能会非常慢。CREATE INDEX和DROP INDEX命令的工作方式是创建一个用所请求的一组索引定义的新的空表。然后，它将现有行逐个复制到新表中，并在执行过程中更新索引。以这种方式向索引中插入条目需要随机访问索引节点，而且远不是最优的。复制原始表中的所有行后，将删除旧表，并使用原始表的名称重命名副本。

从5.1版开始，MySQL允许存储引擎创建或删除索引，而无需复制整个表的内容。但是，MySQL版本5.1中的标准内置InnoDB没有利用此功能。