MySQL数据库开发的 36 条军规

MySQL数据库开发的 36 条军规

标签:数据库

核心军规（5个）

1. 尽量不在数据库做运算。
2. 控制单表数量：行不超过500W条，超出分表；列不超过50个，超过拆表。
3. 控制表数量：单库不超过300张,超过分库。
4. 平衡范式与冗余。
5. 拒绝3B:大SQL (BIG SQL),大事务 (BIG Transaction),大批量 (BIG Batch)。

字段类规约（6个）

1. 用合适的整数类型 类型存储空间有符号无符号TINYINT1byte[-128,127][0,255]SMALLINT2byte[-32768,32767][0,65535]MEDIUMINT3byte[-8388608,8388607][0,16777215]INT4byte[-2147483648,2147483647][0,4,294,967,295]BIGINT8byte[-9223372036854775808,9223372036854775807][0,18446744073709551615]
2. 能转数字的尽量转数字(节省空间,提升效率)
3. 优先使用Enum或者Set类型
4. 避免字段的值为NULL(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效)
5. 尽量少用text/blob类型（尽量使用varchar代替text字段），需要的时候请拆表
6. 不在数据库存图片，请存图片路径

索引类规约（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个）

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

参考文章

https://blog.csdn.net/y_bccl27/article/details/129343773