mysql设计与编码规范

• 规范目的：为研发人员在mysql表结构设计与SQL编码提供指导意见；
• 规范等级：每个规范的强度由强到弱分为：强制、推荐、参考三个等级；
• 规范范围：建表、建索引、SQL语句、编程框架

1.建表

1.1.【强制】统一使用数据库默认字符集utf8
生产库默认字符集utf8，如果使用了不同的字符集，表关联将无法正常使用索引；
需要用不同字符集的场景较少，如移动端emoji表情，需要用utf8mb4字符集，指定字段级字符集即可;

1.2.【强制】统一使用默认引擎innodb
innodb是mysql5.7默认的存储引擎，支持事务处理；如果有使用其他引擎的需求，需经评审确认。

1.3.【强制】对于相同的属性，字段名和类型要一致
说明：如user_id，只要是同一个模型的属性，在不同的表中要统一，varchar与int不能直接关联

1.4.【强制】varchar最大字符长度5000，超过5000定义为text类型，并创建到新表用主键关联
一条行记录除text和blobs最大字节长度65535，例如一个表只有一个varchar字段，字符集utf8，varchar最大字符长度为(65535-1-2)/3=21844；
-1：实际存储从第二字节开始；-2：每个varchar字段需要2个字节表示长度，以上是社区版特性，阿里云RDS要额外再减8个字节；
建表指定varchar长度超过最大限制后，字段会自动转换为text类型

1.5.【强制】每个表创建create_time和update_time字段
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
说明：这两个字段作为记录的元数据，由数据库自动初始化和更新，业务不需要手动插入或修改；可用于业务追溯和数据归档。

1.6.【强制】每个表创建主键

1.7.【强制】表和字段都必须有明确含义的注释，状态注释要完整

1.8.【强制】表达是否含义时，字段名is_xxx，类型tinyint，1表示是，0表示否

1.9.【强制】表名或字段名使用小写字母、数字和下划线，禁止数字开头，禁止2个下划线之间只有1个数字

1.10.【强制】表名只用单数形式，不用复数形式

1.11.【强制】小数类型使用 decimal，禁止使用 float 和 double
float 和 double 是浮点型，在存储的时候，存在精度损失的问题，decimal是定点型，不存在该问题

1.12.【强制】禁用外键、触发器

1.13.【强制】修改大表字段类型或特定范围的长度，必须使用阿里云的“DMS无锁变更”功能
rds 5.7版本，以下DDL操作会导致操作全过程加表级锁，该表所有事务都会被堵塞。

• 修改数据类型（如int -> bigint）
• 缩小varchar长度
• 增加varchar长度且跨过固定值
• utf8字符集，字符长度从 (1-85) 扩展到86+（我们目前用的utf8）
• utf8mb4字符集，字符长度从 (1-63) 扩展到64+

DDL耗时与表大小有关，如：表大小100M，ddl耗时预计10s左右，期间所有事务将被堵塞，通过阿里云的“DMS无所变更”功能可避免锁表问题。

1.14.【推荐】varchar长度如果在分界点附近，尽量大于分界点
utf8分界点：85、86
utf8mb4分界点：63、64
mysql5.7版本，以utf8为例，varchar长度非跨界扩展字段长度（如70->80或90->100），以inplace方式执行，跨界扩展长度（如80->90），以copy方式执行；
inplace方式：毫秒级响应，不堵塞DML
copy方式：整个表会copy一个临时表并rename，耗时依赖表大小，100M预计10s左右，期间堵塞该表所有DML。

1.15.【推荐】两个表1对1 的映射关系，涉及到排序的字段，统一放在主表
如商品表与商品扩展信息表，当两表关联且无选择性强的条件时（全表扫描），排序字段统一在主表，优化器以主表为驱动表JOIN时，一般可以用到排序字段的索引。

1.16.【推荐】字段尽可能使用not null属性
mysql可空列会使表和索引的统计信息更加复杂，执行计划也更复杂，更容易走错误的执行计划；
mysql需要对可空列做特殊处理，对可空列创建索引还需要额外字节作为判断是否为 NULL 的标志位。

1.17.【参考】时间字段类型的选择datetime与timestamp
数据记录元数据（create_time,update_time）使用timestamp类型
与业务关系不大但数据量较大的数据（如日志、监控、事件等）使用timestamp类型
其他使用datetime

2.建索引

2.1.【强制】唯一索引名：uk_字段名；普通索引名：idx_字段名
如组合索引，字段名较长，字段名可简写，但前缀必须按上述规范

2.2.【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
有效避免脏数据，且查询几乎都用得上该索引

2.3.【强制】多表join时，每个表的关联字段都创建索引
一般情况下，两表关联会用到其中一个表的关联字段的索引，强制每个表的关联字段都创建索引的原因有二：1、不能明确用哪个表的关联字段索引；2、在关联SQL中未用到的关联字段索引，在其他场景下用到的概率很大，因为是关联字段

2.4.【强制】组合索引，区分度最高的在最左边

2.5.【推荐】varchar长度超过64，创建索引时指定字段长度（前缀索引）

太长的字段直接创建的索引太大，效率较低，推荐用前缀索引；
通过count(distinct left(列名, 索引长度))/count()确认辨识度，在索引长度和辨识度之间取得平衡，如索引长度10的辨识度50%，索引长度20的辨识度90%，则取后者。
order by\group by无法使用前缀索引。

2.6.【推荐】条件语句如果选择性低于15%，确保条件字段有索引或组合索引
新写一条SQL，一定要关注下是否有索引，是否需要创建索引；

2.7.【推荐】单个索引字段数不超过5个
索引字段过多，索引会更大，扫描代价大，复用可能性低。

2.8.【推荐】不在低基数列上建立索引，如：性别、是否删除

3.SQL语句

3.1.【强制】记录数超过1000行的表，禁止左模糊或全模糊

3.2.【强制】参数类型必须与字段类型匹配

容易犯的错误：字段是varchar类型，条件语句中的数字未加引号，导致不能走索引，如SELECT * FROM tbl_name WHERE str_col=1。
因为'1'，'1a'等都会隐式转换为1，走索引会要对比多个值，因此不走索引。
mybatis参数用#{}，不要用${}，否则会导致上述问题。

3.3.【强制】where子句中对字段进行函数、表达式运算，只要能改写不用的，都不要用
举例
SELECT ……
FROM offline_monitor_statistics_pomm
where 1=1
AND DATE_FORMAT(offline_start_time,'%Y-%m-%d %H:%i:%s') >= '2021-05-13 16:41'
AND DATE_FORMAT(offline_start_time,'%Y-%m-%d %H:%i:%s') <= '2021-05-14 16:41'
order by offline_start_time desc LIMIT 10
条件改写：
AND offline_start_time >= '2021-05-13 16:41:00'
AND offline_start_time <= '2021-05-14 16:41:00'

3.4.【强制】禁用存储过程实现业务逻辑

3.5.【强制】处理数据时，必须先select+where条件确认范围无误，再update或delete；同时不可用select for update修改数据

select for udpate修改数据加锁时间较长（一般几十秒），有时甚至会忘记commit，导致线上业务堵塞

3.6.【强制】禁止使用select * 查询，列出需要的字段

3.7.【强制】禁止为了业务开发方便使用视图

含视图的SQL执行计划可能更复杂，也更难以调整优化；
视图可能给多个功能使用，使单个功能的SQL不是最精简的；
视图的维护复杂，基于一个功能需求调整视图可能给别的业务带来不确定性。

3.8.【推荐】分页功能要支持手写SQL

count(*)语句要支持自定义，很多情况下count(*)可以比分页查询更精简，甚至部分场景可以不用count(*)

3.9.【推荐】in操作要控制元素个数，建议不超过1000

3.10.【推荐】评估SQL执行频率，如果频率较高，条件语句适合用缓存，尽量用缓存

对公网暴露的功能、对海量终端提供服务的功能，更需要考虑高并发请求带来的数据库负载问题

3.11.【推荐】业务功能上只需要查最近n分钟、n小时或n天的数据，要加上时间条件

业务上只需要关注最近较短时间的数据，强制加上时间条件，避免不必要的全表扫描

4.编程框架

4.1.【强制】禁用JPA等全自动的持久层框架
SQL要能灵活地手写，要确保每个请求对应的SQL都是最精简的

4.2.【强制】分页框架要能支持手写count(*)语句
很多情况下count(*)可以比分页列表查询的SQL更精简，不要直接使用框架生成的SQL

4.3.【强制】transaction注解不能滥用，必须用在特定的方法上，不能笼统地注解到类上
查询功能走事务增加tps消耗，会执行大量SET autocommit = 0; commit;等语句

4.4.【强制】mybatis动态传递参数尽可能用#{}，尽量不用${}

 #{}是预编译处理，执行时会将sql中的#{}替换为？，然后调用PreparedStatement的set方法来赋值，传入参数后，会在值两边加上单引号，可避免SQL注入；
 ${}是字符串替换，执行时会将sql中的${}替换为变量的值，传入的参数不会在两边加上单引号。使用场景一般是字段名,表名等参数,例如order by ${column}。

4.5.【推荐】不要写一个大而全的数据更新或查询接口

一个大而全的接口会导致SQL变得臃肿，应该不同场景对应不同的SQL，确保SQL精简