MySQL中级优化教程（四）——索引的维护和优化

向右看齐 2022-01-14 02:43 300阅读 0赞

*  您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest：
 *  [https://blog.csdn.net/IT\_TIfarmer/article/details/92224646][https_blog.csdn.net_IT_TIfarmer_article_details_92224646]
 *  关于explain语句的结果参数详解也可以参考如上博文。

> ###  重复及冗余索引： ###

适当的建立索引，可以优化我们对数据库的查询操作效率，那么是不是索引越多越好呢？当然不是！

首先，建立索引本身就会对数据库的增删改操作的效率造成影响，更重要的是，当我们索引维护不当的时候，一个列上建立了很多索引，在我们对该列进行相关的查询操作时，数据库引擎会去一个一个的分析、比较这个列上所有索引，直到找出它认为最优的索引。

而这个过程，是很影响数据库查询效率的，因此我们要把索引的维护和优化放在比较重要的位置上。

那么如何优化索引呢？首先我们应该做的就是尽量消除重复和冗余的索引。

### 重复索引： ###

重复索引就是指在已经建立索引的列上，又加了一个以相同顺序建立的同类型的索引，如下表的primary key和id列上的索引就是重复索引：

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `name` varchar(10) NOT NULL,
      `title` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`)
    )

id列在被声明为主键时，就已经建立了主键索引，再为其建立一个唯一索引UNIQUE KEY，显然就是重复了（某种程度上来说，主键索引就是一种特殊的唯一索引，但二者本质是有别的）。

此时我们就需要对重复的索引进行删除操作,以达到优化的目的：

alter table test drop index id;

### 冗余索引： ###

如果两个索引所覆盖的列有重叠，或者和主键做联合索引，则称为冗余索引。

以下列举冗余索引出现的情况：

*  我们已经建立了一个联合索引（A,B），然后又建立了一个单列索引A在A列上，那么A就是冗余索引了。但是，如果我们建立的第二个索引是B，是存在于B列上的话，那么B就不是冗余索引，因为B不是联合索引（A,B）的前缀，与联合索引前缀重复，才算是冗余索引。
 *  反之亦然，假设我们已经建立了一个索引A，然后又建立了一个联合索引（A,B），那么（A,B）就是冗余索引，但如果我们第二次建立的索引是（B,A），那么它就不能称之为冗余索引。
 *  第三种情况：如果我们先建立了索引（A,B）,又建立了索引（B,A），那么（B,A）也是冗余索引，因为（B,A）包含了（A,B）的前缀列。
 *  还有一点需要我们注意：

innodb默认会隐式的把每个索引和主键建立联合索引，所以不需要再显式的和主键建立任何联合索引,而且就算我们手动建立联合索引是(name,id)而不是（id,name），纵然主键id不是它的前缀，它也仍然是冗余索引，比如下列情况：

CREATE TABLE `test2` (
      `id` int(11) NOT NULL,
      `name` varchar(10) NOT NULL,
      `title` varchar(50) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`,`id`)
    )

索引\`name\`就是一个冗余索引，一般情况下我们都要对其进行删除操作：

alter table test2 drop index name;

有意思的事情来了：冗余索引并不是在所有情况下都百害而无一利，在有些情况下，建立冗余索引以此达到索引覆盖的目的，反而对查询效率是一种优化~

如果您感兴趣的话可以看下这篇博客：[https://blog.csdn.net/baochao95/article/details/62439908][https_blog.csdn.net_baochao95_article_details_62439908]

说到这里，就不得不说一下一个非常好用的工具：[pt-duplicate-key-cheker][]，也是一个mysql的分析工具，它的作用非常强大，可以帮助我们分析数据库表的重复索引和冗余索引，甚至还可以很智能的给我们提供数据库索引优化的建议，比如删除两个冗余索引中的的哪一个！

唯一美中不足的是只有linux，没有windows版的~~~想学习它的使用的话，必须要在linux之下。  
下图为pt-duplicate-key-cheker的运行时截图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70][]

dept\_emp是表名，第一行emp\_no is a left-prefix of primary意思就是emp\_no列是另一个索引的左前缀，冗余索引就被检测到了。

> ### 删除不用索引： ###

在实际的开发过程中，随着业务需求的不断变更，难免会出现一些不再需要的索引。可能之前对某个索引的使用频率非常高，但某一段时间之后就不用了（可以联想一下某公司官网一个爆火的活动到了截止日期），那么，就会产生很多不需要的索引，这些索引的存在就是赘余，如何查定位这些索引呢？

目前MySql官方还不提供记录索引使用情况的功能，但是在PerconMysql和MariaDB中可以通过INDEX\_STATISTICS表来查看哪些索引未使用，但在MySql中目前只能通过慢查询日志配合py-index-usage工具来进行索引使用情况的分析。

删除索引语句：

alter test1 drop index XXX

[https_blog.csdn.net_IT_TIfarmer_article_details_92224646]: https://blog.csdn.net/IT_TIfarmer/article/details/92224646
[https_blog.csdn.net_baochao95_article_details_62439908]: https://blog.csdn.net/baochao95/article/details/62439908
[pt-duplicate-key-cheker]: https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUX1RJZmFybWVy_size_16_color_FFFFFF_t_70]: /images/20220113/d358bcafcdcf46839779df0ec813549d.png