Mysql 索引设计原则

适合添加索引的情况

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.已经有索引的列尽量避免定义冗余或重复索引

比如某些字段已经存在于联合索引中了，就不在需要单独创建索引。又或者某个字段已经创建了唯一索引，则无需在定义一个普通索引。

补充说明

1. 在多个字段都要创建索引的情况下，联合索引优于单值索引。
2. 创建联合索引时，使用最频繁的列需要放到联合索引的左侧。在进行查询时，也应该把使用最频繁的列放在最左侧。
3. 索引是一般双刃剑，可以提高查询效率，但也会降低插入和更新的速度，并占用更多的磁盘空间。
4. 在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量尽量不超过 6 个。原因：

• • 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  • 索引会影响 insert、delete、update 等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担。
  • 优化器在选择如何优化查询时，会根据统一信息对每一个可以用到的索引来进行评估，以生成一个最好的执行计划，如果同时有很多个索引可以用于查询，会增加 mysql 优化器生成执行计划时间，降低查询性能。