-
您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest:
-
关于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
说到这里,就不得不说一下一个非常好用的工具:pt-duplicate-key-cheker,也是一个mysql的分析工具,它的作用非常强大,可以帮助我们分析数据库表的重复索引和冗余索引,甚至还可以很智能的给我们提供数据库索引优化的建议,比如删除两个冗余索引中的的哪一个!
唯一美中不足的是只有linux,没有windows版的~~~想学习它的使用的话,必须要在linux之下。
下图为pt-duplicate-key-cheker的运行时截图:
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