Schema的优化和索引 - 高性能的索引策略 - 冗余和重复的索引

Schema的优化和索引 - 高性能的索引策略 - 冗余和重复的索引

　　MySQL可以在一个列上创建多个索引；这么做并不会提醒和防止发生错误。MySQL必须单独维护每一个重复的索引，当语句优化器优化语句的时候，它会参考这些索引。这样会对性能造成影响。

　　重复的索引是那些具有相同类型，在同顺序下的相同一组索引。你应该避免这样来创建它们并且如果发现它们要尽早的删除。

　　有的时候你可能在不知道的情况下就创建了重复索引。比如，如下的代码

CREATE TABLE test (
　　 ID INT NOT NULL PRIMARY KEY,
　　 UNIQUE(ID),
　　 INDEX(ID)
);

　　一个没有经验的用户可能想到要使列的作为主键，要加上一个UNIQUE约束，并且添加一个索引。事实上，MySQL实现了UNIQUE约束和PRIMARY KEY索引。因此实际上，在一个列上已经创建了三个索引了。没有必要这样做，除非你要在同样的列上创建不同类型的索引来满足不同类型的查询。

　　冗余的索引和重复的索引有所不同。如果在（A,B）上有个索引，另一个在（A）上的索引就是冗余的。因为它是第一个索引的前缀。(A,B)上的索引也可以当作（A）上的索引来单独使用。然而，一个（B,A）上的索引就不冗余，（B）上的索引也是不冗余的，因为它并不是（A,B）索引的前缀。进一步的说，不同的索引类型对于B-Tree索引都不是冗余的，不管它们所覆盖的列是怎样。

　　当人们添加索引的时候，经常会出现冗余索引。比如，有的人在（A,B）添加了索引，用来取代扩展现有（A）的索引。

　　大部分的情况，你都不会想要冗余索引，并且要避免它们，你应该扩展现有索引而不是添加一个新的。也有的时候可能因为性能的原因你需要使用冗余索引。使用冗余索引的主要原因就是当扩展现有的索引，这个冗余索引会使它变得更大一些。

　　举个例子，如果你有一个在整型列上的索引并且你扩展为这个整型列和一个VARCHAR列上的索引，它会变慢。如果你的查询覆盖了这个索引，或者如果表是MyISAM的以及需要范围扫描（因为MyISAM使用前缀压缩），变慢的情况就会发生。

　　考虑下userinfo表。在前面的教程中所提到过。这个表有1，000，000行并且每个state_id有20，000记录。有个索引在state_id上。这对下列的查询很有用。Q1语句如下：

　　mysql> SELECT count(*) FROM userinfo WHERE state_id=5;

　　一个简单的基准测试显示执行率是每秒115条（QPS）。我们在看看相关查询Q2。

　　mysql> SELECT state_id, city, address FROM userinfo WHERE state_id=5;

　　对于这个查询，结果少于10QPS.简单的性能提高方法就是扩展索引（state_id, city, address）。因此索引覆盖了查询。

mysql> ALTER TABLE userinfo DROP KEY state_id,
　　 　-> ADD KEY state_id_2 (state_id, city, address);

　　在修改索引之后，Q2变快了，Q1却慢了。如果我们想让两个查询都快的话，我们应该留下这两个索引，即使那个单独的索引是冗余的。下面的表给出了在MyISAM和INNODB中这两个索引的表现。注意在使用state_id2索引的时候，InnoDB的Q1并没有降低多少性能，因为InnoDB没有键压缩。

　　 　 　 　 　 　 　 　 　 　 　 　 　 　state_id only 　 　 　 　 　state_id_2 only 　 　 　 　 　 　Both state_id and　state_id_2
MyISAM, Q1 　 　 　 　 114.96 　 　 　 　 　 　 　 　 　 　 　 　 　25.40 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　112.19
MyISAM, Q2 　 　 　 　 　9.97 　 　 　 　 　 　 　 　 　 　 　 　 　 　 16.34 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 16.37
InnoDB, Q1 　 　 　 　 　 108.55 　 　 　 　 　 　 　 　 　 　 　 　 100.33 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　107.97
InnoDB, Q2 　 　 　 　 　12.12 　 　 　 　 　 　 　 　 　 　 　 　 　 　 28.04 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 28.06

　　同时使用两个索引的缺点是维护消耗大。下面的数据是插入1百万行性能。

　　 　 　 　 　 　 　 　 　 　 　 　 　state_id only 　 　 　 　 Both state_id and state_id_2
InnoDB, enough memory for both　indexes 　 　 　 　　80 seconds 　 　 　 　 　 　 　 　 　 　136 seconds
MyISAM, enough memory for only　one index 　 　 　72 seconds 　 　 　 　 　 　 　 　 　 　470 seconds

　　你也看到了，插入数据的性能很差。一般来说如下说法是正确的：添加一个索引会对INSERT,UPDATE,DELETE操作性能影响很大，尤其是新的索引达到了内存的限制。