MySQL数据库开发的三十六条军规

摘录自：http://www.infoq.com/cn/presentations/wsz-mysql-development
原作者：@吴诗展

一、核心军规
1、尽量不在数据库做运算

2、控制单表数据量

3、保持表身段苗条

4、平衡范式与冗余

5、拒绝3B(Big SQL && Big Transaction && Big Batch)

---

二、字段类军规
6、用好数值字段类型

7、将字符转化为数字
数字型VS字符串型索引
举例：用无符号INT存储IP，而非CHAR(15)

8、优先使用ENUM或SET
ENUM占用1字节，转为数值运算
SET视节点定，最多占用8字节
比较时需要加‘ 单引号(即使是数值)

9、避免使用NULL字段
很难进行查询优化
NULL列加索引，需要额外空间
含NULL复合索引无效
举例：
a char(32) DEFAULT NULL
b int(10) NOT NULL
c int(10) NOT NULL DEFAULT 0

10、少用并拆分TEXT/BLOB
TEXT类型处理性能远低于VARCHAR
强制生成硬盘临时表
浪费更多空间
VARCHAR(65535)==>64K(注意UTF-8)
尽量不用TEXT/BLOB数据类型
若必须使用则拆分到单独的表

11、不在数据库里存图片

---

三、索引类军规
12、谨慎合理添加索引
能不加的索引尽量不加
综合评估数据密度和数据分布
最好不超过字段数20%
结合核心SQL优先考虑覆盖索引

13、字符字段必须建前缀索引
pinyin varchar(100) DEFAULT NULL COMMENT ‘小区拼音’, KEY idx_pinyin (pinyin(8)),
) ENGINE=InnoDB

14、不在索引列做运算
不在索引列进行数学运算或函数运算
无法使用索引
导致全表扫描

15、自增列或全局ID做INNODB主键

16、尽量不用外键

---

四、SQL类军规
17、SQL语句尽可能简单
拒绝大SQL，拆解成多条简单SQL

18、保持事务(连接)短小

19、尽可能避免使用SP/TRIG/FUNC

20、尽量不用SELECT *

21、改写OR为IN()
同一字段，将or改写为in() • OR效率:O(n)
IN效率：O(Log n)
当n很大时，OR会慢很多
注意控制IN的个数，建议n小于200

21、改写OR为UNION

22、避免负向查询和% 前缀模糊查询
负向查询：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、 NOT LIKE等
%前缀查：B+ Tree，使用不了索引，导致全表扫描

23、减少COUNT(*)
结论：
COUNT(*)=count(1)
COUNT(0)=count(1)
COUNT(1)=count(100)
COUNT(*)!=count(col)

24、LIMIT高效分页
推荐：SELECT * FROM table WHERE id>=123 LIMIT 11

25、用UNION ALL而非UNION
若无需对结果进行去重，则用UNION ALL

26、分解联接保证高并发

27、GROUP BY去除排序
无需排序：ORDER BY NULL
特定排序：GROUP BY DESC/ASC

28、同数据类型的列值比较
原则：数字对数字，字符对字符

29、LOAD DATA导数据

30、打散大批量更新

31、Know Every SQL

---

五、约定类军规
32、隔离线上线下

33、禁止未经DBA确认的子查询

34、永远不在程序端显式加锁

35、统一字符集为UTF-8
统一字符集：UTF8
校对规则：utf8_general_ci
乱码：SET NAMES UTF8

36、统一命名规范
库表等名称统一用小写
索引命名默认为“idx_字段名”
库名用缩写，尽量在2~7个字母
注意避免用保留字命名