京公网安备11010502063487号复合索引的字段顺序到底怎么排?ESR 规则是不是总是对的?这篇用 30 万个文档,四个顺序不同的索引,在带 limit 和不带 limit 的同一条查询上逐个 hint,对比计划、键数和耗时。
test> db.t.countDocuments({status: "open", score: {$gte: 900}})
6000等值(E)、排序(S)、范围(R)三个字段的不同排列:E-S-R、E-R-S、R-E-S、S-E-R,都用 hint 逐个强制一次。
test> hint("E-S-R") // 字段顺序 status , ts , score
{
plan: 'LIMIT > FETCH > IXSCAN(E-S-R)',
nReturned: 20,
keysExamined: 195,
docsExamined: 20,
ms: 3
}
test> hint("E-R-S") // 字段顺序 status , score , ts
{
plan: 'FETCH > SORT > IXSCAN(E-R-S)',
nReturned: 20,
keysExamined: 6000,
docsExamined: 20,
ms: 3
}
test> hint("R-E-S") // 字段顺序 score , status , ts
{
plan: 'FETCH > SORT > IXSCAN(R-E-S)',
nReturned: 20,
keysExamined: 6080,
docsExamined: 20,
ms: 6
}
test> hint("S-E-R") // 字段顺序 ts , status , score
{
plan: 'LIMIT > FETCH > IXSCAN(S-E-R)',
nReturned: 20,
keysExamined: 975,
docsExamined: 20,
ms: 4
}E-S-R 能利用索引顺序直接取排序后的前 20 条,只检查 195 个键;E-R-S 和 R-E-S 要先读完 6000 个键再内存排序。
test> hint("E-S-R") // 字段顺序 status , ts , score,无 limit
{
plan: 'FETCH > IXSCAN(E-S-R)',
nReturned: 6000,
keysExamined: 60001,
docsExamined: 6000,
ms: 48
}
test> hint("E-R-S") // 字段顺序 status , score , ts,无 limit
{
plan: 'FETCH > SORT > IXSCAN(E-R-S)',
nReturned: 6000,
keysExamined: 6000,
docsExamined: 6000,
ms: 7
}
test> hint("R-E-S") // 字段顺序 score , status , ts,无 limit
{
plan: 'FETCH > SORT > IXSCAN(R-E-S)',
nReturned: 6000,
keysExamined: 6080,
docsExamined: 6000,
ms: 7
}
test> hint("S-E-R") // 字段顺序 ts , status , score,无 limit
{
plan: 'FETCH > IXSCAN(S-E-R)',
nReturned: 6000,
keysExamined: 300000,
docsExamined: 6000,
ms: 94
}排序要给出全部 6000 条:E-S-R 虽然没有 SORT,却要扫 60,001 个键才凑够结果;E-R-S 只读 6000 个键,在内存里排序 6000 条很便宜,总耗时更短。
索引 limit 20 无 limit E-S-R 0.3 ms 37 ms E-R-S 2.3 ms 9.3 ms R-E-S 2.5 ms 10 ms S-E-R 0.5 ms 80 ms
同一个索引在两种查询上表现相反,说明 ESR 是经验规则,要结合 limit 和结果集大小判断。
test> 不加 hint,limit 20
{
plan: 'LIMIT > FETCH > IXSCAN(E-S-R)',
nReturned: 20,
keysExamined: 195,
docsExamined: 20,
ms: 0
}
test> 不加 hint,无 limit
{
plan: 'FETCH > IXSCAN(E-S-R)',
nReturned: 6000,
keysExamined: 60001,
docsExamined: 6000,
ms: 39
}不 hint 时优化器两次都选了 E-S-R;无 limit 时它不是最快的。计划靠试跑最初的一小段 works 来选,不保证全局最优。
test> hint("E-S-R")
{
plan: 'FETCH > IXSCAN(E-S-R)',
nReturned: 300,
keysExamined: 60001,
docsExamined: 300,
ms: 36
}
test> hint("E-R-S")
{
plan: 'FETCH > IXSCAN(E-R-S)',
nReturned: 300,
keysExamined: 300,
docsExamined: 300,
ms: 0
}当条件全是等值时,把等值字段放前面、排序字段放最后(E-R-S)只需检查 300 个键,E-S-R 要检查 60,001 个。