MySQL · 开发规范

发表于 | 更新于 | 分类于 | 阅读次数：

库表规范

  • 存储引擎必须使用 InnoDB

  • 库名、表名、字段名必须用小写，采用下划线分隔

  • 表字符集默认使用 utf8，必要时候使用 utf8mb4

    1
    2
    3
    解读：
    ① 通用，无乱码风险，汉字3字节，英文1字节
    ② utf8mb4是utf8的超集，存储4字节，例如：表情符号时使用它

  • 禁止使用存储过程、视图、触发器、事件

    1
    2
    3
    解读：
    ① 对数据库性能影响较大，能让站点层和服务层干的事情，不要交到数据库层
    ② 难以调试和扩展，更没有移植性

  • 禁止在数据库中存储大文件

    1
    2
    解读：
    ① 例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

  • 禁止使用 text、blob 类型

    1
    2
    解读：
    ① 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

  • 根据业务区分使用 tinyint（1 byte）、smallint（2 byte）、int（4 byte）、bigint（8 byte）

    1
    2
    3
    解读：
    ① 合适的字符存储长度，不但节约数据库表空间，节约索引存储，更重要的是提升检索速度
    ② 整数类型指定显示宽度，例如：int(M)对大多数应用是没有意义的，他不会限制值的合法范围，对于存储和计算来说，int(1)和int(20)是相同的
    对象 年龄区间 类型 范围 字节
    人 150岁之内 unsigned tinyint 0～255 1
    龟 数百岁 unsigned smallint 0～65535 2
    恐龙化石 数千万岁 unsigned int 0～4294967295 4
    太阳 约50亿年 unsigned bigint 0～2^64-1 8

  • 根据业务区分使用 char、varchar

    1
    2
    3
    解读：
    ① 字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
    ② 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，节省磁盘空间

  • 根据业务区分使用 datetime、timestamp

    1
    2
    解读：
    ① 前者占用8个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

  • 小数类型使用 decimal，禁止使用 float 和 double

    1
    2
    3
    解读：
    ① float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果
    ② decimal(M,D)，如果M>D，占用字节M+2，否则为D+2，M是数字最大位数，D是小数点右侧数字个数

  • 表必须有主键，推荐使用 unsigned 整数为主键

    1
    2
    解读：
    ① 删除无主键的表，如果是row模式的主从架构，从库会挂住

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

  • 表必备三字段：id，gmt_create，gmt_modified

    1
    2
    3
    解读：
    ① id必为主键，类型为unsigned bigint，单表时自增，步长为1
    ② gmt_create, gmt_modified类型均为datetime，前者现在时表示主动创建，后者过去分词表示被动更新

  • 字段定义必须为 NOT NULL ，并设默认值

    1
    2
    3
    4
    解读：
    ① NULL列使用索引，会影响优化器选择执行计划，很难优化
    ② NULL只能采用IS NULL或者IS NOT NULL，而在使用=、!=、in、not in时有大坑
    ③ 数值默认值设置为0，字符串默认值设置为''

  • 使用 unsigned int 存储 IPv4，不要用 char(15)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    解读：
    ① 节省存储空间，char(15)占据16个字节，unsigned int只需要4个字节
    ② int型在逻辑运算上，比char速度快
    ③ ip和数字转换，函数算法：inet_aton()和inet_ntoa()

    mysql> select inet_aton('192.168.1.200');
    +----------------------------+
    | inet_aton('192.168.1.200') |
    +----------------------------+
    |                 3232235976 |
    +----------------------------+
    1 row in set (0.00 sec)

    mysql> select inet_ntoa('3232235976');
    +-------------------------+
    | inet_ntoa('3232235976') |
    +-------------------------+
    | 192.168.1.200           |
    +-------------------------+
    1 row in set (0.00 sec)

  • 使用 varchar(20) 存储手机号，不要使用整数

    1
    2
    3
    4
    解读：
    ① 牵扯到国家代号，可能出现+/-/()等字符，例如+86
    ② 手机号不会用来做数学运算
    ③ varchar可以模糊查询，例如：like '138%'

  • 控制单表数据量，单表数据控制在千万级

  • 测试、开发、线上数据库环境必须隔离

  • 数据表、数据字段要有中文注释

SQL规范

  • 禁止使用 select *，只获取必要字段

    1
    2
    3
    4
    解读：
    ① select*会增加cpu/io/内存/带宽的消耗
    ② 指定字段能有效利用索引覆盖
    ③ 指定字段查询，在表结构变更时，能保证对应用程序无影响

  • 禁止在 where 条件列使用函数或者表达式

    1
    2
    解读：
    ① 导致不能命中索引，全表扫描

  • 禁止负向查询，以及 % 开头的模糊查询

    1
    2
    3
    解读：
    ① 负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
    ② %开头的模糊查询，会导致全表扫描

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

  • count(*) 会统计值为 null 的行，而 count(列名) 不会统计此列为 null 值的行

  • 数据订正（delete、update 操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句

  • 推荐使用 truncate，速度比 delete 快，且使用的系统和事务日志资源少

    1
    2
    3
    解读：
    ① truncate table在功能上与不带where子句的delete语句相同
    ② truncate无事务且不触发trigger，有可能造成事故

  • 避免 in 操作，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内

  • 推荐 or 改写为 union

索引规范

  • 唯一索引使用 uniq_[字段名] 来命名，非唯一索引使用 idx_[字段名] 来命名

  • 单张表索引数量建议控制在 5 个以内

    1
    2
    3
    4
    解读：
    ① 高并发业务，太多索引会影响写性能
    ② 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
    ③ 异常复杂的查询需求，可以选择ES等更为适合的方式存储

  • 超过三个表禁止 join，需要 join 的字段，数据类型必须绝对一致，多表关联查询时，确保被关联的字段需要有索引

    1
    2
    解读：
    ① 踩过因为join字段类型不一致，而导致全表扫描的坑

  • 禁止在更新十分频繁，区分度不高的字段建立索引

    1
    2
    3
    4
    解读：
    ① 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
    ② 区分度：count(distinct left(列名, 索引长度))/count(*)
    ③ 性别不适合建索引

  • 理解组合索引最左前缀原则，避免重复建索引，如果建了 (a,b,c)，相当于建了 (a)，(a,b)，(a,b,c)

    1
    2
    3
    解读：
    ① 示例：where a=? and b=? order by c; 索引：a_b_c
    ② 存在非等号和等号混合判断条件时，在建索引时把等号条件的列前置。如：where a>? and b=? 那么即使a的区分度更高，也必须把b放在索引的最前列

  • 避免因字段类型不同造成的隐式转换，导致索引失效

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

    1
    2
    3
    4
    解读：
    ① consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据
    ② ref指的是使用普通的索引（normal index）
    ③ range对索引进行范围检索

参考

《58到家 · MySQL军规》

《阿里巴巴 · Java开发手册》

​