This article comes from the excellent case award of 2021MongoDB technology practice and application case collection activity
Author: Rachel

a citation

MongoDB supports multi key indexing in earlier versions to speed up array retrieval, which is very popular with programmers; In version 4.2, the wildCard index is launched to support object and array retrieval. The two indexes have similarities, but wildCard is more powerful in function. In daily work, some students are vague about the use scenarios of these two indexes. Therefore, here we offer a brick to attract jade for your reference.

Multi-key index

Multi key supports efficient query of arrays.

give an example:

db.employee1.insertMany([
{"name":"xiaoming","age":25,"ctime":new ISODate(),goodAt:
["mongodb","hbase","c++"]},{"name":"xiaohong","age":28,"ctime":new
ISODate(),goodAt:["es","java","c++"]},{"name":"xiaoguang","age":29,"ctime":new
ISODate(),goodAt:["mysql","c++","mongodb"]}
])
--index
db.employee1.createIndex({goodAt:1})
--lookup
db.employee1.find({"goodAt":"mysql"})

In the result of explain, winningplan inputStage. The stage is IXSCAN, and the index goodat is used_ 1. Here word

The segment "mysql" is a complete array element. Here are two more tests:

Intrusion query test

If the array element is a json string, you cannot query the attribute of an element through the multi key index

db.employee1.insertMany([{
"name":"a",
"age":25,
"ctime":new ISODate(),
"goodAt":[
 {database:"mysql", lang:"c++"},
 {database:"hbase",lang:"java"}, 
 {database:"tidb",lang:"golang"}
 ]
}])
--intercept json Property, not supported; db.employee1.find({"goodAt":{"database":"mysql"}}).explain() /**Go to the index and the result is
 Empty, no element satisfying the condition**/
db.employee1.find({"goodAt":{"database":"mysql", "lang" : "c++" }}).explain() 
/**Go to the index and the result is not empty**

The following wording is recommended:

 --recursion
db.employee1.find({"goodAt.database":"mysql"}).explain() /**No index, the result is not empty
**/

If you want to query the database field, you can only query goodat Database indexing

db.employee1.createIndex({"goodAt.database":1})
db.employee1.find({"goodAt.database":"mysql"}).explain() /**Go to the index and the result is not empty
**/

tips:

Multi key is suitable for indexing arrays

Cannot hash an array

Query of nested objects is not supported;

WildCard index

In the above, when querying a field of an array element, you need to index the field separately, which is very inconvenient to use. In mongodb4 Version 2 introduces the wildCard index, supports the retrieval of objects and arrays, and can invade the internal traversal of elements, which is very convenient.

Multi attribute set, ok:{k1:v1,k2:v2}, index ok

give an example:

db.employee2.insertMany([
{
"name":"xiaoming",
"age":25,
"ctime":new ISODate(),
"goodAt":{
"database":["mongodb","hbase"],
"programLanguage":"c++"
}
},
{
"name":"xiaohong",
"age":28,
The index has not been created at this time. Please query goodAt For a property, you can see stage by COLLSCAN
 add to wildCard After index
"ctime":new ISODate(),
"goodAt":{
"database":"mysql",
"programLanguage":"java",
"middleAware":"zookeeper"
}
},
{
"name":"xiaoguang",
"age":29,
"ctime":new ISODate(),
"goodAt":{
"database":"mongodb",
"programLanguage":"python",
"web":"nodejs"
}
}
])

At this time, the index has not been created. Query a property of goodAt and you can see that the stage is COLLSCAN

db.employee2.find({"goodAt.database": "mysql"}).explain()

After adding the wildCard index

--yes goodAt Indexing
db.employee2.createIndex({ "goodAt.$**": 1 })
db.employee2.find({"goodAt.database": "mongodb"}).explain()

In the element "name":"xiaoming", goodat The value of the database field is an array. Let's see if we can match the index

db.employee2.find({"goodAt.database": "mongodb"}).explain()

The wildCard index also supports a multi key index, which can index and match the array elements.

Intrusion query test

Further, add objects under the array elements in the wildCard index. Can I go through the index? We're at goodat In the database attribute, add the array attribute and conduct subordinate tests. The goal is to confirm whether the wildCard can recurse in the array;

db.employees2.insert(
{
"name":"xiaohong1",
"age":29,
"ctime":new ISODate(),
"goodAt":{
"database":[{"rdb":"mysql"},
 {"nosql":["mongodb","redis"]},
 {"newsql":"tidb"}
 ],
"programLanguage":"go"
 }
})
db.employee2.find({"goodAt.database.nosql": "mongodb"}).explain()

Obviously, the wildCard index supports retrieval from array elements.

db.employees2.insert(
{
"name":"a",
"age":29,
"ctime":new ISODate(),
"goodAt":{
"database":{"rdb":"mysql","nosql":"mongodb","newsql":"tidb"},
"programLanguage":"go"
 }
})
db.employee2.find({"goodAt.database.nosql": 1}).explain()

Returning to the example in our multi key, is it feasible to change the index to wildCard?

db.employee1.dropIndexes('goodAt_1')
db.employee1.createIndex({ "goodAt.$**": 1 })
db.employee1.find({"goodAt.database":"mysql"}).explain()

Can meet the demand. be careful:

wildCard cannot support more than two levels of array nesting

wildCard also does not support index access to the following queries

db.employee1.find({"goodAt":{"database":"mysql"}}).explain()
It is recommended to use {"goodAt.database":1} instead of {goodAt:{"database":1}} to query sub attributes, which is more index friendly.

Summary

Multi key and wildCard indexes are applicable to different scenarios respectively, making entry modeling easier. When using, pay attention to:

Multi key index mainly speeds up array traversal, with pure function;

wildCard can intrude into objects or arrays to avoid single attribute index creation, which is more flexible;

wildCard will not traverse arrays that are nested more than two consecutive layers;

It is not recommended to nest too many layers, and try to control it within 3 layers;

About Author: Rachel

The senior operation and maintenance engineer of the database team of Sohu group has rich experience in database operation and maintenance, is proficient in database architecture design, performance optimization and fault diagnosis, is currently responsible for the operation and maintenance management of MySQL and MongoDB, participates in the development and construction of the company's database cloud platform, and integrates the operation and maintenance experience into the company's database cloud platform. Focus on CDC service construction. Willing to communicate and learn more with you and contribute to the community!