思享工具箱

MongoDB 复合索引的试验

摘要：
您将了解｛userId:vs｛status：｛“namespace”：

根据典型碰到的场景，来做几个实验：
这里创建了个loans collection。简化只有100条数据。这个是借贷的表有 _id, userId, status(借贷状态), amount(金额).

看完 这个实验后， 你会明白了 {userId:1, status:1}, vs {status:1,userId:1} 的差别

PS：这个case 里面其实status 区分度不高，不应该建立的，这里只是作为实例展示。

总结：

  • 注意使用上 使用频率上 区分高的/常用的在前面
  • 如果需要减少索引以节省memory/提高修改数据的性能的话，可以保留区分度高，常用的，去除区分度不高，不常用的索引。

实验如下：

db.loans.count()
100

db.loans.find({ "userId" : "59e022d33f239800129c61c7", "status" : "repayed", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "repayed"
}
},
{
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
}
]
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"status" : {
"$eq" : "repayed"
}
},
{
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
}
]
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

注意上面 COLLSCAN 全表扫描了。因为没有索引。
next 我们分别建立几个索引

step 1 先建立 {userId:1, status:1}

db.loans.createIndex({userId:1, status:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}

db.loans.find({ "userId" : "59e022d33f239800129c61c7", "status" : "repayed", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "repayed"
}
},
{
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"["repayed", "repayed"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

如愿命中 {userId:1, status:1} 作为 winning plan

step2 再建立个典型的索引 userId

db.loans.createIndex({userId:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}

db.loans.find({ "userId" : "59e022d33f239800129c61c7", "status" : "repayed", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "repayed"
}
},
{
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"["repayed", "repayed"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "repayed"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1
},
"indexName" : "userId_1",
"multiKeyPaths" : {
"userId" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
]
}
}
}
]
},
"ok" : 1
}

留意到 DB 检测到 {userId:1, status:1} 为更优执行的方案

db.loans.find({ "userId" : "59e022d33f239800129c61c7" }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1
},
"indexName" : "userId_1",
"multiKeyPaths" : {
"userId" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"[MinKey, MaxKey]"
]
}
}
}
]
},
"ok" : 1
}

留意到 DB 检测到 {userId:1} 为更优执行的方案，嗯~，如我们所料

db.loans.find({ "status" : "repayed" }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"status" : {
"$eq" : "repayed"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"status" : {
"$eq" : "repayed"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

***有趣的部分： status 不命中索引， 全表扫描 ***
接下来，我加了个sort

db.loans.find({ "userId" : "59e022d33f239800129c61c7" }).sort({status:1}).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"status" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1
},
"indexName" : "userId_1",
"multiKeyPaths" : {
"userId" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
]
}
}
}
}
}
]
},
"ok" : 1
}

***有趣的部分： status 不命中索引 ***

db.loans.find({ "status" : "repayed","userId" : "59e022d33f239800129c61c7", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"$and" : [
{
"status" : {
"$eq" : "repayed"
}
},
{
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"["repayed", "repayed"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "repayed"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1
},
"indexName" : "userId_1",
"multiKeyPaths" : {
"userId" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
]
}
}
}
]
},
"ok" : 1
}

命中索引， 跟 query 的各个字段顺序不相关，如我们猜测

有趣部分再来， 我们删掉索引{userId:1}

db.loans.dropIndex({"userId":1})
{ "nIndexesWas" : 3, "ok" : 1 }

db.loans.find({"userId" : "59e022d33f239800129c61c7", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"["59e022d33f239800129c61c7", "59e022d33f239800129c61c7"]"
],
"status" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

DB 执行分析器觉得索引{userId:1, status:1} 能更优

没有命中复合索引 ，这个是因为status 不是 leading field

db.loans.find({ "status" : "repayed" }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"status" : {
"$eq" : "repayed"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"status" : {
"$eq" : "repayed"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

再换个角度sort 一遍， 与前面query & sort 互换 ，之前是

db.loans.find({userId:1}).sort({ "status" : "repayed" })
看看有啥不一样？

db.loans.find({ "status" : "repayed" }).sort({userId:1}).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"status" : {
"$eq" : "repayed"
}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"status" : {
"$eq" : "repayed"
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"userId" : 1,
"status" : 1
},
"indexName" : "userId_1_status_1",
"multiKeyPaths" : {
"userId" : [ ],
"status" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"userId" : [
"[MinKey, MaxKey]"
],
"status" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

如猜测，命中索引

再来玩1玩，确认下leading filed试验：

db.loans.dropIndex("userId_1_status_1")
{ "nIndexesWas" : 2, "ok" : 1 }

db.loans.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "id",
"ns" : "cashLoan.loans"
}
]

db.loans.createIndex({status:1, userId:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}

db.loans.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "id",
"ns" : "cashLoan.loans"
},
{
"v" : 2,
"key" : {
"status" : 1,
"userId" : 1
},
"name" : "status_1_userId_1",
"ns" : "cashLoan.loans"
}
]

db.loans.find({ "status" : "repayed" }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"status" : {
"$eq" : "repayed"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"status" : 1,
"userId" : 1
},
"indexName" : "status_1_userId_1",
"multiKeyPaths" : {
"status" : [ ],
"userId" : [ ]
},
"direction" : "forward",
"indexBounds" : {
"status" : [
"["repayed", "repayed"]"
],
"userId" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

status_1_userId_1 有这个索引的前提，去查 leading fields -- status: xx 会中

db.loans.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "id",
"ns" : "cashLoan.loans"
},
{
"v" : 2,
"key" : {
"status" : 1,
"userId" : 1
},
"name" : "status_1_userId_1",
"ns" : "cashLoan.loans"
}
]

db.loans.find({"userId" : "59e022d33f239800129c61c7", }).explain()
{
"queryPlanner" : {
"namespace" : "cashLoan.loans",
"parsedQuery" : {
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"userId" : {
"$eq" : "59e022d33f239800129c61c7"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"ok" : 1
}

status_1_userId_1 有这个索引的前提，去查 非leading fields -- user_id: xx 没中，全表扫描

所以 注意使用上 使用频率上 区分高的/常用的， 应该使用于混合索引，在前面作为leading fields，

标签：#status#userId#: | 浏览：281 | 发布日期：

免责声明：文章转载自《MongoDB 复合索引的试验》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇一行css代码搞定响应式布局分享8款办公或开发中简单好用的小工具下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

【代码笔记】iOS-多张图片合成一张

代码：   RootViewController.m   #import "RootViewController.h" @interface RootViewController () @end @implementation RootViewController - (id)initWithNibName:(NSString...

Let's GO(四）

人生苦短，Let's GO Let's GO(一) Let's GO(二) Let's GO(三) Let's GO(四) 今天我学了什么？ 1.panic && recover Go的logo是一只萌萌的囊地鼠(Gopher) 当Go程序出现错误，程序将报panic(恐慌) 所以是错误代码吓到小地鼠了吗哈哈 然后需要用recover来...

天气插件（vue）和风天气插件

本来项目中用的中国天气，今天突然发现天气不显示了，突然报错net::ERR_SSL_VERSION_OR_CIPHER_MISMATCH 然后上中国天气管网去看，发现登陆都报错404了。。。 当时就一口老血。。。 后来翻了翻有采用了和两个样式差不多的和风天气，从两个人的管网体验和风就胜出了一大截。在使用上两者基本相同。 和风天气生成插件的自定义样式的网址 ...

Vue Element 使用 icon 图标 (第三方)

Vue Element 使用 icon 图标 (第三方) element-ui 自带的图标库还是不够全, 还是需要需要引入第三方 icon, 自己在用的时候一直有些问题, 参考了些教程, 详细地记录补充下 对于我们来说, 首选的当然是阿里 icon 库 教程: 1. 打开阿里 icon, 注册 > 登录 > 图标管理 > 我的项目 图标...

Linux链接脚本学习--lds(转）

Linux链接脚本学习--lds 一、概论 ld: GNU的链接器. 用来把一定量的目标文件跟档案文件链接在一起,并重新定位它们的数据,链接符号引用. 一般编译一个程序时,最后一步就是运行ld进行链接 每一个链接都被一个链接脚本所控制,这个脚本是用链接命令语言书写的. 二、链接脚本 链接脚本的一个主要目的是描述输入文件中的各个段(数据段,代码段,堆,...

vue+element-ui el-table表格(含表头)内容溢出省略，鼠标悬浮提示

第一种:参考：https://my.oschina.net/u/3455362/blog/4674804 <template> <div class="test"> <el-table :data="gridData" border stripe style=" 100%"> &...

最新文章

随机推荐

思享工具箱导航