mysql优化（五）重复索引与冗余索引

发表于 | 分类于 |
字数统计

冗余索引（重点）

重复索引：

是指的在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复索引，发现以后也应该立即删除。重复索引没有任何帮助，只会增大索引文件，拖慢更新速度。

1
2
3
4
5
6
7
CREATE TABLE test(
  ID INT NOT NULL PRIMARY KEY,
  A INT NOT NULL,
  B INT NOT NULL,
  UNIQUE(ID),
  INDEX(ID),
) ENGINE=InnoDB;

这段SQL创建了3个重复索引。通常并没有理由这么做。

冗余索引

冗余索引是指两个索引覆盖的列有重叠，称之为冗余索引

比如文章和标签表

1
2
3
4
5
6
7
8
+-------+--------+---------+
|  id   | artid  |   tag   |
+-------+--------+---------+
|   1   |    1   |   PHP   |
|   2   |    1   |  Linux  |
|   3   |    2   |  MySQL  |
|   4   |    2   |  Oracle |
+-------+--------+---------+

在实际使用中有两种查询
Artid —> 查询文章 —> tag
Tag –> 查询文章 —> artid

列:
Selet tag from table where artid =2
Selet artid from table where tag=PHP

建立索引
Alter table 表名 add index …….
思路 1 :
Artid -查— tag -> index artid（artid）；
Tag –查— artid -> index tag(tag)
如图
Extra：using where
这种建立两个索引的思路不够优化

优化思路 1 思路 2（联合索引） ：
Artid —查—tag -> index artid（artid,tag）；
Tag –查—artid –> index tag(tag,artid)
如图
Extra：using where，using index 索引覆盖

具体在不同的数据库引擎上测试结果如下

列:
如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。特别是有查询把这个索引当做覆盖索引，或者这是MyISAM表并且有很多范围查询（由于MyISAM的前缀压缩）的时候。
有一个userinfo表。这个表有1000000行，对每个state_id值大概有20000条记录。在state_id列有一个索引对下面的查询有用，假设查询名为Q1：

1
SELECT count(*) FROM userinfo WHERE state_Id=5;

一个简单的测试表明该查询的执行速度大概是每秒115次(QPS)。还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为Q2：

1
SELECT state_id,city,address FROM userinfo WHERE state_id=5;

对于这个查询，测试结果QPS小于10。提升该查询性能的最简单办法就是扩展索引为(state_id,city,address)，让索引能覆盖查询：

1
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没有使用索引压缩。
如图
有两个索引的缺点是索引成本更高。图2显示了想表中插入100万行数据所需要的时间。
如图
可以看到，表中的索引越多插入速度越慢。一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候。
解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这样的索引。