MySQL查询性能优化：8种常见低效SQL写法分析与重构方案

01 LIMIT 深分页性能问题

分页查询是最常见的场景之一，但也最容易引发性能瓶颈。例如下面这条语句，DBA 通常会在 type、name、create_time 字段上建立组合索引以提升排序和筛选效率。

SELECT *
FROM   operation
WHERE  type = 'SQLStats'
       AND name = 'SlowLog'
ORDER  BY create_time
LIMIT  1000, 10;

然而，当查询偏移量变得极大时，例如 LIMIT 1000000, 10，性能会急剧下降。因为数据库无法直接定位到第 1000000 条记录，即使有索引也需要从头开始遍历计算。这在数据浏览或批量导出的场景下尤为明显。

一个高效的优化方案是利用上一页的末尾值作为游标。将 SQL 重构如下：

SELECT   *
FROM     operation
WHERE    type = 'SQLStats'
AND      name = 'SlowLog'
AND      create_time > '2017-03-16 14:00:00' -- 上一页最后一条记录的时间
ORDER BY create_time limit 10;

这样，查询时间将保持稳定，不会随数据量增长而线性增加。

02 隐式转换导致索引失效

SQL 语句中查询变量与字段定义类型不匹配，会导致隐式转换，从而使索引失效。例如：

mysql> explain extended SELECT *
     > FROM my_balance b
     > WHERE b.bpn = 14000000123
     > AND b.isverified IS NULL ;
mysql> show warnings;
| Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'

这里字段 bpn 被定义为 varchar(20)，而查询条件使用了数字 14000000123。MySQL 的优化器会将字符串转换为数字进行比较，导致函数作用在字段上，索引无法使用。这类问题常由应用框架自动参数填充引起，需要特别注意。

03 关联更新/删除的优化

在 MySQL 5.6 之前，对于包含子查询的更新或删除操作，即使子查询能被物化优化（仅针对 SELECT），UPDATE 或 DELETE 也可能执行低效的循环嵌套子查询。例如：

UPDATE operation o
SET    status = 'applying'
WHERE  o.id IN (SELECT id
                FROM   (SELECT o.id,
                               o.status
                        FROM   operation o
                        WHERE  o.group = 123
                               AND o.status NOT IN ( 'done' )
                        ORDER BY o.parent,
                                 o.id
                        LIMIT 1) t);

其执行计划为 DEPENDENT SUBQUERY，效率低下。应将其重写为 JOIN 形式：

UPDATE operation o
       JOIN  (SELECT o.id,
                     o.status
              FROM   operation o
              WHERE  o.group = 123
                     AND o.status NOT IN ( 'done' )
              ORDER  BY o.parent,
                        o.id
              LIMIT  1) t
         ON o.id = t.id
SET    status = 'applying'

改写后，子查询变为 DERIVED（派生表），执行效率得到数量级提升。

04 混合排序的优化策略

MySQL 难以利用索引处理包含多个字段且排序方向不同的混合排序。例如：

SELECT *
FROM   my_order o
       INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC,
          a.appraise_time DESC
LIMIT  0, 20

执行计划会显示 Using filesort 并进行全表扫描。如果 is_reply 字段仅有0和1两种状态，可以通过 UNION ALL 分治策略优化：

SELECT *
FROM   ((SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 0
         ORDER BY appraise_time DESC
         LIMIT 0, 20)
        UNION ALL
        (SELECT *
         FROM   my_order o
                INNER JOIN my_appraise a
                        ON a.orderid = o.id
                           AND is_reply = 1
         ORDER BY appraise_time DESC
         LIMIT 0, 20)) t
ORDER BY  is_reply ASC,
          appraise_time DESC
LIMIT  20;

此方法将全表混合排序拆分为两个可利用索引的有序子集合并，性能显著改善。

05 用 JOIN 替代 EXISTS 子查询

MySQL 在处理 EXISTS 子句时，默认采用关联子查询（DEPENDENT SUBQUERY）的执行方式，可能导致性能不佳。

SELECT *
FROM   my_neighbor n
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx'
WHERE  n.topic_status < 4
       AND EXISTS(SELECT 1
                  FROM   message_info m
                  WHERE  n.id = m.neighbor_id
                         AND m.inuser = 'xxx')
       AND n.topic_type <> 5

将其改写为 JOIN，可以避免嵌套子查询：

SELECT *
FROM   my_neighbor n
       INNER JOIN message_info m
               ON n.id = m.neighbor_id
                  AND m.inuser = 'xxx'
       LEFT JOIN my_neighbor_apply sra
              ON n.id = sra.neighbor_id
                 AND sra.user_id = 'xxx'
WHERE  n.topic_status < 4
       AND n.topic_type <> 5

优化后，执行计划从嵌套循环变为简单的连接，查询时间大幅缩短。在处理复杂查询时，理解 数据库/中间件 的优化器行为至关重要。

06 条件下推聚合查询

在包含聚合子查询的语句中，外部条件可能无法被下推到子查询内部，导致先全量聚合再过滤的低效操作。

SELECT *
FROM   (SELECT target,
               Count(*)
        FROM   operation
        GROUP  BY target) t
WHERE  target = 'rm-xxxx'

从语义上分析，条件 target = 'rm-xxxx' 完全可以提前。重构后的 SQL 为：

SELECT target,
       Count(*)
FROM   operation
WHERE  target = 'rm-xxxx'
GROUP  BY target

优化后，数据库可以先过滤数据再进行聚合，效率极大提升。

07 提前缩小连接范围

在多表连接查询中，如果排序和过滤条件主要针对驱动表，应优先对驱动表进行筛选和排序，以缩小中间结果集。

SELECT *
FROM   my_order o
       LEFT JOIN my_userinfo u
              ON o.uid = u.uid
       LEFT JOIN my_productinfo p
              ON o.pid = p.pid
WHERE  ( o.display = 0 )
       AND ( o.ostaus = 1 )
ORDER  BY o.selltime DESC
LIMIT  0, 15

原语句先进行多表连接，最后对90万行结果排序取前15条。可优化为先对主表查询：

SELECT *
FROM (SELECT *
      FROM   my_order o
      WHERE  ( o.display = 0 )
             AND ( o.ostaus = 1 )
      ORDER BY o.selltime DESC
      LIMIT 0, 15) o
     LEFT JOIN my_userinfo u
            ON o.uid = u.uid
     LEFT JOIN my_productinfo p
            ON o.pid = p.pid
ORDER BY  o.selltime DESC
limit 0, 15

这样，参与连接的记录数从90万降至15条，性能得到根本性改善。

08 中间结果集下推

在复杂查询中，应避免子查询进行不必要的全表操作，尽量将主表的过滤条件“下推”到相关子查询中。
初始优化后的语句：

SELECT    a.*,
          c.allocated
FROM      (
              SELECT   resourceid
              FROM     my_distribute d
              WHERE    isdelete = 0
              AND      cusmanagercode = '1234567'
              ORDER BY salecode limit 20) a
LEFT JOIN (
              SELECT   resourcesid， sum(ifnull(allocation, 0) * 12345) allocated
              FROM     my_resources
              GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

子查询 c 进行了全表聚合。由于最终结果只关心与主表匹配的资源ID，可将主表结果集 a 下推到子查询 c 中作为过滤条件，并利用 WITH 语句（公共表表达式）使逻辑更清晰：

WITH a AS (
         SELECT   resourceid
         FROM     my_distribute d
         WHERE    isdelete = 0
         AND      cusmanagercode = '1234567'
         ORDER BY salecode limit 20
)
SELECT    a.*,
          c.allocated
FROM      a
LEFT JOIN (
              SELECT   resourcesid, sum(ifnull(allocation, 0) * 12345) allocated
              FROM     my_resources r,
                       a -- 关键：引用CTE a，将资源表连接限制在必要范围内
              WHERE    r.resourcesid = a.resourcesid
              GROUP BY resourcesid) c
ON        a.resourceid = c.resourcesid

此优化将子查询 c 的数据处理范围从全表大幅缩小，提升了 大数据 量下的查询性能。

09 总结

数据库优化器负责生成执行计划，但其能力并非完美无缺。文中提及的多数低效场景在其他数据库中也普遍存在。理解数据库编译器的特性，是写出高性能SQL的关键。

程序员在设计数据模型和编写SQL时，应具备算法复杂度意识。对于复杂查询，养成使用 WITH 语句（CTE）的习惯，不仅能提升代码清晰度，也能有效减轻数据库的负担，是实现高效查询的重要手段。