SQL优化案例

深度解析：三个让数据库性能飙升的SQL优化实战案例（附性能对比）

---

目录导读

1. 引言：为什么你的SQL跑不快？
2. 隐式类型转换引发的“血案”——索引失效全解析
3. 深分页之殇——LIMIT 1000000, 10 的优化策略（延迟关联）
4. OR 条件的陷阱——如何用 UNION ALL 拯救执行计划
5. 实战问答：关于索引与优化的高频疑问
6. SQL优化的核心思维模型

---

引言：为什么你的SQL跑不快？

在业务初期，数据量在百万级时，简单的SQL也能“跑得飞快”，但当数据量突破千万级，或者并发量上来后，一些原本“性能尚可”的查询会突然变成慢查询，拖垮整个数据库，很多开发者在排查时，往往只关注“加了索引没”，却忽略了执行计划中的细节，本文将通过三个真实的线上故障案例，深入剖析那些“隐形的杀手”，并给出可落地的优化方案，这不仅是一次SQL改写,更是一次对数据库底层逻辑的认知升级。

---

案例一：隐式类型转换引发的“血案”——索引失效全解析

场景描述：某电商订单表 orders 中，order_no 字段类型为 VARCHAR(32)，且已建有唯一索引,线上查询语句为：

SELECT * FROM orders WHERE order_no = 123456789;

故障现象：该查询执行时间高达 2.3 秒,且随着数据量增加持续恶化。

SQL优化案例分析：

• 问题根源：在MySQL中，当字符串字段与数字进行比较时，MySQL会隐式地将字符串转换为数字，这意味着索引列 order_no 上发生了函数操作（类型转换），导致索引失效,全表扫描。
• 验证方法：通过 EXPLAIN 查看执行计划，type 列为 ALL，key 列为 NULL。
• 解决方案：将查询中的数字常量改为字符串字面量：

  SELECT * FROM orders WHERE order_no = '123456789';

性能对比：优化后执行计划 type 变为 const，查询耗时从 2.3 秒降至 001 秒，性能提升超过 2000倍。

---

案例二：深分页之殇——LIMIT 1000000, 10 的优化策略（延迟关联）

场景描述：后台管理系统需要获取第 100000 页的数据（每页10条）,查询语句为：

SELECT * FROM operation_log WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 100000, 10;

故障现象：该语句执行时间超过 5 秒，且 EXPLAIN 显示虽然使用了索引 idx_user_time，但 rows 扫描行数高达 200 万行。

SQL优化案例分析：

• 问题根源：LIMIT 100000, 10 意味着MySQL必须扫描前 100010 条记录，然后丢弃前 100000 条，即使有索引，扫描 10 万行聚簇索引数据并进行排序,代价依然巨大。
• 核心策略：延迟关联（即先查主键，再回表）。

  SELECT t1.* 
  FROM operation_log t1
  INNER JOIN (
      SELECT id 
      FROM operation_log 
      WHERE user_id = 10086 
      ORDER BY create_time DESC 
      LIMIT 100000, 10
  ) AS tmp ON t1.id = tmp.id;

性能对比：优化后，子查询只需在二级索引上扫描（避免回表），大大减少了IO成本，执行时间从 5 秒降至 2 秒，性能提升 25倍，如果分页非常深，建议进一步使用“书签”方案（基于 WHERE create_time < '上次翻页的最大时间'）。

---

案例三：OR 条件的陷阱——如何用 UNION ALL 拯救执行计划

场景描述：用户搜索接口,需要查询满足两个条件之一的数据：

SELECT * FROM product 
WHERE brand_id = 1001 OR status = 1 
ORDER BY sales_volume DESC LIMIT 20;

故障现象：brand_id 和 status 分别建有单列索引，但该查询耗时 800ms，且 EXPLAIN 显示 type 为 index_merge（索引合并）,但实际执行时可能因为合并操作的排序开销过大而变慢。

SQL优化案例分析：

• 问题根源：OR 条件可能导致优化器选择 index_merge 算法，合并两个索引的结果集，若两个条件的选择性都很高（即过滤出的数据多）,合并去重后的排序会成为CPU瓶颈。
• 优化策略：将 OR 改写为 UNION ALL 并分别利用索引，注意必须保证两个查询结果无重复（此处 brand_id 和 status 无交集，但如果可能有交集需用 UNION 去重，代价需权衡）。

  SELECT * FROM product WHERE brand_id = 1001 
  UNION ALL 
  SELECT * FROM product WHERE status = 1;

性能对比：改写后，每个子查询都能高效地利用独立索引（type 为 ref），且避开了 index_merge的合并排序，耗时从 800ms 降至 120ms，性能提升 6倍。

---

实战问答：关于索引与优化的高频疑问

问：我已经给WHERE条件字段建了索引，为什么查询还是很慢？ 答：请检查执行计划，常见原因有：① LIKE '%xx' 导致索引失效；② 对索引列使用了函数或运算（如 WHERE age + 1 = 20）；③ 查询结果集过大（超过全表的 20%），优化器放弃索引选择全表扫描更高效；④ 索引选择性太低（如性别字段）。

问：优化 SQL 时，EXPLAIN 中 key_len 和 ref 字段有什么作用？ 答：key_len 表示索引使用的字节数，可以推断出使用了联合索引的哪几列；ref 显示了索引被用来匹配的列或常量。ref 为 NULL 且 type 为 ALL,则代表根本没用上索引。

问：在业务高并发下，频繁更新的字段适合加索引吗？ 答：不适合高频率更新，索引维护需要额外开销，会导致写操作变慢，应评估读多写少还是写多读少，对于写多读少的场景，可以考虑在从库上建索引，或使用 ES 等搜索引擎。

---

SQL优化的核心思维模型

从上述三个案例可以看出，SQL 优化的本质是减少数据库的 IO 成本和 CPU 计算量,优化思路遵循以下优先级：

1. 改写 SQL（消除隐式转换、优化分页、拆分复杂条件）。
2. 优化索引结构（添加联合索引、覆盖索引）。
3. 反范式设计（引入冗余字段，避免关联查询）。

请养成一个好习惯：每次上线 SQL 前，使用 EXPLAIN 检查关键查询的执行计划，当面对慢查询时，不要盲目增加索引，先质疑 SQL 写法是否“欺骗”了优化器。

希望这些实战案例能为你的性能调优工作带来新的启发，如果你有更奇特的优化经历,欢迎在评论区交流讨论。