因为岗位原因，负责制定后端组数据库的规范。作为所有产品线的规范，经过多次修改，最终形成如下文字。从规范落地到整个后端，需要半年多的时间。对整个团队在开发阶段减少不合适的建表语句、错误的SQL、错误的索引具有积极意义，分享给大家参考。下面分为三个部分：建表规范、SQL规范、索引规范。每个部分中的每个项目都有两个级别：强制性和推荐性。大家参考的时候要根据自己公司的情况来衡量。建表协议【强制】：①存储引擎必须使用InnoDB解释：InnoDB支持事务，行级锁，并发性能更好。CPU和内存缓存页面优化使资源利用率更高。【强制】：②必须为每张表设置一个主键ID，这个主键ID使用自增主键（满足需要尽量短），除非在分库和分库中解释-表环境：由于InnoDB组织数据的方式，另外还需要有一个主键，如果主键ID单调递增，可以有效提高插入性能，避免分页过多，减少表碎片和提高空间利用率。在分库分表环境下，需要统一分配每张表的主键值，避免整个逻辑表的主键重复。【强制】：③必须使用utf8mb4字符集解读：MySQL中的UTF-8不是“真正的UTF-8”，“utf8mb4”才是真正的“UTF-8”。【强制】：④数据库表，表字段必须加上with中文注释.解读：大家不要偷懒[强制]：⑤库名、表名、字段名全部小写，带下划线，不超过32个字符，名字一定要清楚，拼音和英文解释：约定【强制】：⑥单表列数必须小于30，超过则考虑分表。⑦禁止使用外键。如果有外键完整性约束，需要应用程序控制。解读：外键会造成表之间的耦合，UPDATE和DELETE操作会涉及到关联表，会极大地影响SQL的性能，甚至导致死锁。【强制】：⑧字段必须定义为NOTNULL并提供默认值解释：NULL列使索引/索引统计/值比较更复杂，更难针对MySQL进行优化。这类NULL需要MySQL内部进行特殊处理，增加了数据库处理记录的复杂度；同样条件下，当表中有很多空字段时，数据库的处理性能会大大降低。NULL值需要更多的存储空间，无论是在表中还是在索引中，每一行中带有NULL的列都需要额外的空间来标识它。【强制】：⑨禁用保留字，如DESC、RANGE、MARCH等。解释：请参考MySQL官方保留字。【强制】：⑩如果存储的字符串长度几乎相等，使用CHAR定长字符串类型解释：可以减少空间碎片，节省存储空间。【建议】：?在某些场景下，考虑使用TIMESTAMP代替DATETIME解释：两种类型都可以用“yyyy-MM-ddHH:mm:ss”的格式来表达时间，TIMESTAMP只需要占用4个字节的长度，可存储范围为（1970-2038）年，每个时区显示时间不同。DATETIME类型占用8个字节，对时区不敏感。它可以存储（1001-9999）年。[建议]：?谨防自动生成的模式，建议所有模式都手动编写和解释：不要过于信任某些数据库客户端。SQL协议【建议】：①为了充分利用缓存，不允许自定义函数、存储函数、用户变量。解释：如果查询包含任何用户自定义函数、存储函数、用户变量、临时表和系统表，则不会缓存其查询结果。例如函数NOW()或CURRENT_DATE()会因为查询时间不同而返回不同的查询结果。【强制】：②在查询中指定需要的列，而不是直接使用“*”返回所有列解释：读取不需要的列会增加CPU、IO、NET的消耗。无法有效使用覆盖索引。【强制】：③属性的隐式转换是不允许的解读：假设我们给手机号码列加一个索引，然后执行下面的SQL，会发生什么情况？解释SELECTuser_nameFROMparentWHEREphone=13812345678;很明显，索引没有生效，会扫描全表。[建议]：④在WHERE条件的属性上使用函数或表达式。解释：MySQL不能自动解析这样的表达式，不能使用索引。【强制】：⑤禁止使用外键和级联，所有外键概念必须在应用层解决阻塞，存在数据库更新风暴风险；外键影响数据库的插入速度。[建议]：⑥尽量避免在WHERE子句中使用or作为连接条件。解读：根据情况，可以选择使用UNIONALL代替OR。【强制】：⑦不允许以%开头的模糊查询解释：根据索引最左前缀原则，以%开头的模糊查询不能使用索引，可以使用ES进行检索。索引协议【建议】：①避免在更新频繁、区分度低的列上单独创建索引维护成本较高。【强制】：②JOIN表不能超过5个。需要JOIN的字段的数据类型必须绝对一致；多表关联查询时，保证关联字段需要有索引解释：JOIN过多的表会使MySQL优化器更难权衡“最佳”执行计划（可能是表数的阶乘）表），同时注意关联字段的类型、长度、字符编码等是否一致。【强制】：③在联合索引中，如果第一列的索引区分度等于1，则不需要建立联合索引解释：索引完全可以通过第一列定位到数据，所以后者联合索引的一部分是不需要的。【强制】：④创建联合索引时，必须将区分度较高的字段放在左边解释：将区分度较高的列放在左边，可以有效地从一开始就过滤掉无用的数据。为了提高索引的效率，当Mapper写的SQL的WHERE条件中有多个条件时，我们需要检查当前表是否有现成的联合索引直接使用。注意每个条件的顺序要和索引的顺序一致。【建议】：⑤使用覆盖索引进行查询操作，避免回读表：覆盖查询是指查询只需要使用索引获取需要的DATA，不需要回表查询再次，所以效率比较高。我们正在使用EXPLAIN的结果，将出现额外的列：“usingindex”。这里还要强调的是不要使用“SELECT*”，否则覆盖索引几乎不可能使用。[建议]：⑥在长VARCHAR字段上建立索引时，如VARCHAR(100)，应指定索引长度。没有必要对整个字段建立索引。索引长度可以根据实际的文本判别来确定：索引长度和判别度是一对矛盾体。一般对于字符串类型的数据，如果索引长度为20，区分度会高达90%，所以可以考虑创建长度为20的索引，而不是全字段索引。例如，您可以使用SELECTCOUNT(DISTINCTLEFT(lesson_code,20))/COUNT(*)FROMlesson;判断lesson_code字段长度为20个字符时的文本判别。【建议】：⑦如果有ORDERBY场景，请注意索引的有序性。ORDERBY的最后一个字段是联合索引的一部分，放在索引组合顺序的末尾，避免file_sort影响查询性能。解读：假设有一个查询条件WHEREa=?和b=?按c订购；有一个索引：a_b_c，那么此时就可以使用索引进行排序了。反例：如果查询条件中包含范围查询，则不能使用索引顺序，如：WHEREa>10ORDERBYb;无法对索引a_b进行排序。[建议]：⑧Where索引的列不能是表达式的一部分，也不能是函数的参数。参数，MySQL无法单独解析该列，索引不会生效。[建议]：⑨我们在Where条件中使用范围查询时，索引最多用于一个范围条件，如果有多个范围条件，则不使用索引。解释：MySQL可以在多个范围条件下使用最左边第一个范围查询，但不能使用后续范围查询。【建议】：⑩多表外联时，表之间的关联字段类型必须完全一致。解释：当两个表连接时，如果字段类型不完全一致，索引将不会生效。这里的完全一致性包括但不限于字段类型、字段长度、字符集、Collection等。
