SQL优化实战随记

一、核心优化原则：让索引为你打工

1. 避免索引失效的六大禁忌

• 字段计算陷阱：WHERE子句中避免对索引字段进行运算
  反例：WHERE salary/12 > 5000 → 正例：WHERE salary > 5000*12
• 模糊查询黑洞：前导通配符导致索引失效
  反例：LIKE '%张三%' → 正例：LIKE '张%'
• 类型转换灾难：隐式类型转换让优化器迷茫
  反例：WHERE id = '100'（id为数值型） → 正例：WHERE id = 100

2. 索引设计的黄金法则

• 复合索引顺序：遵循最左前缀原则，高频查询字段放左侧
• 覆盖索引妙用：SELECT字段尽量包含在索引中，减少回表查询
• 索引数量控制：单表索引不超过5个，避免写操作性能下降

二、实战技巧：改写SQL的智慧

1. 拒绝“无脑查询”

• SELECT：只取所需字段，数据传输量减少50%
  反例：SELECT * FROM orders → 正例：SELECT order_id, amount FROM orders
• UNION ALL替代UNION：避免重复数据过滤的开销

2. 复杂条件优化

• OR条件拆分：用UNION ALL替代OR连接
  反例：WHERE id=1 OR id=3 → 正例：SELECT ... UNION ALL SELECT ...
• EXISTS妙用：小表驱动大表时性能提升显著

3. 批量操作的艺术

• 批量插入：单次提交1000条数据比逐条插入快20倍

INSERT INTO users (id, name) VALUES (1, '张三'), (2, '李四'), ...;

• 分页优化：避免LIMIT 100000,20式深分页，改用ID范围查询

三、高阶武器：性能分析工具

1. EXPLAIN执行计划解读

具体请参看：SQL 优化工具使用之 explain 详解

• 关键指标：

• • type：ALL代表全表扫描，需优化为ref或range
  • rows：扫描行数越少越好
  • Extra：出现Using filesort或Using temporary需警惕

2. 慢查询日志分析

• 开启方式：SET GLOBAL slow_query_log = ON;
• 分析工具：Percona Toolkit、pt-query-digest

3. SHOW PROFILE深度追踪

• 查看SQL各阶段耗时：

SET profiling = 1;SELECT * FROM orders;SHOW PROFILES;SHOW PROFILE FOR QUERY 1;```:cite[3]  

四、避坑指南：这些“优化”可能是毒药

1. 过度索引：索引维护成本可能超过查询收益10
2. 盲目并行：高并发下可能引发资源争用8
3. 游标滥用：万行以上数据操作优先考虑集合运算