mysql索引利弊分析

转载自：http://blog.csdn.net/linminqin/article/details/44342205 

索引的利弊与如何判定，是否需要索引

相信读者都知道索引能够极大地提高数据检索的效率，让Query 执行得更快，但是可能并不是每一位朋友都清楚索引在极大提高检索效率的同时，也给数据库带来了一些负面的影响。下面就分别对 MySQL 中索引的利与弊做一个简单的分析。

索引的好处

索引带来的益处可能很多读者会认为只是”能够提高数据检索的效率，降低数据库的IO成本”。

确实，在数据库中表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件时可以极大地提高检索效率，加快检索时间，降低检索过程中须要读 取的数据量。但是索引带来的收益只是提高表数据的检索效率吗？当然不是，索引还有一个非常重要的用途，那就是降低数据的排序成本。

我们知 道，每个索引中的数据都是按照索引键键值进行排序后存放的，所以，当Query 语句中包含排序分组操作时，如果排序字段和索引键字段刚好一致，MySQL Query Optimizer 就会告诉 mysqld 在取得数据后不用排序了，因为根据索引取得的数据已经满足客户的排序要求。

那如果是分组操作呢？分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的，所以当Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用索引已经排好序的这个特性，省略掉分组中的排序操作。

排序分组操作主要消耗的是内存和 CPU 资源，如果能够在进行排序分组操作中利用好索引，将会极大地降低CPU资源的消耗。

索引的弊端

索引的益处已经清楚了，但是我们不能只看到这些益处，并认为索引是解决 Query 优化的圣经，只要发现 Query 运行不够快就将 WHERE 子句中的条件全部放在索引中。

确实，索引能够极大地提高数据检索效率，也能够改善排序分组操作的性能，但有不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。假 设在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同时，都须要更新Column ca 的索引数据，调整因为更新带来键值变化的索引信息。而如果没有对 Column ca 进行索引，MySQL要做的仅仅是更新表中 Column ca 的信息。这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca须要占用存储空间，而且随着 Table ta 数据量的增加，idx_ta_ca 所占用的空间也会不断增加，所以索引还会带来存储空间资源消耗的增加。

如何判定是否须要创建索引

在了解了索引的利与弊之后，那我们到底该如何来判断某个索引是否应该创建呢？

实际上，并没有一个非常明确的定律可以清晰地定义什么字段应该创建索引，什么字段不该创建索引。因为应用场景实在是太复杂，存在太多的差异。当然，还是仍然能够找到几点基本的判定策略来帮助分析的。

1. 较频繁的作为查询条件的字段应该创建索引

提高数据查询检索的效率最有效的办法就是减少须要访问的数据量，从上面索引的益处中我们知道，索引正是减少通过索引键字段作为查询条件的 Query 的IO量之最有效手段。所以一般来说应该为较为频繁的查询条件字段创建索引。

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

唯一性太差的字段主要是指哪些呢？如状态字段、类型字段等这些字段中存放的数据可能总共就是那么几个或几十个值重复使用，每个值都会存在于成千上万 或更多的记录中。对于这类字段，完全没有必要创建单独的索引。因为即使创建了索引，MySQL Query Optimizer 大多数时候也不会去选择使用，如果什么时候 MySQL Query Optimizer选择了这种索引，那么非常遗憾地告诉你，这可能会带来极大的性能问题。由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引 访问数据的时候会带来大量的随机IO，甚至有些时候还会出现大量的重复IO。

这主要是由于数据基于索引扫描的特点引起的。当我们通过索引访问表中数据时，MySQL 会按照索引键的键值顺序来依序访问。一般来说，每个数据页中大都会存放多条记录，但是这些记录可能大多数都不会和你所使用的索引键的键值顺序一致。

假如有以下场景，我们通过索引查找键值为A和B的某些数据。在通过A键值找到第一条满足要求的记录后，会读取这条记录所在的 X 数据页，然后继续往下查找索引，发现 A 键值所对应的另外一条记录也满足要求，但是这条记录不在 X 数据页上，而在Y数据页上，这时候存储引擎就会丢弃X数据页，而读取Y数据页。如此继续一直到查找完A键值所对应的所有记录。然后轮到B键值了，这时发现 正在查找的记录又在X数据页上，可之前读取的 X 数据页已经被丢弃了，只能再次读取 X 数据页。这时候，实际上已经重复读取 X 数据页两次了。在继续往后的查找中，可能还会出现一次又一次的重复读取，这无疑给存储引擎极大地增加了IO访问量。

不仅如此，如果一个键值对应了太多的数据记录，也就是说通过该键值会返回占整个表比例很大的记录时，由于根据索引扫描产生的都是随机 IO，其效率比进行全表扫描的顺序IO效率低很多，即使不会出现重复 IO 的读取，同样会造成整体 IO 性能的下降。

很多比较有经验的 Query 调优专家经常说，当一条Query返回的数据超过了全表的 15%时，就不应该再使用索引扫描来完成这个 Query 了。对于”15%”这个数字我们并不能判定是否很准确，但是至少侧面证明了唯一性太差的字段并不适合创建索引。

3. 更新非常频繁的字段不适合创建索引

上面在索引的弊端中已经分析过了，索引中的字段被更新的时候，不仅要更新表中的数据，还要更新索引数据，以确保索引信息是准确的。这个问题致使IO 访问量较大增加，不仅仅影响了更新 Query 的响应时间，还影响了整个存储系统的资源消耗，加大了整个存储系统的负载。

当然，并不是存在更新的字段就适合创建索引，从判定策略的用语上也可以看出，是”非常频繁”的字段。到底什么样的更新频率应该算是”非常频繁”呢？ 每秒？每分钟？还是每小时呢？说实话，还真难定义。很多时候是通过比较同一时间段内被更新的次数和利用该字段作为条件的查询次数来判断的，如果通过该字段 的查询并不是很多，可能几个小时或是更长才会执行一次，更新反而比查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过该字段的查询比较频 繁，但更新并不是特别多，比如查询几十次或更多才可能会产生一次更新，那我个人觉得更新所带来的附加成本也是可以接受的。

4. 不会出现在 WHERE 子句中的字段不该创建索引

不会还有人会问为什么吧？自己也觉得这是废话了，哈哈！

8.4.6  单键索引还是组合索引

在大概了解了MySQL 各种类型的索引，以及索引本身的利弊与判断一个字段是否须要创建索引之后，就要着手创建索引来优化Query 了。在很多时候，WHERE 子句中的过滤条件并不只是针对于单一的某个字段，经常会有多个字段一起作为查询过滤条件存在于 WHERE 子句中。在这种时候，就必须要判断是该仅仅为过滤性最好的字段建立索引，还是该在所有字段（过滤条件中的）上建立一个组合索引。

对于这种问题，很难有一个绝对的定论，须要从多方面来分析考虑，平衡两种方案各自的优劣，然后选择一种最佳的方案。因为从上一节中已了解到索引在提 高某些查询的性能同时，也会让某些更新的效率下降。而组合索引中因为有多个字段存在，理论上被更新的可能性肯定比单键索引要大很多，这样带来的附加成本也 就比单键索引要高。但是，当WHERE 子句中的查询条件含有多个字段时，通过这多个字段共同组成的组合索引的查询效率肯定比只用过滤条件中的某一个字段创建的索引要高。因为通过单键索引过滤的 数据并不完整，和组合索引相比，存储引擎须要访问更多的记录数，自然就会访问更多的数据量，也就是说需要更高的 IO 成本。

可能有朋友会说，那可以创建多个单键索引啊。确实可以将 WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQL Query Optimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或更多的索引通过 INDEX_MERGE 来优化查询，所收到的效果可能并不会比选择其中某一个单键索引更高效。因为如果选择通过 INDEX_MERGE 来优化查询，就须要访问多个索引，同时还要将几个索引进行 merge 操作，这带来的成本可能反而会比选择其中一个最有效的索引更高。

在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据，而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引， 尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

此外，MySQL 还提供了另外一个优化索引的功能，那就是前缀索引。在 MySQL 中，可以仅仅使用某个字段的前面部分内容做为索引键索引该字段，以达到减小索引占用的存储空间和提高索引访问效率的目的。当然，前缀索引的功能仅仅适用于 字段前缀随机重复性很小的字段。如果须要索引的字段前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索 引虽然能够减少存储空间消耗，但是可能会造成 Query 访问效率的极大降低，得不偿失。

摘自http://www.canphp.com/article/show-130.html

复合索引优化

两个或更多个列上的索引被称作复合索引。
利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿 首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不 姓，电话簿将没有用处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
如：建立 姓名、年龄、性别的复合索引。

复合索引的建立原则：

如果您很可能仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。
如上图所示，如果查询中需要对年龄和性别做查询，则应当再新建一个包含年龄和性别的复合索引。
包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索，确定哪一列应该排在最前面。
请注意，创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

         
复合索引对排序的优化：

复合索引只对和索引中排序相同或相反的order by 语句优化。
在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引：

  

其中 有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询

1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC

  和索引顺序相同

2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC

 和索引顺序相反

查询1，2 都可以别复合索引优化。

如果查询为：

Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

  排序结果和索引完全不同时，此时的查询不会被复合索引优化。

查询优化器在在where查询中的作用：

如果一个多列索引存在于 列 Col1 和 Col2 上，则以下语句：Select   * from table where   col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。

1． 如果存在一个多列索引，任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面。

如：一个多列索引为 (col1 ，col2， col3)

    那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。

2． 如果列不构成索引的最左面前缀，则建立的索引将不起作用。

如：

3． 如果一个 Like 语句的查询条件不以通配符起始则使用索引。

如：%车 或 %车%   不使用索引。

    车%              使用索引。

索引的缺点：

1.       占用磁盘空间。

2.       增加了插入和删除的操作时间。一个表拥有的索引越多，插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

下面是一些常见的索引限制问题

1、使用不等于操作符(<>, !=)

下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？

有！

通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。

2、使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图 索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

3、使用函数

如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：

  

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。

下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

  

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。

把SQL语句改为如下形式就可以使用索引

恩，这里还有要注意的：

来自老王的博客（http://hi.baidu.com/thinkinginlamp/blog/item/9940728be3986015c8fc7a85.html）

比方说有一个文章表，我们要实现某个类别下按时间倒序列表显示功能：

SELECT * FROM articles WHERE category_id = … ORDER BY created DESC LIMIT …

这样的查询很常见，基本上不管什么应用里都能找出一大把类似的SQL来，学院派的读者看到上面的SQL，可能会说SELECT *不好，应该仅仅查询需要的字段，那我们就索性彻底点，把SQL改成如下的形式：

SELECT id FROM articles WHERE category_id = … ORDER BY created DESC LIMIT …

 

我们假设这里的id是主键，至于文章的具体内容，可以都保存到memcached之类的键值类型的缓存里，如此一来，学院派的读者们应该挑不出什么毛病来了，下面我们就按这条SQL来考虑如何建立索引：

不考虑数据分布之类的特殊情况，任何一个合格的WEB开发人员都知道类似这样的SQL，应该建立一个”category_id, created“复合索引，但这是最佳答案不？不见得，现在是回头看看标题的时候了：MySQL里建立索引应该考虑数据库引擎的类型！

如果我们的数据库引擎是InnoDB，那么建立”category_id, created“复合索引是最佳答案。让我们看看InnoDB的索引结构，在InnoDB里，索引结构有一个特殊的地方：非主键索引在其BTree的叶节 点上会额外保存对应主键的值，这样做一个最直接的好处就是Covering Index，不用再到数据文件里去取id的值，可以直接在索引里得到它。

如果我们的数据库引擎是MyISAM，那么建立”category_id, created”复合索引就不是最佳答案。因为MyISAM的索引结构里，非主键索引并没有额外保存对应主键的值，此时如果想利用上Covering Index，应该建立”category_id, created, id”复合索引。

唠完了，应该明白我的意思了吧。希望以后大家在考虑索引的时候能思考的更全面一点，实际应用中还有很多类似的问题，比如说多数人在建立索引的时候不从 Cardinality（SHOW INDEX FROM …能看到此参数）的角度看是否合适的问题，Cardinality表示唯一值的个数，一般来说，如果唯一值个数在总行数中所占比例小于20%的话，则 可以认为Cardinality太小，此时索引除了拖慢insert/update/delete的速度之外，不会对select产生太大作用；还有一个 细节是建立索引的时候未考虑字符集的影响，比如说username字段，如果仅仅允许英文，下划线之类的符号，那么就不要用gbk，utf-8之类的字符 集，而应该使用latin1或者ascii这种简单的字符集，索引文件会小很多，速度自然就会快很多。这些细节问题需要读者自己多注意，我就不多说了。

1. 对于具有2个用and连接条件的语句，且2个列之间的关联度较低的情况下，multi col index有一定优势。
2. 对于具有2个用and连接条件的语句，且2个列之间的关联度较高的情况下，multi col index有很大优势。
3. 对于具有2个用or连接条件的语句，isolate col index有一定优势，因为这种情况下multi col index 将会导致全表扫描，而前者可以用到index merge的优化。