MySQL数据库36条军规

前言

• 总是在灾难发生后，才想起容灾的重要性；
• 总是在吃过亏后，才记得曾经有人提醒过。

来自一线的实战经验

• 每一条军规背后都是血淋淋的教训。
• 不要华丽，只要实用。
• 若有一条让各位有所受益，足矣。
• 别让脚趾头想事情，那是脑子的工作。

正文

一. 核心军规 (5)

1. 尽量不在数据库做运算

• 尽量不在数据库做复杂运算，将运算移到程序端CPU。
• 尽可能简单化MySQL的应用，如避免使用 md5() 或 ORDER BY RAND()。

2. 控制单表数据量

• 合理预估单表数据量，进行适当分表，防止超载。
• 单表纯 INT 不超过1000W，纯 CHAR 不超过500W。
• 单库不超过300-400个表。

3. 保持表身段苗条

• 表字段数尽量少而精，提升IO效率。
• 单表1G体积或500W行需评估，单行不超过200Byte。
• 单表不超过50个纯INT字段，不超过20个 CHAR(10) 字段。

4. 平衡范式与冗余

• 在遵循范式的同时，适当加入冗余提升效率，但需权衡代码复杂度。

5. 拒绝3B

• 拒绝大SQL、大事务、大批量操作（3B），避免数据库性能瓶颈。

核心军规小结

• 尽量不在数据库做运算。
• 控制单表数据量。
• 保持表身段苗条。
• 平衡范式与冗余。
• 拒绝3B。

二. 字段类军规 (6)

1. 用好数值字段类型

• 根据存储需求选择合适的字段类型，如 TINYINT、SMALLINT 等。

2. 将字符转化为数字

• 使用数值型字段而非字符型，提升查询效率。如用 INT 存储IP而非 CHAR(15)。

3. 优先使用 ENUM 或 SET

• ENUM 和 SET 类型占用存储空间小，运算效率高。

4. 避免使用 NULL 字段

• NULL 字段难以优化，并且索引空间消耗大，建议尽量避免。

5. 少用并拆分 TEXT/BLOB

• TEXT 类型性能远低于 VARCHAR，若必须使用则拆分到单独表。

6. 不在数据库里存图片

• 将图片存储在文件系统中，而不是数据库。

字段类军规小结

• 用好数值字段类型。
• 将字符转化为数字。
• 优先使用 ENUM/SET。
• 避免使用 NULL 字段。
• 少用并拆分 TEXT/BLOB。
• 不在数据库存图片。

三. 索引类军规 (5)

1. 谨慎合理添加索引

• 索引改善查询但减慢更新，综合评估数据密度和分布后合理添加。

2. 字符字段必须建前缀索引

• 对长字段创建前缀索引，提升索引效率。

3. 不在索引列做运算

• 索引列上的运算会导致无法使用索引，必须避免。

4. 自增列或全局ID做INNODB主键

• 使用自增列或全局ID做 INNODB 主键，避免使用字符串主键。

5. 尽量不用外键

• 外键有额外的开销且容易导致死锁，建议由程序逻辑控制约束。

索引类军规小结

• 谨慎合理添加索引。
• 字符字段必须建前缀索引。
• 不在索引列做运算。
• 自增列或全局ID做 INNODB 主键。
• 尽量不用外键。

一. SQL类军规 (16)

1. SQL语句尽可能简单

• 拆解复杂SQL为多个简单SQL，以提升性能和缓存命中率。

2. 保持事务连接时间短小

• 事务连接应即开即用，用完即关，避免不必要的资源占用。

3. 尽可能避免使用存储过程、触发器和函数

• 线上OLTP系统尽量减少存储过程、触发器和函数的使用。

4. 尽量不用 SELECT *

• 明确指定需要的字段，避免消耗多余资源。

5. 改写 OR 为 IN()

• 同一字段的多值查询，使用 IN() 替代 OR，提高查询效率。

6. 改写 OR 为 UNION

• 不同字段的多条件查询可用 UNION 替代 OR。

7. 避免负向查询和% 前缀模糊查询

• 避免使用 NOT、!=、%前缀 等查询，因其无法使用索引。

8. COUNT(*) 的优化

• COUNT(*) 开销大，尽量减少使用，优先用其他统计方法。

9. LIMIT 高效分页

• 对于大数据分页，通过优化 LIMIT 和 ID 的使用提升查询性能。

10. 用 UNION ALL 而非 UNION

• UNION 有去重开销，若不需要去重则使用 UNION ALL。

11. 分解联接以保证高并发

• 高并发数据库应避免复杂联接，适当分解连接以提高性能。

12. GROUP BY 去除排序

• GROUP BY 会自动排序，若不需要可通过 ORDER BY NULL 关闭排序。

13. 同数据类型的列值比较

• 保持列类型一致，避免类型转换带来的性能损耗。

14. Load Data 导数据

• 使用 LOAD DATA 导入大量数据，速度远快于 INSERT。

15. 打散大批量更新

• 大批量更新操作应分散执行，尤其是在高并发系统中。

16. 精通每条SQL

• 确保对所有SQL语句的理解与优化，减少性能瓶颈。

SQL类军规小结

• SQL语句尽可能简单。
• 保持事务(连接)短小。
• 尽可能避免使用存储过程、触发器、函数。
• 尽量不用 SELECT *。
• 改写 OR 为 IN() 或 UNION。
• 避免负向查询和% 前缀模糊查询。
• 降低 COUNT(*) 的使用。
• 优化 LIMIT 分页查询。
• 用 UNION ALL 而非 UNION。
• 分解连接保证高并发。
• GROUP BY 去除排序。
• 同数据类型的列值比较。
• LOAD DATA 批量导数据。
• 打散大批量更新。
• 精通每条SQL。

二. 约定类军规 (5)

1. 隔离线上线下

• 构建不同环境的数据库：线上、模拟、测试、开发环境独立。

2. 禁止未经DBA确认的子查询

• 避免上线未经优化的子查询，建议使用 JOIN 改写。

3. 永远不在程序端显式加锁

• 外部显式加锁容易导致灾难性后果，应采用事务代替。

4. 统一字符集为 UTF8

• 所有数据库和表统一字符集为 UTF8，避免乱码问题。

5. 统一命名规范

• 采用小写命名，并避免使用保留字。

约定类军规小结

• 隔离线上线下。
• 禁止未经DBA确认的子查询上线。
• 永远不在程序端显式加锁。
• 统一字符集为 UTF8。
• 统一命名规范。