MySQL数据库开发的 36 条军规
标签:数据库
核心军规（5个）
- 尽量不在数据库做运算。
- 控制单表数量：行不超过500W条，超出分表；列不超过50个，超过拆表。
- 控制表数量：单库不超过300张,超过分库。
- 平衡范式与冗余。
- 拒绝3B:大SQL (BIG SQL),大事务 (BIG Transaction),大批量 (BIG Batch)。
字段类规约（6个）
-
用合适的整数类型
类型 存储空间 有符号 无符号 TINYINT 1byte [-128,127] [0,255] SMALLINT 2byte [-32768,32767] [0,65535] MEDIUMINT 3byte [-8388608,8388607] [0,16777215] INT 4byte [-2147483648,2147483647] [0,4,294,967,295] BIGINT 8byte [-9223372036854775808,9223372036854775807] [0,18446744073709551615] -
能转数字的尽量转数字(节省空间,提升效率)
-
优先使用Enum或者Set类型
-
避免字段的值为NULL(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效)
-
尽量少用text/blob类型（尽量使用varchar代替text字段），需要的时候请拆表
-
不在数据库存图片，请存图片路径
索引类规约（5个）
-
合理使用索引（改善查询，减慢更新，索引一定不是越多越好）
-
字符字段必须建前缀索引
`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音', KEY `idx_pinyin` (`pinyin`(8)), -
不在列上做运算
-
Innodb主键推荐使用自增列（主键建立聚簇索引，主键不应该被修改，字符串不应该做主键）
-
不用外键（由程序保证约束）
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 * from xxx where a=1 union select * from xxx where b=1 -
避免负向%查询
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 提高查询效率 -
请使用同类型比较（避免转换导致索引丢失）
-
使用 LOAD DATA 导入命令比 insert 速度快20倍左右（适合导入大量测试数据）
-
打散大批量更新（例如：在凌晨空闲时期更新执行）
约定类军规（4个）
- 隔离线上线下（开发用dev库，测试用test库，生产用production库）
- 不在程序端加锁，即外部锁，外部锁不可控，会导致高并发会炸，极难调试和排查
- 统一字符集为utf8_general_ci
- 统一命名规范，库表名一律小写，索引前缀用 idx_ 库名 用缩写（2-7字符），不使用系统关键字保留字命名
