MySQL 8.0分页查询优化器索引选择不合理问题咨询

问题背景 #

业务库中有存量数据量较大的表PendingExpense，近期开发分页查询GET接口时遇到性能异常：MySQL 8.0.23优化器未按预期走过滤字段的联合索引，反而优先为ORDER BY子句做DATE字段的全索引反向扫描，再逐行执行WHERE条件过滤，最终查询耗时长达数秒。
业务逻辑中通过companyId、loginCredentialId两个字段联合定位数据所属用户。

表结构与现有索引 #

create table PendingExpense
(
    ID                        bigint   auto_increment primary key,
    LOGINCREDENTIALID         int      null,
    COMPANYID                 int      null,
    DATE                      datetime null,
    -- ... 其他无关字段：金额、状态、类型、国家、商户信息等
)

-- 现有索引
create index IN_PendingExpense_COMPANYID_ASC_LOGINCREDENTIALID_ASC
    on PendingExpense (COMPANYID, LOGINCREDENTIALID);

create index IN_PendingExpense_LOGINCREDENTIALID_ASC
    on PendingExpense (LOGINCREDENTIALID);

create index IN_PendingExpense_Date
    on PendingExpense (DATE);

对比测试结果 #

两条查询逻辑完全一致，仅存在是否加索引提示的差异，执行计划与耗时对比如下：

无索引提示的查询（耗时5.5秒） #

查询SQL：

explain analyze select id from PendingExpense
where COMPANYID = 1641 and LOGINCREDENTIALID = 2451
order by date DESC, id DESC
limit 101;

执行计划输出：

-> Limit: 101 row(s)  (cost=2356102.00 rows=101) (actual time=2292.676..4474.843 rows=101 loops=1)
    -> Filter: ((PendingExpense.LOGINCREDENTIALID = 2451) and (PendingExpense.COMPANYID = 1641))  (cost=2356102.00 rows=105) (actual time=2292.675..4474.818 rows=101 loops=1)
        -> Index scan on PendingExpense using IN_PendintExpense_Date (reverse)  (cost=2356102.00 rows=5660) (actual time=0.088..4371.774 rows=1491859 loops=1)

该执行路径下优化器选择反向扫描DATE索引，累计扫描149万余行数据后才过滤出符合条件的101条结果。

强制走联合索引的查询（耗时0.184秒） #

查询SQL：

explain analyze select id from PendingExpense use index (IN_PendingExpense_COMPANYID_ASC_LOGINCREDENTIALID_ASC)
where COMPANYID = 1641 and LOGINCREDENTIALID = 2451
order by date desc, id desc
limit 101;

执行计划输出：

-> Limit: 101 row(s)  (cost=9722.30 rows=101) (actual time=38.255..38.267 rows=101 loops=1)
    -> Sort: PendingExpense.`DATE` DESC, PendingExpense.ID DESC, limit input to 101 row(s) per chunk  (cost=9722.30 rows=27778) (actual time=38.254..38.259 rows=101 loops=1)
        -> Index lookup on PendingExpense using IN_PendingExpense_COMPANYID_ASC_LOGINCREDENTIALID_ASC (COMPANYID=1641, LOGINCREDENTIALID=2451)  (actual time=0.046..35.410 rows=14170 loops=1)

该执行路径下仅通过联合索引定位到14170条符合过滤条件的记录，排序后取前101条，性能提升近30倍。

核心疑问 #

• 过滤字段COMPANYID、LOGINCREDENTIALID已经存在可用的联合索引，MySQL优化器为什么会选择全量扫描DATE索引再逐行过滤的低效方案？
• 出于代码可维护性考虑，不希望在业务SQL中硬编码索引提示，有什么其他优化方案可以让优化器自动选择正确的执行路径？

内容的提问来源于stack exchange，提问作者J. LaF