京公网安备11010502063487号哪些文档会进稀疏索引、哪些进部分索引?优化器什么时候会用、什么时候不会?这篇用 3 万个文档把缺失、null、字符串三种 email 和 5% 的 inactive 状态逐个验证,最后用部分唯一索引做软删除。
test> var c = db.t; ({total: c.countDocuments({}), hasEmail: c.countDocuments({email: {$type: "string"}}), nullEmail: c.countDocuments({email: {$type: "null"}}), missing: c.countDocuments({email: {$exists: false}}), inactive: c.countDocuments({status: "inactive"})})
{
total: 30000,
hasEmail: 20000,
nullEmail: 5000,
missing: 5000,
inactive: 1500
}字段有字符串值、显式 null、字段缺失是三种不同情况,稀疏索引、唯一索引、部分索引对它们的处理各不相同。
普通索引条目数 30,000;稀疏索引条目数 25,000(= 有值 + 显式 null) 索引大小:普通 401,408 字节,稀疏 385,024 字节
只收录字段存在的文档(显式 null 也算存在)。需要缺失的文档时优化器不会选它,硬 hint 或用它排序会漏掉缺失的文档。
test> db.t.find({email: null}) // 两个索引都在,优化器怎么选
{
plan: 'FETCH > IXSCAN(email_full)',
nReturned: 10000,
keysExamined: 10000,
docsExamined: 10000,
ms: 6
}
test> db.t.find({email: null}).hint("email_sparse") 与实际数量
5000 vs 实际 10000
test> db.t.find().sort({email: 1}).hint("email_sparse") // 排序用稀疏索引也会漏
25000 vs 实际 30000用 hint("email_sparse") 查 {email: null} 只返回 5000 个(实际 10000),对全集合排序只返回 25000 个(实际 30000)。
部分索引 score_inactive 条目数 1,500(只有 inactive 的),普通索引 score_all 条目数 30,000
索引大小: score_inactive 24,576 字节,score_all 155,648 字节
test> db.t.find({status: "inactive", score: 5}) // 条件蕴含过滤条件
{
plan: 'FETCH > IXSCAN(score_inactive)',
nReturned: 0,
keysExamined: 0,
docsExamined: 0,
ms: 0
}
test> db.t.find({score: 5}) // 没有 status 条件,部分索引不能用
{
plan: 'COLLSCAN',
nReturned: 300,
keysExamined: 0,
docsExamined: 30000,
ms: 5
}
test> db.t.find({status: {$in: [inactive, active]}, score: 5})
{
plan: 'COLLSCAN',
nReturned: 300,
keysExamined: 0,
docsExamined: 30000,
ms: 5
}只收录满足过滤条件的文档,体积小得多;查询条件必须蕴含过滤条件才会用它,没有写 status 条件就是全表扫描。
test> 再插入一个未删除、同 email 的文档
E11000 duplicate key error collection: test.u index: email_1 dup key: { email: "a@x.com" }
test> 把 _id 1 软删除之后,同 email 的新文档可以插入
true
test> 再插一个同 email 且已删除的文档
true只给未删除的文档建唯一索引,已删除的文档可以重复;软删除之后同 email 的新文档可以插入。
test> db.p1.createIndex({a: 1}, {partialFilterExpression: {a: {$exists: true}}})
a_1
test> db.p2.createIndex({a: 1}, {partialFilterExpression: {$or: [{a: 1}, {b: 2}]}})
a_1
test> db.p3.createIndex({a: 1}, {partialFilterExpression: {a: {$ne: 1}}})
Expression not supported in partial index: $not
a $eq 1
test> db.p4.createIndex({a: 1}, {sparse: true, partialFilterExpression: {a: {$gt: 1}}})
cannot mix "partialFilterExpression" and "sparse" options9.0.2 里过滤条件可以用 $exists: true、$or,不能用 $ne($not);不能和 sparse 同时指定。