因为岗位原因，负责制定后端组数据库的规范规范。作为所有产品线的规范，经过多次修改，最终形成了如下文字，该规范已经在整个后端实施了半年多。在开发阶段减少不合适的建表语句、错误的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，并提供默认值解释：a．NULL列使索引/索引统计/值比较更加复杂，MySQL更难优化b。NULL是Msql的一种，内部需要特殊处理，增加数据库处理记录的复杂度；同样条件下，当表中有很多空字段时，数据库的处理性能会降低很多c．NULL值需要更多的存储空间，无论是表还是索引中每一行的NULL列都需要额外的空间来标识【强制】（9）禁用保留字，如DESC、RANGE、MARCH等，请参考官方的Mysql保留字。【强制】(10)如果存储的字符串长度几乎相等，则使用CHAR定长字符串类型。解读：可以减少空间碎片，节省存储空间。更多SQL技巧，可以搜索公众号：SQL数据库开发【建议】（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）在查询中指定需要的列，而不是直接使用“*”返回所有列解释：a）读取不必要的列会增加CPU、IO、NET消耗b）不能有效使用覆盖索引【强制](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)，需要指定索引长度。不需要对整个字段都建立索引，索引长度可以根据实际的文本判别来确定。解读：指标的长度和区分度是一对矛盾体。一般对于string类型的数据，如果长度为20的索引判别度在90%以上，可以考虑创建长度为20的索引，而不是全量索引。字段索引。例如，您可以使用SELECTCOUNT(DISTINCTLEFT(lesson_code,20))/COUNT(*)FROMlesson;判断lesson_code字段长度为20个字符时的文本判别。【建议】（7）如果有ORDERBY场景，请注意索引的有序性。ORDERBY的最后一个字段是联合索引的一部分，放在索引组合顺序的末尾，避免file_sort影响查询性能。解读：假设有一个查询条件WHEREa=?和b=?按c订购；有一个索引：a_b_c，那么此时就可以使用索引进行排序了。反例：如果查询条件包含范围查询，则不能使用索引顺序，如：WHEREa>10ORDERBYb；无法对索引a_b进行排序。[建议](8)where索引的列不能是表达式的一部分，也不能是函数的参数。解释：即为某列添加了索引，但是如果该列成为表达式的一部分或者函数的参数，Mysql无法单独解析该列，索引将不会生效。[建议](9)我们在where条件中使用范围查询时，索引最多用于一个范围条件，如果有多个范围条件，后面的索引将不会被使用。解读：Mysql可以在多个范围条件下使用最左边的第一个范围查询，但是不能使用后面的范围查询。[建议](10)对多张表进行外连接时，表之间的关联字段类型必须完全一致。解释：当两个表连接时，如果字段类型不完全一致，则索引不会生效。这里的完全一致性包括但不限于字段类型、字段长度、字符集、集合等。
