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

最新推荐文章于 2022-03-26 14:34:06 发布

xiayuanfeng

最新推荐文章于 2022-03-26 14:34:06 发布

阅读量111

点赞数

分类专栏：高性能MySQL 文章标签： MySQL

高性能MySQL 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

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