【MySQL数据库开发设计规范】之索引设计规范

欢迎点开这篇文章，自我介绍一下哈，本人姑苏老陈 ，是一名JAVA开发老兵。

本文收录于 《MySQL数据库开发设计规范》专栏中，该专栏主要分享一些关于MySQL数据库开发设计相关的技术规范文章，定期更新，欢迎关注，谢谢 :-）

一、前言

在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。

本文介绍MySQL数据库开发设计规范之索引设计规范。

PS：以下规范在大型互联网公司经过了充分的验证，尤其适用于并发量大、数据量大的业务场景。

二、索引设计规范

1.【推荐】单表索引建议控制在5个以内；
说明：索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率，所以不是越多越好；

2.【强制】禁止在更新十分频繁，区分度不高的属性上建立索引；
说明：比如订单状态字段，不适合建立索引；

3.【强制】建立组合索引必须把区分度高的字段放在前面；
说明：比如，添加组合索引；

ALTER TABLE `payment` ADD INDEX idx_customer_id_staff_id(`customer_id`, `staff_id`)，

其中customer_id的区分度更高，所以放在前面；

4.【推荐】对字符串使用索引，如果字符串定义长度超过128的，可以考虑前缀索引。

说明，例如，普通的字符串索引：alter table user add index idx_user_email(email);  前缀字符串索引：alter table user add index idx_user_email(email(6))，建议使用前缀索引；

5.【强制】表必须有主键，并且是auto_increment及not null的，根据表的实际情况定义无符号的tinyint,smallint,int,bigint；

说明：比如，order_id类型定义为int(11) unsigned；

6.【强制】禁止更新频繁的列作为主键；

7.【强制】禁止字符串列作为主键；

8.【强制】禁止UUID、MD5、HASH这些作为主键，这样数值太离散了；

9.【推荐】默认使用非空的唯一键作为主键；

10.【推荐】主键建议设置为自增长；

11.【推荐】核心SQL优先考虑覆盖索引；

12.【强制】不在索引列进行数学运算和函数运算；

13.【推荐】研发要经常使用explain，如果发现索引选择性差，必须要学会使用hint。
说明：type值至少要达到 range 级别；其中，type即找到所需数据使用的扫描方式，效率从好到坏依次为：System(系统表)>const(常量连接)>eq_ref(唯一性索引扫描)>ref(非唯一索引扫描)>range(范围扫描)>index(索引树扫描）>all(全表扫描)；

14.【推荐】能使用唯一索引就要使用唯一索引，提高查询效率

15.【推荐】多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量；

16.【强制】索引字段要保证不为NULL，考虑default value进去。NULL也是占空间，而且NULL非常影响索引的查询效率；

17.【强制】新建的唯一索引不能和主键重复；

18.【推荐】尽量不使用外键、外键用来保护参照完整性，可在业务端实现；
说明：避免对父表和子表的操作会相互影响，降低可用性，影响数据库性能；

19.【强制】表必须有无符号int型自增主键，对应表中id字段；
说明：使用自增主键，当数据写入时，可以提高插入性能，避免page分裂，减少表碎片；

20.【推荐】WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引；

21.【推荐】索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面；
说明：换言之，记录字段值重复多的放在后面；

22.【推荐】ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面；

23.【参考】合理创建联合索引（避免冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)；

24.【推荐】复合索引中的字段数建议不超过5个；

25.【强制】不在选择性低的列上建立索引，例如"性别"、“状态”、“类型”；

26.【推荐】对于单独条件如果走不了索引，可以使用force-index强制指定索引；
说明：例如，假设我们有一个名为users的表，它有一个名为lastname的列，并且我们对这个列建立了一个索引。如果我们想强制查询使用这个索引。

SELECT * FROM users FORCE INDEX (idx_user_lastname) WHERE lastname = 'Smith';

27.【强制】禁止给表中的每一列都建立单独的索引;

28.【推荐】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可；
说明：比如给email字段指定索引长度。

alter table user add index idx_user_email(email(6))；

三、总结

以上介绍了在进行MySQL数据库表索引设计时，可以遵循的一些技术规范。希望对大家有帮助，谢谢 :-）

附本文参考资料：

• 阿里云官方知乎：https://www.zhihu.com/org/a-li-yun-97-77
• 阿里云开发者官方社区：https://developer.aliyun.com/
• 阿里开发者官方CSDN社区：https://blog.csdn.net/alitech2017?type=blog
• 阿里云云栖号CSDN：https://blog.csdn.net/yunqiinsight/category_10231626.html
• 阿里巴巴技术团队发布的《JAVA开发手册》泰山版
• 阿里云开发者官方微信公众号

如果您对文章中内容有疑问，欢迎在评论区进行留言，我会尽量抽时间给您回复。如果文章对您有帮助，欢迎点赞、收藏。您的点赞，是对我最大的支持和鼓励，谢谢 :-）