索引的利与弊

索引的优点：

为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。 
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。 
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

第一，创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。 
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

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

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 子句中的字段不该创建索引