Shulou(Shulou.com)05/31 Report--

This article is about how to use the composite index in MongoDB, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

What is a composite index?

A composite index, or Compound Index, refers to the combination of multiple keys to create an index, which speeds up queries that match multiple keys. You might as well understand the composite index through a simple example.

The collection of students is as follows:

Db.students.find () .pretty () {"_ id": ObjectId ("5aa7390ca5be7272a99b042a"), "name": "zhang", "age": "15"} {"_ id": ObjectId ("5aa7393ba5be7272a99b042b"), "name": "wang", "age": "15"} {"_ id": ObjectId ("5aa7393ba5be7272a99b042c"), "name": "zhang", "age": "14"}

Indexes are created at name and age (_ id comes with its own index):

Db.students.getIndexes () [{"v": 1, "key": {"name": 1}, "name": "name_1", "ns": "test.students"}, {"v": 1, "key": {"age": 1}, "name": "age_1", "ns": "test.students"}]

When making a multi-key query, you can analyze the execution with explian () (only winningPlan remains as a result):

Db.students.find ({name: "zhang", age: "14"}). Explain () "winningPlan": {"stage": "FETCH", "filter": {"name": {"$eq": "zhang"}}, "inputStage": {"stage": "IXSCAN", "keyPattern": {"age": 1}, "indexName": "age_1", "isMultiKey": false, "isUnique": false "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"age": [[\ "14\",\ "14\"]]}

As you can see from winningPlan, this query is divided into two phases: IXSCAN and FETCH. IXSCAN is index scanning, using age index; FETCH is to query documents according to the index, which needs to be filtered by name.

Create a composite index for name and age:

Db.students.createIndex ({name:1,age:1}) db.students.getIndexes () [{"v": 1, "key": {"name": 1, "age": 1}, "name": "name_1_age_1", "ns": "test.students"}]

With a composite index, the same query is executed differently:

Db.students.find ({name: "zhang", age: "14"}). Explain () "winningPlan": {"stage": "FETCH", "inputStage": {"stage": "IXSCAN", "keyPattern": {"name": 1, "age": 1}, "indexName": "name_1_age_1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1 "direction": "forward", "indexBounds": {"name": ["[\" zhang\ ",\" zhang\ "]]," age ": [[\" 14\ ",\" 14\ "]]}

As can be seen from winningPlan, the order of this query remains unchanged, which is divided into two phases: IXSCAN and FETCH. However, IXSCAN uses a composite index of name and age; FETCH queries documents based on the index without filtering.

The amount of data in this example is too small to see anything wrong. But in fact, when the amount of data is large and IXSCAN returns a large number of indexes, filtering at FETCH will be very time-consuming. Next, we will introduce a real case.

Locate MongoDB performance issu

With the increasing number of error data received, our Fundebug has accumulated 350 million error events, which poses continuous performance challenges to our services, especially for MongoDB clusters.

For the production database, configure profile to record the performance data of MongoDB. Execute the following command, and all database reads and writes longer than 1 second will be recorded.

Db.setProfilingLevel (1pc1000)

If you query the data recorded by profile, you will find that a query of the events collection is very slow:

Db.system.profile.find (). Pretty () {"op": "command", "ns": "fundebug.events", "command": {"count": "events", "query": {"createAt": {"$lt": ISODate ("2018-02-05T20:30:00.073Z")}, "projectId": ObjectId ("58211791ea2640000c7a3fe6")}}, "keyUpdates": 0, "writeConflicts": 0 "numYield": 1414, "locks": {"Global": {"acquireCount": {"r": NumberLong (2830)}, "Database": {"acquireCount": {"r": NumberLong (1415)}}, "Collection": {"acquireCount": {"r": NumberLong (1415)}, "responseLength": 62, "protocol": "op_query" Millis: 28521, execStats: {}, ts: ISODate ("2018-03-07T20:30:59.440Z"), "client": "192.168.59.226", "allUsers": [], "user": ""}

There are hundreds of millions of documents in the events collection, so it's not surprising that count operations are slow. According to profile data, this query takes 28.5s, which is a bit ridiculously long. In addition, the numYield is as high as 1414, which should be the direct reason why the operation is so slow. According to the MongoDB documentation, numYield means something like this:

The number of times the operation yielded to allow other operations to complete. Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete while MongoDB reads in data for the yielding operation.

This means that a lot of time is spent reading the hard drive and reading it many times. It can be speculated that it should be caused by the index problem.

You might as well use explian () to analyze this query (leaving only executionStats):

Db.events.explain ("executionStats") .count ({"projectId": ObjectId ("58211791ea2640000c7a3fe6"), createAt: {"$lt": ISODate ("2018-02-05T20:30:00.073Z")}) "executionStats": {"executionSuccess": true, "nReturned": 20853, "executionTimeMillis": 28055, "totalKeysExamined": 28338, "totalDocsExamined": 28338, "executionStages": {"stage": "FETCH" "filter": {"createAt": {"$lt": ISODate ("2018-02-05T20:30:00.073Z")}}, "nReturned": 20853, "executionTimeMillisEstimate": 27815, "works": 28339, "advanced": 20853, "needTime": 7485, "needYield": 0, "saveState": 1387, "restoreState": 1387, "isEOF": 1, "invalidates": 0, "docsExamined": 28338, "alreadyHasObj": 0 "inputStage": {"stage": "IXSCAN", "nReturned": 28338, "executionTimeMillisEstimate": 30, "works": 28339, "advanced": 28338, "needTime": 0, "needYield": 0, "saveState": 1387, "restoreState": 1387, "isEOF": 1, "invalidates": 0, "keyPattern": {"projectId": 1}, "indexName": "projectId_1" "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": {"projectId": [[ObjectId ('58211791ea2640000c7a3fe6'), ObjectId (' 58211791ea2640000c7a3fe6')]}, "keysExamined": 28338, "dupsTested": 0, "dupsDropped": 0, "seenInvalidated": 0}}

We know that the events collection does not establish a composite index for projectId and createAt, so the IXSCAN phase uses the projectId index, and its nReturned is 28338; FETCH phase needs to be filtered according to createAt, and its nReturned is 20853, filtering out 7485 documents; in addition, the executionTimeMillisEstimate of IXSCAN and FETCH phase is 30ms and 27815ms, respectively, so basically all time is spent in the FETCH phase, which should be caused by reading the hard drive.

Create a composite index

Not creating a composite index for projectId and createAt is an embarrassing mistake, so fix it:

Db.events.createIndex ({projectId:1,createTime:-1}, {background: true})

Building an index in a production environment is best done at night, and this command took about 7 hours! Background is set to true, which means that you do not block other operations of the database to ensure the availability of the database. However, this command will occupy the terminal all the time, and you cannot use CTRL + C at this time, otherwise the index construction process will be terminated.

After the creation of the composite index, the previous query is much faster (only executionStats is retained):

Db.javascriptevents.explain ("executionStats") .count ({"projectId": ObjectId ("58211791ea2640000c7a3fe6"), createAt: {"$lt": ISODate ("2018-02-05T20:30:00.073Z")}) "executionStats": {"executionSuccess": true, "nReturned": 0, "executionTimeMillis": 47, "totalKeysExamined": 20854, "totalDocsExamined": 0, "executionStages": {"stage": "COUNT", "nReturned": 0, "executionTimeMillisEstimate": 50, "works": 20854 "advanced": 0, "needTime": 20853, "needYield": 0, "saveState": 162,162,162,162, "isEOF": 1, "invalidates": 0, "nCounted": 20853, "nSkipped": 0, "inputStage": {"stage": "COUNT_SCAN", "nReturned": 20853, "executionTimeMillisEstimate": 50, "works": 20854, "advanced": 20853, "needTime": 0, "needYield" 0: "saveState": 162,162 "restoreState", "isEOF": 1, "invalidates": 0, "keysExamined": 20854, "keyPattern": {"projectId": 1, "createAt":-1}, "indexName": "projectId_1_createTime_-1", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false "indexVersion": 1} above is how to use composite indexes in MongoDB The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.