我有一个类似于以下内容的查询:
FROM example_table
WHERE
`date` BETWEEN '2023-11-26' AND '2023-11-28'
AND location_id IN (3, 4, 6, 7, 8, 10, 11, 12, 14, 18, 19, 22, 23, 24, 28, 29, 30, 31, 32, 36, 39, 40, 41, 43, 45, 46, 48, 49, 50, 51, 52, 54, 55, 56, 57, 59, 60, 61, 62, 68, 69, 75, 121)
AND ( `type` IS NULL OR ( `type` IN ('type1', 'type2', 'type3') ) )
GROUP BY location_id;
我的理解是,在创建多列索引时,基数/选择性较高的列先行。我尝试使用两个索引键测试性能:
- (日期、地点 ID、类型、金额)
- (地点 ID、日期、类型、金额)
在我的实际表中,日期列中有 11,833 个唯一值,而 location_id 中只有 99 个。目前,有超过 63 万行。
尽管如此,MySQL 8 更喜欢使用以 location_id 开头的。即使当我尝试FORCE INDEX
时EXPLAIN ANALYZE
,它显示从 开始的成本/时间更高date
。
可能发生什么事?
编辑:
解释分析:
- 日期第一个索引
-> Group aggregate: sum(ledger_entries.amount_cents) (cost=1897 rows=6236) (actual time=0.167..4.67 rows=43 loops=1)
-> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale')))) (cost=1273 rows=6236) (actual time=0.0221..4.09 rows=6192 loops=1)
-> Covering index range scan on ledger_entries using index_le_date_location_type_amount_cents over (date = '2023-11-28' AND location_id = 3 AND type = NULL) OR (date = '2023-11-28' AND location_id = 3 AND type = 'Adjustment') OR (170 more) (cost=1273 rows=6236) (actual time=0.02..2.83 rows=6192 loops=1)
- 位置第一索引
-> Group aggregate: sum(ledger_entries.amount_cents) (cost=1888 rows=6236) (actual time=0.171..4.74 rows=43 loops=1)
-> Filter: ((ledger_entries.`date` = DATE'2023-11-28') and (ledger_entries.location_id in (3,4,6,7,8,10,11,12,14,18,19,22,23,24,28,29,30,31,32,36,39,40,41,43,45,46,48,49,50,51,52,54,55,56,57,59,60,61,62,68,69,75,121)) and ((ledger_entries.`type` is null) or (ledger_entries.`type` in ('Procedure','Adjustment','AncillarySale')))) (cost=1265 rows=6236) (actual time=0.0244..4.15 rows=6192 loops=1)
-> Covering index range scan on ledger_entries using ledger_entries_location_date_type_amount_cents over (location_id = 3 AND date = '2023-11-28' AND type = NULL) OR (location_id = 3 AND date = '2023-11-28' AND type = 'Adjustment') OR (170 more) (cost=1265 rows=6236) (actual time=0.022..2.91 rows=6192 loops=1)
GROUP BY location_id
。如果所选索引以 开头
location_id
,则处理可以跳过该索引,否则,将需要一个临时表和一个排序。
优化器没有足够的信息来确定哪个执行计划确实会更快,但上面的项目符号项目是它必须使用的最佳项目。
如果您想进一步讨论此问题,请提供
SHOW CREATE TABLE
和EXPLAIN ANALYZEs
。如果您还想在不使用所有索引列的查询中重用该索引,那么这是有意义的。如果您有 (a,b,c) 上的索引,它也将免费用作 (a,b) 和 (a) 上的索引。如果 (a) 和/或 (a,b) 具有良好的选择性(高基数),那么这些“自由”索引会更有用。否则,如果 (a) 的基数较低,则单独对 (a) 建立索引是无用的。
现在你的查询是:
使用 (location_id,date) 上的 btree 索引,它非常简单,算法如下:
(a,b,c) 上的 btree 索引按 (a,b,c) 排序,因此它支持对任何列子集进行范围查询,只要它们是 (a)、(a,b) 或 (a,公元前)。但不是任何其他组合或任何其他顺序。
嗯...现在我必须解释元组排序...就像按(姓氏,名字)排序。在这种情况下,对 (location_id,date) 之间 (1,'2022-01-02') 和 (1,'2022-01-04') 之间的范围查询将选择以下行:
...它所做的就是找到范围的第一行,然后按顺序读取索引行,直到范围的末尾,这非常快。因此,每个 loc_id 都有一个索引查找,然后读取范围。作为奖励,数据已经按 location_id 排序,因此不需要为分组依据做任何额外的工作。看起来不错。
使用 (date,location_id) 上的 btree 索引,情况要复杂得多。我们把之前的数据重新做一个有序索引。
这里的问题是索引列被交换,但范围查询仍然在与以前相同的列上。依然是那个日期。如果您在 (date,loc) 上有索引,它可以有效地对 (date) 进行范围查询,但索引不会按 loc 进行过滤。这必须在从索引读取行之后完成。让我们对“2022-01-02”和“2022-01-04”之间的日期进行范围查询:
因此,它将扫描并读取许多带有 location_id 的行,这些行不在您的(大列表)中,然后将它们丢弃。如果日期范围很小,这仍然很好,最好读取表的 1% 并扔掉其中的大部分,而不是没有索引,读取整个表并扔掉其中的大部分,最终得到相同的结果。
此外,结果行不是按 location_id 排序的,因此分组需要额外的工作。
因此,查询计划的选择是合乎逻辑的。
索引也可以用来避免排序,因此 (loc,date) 上的索引将优化“WHERE loc=... AND date BETWEEN ... ORDER B date”,但如果查询有“loc IN”,则它将不起作用(...)" 因为这样它确实会按日期顺序读取几个块,但它仍然必须对整个结果进行排序。