优化SQL查询，告别COUNT(*)、SELECT *与“万恶”的子查询

发布于 2025-10-02 分类: 数据库
系列文章: MySQL开发避坑指南
第 1 部分: 别让数据类型和字符集成为性能瓶颈 第 2 部分: 揭秘索引失效与滥用的性能黑洞 第 3 部分: 优化SQL查询，告别COUNT(*)、SELECT *与“万恶”的子查询 (当前) 第 4 部分: 事务、锁与连接池的终极迷思

前言

今天，我们将聚焦于4个在日常开发中极易被忽视的SQL编写习惯。这些习惯可能在开发环境中运行良好，但一旦到了生产环境的海量数据面前，就会立刻暴露出其性能瓶颈。

坑十：SELECT * 的“便利”与“罪恶”

SELECT * 几乎是每个SQL初学者学会的第一条命令，因为它实在是太方便了。但这种便利性背后，隐藏着巨大的性能隐患和维护性问题。

场景描述:
一个博客系统，需要在一个页面上展示文章列表，只需要显示文章的 id, title, 和 author_id。开发者为了方便，直接使用了 SELECT * FROM articles LIMIT 10;。articles 表中还有一个体积很大的 content 字段（TEXT类型）。

问题剖析:

  1. 不必要的I/O开销: SELECT * 会读取表中所有的列，包括那个我们根本不需要的、巨大的 content 字段。这意味着数据库需要从磁盘读取更多的数据页到内存中。当并发量高时，这会极大地增加磁盘I/O压力和网络带宽消耗。

  2. 无法利用覆盖索引（Covering Index）: 这是一个极其重要的性能优化点。如果我们创建了一个复合索引 idx_title_author(title, author_id)，然后执行查询 SELECT title, author_id FROM articles WHERE ...，MySQL发现所有需要的数据都在这一个索引树上，它就不需要回表去主键索引中查找其他数据了。这个过程就叫做“覆盖索引”。而 SELECT * 因为请求了所有列，几乎总是需要回表，从而丧失了这一重要的优化机会。

    graph LR
    subgraph "使用覆盖索引 (SELECT title, author_id)"
        A[查询开始] --> B(扫描 idx_title_author 索引);
        B --> C{所需数据全在索引中?};
        C -- 是 --> D(直接从索引返回数据);
        D --> E[查询结束 - 高效];
    end

    subgraph "使用 SELECT *"
        F[查询开始] --> G(扫描某个索引, 如主键);
        G --> H{获取主键ID};
        H --> I(回表: 根据主键ID再次查询聚簇索引);
        I --> J(获取整行数据, 包括content);
        J --> K[查询结束 - 低效];
    end

  3. 应用程序内存消耗: 查询出了大量不需要的数据，这些数据会被传输到应用服务器，并被ORM框架（如MyBatis, Hibernate）映射成对象，无谓地消耗了应用服务器的内存。

  4. 维护性差: 当数据库表结构发生变更（比如增加或删除字段）时，依赖 SELECT * 的代码可能会出现意想不到的错误或行为。

正确实践:
按需取列，明确指定你需要的每一个字段。

-- 推荐的写法
SELECT id, title, author_id FROM articles LIMIT 10;

这不仅是最佳实践，更是专业DBA和高级开发者的基本素养。

坑十一：COUNT(*) vs COUNT(1) vs COUNT(column)，傻傻分不清楚

统计行数是数据库的常见操作，但这三个 COUNT 写法之间有微妙的差别，选择不当可能会导致性能差异或逻辑错误。

背景知识:

  • COUNT(*): SQL标准中用于统计行数的标准写法。它会统计所有行，包括 NULL 值的行。
  • COUNT(1): 这里的 1 是一个常量，效果与 COUNT(*) 类似，也是统计所有行，包括 NULL 值的行。
  • COUNT(column_name): 统计指定列中非 NULL 值的行数。

问题剖析:

  1. 性能误区: 长期以来，社区流传着 COUNT(1) 比 COUNT(*) 快的说法。在现代的MySQL版本（尤其是InnoDB存储引擎）中，这两者之间没有任何性能差异。MySQL优化器会专门对 COUNT(*) 和 COUNT(1) 进行优化，将其识别为“统计所有行”，并选择一个最高效的方式来执行（通常是扫描最小的可用索引）。

  2. 逻辑混淆: 最常见的坑是混淆 COUNT(*) 和 COUNT(column)。

    • SELECT COUNT(*) FROM users; 是统计用户总数。
    • SELECT COUNT(email) FROM users; 是统计拥有Email地址的用户数（如果email列允许为NULL）。
      这两个查询的结果可能完全不同！

  3. InnoDB vs MyISAM:

    • 在MyISAM存储引擎中，因为它没有事务，表的总行数被直接存储在一个元数据中，所以 COUNT(*) 操作是O(1)级别的，瞬间完成。
    • 在InnoDB存储引擎中，由于MVCC（多版本并发控制）的存在，不同事务在同一时刻看到的“总行数”可能不同。因此，InnoDB必须通过扫描来实时计算行数。通常它会选择扫描一个最小的二级索引来完成，如果不存在二级索引，则会扫描聚簇索引（主键索引），成本较高。

正确实践:

  1. 明确意图:
    • 当你需要统计表总行数时，使用 COUNT(*)。这是最标准、最清晰的写法。
    • 当你需要统计某一列非空值的数量时，使用 COUNT(column_name)。
  2. 为 COUNT(*) 优化: 对于大表的 COUNT(*)，可以考虑以下策略：
    • 建立一个最小的二级索引: 例如 KEY idx_dummy (tinyint_column)。这样InnoDB就可以扫描这个非常小的索引来完成计数。
    • 使用计数表: 对于实时性要求不高的场景，可以创建一个单独的计数表，通过触发器或应用层逻辑来维护总数。
    • 估算: 在某些场景下，EXPLAIN SELECT * FROM table; 返回的 rows 数或者 SHOW TABLE STATUS 里的 Rows 数可以作为一个粗略的估算值，但它并不精确。

坑十二：子查询的滥用，尤其是 IN 和 NOT IN

子查询（Subquery）让SQL看起来更具结构化，但MySQL优化器在处理某些类型的子查询时，表现得非常糟糕，特别是关联子查询和 IN/NOT IN 子查询。

场景描述:
需要查询所有没有发布过文章的用户。users 表和 articles 表。

-- 使用 NOT IN 子查询，在老版本MySQL中是性能灾难
SELECT name FROM users
WHERE id NOT IN (SELECT author_id FROM articles);

问题剖析:
在MySQL 5.5及之前的版本中，优化器会将 NOT IN 子查询转换为一种叫做“相关子查询”（Correlated Subquery）的执行方式。它的逻辑大致是这样的：

遍历 users 表的每一行（我们称之为outer_row）。
对于每一行outer_row，都去执行一次内部的子查询 SELECT author_id FROM articles，并检查 outer_row.id 是否存在于子查询的结果集中。

如果 users 表有10000行，articles 表有100000行，这个操作的复杂度近似于 10000 * 100000，一场彻头彻尾的性能灾难。虽然新版本的MySQL对子查询做了很多优化（比如物化子查询），但它仍然不是最佳选择。

NOT EXISTS 通常比 NOT IN 性能更好，但最佳的方案往往是使用 JOIN。

正确实践:
尽可能用 JOIN 来代替子查询。JOIN 通常能更好地利用索引，执行计划也更可控。

-- 使用 LEFT JOIN ... IS NULL 的方式，性能极佳
SELECT u.name
FROM users u
LEFT JOIN articles a ON u.id = a.author_id
WHERE a.author_id IS NULL;

执行逻辑对比:

  • LEFT JOIN 方案:
    1. 以 users 表为驱动表，通过索引（如果有）去 articles 表中查找匹配的行。
    2. 对于那些在 articles 表中找不到匹配行的 users 记录（即从未发表过文章的用户），articles 表的相关字段会是 NULL。
    3. 最后通过 WHERE a.author_id IS NULL 筛选出这些用户即可。
      这个过程通常只需要对两张表进行一次高效的连接扫描。

对于 IN 子查询，同样可以用 INNER JOIN 或 EXISTS 来改写，以获得更好的性能。

坑十三：LIMIT 分页的深坑

当数据量巨大时，LIMIT offset, count 的分页方式会随着 offset 的增大而性能急剧下降。

场景描述:
一个拥有数百万条记录的帖子表 posts，需要进行分页展示。

-- 查询第一页，速度飞快
SELECT * FROM posts ORDER BY created_at DESC LIMIT 0, 20;

-- 查询第10000页，灾难降临
SELECT * FROM posts ORDER BY created_at DESC LIMIT 200000, 20;

问题剖析:
LIMIT 200000, 20 的工作原理是：

  1. MySQL会根据 ORDER BY created_at DESC 排序。
  2. 然后，它会从头开始，扫描并丢弃掉前面的 200,000 条记录。
  3. 最后，再取后面的 20 条记录作为结果返回。

这个“扫描并丢弃”的过程是性能瓶颈所在。offset 越大，需要扫描和丢弃的记录就越多，查询耗时也就越长。

正确实践:
避免大 offset，改用“书签”或“延迟关联”的方式。

  1. 书签法 (Seek Method / Keyset Pagination):
    这是一种基于上一页最后一条记录的位置来进行下一次查询的方法，适用于无限滚动的场景。

    • 前端在请求第一页时，正常查询 LIMIT 20。
    • 服务器返回20条数据，同时告诉前端最后一条数据的 id 和 created_at（排序键）。
    • 前端请求第二页时，带上这两个参数，查询变为：
      -- last_id 和 last_created_at 是上一页最后一条记录的值
SELECT * FROM posts
WHERE (created_at < 'last_created_at')
   OR (created_at = 'last_created_at' AND id < 'last_id')
ORDER BY created_at DESC, id DESC
LIMIT 20;

    这样每次查询都是从一个确定的、很小的范围开始，避免了扫描大量数据。

  2. 延迟关联 (Deferred Join):
    如果必须使用 offset 分页，可以先通过索引快速定位到目标页的 id，然后再关联回原表获取完整数据。

    SELECT p.*
FROM posts p
INNER JOIN (
    SELECT id FROM posts
    ORDER BY created_at DESC
    LIMIT 200000, 20
) AS page_ids ON p.id = page_ids.id
ORDER BY p.created_at DESC;

    这个查询的内层子查询 (SELECT id ...) 是一个覆盖索引查询，速度非常快。它快速找到那20个目标id后，外层再通过主键关联，获取20行完整数据。这极大地减少了需要扫描和处理的数据量。

-- 感谢阅读 --