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