MySQL-索引设计原则

适合创建索引

1. 字段的数值有唯一性的限制

• 索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的
• 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引

2. 频繁作为 WHERE 查询条件的字段

3. 经常 GROUP BY 和 ORDER BY 的列

• 索引就是让数据按照某种顺序进行存储或检索
• 使用 GROUP BY 对数据进行分组查询，或者使用ORDER BY对数据进行排序的时候，就需要对分组或者排序的字段进行索引
• 如果待排序的列有多个，那么可以在这些列上建立组合索引

4. UPDATE、DELETE 的 WHERE 条件列

• 需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除
• 如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显

5. DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时

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

7. 使用数据类型小的列创建索引

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

8. 使用字符串前缀创建索引

• 字符串越长，在索引中占用的存储空间越大，做字符串比较时会占用更多的时间
• 通过截取字段的前面一部分内容建立索引，这个就叫前缀索引
• 能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题
• 计算不同的长度的选择性，越接近于1，区分度越好

select count(distinct address) / count(*) from shop;

• 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本
  区分度决定索引长度

9. 区分度高(散列性高)的列适合作为索引

• 列的基数指的是某一列中不重复数据的个数，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中，最好为基数大的列建立索引
• 联合索引把区分度高的列放在前面

10. 使用最频繁的列放到联合索引的左侧

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

限制索引的数目

• 索引的数目不是越多越好。建议单张表索引数量不超过6个
• 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
• 索引会影响 INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担
• 如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能

不适合创建索引

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

2. 数据量小的表最好不要使用索引

3. 有大量重复数据的列上不要建立索引

4. 避免对经常更新的表创建过多的索引

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

5. 不建议用无序的值作为索引

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引