假定：
- 某个集合有复合索引
{start_time:1, end_time:1} - 此时查询条件为
{start_time: {$gt: "2020-01-01 00:00:00"}, end_time: {$lt: "2020-02-01 00:00:00"}}
Q1: 能否命中end_time？
Q2: 查询是否相比只添加单索引{start_time}速度更快了？
对索引最左匹配原则耳濡目染的小伙伴一定会回答：No！
熟悉B+树底层结构的小伙伴一定会回答：No!
结论是：
A1：end_time没命中。
A2：复合索引{start_time:1, end_time:1}比单索引{start_time:1}查询速度要快得多。
既然end_time没命中，那为什么复合索引{start_time:1, end_time:1}更快？有什么黑科技吗？通过对比两个样例来一探究竟。
搭建测试环境
表结构
| 键 | 类型 | 说明 |
|---|---|---|
| _id | ObjectId | 主键 |
| rand | number | 随机数字，范围[0, 100000) |
| rand2 | number | 随机数字，范围[0, 100000) |
添加索引
db.user.ensureIndex({rand:1})
db.user.ensureIndex({rand:1, rand2:1})
填充2500w数据
for (j=0; j<50; j++) {
items = []; for (i=0; i<500000; ++i) {
items.push({rand: Math.random() * 100000, rand2: Math.random() * 100000});
} db.user.insertMany(items)
}
样例A
执行语句
db.user.find({rand: {$gt: 80000}, rand2: {$lt: 100}}).hint('rand_1')
执行结果
耗时29秒
image.png
执行计划
用explain查看executionStats
{
"executionSuccess" : true,
"nReturned" : 4951,
"executionTimeMillis" : 23011,
"totalKeysExamined" : 4997312,
"totalDocsExamined" : 4997312,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"rand2" : {
"$lt" : 100
}
},
"nReturned" : 4951,
"executionTimeMillisEstimate" : 4121,
"works" : 4997313,
"advanced" : 4951,
"needTime" : 4992361,
"needYield" : 0,
"saveState" : 39165,
"restoreState" : 39165,
"isEOF" : 1,
"docsExamined" : 4997312,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4997312,
"executionTimeMillisEstimate" : 161,
"works" : 4997313,
"advanced" : 4997312,
"needTime" : 0,
"needYield" : 0,
"saveState" : 39165,
"restoreState" : 39165,
"isEOF" : 1,
"keyPattern" : {
"rand" : 1
},
"indexName" : "rand_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"rand" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"rand" : [
"(80000.0, inf.0]"
]
},
"keysExamined" : 4997312,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
样例B
执行语句
db.user.find({rand: {$gt: 80000}, rand2: {$lt: 100}}).hint('rand_1_rand2_1')
执行结果
耗时132毫秒
image.png
执行计划
用explain查看executionStats
{
"executionSuccess" : true,
"nReturned" : 4951,
"executionTimeMillis" : 8683,
"totalKeysExamined" : 4997312,
"totalDocsExamined" : 4951,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 4951,
"executionTimeMillisEstimate" : 216,
"works" : 4997313,
"advanced" : 4951,
"needTime" : 4992361,
"needYield" : 0,
"saveState" : 39041,
"restoreState" : 39041,
"isEOF" : 1,
"docsExamined" : 4951,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4951,
"executionTimeMillisEstimate" : 212,
"works" : 4997313,
"advanced" : 4951,
"needTime" : 4992361,
"needYield" : 0,
"saveState" : 39041,
"restoreState" : 39041,
"isEOF" : 1,
"keyPattern" : {
"rand" : 1,
"rand2" : 1
},
"indexName" : "rand_1_rand2_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"rand" : [ ],
"rand2" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"rand" : [
"(80000.0, inf.0]"
],
"rand2" : [
"[-inf.0, 100.0)"
]
},
"keysExamined" : 4997312,
"seeks" : 4992362,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
样例 A vs B 比对分析
执行过程比对
主要分析样例B为什么快。
- 样例B是否命中了复合索引
{rand:1, rand2:1}里的rand2？
其实没命中。从keysExamined就可以看出，样例A和样例B索引扫描Key数量是一样。因此推测两个样例都只用到了索引中的rand。（画外音：假设命中了rand2，扫描量就不可能和没命中rand2的一样） - 为什么样例A
nReturned: 4997312，样例BnReturned: 4951？
因为样例B中的索引节点包含rand2信息，因此在索引扫描的同时就把符合{rand2: {$lt: 100}}条件的数据"挑"出来了。（也是一种覆盖索引） -
docsExamined和keysExamined耗时差别为什么这么大？
docsExamined即文档扫描，文档的数据需要读磁盘（如果内存中没有的话）。keysExamined即索引扫描，属于纯粹的内存操作。
总结
在上述样例中，复合索引{rand:1, rand2:1}中的rand2虽然没有直接命中，但在索引扫描的过程中起到了filter的作用。于是到了docsExamined的数据量就没那么大了。
大方向上： docsExamined越小越好，在此基础上keysExamined越小越好。
方案上：
- 缩小
keysExamined。尽量使用B+树直接定位出最小的数据量。（例如：循环遍历时尽量用 WHERE `id` > xxx 而不是 LIMIT yyy, zzz） - 缩小
docsExamined。除了【方案1】以外，尽量使用覆盖索引检索出相对较小的数据量，此时耗时是内存级别的。
