MySQL使用规范.md 14 KB

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是按照主键索引的顺序来组织表的。

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

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

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

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

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

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

第一范式:具有原子性

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

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

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

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

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

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

字段设计规范

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

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

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

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

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

  • 尽可能避免使用TEXTBLOBENUM数据类型

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

  • 避免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 *查询字段

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

  • 查询语句尽可能限定条件以提高查询效率

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

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

  • 充分利用索引优势

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

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

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

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

  # 应该这样操作
  INSERT INTO user (`name`,`age`) VALUES ('lihua',23);
  • 尽可能使用JOIN替代子查询操作

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

  • 尽可能避免使用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()随机排序语句

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

  • 禁止在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 微信模板消息配置表

字段规范

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

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

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