MySQL 社区规范 | 数据库篇

前言 | 笔记归档

这周公司开发工作比较悠闲，工作几乎压在设计上游，于是整理了下公司开发的文档，包括项目架构、服务器运维、规范、api对接、基本依赖信息等。如下是包含其中的MySQL开发规范，根据社区很多的博文参考以及结合自身小团队开发情况总结。

---

命名规范

• 对象名称必须使用小写，多单词统一使用下划线分割

• 命名的单词必须做到顾名思义、简洁，表名长度不要超过16个字符，字段名称长度不要超过32个字符

• 禁止使用保留字并且尽量少用含有关键词来命名

• 临时表必须以tmp_开头、以日期结尾，备份表必须以bak_开头、以日期结尾

基础规范

• 尽可能地使用InnoDB作为表的存储引擎

  在MySQL 5.6以后，InnoDB被设置成默认的存储引擎，支持事务和行级锁。

• 数据库和数据表统一使用UTF8MB4字符编码

  UTF8MB4字符编码支持中文储存以及表情存储，兼容性杠杠的。

• 所有的表和字段必须添加注释

  这个是好习惯的问题，即使做到了顾名思义，以防万一哪天健忘或理解错误，同时给后人留下后路，提高维护性。使用comment设定注释。

• 尽量控制表行数在500万以内

  数据量越多，则查询的效率越低，同时会导致长时间占用高内存以及磁盘IO过高。数据量膨大建议采用分表、合理分区等方案。

• 尽可能采用冷热数据分离策略

  在MySQL中，数据表列数最大限制为4096列 ，每条元祖数据总和大小不能超过65535字节，常用的字段与基本不常用的字段、细分不同业务的数据分开表设计存储，减小表宽度，保证热数据的内存缓存命中率，降低CPU使用率以及降低IO流。

• 禁止以图片、文件等二进制数据

  MySQL虽然支持对文件对象的存储，但是开发人员是不允许、不推荐这样做的。文件通常是很大的，转成二进制数据将是一串很长的字符串，无疑占用数据库很大的存储空间，在数据库读写更是消耗内存和占用大量的IO流，最终导致查询的效率低下。一般文件是存放于文件服务器，将文件服务器的路径存储于数据库中。

行为与流程规范

• 禁止在线上做数据库的压力测试

• 对应的环境使用对应的数据库比如测试环境一定要使用测试环境的数据库

• super权限只能属于DBA，不能赋予项目程序

• 养成查看SQL运行性能的习惯，可以借用性能分析工具

  譬如：EXPLAIN语句 | showprofile | mySQLsla等。

• 禁止在业务高峰期批量更新、查询数据

  可以在流量比较低的凌晨跑批操作。

• 活动推广、系统上线以及平台上新务必对流量进行评估

  防患于未然、否则可能造成数据库服务器流量瓶颈进而导致影响业务。

• 所有建表前都要确定字段的类型、长度以及索引方可建表

  确保表结构设计为最优是前期数据库最大的优化

• 所有对表的结构、数据的修改务必经过DBA的审阅和同意

表设计规范

• 尽可能每张表的索引数量控制在5个以内

  索引具有提高查询的效率的好处也有降低写操作效率的坏处，甚至会降低查询到的效率。同时索引也是占用内存空间的，因而应该合理控制索引的数量。

• 每一张InnoDB表都必须含有一个主键

  InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种 InnoDB是按照主键索引的顺序来组织表的。不要使用可能会更新的列作为主键，同时尽量不要使用UUID、MD5、HASH等无序的字符串作为主键。在没有特别的情况下，要使用自增的整型或发号器作为主键。

• 尽可能避免使用外键约束

  外键可以保证数据的准确性、参照完整性，每次进行写操作时都会走校验数据知否正确的流程，将会有损写操作的性能，数据的参照完整性建议在业务层实现。倘若字表的写操作很少的情况下务必使用外键约束。

• 设置数据表架构应考虑后期扩展型

  体验产品和架构师的交流和能力、对业务的熟悉度。

• 遵循范式与冗余平衡原则

  第一范式：具有原子性

  第二范式：主键列与非主键列遵循完全函数依赖关系

  第三范式：非主键列之间没有传递函数依赖关系

  合理的原则能够体验出数据库的可操作性、稳定性以及性能nice。范式设计是数据结构的一种思想，但是我们应当灵活使用，一味追求三范式无疑会影响程序的性能，适当的冗余是可以提高查询的效率的，前提要保证是主键的冗余。

• 控制每张表的字段在20以内，否则业务分表

  数据表的宽度与内存占用的大小成正比，在进行读写操作时，数据库程序将表结构与数据载入内存，当表宽度越长消耗的内存越多、越占IO流，导致操作的效率下降。将可能将字段按照业务细分、冷热的条件进行分表设计。

字段设计规范

• 尽可能不要在表中建立顾名思义的扩展字段

  比如ext、ext_1、extend_n，时间一长，好几个这样的字段，即使每一个都有comment，也会造成SQL的可读性，特别是在构建SQL语句的时候。

• 优先设置占存储空间最小的类型和长度

  合理设置字段的类型和长度，可以节省MySQL的表空间，是性能优化的姿势之一。同时，索引列定义空间越大也会导致建立索引的所需空间也越大。应当严禁定义字段，譬如

  IP应使用UNSUGNED或者INT结构类型，在PHP中可以使用long2ip与ip2long函数进行互转

  性别应使用CHAR(1)，即定长的字符串类型

  ... ...

• 尽可能避免使用TEXT、BLOB、ENUM数据类型

  MySQL 内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行，毋庸置疑会降低查询的效率。MySQL对索引字段长度是有限制的，TEXT或BLOB类型只能使用前缀索引。

• 避免ENUM数据类型

  在MySQL中，存储枚举类型的数据在库中，字段列中保存的值实际为整数，特别容易导致开发者混乱，同时在查询使用排序是基于数值整型的，虽然可以使用ORDER BY FIELD(),但是会导致索引失效，尽量避免这么做。

• 尽可能将所有的数据列定义为NOT NULL类型

  NULL列比较特殊，需要额外的空间来保存，同时会造成索引失效。

• 使用TIMESTAMP与INT替换DATETIME存储时间

  很明显，TIMESTAMP与INT占4位字节，而DATETIME占8位字节。那么存储时间应该如何选择TIMESTAMP与INT呢？TIMESTAMP的可读性高而INT的灵活性高，因而经常需要使用计算操作的应当使用INT存储，否则使用TIMESTAMP。

• 金额相关的数据必须使用DECIMAL数据类型

  谈到钱这个东西呢，精确是非常重要的，即便要浪费存储空间、笑?~DECIMAL 类型为精准浮点数，在计算时不会丢失精度，可以自定义其长度，可用于存储比 bigint 更大的整型数据。

• 表与表关联的键名保持一致或以关联表名的缩写为前缀

  规范事项，保持规范、养成习惯，提高程序的可读性。

• 固定长度的字符串字段务必使用CHAR

  节省存空间、降低内存使用率、提高读写性能。

• 使用UNSIGNEG存储非负整数

  节省存空间、降低内存使用率、提高读写性能。

• 禁止敏感数据以明文形式存储

  确保信息的安全性，比如密码、隐秘数据等。

索引规范

• 重要的SQL语句必须带上索引作为条件

• 避免冗余和重复索引

  重复索引： 在相同的列上按照相同的顺序创建的相同类型的索引。

  冗余索引： 两个索引按照相同的顺序覆盖了相同的列。

  在一张用户表里面，将用户id设置成主键的同时再设置成唯一索引，那就是重复索引，如果创建了索引(a,b)，再设置a索引，则a为冗余索引，这两种错误的操作都会降低读写的性能。

• 务必不要在作为查询条件很少、或者没有关联的字段下建立索引

  索引本身占用存储空间，过多设置会导致查询效率降低。比如在成绩表中将分数设置为索引，这是一种错误的做法。

• 禁止在索引列进行数学运算和函数运算

  MySQL不擅长于运算，需要计算的应该移至代码业务层。总而言之，凡是计算都要移至代码业务层(MySQL不擅长于运算)。

• 符合索引将区分度高的置前

  将区分度高的索引置前可以缩短查询的范围，以至提高查询的效率，特别是在JOIN连表查询，提高效率特别明显。

SQL使用规范

• 危险的SQL语句必须带上索引作为条件，谨记谨记

  哪些是危险的SQL语句呢，删、改皆为危险的语句，一定要记住带上WHERE。

• 建议使用预编译语句操作数据库

  先简单了解下SQL执行的流程，SQL先解析、预编译处理再生成执行计划，最后调用引擎的api方法返回执行的结果，使用预编译的操作姿势，在读写的时候可以省去预编译的时间，终而提高执行效率。

• 严禁使用SELECT *查询字段

  要什么SELECT什么，不能多，否则可能导致覆盖索引失效，消耗更多的 CPU 和 IO 以网络带宽资源。

• 查询语句务必带上索引以提高查询效率

• 必须避免数据类型隐式转换

  在MySQL中，数据会存在隐式转换，当该字段发生转换时，索引会造成失效。

• 充分利用利用索引优势

  既然设置了索引就好好充分利用好索引，将查询的效率提至最高。

• 禁止使用相同的账号跨库操作

  各执其职，互不越权。

• 禁止使用带有数据值却不带有字段键名的INSERT操作

  这是一种错误的做法，对于表的改动后会造成比较大的影响。

  INSERT INTO user VALUES ('alicfeng',23);
  # 应该这样操作
  INSERT INTO user (`username`,`age`) VALUES ('alicfeng',23);
  

• 尽可能使用JOIN替代子查询操作

  子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

• 尽可能避免使用JOIN关联过多的表

  一般情况下，建议JOIN的表不要超过5个，JOIN多表查询比较耗时时间，关联的表越多越耗时间，防止执行超时或死锁。

• 合并操作、减少数据库的交互

  可以灵活地合并 SQL 操作，降低IO消耗的同时也提高了执行效率，譬如

  UPDATE user SET username='alicfeng' FROM id=1995;
  UPDATE user SET age=23 FROM id=1995;
  
  # 合并操作成一条SQL
  UPDATE user SET username='alicfeng',age=23 FROM id=1995;
  

• 尽可能使用IN代替OR语句

• 禁止使用ORDER BY RAND()随机排序语句

  会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。

• 禁止在WHERE语句中进行计算

  对列进行函数转换或计算时会导致无法使用索引。

  # 索引会失效
  WHERE DATE(create_date)='20190308';
  # 灵活使用[推荐]
  WHERE create_date>='20190308' AND create_date<'20190309';
  

• 使用UNION ALL而不是使用UNION

  在已知数据没有重复或无须删除重复行的前提下，因为UNION需要重复值扫描，降低效率。

• 大批量写操作尽可能合理地分批次处理

  大批量的操作应当合理平均分批次处理，防止死锁影响业务，同时尽量将跑批这种大操作至于凌晨操作。

• 不在数据库做运算，务必将运算置于业务层

  MySQL不擅长数学运算和逻辑判断。

• 禁止使用索引做运算

  索引会失效。

• SQL语句简单化

• 使用事务尽量简单化，同时控制事务执行的时间

  时间长会导致长时间锁表，造成死锁，进而影响业务。

• IN语句参数的个数尽量控制在1000以内

• 注意LIMIT分页查询效率，LIMIT越大效率越低

  在使用LIMIT做分页时，更改巧妙地处理查询，譬如使用S1替换成S2，将有效地提高查询的效率。

  # S1
  SELECT `username` FROM `user` LIMIT 10000,20;
  # S2
  SELECT `username` FROM `user` WHERE id>10000 LIMIT 20;
  

• 编写SQL语句必须全部为大写，每个词必只允许只有一个空格符

  编写规范，必须统一并遵循。

• 尽可能使用EXIST|NOT EXIST替代IN | NOT IN

• 禁止使用LIKE添加%前缀进行模糊查询

  %前置会导致索引失效

• 禁止一条语句同时对多个表进行写操作

参考A_aliane、雪松等前辈的总结，非常感谢！