四、索引的利弊
索引为我们带来的好处主要有以下两点:1、缩小检索范围,加快检索速度;2、在索引上排序及group by资源消耗极低。
同时,大量使用索引也会给我们带来以下弊端:1、增删改操作将比原来更加耗时;2、索引的存储会占用存储空间。
五、创建索引的考虑点
当要为表创建索引时,我们可以从以下几个方面考虑是否应该创建索引:
1、较频繁的作为查询条件的字段应该创建索引;
2、唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件;
假如有以下场景,我们通过索引查找键值为A和B的某些数据。在通过A键值找到第一条满足要求的记录后,会读取这条记录所在的 X 数据页,然后继续往下查找索引,发现 A 键值所对应的另外一条记录也满足要求,但是这条记录不在 X 数据页上,而在Y数据页上,这时候存储引擎就会丢弃X数据页,而读取Y数据页。如此继续一直到查找完A键值所对应的所有记录。然后轮到B键值了,这时发现 正在查找的记录又在X数据页上,可之前读取的 X 数据页已经被丢弃了,只能再次读取 X 数据页。这时候,实际上已经重复读取 X 数据页两次了。在继续往后的查找中,可能还会出现一次又一次的重复读取,这无疑给存储引擎极大地增加了IO访问量。
很多比较有经验的 Query 调优专家经常说,当一条Query返回的数据超过了全表的 15%时,就不应该再使用索引扫描来完成这个 Query 了。对于"15%"这个数字我们并不能判定是否很准确,但是至少侧面证明了唯一性太差的字段并不适合创建索引。
3、更新非常频繁的字段不适合创建索引,因为添加索引后字段更新将更加耗时;
六、使用索引的注意项
1、对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用。例如在a,b,c列上创建索引,那么查询条件"a=5","a=5 and b=6","a=7 and b=8 and c=6"都会使用该索引;
2、对于使用like的查询,查询如果是‘%aaa’不会使用到索引‘aaa%’会使用到索引;
3、如果条件中有or,那么除非or条件都带有索引,否则还是会全表扫描;
4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引;
5、如果字符串较长时,直接使用'create index KEY_NAME on table6 (name)' 命令创建的索引也将较长,浪费磁盘空间;这时候我们可以通过'create index KEY_NAME on table6 (name(4))' 命令限定索引长度;
6、连接查询时,在连接键上增加索引可以加快速度,同时遵循小表驱动大表原则;
7、如果mysql估计使用全表扫描要比使用索引快,则不使用索引。