MySQL 冗余和重复索引-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL 冗余和重复索引

简介:

                                       冗余和重复索引

冗余和重复索引的概念:

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

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

eg:有时会在不经意间创建了重复索引

1
2
3
4
5
CREATE TABLE test (
  id INT NOT NULL PRIMARY KEY,
  a  INT NOT NULL,
  INDEX(ID)
)ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键,然后再加上索引以供查询使用。事实上主键也就是索引了。所以完全没必要再添加INDEX(ID)了。

冗余索引和重复索引有一些不同,如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。因此索引(A,B)也可以当索引(A)来使用(这种冗余只是对B-Tree索引来说)。冗余索引通常发生在为表添加新索引的时候。例如,有人可能会增加一个新的索引(A,B)而不是扩展已有的索引(A)。还有一种情况是将一个索引扩展为(A,ID),其中ID是主键,对于InnoDB来说主键列已经包含在二级索引中了,索引也是冗余的。

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

eg:如果在整数列上有一个索引,现在需要额外增加一个很长的VARCHAR列来扩展该索引,那性能可能会急剧下降。特别是有查询把这个索引当作覆盖索引,或者这是MyISAM表并且有很多范围查询的时候。

另外注意到:表中的索引越多插入速度会越慢。一般来说,增加新索引将会导致INSERT,UPDATE,DELETE等操作的速度变慢,特别是当新增索引后导致达到了内存瓶颈的时候。


解决冗余索引和重复索引的方法:

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

方法:

1:可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找。

2:通过common_schema中的一些视图来定位

3:通过Percona Toolkit中的pt-duplicate-key-checker工具

eg: pt-duplicate-key-checker工具的使用

首先pt-duplicate-key-checker工具的安装,参考相关官方手册。

使用语法:

pt-duplicate-key-checker[OPTIONS][DSN]

主要参数的介绍:

-u                    :指定连接数据库的用户名

-p                    :指定连接数据库的密码

--charset         :指定字符集

--database       :指定要检查的数据库名列表

实例如下:

1
2
pt-duplicate-key-checker -udbuser -pdbpaswd --charset=gbk \
--database=dbname

执行过后将会统计出有关dbname数据库的重复和冗余的索引,内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# ########################################################################
# dbname.test1                                             
# ########################################################################
# vkey is a left-prefix of keydesc_index
# Key definitions:
#   KEY `vkey` (`VehicleKey`),
#   KEY `keydesc_index` (`VehicleKey`,`Description`)
# Column types:
#         `vehiclekey` char(8) not null default ''
#         `description` char(255) not null default ''
# To remove this duplicate index, execute:
ALTER TABLE `dbname`.`test1` DROP INDEX `vkey`;
# ########################################################################
# dbname.test2                                              
# ########################################################################
# vkey is a duplicate of PRIMARY
# Key definitions:
#   KEY `vkey` (`VehicleKey`),
#   PRIMARY KEY (`VehicleKey`),
# Column types:
#         `vehiclekey` varchar(8) not null default '0'
# To remove this duplicate index, execute:
ALTER TABLE `dbname`.`test2` DROP INDEX `vkey`;

它会统计出所有出现的重复,冗余的索引,还将要执行的SQL语句也提供了,是不是很方便。

想了解其工具所有参数或其用法的请参考:pt-duplicate-key-checker










本文转自 kuchuli 51CTO博客,原文链接:http://blog.51cto.com/lgdvsehome/1278740,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章