MongoDB 索引查询还是慢 mongodb索引命中规则

关于mongo的索引，基本规则与普遍数据库是一致的。

在实际分析索引问题是否最优的时候，主要查看executionStats.totalKeysExamined、

executionStats.totalDocsExamined、executionStats .nReturned三个统计项，如果存在以下情况则说明索引存在问题，可能索引不是最优的：

1. executionStats.totalKeysExamine远大于executionStats .nReturned
2. executionStats. totalDocsExamined远大于executionStats .nReturned

explain的参数

• queryPlanner

输出索引的候选索引，包括最优索引及其执行stage过程(winningPlan)+其他非最优候选索引及其执行stage过程。

注意：queryPlanner没有真正在表中执行整个SQL，只做了查询优化器获取候选索引过程，因此可以很快返回。

• executionStats

相比queryPlanner参数，executionStats会记录查询优化器根据所选最优索引执行SQL的整个过程信息，会真正执行整个SQL。如果写操作，将返回要执行的更新或删除操作的信息，但不会应用于数据库。

• allPlansExecution

和executionStats类似，只是多了所有候选索引的执行过程。

To determine whether a query is a covered query, use the explain() method. If the explain() output displays totalDocsExamined as 0, this shows the query is covered by an index.

索引尽量覆盖到到整个查询，如果totalDocsExamined是0，说明索引完全覆盖

indexStats

MongoDB默认提供有索引统计命令来获取各个索引命中的次数，该命令如下：

> db.xxxxx.aggregate({"$indexStats":{}})  
 { "name" : "alxxxId_1_parentItxxxId_1_parentAlxxxId_1", "key" : { "alxxxId" : 1, "parentItxxxId" : 1, "parentAlxxxId" : 1 }, "host" : "TENCENT64.site:7014", "accesses" : { "ops" : NumberLong(11236765), "since" : ISODate("2020-08-17T06:39:43.840Z") } }

该聚合输出中的几个核心指标信息如下表：

字段内容	说明
name	索引名，代表是针对那个索引的统计。
ops	索引命中次数，也就是所有查询中采用本索引作为查询索引的次数。

上表中的ops代表命中次数，如果命中次数为0或者很小，说明该索引很少被选为最优索引使用，因此可以认为是无用索引，可以考虑删除。

以下摘抄自https://mongoing.com/archives/81885

3.1.等值类查询常见索引错误创建方法及如何创建最优索引

3.1.1. 同一类查询创建多个索引问题

如下三个查询：

db.test4.find({"a":"xxx", "b":"xxx", "c":"xxx"})   
db.test4.find({"b":"xxx", "a":"xxx", "c":"xxx"})   
db.test4.find({"c":"xxx", "a":"xxx", "b":"xxx"})

用户创建了如下3个索引：

{a:1, b:1, c:1}
{b:1, a:1, c:1}
{c:1, a:1, b:1}

实际上这3个查询属于同一类查询，只是查询字段顺序不一样，因此只需创建任一个索引即可满足要求。

3.1.2. 多字段等值查询组合索引顺序非最优

例如test表有多条数据，每条数据有3个字段，分别为a、b、c。其中a字段有10种取值，b字段有100种取值，c字段有1000种取值，称为各个字段值的“区分度”。

用户查询条件为db.test.find({“a”:“xxx”, “b”:“xxx”, “c”:“xxx”})，创建的索引为{a:1, b:1, c:1}。如果只是针对这个查询，该查询可以创建a，b，c三字段的任意组合，并且其SQL执行代价一样.

多字段等值查询各个字段的组合顺序对应执行计划代价一样。绝大部分用户在创建索引的时候，都是直接按照查询字段索引组合对应字段。

但是，单就这一个查询，这里有个不成文的建议，把区分度更高的字段放在组合索引左边，区分度低的字段放到右边。这样做有个好处，数据库组合索引遵从最左原则，就是当其他查询里面带有区分度最高的字段时，就可以快速排除掉更多不满足条件的数据。

3.1.3. 最左原则包含关系引起的重复索引

例如有如下两个查询：

db.test.find({"b" : 2, "c" : 1})  //查询1 db.test.find({"a" : 10, "b" : 5, "c" : 1})  //查询2

用户创建了如下两个索引：

{b:1, c:1}
{a:1,b:1,c:1}

这两个查询中，查询2中包含有查询1中的字段，因此可以用一个索引来满足这两个查询要求，按照最左原则，查询1字段放左边即可，该索引可以优化为：b, c字段索引+a字段索引，b,c字段顺序可以根据区分排序，加上c字段区分度比b高，则这两个查询可以合并为一个{c:1, b:1, a:1}。两个查询可以走同一个索引。

3.1.4. 唯一字段和其他字段组合引起的无用重复索引

例如用户有以下两个查询：

db.test.find({a:1,b:1})    
db.test.find({a:1,c:1})

用户为这两个查询创建了两个索引，{a:1, b:1}和{a:1, c:1}，但是a字段取值是唯一的，因此这两个查询中a以外的字段无用，一个{a:1}索引即可满足要求。

3.2.非等值类查询常见索引错误创建方法及如何创建最优索引

3.2.1. 非等值组合查询索引不合理创建

假设用户有如下查询：

//两字段非等值查询   
 db.test.find({a:{$gte:1}, c:{$lte:1}})

a,c两个字段都是非等值查询，很多用户直接添加了{a:1, c:1}索引，实际上多个字段的非等值查询，只有最左边的字段才能走索引，例如这里只会走a字段索引，c字段无法走索引。

同理，当查询中包含多个字段的范围查询的适合，除了最左边第一个字段可以走索引，其他字段都无法走索引。因此，上面例子中的查询候选索引为{a:1}或者{b:1}中任何一个就可以了，组合索引中字段太多会占用更多存储成本、同时暂用更多IO资源引起写放大。

3.2.2. 等值+非等值组合查询索引字段顺序不合理

例如下面查询：

//两字段非等值查询    
  db.test.find({"d":{$gte:4}, "e":1})

如上查询，d字段为非等值查询，e字段为等值查询，很多用户遇到该类查询直接创建了{d:1, e:1}索引，由于d字段为非等值查询，因此e字段无法走索引，等值类和非等值类组合查询对应组合索引，最优索引应该优先把等值查询放到左边，上面查询对应最优索引{e:1, d:1}。

3.2.3. 不同类型非等值查询优先级问题

前面用到的非等值查询操作符只提到了比较类操作符，实际上非等值查询还有其他操作符。常用非等值查询包括：$gt、$gte、$lt、$lte、$in、$nin、$ne、$exists、$type等，这些非等值查询在绝大部分情况下存在如下优先级：

1. $In
2. $gt $gte $lt $lte
3. $nin
4. $ne
5. $type
6. $exist

从上到下优先级更高，例如下面的查询：

//等值+多个不同优先级非等值查询    
 db.test.find({"a":1, "b":1, "c":{$ne:5}, "e":{$type:"string"}, "f":{$gt:5},"g":{$in:[3,4]})  查询1

如上，该查询等值部分查询最优索引{a:1,b:1}(假设a区分度比b高)；非等值部分，因为$in操作符优先级最高，排他性更好，加上多个字段非等值查询只会有一个字段走索引，因此非等值部分最优索引为{g:1}。

最终该查询最优索引为：”等值部分最优索引”与”非等值部分最优索引”拼接，也就是{a:1,b:1, g:1}。

3.3.OR类查询常见索引错误创建方法及如何创建最优索引

3.3.1. 普通OR类查询

例如如下or查询：

//or中包含两个查询    
  db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}} ] } )

该查询很多用户直接创建了{b:1,d:1, c:1, a:1}，用户创建该索引后，发现用户还是全表扫描。

Or类查询需要给数组中每个查询添加索引，例如上面or数组中实际包含{ b: 0, d:0 }和{“c”:1, “a”:{$gte:4}}查询，需要创建两个查询的最优索引，也就是{b:1, d:1}和{c:1, a:1}，如果该OR类查询走{b:1, d:1, c:1, a:1}索引，则实际上做了全表扫描。如果同时创建{b:1, d:1}、{c:1, a:1}索引，则直接走两个索引，其执行key和doc扫描行数远远小于全表扫描。

3.3.2. 复杂OR类查询

这里在提升一下OR查询难度，例如下面的查询：

//等值查询+or类查询+sort排序查询    
  db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ) 查询1

上面的查询可以转换为如下两个查询：

------db.test.find( {"f":3, g:2, b: 0, d:0  } )  //查询2  
or--|         
------db.test.find( {"f":3, g:2, "c":1, "a":6} )  //查询3

如上图，查询1拆分后的两个查询2和查询3组成or关系，因此对应最优所有需要创建两个，分表是：{f:1, g:1, b:1, d:1}和 {f:1, g:1, b:1, d:1}。

同理，不管怎么增加难度，OR查询最终可转换为多个等值、非等值或者等值与非等值组合类查询，通过如上变换最终可以做到举一反三的作用。

说明：这个例子中可能在一些特殊数据分布场景，最优索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:-1}或者{ f:1, g:1, c:1, a:1}，这里我们只考虑大部分通用场景。

3.4.Sort类排序查询常见索引错误创建方法及如何创建最优索引

3.4.1. 单字段正反序排序查询引起的重复索引

例如用户有以下两个查询：

db.test.find({}).sort({a:1}).limit(2)   db.test.find({}).sort({a:-1}).limit(2)

这两个查询都不带条件，排序方式不一样，因此很多创建了两个索引{a:1}和{a:-1}，实际上这两个索引中的任何一个都可以满足两种查询要求

3.4.2. 多字段排序查询正反序问题引起索引无效

假设有如下查询：

//两字段排序查询   
 db.test.find().sort({a:1, b:-1}).limit(5)

其中a字段为正序，b字段为反序排序，很多用户直接创建{a:1, b:1}索引，这时候b字段内容就存在内存排序情况。多字段排序索引，如果没有携带查询条件，则最优索引即为排序字段对应索引，这里切记保持每个字段得正反序和sort完全一致，否则可能存在部分字段内存排序的情况

3.4.3. 等值查询+多字段排序组合查询

例如如下查询:

//多字段等值查询+多字段排序查询     
  db.test.find({ "a" : 3, "b" : 1}).sort({c:-1, d:1})

该类查询很多人直接创建{a:1,b:1, c:1, d:1}，结果造成内存排序。这种组合查询最优索引=“多字段等值查询最优索引_多字段排序类组合最优索引”，例如该查询：

{ “a” : 3, “b” : 1}等值查询假设a区分度比b高，则对应最优索引为：{a：1, b：1}

{ c:-1, d:1}排序类查询最优索引保持正反序一致，也就是:{ c:-1, d:1}

因此整个查询就是这两个查询对应最优索引拼接，也就是{a:1, b:1, c:-1, d:1}.

3.4.4. 等值查询+非等值查询+sort排序查询

Follow the ESR rule

For compound indexes, this rule of thumb is helpful in deciding the order of fields in the index:

复合索引，简言之索引规则要考虑到如下规则：

• First, add those fields against which Equality queries are run. 等值查询放前面
• The next fields to be indexed should reflect the Sort order of the query. 排序放中间
• The last fields represent the Range of data to be accessed.范围查找放最后

假设有下面的查询：

//等值+非等值+sort排序查询     
  db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1})

腾讯云很多用户看到该查询直接创建{a:1,b:1, c:1, d:-1, e:1}索引，发现存在内存排序。等值+非等值+sort排序组合查询，由于非等值查询右边的字段不能走索引，因此如果把d, e放到c的右边，则d，e字段索引无效。

等值+非等值+sort排序最优索引组合字段顺序为：等值_sort排序_非等值，因此上面查询最优索引为:{a:1, b:1, d:-1, e:1, c:1}。

3.4.5. OR +SORT组合排序查询

例如如下查询：

//or+sort组合   查询1  
  db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1})

上面组合很多人直接创建{b:1, d:1, c:1, a:1, e:1}，该索引创建后还是会扫表和内存排序，实际上OR+SORT组合查询可以转换为下面两个查询：

//查询1等价转换为如下查询           
 -----db.test.find({ b: 3, d:5 }).sort({e:-1})        //查询2    
 or--|          
 -----db.test.find( {"c":1, "a":6}  ).sort({e:-1})     //查询3

所以这个复杂查询就可以拆分为等值组合查询+sort排序查询，拆分为上面的两个查询，这样我们只需要同时创建查询2和查询3对应最优索引即可。该查询最终拆分后对应最优索引需要添加如下两个：

{b:1, d:1, e:-1}和{c:1,a:1, e:-1}

OR+SORT类查询，最终可以《参考前面的OR类查询常见索引错误创建方法》把OR查询转换为多个等值、非等值或者等值与非等值组合查询，然后与sort排序对应索引字段拼接。例如下面查询：

//原查询   
 db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1})  //查询1

拆分后的两个查询组成or关系，如下：

//拆分后查询           
 ------ db.test.find( {"f":3, g:2,  b: 0, d:0} ).sort({e:-1})  //查询2 
 or---          
 ------ db.test.find( {"f":3, g:2, "c":1, "a":6}).sort({e:-1}) //查询3

如上，查询1 = or: [查询2， 查询3]，因此只需要创建查询2和查询3两个最优索引即可满足查询1要求，查询2和查询3最优索引可以参考前面《or类查询常见索引错误创建方法》，该查询最终需要创建如下两个索引：

{f:1, g:1, b:1, d:1, e:-1}和{ f:1, g:1, c:1, a:1, e:-1}

说明：这个例子中可能在一些特殊数据分布场景，最优索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:1, e:-1}或者{ f:1, g:1, c:1, a:1, e:-1}，这里我们只考虑通用场景。

最后，平日记录的一些情况：

1.regex

If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan. Further optimization can occur if the regular expression is a “prefix expression”, which means that all potential matches start with the same string. This allows MongoDB to construct a “range” from that prefix and only match against those values from the index that fall within that range.

如果查询中正则列带索引，这里索引不是用于二分查找，而是作为快速查找值来用。

2.假设一张表上有{name ,age}两列，name和age上分别有一个索引。如果find(name).sort(age)，即使name返回很少，也有大概率会走age上的索引导致全表扫。

这是因为，如果执行计划中等值列有索引，排序列也有自己的索引，优化器会更偏向排序列的索引，因为对于优化器来说要尽量避免排序，在未知查询返回数量的情况下，走排序的索引效率高的可能性更大。这也是会遇见明明筛选出来的列更少，却走了排序上的索引导致全表扫描的原因。