适合添加索引的情况
1.字段的数值有唯一性的限制
索引本身可以起到约束的作用,比如唯一索引、主键索引都可以起到唯一性约束的,因此我们在创建数据表时,如果某个字段时唯一的,就可以直接创建唯一性索引或主键索引。不要以为唯一索引影响了 insert 的速度,这个速度损耗可以忽略不计,单体高查找速度是明显的。
2.频繁作为 where 查询条件的字段
如果某个字段经常在(包括 insert、update、delete 的) where 条件中被使用到,那么就需要给这个字段创建索引。尤其是在数据量大的情况下,创建普通索引就可以大幅提高查询效率。
3.经常 group by 和 order by 的列
索引就是让数据按照某种顺序进行存储或检索,因此我们使用 group by 对数据进行分组查询,或者使用 order by 对数据进行排序的时候,就需要对分组或者排序的字段添加索引。如果待排序的列有多个,那么就可以在这些列上建立组合索引。
4.distinct 字段需要创建索引
有时我们需要对某个字段进行去重,使用 distinct,那么对这个字段创建索引,就能大幅提高效率。
5.多表 join 连接操作时
首先,连接表的数量尽量不要超过3张,因为每增加一张表就相当于增加了一次嵌套的循环,数量级增长的非常快,严重影响查询效率。
其次,需要对 where 条件中的字段创建索引,因为 where 才是对数据条件的过滤。如果在数据量非常大的情况下,没有 where 条件过滤时非常可怕的。
最后,对用于连接的字段进行创建索引,并且该字段在多张表中的类型必须一致。因为如果数据类型不一致会进行隐式转换,索引就会失效。
6.存储长字符串时建议使用字符串前缀创建索引
假设我们的字符串很长,那存储字符串就需要占用很大的存储空间。在我们需要为这个字符串列创建索引时,那就意味着对于的 b+ 树种有这么两个问题:
- b+ 树索引中的记录需要把列的完整字符串存储起来,很费时。并且字符串越长,在索引中占用的存储空间就越大。
- 如果 b+ 树索引中索引列存储的字符串很长,拿在做字符串比较时会占用更多的时间。
因此我们可以通过截取字符串前面一部分内容建立索引,这就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置,但是能定位到相应前缀所在的位置,然后根据前缀相同的记录的主键回表查询完整的字符串值。即节约空间又减少了字符串的比较时间,还可以答题解决排序的问题。
注意:如果使用了索引列前缀的方式可能会导致使用索引排序时结果出错,只能使用文件排序。
7.区分度高(散列性高)的列适合创建索引
列的基数指的是某一列中重复的个数,也就是说,在记录行数一定的情况下,列的基数越大,该列中的数值越分散;列的基数约小,该列中的数值越集中。这个列的基数指标会直接影响我们是否能有效的利用索引,为基数太小的列创建索引的效果可能不好。
可以使用公式 select count(distinct a)/count(*) from table
计算区分度,越接近1月号,一般超过 0.33 就算是基数比较高的列了。因此,有大量重复数据的列上就不用建立索引了。
不适合添加索引的情况
1.在条件判断中没有使用的字段不用创建索引
在 where、group by、order by 里用不到的字段不需要创建索引,索引的价值时快速定位,如果起不到定位的字段通常是不需要建立索引的。
2.数据量小的表最好不要使用索引
如果表记录太少,那么时不需要创建索引的。表记录太少的话,有没有索引对查询效率的影响并不大。甚至说,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
3.避免对经常更新的字段创建索引
频繁更新的字段不一定要创建索引。因为数据更新的时候,也需要更新索引,如果索引太多,在更新索引的时候也会造成负担,从而影响效率。
避免对经常更新的表创建过多索引,并且索引中的列尽可能少。否则,虽然提高了查询速度,但却降低更新表的速度。
4.不建议用无序的值作为索引
例如:身份证、UUID、MD5、HASH、无序长字符串等。
5.很少使用或不使用的列无需建立索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。DBA 应当定期找出这些索引,将他们删除,从而减少索引对更新操作的影响。
6.已经有索引的列尽量避免定义冗余或重复索引
比如某些字段已经存在于联合索引中了,就不在需要单独创建索引。又或者某个字段已经创建了唯一索引,则无需在定义一个普通索引。
补充说明
- 在多个字段都要创建索引的情况下,联合索引优于单值索引。
- 创建联合索引时,使用最频繁的列需要放到联合索引的左侧。在进行查询时,也应该把使用最频繁的列放在最左侧。
- 索引是一般双刃剑,可以提高查询效率,但也会降低插入和更新的速度,并占用更多的磁盘空间。
- 在实际工作中,我们也需要注意平衡,索引的数目不是越多越好。我们需要限制每张表上的索引数量尽量不超过 6 个。原因:
-
- 每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。
- 索引会影响 insert、delete、update 等语句的性能,因为表中的数据更改的同时,索引也会进行调整和更新,会造成负担。
- 优化器在选择如何优化查询时,会根据统一信息对每一个可以用到的索引来进行评估,以生成一个最好的执行计划,如果同时有很多个索引可以用于查询,会增加 mysql 优化器生成执行计划时间,降低查询性能。