超全的数据库建表、SQL、索引规范

【强制】

1. 存储引擎必须使用InnoDB

   解读：InnoDB支持事务、行级锁、并发性能更好，CPU及内存缓存页优化使得资源利用率更高。

2. 每张表必须设置一个主键ID，且这个主键ID使用自增主键（在满足需要的情况下尽量短），除非在分库分表环境下

   解读：InnoDB组织数据的方式决定了需要有一个主键，单调递增的主键ID可以有效提高插入性能，避免过多的页分裂、减少表碎片提高空间使用率。在分库分表环境下，则需要统一分配主键值，避免主键重复。

3. 必须使用utf8mb4字符集

   解读：在MySQL中的UTF-8并非“真正的UTF-8”，而utf8mb4才是真正的“UTF-8”。

4. 数据库表、表字段必须加入中文注释

   解读：提高代码可读性，方便团队协作。

5. 库名、表名、字段名均小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用

   解读：统一的命名规范有助于团队协作和代码维护。

6. 单表列数目必须小于30，若超过则应该考虑将表拆分

   解读：单表列数太多会增加MySQL服务器处理InnoDB返回数据之间的映射成本。

7. 禁止使用外键，如果有外键完整性约束，需要应用程序控制

   解读：外键会导致表与表之间耦合，影响SQL性能，甚至造成死锁。

8. 必须把字段定义为NOT NULL并且提供默认值

   解读：NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化；NULL类型需要额外的存储空间和处理复杂性。

9. 禁用保留字，如DESC、RANGE、MARCH等，请参考MySQL官方保留字

   解读：避免使用保留字导致SQL语句错误。

10. 如果存储的字符串长度几乎相等，使用CHAR定长字符串类型

    解读：能够减少空间碎片，节省存储空间。

【建议】

1. 在一些场景下，考虑使用TIMESTAMP代替DATETIME

   解读：TIMESTAMP占用空间小，对时区敏感，适用于需要跨时区处理的时间数据；DATETIME占用空间大，对时区不敏感，适用于不需要跨时区处理的时间数据。

2. 当心自动生成的Schema，建议所有的Schema手动编写

   解读：避免对数据库客户端的过度依赖，确保Schema的准确性和可控性。

【建议】

1. 为了充分利用缓存，不允许使用自定义函数、存储函数、用户变量

   解读：包含这些元素的查询结果不会被缓存，影响查询性能。

【强制】

1. *在查询中指定所需的列，而不是直接使用“ ”返回所有的列

   解读：减少不必要的CPU、IO、NET消耗，有效利用覆盖索引。

2. 不允许使用属性隐式转换

   解读：隐式转换会导致索引失效，影响查询性能。

3. 在WHERE条件的属性上使用函数或者表达式（不推荐，但列出以强调避免）

   解读：MySQL无法自动解析这种表达式，无法使用到索引。

4. 禁止使用外键与级联，一切外键概念必须在应用层解决

   解读：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

【建议】

1. 应尽量避免在WHERE子句中使用or作为连接条件

   解读：根据情况可以选择使用UNION ALL来代替OR，提高查询性能。

2. 不允许使用%开头的模糊查询

   解读：%开头的模糊查询无法使用索引，可以使用ES等搜索引擎来做检索。

【建议】

1. 避免在更新比较频繁、区分度不高的列上单独建立索引

   解读：区分度不高的列单独创建索引的优化效果很小，但是较为频繁的更新则会让索引的维护成本更高。

【强制】

1. JOIN的表不允许超过五个。需要JOIN的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引

   解读：太多表的JOIN会让MySQL的优化器更难权衡出一个“最佳”的执行计划；同时要注意关联字段的类型、长度、字符编码等是否一致。

2. 在一个联合索引中，若第一列索引区分度等于1，那么则不需要建立联合索引

   解读：索引通过第一列就能够完全定位的数据，所以联合索引的后边部分是不需要的。

3. 建立联合索引时，必须将区分度更高的字段放在左边

   解读：区分度更高的列放在左边，能够在一开始就有效的过滤掉无用数据，提高索引的效率。

【建议】

1. 利用覆盖索引来进行查询操作，避免回表

   解读：覆盖查询即是查询只需要通过索引即可拿到所需DATA，而不再需要再次回表查询，所以效率相对很高。

2. 在较长VARCHAR字段，例如VARCHAR(100)上建立索引时，应指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可

   解读：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，若长度为20的索引区分度会高达90%以上，则可以考虑创建长度为20的索引。

3. 如果有ORDER BY的场景，请注意利用索引的有序性。ORDER BY最后的字段是联合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能

   解读：合理利用索引的有序性可以提高ORDER BY的查询性能。

4. 在where中索引的列不能是某个表达式的一部分，也不能是函数的参数

   解读：索引列成为表达式的一部分或函数的参数时，MySQL无法将此列单独解析出来，索引也不会生效。

5. 在where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则后边的不走索引

   解读：MySQL能够使用多个范围条件里边的最左边的第一个范围查询，但是后边的范围查询则无法使用索引。

6. 在多个表进行外连接时，表之间的关联字段类型必须完全一致

   解读：当两个表进行Join时，字段类型若没有完全一致，则加索引也不会生效。

以上规范涵盖了数据库建表、SQL编写和索引设计的各个方面，旨在提高数据库的性能和可维护性。在实际应用中，应根据具体情况灵活调整这些规范。