MariaDB特定条件下简单查询性能低下原因咨询

问题背景 #

在MariaDB中，一条看似简单的查询执行耗时高达0.7678秒，语句如下：

select  `referenceNumber`
    from  `invoice`
    where  `groupId` = 3550
      and  `referenceNumber` >= 301
    order by  `referenceNumber` desc
    limit  1;

referenceNumber和groupId字段均有单独索引，原查询的EXPLAIN结果显示：优化器选择了invoice_groupid_index（groupId单字段索引），type为ref，Extra字段包含Using where; Using filesort——意味着需要先遍历该索引下所有groupId=3550的行，筛选出referenceNumber>=301的数据后，再对这些数据进行排序，最后取第一条结果。

通过子查询优化后，查询耗时骤降至0.0011秒，优化后的语句：

select  `referenceNumber`
    from  (
        SELECT  id
            from  `invoice`
            where  `groupId` = 3550
              and  `referenceNumber` >= 301
          ) as subquery
    JOIN  invoice as invoice  ON invoice.id = subquery.id
    order by  `referenceNumber` desc
    limit  1;

优化后查询的EXPLAIN结果显示：子查询先筛选出符合条件的主键id，再通过主键关联主表获取数据，此时需要排序的数据集极小，避免了大规模排序操作。

两个关键现象 #

• 去掉where groupId = 3550条件后，查询耗时仅0.0005秒，语句：

select  `referenceNumber`
    from  `invoice`
    where  `referenceNumber` >= 301
    order by  `referenceNumber` desc
    limit  1;

• 去掉order by referenceNumber desc后，查询耗时仅0.0011秒，语句：

select  `referenceNumber`
    from  `invoice`
    where  `groupId` = 3550
      and  `referenceNumber` >= 301
    limit  1;

表结构 #

CREATE TABLE `invoice` (
  `id` int(10) UNSIGNED NOT NULL,
  `groupId` int(11) NOT NULL,
  `referenceNumber` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `invoice`
  ADD PRIMARY KEY (`id`),
  ADD KEY `invoice_groupid_index` (`groupId`),
  ADD KEY `invoice_referencenumber_index` (`referenceNumber`);

ALTER TABLE `invoice`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

性能低下的核心原因 #

1. 索引选择与排序成本不匹配：原查询中优化器选择了groupId单字段索引，虽然能快速定位到groupId=3550的所有行，但这些行的referenceNumber是无序的。要满足order by referenceNumber desc的要求，必须把所有符合referenceNumber>=301的数据加载到内存后执行filesort排序——如果该分组下数据量较大，排序操作会消耗大量CPU和IO资源，导致耗时剧增。
2. 缺少最优联合索引：当前仅存在两个单字段索引，没有(groupId, referenceNumber)的联合索引。如果有这个联合索引，优化器可以直接通过索引定位到groupId=3550且referenceNumber>=301的数据，且索引本身按groupId+referenceNumber排序，只需取最后一条即可，完全避免排序操作，效率会大幅提升。
3. 优化器执行计划偏差：原查询的order by+limit组合，优化器没有选择referenceNumber索引（该索引可避免排序，但需要过滤groupId=3550的条件）。优化器可能认为groupId=3550的过滤性更好，但忽略了后续大规模排序的成本；而子查询的写法强制优化器先筛选出小范围的符合条件的id，再通过主键关联获取数据，此时排序的数据集极小，耗时自然骤降。
4. 单字段索引的局限性体现：
   • 去掉groupId条件后，查询直接使用referenceNumber索引，该索引本身有序，只需从后往前找到第一个>=301的记录即可，无需排序，因此极快。
   • 去掉order by后，优化器只需从groupId索引中找到第一条符合referenceNumber>=301的记录即可，无需排序，耗时也显著降低。

内容的提问来源于stack exchange，提问作者Clément B.