查询计划一致但LIMIT不同的SQL查询执行时间差异悬殊

问题背景 #

现有一张约200万条记录的tickets表，其中绝大多数记录的tickets.spam为FALSE，仅31条记录的tickets.spam为TRUE，表结构如下：

CREATE TABLE tickets (
  id INT PRIMARY KEY,
  account_id INT,
  w_id INT,
  spam BOOLEAN,
  due_by DATE
);

执行以下查询时，返回数据耗时不足200ms：

SET SESSION query_cache_type = OFF;
SELECT SQL_NO_CACHE tickets.*
FROM tickets
WHERE tickets.account_id = 95
  AND tickets.w_id IN (2, 3, 4, 5, 6)
  AND tickets.spam = TRUE
LIMIT 25 OFFSET 0;

对应的查询计划：

但将LIMIT改为26后，查询执行时间增至5秒，且查询计划与上述完全一致：

其中index_acc_due_ws_id是按account_id、due_by、w_id、id顺序创建的联合索引。

疑问：原以为当LIMIT大于符合条件的记录数时会触发全表扫描，但本次符合条件的记录有31条，为何LIMIT设为26时就出现性能骤降？同时，如何有效处理存在严重数据倾斜的列？

问题解答 #

一、LIMIT 26性能骤降的原因 #

核心问题出在索引结构与数据分布的不匹配：

1. 当前使用的index_acc_due_ws_id索引不包含spam字段，数据库只能先通过account_id=95定位到约104万条索引记录，然后逐条回表读取spam和w_id字段进行过滤。
2. 当LIMIT=25时，数据库在遍历索引记录的过程中，很快找到了25条符合w_id和spam=TRUE的记录，随即停止遍历，所以耗时极短。
3. 而LIMIT=26时，需要找到第26条符合条件的记录，但由于spam=TRUE的记录在索引顺序中分布极其分散且靠后，数据库必须遍历远多于前25条所需的索引条目，才能找到第26条符合要求的记录——并非因为LIMIT超过总符合数，而是前26条符合条件的记录需要扫描大量无关数据才能定位到。

二、严重数据倾斜列的处理方案 #

针对spam这种严重倾斜（绝大多数为FALSE，仅少量TRUE）的列，结合当前查询场景，可采用以下优化手段：

1. 调整联合索引，纳入倾斜过滤字段

创建包含spam的联合索引，推荐顺序为(account_id, spam, w_id, due_by, id)。这样查询时，数据库可以直接通过account_id=95+spam=TRUE定位到极小范围的记录，再过滤w_id，无需大量回表和无效扫描，直接从索引中获取数据。

2. 使用覆盖索引减少回表开销

如果查询不需要全表字段，可以创建覆盖索引(account_id, spam, w_id, id)，查询时直接从索引读取数据，无需回表访问主表。即使需要全字段，也可以先通过覆盖索引找到符合条件的id，再关联主表获取数据：

SELECT t.*
FROM tickets t
JOIN (
    SELECT id
    FROM tickets
    WHERE account_id = 95
      AND w_id IN (2,3,4,5,6)
      AND spam = TRUE
    LIMIT 26
) AS sub ON t.id = sub.id;

3. 对倾斜列进行分区

如果表数据量极大，可以按spam字段分区，将spam=TRUE的记录单独放在一个小分区中，查询时直接扫描该分区，避免遍历绝大多数无关数据。

4. 更新统计信息或强制指定索引

• 执行ANALYZE TABLE tickets;更新表的统计信息，让优化器更准确地判断执行计划。
• 如果优化器仍未选择最优索引，可使用FORCE INDEX强制指定包含spam的索引：

SELECT SQL_NO_CACHE tickets.*
FROM tickets FORCE INDEX(idx_account_spam_wid)
WHERE tickets.account_id = 95
  AND w_id IN (2,3,4,5,6)
  AND spam = TRUE
LIMIT 26;

内容的提问来源于stack exchange，提问作者thebenman