外观
开发军规
约 1453 字大约 5 分钟
2025-06-19
参考来源：赶集网 MySQL 开发军规
写在前面的话
总是在灾难发生后，才想起容灾的重要性； 总是在吃过亏后，才记得曾经有人提醒过。
核心军规
| 编号 | 规则 | 说明 |
|---|---|---|
| （1） | 不在数据库做运算 | CPU 计算务必移至业务层 |
| （2） | 控制单表数据量 | 单表记录建议控制在 1000 万以内 |
| （3） | 控制列数量 | 字段数控制在 20 以内 |
| （4） | 平衡范式与冗余 | 为提高效率可适当牺牲范式，冗余数据 |
| （5） | 拒绝 3B | 拒绝大 SQL、大事务、大批量 |
字段类军规
（6）用好数值类型
选择合适的数值类型以节省存储空间：
| 类型 | 字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| tinyint | 1 | -128 ~ 127 | 0 ~ 255 |
| smallint | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| mediumint | 3 | -838万 ~ 838万 | 0 ~ 1677万 |
| int | 4 | -21亿 ~ 21亿 | 0 ~ 42亿 |
| bigint | 8 | ±9.2×10¹⁸ | 0 ~ 1.8×10¹⁹ |
Bad Case：
int(1)/int(11)— 显示宽度与存储大小无关，不要用来限制数值范围。
（7）IP 地址使用 int 存储
-- 使用 int 而不是 varchar(15) 存储 IP
INET_ATON('192.168.1.1') -- 转为 int
INET_NTOA(3232235777) -- int 转回 IP（8）优先使用 ENUM 或 SET
-- 推荐：使用枚举类型
sex ENUM('F', 'M') NOT NULL DEFAULT 'M'（9）避免使用 NULL 字段
- NULL 字段难以查询优化
- NULL 字段的索引需要额外空间
- NULL 字段的复合索引可能失效
-- Bad Case
`name` CHAR(32) DEFAULT NULL
`age` INT NOT NULL
-- Good Case
`name` CHAR(32) NOT NULL DEFAULT ''
`age` INT NOT NULL DEFAULT 0（10）少用 TEXT/BLOB
- VARCHAR 的性能远高于 TEXT
- 如果实在无法避免 BLOB 类型，请考虑拆分表
（11）不在数据库中存储图片
图片应存储在文件系统中，数据库中只保存路径。
索引类军规
（12）谨慎合理使用索引
- 索引改善查询，但会减慢更新
- 索引不是越多越好（能不加就不加，要加的一定得加）
- 区分度过低的字段不适合建索引，如"性别"
（13）字符字段必须建前缀索引
-- 对长字符串字段建立前缀索引
ALTER TABLE user ADD INDEX idx_name (name(10));（14）不在索引列上做运算
-- Bad Case
SELECT id FROM t WHERE age + 1 = 10;
-- Good Case
SELECT id FROM t WHERE age = 9;（15）InnoDB 主键推荐使用自增列
- 主键建立聚簇索引
- 主键不应被修改
- 字符串不应做主键
- 如果不指定主键，InnoDB 会使用唯一且非空的索引代替
（16）不使用外键
- 外键约束会影响性能
- 数据一致性由应用程序保证
SQL 类军规
（17）SQL 语句尽可能简单
- 一条 SQL 只能在一个 CPU 上运算
- 大语句拆小语句，减少锁时间
- 一条大 SQL 可能阻塞整个数据库
（18）保持事务简短
- 事务时间尽可能短
- 避免在事务中进行远程调用
（19）避免使用触发器和函数
- 触发器、函数不利于调试和维护
- 逻辑应在客户端程序中实现
（20）禁止 SELECT *
-- Bad Case
SELECT * FROM user WHERE id = 1;
-- Good Case
SELECT id, name, email FROM user WHERE id = 1;- 消耗 CPU、IO、内存、带宽
- 程序不具有扩展性
（21）OR 改写为 IN()
-- Bad Case：OR 效率为 O(n)
SELECT id FROM t WHERE phone = '159' OR phone = '136';
-- Good Case：IN 效率为 O(log n)
SELECT id FROM t WHERE phone IN ('159', '136');IN 的个数建议控制在 200 以内。
（22）不同字段的 OR 改写为 UNION
-- Bad Case：MySQL 的索引合并能力有限
SELECT id FROM t WHERE phone = '159' OR name = 'john';
-- Good Case
SELECT id FROM t WHERE phone = '159'
UNION
SELECT id FROM t WHERE name = 'john';（23）避免负向 % 模糊查询
-- Bad Case：无法使用索引
SELECT * FROM user WHERE name NOT LIKE '%abc%';
SELECT * FROM user WHERE name LIKE '%abc';
-- Good Case：使用前缀匹配
SELECT * FROM user WHERE name LIKE 'abc%';（24）慎用 COUNT(*)
- COUNT(*) 在 InnoDB 中需要扫描索引树，数据量大时较慢
- 可考虑使用近似计数或缓存计数
（25）Limit 高效分页
-- Bad Case：offset 越大越慢
SELECT id FROM t LIMIT 100000, 10;
-- Good Case：基于游标的分页
SELECT id FROM t WHERE id > 100000 LIMIT 10;（26）使用 UNION ALL 替代 UNION
- UNION 有去重开销
- 如果不需要去重，使用 UNION ALL
（27）少用 JOIN
- 尽量在应用层做关联
- 复杂 JOIN 会影响执行计划
（28）合理使用 GROUP BY
- GROUP BY 会触发临时表和文件排序
- 尽量使用索引覆盖 GROUP BY 字段
（29）保持类型一致
-- Bad Case：隐式类型转换导致索引失效
SELECT * FROM user WHERE phone = 13800000000; -- 字段是 VARCHAR
-- Good Case
SELECT * FROM user WHERE phone = '13800000000';（30）使用 LOAD DATA 导入数据
# LOAD DATA 比 INSERT 快约 20 倍
LOAD DATA INFILE '/path/to/data.txt' INTO TABLE table_name;（31）打散批量更新
-- Bad Case：一次性更新大量数据
UPDATE user SET status = 1 WHERE id > 10000;
-- Good Case：分批更新
UPDATE user SET status = 1 WHERE id > 10000 AND id <= 10100;
UPDATE user SET status = 1 WHERE id > 10100 AND id <= 10200;
-- ...（32）性能分析工具
| 工具 | 用途 |
|---|---|
EXPLAIN | 查看 SQL 执行计划 |
SHOW PROFILE | 查看 SQL 执行的资源消耗 |
SHOW SLOW LOG | 查看慢查询日志 |
SHOW PROCESSLIST | 查看当前运行的线程 |
mysqldumpslow | 分析慢查询日志 |
mysqlsla | 第三方 SQL 分析工具 |
query_response_time | Percona 提供的性能分析 |
附录：常见陷阱
- 隐式类型转换：字段类型与查询条件类型不一致会导致索引失效
- 隐式字符编码转换：不同字符集的表 JOIN 会产生临时表和文件排序
- ORDER BY 与 LIMIT 混用：可能导致额外的排序操作
- 分页 OFFSET 过大：参考本目录下的 查询优化 文章