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操作性能影响很大，尤其是新的索引达到了内存的限制。