MySQL规范

mysql 转载
转载验证
Publish Date:   2020-11-20

规范目的

优化数据访问层代码

规范范围

公司业务过程中使用的所有SQL代码 ，基于Mysql 5.5+

规范原则

减少SQL性能问题导致的系统故障，提高系统的稳定性。

规范内容

  1. 所有的DDL语句都不能回滚。
    解释：MySQL的DDL语句是非事务的，当执行到DDL语句时，会隐式的将当前回话的事务进行一次“COMMIT”操作，因此在MySQL中执行DDL语句时，应该严格地将DDL和DML完全分开，不能混合在一起执行。

  2. 新建的数据库命名规范要统一：<业务系统缩写>_<子系统缩写>；分库名称命名格式是<业务系统缩写>_<子系统缩写>_<编号>，编号从0开始递增。
    解释：采用26个小写字母和0-9的自然数（一般不使用）加上下互相 ‘_’ 组成，命名简洁明确，多个单词用下划线 ‘_’ 隔开

  3. 库名、表名、字段名、索引名必须使用小写字母。库名和表名不超过40个字符。临时表以tmp开头，备份表以bak结尾。
    解释：采用26个小写字母和0-9的自然数（一般不使用）加上下互相 ‘_’ 组成，命名简洁明确，多个单词用下划线 ‘_’ 隔开

  4. 单表字段不能超过50个，超出建议拆表。
    解释：mysql内存加载过程中，数据加载的最小单位是页。每个页中存储的行越多,则数据加载的页会越少，性能更好。

  5. 表一旦设计好，字段只允许增加，不允许减少（drop column），不允许改名称（change column)
    解释：减少字段，修改名称可能会导致已有业务的兼容性问题，不用字段废弃即可，不需要删除。对于实在需要修改名称的，用增加新字段的方式来带代替。

  6. 统一使用INNODB存储引擎,utf8编码, 整个数据库的编码统一为utf8_general_ci；如果需要存储emoji表情或者一些Unicode的符号。采用utf8mb4字符集，整个数据库的编码统一为utf8mb4_general_ci
    解释：MySQL里面实现的utf8最长使用3个字符，包含了大多数字符但并不是所有。例如emoji和一些不常用的汉字，如“墅”，这些需要四个字节才能编码的就不支持。确认没有特殊需求时，使用utf8比utf8mb4占用更少的空间。

  7. 禁止使用Mysql的存储过程，函数，触发器
    解释：互联网高并发的场景，很多数据都是分库分表的，而且要求高度可扩展，原则是对DB的保护做到最大化，能减少DB压力的就减少DB压力，尽量把运算逻辑拉到代码里面。存储过程的优点在于封装性好，直接让DB进行运算，但是缺点在于难以维护，而且大大增大DB压力。所以开发过程中禁止使用存储过程。

  8. 表必须有主键，建议统一由Auto-Increment字段生成整型，不建议使用组合主键，自增id只作为虚拟主键，不建议与业务数据处理有关联关系。
    解释：自增主键方便dba运维，比如归档，批量更新数据，可以根据主键来做范围归档，做到更精准的遍历数据。使用非自增id做主键，那么计算主键索引值可能每次结果都不会出现连续的，这样在插入B+树叶子节点的时候，就不会在最后一个叶子节点尾部插入。而是中间某个叶子节点插入节点，导致插入数据需要不断对页数据进行重排，效率会明显降低。如果使用varchar作为主键，索引比int大多了，bigint是8字节，索引大了，数据大了，IO压力也会加大。 按加载到内存考虑，varchar 占用的内存空间，是它定义的字符长度的至少3倍，同时非int, bigint容易产生索引分裂，InnoDB数据是按照主键聚簇的，数据在物理上按照主键大小顺序存储，使用其他列或者组合无法保证顺序插入，随机IO大，导致插入性能下降。

  9. 字段的数据类型和长度，必须符合数据的实际, 不能滥用varchar
    解释：合理的类型定义: 自增主键：bigint unsigned; 状态/code字段：tinyint/int/varchar; 日期时间：timestamp; 日期：date; 带小数数值：decimal ; 整数数值：tinyint/int/bigint (unsigned); 字符：varchar

  10. 尽量用单表查询，避免多表JOIN，禁止多于3表join，join的字段数据类型必须绝对一致。
    解释：互联网高并发的场景，任何表的数据都有可能爆发性增长。多表查询是笛卡尔乘积方式，需要检索的数据是几何倍上升的。多表查询数据量大了很容易遇到速度问题。做优化时，优化不仅仅是一个语句，而是所有业务。对于业务的理解不够，多表查询又多，优化是非常困难的。而单表的多次查询，业务逻辑清晰，优化方便，用多几行的代码和多几次的数据库查询是值得的。

  11. 表名列名必须有注释（必须加上COMMENT '<字段扼要解说>'），表结构变更须由库表OWNER所在团队发起
    解释：加上注释可以让其他开发人员更方便的理解字段和业务模型。同时业务表后续都会入仓数据仓库给大数据团队做进一步的数据挖掘，注释可以方便大数据团队更好的做数据挖掘。

  12. 不使用TEXT、BLOB、char，请使用VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N，请注意同一表中， 除blobs数据类型外，所有列允许最大长度之和 所占的 字节数 不能超过 65535。
    解释：char是定长格式，绝大多数场景，我们的业务数据不是定长的字符串，用char会浪费存储空间。

  13. 每张表数据量建议控制在三千万以下。
    解释：控制DDL变更时长，同时缩短整库备份时间，降低恢复难度；从性能角度看，走索引，每次查询数据精准到就几百行，22亿和1亿性能是没有区别的，如果是比较特殊场景，如where后面不带时间范围条件，全表查某个类型（type）的查询就有性能问题

  14. 如果字段只有true or false，请使用tinyint（数值范围-128~127）， 禁止使用enum，不方便扩展。
    解释：ENUM 字段底层其实是用整型来存储，比如如果用 ENUM 来存储字符串 ‘3’、’2’、’1’ 这样的数字字符串，那对应的索引就是 1、2、3，要是不熟悉的开发人员，很容易就会造成混淆，新增错误的数据。同时对于枚举类型，是很有可能会随时增加的，如果每增加一个枚举，就要修改字段，代价太大。

  15. 存储时间（精确到秒）建议使用TIMESTAMP类型，而不是DATETIME类型
    解释：TIMESTAMP使用4字节，DATETIME使用8个字节，使用TIMESTAMP更节约空间。同时TIMESTAMP具有自动赋值以及自动更新的特性。

  16. 禁止default NULL。建议数字类型not null default 0，字符类型not null default ‘’，时间not null default ‘1970-01-01 00:00:00’；
    解释：1）NULL值会有额外的空间来存储，并不是Null标志位只固定占用1个字节==，而是以8为单位，满8个null字段就多1个字节，不满8个也占用1个字节，高位用0补齐。对于相同数据的表，字段中有NULL值的表比NOT NULL的大，为了空间容量和性能，参考：http://mysql.taobao.org/monthly/2016/08/07/；2）NULL值是不相等的 ,对业务表述可能会有影响,不能=‘NULL’ 或者 = ‘null’，只能 is null，这个最终不利于开发的编写；3）count(),max(),min()是忽略NULL的，而count(*)是包含NULL值，那么count(col)和count(*)结果不一样

  17. 所有表必须有create_time和update_time，有明确操作来源的，考虑加上created_by, last_updated_by两个字段，记录数据的创建者和修改者。
    解释：方便后期数据分析与记录变化排查，哪怕只是配置表，只有10行记录; 有利于数据迁移和同步数据 有利于数据统一归档。

  18. 所有业务实体表/关系表，禁止硬删除，必须软删除，加上is_deleted字段，标注这条记录的状态。
    解释：软删可以记录最近的删除相关信息，方便出问题时的排查分析，以及业务历史数据分析。

  19. 加字段禁止使用after。
    解释：我我们不确定全局代码里面（如其他团队使用你的表）是否都insert into table（col，col，col。。。） value，如果你在中间插一个字段，就导致数据偏移的问题了，同样select * 的也可能会影响数值的偏移

  20. 字段size大小好合理设计，特别是varchar字段。
    解释：过大的varchar长度浪费存储资源，而过小的varchar长度可能出现业务突发情况时导致数据长度超过字段长度从而产生故障。

  21. SELECT语句只获取需要的字段，禁止使用SELECT * FROM语句
    解释：可以有效防止新增字段对应用逻辑的影响，还能减少对性能的影响。

  22. INSERT语句必须显式的指明字段名称，不使用INSERT INTO table value()。
    解释：可以有效防止字段并更时字段偏移引发的问题。

  23. 禁止在where子句中对字段施加函数.
    解释：会导致全表扫描。如to_date（add_time)>xxxxx,应改为:add_time >= unix_timestamp(date_add(str_to_date(‘20130227’,’%Y%m%d’),interval - 29 day))

  24. 写到应用程序里的SQL语句，禁止一切DDL操作，如对这些权限有要求，必需与DBA协商同意方可使用
    解释：DDL写入程序，可能对表结构产生不可控的影响，进而影响业务使用。

  25. WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，如ISENDED=1，字段类型是tinyint，那么不能是ISENDED=‘1’。
    解释：隐式转换会导致索引失效。

  26. 避免在SQL语句进行数学运算或者函数运算。
    解释：容易将业务逻辑和DB耦合在一起，推荐在业务逻辑中完成功能。

  27. INSERT语句尽量使用batch提交。
    解释：减小DB的写压力。

  28. 尽可能使用合理的SQL语句减少与数据库的交互次数。
    解释：减小交互次数可以减小DBD的负载。

  29. 不使用ORDER BY RAND() 。
    解释：在ORDER BY从句里面使用RAND()函数，会导致数据列被多次扫描。可以用max(id) * rand()代替

  30. 使用合理的分页方式以提高分页的效率。
    解释：对于大表的扫描，直接select * from XXX limit X,Y，在深度翻页时会导致严重的慢查询。推荐的方式为：select * from XXX WHERE id>=M order by id limit N; M为上一轮扫描的最大id值。

  31. InnoDB表避免使用COUNT(*)操作。
    解释：会显著增大DB的负载，建议计数统计实时要求较强可以使用memcache或者redis，非实时统计可以使用单独统计表，定时更新。

  32. 不建议使用%前缀模糊查询，例如LIKE “%XXX”
    解释：会显著增大DB的负载，如果有较多类似的查询需求，建议数据入ES，在ES做模糊查询。

  33. 避免多余的排序，排序尽量在业务端做。
    解释：排序会显著增大DB的负载。使用GROUP BY 时，默认会进行排序，当你不需要排序时，可以使用order by null,例如Select a.OwnerUserID,count(*) cnt from DP_MessageList a group by a.OwnerUserID order by null; 如果有较多排序需求，建议数据入ES，在ES做排序。

  34. 禁止使用 REPLACE INTO。
    解释：Replace into 操作在唯一键重复情况下，是先尝试写入，检测到冲突则删除原记录，再写入新记录。该操作在二进制日志中只有一条 update 操作，将导致同一张表的 auto_increment 值主从不一致，主从切换后，极易致业务写入失败；同时一次 Replace into 的写入操作最坏情况下有两次 DB 操作，操作负担重；在并发情况下，Replace into 因不同 session 获取锁的顺序问题，产生死锁的概率更高。

  35. 禁止使用子查询，比如select col、col from table where id in （select col from table）
    解释：会显著增大DB的负载，高并发场景下，会出现严重的延迟问题。

  36. 数据查询每次返回数据记录数大小不能超过1000
    解释：会显著增大DB的负载，高并发场景下，会出现严重的延迟问题。需要根据业务QPS和记录长度来评估1000以内什么值合适。

  37. 禁止使用 UUID()，USER()这样的MYSQL INSIDE函数
    解释：会导致主备数据不一致，重要的是会严重影响mysql性能。

  38. 如果应用使用的是长连接，应用必须具有自动重连的机制。避免每执行一个SQL去检查一次DB可用性；
    解释：会显著增大DB的负载

  39. 原则上禁止使用分区表
    解释：分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键， 跨分区查询效率可能更低，如果有对应的需求，建议将数据入大数据的分区的数据仓库，比如Hive分区表，基于大数据做离线查询。

  40. 对于访问量非常大，且不做表拆分的数据库，需考虑读写分离
    解释：读写分离可以提高查询性能，但是读写分离有出现主从复制有延迟的可能性，需要综合评估。

  41. 单个事务，处理的行数不能超过1000 rows/s
    解释：超出了会导致主从复制延迟的问题，进而引发其他问题。

  42. 事务操作完后要及时commit，释放锁。
    解释：避免死锁和性能问题。

  43. 如果存在并发对相同对数据做DML，要按顺序操作。
    解释：避免死锁。

  44. 非唯一索引使用“idx_表缩写名称_字段缩写名称”进行命名。唯一索引建议使用“uniq_表缩写名称_字段缩写名称”进行命名。索引名称必须使用小写。

  45. 唯一键不和主键重复。每个业务实体表和关系表都应该至少有一个业务主键对应的唯一索引。
    解释：与业务相关的唯一健可以避免业务数据错乱。

  46. 索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面
    解释：提高索引检索的效率。

  47. 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。。
    解释：using temporary：意味着mysql对查询结果进行排序的时候使用了一张临时表。using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

  48. UPDATE、DELETE语句需要根据WHERE条件添加索引。
    解释：提高sql执行效率。

  49. 合理创建联合索引（避免冗余）
    解释：比如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

  50. 组合索引字段数不允许超过5个
    解释：字段超过5个时，实际已经起不到有效过滤数据的作用。

  51. 合理利用覆盖索引。
    解释：比如SELECT email,uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid,email)，以获得性能提升。

  52. 单表索引尽量不要超过10个
    解释：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

  53. 主键的内容不能被修改。
    解释：如果的确有主键修改需求，建议先删除记录，再重新添加。

  54. 禁用数据库外键 。
    解释：外键会导致性能下降，每次对数据进行DELETE或UPDATE操作都必须考虑外键约束，数据库都会判断当前操作是否违反数据完整性，性能下降。同时，使用外键，外键关联的数据查询要去另一张表，获取额外的锁，容易造成死锁。此外还会导致扩展性问题，在数据迁移时会遇到问题。

原文: …

Author: Itaken
Link: https://itaken.github.io/2020/11/20/MySQL%E8%A7%84%E8%8C%83/
Reprint policy: All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source Itaken !
mysql 转载
  TOC目录