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-Tree索引来说的）。但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀。另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。
冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B)而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(A,ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。特别是有查询把这个索引当做覆盖索引，或者这是MyISAM表并且有很多范围查询（由于MyISAM的前缀压缩）的时候。
有一个userinfo表。这个表有1000000行，对每个state_id值大概有20000条记录。在state_id列有一个索引对下面的查询有用，假设查询名为Q1：

SELECT count(*) FROM userinfo WHERE state_Id=5;

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

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

对于这个查询，测试结果QPS小于10。提升该查询性能的最简单办法就是扩展索引为(state_id,city,address)，让索引能覆盖查询：

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

索引扩展后，Q2运行得更快了，但是Q1却变慢了。如果我们想让两个查询都变得更快，就需要两个索引，尽管这样一来原来的单列索引是冗余的了。图1显示这两个查询在不同索引策略下的详细结果，分别使用MyISAM和InnoDB存储引擎。注意到只有state_id_2索引时，InnoDB引擎上的查询Q1的性能下降并不明显，这是因为InnoDB没有使用索引压缩。
图1
有两个索引的缺点是索引成本更高。图2显示了想表中插入100万行数据所需要的时间。
图2
可以看到，表中的索引越多插入速度越慢。一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找，不过还有两个更简单的方法。可使用Shlomi Noach的common_schema中的一些视图来定位，common_schema是一系列可以安装到服务器上的常用的存储和视图。这笔自己编写查询要快而且简单。另外也可以使用Percona Toolkit中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复的索引。对于大型服务器来说，使用外部的工具可能更合适些；如果服务器上有大量的数据或者大量的表，查询INFORMATION_SCHEMA表可能会导致性能问题。
在决定哪些索引可以被删除的时候要非常小心。回忆一下，在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列(A)上的索引就相当于在(A,ID)上的索引。如果有像WHERE A = 5 ORDER BY ID这样的查询，这个索引会很有作用。但如果将索引扩展为(A,B)，则实际上就变成了(A,B,ID)，那么上面查询的ORDER BY子句就无法使用该索引做排序，而只能用文件排序了。所以，建议使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。