mongodb索引最佳实践

mongodb, 索引

前几天公司的一个核心服务突然断断续续抽风，访问奇慢，经排查定位是我们的mongdb数据库read响应很慢，有几张表的read响应时间居然达到8-10S，数据量实在不大，也就200W多一点，后面分析慢查询日志，根据日志判断，尝试了很多次加索引，磕磕碰碰弄了好几次才解决问题。这次事件给我一个很大的警醒，对于数据量与日俱增的生产系统，负责人一定要具备应对突发事件的技术能力。

因着这个事件，我专门抽出一整段时间来阅读了mongdb官方的索引文档，并逐一做了验证，将这些过程记录下来。

认识 explain()

在介入索引的实际内容之前，我们需要先认识一个工具explain()。 因为索引的理论知识其实很简单，我们需要知道去验证我们设置的索引是否有效，是否真的按照我们的想法在发挥作用，而explain()为我们提供了详细的反馈信息。

explain()是mongodb提供的一个专门用于分析mongdb操作执行效率的工具，它有三种模式：

  • queryPlanner

  • executionStats

  • allPlansExecution

其中 queryPlanner 为默认模式，而 allPlansExecution 信息最为详细，建议使用此模式进行分析。

我们来看一个实例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
// 执行分析
db.ServiceInvokerSeq.find( 
    { 
         "serviceId":ObjectId("556bf36bfa0bab5b3fd30189"),
         "sdCode" : "ORDER003", 
         "en" : "b1bdb597", 
    }
).explain("allPlansExecution");


// 返回信息
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "weipos.ServiceInvokerSeq",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "en" : {
                        "$eq" : "b1bdb597"
                    }
                },
                {
                    "sdCode" : {
                        "$eq" : "ORDER003"
                    }
                },
                {
                    "serviceId" : {
                        "$eq" : ObjectId("556bf36bfa0bab5b3fd30189")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "KEEP_MUTATIONS",
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "sdCode" : {
                        "$eq" : "ORDER003"
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "serviceId" : 1,
                        "en" : 1,
                        "time" : 1
                    },
                    "indexName" : "serviceId_1_en_1_time_1",
                    "isMultiKey" : false,
                    "direction" : "forward",
                    "indexBounds" : {
                        "serviceId" : [
                            "[ObjectId('556bf36bfa0bab5b3fd30189'), ObjectId('556bf36bfa0bab5b3fd30189')]"
                        ],
                        "en" : [
                            "[\"b1bdb597\", \"b1bdb597\"]"
                        ],
                        "time" : [
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 1367,
        "executionTimeMillis" : 3,
        "totalKeysExamined" : 1367,
        "totalDocsExamined" : 1367,
        "executionStages" : {
            "stage" : "KEEP_MUTATIONS",
            "nReturned" : 1367,
            "executionTimeMillisEstimate" : 0,
            "works" : 1368,
            "advanced" : 1367,
            "needTime" : 0,
            "needFetch" : 0,
            "saveState" : 10,
            "restoreState" : 10,
            "isEOF" : 1,
            "invalidates" : 0,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "sdCode" : {
                        "$eq" : "ORDER003"
                    }
                },
                "nReturned" : 1367,
                "executionTimeMillisEstimate" : 0,
                "works" : 1368,
                "advanced" : 1367,
                "needTime" : 0,
                "needFetch" : 0,
                "saveState" : 10,
                "restoreState" : 10,
                "isEOF" : 1,
                "invalidates" : 0,
                "docsExamined" : 1367,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 1367,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 1368,
                    "advanced" : 1367,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "saveState" : 10,
                    "restoreState" : 10,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "serviceId" : 1,
                        "en" : 1,
                        "time" : 1
                    },
                    "indexName" : "serviceId_1_en_1_time_1",
                    "isMultiKey" : false,
                    "direction" : "forward",
                    "indexBounds" : {
                        "serviceId" : [
                            "[ObjectId('556bf36bfa0bab5b3fd30189'), ObjectId('556bf36bfa0bab5b3fd30189')]"
                        ],
                        "en" : [
                            "[\"b1bdb597\", \"b1bdb597\"]"
                        ],
                        "time" : [
                            "[MinKey, MaxKey]"
                        ]
                    },
                    "keysExamined" : 1367,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0,
                    "matchTested" : 0
                }
            }
        },
        "allPlansExecution" : [ ]
    },
    "serverInfo" : {
        "host" : "iZ25zhv23mwZ",
        "port" : 27017,
        "version" : "3.0.0",
        "gitVersion" : "a841fd6394365954886924a35076691b4d149168"
    },
    "ok" : 1
}

这个返回结果乍一看非常复杂，我们需要抽丝剥茧，层层定位真正对我们有意义的信息，下面我列了一些我们需要关注的内容：

executionTimeMillis

executionTimeMillis的意义是语句的执行时间，我们当然希望这个值越小越好。仔细观察，我们发现会存在3个executionTimeMillis,分别如下：

  • executionStats.executionTimeMillis
    该query的整体查询时间

  • executionStats.executionStages.executionTimeMillisEstimate
    该查询根据index去检索document获取1367行具体数据的时间

  • executionStats.executionStages.inputStage.executionTimeMillisEstimate
    该查询扫描1367行index所用时间

扫描数 VS 返回数

这里主要涉及到3个item：

  • nReturned
    返回条目数
  • totalKeysExamined
    索引扫描条目数
  • totalDocsExamined
    文档扫描条目数

这三个数据最优状态应该是这样的：

nReturned=totalKeysExamined && totalDocsExamined=0

也就是说， 用索引就完成了数据的查询，返回的文档数刚好被索引扫描全部查找到(nReturned=totalKeysExamined)，而且没有扫描额外的文档(totalDocsExamined=0)

当然，如下状态也是可以接受的：

nReturned=totalKeysExamined=totalDocsExamined

这种情况是正常的索引利用，没有多余的文档扫描。

如果加上排序，为了保证排序不在内存中完成，我们可以在保证nReturned=totalDocsExamined的基础上，totalKeysExamined可以大于totalDocsExamined与nReturned，因为在数量大的情况下，相对于「把sort放到内存中」，「totalKeysExamined>totalDocsExamined」还是挺划算的。

stage 分析

这里说的stage是指winningPlan.stage 或者executionStages.stage还有inputStage.stage. 为啥要说stage呢，是因为它就决定了扫描范围，说白了，它影响了totalKeysExamined,totalDocsExamined这二者的值。

stage 的值分别有：

  • COLLSCAN （×）
    全表扫描
  • IXSCAN （√）
    索引扫描
  • FETCH （√）
    根据索引去检索指定document
  • SHARD_MERGE （-）
    将各个分片返回数据进行merge
  • SORT （×）
    表明在内存中进行了排序
  • LIMIT （√）
    使用limit限制返回数
  • SKIP （-）
    使用skip进行跳过
  • IDHACK （√）
    针对_id进行查询
  • SHARDING_FILTER （-）
    通过mongos对分片数据进行查询
  • COUNT （×）
    利用db.coll.explain().count()之类进行count运算
  • COUNTSCAN （×）
    count不使用用Index进行count时的stage返回
  • COUNT_SCAN （√）
    count使用了Index进行count时的stage返回
  • SUBPLA （×）
    未使用到索引的$or查询的stage返回

如上，我对这些类型的优劣已经做了基本判断，后续会有一个详细的例子来说明如何一步步进行索引优化分析。

基础知识

在分析不同索引的不同特性之前，先把一些语法性的东西捋一遍。

  • 创建索引
1
db.collection.ensureIndex({a:1,b:1});
  • 查看集合的所有索引
1
db.collection.getIndexes();
  • 删除索引
1
2
3
4
5
// 按索引名来删除
db.collection.dropIndex("a_1_b_1");

// 按字段名删除
db.collection.dropIndex({a:1,b:1})
  • 索引重建
1
db.collection.reIndex()
  • 修改索引
1
2

// To modify an existing index, you need to drop and recreate the index.
  • 强制使用索引
1
2
3
4
5
// 强制使用索引名
db.collection.find({}).hint("a_1_b_1")

// 强制使用索引字段域
db.collection.find({}).hint({a:1,b:1})

索引细分

首先，我们来瞟一眼mongodb的索引结构图

从图中看出，索引部分内容不多，我们需要知道的是其适合的使用场景，以及需要知道避开限制它们发挥作用的限制点。

单键索引 Single-Field

单键索引 是指只有一个字段域(field)的索引，一般使用场景很受限。官方文档额外提到了3个case.

  • _id
  • Indexes on Embedded
  • Indexes on Embedded Documents

这些基本上不用多说， 了解一下就好。在实际环境中，抛开简单日志性表,业务性属性比较强的表一般都不是单键索引所能支撑的，所以在建索引的时候，一定要综合考虑下，不能仅仅为了满足当前需求，简单粗暴直接加上单键索引。因为你一旦这么做了，你会发现最后你的表中有很多不同字段的单键索引，他们要联合起作用的话就比较麻烦。

复合索引 compound

复合索引应该是适用场景最广的一种索引，它包含了多个field，所以细节比较复杂，我们在这里一一进行分析。

  • 1.排序顺序(Sort Order)

排序顺序(Sort Order) 对于复合索引来说特别重要，先看看官网的描述：

Indexes store references to fields in either ascending (1) or descending (-1) sort order. For single-field indexes, the sort order of keys doesn’t matter because MongoDB can traverse the index in either direction. However, for compound indexes, sort order can matter in determining whether the index can support a sort operation.

对于单键索引来说， Sort Order没那么重要，因为mongodb能从两个方向(升序，降序)使用索引。 但是对于复合索引来说，索引中的排序顺序是由多个field决定的，一旦某个field顺序有误，则导致索引无法被命中。下面看看一个例子：

首先，存在一个这样的索引：

1
db.events.createIndex( { "username" : 1, "date" : -1 } )

可以命中索引的情况有：

1
2
3
4
// 匹配索引本身的排序顺序
db.events.find().sort( { username: 1, date: -1 } )
// 匹配索引本身的排序顺序的「反序」
db.events.find().sort( { username: -1, date: 1 } )

这里，我们来用explain()说话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
    // 执行查询
    db.events.find().sort( { username: 1, date: -1 } ).explain();

    // explain result
    {
        "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.events",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",   // 使用索引扫描，最高效的stage
                "keyPattern" : {
                    "username" : 1,
                    "date" : -1
                },
                "indexName" : "username_1_date_-1", // 命中的索引名
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "username" : [
                        "[MinKey, MaxKey]"
                    ],
                    "date" : [
                        "[MaxKey, MinKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "yijianbodeMacBook-Air.local",
        "port" : 27017,
        "version" : "3.0.2",
        "gitVersion" : "nogitversion"
    },
    "ok" : 1
}

而如下的情况则不能命中索引

1
2
3
4
// date 字段排序顺序不匹配
db.events.find().sort( { username: 1, date: 1 } )
// username 字段排序顺序不匹配
db.events.find().sort( { username: -1, date: -1 } )

同样，这里我们也用explain()来说话

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

     // 执行查询
     db.events.find().sort( { username: 1, date: 1 } ).explain();

     // explain result
     {
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.events",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ ]
        },
        "winningPlan" : {
            "stage" : "SORT", // 在内存中排序了，要尽量避免的事情！
            "sortPattern" : {
                "username" : 1,
                "date" : 1
            },
            "inputStage" : {
                "stage" : "COLLSCAN", // 全表扫描！绝对要避免的事情！
                "filter" : {
                    "$and" : [ ]
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "yijianbodeMacBook-Air.local",
        "port" : 27017,
        "version" : "3.0.2",
        "gitVersion" : "nogitversion"
    },
    "ok" : 1
}

官方文档对于这个特性有比较详细的说明，值得一看。

  • 2.索引前缀(Prefixes)

索引前缀正是复合索引的魅力所在之处，它能灵活的支撑不同的字段组合查询场景。

例如存在这么一个索引：

1
db.gift.ensureIndex({ "item": 1, "location": 1, "stock": 1 });

那么，这个索引存在两个索引前缀

1
2
- {item:1}
- {item:1,location}

所以，如下这些查询都是能命中索引的

1
2
3
4
db.gift.find({item:1}); // #1
db.gift.find({item:1,location}); // #2
db.gift.find({ "item": 1, "location": 1, "stock": 1 }); // #3
db.gift.find({ "item": 1, "stock": 1 }); // #4

需要说明的是， #4效率是会低于 #2 的。而没有带前缀的查询都是无法命中索引的：

1
2
db.gift.find({"location": 1, "stock": 1 }); // #1
db.gift.find({"stock": 1 }); // #2
  • 3.索引交叉(Index Intersection)

索引交叉是指当一个表存在多个索引，在某个查询当中同时使用了不同索引进行查询的情况。

例如存在这么两个索引：

1
2
{ qty: 1 }
{ item: 1 }

下面这个查询就会触发索引交叉机制

1
db.orders.find( { item: "abc123", qty: { $gt: 15 } } )

当索引中存在复合索引，结合索引交叉机制时，也同样满足索引前缀原则，这个算是mongodb对于索引命中率的优化，详情可以参考官方文档

多键索引(Multikey Indexes)

多键索引是指建在array类型上的索引。

MongoDB automatically creates a multikey index if any indexed field is an array; you do not need to explicitly specify the multikey type.

用的场景很窄，不多说，直接看文档

地理位置索引(GEO)

这类索引是mongodb索引的一大亮点，基于LBS的应用服务都能用上，非常方便，因为平时用的不深，场景也比较单一，不再多说。

全文搜索(Text)

不支持中文，对于我们来说基本等于没用。

哈希索引(Hashed)

哈希索引是指以数据的哈希值作为索引值，它的特性是速度快。

Hashed indexes maintain entries with hashes of the values of the indexed field. The hashing function collapses embedded documents and computes the hash for the entire value but does not support multi-key (i.e. arrays) indexes.

注意一下它的语法：

db.active.createIndex( { a: “hashed” } )

这种索引比较适合一些格式固定但是数据量比较大的字段，例如身份证号码，手机号码等。 hash 值的计算和匹配都是mongodb自动完成的，不需要开发者参与，非常方便。

说完了索引的类型，再来看看索引的属性。

时效性 ttl

ttl 可以让mongodb自动按时间来移除数据，对于一些日志数据表来说，非常方便。

1
db.eventlog.createIndex( { "lastModifiedDate": 1 }, { expireAfterSeconds: 3600 } )

expireAfterSeconds 这个属性就是表示此索引具备了ttl特性，单位为秒。

这个属性的原理如下：

A background thread in mongod reads the values in the index and removes expired documents from the collection.

有不少限制：

  1. TTL indexes are a single-field indexes. Compound indexes do not support TTL and ignores the expireAfterSeconds option.
  2. The _id field does not support TTL indexes.
  3. You cannot create a TTL index on a capped collection because MongoDB cannot remove documents from a capped collection.
  4. You cannot use createIndex() to change the value of expireAfterSeconds of an existing index. Instead use the collMod database command in conjunction with the index collection flag. Otherwise, to change the value of the option of an existing index, you must drop the index first and recreate.
  5. If a non-TTL single-field index already exists for a field, you cannot create a TTL index on the same field since you cannot create indexes that have the same key specification and differ only by the options. To change a non-TTL single-field index to a TTL index, you must drop the index first and recreate with the expireAfterSeconds option.

这部分内容官方文档说的很详细，建议细读。

唯一性 Unique

唯一索引非常好理解，语法如下：

db.members.createIndex( { “user_id”: 1 }, { unique: true } )

需要注意下，当索引字段域缺少了数据时，mongodb默认会存储一个null值，这意味着如果再次有缺失值，则会触发唯一性机制，操作会报错。

同时有这么一个限制：

You may not specify a unique constraint on a hashed index.

稀疏性 Sparse|Partial

稀疏性 对于索引的灵活性特别有意义。它可以把索引建在只感兴趣的数据上。需要特别说明的是，sparse是3.2 之前的支持方式，3.2版本开始支持partial，它的性能更高，同时定义方式也更为灵活。如果mongdb版本为3.2以上，建议直接使用Partial.

还是来看一下语法吧:

1
2
3
4
5
6
7
8
9
10
11
12

// 只对rating大于5的数据索引
db.restaurants.createIndex(
   { cuisine: 1 },
   { partialFilterExpression: { rating: { $gt: 5 } } }
)
 
// 只对name存在的数据索引, Sparse就只支持此模式
db.contacts.createIndex(
   { name: 1 },
   { partialFilterExpression: { name: { $exists: true } } }
)

文档的话也是值得通读一遍的。

优化实践

在了解了mongodb的索引机制以后，下面以一个实例来展示索引优化的过程。

首先，我们有一个集合gift，它的数据如下：

1
2
3
4
5
6
7
8
9
10
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b90f"), "item" : 1, "location" : "changsha", "stock" : 100 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b910"), "item" : 2, "location" : "beijing", "stock" : 100 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b911"), "item" : 3, "location" : "changsha", "stock" : 20 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b912"), "item" : 4, "location" : "guangzhou", "stock" : 100 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b913"), "item" : 5, "location" : "tianjing", "stock" : 26 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b914"), "item" : 6, "location" : "changsha", "stock" : 100 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b915"), "item" : 7, "location" : "yiyang", "stock" : 64 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b916"), "item" : 8, "location" : "changsha", "stock" : 34 }
{ "_id" : ObjectId("56ad84cbbdd239ce1b21b917"), "item" : 9, "location" : "wuhan", "stock" : 100 }
{ "_id" : ObjectId("56ad84ccbdd239ce1b21b918"), "item" : 10, "location" : "shenzheng", "stock" : 34 }

我们想要执行的语句如下：

1
db.gift.find({item :{"$gt":4},location:"changsha"}).sort({stock:-1})

首先，看下无索引的explain()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

{
    "queryPlanner" : {...} // 略去
         
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 2,            // 返回2
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 0,   // 索引扫描数0 
        "totalDocsExamined" : 10,  // 文档扫描数10
        "executionStages" : {
            "stage" : "SORT",      // 内存排序
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "works" : 16,
            "advanced" : 2,
            "needTime" : 12,
            "needFetch" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "sortPattern" : {
                "stock" : -1
            },
            "memUsage" : 164,
            "memLimit" : 33554432,
            "inputStage" : {
                "stage" : "COLLSCAN",  // 全表扫描
                "filter" : {
                    "$and" : [
                        {
                            "location" : {
                                "$eq" : "changsha"
                            }
                        },
                        {
                            "item" : {
                                "$gt" : 4
                            }
                        }
                    ]
                },
                ...
            }
        }
    },
    "serverInfo" : {}, // 略去
    "ok" : 1
}

毫无疑问，性能非常低，列一下可优化的点：

  • 全表扫描
  • 内存排序
  • totalDocsExamined>(nReturned=totalKeysExamined=0)

首先考虑内存排序问题，先对排序字段加个索引，看看效果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
db.gift.ensureIndex({stock:1})

// explain result

...

"executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 2,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 10,  // 索引扫描数还是很大
        "totalDocsExamined" : 10,  // 文档扫描数还是很大（全表）
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [
                    {
                        "location" : {
                            "$eq" : "changsha"
                        }
                    },
                    {
                        "item" : {
                            "$gt" : 4
                        }
                    }
                ]
            },
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "works" : 11,
            "advanced" : 2,
            "needTime" : 8,
            "needFetch" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 10,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",  // 启用索引了，不再内存排序
                "nReturned" : 10,
                "executionTimeMillisEstimate" : 0,
                "works" : 10,
                "advanced" : 10,
                "needTime" : 0,
                "needFetch" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "stock" : 1
                },
                "indexName" : "stock_1",
                "isMultiKey" : false,
                "direction" : "backward",
                "indexBounds" : {
                    "stock" : [
                        "[MaxKey, MinKey]"
                    ]
                },
                "keysExamined" : 10,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0,
                "matchTested" : 0
            }
        }
    },

...

把查询条件也加上索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 加上索引
db.gift.ensureIndex({item:1,location:1,stock:1});
// explain result
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 2,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 6,  // 索引过滤没有起作用，居然比totalDocsExamined大
    "totalDocsExamined" : 2,
    "executionStages" : {
        "stage" : "SORT",  // 内存排序
        ...
    }    
}
//

经过分析：

item 为区间查询，它作为索引的前缀字段，必然影响索引的扫描范围

所以调整一下索引的顺序：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// 加上索引
db.gift.ensureIndex({location:1,item:1,stock:1});
// explain result

    "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 2,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 2,
    "totalDocsExamined" : 2,
    "executionStages" : {
        "stage" : "SORT",
    }
}

分析结果：

nReturned=totalKeysExamined=totalDocsExamined

非常完美对不对，至少说明扫描范围已经控制到了极限。

但是需要注意stage=SORT, 在数据量非常大的场景中，我们应该尽量消除这个因素，让排序尽量通过索引完成。

再次修改索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 索引
db.gift.ensureIndex({location:1,stock:1,item:1});

// explain result
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 2,
    "executionTimeMillis" : 0,
    "totalKeysExamined" : 4,
    "totalDocsExamined" : 2,
    "executionStages" : {
        "stage" : "FETCH",
        ...
    }
}

可以看到stage=FETCH, 虽然totalKeysExamined>totalDocsExamined,但是相对于「sort」带来的消耗，多扫描几条数据根本不值一提。

到此，索引优化的示例就结束了，从这个示例中我们可以得出一个「小原则」：

当我们的的一个查询覆盖了「精确匹配」，「范围查询」，「排序」等3个场景时，复合索引的顺序应该是这样： 精度匹配字段, 排序字段, 范围查询字段。