我是 Mongo 的新手，在匹配管道中使用 $ne 時查詢速度很慢（獲取僅匹配的記錄，而不是陣列為空的所有記錄）
查詢如下：
db.EN.aggregate([
{
$lookup: {
from: 'csv_import',
let: {pn:'$ICECAT-interface.Product.@Prod_id'},
pipeline: [{
$match: {
$expr: {
$eq: ["$$pn","$part_no"]
}
}
}],
as: 'part_number_info'
}
}, { $match: { part_number_info: { $ne: [] } } }
]).pretty();
當我洗掉{ $match: { part_number_info: { $ne: [] } } }查詢時，查詢在 21 秒內執行，而使用 $ne 子句執行時幾乎需要 2 小時。
ICECAT-interface.Product.@Prod_id 上已經有一個索引，這里是 2 個集合結構示例：
csv_import：
{
"_id": "ObjectId(\"6348339cc6e5c8ce0b7da5a4\")",
"index": 23679,
"product_id": 4019734,
"part_no": "CP-HAR-EP-ADVANCED-REN-1Y",
"vendor_standard": "Check Point"
}
CN:
[{
"_id": "1414",
"ICECAT-interface": {
"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
"@xsi:noNamespaceSchemaLocation": "https://data.icecat.biz/xsd/ICECAT-interface_response.xsd",
"Product": {
"@Code": "1",
"@HighPic": "https://images.icecat.biz/img/norm/high/1414-HP.jpg",
"@HighPicHeight": "400",
"@HighPicSize": "43288",
"@HighPicWidth": "400",
"@ID": "1414",
"@LowPic": "https://images.icecat.biz/img/norm/low/1414-HP.jpg",
"@LowPicHeight": "200",
"@LowPicSize": "17390",
"@LowPicWidth": "200",
"@Name": "C6614NE",
"@IntName": "C6614NE",
"@LocalName": "",
"@Pic500x500": "https://images.icecat.biz/img/gallery_mediums/img_1414_medium_1480667779_072_2323.jpg",
"@Pic500x500Height": "500",
"@Pic500x500Size": "101045",
"@Pic500x500Width": "500",
"@Prod_id": "C6614NE",
解決方案
我確實在 csv_import 的 part_no 欄位上添加了一個索引，并將查詢的順序更改為從小到大（EN 為 27GB，csv_import 為幾 MB）
最終查詢：（包括 nimrod serok 提出的建議
db.csv_import.aggregate([
{
$lookup: {
from: 'EN',
let: {pn:'$part_no'},
pipeline: [{
$match: {
$expr: {
$eq: ["$$pn","$ICECAT-interface.Product.@Prod_id"]
}
}
}],
as: 'part_number_info'
}
},{$match: {"part_number_info.0": {$exists: true}}}
])
uj5u.com熱心網友回復：
更好的選擇是使用：
{$match: {"part_number_info.0": {$exists: true}}}
看看它在操場上的例子是如何作業的
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/522799.html
標籤：mongodb加入索引