MongoDB大小写不敏感索引失效致聚合查询性能骤降问题排查

核心问题分析 #

1. 索引创建语法错误：你提供的索引创建语句存在括号未闭合的语法问题，导致索引根本没有成功创建，自然无法被查询利用。
2. 索引类型不匹配：你的查询同时匹配零件编号和供应商名称两个字段，仅创建单字段索引无法覆盖联合查询条件，导致EN集合只能使用单个索引，另一字段需要额外扫描数据。
3. collation匹配问题：使用$expr进行跨集合变量匹配时，需确保查询的collation与索引的collation完全一致，否则索引无法生效。
4. 全表扫描无过滤：聚合初始阶段没有过滤条件，会遍历csv_import集合所有文档，数据量较大时直接导致性能瓶颈。

步骤1：修正并重新创建索引 #

1.1 修正csv_import集合的单字段索引 #

db.csv_import.createIndex(
    { part_no: 1 },
    {
        name: "part_no_unsensitive_idx",
        collation: { locale: "en", strength: 2 }
    }
);

1.2 创建EN集合的复合大小写不敏感索引（关键） #

因为查询同时匹配Prod_id和Supplier.Name，必须创建复合索引才能让查询高效利用索引：

db.EN.createIndex(
    {
        "ICECAT-interface.Product.@Prod_id": 1,
        "ICECAT-interface.Product.Supplier.@Name": 1
    },
    {
        name: "prod_vendor_unsensitive_idx",
        collation: { locale: "en", strength: 2 }
    }
);

步骤2：优化聚合查询 #

优化点说明： #

• 增加初始过滤阶段，排除无效数据，减少后续处理量
• 确保聚合使用的collation与索引完全一致
• 保持查询条件字段顺序与复合索引字段顺序匹配

use Icecat
db.csv_import.aggregate(
    [
        // 初始过滤：排除空值或无效数据
        {
            $match: {
                part_no: { $exists: true, $ne: "" },
                vendor_standard: { $exists: true, $ne: "" }
            }
        },
        {
            $lookup: {
                from: 'EN',
                let: { pn: '$part_no', vendor: '$vendor_standard' },
                pipeline: [
                    {
                        $match: {
                            $expr: {
                                $and: [
                                    { $eq: ["$$pn", "$ICECAT-interface.Product.@Prod_id"] },
                                    { $eq: ["$$vendor", "$ICECAT-interface.Product.Supplier.@Name"] }
                                ]
                            }
                        }
                    }
                ],
                as: 'part_number_info'
            }
        },
        { $match: { "part_number_info.0": { $exists: true } } },
        {
            $project: {
                part_no: 1,
                "part_number_info.ICECAT-interface.Product.@ID": 1,
                "part_number_info.ICECAT-interface.Product.Supplier.@Name": 1,
                _id: 0
            }
        }
    ],
    { collation: { locale: 'en', strength: 2 } }
).pretty();

验证步骤 #

1. 执行db.EN.getIndexes()和db.csv_import.getIndexes()，确认索引已正确创建
2. 对聚合查询执行explain("executionStats")，查看executionStats中的索引使用情况，确认EN集合使用了创建的复合索引，csv_import的扫描文档数因初始过滤减少

内容的提问来源于stack exchange，提问作者Matias