我有一个类似于以下内容的查询:
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)