【MySQL】之 SQL 优化案例

前言

我们在开发过程中难免会遇到慢 SQL 的问题，常常需要我们去优化 SQL 以提高效率，就是我们常说的 SQL 调优。在这里，我记录一些实际开发过程中解决过的慢 SQL 问题，作为一些 SQL 优化案例。

案例一：有条件推翻小表驱动大表的原则

---

场景：

在一个电商项目中，对目标用户信息进行筛选分组分类，脱敏 SQL 如下：

SELECT z.zoneid,
       z.zonename,
       ...
FROM zone z
LEFT JOIN affiliates s ON s.affiliateid = z.affiliateid
LEFT JOIN new_pub p ON p.id = s.new_pub_id
LEFT JOIN agency a ON a.agencyid = p.agency_id 
WHERE z.enabled = 1
  AND s.enabled = 1
  AND p.enabled = 1
  AND a.active = 1
 GROUP BY z.zoneid

问题分析：

• 通过研究 MySQL 语句的执行计划，会发现查询语句需要找出所有 zone 表的记录，并且以 zone-id 进行分组聚合。
• 通过执行计划的反馈，此 SQL 语句使用了 using temporary 以及 using filesort。这块是一个性能损失明显的信号。
• 但观察执行计划，MySQL 引擎使用 “小表驱动大表的原则”，直接调整了 join 表之间的执行顺序。将 agency (它的记录是1000+条记录)设置为驱动表，而 zone（130W+条记录）调整为被驱动表。
• 这个原则对于很多普通 MySQL 查询语句没有问题。在我们场景下，需要推翻这个原则，才能达成优化。原因很简单，我们这个场景最后需要以 zone-id 进行分组聚合，这是导致 using temporary和 using filesort 的原因。换言之，如果能确保 zone 是一个驱动表，并且使用它的 primary 索引进行查询（它的输出结果本身就是有序列的），那么后续的 join 查询，就可以避免 using temporary 和 using filesort 的额外负担。
• 而事实证明，返回54W条记录，优化前查询花费 18 秒,优化后查询时间 3.5 秒。

优化后：

SELECT z.zoneid,
       z.zonename,
       ...
FROM zone z
STRAIGHT_JOIN affiliates s ON s.affiliateid = z.affiliateid
LEFT JOIN new_pub p ON p.id = s.new_pub_id
LEFT JOIN agency a ON a.agencyid = p.agency_id 
WHERE z.enabled = 1
  AND s.enabled = 1
  AND p.enabled = 1
  AND a.active = 1
 GROUP BY z.zoneid

问题总结

• 本案例优化的目标是消除由 group by zone-id 执行语句中引入的 using temporary 与 using file-sort。
• MySQL的优化器证明也不是万能的。它始终坚守一个 小表驱动大表原则，结果将大表 zone 调度为被驱动表，从而额外引入了 using temporary 与 using filesort 的步骤，延长的查询语句的执行时间。
• 我们需要在特殊的场景来有条件地推翻小表驱动大表原则。

案例二：使用区分度大的字段作为索引

---

场景：

在一次日志与配置的查询业务当中，有个复杂的联合查询，脱敏 SQL 如下：

SELECT `a`.`bannerId` AS `ad_id` FROM `new_bn` AS `a`
INNER JOIN `upload_config` AS `uc` ON uc.id = '2337'
LEFT JOIN `upload_log` AS `ul` ON ul.config_id = uc.id
AND ul.output_id IS NULL
AND ul.updated =
  (SELECT MAX(updated)
   FROM upload_log
   WHERE config_id = ul.config_id
     AND ad_id = ul.ad_id
     AND output_id IS NULL)
WHERE (a.bannertype = 1)
  AND (a.filename NOT REGEXP "^https?:\/\/")
UNION
SELECT `a`.`bannerId` AS `ad_id` FROM `new_bn` AS `a`
INNER JOIN `upload_config` AS `uc` ON uc.id = '2337'
LEFT JOIN `upload_log` AS `ul` ON ul.config_id = uc.id
AND ul.output_id = at.id
AND ul.updated =
  (SELECT MAX(updated)
   FROM upload_log
   WHERE config_id = ul.config_id
     AND ad_id = ul.ad_id
     AND output_id = at.id)
WHERE (a.bannertype = 1)
  AND (a.filename NOT REGEXP "^https?:\/\/");

问题分析：

• 这个 SQL 优化问题花了几个小时来排查定位。
• 我们知道级联 join 表查询，最内层表记录数目如果很大，并且索引区分度很底的话，与外层循环做迪卡尔积的效率是非常低的。
• 而通过执行计划可以发现，最内层 upload-log 表使用了 output_id 索引，这张表有 5W+ 条记录。由于 output-id 字段是允许为 null 的，所以它作为索引本质上区分度是非常低的。

优化：

• 通过观察查询语句，我们发现 config-id 与 output-id 在至少二个 join 表中有重复使用，二者联合索引的区分度也是明显高于单独一个 output-id 索引的区分度。
• 所以我们引入这个复合索引 <config_id, output_id>，带来的好处如下：
  • 复合索引的区分度比以前要高；
  • 从 using where 到 using index condition，这个会将 config-id 与 output-id 的查询进行索引下推到 InnoDB 的存储引擎，极大提升效率；
  • 二张 join 表从旧索引切换到新索引。
• 经过优化之后，从之前查询花费 25 秒，到查询时间 1.8 秒。

问题总结：

• 引入 ICP 索引下推；
• 选择区分度（选择率）大的列建立索引。组合索引中，区分度（选择率）大的字段放在最前面。