带与不带Limit子句的Order By为何选择不同索引？

我发现MariaDB的查询优化器在带Limit和不带Limit的相同查询中选择了不同索引，导致性能差异极大，想请人解释原因并给出优化建议。

以下是MariaDB中的执行计划：

带LIMIT 25的查询执行计划 #

EXPLAIN SELECT dms_meta.docid 
FROM dms_meta 
WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') 
ORDER BY dms_meta.docid ASC limit 25;

+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | dms_meta | index | indx_metaid   | indx_docid | 8       | NULL | 889  | Using where |
+------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+

1 row in set (0.001 sec)

不带LIMIT的查询执行计划 #

EXPLAIN SELECT dms_meta.docid 
FROM dms_meta 
WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') 
ORDER BY dms_meta.docid ASC;

+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+
| id   | select_type | table    | type | possible_keys | key         | key_len | ref   | rows   | Extra                       |
+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+
|    1 | SIMPLE      | dms_meta | ref  | indx_metaid   | indx_metaid | 4       | const | 121880 | Using where; Using filesort |
+------+-------------+----------+------+---------------+-------------+---------+-------+--------+-----------------------------+

1 row in set (0.000 sec)

性能差异与环境信息 #

• 带Limit的查询耗时0.5秒，不带Limit的查询耗时超6秒。
• 使用的数据库版本：mysql Ver 15.1 Distrib 10.11.4-MariaDB。
• 对比MySQL 5.0.95：两个查询会选择相同的索引，且耗时均不足1秒。

我需要引擎每次都能做出正确的索引选择，求优化建议？

原因解释 #

查询优化器的核心目标是最小化查询成本，它会基于统计信息评估两种索引的执行代价：

1. 带Limit时选择indx_docid的原因：
   该索引按docid排序，刚好满足ORDER BY dms_meta.docid ASC的需求，无需额外排序。优化器判断：顺着这个索引扫描，找到25条符合metid=3 and value='2015-10-01'的记录即可停止，预估仅需扫描889行，代价远低于先通过indx_metaid过滤12万+行再排序的成本。
2. 不带Limit时选择indx_metaid的原因：
   此时需返回所有符合条件的记录，优化器认为：先通过indx_metaid过滤出121880行，再对这些行做filesort排序的整体代价，远低于全量扫描indx_docid并逐一检查where条件的代价——后者可能要扫描远多于12万行才能捞全所有符合条件的记录。

MySQL 5.0.95与MariaDB 10.11的差异，源于不同版本优化器的代价模型、统计信息收集逻辑有更新，对Limit场景的代价评估规则不一致。

优化建议 #

要让优化器稳定选择最优索引，可按以下方案操作：

• 创建复合覆盖索引（最优方案）：
  建立包含metid、value、docid的复合索引，既能快速过滤where条件，又能直接满足排序需求，彻底避免filesort和索引选择纠结：

  CREATE INDEX idx_metid_value_docid ON dms_meta(metid, value, docid);
  

  该索引优势：按metid+value快速定位目标记录，索引内的docid天然有序，无需额外排序，且无论带不带Limit，优化器都会优先选择它，性能一致且最优。
• 强制指定索引（临时方案）：
  若暂时无法新建索引，可通过FORCE INDEX强制优化器选择指定索引：

  -- 强制使用过滤索引（适合需要统一索引选择的场景）
  SELECT dms_meta.docid 
  FROM dms_meta FORCE INDEX(indx_metaid)
  WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') 
  ORDER BY dms_meta.docid ASC limit 25;
  

• 更新统计信息：
  确保数据库统计信息最新，让优化器能做出准确判断：

  ANALYZE TABLE dms_meta;
  

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