有勇气的牛排博客
☰

开发规范（五） MySQL规范

有勇气的牛排 367 架构、规范 2023-03-24 00:09:08

1 建表规约

1.1 【强制】是否字段

表达是与否概念的字段，必须使用 is_xxx 的方式命名。

数据类型是 unsigned tinyint ：

  • 1 表示 是
  • 0 表示 否

说明：任何字段如果为非负数，必须是 unsigned。

# 正例，删除
is_deleted		1表示删除、0表示未删除

1.2【强制】表名、字段 规范

  • 表名、字段名必须使用小写字母或数据
  • 禁止出现数字开头
  • 禁止两个下划线中间只出现数字

数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

MySQL 在 Windows 中不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大小字母，避免节外生枝。

# 正例
aliyun_admin、rdc_config、level3_name

# 反例
AliyunAdmin、rdcConfig、level_3_name

1.3【强制】表名不使用负数名词

说明：表名应该仅仅表示里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。

1.4【强制】禁用保留字

禁用保留字，如 desc、range、match、delayed等，请参考 MySQL 官方保留字。

5.1.5【强制】索引名

主键索引名为： pk_字段名

唯一索引名为：uk_字段名

普通索引名为：idx_字段名

说明：

  • pk_ 即 primary key
  • uk_ 即 unique key
  • idx_ 即 index 的简称

5.1.6【强制】小数类型为 decimal

说明：在存储的时候，float 和 double 都存在精度损失问题，很可能在比较值的时候，得到不正确的结构。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。

1.7【强制】字符串相等，使用 char

如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

1.8【强制】varchar、text

varchar 是可变长字符串，不预选分配粗出空间，长度不超过 5000。

如果长度超过5000，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其他字段索引效率。

1.9【强制】表必备字段：id、create_time、update_time

说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增，步长为1。

create_time、update_time 的类型均为 datetime 类型。

1.10【推荐】表命名规则

表命名最好遵循： 业务名称_表的作用

# 正例
alipay_task
force_project
teade_config

1.11【推荐】库名与应用名称尽量一致

库名与应用名称尽量一致

1.12【推荐】字段变动，加注释

如果修改字段含义，或对字段表示的状态追加时，需要及时更新字段注释。

1.13【推荐】字段允许适当冗余

字段允许适当冗余，已提高查询性能，但必须考虑数据一致。冗余字段应遵循：

  • 不是频繁修改的字段
  • 不是 varchar 超长字段，更不能是 text 字段
  • 不是唯一索引的字段
# 正例
商品类目名称使用频率高。字段长度短，名称基本不变，可在相关联的表中冗余存储类目名称，避免关联查询。

1.14【推荐】分表分库

单表行数超过 500 万行，或者单表容量超过 2GB，才推荐进行分库分表。

说明：如果预计 3 年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

1.15【参考】合适的字符存储长度

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。

对象 年龄区间 类型 字节 表示范围
人 250岁之内 tinyint unsigned 1 无符号值：0~255
龟 数百岁 smallint unsigned 2 无符号值：0~65535
恐龙化石 数千万年 int unsigned 4 无符号值：0~约42.9亿
太阳 约 50 亿年 bigint unsigned 8 无符号值：0~约10的19次方

2 索引规约

2.1 【强制】唯一索引

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

**说明：**不要以为唯一索引影响了 insert 的速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

2.2【强制】禁止3个以上表 join

超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致；

多表关联查询时，保证被关联的字段需要有索引。

**说明：**即使双表 join 也需要注意表索引、SQL 性能。

2.3【强制】varchar 建立索引，必须指定长度

在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本长度，决定索引长度即可。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型的数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

2.4 【强制】页面搜索，请走搜索引擎

页面搜索，严禁使用走模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tee 的最左前缀匹配的特性，如果左边的值未确定，那么无法使用此索引。

2.5 【推荐】order by 放最后

如果有 order by 的场景，请注意利用索引的有序性。order by 最火的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

# 正例
where a=? and b=? order by c;

# 反例
索引如果存在范围查询，那么索引的有序性无法利用，如：
where a>10 order by b;
其中，索引 a_b 无法排序。

2.6【推荐】覆盖索引，避免回表

利用覆盖索引来进行查询操作，避免回表。

说明：我们看一本，只需要就看目录即可知道章节标题，无需翻到指定页面查看查看标题。

# 正例
能够建立索引的种类分为：主键索引、唯一索引、普通索引三种，而覆盖索引指示一种查询的一种效果，用explain的结果，extra列就会出现：using index

2.7【推荐】利用延迟关联或者子查询优化超多分页场景

说明：MySQL 并不是跳过 offset 行，而是去 offset + N 行，然后返回放弃前 offsert行，返回 N 行，那当 offset 特别大的时候，效率就非常低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

# 正例
# 先快速定位需要获取的 id 段，然后在关联
select a.* from 表1 a,
(select id from 表1 where 条件 limit 100000, 20) b
where a.id=b.id;

2.8【推荐】SQL 性能优化目标

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

说明：

  • consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
  • ref 指的是使用普通的索引（normal index）
  • range 对索引进行范围检索。
# 反例
explain 表中的结构，type=index，索引物理文件全扫描，速度非常慢，这个index界别比较range，还低，与全表扫描是大巫见小巫。

2.9【推荐】建立索引的时候，区分度高的放到最左边

# 正例
# 如果 a 列几乎接近于唯一值，那么只需要单键 idx_a 素索引即可。
where a=? and b=?

说明：存在非等号和等号混合是，在建立索引时，请吧等号条件列前置

where c>? and d=?
其中，c的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。

2.10【推荐】防止因字段类型不同，导致索引失效

防止因字段类型不同造成的隐式转换，导致索引失效。

2.11 【参考】创建索引时避免有如下极端误解

  • 宁滥勿缺，认为一个查询就需要建立一个索引。
  • 宁缺毋滥，任务索引会消耗空间、严重拖慢记录的更新，以及行的新增速度。
  • 抵制唯一索引。认为业务的唯一性，一律需要在应用层通过“先查后插”方式解决。

5.3 SQL 语句

4 ORM 映射

留言

专栏
文章
加入群聊
程序员兼职接单交流
编程交流