MySQL数据库开发的 36 条军规

一、核心军规（5个）

• 尽量不在数据库做运算，cpu可以计算的事情务必移至业务层。
• 控制行、列数量：控制单张表的数据量为500W条，超出可做分表；单张表的字段个数不超过50个，多了拆表。
• 控制表数量：单库表数据量不超过300张。
• 三大范式没有绝对的要使用，效率优先时可适当牺牲范式。
• 拒绝大SQL、拒绝大事务、拒绝大批量事务。

二、字段类规约（6个）

• 用好数值类型（用合适的字段类型节约空间）
• 字符转化为数字（能转化的最好转化，同样节约空间、提高查询性能）

例如一个字段的值只有0和1，这个时候要将该字段设计成 int(1) 而不是 char(1)，这样在使用该字段作为条件进行查询的时候 where xxx=1 比 where xxx='1' 效率高。

• 优先使用Enum或者Set类型

`sex` enum('F','M') COMMENT '性别'
`c1` enum('0','1','2','3') COMMENT '职介审核'

• 避免字段的值为NULL（NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效）

例如要设计为  `c` int(10) NOT NULL DEFAULT 0，而不是 `c` int(10) NOT NULL;

• 少用text/blob类型（尽量使用varchar代替text字段），需要的时候请拆表
• 不在数据库存图片，请存图片路径，然后图片文件存在项目文件夹下

三、索引类规约（5个）

• 合理使用索引（改善查询，减慢更新，索引一定不是越多越好）

例如不要给 “性别” 创建索引

• 字符字段必须建前缀索引

`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音', 
KEY `idx_pinyin` (`pinyin`(8)),

• 不在索引做列运算

如：WHERE to_days(current_date) – to_days(date_col) <= 10   
改为：WHERE date_col >= DATE_SUB('2011-10- 22',INTERVAL 10 DAY);

• Innodb主键推荐使用自增列（主键建立聚簇索引，主键不应该被修改，字符串不应该做主键）

例如：用独立于业务的AUTO_INCREMENT

• 不用外键（由程序保证约束）

四、SQL类军规（16个）

• SQL语句尽可能简单（一条SQL只能在一个cpu运算，大语句拆小语句，减少锁时间，一条大SQL可以堵死整个库）
• 简单的事务（事务时间尽可能短，坏的代码案例：上传图片事务）
• 避免使用trigger/func（触发器、函数不用，由客户端程序取而代之）
• 不用select *（消耗cpu、io、内存、带宽，这种程序不具有扩展性)
• or改写为in

例如：where  a=1 or a=2  改为 where  a in(1,2)

• or改写为union

针对不同字段 where a=1 or b=1 
改：select 1 from a where a=1  union select 1 from a where b=1

• 避免负向%查询

例如将 where a like %北京% 改为 where a like '北京%'

• count函数不要使用在字段值可为null的字段上面
• 减少count(*)
• limit高效分页（limit越大，效率越低）

例如将 limit 10000,10 改为 where id >xxx limit 11

• 使用union all替代union（union有去重开销）
• 发生高并发时，数据库少用两个表以上的join
• 使用group by 去除排序加快效率

例如：group by name 默认是asc排序
改：group by name order by null 提高查询效率

• 请使用同类型比较（避免转换导致索引丢失）

例如：where 双精度=双精度  数字=数字  字符=字符

• 使用 LOAD DATA 导入命令比 insert 速度快20倍左右（适合导入大量测试数据） 
• 打散大批量更新（例如：在凌晨空闲时期更新执行）

五、约定类军规（4个）

• 隔离线上线下（开发用dev库，测试用test库，生产用production库） 
• 不在程序端加锁，即外部锁，外部锁不可控，会导致高并发会炸，极难调试和排查
• 统一字符集为utf8_general_ci
• 统一命名规范，库表名一律小写，索引前缀用 idx_ 库名 用缩写（2-7字符），不使用系统关键字保留字命名