用于使用null，false和true进行查询的复合mongo索引优化 [英] Compound mongo index optimization for query with null, false, and true

问题描述

我正在尝试找出创建索引的最佳方法，mongo可以用来使该查询更快:

I'm trying to figure out the best way to create an index mongo can use to make this query faster:

"query" : {
    "deleted_at" : null,
    "placed_at" : {
        "$exists" : true
    },
    "exported_at" : null,
    "failed_export" : false
}

当前，即使没有结果，也需要2-3分钟才能通过表格. Explain显示它正在浏览数十万条记录，而不使用索引.

Currently, it's taking 2-3 min to go through the table even when there's no results. Explain shows that it's looking through hundreds of thousands of records and not using an index.

我尝试运行此命令:

db.some_table.createIndex({deleted_at: -1, placed_at: 1, exported_at: -1, failed_export: -1}, {background: true})

当我随后运行查询时:

db.some_table.find({deleted_at: null, placed_at: {$exists: true}, exported_at: null, failed_export: false}).explain("executionStats")

我看到它使用了正确的索引，但是非常慢.它正在检查所有330494行.以下是执行状态:

I see it's using the correct index, but it's very slow. It's examining all 330494 rows. Here are the execution stats:

  "executionStats" : {
            "executionSuccess" : true,
            "nReturned" : 0,
            "executionTimeMillis" : 1585381,
            "totalKeysExamined" : 330494,
            "totalDocsExamined" : 330494,
            "executionStages" : {
                    "stage" : "FETCH",
                    "filter" : {
                            "$and" : [
                                    {
                                            "placed_at" : {
                                                    "$exists" : true
                                            }
                                    },
                                    {
                                            "deleted_at" : {
                                                    "$eq" : null
                                            }
                                    },
                                    {
                                            "exported_at" : {
                                                    "$eq" : null
                                            }
                                    },
                                    {
                                            "failed_export" : {
                                                    "$eq" : false
                                            }
                                    }
                            ]
                    },

获胜计划是:

           "winningPlan" : {
                    "stage" : "FETCH",
                    "filter" : {
                            "$and" : [
                                    {
                                            "placed_at" : {
                                                    "$exists" : true
                                            }
                                    },
                                    {
                                            "deleted_at" : {
                                                    "$eq" : null
                                            }
                                    },
                                    {
                                            "exported_at" : {
                                                    "$eq" : null
                                            }
                                    },
                                    {
                                            "failed_export" : {
                                                    "$eq" : false
                                            }
                                    }
                            ]
                    },
                    "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                    "placed_at" : 1
                            },
                            "indexName" : "placed_at_1",
                            "isMultiKey" : false,
                            "direction" : "forward",
                            "indexBounds" : {
                                    "placed_at" : [
                                            "[MinKey, MaxKey]"
                                    ]
                            }
                    }
            },

它确实列出了我在其中一个被拒绝的计划中创建的索引.

And it did list the index I created in one of the rejected plans.

关于它为什么要遍历数据库中的每个记录的任何想法?这损害了我们的表现.

Any ideas on why it would be going through every record in the database? This is hurting our performance.

我尝试提示正确的记录，但这似乎并没有太大帮助.

I've tried hinting at the right record and that didn't seem to help much.

推荐答案

与其查询deleted_at: null，不如创建一个新的status字段或一个isDeleted字段并配置您的应用服务器以填充该字段，会更好.场地.然后，您可以在此字段上创建更有效的索引，以查找所有软删除的文档.

Instead of querying for deleted_at: null it would be better to create a new status field or an isDeleted field and configure your app servers to populate that field. Then, you can create a more effective index on this field to find all of your soft-deleted documents.

来自 性能MongoDB最佳实践 白皮书:

From the Performance Best Practices for MongoDB white paper:

避免查询中的求反.与大多数数据库系统一样，MongoDB不会为缺少值的索引，并且求反条件可能要求扫描所有文档.如果否定是唯一的条件，并且不是选择性的(例如，查询订单表，其中99％的订单已完成以识别那些尚未满足的订单)，则将需要扫描所有记录.

Avoid negation in queries. Like most database systems, MongoDB does not index the absence of values and negation conditions may require scanning all documents. If negation is the only condition and it is not selective (for example, querying an orders table where 99% of the orders are complete to identify those that have not been fulfilled), all records will need to be scanned.

这篇关于用于使用null，false和true进行查询的复合mongo索引优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！