京公网安备11010502063487号视图是物化的吗?能走索引吗?这篇建了两个视图,看 listCollections 和 system.views 里存了什么,用 explain 对比条件能不能前移到 $group 之前,再试各种写入和限制。
test> db.createView("paid_orders", "orders", [{$match: {status: "paid"}}, {$project: {secret: 0}}])
{ ok: 1 }
test> db.getCollectionInfos({name: "paid_orders"})
[
{
name: 'paid_orders',
type: 'view',
options: {
viewOn: 'orders',
pipeline: [
{ '$match': { status: 'paid' } },
{ '$project': { secret: 0 } }
]
},
info: { readOnly: true }
}
]
test> db.system.views.find()
[
{
_id: 'test.paid_orders',
viewOn: 'orders',
pipeline: [ { '$match': { status: 'paid' } }, { '$project': { secret: 0 } } ]
}
]
test> db.getCollectionInfos().map(c => c.name + ":" + c.type)
[ 'system.views:collection', 'orders:collection', 'paid_orders:view' ]视图在 system.views 里只存 viewOn 和 pipeline,类型是 view,只读,不含任何数据。
test> db.paid_orders.countDocuments({})
1000
test> db.orders.updateMany({_id: {$lt: 6}}, {$set: {status: "paid"}}).modifiedCount
4
test> db.paid_orders.countDocuments({})
1004
test> db.paid_orders.findOne({_id: 3})
{ _id: 3, user: 'u3', status: 'paid', amount: 13 }每次查询都重新执行 pipeline,所以底层数据一改,视图立刻跟着变。
test> db.createView("by_user", "orders", [{$group: {_id: "$user", total: {$sum: "$amount"}}}])
{ ok: 1 }
视图 paid_orders = orders 上 [{$match: {status: "paid"}}, {$project: {secret: 0}}]:
test> db.paid_orders.find({user: "u3"}).explain("executionStats")
{
plan: 'PROJECTION_SIMPLE > FETCH > IXSCAN(user_1)',
optimizedPipeline: true,
totalKeysExamined: 150,
totalDocsExamined: 150,
nReturned: 51
}
对照:直接在 orders 上查同样的条件
test> db.orders.find({status: "paid", user: "u3"}).explain("executionStats")
{
plan: 'FETCH > IXSCAN(user_1)',
optimizedPipeline: undefined,
totalKeysExamined: 150,
totalDocsExamined: 150,
nReturned: 51
}
视图 by_user = orders 上 [{$group: {_id: "$user", total: {$sum: "$amount"}}}]:
test> db.by_user.explain("executionStats").aggregate([{$match: {_id: "u3"}}]) // 条件在分组键 _id 上
{
plan: 'GROUP > FETCH > IXSCAN(user_1)',
optimizedPipeline: true,
totalKeysExamined: 150,
totalDocsExamined: 150,
nReturned: 1
}
test> db.by_user.explain("executionStats").aggregate([{$match: {total: {$gt: 1000}}}]) // 条件在聚合结果 total 上
{
plan: 'MATCH > GROUP > COLLSCAN',
optimizedPipeline: true,
totalKeysExamined: 0,
totalDocsExamined: 3000,
nReturned: 20
}视图自己不能建索引,查询用底层集合的索引。优化器会把对视图的过滤条件尽量前移:在分组键上的条件前移到 $group 之前,走底层索引;在聚合结果字段上的条件前移不了,只能全表扫描。
test> db.paid_orders.insertOne({a: 1})
Namespace test.paid_orders is a view, not a collection
test> db.paid_orders.createIndex({user: 1})
Namespace test.paid_orders is a view, not a collection
test> db.paid_orders.updateOne({_id: 3}, {$set: {a: 1}})
Namespace test.paid_orders is a view, not a collection
test> db.paid_orders.find().sort({$natural: 1}).toArray().length
1004
test> db.paid_orders.renameCollection("x")
cannot rename view: test.paid_orders对视图的写入、建索引、更新、rename 都会报错;$natural 排序可以。
连续把 lv(i-1) 作为 lv(i) 的 viewOn 创建视图:成功到 lv20,下一个报错: View depth limit exceeded; maximum depth is 20, full error: {'ok': 0.0, 'errmsg': 'View depth limit exceeded; maximum depth is 20', 'code': 视图套视图最多 20 层。
test> db.paid_orders.find().toArray().length
0
test> db.getCollectionInfos({name: "paid_orders"})[0].options.viewOn
orders
test> db.v2.countDocuments({})
3
test> db.runCommand({collMod: "v2", viewOn: "src2", pipeline: [{$match: {k: {$gte: 3}}}]})
{ ok: 1 }
test> db.v2.countDocuments({})
7底层集合被删除后视图仍在,查询返回空;collMod 可以直接修改视图的 pipeline。