M201: MongoDB Performance chapter 2 Mongodb Indexes学习记录

原创 2018年04月17日 10:30:24

M201: MongoDB Performance chapter 2 Mongodb Indexes学习记录

运行环境

操作系统:windows 10 家庭中文版
Mongodb :Mongodb 3.4

Mongodb安装路径:E:>MongoDB\Server\3.4\bin\
Mongodb存储路径:E:>MongoDB\data

课后问题

lab1

In this lab you’re going to determine which queries are able to successfully use a given index for both filtering and sorting.

Given the following index:

{ "first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1 }

Which of the following queries are able to use it for both filtering and sorting?

Check all that apply:

  • db.people.find({“address.city”: “West Cindy”}).sort({“address.city” : -1 })
  • db.people.find({“first_name”: {$gt: “J”}}).sort({“address.city”: -1})
  • db.people.find({“first_name”:”Jessica”}).sort({“address.state”: 1,”address.city” : 1})
  • db.people.find({“first_name”: “Jessica”,”address.state”: {$lt:”S”}}).sort({“address.state”:1})
  • db.people.find({“address.state”:”South Dakota”,”first_name”: “Jessica”}).sort({“address.city”: -1})

解答

启动mongod守护进程:

C:\Users\Shinelon>e:

E:\>MongoDB\Server\3.4\bin\mongod.exe --dbpath MongoDB\data
2018-04-16T08:43:34.604-0700 I CONTROL  [initandlisten] MongoDB starting : pid=7500 port=27017 dbpath=MongoDB\data 64-bit host=DESKTOP-MP9NVQ7
2018-04-16T08:43:34.605-0700 I CONTROL  [initandlisten] targetMinOS: Windows 7/Windows Server 2008 R2
2018-04-16T08:43:34.605-0700 I CONTROL  [initandlisten] db version v3.4.6
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] git version: c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1u-fips  22 Sep 2016
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] allocator: tcmalloc
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] modules: none
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] build environment:
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten]     distmod: 2008plus-ssl
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten]     distarch: x86_64
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten]     target_arch: x86_64
2018-04-16T08:43:34.606-0700 I CONTROL  [initandlisten] options: { storage: { dbPath: "MongoDB\data" } }
2018-04-16T08:43:34.634-0700 I -        [initandlisten] Detected data files in MongoDB\data created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2018-04-16T08:43:34.635-0700 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=7656M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2018-04-16T08:43:35.494-0700 I CONTROL  [initandlisten]
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten]
2018-04-16T23:43:35.738+0800 I FTDC     [initandlisten] Initializing full-time diagnostic data capture with directory 'MongoDB/data/diagnostic.data'
2018-04-16T23:43:35.741+0800 I NETWORK  [thread1] waiting for connections on port 27017

进入mongo shell:

E:\>MongoDB\Server\3.4\bin\mongo.exe
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
Server has startup warnings:
2018-04-16T08:43:35.494-0700 I CONTROL  [initandlisten]
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2018-04-16T08:43:35.495-0700 I CONTROL  [initandlisten]

切换至m201库:

> use m201
switched to db m201

创建题目给出的索引:

> db.people.ensureIndex({ "first_name": 1, "address.state": -1, "address.city": -1, "ssn": 1 })
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

依次查看5个查询的执行计划:

  • 第一个:
> db.people.find({"address.city": "West Cindy"}).sort({"address.city" : -1 }).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "address.city" : {
                                "$eq" : "West Cindy"
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "address.city" : -1
                        },
                        "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                        "stage" : "COLLSCAN",
                                        "filter" : {
                                                "address.city" : {
                                                        "$eq" : "West Cindy"
                                                }
                                        },
                                        "direction" : "forward"
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "DESKTOP-MP9NVQ7",
                "port" : 27017,
                "version" : "3.4.6",
                "gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
        },
        "ok" : 1
}

注意到使用的是COLLSCAN(全表扫描)

第二个

> db.people.find({"first_name": {$gt: "J"}}).sort({"address.city": -1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "first_name" : {
                                "$gt" : "J"
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "address.city" : -1
                        },
                        "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR",
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "first_name" : 1,
                                                        "address.state" : -1,
                                                        "address.city" : -1,
                                                        "ssn" : 1
                                                },
                                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                                "isMultiKey" : false,
                                                "multiKeyPaths" : {
                                                        "first_name" : [ ],
                                                        "address.state" : [ ],
                                                        "address.city" : [ ],
                                                        "ssn" : [ ]
                                                },
                                                "isUnique" : false,
                                                "isSparse" : false,
                                                "isPartial" : false,
                                                "indexVersion" : 2,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "first_name" : [
                                                                "(\"J\", {})"
                                                        ],
                                                        "address.state" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "address.city" : [
                                                                "[MaxKey, MinKey]"
                                                        ],
                                                        "ssn" : [
                                                                "[MinKey, MaxKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "DESKTOP-MP9NVQ7",
                "port" : 27017,
                "version" : "3.4.6",
                "gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
        },
        "ok" : 1
}

注意只有sorting使用的是SORT_KEY_GENERATOR(内存排序),filtering用的IXSCAN(索引)

第三个:

> db.people.find({"first_name":"Jessica"}).sort({"address.state": 1,"address.city" : 1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "first_name" : {
                                "$eq" : "Jessica"
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "first_name" : 1,
                                        "address.state" : -1,
                                        "address.city" : -1,
                                        "ssn" : 1
                                },
                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "first_name" : [ ],
                                        "address.state" : [ ],
                                        "address.city" : [ ],
                                        "ssn" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "backward",
                                "indexBounds" : {
                                        "first_name" : [
                                                "[\"Jessica\", \"Jessica\"]"
                                        ],
                                        "address.state" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "address.city" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "ssn" : [
                                                "[MaxKey, MinKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "DESKTOP-MP9NVQ7",
                "port" : 27017,
                "version" : "3.4.6",
                "gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
        },
        "ok" : 1
}

符合题意

第四个:

> db.people.find({"first_name": "Jessica","address.state": {$lt:"S"}}).sort({"address.state":1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "first_name" : {
                                                "$eq" : "Jessica"
                                        }
                                },
                                {
                                        "address.state" : {
                                                "$lt" : "S"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "first_name" : 1,
                                        "address.state" : -1,
                                        "address.city" : -1,
                                        "ssn" : 1
                                },
                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "first_name" : [ ],
                                        "address.state" : [ ],
                                        "address.city" : [ ],
                                        "ssn" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "backward",
                                "indexBounds" : {
                                        "first_name" : [
                                                "[\"Jessica\", \"Jessica\"]"
                                        ],
                                        "address.state" : [
                                                "[\"\", \"S\")"
                                        ],
                                        "address.city" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "ssn" : [
                                                "[MaxKey, MinKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "DESKTOP-MP9NVQ7",
                "port" : 27017,
                "version" : "3.4.6",
                "gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
        },
        "ok" : 1
}

符合题意

第五个:

> db.people.find({"address.state":"South Dakota","first_name": "Jessica"}).sort({"address.city": -1}).explain()
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "m201.people",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "address.state" : {
                                                "$eq" : "South Dakota"
                                        }
                                },
                                {
                                        "first_name" : {
                                                "$eq" : "Jessica"
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "first_name" : 1,
                                        "address.state" : -1,
                                        "address.city" : -1,
                                        "ssn" : 1
                                },
                                "indexName" : "first_name_1_address.state_-1_address.city_-1_ssn_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "first_name" : [ ],
                                        "address.state" : [ ],
                                        "address.city" : [ ],
                                        "ssn" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "first_name" : [
                                                "[\"Jessica\", \"Jessica\"]"
                                        ],
                                        "address.state" : [
                                                "[\"South Dakota\", \"South Dakota\"]"
                                        ],
                                        "address.city" : [
                                                "[MaxKey, MinKey]"
                                        ],
                                        "ssn" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "DESKTOP-MP9NVQ7",
                "port" : 27017,
                "version" : "3.4.6",
                "gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
        },
        "ok" : 1
}

符合题意

所以3,4,5为所求

lab2

In this lab you’re going to examine several example queries and determine which compound index will best service them.

> db.people.find({
    "address.state": "Nebraska",
    "last_name": /^G/,
    "job": "Police officer"
  })
> db.people.find({
    "job": /^P/,
    "first_name": /^C/,
    "address.state": "Indiana"
  }).sort({ "last_name": 1 })
> db.people.find({
    "address.state": "Connecticut",
    "birthday": {
      "$gte": ISODate("2010-01-01T00:00:00.000Z"),
      "$lt": ISODate("2011-01-01T00:00:00.000Z")
    }
  })

If you had to build one index on the people collection, which of the following indexes would best sevice all 3 queries?

Choose the best answer:

  1. { “job”: 1, “address.state”: 1 }
  2. { “job”: 1, “address.state”: 1 ,”last_name”: 1}
  3. {“address.state”: 1, “job”: 1}
  4. {“address.state”: 1, “job”: 1, “first_name”: 1}
  5. {“address.state”: 1,”last_name”: 1, “job”: 1}
  6. { “job”: 1, “address.state”: 1 ,”first_name”: 1}

解答

分析:

第一个查询只包含范围查询,无排序。索引包含address.state,last_name,job这3个字段即可,适合的索引为:

5=2>1=3>4=6

第二个查询需要根据last_name排序,所以最适合他的索引是包含job,first_name,address.state,last_name字段,并且last_name字段排在最后的索引,适合的索引为:

2>5>4=6>1=3

第三个查询需要根据birthday排序(无相关索引),且要包含address.state字段,适合的索引为:

3>4=5>1=2=6

综上分析,只有5是最合适的

mongodb3.0 单机数据迁移 分片复制集

1,启用分片 1,启用分片 mongos> use admin switched to db admin mongos> db.runCommand({enablesharding:"test"})...
  • joeyon
  • joeyon
  • 2015-05-28 14:17:04
  • 1333

M201: MongoDB Performance chapter 3 Index Operations学习记录

M201: MongoDB Performance chapter 3 Index Operations学习记录 运行环境 操作系统:windows 10 家庭中文版 Mongodb :Mo...
  • sunbocong
  • sunbocong
  • 2018-04-22 23:47:08
  • 4

MongoDB 部分索引(Partial Indexes)

MongoDB部分索引只为那些在一个集合中,满足指定的筛选条件的文档创建索引。由于部分索引是一个集合文档的一个子集,因此部分索引具有较低的存储需求,并降低了索引创建和维护的性能成本。部分索引通过指定过...
  • robinson_0612
  • robinson_0612
  • 2016-12-27 15:34:06
  • 1830

MongoDB备份-mongodump和恢复-mongorestore

备份 mongodump -h dbhost -d dbname -o dbdir上述语法中: -h MongDB所在服务器,可以附带指定端口号‘:port’ -d 需要备份的数据库实例 -o 备份数...
  • jc_benben
  • jc_benben
  • 2017-08-06 21:52:35
  • 993

M201: MongoDB Performance chapter 1 Introduction学习记录

M201: MongoDB Performance chapter 1 Introduction学习记录 运行环境 操作系统:windows 10 家庭中文版 Mongodb :Mongod...
  • sunbocong
  • sunbocong
  • 2018-04-16 23:47:51
  • 9

M201: MongoDB Performance chapter 4 CRUD Operations学习记录

M201: MongoDB Performance chapter 4 CRUD Operations学习记录 运行环境 操作系统:windows 10 家庭中文版 Mongodb :Mon...
  • sunbocong
  • sunbocong
  • 2018-04-23 11:45:07
  • 9

MongoDB in Action Second Edition笔记之Text Index

创建Text index 创建Text index并指定权重 为域的成员创建Text index Text index 大小 手动指定Text index名称 全域Text index Text in...
  • KingEasternSun
  • KingEasternSun
  • 2016-09-30 16:21:17
  • 383

MongoDB Performance Best Practices

  • 2017年11月08日 11:24
  • 982KB
  • 下载

The.Definitive.Guide.to.MongoDB.3rd.Edition.1484211839

  • 2015年12月23日 14:58
  • 4.54MB
  • 下载
收藏助手
不良信息举报
您举报文章:M201: MongoDB Performance chapter 2 Mongodb Indexes学习记录
举报原因:
原因补充:

(最多只允许输入30个字)