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工具来仔细检查计划中的索引变更。

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值