# 数据库开发规范

本文描述了 MySQL 开发中需遵循的相关规范。

# Schema 设计规范

# 数据库命名规范

不能使用与数据库相关的关键字，例如 index、desc、range、match、 delayed 等， 请参考 MySQL 官方保留字。

缺点：引起歧义；同时给数据库维护带来困难，导入数据库会提示错误

建议：选用业务相关的库名

>show databases;
+--------------------+
| Database |
+--------------------+
| index |

# Schema 设计目标

• 快速实现功能为主，保证节省资源
• 平衡业务技术各个方面，做好取舍
• 不要在 DB 里进行大计算，减少复杂操作

# 数据库名称

数据库名称与应用名称尽量保持一致

# 适当考虑一些反范式的表设计

适当增加冗余字段，减少 JOIN ，以提高查询性能;但必须考虑数据一致。

冗余字段应遵循：

1. 不是频繁修改的字段。
2. 不是 varchar 超长字段，更不能是 text 字段。

正例： 商品类目名称使用频率高， 字段长度短，名称基本一成不变， 可在相关联的表中冗余存储类目名称，避免关联查询。

# 字段数量及大字段

核心表字段数量尽可能地少，有大字段要考虑拆分

# 资金字段处理为整型

考虑统一 *100 处理成整型，避免使用 decimal 浮点类型存储。

# 日志类型的表

可以考虑按创建时间水平切割，定期归档历史数据

# 表和数据类型设计规范

# 表的命名规范

表名统一使用 小写，单词间以下划线分隔。 表名前缀 2-3 位的业务缩写，然后跟表的作用，如：hw_exam_report。

表名不使用复数名词。 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应的实体类也是单数形式，符合表达习惯。

# 字段数量

字段数量建议不超过 20-50 个

# 数据类型

更小的数据类型通常更好。

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数 据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要 的 CPU 周期也更少，而且还会占用更少的带宽。涉及几个表做 JOIN 时，效果更 加明显。

简单数据类型的操作通常需要更少的 CPU 周期。

例如，整型比字符型操作代价更低，因为字符集和校对规则（排序规则） 使字符比较比整型比较更复杂。

# 尽量避免 NULL

所有字段均定义为 NOT NULL，除非你真的想存储 NULL。

# 主键

建议使用自增的字段作为主键，这样 B+Tree 的每一个结点都会被顺序的 填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。 Innodb 的索引文件本身就是数据文件，即 B+Tree 的数据域存储的就是实 际的数据，这种索引就是聚集索引。这个索引的 key 就是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。 InnoDB 的辅助索引数据域存储的也是相应记录主键的值而不是地址，所 以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际 的数据。所以 Innodb 不建议使用过长的主键，否则会使辅助索引变得过大。

# 字符集

数据库默认字符集为 utf8 编码时，即使存储电话号码如 18888888888 也会占用 33 个字节(注意和 Java 中 UTF-8 的差别)， 因此在数据库设计时对明确只有 ASCII 字符集的字段可明确指定字符集以节省空间。

ALTER TABLE user MODIFY
  phone VARCHAR(16)
    CHARACTER SET latin1;

对表情符的存储，不建议选择 utf8mb4 字符集； 建议在业务中存储数据库前将表情符替换为 HTML 实体，如 😱 ， 如果需要在业务呈现时再对它解码为表情符(网页可能就不需要做解码了)。

# 字符串类型

VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。 如果存储长度大于此值，定义字段类型为 text ，独立出来一张表，用主键 来对应，避免影响其它字段索引效率。

VARCHAR 的适合场景

字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问 题。 VARCHAR 类型用于存储可变长字符串。它比定长类型 CHAR 更节省空间。

CHAR 适合存储很短的字符串，或者所有值都接近一个长度 CHAR 是固定长度的字段，使用不当，会浪费很多的空间。

# 数值类型

尽量使用 UNSIGNED 存储非负整数，因为实际使用时存储负数的场景不多。

int(2)、int(5)、int(7)、int(10) 和 int 没有区别，后面的数字不是代 表位数，只是显示长度。

数字类型的选取：万不得已，不要用 double 类型。 除了占用空间比较大之外，还有精度问题。同样，固定精度的小数也不要 使用 decimal，建议乘以固定倍数，转换成整数进行存储。可以节省存储空间， 而且不用任何附加维护成本。

# 时间类型

在日常建表时应优先选择 timestamp 类型，datetime 和 timestamp 都可 以精确到秒，但 datetime 占用 8 字节，而 timestamp 只占用 4 字节，timestamp 还具有自动更新时间功能。

对于需要精确到某一天的类型，建议使用 date 类型。因为它存储需要三个字节。比 timestamp 还少。

不建议使用 int 来存储一个 unix timestamp。不直观，不会带来任何好处。

# 布尔类型

表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（ 1 表示是， 0 表示否） 。

说明： 任何字段如果为非负数，必须是 unsigned 。 正例： 表达逻辑删除的字段名 is_deleted ， 1 表示删除， 0 表示未删除。

# 外键

不得使用外键与级联，一切外键概念必须在应用层解决。

说明： 以学生和成绩的关系为例， 学生表中的 student_id 是主键，那么 成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时 触发成绩表中的 student_id 更新， 即为级联更新。外键与级联更新适用 于单机低并发，不适合分布式、高并发集群； 级联更新是强阻塞，存在数 据库更新风暴的风险； 外键影响数据库的插入速度。

# 大表 DDL

对 100 万以上的大表增加字段或索引时，请使用第三方工具进行操作，如 OAK 或 OSC。 避免直接操作对数据库产生长时间的锁定和性能问题。

# 索引设计规范

# 索引使用原则

索引使用原则: 高选择性、取出表中少部分的数据

并不是在所有的查询条件下出现的列都需要添加索引。对于什么时候添加 B+树索引，经验是访问表中很少一部分行时，使用 B+树索引才有意义。 对于性别字段、地区字段、类型字段，它们可取值的范围很小，即低选择 性。如： SELECT * FROM students WHERE gender = 'M'; 这时添加 B+树索引是完全没有必要的。 相反，如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此 时使用 B+树索引是最适合的，例如姓名字段，基本上在一个应用中都不允许重 名的出现。

因此，当访问高选择性字段并从表中取出很少一部分行时，对这个字段添 加 B+树索引是非常有必要的。但是如果出现了访问字段是高选择性，但是取出 的行数据占表中大部分数据时，这时 MySQL 数据库就不会使用 B+树索引了。 根据经验（并没有在源代码中得到验证），当优化器取出的数据量超过表 中数据的 20% ，优化器就不会使用索引，而是进行全表扫描。

# 所有表必须有显式主键

InnoDB 表是以主键排序存储的 IOT 表

# 索引字段数量

单个索引字段数不超过 5，单表索引数量不超过 5，避免冗余索引。 建立的索引能覆盖 80% 主要的查询，不求全，解决问题的主要矛盾。 复合索引排序问题，多用 explain 去确认。

# 提前考虑索引

要在表的设计之初考虑到常用索引。 一些开发人员总是在事后才想起添加索引——这源于一种错误的开发模式。

# 组合索引

建组合索引的时候，区分度最高的在最左边。 正例： 如果 WHERE a=? AND b=? ， a 列的几乎接近于唯一值，那么只需 要单建 idx_a 索引即可。 说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的 列前置。如： WHERE a>? AND b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

# 前缀索引

如果对串列进行索引，应该指定一个前缀长度。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要 对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间， 也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。

更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值， 因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中 较多块的可能性。

# 索引创建考量

• 较频繁的作为查询条件的字段应该创建索引;
• 更新非常频繁的字段不适合创建索引；
• 不会出现在 WHERE 子句中的字段不该创建索引。

# 唯一索引

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引 (存在软删除 is_deleted 的表除外)。

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

# 模糊查询

左模糊或全模糊查询无法使用 INDEX。 应当尽可能避免，如：SELECT * FROM table WHERE name LIKE '%xxx%'， 不建议使用 % 前缀模糊查询，例如 LIKE '%webo'。

# 查询索引失效

尽量避免在 WHERE 子句中对索引字段进行表达式和函数操作，这会导致索引失效。

# 索引有序性

如果有 ORDER BY 的场景，请注意利用索引的有序性。ORDER BY 最后的 字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 正例： WHERE a=? AND b=? ORDER BY c; 索引： a_b_c 反例： 索引中有范围查找，那么索引有序性无法利用，如： WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

# 覆盖索引

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

说明： 如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应 的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。 正例： 能够建立索引的种类分为主键索引、唯一索引、普通索引三种， 而覆盖索引只是一种查询的一种效果，用 explain 的结果， extra 列会 出现： using index。

# SQL 性能优化目标

SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如 果可以是 consts 最好。 说明： 1） consts 单表中最多只有一个匹配行（主键或者唯一索引） ，在优化 阶段即可读取到数据。 2） ref 指的是使用普通的索引（normal index） 。 3） range 对索引进行范围检索。 反例： explain 表的结果， type=index，索引物理文件全扫描，速度非 常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫

# 大表分页

利用延迟关联或者子查询优化超多分页场景。 说明： MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回 放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的 低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。 正例： 先快速定位需要获取的 id 段，然后再关联： SELECT a.* FROM 表1 a, (SELECT id FROM 表1 WHERE 条件LIMIT 100000,20 ) b WHERE a.id=b.id

# 隐式转换

禁止隐式转换。数值类型禁止加引号，字符串类型必须加引号。

# 索引命名规则

• 主键索引命名为 pk_{field}
• 唯一索引命名为 uk_{field}
• 普通索引命名为 idx_{field}

# SQL 编写规范

# SQL 大小写

SQL 本身不区分大小写，开发人员在实际编写 SQL 中需要自己遵循大小写规范。

对所有 SQL (不可变的)关键字，如 SELECT、FROM、WHERE、JOIN、IS NULL 等统一使用大写； 对表名统一使用小写； 字段名根据需要写小驼峰写法。

# 多表 JOIN

禁止多于 3 表的 JOIN，尽量用单表查询。 复杂的语句在应用层来解决。MySQL 只支持 nested loop 算法。 需要 JOIN 的字段，数据类型必须绝对一致； 多表关联查询时， 保证被关联的字段需要有索引。

# 优化 JOIN

避免大表与大表之间的 JOIN ，考虑让小表去驱动大表 JOIN 。 最多允许三表 JOIN ，最好控制成两表。 控制 JOIN 后面 WHERE 选择的行数。

# 避免查询不需要的字段和记录

SELECT 语句只获取需要的字段，禁止使用 SELECT * FROM 语句。 有效防止新增字段对应用逻辑的影响，还能减少对性能的影响；使用 * 会 到数据字典里面解析全部的字段；而且增加了网络传输的开销。减少磁盘的读。

# 关联查询

优化关联查询，确保关联字段上面有索引。 在关联顺序的第二个表的相应列上创建索引。

# 负向查询

禁止使用负向查询，如 NOT IN、!=、NOT LIKE。

# UNION ALL

尽量使用 UNION ALL 代替 UNION。

当有主键存在于语句中的时候， UNION 与 UNION ALL 的差异主要是前者需 要将两个（或多个）结果集合并后进行唯一性过滤操作，这就会涉及到排序，增 加大量的 CPU 运算，加大资源消耗及延迟。所以当我们确定不可能出现重复结果 集或者不在乎重复结果集的时候，尽量使用 UNION ALL 。

# 尽量用 UNION 代替子查询

# EXPLAIN

注重 WHERE 条件，多用 EXPLAIN 确认。

WHERE 条件的字段，尽量用区别度高的字段，这样走索引的性能更好。 出现子查询的 SQL ，先确认 MySQL 版本，利用 EXPLAIN 确认执行计划进行分页优化; DML 时候多个 value 合并。

# COUNT(*)

不要使用 COUNT(列名) 或 COUNT(常量) 来替代 COUNT(*)

COUNT(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和 非 NULL 无关。

说明： COUNT(*) 会统计值为 NULL 的行，而 COUNT(列名) 不会统计此列为 NULL 值的行。

COUNT(distinct col) 计算该列除 NULL 之外的不重复行数 注意 COUNT(distinct col1, col2) 如果其中一列全为 NULL，那么即使另 一列有不同的值，也返回为 0。

当某一列的值全是 NULL 时， COUNT(col) 的返回结果为 0，但 SUM(col) 的返回结果为 NULL， 因此使用 SUM() 时需注意 NPE 问题。

正例： 可以使用如下方式来避免 SUM 的 NPE 问题： SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

# ISNULL

使用 ISNULL() 来判断是否为 NULL 值。 说明： NULL 与任何值的直接比较都为 NULL。

• NULL<>NULL 的返回结果是 NULL， 而不是 false。
• NULL=NULL 的返回结果是 NULL， 而不是 true。
• NULL<>1 的返回结果是 NULL，而不是 true。

# 分页

在代码中写分页查询逻辑时，若 COUNT 为 0 应直接返回，避免执行后面的分页语句。

# 存储过程

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

# 避免多余的排序

使用 GROUP BY 操作时，默认会进行排序，当你不需要排序时，可以使用 ORDER BY NULL，例如： SELECT a.owneruserid, COUNT(*) cnt FROM DP_Messagelist a GROUP BY a, owneruserid ORDER BY NULL;

# 数据修正

数据修正（特别是删除、修改记录操作） 时，要先 SELECT ，避免出现 误删除，确认无误才能执行更新语句。

# 使用 WHERE 子句替换 HAVING 子句

除了一些集合函数的比较，如 COUNT() 等，其它的条件都应该写在 WHERE 子句中。

# 使用 IN 代替 OR

SQL 语句中的 IN 的值不应过多，应少于 1000 个

# 避免在数据库中进行大量计算任务

大事务拆分为多个事务，分批多次操作

# 利用 NoSQL 为 MySQL 减压

Redis 对读操作进行缓存，可以给数据库减少读的压力。

# 应用访问优化原则

• 减少数据访问 - 减少磁盘访问
• 返回更少数据 - 减少网络传输或磁盘访问
• 减少交互次数 - 减少网络传输

# 禁止使用 TRUNCATE TABLE

TRUNCATE TABLE 有可能造成事故，且难以恢复，因此不建议使用此语句。

说明： TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。