MySQL中冗余和重复索引的区别说明

mysql允许在单个列上创建多个索引，无论是有意还是无意，mysql需要单独维护这些重复索引，优化器在优化查询时也需要逐个考虑这会影响mysql的性能

概念阐述

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

冗余索引： 两个索引按照相同的顺序覆盖了相同的列。

创建的原因

一般来说，我们有时候会在不经意间创建了重复索引，例如下面的例子：

因为mysql的唯一限制和主键限制都是通过索引实现的，所以事实上使用如上代码创建的表实际上会在id列上创建3个索引。通常没有理由这样做，除非是在同一列上为了满足不同的查询需求创建不同类型的索引。比如key(col)和fulltext key(col)两种索引。

冗余索引和重复索引有些不同。如果创建了索引（a,b），再创建索引（a）就是冗余索引，因为这只是前一个索引的前缀索引。但是如果创建了索引（b,a），则不是冗余索引，索引（b）也不是冗余索引，因为它不是索引（a,b）的最左前缀列。除此之外，不同类型的索引也不会是b树索引的冗余索引，无论覆盖的索引列是什么。

在大多数情况下都不需要使用冗余索引，应该尽可能拓展已有的索引而不是创建新的索引。但有时候出于性能的考虑，比如拓展已有的索引会使得其变得太大，从而影响其他使用该索引的查询的性能。

影响

创建冗余索引作为覆盖索引可以提高我们对于部分查询的qps，但是存在两个索引也有缺点，即索引成本更高。

当表中的索引越来越多时，表的插入速度会变慢。一般而言，增加新索引将会导致insert、update、delete等操作的速度变慢，特别是当新增索引之后导致达到了内存的瓶颈的时候。

解决冗余索引和重复索引的方法很简单，删除这些索引就可以 ，但是首先要做的就是找出这样的索引。

补充：mysql中重复索引和重复外键清理

mysql允许在相同列上创建重复的索引，但这样做对数据库却是有害而无利的，需要定期检查此类重复索引以改善数据库性能。

可减少磁盘空间占用、减少磁盘io、减少优化器优化查询时需要比较的索引个数、减少数据库维护冗余索引的各类开销、提高数据库性能（插入、更新、删除）

重复索引检测

pt-duplicate-key-checker：通过show create table输出的表定义检测mysql表中重复或者冗余的索引或外键

可以检测到的冗余/重复索引类型：若某个索引和另外某个索引以同样的顺序包含同样的列，或者该索引包含的列是另外某个索引的最左前缀列，则被认为是重复/冗余的索引。

默认情况下只在同类型的索引间（如btree索引）进行比较，不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。

除此之外，还可检测重复的外键，即引用的表和列均相同的外键。对于聚簇索引的表，在辅助索引后添加主键列的索引也被认为是冗余的，因为这种情况下，辅助索引末尾本身就包含有主键信息。

基本用法以及样例输出如下

样例输出：

会给出重复/冗余类型、索引/外键定义、索引包含的列类型、移除重复/冗余索引/外键的sql、最后会给出有关索引的统计信息。

重复索引删除

直接执行工具输出结果中的alter table语句即可，但是执行前一定要仔细评估可能造成的影响。比如，表非常非常大的情况下可能造成主从复制延迟，又比如sql中若包含索引提示的话直接删除索引可能导致报sql语法错误，最好事先查一下是不是包含此类sql（可通过general log或者tcpdump工具获取sql并加以分析）

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。