关于mongodb查询使用 in 关键字，会导致 mongodb 选择索引不是最优

小灰灰 2021-10-09 09:12 488阅读 0赞

定义两个类似的查询脚本：

脚本1：

db.public_setting_detail.find({
        "resourceId": "5f93c78839f8f82e6093d0ab",
        "creator": 32235,
        "publicStatus": {
            "$in": [0, 1]
        },
        "publicObject": {
            "$elemMatch": {
                "classId": {
                    "$in": [1540, 10726, 10766, 10769, 10770, 10777, 3334, 3335]
                }
            }
        },
        "isdir": 1,
        "status": 1
    }).explain("executionStats")

脚本2：

db.public_setting_detail.find({
        "resourceId": {"$in":["5f93c78839f8f82e6093d0ab"]},
        "creator": 32235,
        "publicStatus": {
            "$in": [0, 1]
        },
        "publicObject": {
            "$elemMatch": {
                "classId": {
                    "$in": [1540, 10726, 10766, 10769, 10770, 10777, 3334, 3335]
                }
            }
        },
        "isdir": 1,
        "status": 1
    }).explain("executionStats")

脚本3：

db.public_setting_detail.find({
        "resourceId": {"$in":["5f93c78839f8f82e6093d0ab", "5f93c78039f8f82e6093d093", "5f93c78739f8f82e6093d0a7", "5f93c77e39f8f82e6093d091", "5f88201139f8f861b81c2b1c", "5f88201139f8f861b81c2b1a", "5f93c78339f8f82e6093d09d", "5f93c78f39f8f82e6093d0bf", "5f93c78b39f8f82e6093d0b3", "5f93c79139f8f82e6093d0c3", "5f93c78f39f8f82e6093d0bd", "5f93c78b39f8f82e6093d0b1", "5f93c79139f8f82e6093d0c5", "5f93c79339f8f82e6093d0c9", "5f93c78e39f8f82e6093d0bb", "5f93c78a39f8f82e6093d0af", "5f93c78039f8f82e6093d095", "5f93c78239f8f82e6093d09b", "5f93c78c39f8f82e6093d0b5", "5f93c79039f8f82e6093d0c1", "5f93c78139f8f82e6093d097", "5f93c78639f8f82e6093d0a5", "5f93c78939f8f82e6093d0ad", "5f93c78839f8f82e6093d0a9", "5f93c78439f8f82e6093d09f", "5f93c78d39f8f82e6093d0b9", "5f93c78d39f8f82e6093d0b7", "5f93c78539f8f82e6093d0a3", "5f93c78239f8f82e6093d099", "5f93c78539f8f82e6093d0a1", "5f93c79239f8f82e6093d0c7", "5fa9fb6839f8f842aecff9a4"]},
        "creator": 32235,
        "publicStatus": {
            "$in": [0, 1]
        },
        "publicObject": {
            "$elemMatch": {
                "classId": {
                    "$in": [1540, 10726, 10766, 10769, 10770, 10777, 3334, 3335]
                }
            }
        },
        "isdir": 1,
        "status": 1
    }).explain("executionStats")

上述为3个脚本，脚本基本类似，也可以说本质就是 一样的查询；

但是3个脚本查询的执行过程分析，却有出路；

一、对比脚本1与脚本2

脚本1：可以看出，数据库引擎选择了与我们预期一样的索引。并且抛弃了我们不想选择的索引；并且索引扫描条目和文档返回条目相差不大；表示精准度哈可以

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 1][]

![2020111713582130.png][]

脚本2：分析：基本和脚本1的分析结果一致，此处不赘述

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 2][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 3][]

![20201117135821115.png][]

脚本3：经过与脚本2、脚本1对比分析，惊奇的发现，他竟然选择了我们最不想选择的索引：idx\_public\_setting\_detail\_\_creator\_publicStatus\_publicObject\_isdir\_status

忽略了：idx\_public\_setting\_detail\_\_resourceId\_creator\_publicStatus\_publicObject\_isdir\_status

这是为什么？？？？？？？？？下面分析

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 4][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 5][]

![20201117135821150.png][]

二、大胆假设分析：由于 查询条件中，resourceId的数量太多，导致数据库认为该查询条件并不能较好区别数据。

我们可以看到数据库对我们的查询sql，进行了整理排序，将resourceId排到末尾了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 6][]

三、那么尝试，删除数据库选择的那个索引（idx\_public\_setting\_detail\_\_creator\_publicStatus\_publicObject\_isdir\_status），让数据库只能选择我们指定的索引，看看效果如何？

直接看扫描的文档数：扫描索引键 609个，但是实际匹配的才256个。此处命中率降低了。但是似乎不能直接说，数据库就是对的。此处需结合具体的数据增长情况来定。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 7][]

四、此处我们试试将 resourceId降低等级

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 8][]

执行相同脚本：视乎没变什么

![20201117135820999.png][]

那么直接降到最低：效果出来了，扫描索引数，和实际返回数接近，也就是命中率提升了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 9][]

![20201117135821263.png][]

五、总结，此处并不建议，最后一种索引顺序，因为那只是现有数据的一个扫描结果，导致让我们误以为最后一种就是最优的索引。

原则上来说，应当配合数据的增涨情况，进行设计索引。

以我举例的文档结构来举例：

如果说一个 creator的增加新数据的情况大于 resourceId；

白话一点的描述，就是：如果1个资源A可能以后会属于10000+个用户，然后有1000个资源。那么此处，应当是creator,resourceId的复合索引顺序。

为什么这样描述呢，因为对于索引来说，我们更希望，在前面的字段索引能够更高效的区分数据。以两张图片来抽象表示：

![20201117135821279.png][]   ![20201117135821288.png][]

两颗树，我们不希望，对于树的结构来说，广度增长，更希望深度增长，这样我们所扫描的数量更少，然后更方便命中。

反之举例：如果说1个用户1可能以后会有10000+个资源，然后有1000个用户。那么，我们应当设计索引为resourceId,creator

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201117135820977.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 1]: /images/20211009/9ae7a392456c43fc8b8741bbebbe1036.png
[2020111713582130.png]: /images/20211009/4518ac1d9a7d4f7f8ae07ad0b3a61e0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020111713582168.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 3]: /images/20211009/30dfb508a26146e8b52f825a1f30afad.png
[20201117135821115.png]: /images/20211009/d794ddf959a24c5a89e8d9b419b09208.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201117135821128.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 5]: /images/20211009/6236239ae74e4939a407cc8c9aa8c353.png
[20201117135821150.png]: /images/20211009/d30802bc3e9f441fab4b8a0d2a42cea2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 6]: /images/20211009/875c01db51c24ec1ad53c57aa13e45d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 7]: /images/20211009/98a8754cedea4d879b8c4ba74306855d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 8]: /images/20211009/58a8d14cc251439ba270b3771f8b228d.png
[20201117135820999.png]: /images/20211009/76ca56360e2344239b91f2088f765b69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMjIzNjg4_size_16_color_FFFFFF_t_70 9]: /images/20211009/259cd40225454f6d95a5daa361ac0874.png
[20201117135821263.png]: /images/20211009/0a7551a010034f89a2a168143b915d5d.png
[20201117135821279.png]: https://img-blog.csdnimg.cn/20201117135821279.png
[20201117135821288.png]: /images/20211009/e94769e22f28403e97313d8109256a20.png