MySQL 开发规范

排版规范(修复中)

TODO:
此文档遵循 中文排版指南 规范,并在此之上遵守以下约定:

  • 英文的左右保持一个空白,避免中英文字黏在一起;
  • 使用全角标点符号;
  • 严格遵循 Markdown 语法;
  • 原文中的双引号(” “)请代换成中文的引号(「」符号怎么打出来见 这里);
  • 加亮」和「加粗」和「链接」都需要在左右保持一个空格。

一 基础规范

1. 使用InnoDB存储引擎

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

2. InnoDB表必须有主键列,使用auto_increment

主键递增,数据行写入可以提高插入性能,可以避免 page 分裂,减少表碎片,提升空间和内存的使用
主键要选择较短的数据类型, Innodb 引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率
无主键的表删除,在 row 模式的主从架构,会导致备库卡住
使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;
该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。

3. 数据库字符集使用 UTF8 ,校对字符集使用 utf8_general_ci

兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效

4. 所有表、字段都尽量添加注释

进行数据字典的维护

5. 库名、表名、字段名使用小写字母,禁止超过32个字符,须见名知意

库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符

6. 非唯一索引以 「idx_字段1_字段2」 命名,唯一索引必须以 「uniq_字段1_字段2」 命名

二 查询规范

1. SQL语句尽可能简单,拆分复杂SQL为多个小SQL,避免大事务

简单的SQL容易使⽤用到MySQL的 QUERY CACHE
减少锁表时间,特别是MyISAM;
可以使用多核 CPU

2. 不要使用SELECT * ,查询具体要用到的字段
3. 禁止like '%*' 开头的模糊查询做where条件

无法使用索引,进行全表遍历,非常耗时,性能极低;
如必须,可使用索引覆盖挽救一部分性能

4. 避免使用负向查询条件, NOT , != , <> , !< , !> , NOT IN , NOT LIKE

无法使用索引

5. Where条件里不要对列使用函数或者表达式

无法使用索引

6. 能确定返回结果只有一条时,使用limit 1

(LIMIT分页注意效率,LIMIT越大,效率越低)

7. 少用子查询,改用JOIN

(子查询要在内存里建临时表)

8. 多表JOIN的字段,区分度最大的字段放在前面
9. EXISTIN 的使用场景

exists是对外表做 loop 循环,每次 loop 循环再对内表(子查询)进行查询,那么因为对内表的查询使用的索引(内表效率高,故可用大表),而外表有多大都需要遍历,不可避免(尽量用小表),故内表大的使用 exists
in 是把外表和内表做 hash 连接,先查询内表,再把内表结果与外表匹配,对外表使用索引(外表效率高,可用大表),而内表多大都需要查询,不可避免,故外表大的使用 in
如果用 not in ,则是内外表都全表扫描,无索引,效率低,可考虑使用 not exists ,也可使用 A left join B on A.id=B.id where B.id is null 进行优化

10. Where字句中同一个表的不同字段组合建议小于5组

该考虑分表了

11. 禁止单条语句同时更新多个表
12. 事务要尽量简单,整个事务的时间长度不要太长

三 表设计规范

1. 用DECIMAL代替FLOATDOUBLE存储精确浮点数

(精确数据)

2. 使用TINYINT代替ENUM类型

ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较高;ENUM列值如果含有数字类型,可能会引起默认值混淆

3. 尽可能不使用TEXTBLOB类型

(该数据类型不能设置默认值、不便于排序、不便于建立索引)

4. 同一意义的字段设计定义必须相同

(便于联表查询)

5. 所有字段均定义为NOT NULL

(避免使用NULL字段,NULL字段很难查询优化,NULL字段的索引需要额外空间,NULL字段的复合索引无效)

6. 表必须有主键,不使用更新频繁的列做主键、尽量不使用字符串列做主键,尽量使用非空的唯一自增键做主键

四 索引设计规范

1. 单表索引数量不超过10个
2. 单个字段不要超过两个索引
3. 新建的唯一索引必须不能和主键重复
4. 避免冗余和重复索引
5. 尽量不要在频繁更新的列上建立索引
6. 不在低基数列上建立索引,例如状态、类型等
7. 不在索引列进行数学运算和函数运算

参与了运算的列不会引用索引

8. 复合索引须符合最左前缀的特点建立索引

MySQL 使用复合索引时从左向右匹配

9. 重要的SQL中where条件里的字段必须被索引
10. Where条件里的字段顺序与索引顺序无关

MySQL 内置优化器会自动调整,但是不建议,违背规范问题

11. 索引选择性 = Cardinality / Total Rows,即基数 ÷ 数据行数,值越接近1说明使用索引的过滤效果越好
12. 建立索引时,务必先explain,查看索引使用情况
本作品采用《CC 协议》,转载必须注明作者和本文链接

Thurs

讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!