mysql索引重复导致了什么_「Mysql索引原理（十）」冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个进行考虑，这会影响性能。

重复索引

重复索引是指在相同的列上按照相同的的顺序创建相同类型的索引。应该避免这样创建重复索引，发现以后应该立即删除。

工作中不经意间会创建重复索引，如：

create table test{

ID INT NOT NULL PRIMARY KEY,

A INT NOT NULL,

B INT NOT NULL,

UNIQUE(ID),

INDEX(ID)

}ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的。因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

冗余索引

概念

冗余索引和重复索引有一些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当做索引(A)来使用(这种冗余只是对B树索引来说的)。但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀列。另外，其他不同类型的索引(例如哈希索引)也不会是B树索引的冗余索引。

场景

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B)而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(A,ID),其中ID是主键，对于InnoDB来说主键列已经包含在了二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。特别是有查询把这个索引当做覆盖索引。

例子

考虑一下前面“在InnoDB中按主键顺序插入行”一节提到的userinfo表。这个表有100万行，对每个state_id值大概有20000条记录。在state_id列有个索引对下面的查询有用，假设查询名为Q1：

select count(*) from userinfo where state_id=5;

一个简单的测试表明，该查询的执行速度是每秒115次。还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为Q2：

select state_id ,city,address from userinfo where state_id=5;

对于这个查询，执行速度是每秒10次不到，提升该查询性能的最简单办法就是扩展索引为(state_id,city,address)，让索引能覆盖查询：

alter table userinfo drop key state_id , add key state_id_2 (state_id,city,address)

索引扩展后，Q2运行得更快，但是Q1变慢了(索引变大了)。如果我们想让两个查询都变得更快，就需要两个索引，尽管这样一来原来的单列索引是冗余的了。

这就带来了索引冗余的缺点，索引成本高了。插入时需要维护更多的索引，效率自然下降。一般来讲增加新索引会导致INSERT/UPDATE/DELETE等操作的速度变慢。

注意

在决定哪些索引可以被删除的时候要非常小心。回忆一下，在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列(A)上的索引就相当于在(A,ID)上的索引。如果有像WEHRE A=5 ORDER BY ID这样的查询，这个索引会很有作用。但如果将索引扩展为(A,B)，则实际上就变成了(A,B,ID)，那么上面查询的ORDER BY子句就无法使用该索引来做排序了，而只能用文件排序了。