MySQL 设计规范
在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，下面规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。以下规范在大型互联网公司经过了充分的验证，尤其适用于并发量大、数据量大的业务场景。先介绍的是安全规范，因为安全无小事，很多公司都曾经因为自己的数据泄露导致用户的惨痛损失，所以将安全规范放到了第一位。
一、安全规范
1.【强制】禁止在数据库中存储明文密码，需把密码加密后存储
说明：对于加密操作建议由公司的中间件团队基于如 mybatis 的扩展，提供统一的加密算法及密钥管理，避免每个业务线单独开发一套，同时也与具体的业务进行了解耦
2.【强制】禁止在数据库中明文存储用户敏感信息，如手机号等
说明：对于手机号建议公司搭建统一的手机号查询服务，避免在每个业务线单独存储
3.【强制】禁止开发直接给业务同学导出或者查询涉及到用户敏感信息的数据，如需要需上级领导审批
4.【强制】涉及到导出数据功能的操作，如包含敏感字段都需加密或脱敏
5.【强制】跟数据库交互涉及的敏感数据操作都需有审计日志，必要时要做告警
6.【强制】对连接数据库的 IP 需设置白名单功能，杜绝非法 IP 接入
7.【强制】对重要 sql（如订单信息的查询）的访问频率或次数要做历史趋势监控，及时发现异常行为
8.【推荐】线上连接数据库的用户名、密码建议定期进行更换
二、基础规范
1.【推荐】尽量不在数据库做运算，复杂运算需移到业务应用里完成
2.【推荐】拒绝大 sql 语句、拒绝大事务、拒绝大批量，可转化到业务端完成
说明：大批量操作可能会造成严重的主从延迟，binlog 日志为 row 格式会产生大量的日志
3.【推荐】避免使用存储过程、触发器、函数等，容易造成业务逻辑与 DB 耦合
说明：数据库擅长存储与索引、要解放数据库 CPU，将计算转移到服务层、也具备更好的扩展性
4.【强制】数据表、数据字段必须加入中文注释
说明：后续维护的同学看到后才清楚表是干什么用的
5.【强制】不在数据库中存储图片、文件等大数据
说明：大文件和图片需要储在文件系统
6.【推荐】对于程序连接数据库账号，遵循权限最小原则
7.【推荐】数据库设计时，需要问下自己是否对以后的扩展性进行了考虑
8.【推荐】利用 pt-query-digest 定期分析 slow query log 并进行优化
9.【推荐】使用内网域名而不是 ip 连接数据库
10.【推荐】如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略
11.【推荐】要求所有研发 SQL 关键字全部是小写，每个词只允许有一个空格
三、命名规范
1.【强制】库名、表名、字段名要小写，下划线风格，不超过 32 个字符，必须见名知意，建议使用名词而不是动词，词义与业务、产品线等相关联，禁止拼音英文混用
2.【强制】普通索引命名格式：idx_表名_索引字段名（如果以首个字段名为索引有多个，可以加上第二个字段名，太长可以考虑缩写）；唯一索引命名格式：uk_表名_索引字段名（索引名必须全部小写，长度太长可以利用缩写）；主键索引命名：pk_字段名
3.【强制】库名、表名、字段名禁止使用 MySQL 保留字
4.【强制】临时库表名必须以tmp为前缀，并以日期为后缀
5.【强制】备份库表必须以bak为前缀，并以日期为后缀
6.【推荐】用 HASH 进行散表，表名后缀使用 16 进制数，下标从 0 开始
7.【推荐】按日期时间分表需符合YYYY[MM][DD][HH]格式
8.【推荐】散表如果使用 md5（或者类似的 hash 算法）进行散表，表名后缀使用 16 进制，比如 user_ff
9.【推荐】使用 CRC32 求余（或者类似的算术算法）进行散表，表名后缀使用数字，数字必须从 0 开始并等宽，比如散 100 张表，后缀从 00-99
10.【推荐】使用时间散表，表名后缀必须使用特定格式，比如按日散表 user_20110209、按月散表 user_201102
11.【强制】表达是与否概念的字段，使用 is _ xxx 的方式进行命名
四、库设计规范
1.【推荐】数据库使用 InnoDB 存储引擎
说明：支持事务、行级锁、并发性能更好、CPU 及内存缓存页优化使得资源利用率更高
2.【推荐】数据库和表的字符集统一使用 UTF8
说明：utf8 号称万国码，其无需转码、无乱码风险且节省空间。若是有字段需要存储 emoji 表情之类的，则将表或字段设置成 utf8mb4，utf8mb4 向下兼容 utf8。
3.【推荐】不同业务，使用不同的数据库，避免互相影响
4.【强制】所有线上业务库均必须搭建 MHA 高可用架构，避免单点问题
五、表设计规范
1.【推荐】建表规范示例
1 | CREATE TABLE `student_info` ( |
2.【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制
3.【强制】每个 Innodb 表必须有一个主键
说明：Innodb 是一种索引组织表，其数据存储的逻辑顺序和索引的顺序是相同的。每张表可以有多个索引，但表的存储顺序只能有一种，Innodb 是按照主键索引的顺序来组织表的，因此不要使用更新频繁的列如 UUID、MD5、HASH 和字符串列作为主键，这些列无法保证数据的顺序增长，主键建议使用自增 ID 值。
4.【推荐】单表列数目最好小于 50
5.【强制】禁止使用分区表
说明：分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据
6.【推荐】拆分大字段和访问频率低的字段，分离冷热数据
7.【推荐】采用合适的分库分表策略，例如千库十表、十库百表等（建议表大小控制在 2G）
8.【推荐】单表不超过 50 个 int 字段；不超过 20 个 char 字段，不超过 2 个 text 字段
9.【推荐】表默认设置创建时间戳和更改时间戳字段
10.【推荐】日志类型的表可以考虑按创建时间水平切割，定期归档历史数据
11.【强制】禁止使用 order by rand()
说明：order by rand()会为表增加一个伪列，然后用 rand()函数为每一行数据计算出 rand()值，基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。
12.【参考】可以结合使用 hash、range、lookup table 进行散表
13.【推荐】每张表数据量建议控制在 500w 以下，超过 500w 可以使用历史数据归档或分库分表来实现（500 万行并不是 MySQL 数据库的限制。过大对于修改表结构，备份，恢复都会有很大问题。MySQL 没有对存储有限制，取决于存储设置和文件系统）
14.【强制】禁止在表中建立预留字段
说明：预留字段的命名很难做到见名识义，预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定
六、字段设计规范
1.【强制】必须把字段定义为 NOT NULL 并且提供默认值
说明：NULL 字段很难查询优化，NULL 字段的索引需要额外空间，NULL 字段的复合索引无效
2.【强制】禁止使用 ENUM，可使用 TINYINT 代替
3.【强制】禁止使用 TEXT、BLOB 类型(如果表的记录数在万级以下可以考虑)
4.【强制】必须使用 varchar(20)存储手机号
5.【强制】禁止使用小数存储国币、使用“分”作为单位，这样数据库里就是整数了
6.【强制】用 DECIMAL 代替 FLOAT 和 DOUBLE 存储精确浮点数
7.【推荐】使用 UNSIGNED 存储非负整数
说明：同样的字节数，存储的数值范围更大
8.【推荐】建议使用 INT UNSIGNED 存储 IPV4
说明：用 UNSINGED INT 存储 IP 地址占用 4 字节，CHAR(15)则占用 15 字节。另外，计算机处理整数类型比字符串类型快。使用 INT UNSIGNED 而不是 CHAR(15)来存储 IPV4 地址，通过 MySQL 函数 inet_ntoa 和 inet_aton 来进行转化。IPv6 地址目前没有转化函数，需要使用 DECIMAL 或两个 BIGINT 来存储。例如:
SELECT INET_ATON(‘192.168.172.3’); 3232279555 SELECT INET_NTOA(3232279555); 192.168.172.3
9.【推荐】字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量
10.【推荐】核心表字段数量尽可能地少，有大字段要考虑拆分
11.【推荐】适当考虑一些反范式的表设计，增加冗余字段，减少 JOIN
12.【推荐】资金字段考虑统一*100 处理成整型，避免使用 decimal 浮点类型存储
13.【推荐】使用 VARBINARY 存储大小写敏感的变长字符串或二进制内容
说明：VARBINARY 默认区分大小写，没有字符集概念，速度快
14.【参考】INT 类型固定占用 4 字节存储
说明：INT(4)仅代表显示字符宽度为 4 位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如 INT(3)默认显示 3 位，空格补齐，超出时正常显示，Python、Java 客户端等不具备这个功能
15.【参考】区分使用 DATETIME 和 TIMESTAMP
说明：存储年使用 YEAR 类型、存储日期使用 DATE 类型、存储时间(精确到秒)建议使用 TIMESTAMP 类型。
DATETIME 和 TIMESTAMP 都是精确到秒，优先选择 TIMESTAMP，因为 TIMESTAMP 只有 4 个字节，而 DATETIME8 个字节，同时 TIMESTAMP 具有自动赋值以及⾃自动更新的特性。
补充：如何使用 TIMESTAMP 的自动赋值属性?
自动初始化，而且自动更新：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP 只是自动初始化：column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP 自动更新，初始化的值为 0：column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP 初始化的值为 0：column1 TIMESTAMP DEFAULT 0
16.【推荐】将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据
说明：有利于有效利用缓存，防⽌读入无用的冷数据，较少磁盘 IO，同时保证热数据常驻内存提⾼高缓存命中率
17.【参考】VARCHAR(N)，N 表示的是字符数不是字节数，比如 VARCHAR(255)，可以最大可存储 255 个汉字，需要根据实际的宽度来选择 N
18.【参考】VARCHAR(N)，N 尽可能小，因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节，进行排序和创建临时表一类的内存操作时，会使用 N 的长度申请内存
19.【推荐】VARCHAR(N)，N>5000 时，使用 BLOB 类型
20.【推荐】使用短数据类型，比如取值范围为 0~80 时，使用 TINYINT UNSIGNED
21.【强制】存储状态，性别等，用 TINYINT
22.【强制】所有存储相同数据的列名和列类型必须一致（在多个表中的字段如 user_id，它们类型必须一致）
23.【推荐】优先选择符合存储需要的最小数据类型
24.【推荐】如果存储的字符串长度几乎相等，使用 char 定长字符串类型
七、索引设计规范
1.【推荐】单表索引建议控制在 5 个以内
说明：索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率，所以不是越多越好
2.【强制】禁止在更新十分频繁，区分度不高的属性上建立索引
3.【强制】建立组合索引必须把区分度高的字段放在前面
4.【推荐】对字符串使用索引，如果字符串定义长度超过 128 的，可以考虑前缀索引
5.【强制】表必须有主键，并且是 auto_increment 及 not null 的，根据表的实际情况定义无符号的 tinyint,smallint,int,bigint
6.【强制】禁止更新频繁的列作为主键
7.【强制】禁止字符串列作为主键
8.【强制】禁止 UUID MD5 HASH 这些作为主键(数值太离散了)
9.【推荐】默认使用非空的唯一键作为主键
10.【推荐】主键建议选择自增或发号器
11.【推荐】核心 SQL 优先考虑覆盖索引
12.【参考】避免冗余和重复索引
13.【参考】索引要综合评估数据密度和分布以及考虑查询和更新比例
14.【强制】不在索引列进行数学运算和函数运算
15.【推荐】研发要经常使用 explain，如果发现索引选择性差，必须要学会使用 hint
16.【推荐】能使用唯一索引就要使用唯一索引，提高查询效率
17.【推荐】多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量
18.【强制】索引字段要保证不为 NULL，考虑 default value 进去。NULL 也是占空间，而且 NULL 非常影响索引的查询效率
19.【强制】新建的唯一索引不能和主键重复
20.【推荐】尽量不使用外键、外键用来保护参照完整性，可在业务端实现
说明：避免对父表和子表的操作会相互影响，降低可用性
21.【强制】字符串不应做主键
22.【强制】表必须有无符号 int 型自增主键，对应表中 id 字段
说明：必须得有主键的原因：采用 RBR 模式复制，无主键的表删除，会导致备库夯住 ；使用自增的原因：
数据写入可以提高插入性能，避免 page 分裂，减少表碎片
23.【推荐】对长度过长的 VARCHAR 字段建立索引时，添加 crc32 或者 MD5 Hash 字段，对 Hash 字段建立索引
说明：下面的表增加一列 url_crc32，然后对 url_crc32 建立索引，减少索引字段的长度，提高效率
CREATE TABLE url( … url VARCHAR(255) NOT NULL DEFAULT 0, url_crc32 INT UNSIGNED NOT NULL DEFAULT 0, … index idx_url(url_crc32) ）
24.【推荐】WHERE 条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引
25.【推荐】索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面
26.【推荐】ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面
27.【参考】合理创建联合索引（避免冗余），如(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
28.【推荐】复合索引中的字段数建议不超过 5 个
29.【强制】不在选择性低的列上建立索引，例如”性别”, “状态”， “类型”
30.【推荐】对于单独条件如果走不了索引，可以使用 force –index 强制指定索引
31.【强制】禁止给表中的每一列都建立单独的索引
32.【推荐】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可
八、SQL 使用规范
1.【强制】禁止使用 SELECT *，只获取必要的字段，需要显示说明列属性
说明：按需获取可以减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响。
2.【强制】禁止使用 INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
3.【强制】WHERE 条件中必须使用合适的类型，避免 MySQL 进行隐式类型转化
说明：因为 MySQL 进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不到索引，原因和避免在索引字段中使用函数是类似的，例子 select uid from t_user where phone=15855550101（phone 为 varchat 类型，此时查询中使用数字查询，会导致索引失效）
4.【强制】禁止在 WHERE 条件的属性上使用函数或者表达式
5.【强制】禁止负向查询，以及%开头的模糊查询
6.【强制】应用程序必须捕获 SQL 异常，并有相应处理
7.【推荐】sql 语句尽可能简单、大的 sql 想办法拆成小的 sql 语句
说明：简单的 SQL 容易使用到 MySQL 的 querycache、减少锁表时间特别是 MyISAM、可以使用多核 cpu
8.【推荐】事务要简单，整个事务的时间长度不要太长
9.【强制】避免在数据库中进行数学运算或者函数运算(MySQL 不擅长数学运算和逻辑判断，也容易将业务逻辑和 DB 耦合在一起)
10.【推荐】sql 中使用到 OR 的改写为用 IN() (or 的效率没有 in 的效率高)
11.【参考】SQL 语句中 IN 包含的值不应过多，里面数字的个数建议控制在 1000 个以内
12.【推荐】limit 分页注意效率。Limit 越大，效率越低。可以改写 limit
说明：改写例子：
1）改写方法一
延迟回表写法 select xx,xx from t t1, (select id from t where …. limit 10000,10) t2 where t1.id = t2.id
2）改写方法二
select id from t limit 10000, 10; 应该改为 => select id from t where id > 10000 limit 10;
13.【推荐】尽量使用 union all 替代 union
14.【参考】避免使用大表 JOIN
15.【推荐】对数据的更新要打散后批量更新，不要一次更新太多数据
16.【推荐】使用合理的 SQL 语句减少与数据库的交互次数
17.【参考】注意使用性能分析工具 Sql explain / showprofile / mysqlsla
18.【推荐】能不用 NOT IN 就不用 NOT IN，坑太多了，会把空和 NULL 给查出来
19.【推荐】关于分页查询，程序里建议合理使用分页来提高效率，limit、offset 较大要配合子查询使用
20.【强制】禁止在数据库中跑大查询
21.【强制】禁止单条 SQL 语句同时更新多个表
22.【推荐】统计表中记录数时使用 COUNT(*)，而不是 COUNT(primary_key)和 COUNT(1)
说明：count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行
23.【推荐】INSERT 语句使用 batch 提交（INSERT INTO tableVALUES(),(),()……），values 的个数不应过多
24.【推荐】获取大量数据时，建议分批次获取数据，每次获取数据少于 2000 条，结果集应小于 1M
25.【推荐】在做开发时建议使用数据库框架(如 mybatis) 或 prepared statement，可以提升性能并避免 SQL 注入
26.【强制】禁止跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低风险）
27.【推荐】尽量避免使用子查询，可以把子查询优化为 join 操作（子查询的结果集无法使用索引，子查询会产生临时表操作，如果子查询数据量大会影响效率，消耗过多的 CPU 及 IO 资源）
28.【强制】超过三个表禁止 join。（需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。）
29.【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好
30.【推荐】尽量不要使用物理删除（即直接删除，如果要删除的话提前做好备份），而是使用逻辑删除，使用字段 delete_flag 做逻辑删除，类型为 tinyint，0 表示未删除，1 表示已删除
31.【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句
32.【强制】程序连接不同的数据库要使用不同的账号
33.【推荐】使用 ISNULL()来判断是否为 NULL 值
九、行为规范
1.【强制】禁止使用应用程序配置文件内的帐号手工访问线上数据库
2.【强制】禁止非 DBA 对线上数据库进行写操作，修改线上数据需要提交工单，由 DBA 执行，提交的 SQL 语句必须经过测试
3.【强制】禁止在线上做数据库压力测试
4.【强制】禁止从测试、开发环境直连线上数据库
5.【强制】禁止在主库进行后台统计操作，避免影响业务，可以在离线从库上执行后台统计
十、流程规范
1.【强制】所有的建表操作需要提前告知该表涉及的查询 sql
2.【强制】所有的建表需要确定建立哪些索引后才可以建表上线
3.【强制】所有的改表结构、加索引操作都需要将涉及到所改表的查询 sql 发出来告知 DBA 等相关人员
4.【强制】在建新表加字段之前，要求至少要提前 3 天邮件出来，给 dba 们评估、优化和审核的时间
5.【强制】批量导入、导出数据需要 DBA 进行审查，并在执行过程中观察服务
6.【强制】禁止有 super 权限的应用程序账号存在
7.【强制】推广活动或上线新功能必须提前通知 DBA 进行流量评估
8.【强制】不在业务高峰期批量更新、查询数据库
9.【强制】隔离线上线下环境（开发测试程序禁止访问线上数据库）
10.【强制】在对大表做表结构变更时，如修改字段属性会造成锁表，并会造成从库延迟，从而影响线上业务，必须在凌晨后业务低峰期执行，另统一用工具 pt-online-schema-change 避免锁表且降低延迟执行时间
11.【强制】核心业务数据库变更需在凌晨执行
12.【推荐】汇总库开启 Audit 审计日志功能，出现问题时方可追溯
13.【强制】给业务方开权限时，密码要用 MD5 加密，至少 16 位。权限如没有特殊要求，均为 select 查询权限，并做库表级限制
14.【推荐】如果出现业务部门人为误操作导致数据丢失，需要恢复数据，请在第一时间通知 DBA，并提供准确时间，误操作语句等重要线索。
15.【强制】批量更新数据，如 update,delete 操作，需要 DBA 进行审查，并在执行过程中观察服务
16.【强制】业务部门程序出现 bug 等影响数据库服务的问题，请及时通知 DBA 便于维护服务稳定
17.【强制】线上数据库的变更操作必须提供对应的回滚方案
18.【强制】批量清洗数据，需要开发和 DBA 共同进行审查，应避开业务高峰期时段执行，并在执行过程中观察服务状态
19.【强制】数据订正如删除和修改记录时，要先 select ，确认无误才能执行更新语句，避免出现误删除
