Mysql规范

SQL规范(建议)

规范说明：

规范分为 4个级别（强制、建议、慎用、禁止）：

• 强制 – 必须这样做
• 建议 – 这样做到更好，如果不这样做，可以和leader商榷
• 慎用 – 经过全面考虑后，leader知晓后可以使用
• 禁止 – 不允许这样做

规范示例

1. 【强制】a，b。---- 表示 a 和 b 均为强制级别
2. 【强制】c，【建议】d，【慎用】e，【禁止】f。---- 表示 c 为强制，d 为建议，e 为慎用，
   f 为禁止

---

基础规范

1、【强制】表必须使用 UTF8 或 UTF8MB4 编码

​ uft8 是万国码，无需转码，五乱码风险；uft8mb4 是 utf8 的超集，比 utf8示更多字符。utf8mb4 包括了 emoji 表情、很多不常用的汉字和新增的 unicode 字符等。

​ MySQL5.5 之前，utf8 支持 1-3 字节。而最新的 uft-8 规范使用 1-4 字节，所以新增了
utf8mb4 支持最新的 utf-8 规范。

2、【强制】数据库和数据表每一个字段必须要有注释，且有更改就要更新

​ 加注释并及时更新是必要的，有利于共同开发和对老旧代码进行维护。

3、【禁止】禁止在表中存放文件或者图片

​ 小规模文件存储用操作系统的文件系统更好，大规模文件存储用分布式存储系统更好，mysql 只存储路径即可。

4、【强制】强制使用 innodb存储引擎

​ innodb 支持事务和支持行级锁，并且主键默认是聚集索引，拥有完善的崩溃恢复机制（redolog/undolog）

---

表规范

1、【强制】表、字段命名为小写、数字和下划线，必须要见名知意。多个单词之间用下划线分割。

​ 统一格式，增强可读性

2、【建议】单个表的字段不超过 50 个，超过 50 个要建拓展表。

​ MySQL 的数据是行存储，取一条数据一定要取出整行，字段太多影响性能。对存储空间和磁盘 IO 的读写有影。

3、【建议】关联性不强的字段要建拓展表，不要加在主表中。

4、【禁止】表禁止设置外键，由程序控制外键约束。

​ 外键会导致表与表之间的耦合，在 update 和 delete 操作都会涉及相关联的表十分影响sql 的性能，甚至会造成死锁。高并发情况下使用外键容易造成数据库性能下降，而实际上高并发情况下以数据库性能为优先。外键所需要的数据逻辑完全可以加事务来解决。

5、【强制】表必须要有主键，【建议】每张表都有一个自增主键 id。

​ 自增主键 id 时，表存储每一行数据会按照主键顺序组织数据存储（聚集索引）。这样可以避免记录分裂，减少表碎片，提升空间和内存的使用。聚集索引的读写性能优于非聚集索引。

6、【强制】必须要把字段设置为 not null 并提供 default 值。

​ null 的列使索引/索引统计/值比较都更加复杂，对 MySQL 来说更难优化。

​ null 这种类型 MySQL 内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。

​ null 值需要更多的存储空间，无论是表还是索引中，每行 null 的列都需要额外的空间来标识。对 null 处理的时候，nulll 既不是数字，也不是字符。所以使用时只能采用 is null 或 is not null，而不能采用<、>、=、!=、in 和 not in 等这些操作符号。如：where name != “zhangsan”，如果存在 name为 null 值的记录，查询结果就不会包含 name 为 null 值的记录。

7、【禁止】禁止使用 enum 字段，使用 tinyint 代替。

​ 增加新的 enum 值要做 DDL 操作，不利于类型的扩展，enum 的内部实际存储就是整数。

8、【慎用】慎用 text 和 longtext。

​ 大字段需要更多的磁盘，内存空间和网络消耗，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。

9、【强制】任何字段如果为非负数，必须使用 unsigned。

10、【建议】表达是与否概念的字段，建议使用 is_xxx 的方式命名。

11、【建议】表字段名不要使用 mysql 保留字。

​ 保留字做字段，需要进行加上反引号的特殊处理，不加会引起报错。

12、【建议】建表字段必须要考虑字段范围。

​ 例如：人的年龄要有 tinyint，乌龟的年龄要用smallint，地球的年龄要用 int。

13、【建议】表的命名最好是加上“业务名称_表”，避免与其它业务表关联时有混淆。

14、【强制】存储手机号使用 varchar(20)。

---

索引规范

1、【强制】单表的索引个数强制不超过 15 个，【建议】建议不超过 10 个。（不包括主键索引）。

​ 增加索引后增加了查询速度，但是会增加插入、更新和删除操作的处理时间；索引过多会还会占用更多的磁盘空间；索引多少要从整体来权衡。

2、【强制】组合索引涉及的字段不超过 5 个,组合索引必须要把区分度高的字段放在前⾯（从左）。

​ 组合索引的字段超过 5 个时，实际已经起不到有效过滤数据的作用了。字段区分度高能够更加有效的过滤数据。

3、【禁止】更新频繁字段禁止加索引，区分度不高的字段禁止加索引。

​ 更新会变更 B+树，更新频繁的字段建立索引会大大降低数据库性能。例如：“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

4、【建议】唯一索引名为 uniq字段名；普通索引名则为 idx_字段名。

---

sql规范

1、【慎用】慎用在 sql 中使用计算函数，例如：mod()，sin()，log()，pow()等。

​ 计算函数会占用更多的资源，为什么不放到程序里计算呢。如果 where 在字段上执行计算函数，会执行全表扫描。

2、【强制】写 sql 前必须要看索引，explain 确认 sql 性能。

​ 良好的写 sql 习惯，提前知晓 sql 的风险。

3、【强制】select 请指定查询的字段，【慎用】* 。

​ 读取不需要的列会增加 CPU、IO、NET 消耗。不能有效的利用覆盖索引。使用 SELECT * 容易在增加和删除字段、修改字段名后出现程序 BUG。

4、【建议】select 都加上 limit。

5、【强制】注意字段的属性转换。

​ 例如：字段 mobile 为 varchar 时，要使用 mobile=‘123123123’，不要使用 mobile=123123123。

​ 属性转换可能会导致无法利用索引。

6、【强制】存储手机号使用 varchar(20)。

​ 涉及到区号或者国家代号，可能出现±()；varchar 可以支持模糊查询，例如：like“138%”。

7、【禁止】禁止使用 insert into table values(xxx, xxx)，必须要指明字段。

8、【慎用】慎用在 where 条件上使用函数或表达式。

​ 例如：select uid from user where from_unixtime(day)>=‘2017-01-01’;应该写成：select uid from user where day>=unix_timestamp(‘2017-01-01’)。SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2017-02-15’ 会导致全表扫描，且这种情况无法利用索引。

9、【慎用】修改数据请慎用负向查询。负向查询：not、!=、<>、!<、!>、not in、not like等。

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

10、【禁止】禁止%开头的模糊查询。【建议】如果有%开头的模糊查询的需求，请使用搜索引擎查询。

​ %开头的模糊查询，会导致全表扫描，用不到任何索引。

11、【慎用】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，【强制】控制在 2000 个之内。【建议】in 的字段建议是索引，【建议】且只用于查询。

12、【强制】order by 字段会命中组合索引。

​ 例如 where a= 1 and b=1 order by c desc，命中索引 a_b_c。注意索引的有序性。

​ 充分利用组合索引的最左原则

13、【建议】当使用单个字段排序进行翻页时，请先获取上个翻页的排序字段 a1 的数值 value1，然后使用 select …where a1> value1limit10 的方式获得数据。

​ 顺序翻页时，把所有数据取出来以后 offset 进行翻页，效率低下；而 where + limit则是指定某页数据，效率更高。（不包括跨页）

14、【建议】涉及到金钱类的存储，单位最小到分或分的后两位，建议存储为 bigint 或 int 类型，【禁止】禁用 float 和 double。

​ 防止计算、判断时，带来的精度问题。

15、【强制】每张表都必须有两个字段：创建时间和更新时间；当更新数据的时候，必须将更新时间置为当前时间。

​ 例如：创建时间为 created_at，更新时间为 updated_at。

16、【建议】transactional 事务不要滥用。使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

17、【强制】update 涉及的 where 条件如果搜索的数据行过大，必须先 select 获取主键 ids 后，再执行 update xxx whereid in (ids)。

​ 直接 update+where 会在主库进行查询和更新，而先 select 则是在从库进行查询，后update 则在主库进行更新。

18、【强制】对于 where 条件，explain 查询涉及的数据量超过 10000 行，则需要进行优化。

​ explain 中的 rows 是 mysql 认为必须要逐行去检查和判断的记录行数，如果这个 rows偏大会影响查询性能（limit 除外）。

19、【禁止】禁止 delete，不做物理删除；如果要 delete，必须上报到 leader。

​ 物理删除—AI 更新索引会有问题；不做物理删除可以保留历史信息，查问题的时候会用到。

20、【禁止】禁止使用存储过程、视图、触发器、event。

​ 视图等均属于包含逻辑的 sql，而这部分逻辑本身要耗费 mysql 资源，调试起来也很痛苦，还不如直接让代码处理。

21、【禁止】禁止大表（即大于 100W 数据）使用 join 查询，禁止大表使用子查询；不管表的大小，超过三个表均禁止 join；【强制】需要 join 的字段，数据类型保持绝对一致；【建议】多表关联查询时，保证被关联的字段需要有索引。

​ join 会产生临时表，消耗较多内存与 CPU，执行时间也会比较长，极大影响数据库性能。当涉及 join 的业务访问量突增，则会造成 sql 堆积等问题，影响其他业务。

22、【建议】建议存储时间的字段使用 datetime。

​ datetime 比 int 具有更好的可读性，比 timestamp 更大的范围。