MySQL使用规范

全局命名规范

对象名称必须使用小写，多单词统一使用下划线分割
命名的单词必须做到顾名思义、简洁。表名长度最好不要超过16个字符，字段名称长度最好不要超过32个字符
禁止使用保留字并且尽量少用含有关键词来命名
临时表必须以tmp_开头、以日期结尾，备份表必须以bak_开头、以日期结尾

基础规范

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

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

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

UTF8MB4字符编码支持中文储存以及表情存储，兼容性更好。在创建新的数据库和数据库应使用这个编码，而已有数据库和数据表可以保留原有编码。

所有的表必须添加注释，字段尽可能注释

这个是好习惯的问题，即使字段命名做到了顾名思义，但以防日后健忘或理解错误，同时为了提高维护性，必须对字段尽可能添加注释。

如 id, create_time, update_time 这些通用字段可以忽略注释，但还是建议统一添加注释。而其他业务字段，必须有注释。如果是作为外键关联其他表，必须要注释说明关联哪个表哪个字段。

使用comment设定注释。

尽量控制表行数

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

从经验来看，超过 1000w 行的表就有可能带来性能隐患。

尽量控制表的字段数

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

一般来说，单表字段控制在 20 以内比较合理。

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

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

业务表必须包含通用必要字段

为了更好管理数据的增删查改，约定以下通用字段，新建的业务表必须包含这些字段：

  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '更新时间',
  deleted bit default 0 not null comment '删除标识：0-未删除；1-已删除'

如果业务是支持多个医院的，必须加上医院 id：

  hospital_id int not null comment '医院 id'

行为与流程规范

禁止在线上做数据库的压力测试
对应的环境使用对应的数据库

比如测试环境不能使用生产环境的数据库，防止产生数据污染。

super权限只能属于DBA
养成查看SQL运行性能的习惯，可以借用性能分析工具

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

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

只能在流量比较低的时段跑批操作。

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

防患于未然、否则可能造成数据库服务器流量瓶颈进而导致影响业务。例如之前的疫苗预约临时上线，没做好流量评估就引发过业务故障。

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

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

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

目前没有 DBA 一职，则由技术负责人兼任。

表设计规范

合理建索引

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

一般来说，尽可能每张表的索引数量控制在5个以内。

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

InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种 InnoDB是按照主键索引的顺序来组织表的。

不要使用可能会更新的列作为主键，同时尽量不要使用UUID、MD5、HASH等无序的字符串作为主键。在没有特别的情况下，要使用自增的整型或发号器作为主键。

尽可能避免使用外键约束

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

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

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

遵循范式与冗余平衡原则

第一范式：具有原子性

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

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

合理的原则能够体现出数据库的可操作性、稳定性以及性能水准。

范式设计是数据结构的一种思想，但是我们应当灵活使用，一味追求三范式无疑会影响程序的性能，适当的冗余是可以提高查询的效率的，前提要保证是主键的冗余。

控制每张表的字段数在 20 以内

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

字段设计规范

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

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

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

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

例如软删除标识字段 deleted，就应该使用 bit。性别字段，就可以使用 tinyint。

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

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

避免ENUM数据类型

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

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

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

使用UNSIGNEG存储非负整数

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

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

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

新增或修改字段，必须充分考虑历史数据的兼容性

新增或修改字段，必须充分考虑其类型、取值范围、是否可空等因素，绝对不能对线上历史数据造成影响。

线上执行 SQL，需要在 TAPD 填写《数据库更新》文档

数据库更新文档

示例：

  2020 年 03 月 12 日 13:32:23
  事件：sns_user_info 表增加 terminal 字段索引
  执行人：郭城锋
  审核人：郭城锋
  执行内容：
  ALTER TABLE `ywt_center`.`sns_user_info` ADD INDEX `idx_terminal` (`terminal`)

索引规范

重要的SQL语句必须带上索引作为条件
避免冗余和重复索引

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

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

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

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

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

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

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

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

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

索引命名规范

索引命名统一使用 idx 前缀，后缀一个或多个索引字段的名称。示例：idx_user_id_order_id

SQL使用规范

危险的SQL语句必须带上条件

危险的SQL语句指删、改操作，一定要记住带上WHERE，禁止全表更新。

避免使用SELECT *查询字段

尽可能只查询需要的字段，否则可能导致覆盖索引失效，消耗更多的 CPU 和 IO 以网络带宽资源。

查询语句尽可能限定条件以提高查询效率
必须避免数据类型隐式转换

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

充分利用索引优势

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

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

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

  INSERT INTO user VALUES ('lihua',23);

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

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

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

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

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

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

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

  UPDATE `user` SET username='lihua' WHERE id=680782;
  UPDATE `user` SET age=23 WHERE id=680782;
  
  # 合并操作成一条SQL
  UPDATE user SET username='lihua',age=23 WHERE id=680782;

尽可能使用IN代替OR语句
禁止使用ORDER BY RAND()随机排序语句

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

禁止在WHERE语句中进行计算

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

  # 索引会失效
  SELECT `name` FROM `table` WHERE DATE(create_date)='20190308';
  # 灵活使用[推荐]
  SELECT `name` FROM `table` 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;

尽可能使用EXIST|NOT EXIST替代IN | NOT IN
禁止使用LIKE添加%前缀进行模糊查询

%前置会导致索引失效

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

业务表规范

以下是针对具体的业务表制定的规范。

template_msg_config 微信模板消息配置表

字段规范

template_code 字段，格式规定为 TPL_AAABBB。其中 AAA 是终端号，高位不足补零；BBB 是递增的序号，从 0 开始。同一个终端的不同模板，BBB 不能相同。示例：太和公众号的第 7 条消息，template_code 命名为 TPL_008006。

目前已有的消息模板代码保留不变，后面的业务新增消息代码，要遵守此规范。

新建消息模板配置数据，template_name、template_content 必须补充完整，具体内容可以参考产品提供的消息文档。

MySQL使用规范.md 14 KB Permalink History Raw