给定的是这个查询和索引
create table entries
(
id bigint auto_increment
primary key,
parent_entries_id bigint null,
group_type varchar(16) not null,
quantity int not null,
-- a couple of other columns
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);字符串
我想对数量大于0的所有条目进行计数，并按其类型分组：
SELECT COUNT(*), group_type
FROM entries
WHERE quantity > 0
GROUP BY group_type;型
我希望使用idx_2，因为它应该只通过索引查找/迭代提供所有数据，而不需要额外的操作，并且解释计划支持我的想法：
type, key_len, rows, filtered, Extra
idx_1: range, '4', 667_780, 100, 'Using where; Using index; Using temporary; Using filesort'
idx_2: index, '54', 4_218_471, 33.33, 'Using where; Using index'型
但是如果我在不强制索引的情况下运行查询，数据库将使用idx_1（以及临时表和文件排序-是的，它实际上有点慢），我不明白为什么？这是我第一次使用USE INDEX子句吗？
1条答案
按热度按时间lokaqttq1#
如果你的
WHERE quantity > 0过滤器是一个相等过滤器，你的第一个索引会很好地加速这个查询。它会找到GROUP BY值的正确顺序来扫描索引并生成结果，而不需要任何排序。但这不是你的情况。根据DBMS的确切品牌和版本，您可能能够在
((quantity > 0), group_type)上创建函数索引，从而为您的查询获得完美的覆盖索引。否则，你需要一个索引扫描或表扫描和一个文件排序。文件排序有点命名错误。它不是一个文件系统文件，而是一个需要排序的临时表类对象。如果它们真的很大，它们会溢出到磁盘文件，但这很少见。
我建议你信任查询规划器（DBMS中被使用Oracle的人称为“基于成本的优化器”的部分），而不是强制使用索引。只要你在批量加载后执行
ANALYZE TABLE entries，查询规划器将做一个可靠的和未来弹性的工作，决定是使用索引还是简单地扫描表更快。