MySQL 数据库规范

MySQL 数据库规范

一、基础规范

1. 必须使用 InnoDB 存储引擎

支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

2. 必须使用 UTF8MB4 字符集

万国码、无需转码、无乱码风险、支持emoji表情

3. 数据表、数据字段必须加入中文注释

n 年后，谁知道 tb1,tbl2,col1,col2 表或字段是干嘛的？你知道吗？我不知道。好好写注释。

4. 禁止使用存储过程(procedure)、视图(view)、触发器(trigger)、事件(event)

高并发大数据的互联网业务，架构设计的思路是：解放数据库 CPU，将计算转移至服务层，数据库只负责做数据查询和存储。

并发量大的情况下，这些功能很有可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性和可伸缩性，能够轻易实现 增加机器就增加性能、数据库擅长存储和索引，CPU 计算等复杂操作请上移至服务层。

一个准则是 MySQL 的查询语句尽量简单易懂高性能，理想情况下我们甚至不需要任何的 join 等复杂语句，所有这类复杂语句尽量上移至服务层进行操作，你的程序代码更适合做这些事情。

5. 禁止存储大文件或者大照片

不要让数据库做其不擅长的事情，大文件和大照片存储在文件系统上，数据库里只存储 URL、URI 连接地址。

二、命名规范

1. 库名、表名、字段名：小写、下划线风格，不超过32个字符

必须见名知意，禁止使用中文，禁止使用大写字母，禁止使用中英文混用。

2. 非唯一索引名 idx_xxx,唯一索引名 uniq_xxx

命名索引以 idx_xxx ，xxx 以字段名或者多个字段时简写 例如: 单列索引：user_id 字段命名为 idx_userid。联合索引：user_id identity ， 以 idx_userid_identity 命名。

索引字段必须为非空字段

三、表设计规范

1. 单实例表数目尽量小于500张表

message 消息信息这种存储除外

2. 单表列数必须小于30个

3. 表必须有主键，例如自增主键。

主键递增，数据行写入可以提高插入性能，可以避免 page 分裂，减少表碎片提升空间和内存使用。

主键要选较短的数据类型，InnoDB 引擎普通索引会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率。主键尽量用 id 表示（无任何意义最好），禁止将主键进行变更（InnoDB 表变更成本太大）。

无主键的表删除，在 row 模式的主从架构下，会导致备库夯住。

4. 禁止使用外键，如果有外键完整性约束，需要应用程序控制

外键会导致表于表之间耦合，update 与 delete 操作都会涉及相关联的表，十分影响 SQL 性能，甚至会造成死锁，高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先为主。

5. 创建表

1. 必须字段，id，create_time, update_time
2. 字段长度，2 的指数，如 varchar(10) 建议为 varchar(16)
3. 非空字段，必须设置默认值
4. 字段必须有 comment
5. 尽量避免使用大字段，如 text、blob 等
6. 索引变更原则：永远先添加索引，再执行删除索引。（防止索引删除产生全表扫描，导致线上存在问题）

6. 修改表禁止使用 after 或 after 字段，在某列后面。

四、字段设计规范

1. 可选使用 TEXT/BLOB 类型，禁止使用 JSON 数据类型

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

2. 禁止使用小数存储货币

使用整数吧，小数容易导致钱对不上，小数有精度问题。比如：可以将钱数放大100倍或者1000倍，使用整数类型存储即可。

3. 必须使用 varchar(20) 存储手机号

涉及到区号或者国家代号，可能出现+ - ()

手机号不会去做数学运算

varchar可以支持模糊查询，例如: like “135%”

4. 禁止使用 ENUM 类型,可以使用 TINYINT 类型代替

增加新的 ENUM 值要做 DDL 操作 ，ENUM 的内部实际存储的就是整数，而不是定义的字符串

五、索引设计规范

1. 单表索引建议控制在 5 个以内

2. 单索引字段数不允许超过 5 个

字段超过5个时，实际已经起不到有效过滤数据的作用了

3. 禁止在更新十分频繁、区分度不高的属性上建立索引

更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能

性别这种区分度不大的属性，建立索引没有什么意义，不能有效过滤数据，性能与全表扫描类似

4. 建立组合索引，必须把区分度高的字段放在前面

能够更加有效的过滤数据

六、SQL 使用规范

*1. 禁止使用SELECT ,只获取必要的字段，需要显示说明列属性

读取不需要的列会增加CPU、IO、NET消耗

不能有效的利用覆盖索引

使用SELECT * 容易在增加或者删除字段后出现程序BUG

2. 禁止使用INSERT INTO t_xxx VALUES (xxx)，必须显示指定插入的列属性

容易在增加或者删除字段后出现程序 BUG

3. 禁止使用属性隐式转换

SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中 phone 索引，因为 phone 是 varchar 字段，而SQL phone 字段没有加’'引号，所以会认为是 int 类型，从而导致全表扫描。

4. 禁止在 WHERE 条件中的属性上使用函数或者表达式

SELECT uid FROM t_user WHERE from_unixtime(day)>‘2017-02-15’ 会导致全表扫描

正确写法：SELECT uid FROM t_user WHERE day >= unix_timestamp(‘2017-02-15’ 00:00:00’)

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

负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

%开头的模糊查询，会导致全表扫描

6. 禁止大表使用JOIN查询，禁止大表使用子查询

会产生临时表，消耗较多内存与CPU，极度影响数据库性能

7. 禁止使用 OR 条件，必须改为 IN 查询

旧版本MySQL的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

8.应用程序必须捕获 SQL 异常，并有相应处理