京公网安备11010502063487号想做大小写不敏感的查询,用 collation 还是正则?索引还能不能用?这篇用 20 万个名字对比二进制、collation 查询、匹配与不匹配的索引、正则的耗时和计划,并看 collation 对排序和索引大小的影响。
test> db.names.find({n: "alice"}).toArray().map(d => d.n)
[ 'alice' ]
test> db.names.find({}, {_id: 0}).sort({n: 1}).toArray().map(d => d.n)
[
'ALICE', 'Alice', 'Bob',
'Elan', 'Zoe', 'a1',
'a10', 'a2', 'alice',
'arger', 'bob', 'zoe',
'Ärger', 'élan'
]不指定 collation 时按字节比较:大小写不同是不同的值,排序是大写在前、小写在后、带重音的字符最后。
test> db.names.find({n: "alice"}).collation({locale: "en", strength: 2}).toArray().map(d => d.n)
[ 'alice', 'Alice', 'ALICE' ]
test> db.names.find({n: "arger"}).collation({locale: "en", strength: 2}).toArray().map(d => d.n)
[ 'arger' ]
test> db.names.find({n: "arger"}).collation({locale: "en", strength: 1}).toArray().map(d => d.n)
[ 'Ärger', 'arger' ]
test> db.names.find({}, {_id: 0}).sort({n: 1}).collation({locale: "en"}).toArray().map(d => d.n)
[
'a1', 'a10', 'a2',
'alice', 'Alice', 'ALICE',
'arger', 'Ärger', 'bob',
'Bob', 'Elan', 'élan',
'zoe', 'Zoe'
]collation 的 strength: 2 忽略大小写但区分重音,strength: 1 连重音也忽略;locale 决定排序规则。
test> db.names.find({n: /^a\d/}, {_id: 0}).sort({n: 1}).collation({locale: "en", numericOrdering: true}).toArray().map(d => d.n)
[ 'a1', 'a2', 'a10' ]
test> db.cn.find().sort({n: 1}) // 默认:按 UTF-8 字节
[
'刘', '张',
'李', '杨',
'王', '赵',
'陈'
]
test> db.cn.find().sort({n: 1}).collation({locale: "zh"}) // 拼音顺序
[
'陈', '李',
'刘', '王',
'杨', '张',
'赵'
]numericOrdering: true 让 a2 排在 a10 前面;locale: "zh" 按拼音排序中文。
test> db.big.find({n: "alice77"}).collation({locale: "en", strength: 2}).explain() // 索引 n_1 是二进制比较
{
plan: 'COLLSCAN',
nReturned: 1,
totalKeysExamined: 0,
totalDocsExamined: 200000
}
test> 同样的查询,建了 {n: 1} + collation {locale: "en", strength: 2} 的索引 n_ci 之后
{
plan: 'FETCH > IXSCAN(n_ci)',
nReturned: 1,
totalKeysExamined: 1,
totalDocsExamined: 1
}
test> 不带 collation 的查询仍然用 n_1 索引
{ plan: 'FETCH > IXSCAN(n_1)', nReturned: 1, totalKeysExamined: 1 }查询的 collation 必须与索引的 collation 相同才能走索引;否则退化为全表扫描,检查全部 200,000 个文档。
精确等值(二进制索引 n_1) 0.2 ms collation strength 2 + 同 collation 索引 0.3 ms collation strength 2 但索引 collation 不匹配(全表) 28.8 ms 正则 /^alice77$/i 60.1 ms
匹配的 collation 索引下查询 0.3 ms,几乎不比精确查找慢;全表 collation 查询 28.8 ms;正则 /i 60.1 ms,更慢。
test> db.getCollectionInfos({name: "ci"})[0].options
{
collation: {
locale: 'en',
caseLevel: false,
caseFirst: 'off',
strength: 2,
numericOrdering: false,
alternate: 'non-ignorable',
maxVariable: 'punct',
normalization: false,
backwards: false,
version: '57.1'
}
}
集合里存着 'Erin11980',现在用大小写相反的 'eRIN11980' 去查(查询里没有写 collation):
test> db.ci.find({n: "eRIN11980"}) 与 db.bin.find({n: "eRIN11980"})
2 个(ci 集合),0 个(普通二进制集合)
test> db.ci.getIndexes()
[
{
v: 2,
key: { _id: 1 },
name: '_id_',
collation: {
locale: 'en',
caseLevel: false,
caseFirst: 'off',
strength: 2,
numericOrdering: false,
alternate: 'non-ignorable',
maxVariable: 'punct',
normalization: false,
backwards: false,
version: '57.1'
}
},
{
v: 2,
key: { n: 1 },
name: 'n_1',
collation: {
locale: 'en',
caseLevel: false,
caseFirst: 'off',
strength: 2,
numericOrdering: false,
alternate: 'non-ignorable',
maxVariable: 'punct',
normalization: false,
backwards: false,
version: '57.1'
}
}
]
同样 100,000 个文档上的 n_1 索引大小:二进制 1,216,512 字节,collation(en, strength 2) 1,421,312 字节(1.17 倍)创建集合时指定 collation,查询与索引默认继承;collation 索引比二进制索引大约 17%。