MySQL-索引相关设计原则

1、适合创建索引的情况

1.1、字段数值具有唯一性的限制

• 索引本身是具有约束作用的，比如创建唯一索引、主键索引
• 在数据表中如果某个字段是唯一性的，可以创建唯一性索引或主键索引

在业务场景中具有唯一特性的字段，即使是组合字段也必须建成唯一索引
唯一索引对insert语句的损耗可以忽略不计，但提高查找速度是明显的

1.2、频繁作为where查询条件的字段

• 某个字段在执行select语句的where条件中经常被使用到，那需要对该字段创建索引
• 数据量大的情况下，创建普通索引可以大幅度提高查询效率

1.3、经常使用group by 和 order by 的列

• 索引就是让数据按照某种顺序进行存储或检索，因此当使用 group by 对数据进行分组查询 或 使用 order by 对数据进行排序时，则需要 对分组或者排序的字段进行索引
• 如果排序或分组的列有多个，可以在这些列上建立组合索引

1.4、update 、 delete 的 where条件列

• 对于数据按照某个条件查询后在进行 update 或 delete 的操作，如果对于where 字段创建了索引，能大幅度提高检索效率
• 如果进行更新时，更新的字段为非索引字段，提升的效率更加明显，因为非索引字段更新不需要对索引进行维护。

1.5、distinct 字段创建索引

1.6、多表 join 连接操作时，创建索引的注意事项

• 连接表的数量尽量不要超过三张，每增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快，严重影响查询效率。
• 对where 条件创建索引
• 对于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

1.7、使用列的类型小的列创建索引

• 此处列的类型大小指 该类型表示的数据范围的大小
• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间越小，在同一个数据页中存放的记录越多，从而减少磁盘I/O带来的性能损耗

1.8、使用字符串前缀创建索引

1.9、区分度高（散列性高）的列适合作为索引

1.10、使用最频繁的列放到联合索引的左侧

1.11、在多个字段都要创建索引的情况下，联合索引优于单值索引

2、限制索引的数目

• 单张索引数量不超过6个：
  • 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大
  • 所以会影响增删改语句的性能，表中数据更改的同时，索引也会进行调整和更新
  • 优化器在选择如何查询时会根据同一信息，对每个可以用到的索引进行评估，以生成一个最好的止执行计划，如果同时有多个索引都可用于查询，会增加MySQL优化器生成计划的时间，降低查询性能。

3、不适合创建索引的情况

3.1、在where中使用不到的字段，不要设置索引

• 索引的价值是快速定位，不起到该作用的字段无需设置索引

3.2、数据量小的表最好不使用索引

• 表中数据量级过小是否创建索引对查询效率的影响并不大。

3.3、有大量重复数据的列上不要创建索引

• 该情况下创建索引可能会严重降低数据的更新速度

3.4、避免对经常更新的表创建过多的索引

• 频繁更新的字段不一定要创建索引，因为在更新数据时还需要更新索引，如果索引过多，在更新索引的造成负担进而影响效率
• 避免对经常更新的表创建过多的索引，并且索引中的列尽可能的少。虽然提高了查询速度，同时会降低表的更新速度。

3.5、不推荐以无序的值作为索引

3.6、删除不在使用或者很少使用的索引

• 减少索引对更新操作的影响

3.7、不要定义冗余或重复的索引