告诉你Mysql数据库是否需要创建索引的判定标准

告诉你Mysql数据库是否需要创建索引的判定标准

• 来源:网络
• 更新日期:2020-07-15

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

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

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

关于这一点我想不用分析大家都知道了。