为何MySQL优先选用带filesort的idx1索引范围扫描而非idx2索引查找？

背景信息 #

表结构与索引 #

create table entries
(
    id                        bigint auto_increment
        primary key,
    parent_entries_id         bigint                                 null,
    group_type                varchar(16)                            not null,
    quantity                  int                                    not null,
    -- 若干其他字段
    created_at                datetime     default CURRENT_TIMESTAMP null,
    modified_at               datetime                               null on update CURRENT_TIMESTAMP,
    constraint FK_396C2CCA677190CC
        foreign key (parent_entries_id) references parent_entries (id)
)
    collate = utf8_unicode_ci;

create index idx_1 on entries (quantity, group_type);
create index idx_2 on entries (group_type, quantity);

查询需求与SQL #

需要统计quantity>0的记录按group_type分组的数量，执行SQL：

SELECT COUNT(*), group_type
FROM entries
WHERE quantity > 0
GROUP BY group_type;

执行计划对比 #

两种索引的执行计划如下：

问题 #

预期MySQL会选择idx_2（无需临时表与文件排序，理论上更高效），但实际默认选用了idx_1（速度更慢，需临时表和文件排序），想知道是否需要用USE INDEX子句强制指定索引？

解答 #

MySQL默认选idx_1的原因 #

MySQL优化器是基于成本估算选择索引的：

• idx_1是(quantity, group_type)，WHERE quantity>0属于范围查询，优化器估算仅需扫描66万多行，虽然后续要做临时表和排序，但它判定这个扫描量的总成本更低。
• idx_2是(group_type, quantity)，优化器需要全扫整个索引（421万多行），再过滤quantity>0的记录，它估算过滤后的数据量约为421万×33.33%≈140万行，比idx_1的扫描量高，因此判定idx_1成本更低，优先选择。

但这里优化器的估算和实际执行效率不符——idx_2不需要临时表和排序，实际运行时的开销可能比idx_1的排序+临时表操作更小。

是否需要强制指定索引？ #

可以使用USE INDEX(idx_2)或FORCE INDEX(idx_2)强制优化器选用idx_2，如果实际测试后idx_2的执行速度确实更快，那这个方案是可行的。

另外，也可以先执行ANALYZE TABLE entries;更新表的统计信息，让优化器获得更准确的行数估算，有可能会自动选择更优的索引。如果更新统计后优化器仍选错，再强制指定索引是合理的选择。

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