MySQL开发规范


MySQL开发规范，每个后端开发人员都应该理解。

正文

基本规范

  • 使用INNODB存储引擎。
  • 字符集统一使用utf8mb4，使用默认的校对规则。
  • 表必须有注释。
  • 不在数据库库存储图片及文件大文件。如有需要存储相关路径。
  • 不使用存储过程、自定义函数、触发器、视图、事件。不利于调试和跨平台迁移。
  • 不写在严格模式通不过的语句,如：select id,name,count(*) from tb group by id。 name没有参与分组也没通过聚合。 (原因：1、可读性差； 2、不利于升级或者迁移到其它平台MySQL，在严格模式下类似语句会报错)
  • 不存储明文密码。
  • 不在线上数据库做压力测试。
  • 开发及测试环境不直接连接线上数据库。

对象命名规范

  • 所有对象遵循“长名”（除非常通用的简写外，尽量避免使用简写）和“表意”（通过名称大致知道该对象所表达的内容），命名中的多个单词通过下划线连接。所有对象命名使用小写。
  • 禁止使用MySQL关键字命名。如from、 select 等。
  • 临时库、临时表使用tmp作为前缀，使用_日期作为后缀,如果因为日期后缀导致名称太长，可以去掉日期后缀，在注释里说明。
  • 对象名总长度不超过32个字符。

表、字段规范

  • 拆分大字段和访问频率低的字段。不把热点数据和冷数据放在一张表里。如text字段和比较长的、不常使用的varchar字段应垂直拆分出来。
  • 分表需符合YYYY[MM][DD][HH]格式，如admin_2019022305 如果对分表自增列有要求的，且提前建立的表，必须在建表以后插入一条数据。否则空表在MySQL服务重启后，AUTO_INCREMENT会被重置为1，切记。
  • 无特殊情况不使用TEXT、BLOB数据类型。
  • 带小数位的数据存储使用DECIMAL类型，不使用FLOAT类型。
  • 所有字段使用NOT NULL约束且已有表增加新字段要有默认值,TEXT类型不需要默认值。
  • 不使用主外键约束。参照完整性通过程序实现。
  • 使用varbinary存储大小写敏感的变长字符串。
  • 父子表的类型、长度、字符集必须保持一致。
  • 所有字段必须有注释。
  • 字段类型采用够用原则。如tinyint能满足就不使用int类型。varchar(10)能满足则不设计varchar(50)。
  • 存储ip最好用int存储而非char(15)。
  • 数值类型能满足则不使用varchar等其它类型。
  • 日期存储使用datetime,date,timestamp,int类型。不得使用字符型。timestamp范围比datetime小。
  • 不使用enum类型，用tinyint代替。
  • 对需要对基础表经常关联查询的。可对不更新的数值或长度较小的字符字段，在设计表的时候可以做适当的冗余，如id,userid。避免在查询的时候做太多的关联。
  • 表的字段总数不超过50个。
  • 新建表必须包含create_time和update_time 字段,update_time字段的值由数据库维护(DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’)

索引规范

  • 表必须有主键。
  • 不使用更新频繁的列作为主键。
  • 尽量不选择字符串列作为主键。
  • 不使用UUID MD5 HASH这些作为主键。
  • 默认使非空的唯一键作为主键。
  • 建议选择自增或顺序增长的数值列作为主键。
  • 避免重复索引，比如索引1(userid,username),索引2(userid)，索引2即为重复索引。
  • 不在低基数列上建立索引，如性别。
  • 不使用外键约束。参照完整性可通过程序去实现。
  • 非唯一索引必须以 idx_字段1_字段2命名，唯一所以必须以uniq_字段1_字段2命名。
  • 唯一索引必须不能和主键重复。
  • 能使用唯一索引则使用唯一索引，而不使用普通索引，提高查询效率。
  • 字符长度超过50(如:varchar(50以上)的不适合建立索引。
  • 拒绝自行使用强制索引（force index）,如果一定要使用请DBA确认。

SQL规范

  • SQL语句尽可能简单。线上JOIN表的数量不超过3个。超过3个表的SQL需要分解成小的SQL，通过程序实现大的SQL业务逻辑。嵌套不超过2层。 BI分析系统除外。BI分析系统关联表的总数量不超过5个。原则上BI分析系统不允许查询线上数据库（包括从库）。可通过ETL等同步工具同步到数据仓库，分析统计统计业务在数据仓库进行。
  • 事务尽可能简单。及时提交事务或回滚事务。
  • 降低业务的耦合度，方便以后的数据库切分。
  • 避免在数据库中进行复杂的数学和逻辑运算。MySQL为存储层，应实现业务逻辑和存储分离。
  • 避免select * 查询，使用什么字段就select 什么字段。降低IO和有可能的使用到覆盖索引。
  • Select 中的or 改为 In ，如：id=1 or id=2 or id=3改为 id in(1,2,3),IN的数量控制在1000以内。
  • 注意limit分页。普通的limit的分页，limit越大分页越慢。改写limit分页，如：select id from t order by id limit 10000,10 –> select id from t where id > 10000 order by id limit 10
  • union all 能满足业务则不使用union。
  • 批量数据更新需打散后分批更新，一次不要更新太多数据。
  • 减少与数据库的交互次数。
  • SQL语句不可以出现隐式转换，如： select id from 表 where id='1'，数值型不需要引号，字符型、日期型等必须使用引号,否则会出现字符向数值的隐式转换，导致索引失效。
  • where 部分字段不应出现函数计算。如 where id*1=5 where date_format(xx) between '' and ''
  • 应避免not in、 not like等负向查询。
  • like查询应避免 ‘%xx’, ’%xx%’ 。如果这类查询比较多，可考虑第三方软件实现。
  • 避免order by rand()查询，如程序能完成排序则不使用数据库排序。
  • 避免同一条语句更新、删除多张表数据。
  • Inner join 还是 Left join,业务需要才是第一位。Left join 用在需要返回两个共有记录的同时，还需要返回左表不在右表中的记录, Inner join 用在返回两个表共有的记录。不要刻意通过Left join 去实现Inner join的效果,如果通过Left join实现Inner join 的效果， 需增加更多的条件实现如：where b.xx is not null and c.xx is not null，这样会增加代码的复杂度和可读性和误导优化器的判断。
  • Delete 、update语句必须有where条件。
  • 不使用where 1=1这种没有意义的条件，存在注入风险。
  • 如果排序不是必须的，则不使用order by 排序，增加排序的成本。
  • 对一个表多次的alter 操作，必须合并为一个语句。

流程规范

  • SQL审核包括建表语句，update 语句，delete语句，查询语句。
  • 审核需提前至少2天，大的项目需提前至少3天通过邮件提供审核脚本。
  • 不允许没有经过审核的SQL私自上线。
  • 不在业务高峰时执行批量操作和大表查询。

数据库建表/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）禁止使用外键，如果有外键完整性约束，需要应用程序控制

解读：外键会导致表与表之间耦合，UPDATE与DELETE操作都会涉及相关联的表，十分影响SQL的性能，甚至会造成死锁。

【强制】（8）必须把字段定义为NOT NULL并且提供默认值

解读：

NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化；

NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多；

NULL值需要更多的存储空间，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识。

【强制】（9）禁用保留字，如DESC、RANGE、MARCH等，请参考Mysql官方保留字

【强制】（10）如果存储的字符串长度几乎相等，使用CHAR定长字符串类型。

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

【建议】（11）在一些场景下，考虑使用TIMESTAMP代替DATETIME

解读：

这两种类型的都能表达”yyyy-MM-dd HH:mm:ss”格式的时间，TIMESTAMP只需要占用4个字节的长度， 可以存储的范围为(1970-2038)年，在各个时区，所展示的时间是不一样的；

而DATETIME类型占用8个字节，对时区不敏感，可以存储的范围为(1001-9999)年。

【建议】（12）当心自动生成的Schema，建议所有的Schema手动编写

解读：对于一些数据库客户端不要太过信任。

可以再过一下 关系型数据库表结构设计思考

SQL规范

【建议】 (1) 为了充分利用缓存，不允许使用自定义函数、存储函数、用户变量

解读：如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果都不会被缓存。 比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果。

【强制】（2）在查询中指定所需的列，而不是直接使用“ *”返回所有的列

解读：

读取不需要的列会增加CPU、IO、NET消耗；

不能有效的利用覆盖索引。

【强制】（3）不允许使用属性隐式转换

解读：假设我们在手机号列上添加了索引，然后执行下面的SQL会发生什么？ explain SELECT user_name FROM parent WHERE phone=13812345678; 很明显就是索引不生效，会全表扫描。

【建议】（4）在WHERE条件的属性上使用函数或者表达式

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

【强制】（5）禁止使用外键与级联，一切外键概念必须在应用层解决

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

【建议】（6）应尽量避免在WHERE子句中使用or作为连接条件

解读：根据情况可以选择使用UNION ALL来代替OR。

【强制】（7）不允许使用%开头的模糊查询

解读：根据索引的最左前缀原理，%开头的模糊查询无法使用索引，可以使用ES来做检索。

索引规范

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

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

【强制】（2） JOIN的表不允许超过五个。需要JOIN的字段，数据类型必须绝对一致; 多表关联查询时，保证被关联的字段需要有索引

解读：太多表的JOIN会让Mysql的优化器更难权衡出一个“最佳”的执行计划（可能性为表数量的阶乘），同时要注意关联字段的类型、长度、字符编码等等是否一致。

【强制】（3）在一个联合索引中，若第一列索引区分度等于1，那么则不需要建立联合索引

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

【强制】（4）建立联合索引时，必须将区分度更高的字段放在左边

解读：区分度更高的列放在左边，能够在一开始就有效的过滤掉无用数据。提高索引的效率，相应我们在Mapper中编写SQL的WHERE条件中有多个条件时， 需要先看看当前表是否有现成的联合索引直接使用，注意各个条件的顺序尽量和索引的顺序一致。

【建议】（5）利用覆盖索引来进行查询操作，避免回表

解读：覆盖查询即是查询只需要通过索引即可拿到所需DATA，而不再需要再次回表查询，所以效率相对很高。 我们在使用EXPLAIN的结果，extra列会出现：”using index”。这里也要强调一下不要使用“SELECT * ”，否则几乎不可能使用到覆盖索引。

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

解读：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，若长度为20的索引，区分度会高达90%以上，则可以考虑创建长度例为20的索引，而非全字段索引。 例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;来确定lesson_code字段字符长度为20时文本区分度。

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

解读：

假设有查询条件为WHERE a=? and b=? ORDER BY c；存在索引：a_b_c，则此时可以利用索引排序；

反例：在查询条件中包含了范围查询，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引a_b无法排序。

【建议】（8）在where中索引的列不能是某个表达式的一部分，也不能是函数的参数

解读：即是某列上已经添加了索引，但是若此列成为表达式的一部分、或者是函数的参数，Mysql无法将此列单独解析出来，索引也不会生效。

【建议】 （9）我们在where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则后边的不走索引

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

【建议】 （10）在多个表进行外连接时，表之间的关联字段类型必须完全一致

解读：当两个表进行Join时，字段类型若没有完全一致，则加索引也不会生效，这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等。






参考资料

技术大神

超全的数据库建表/SQL/索引规范，适合贴在工位上！ https://mp.weixin.qq.com/s?__biz=MzIwNjQ5MDk3NA==&mid=2247501404&idx=1&sn=d4f6aff13231b719aa27538898e6459a

《High.Performance.MySQL.3rd.Edition》

《阿里巴巴java开发手册》

返回