20个MySQL索引设计原则总结

大家好，我是程序员阿江。

开门见山，MySQL索引是一把双刃剑，使用好了，不仅可以提高查询效率，还可以升职加薪；用的不好，反而会对系统产生副作用，免不了领导的一顿骂。

下面是我整理的20个索引设计原则以及索引的优缺点，希望对大家有帮助

索引的优缺点

优点

• 提高数据的检索速度
• 减少磁盘IO次数
• 唯一索引可以保证数据的唯一性

缺点

• 占用磁盘空间
• 由于每次写操作(插入、更新、删除)都需要更新索引，从而会降低写操作速度

20个索引设计原则

1. 数据量大且频繁查询的表适合使用索引
2. 具有唯一特性的字段适合建立索引

引用自阿里规范：
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：虽然唯一索引影响了insert速度，但是这个速度损耗可以忽略，但提高查找速度是明显的。

3. 频繁作为where条件的字段

不论是select,还是update或者delete, 经常作为where条件的字段建议创建普通索引，尤其数据量大的时候，效果还是很明显的。

4. 经常group by和order by 的字段

补充：如果需要同时为group by和order by的字段创建索引，建议使用联合索引，并且group by的字段在左效率更高，因为SQL先执行group by。

5. 需要distinct的字段

因为索引会对数据按照某种顺序进行排序，索引去重的时候会快很多。

6. 多表join时的关联字段

多表连接查询时，对连接的字段创建索引，并且该字段在关联表的数据类型必须是一致的。

7. 使用类型小的字段创建索引

例如优先使用int而不是bigint字段

8. 字符串长度较长的字段建议使用前缀索引

如果字符串很长，需要使用前缀索引，也就是截取前面部分字符串来创建索引，而不是使用完整的字符串来建立索引。

9. 区分度高的字段适合做索引
10. 使用频繁的字段放在联合索引的左侧

根据最左前缀原则，在联合索引中，越频繁使用的字段应该放在联合索引的左侧。

11. 联合索引优先于单列索引

要对多个字段建立索引时，优先考虑使用联合索引。

12. 限制索引的数量

创建索引并不是越多越好，建议单表索引数量不超过6个。
因为： 索引会占用磁盘空间；索引会影响写操作的速度；MySQL优化器会对每一个可以用到的索引进行评估，生成一个最佳的执行计划，如果同一个查询可用的索引太多会增加优化器的负担，从而降低效率。

13. 尽量使用not null约束

方便优化器更好的确定哪个索引最有效

14. where条件中使用不到的字段不需要创建索引
15. 数据量小的表不需要创建索引，比如不到1000行
16. 有大量重复数据（区分度低）的列不建议创建索引

例如deleted、gender、status字段

17. 查询少更新多的表不要建立太多索引
18. 避免使用无序的列作为索引

例如uuid ,md5

19. 避免定义冗余或重复的索引
20. 删除不再使用或很少使用的索引

我的总结

索引是一把双刃剑，有利有弊，而索引的设计原则本质上就是力求找到某种平衡，使得利大于弊。