preface

Last article introduced the basic operation of MongoDB index, including index view, create, delete, specific can refer to: MongoDB not professional refers to north (11) : index operation (on). The rest of this article covers additional operations on MongoDB indexes.

Sparse Index

A sparse index is only used to build an index for documents with this field, and is treated as null for documents without this field. Taking the previous employee as an example, we add a mobile phone number field mobile to the employee, and then assume that some employees do not have a mobile phone number and the mobile phone number of the employee cannot be the same.

db.employees.update({name: 'Jenny'}, {$set: {mobile: '13800138000'}});
db.employees.update({name: 'Island code farmer'}, {$set: {mobile: '13800138001'}});
db.employees.update({name: 'Amy'}, {$set: {mobile: '13800138002'}});
Copy the code

The data set used is as follows:

{
	"_id" : ObjectId("60d734f0d8079507891982a8")."name" : "Island code farmer"."dept" : "R&d Department"."languages" : [
		"Dart"."Java"."Javascript"]."age" : 30."totalExp" : 10."mobile" : "13800138001"
}
{
	"_id" : ObjectId("60d734f0d8079507891982a9")."name" : "Amy"."dept" : "R&d Department"."languages" : [
		"Java"."Go"]."age" : 35."totalExp" : 11."mobile" : "13800138002"
}
{
	"_id" : ObjectId("60d734f0d8079507891982aa")."name" : "Bob"."dept" : "Testing department"."languages" : [
		"Java"."Javascript"]."age" : 36."totalExp" : 14
}
{
	"_id" : ObjectId("60d734f0d8079507891982ab")."name" : "Cathy"."dept" : "R&d Department"."languages" : [
		"Javascript"."Python"]."age" : 31."totalExp" : 4
}
{
	"_id" : ObjectId("60d734f0d8079507891982ac")."name" : "Mike"."dept" : "Testing department"."languages" : [
		"Java"."Python"."Go"]."age" : 26."totalExp" : 3
}
{
	"_id" : ObjectId("60d734f0d8079507891982ad")."name" : "Jenny"."dept" : "R&d Department"."languages" : [
		"Java"."Javascipt"."Dart"]."age" : 26."totalExp" : 3."mobile" : "13800138000"
}
Copy the code

At this point, if we want to ensure the uniqueness of the phone number, directly create a unique index of the phone number.

db.employees.createIndex({mobile: 1}, {unique: true});
Copy the code

In this case, the system will prompt the creation failure because the mobile of multiple documents is null and uniqueness cannot be guaranteed. Sparse indexes can be used to solve this problem.

db.employees.createIndex({mobile: 1}, {unique: true.sparse: true});
Copy the code

You can see the sparse indexes created with getIndexes:

{
		"v" : 2."unique" : true."key" : {
			"mobile" : 1
		},
		"name" : "mobile_1"."sparse" : true
	}
Copy the code

Sparse indexes are more compact than normal indexes. If less than 10% of the documents in a data set specify the field, you can create indexes with less storage space and less memory resources, as in the following query:

db.employees.find({mobile: '13800138000'});
Copy the code

Partial Indexes

Partial indexing means that you can index documents that satisfy a condition in part of a data set, rather than all data. Only employees over 35 are indexed below (35 is the threshold for programmers, you know).

db.employees.createIndex(
	{age: 1}.
  {partialFilterExpression: {age: {$gt: 35}}}
);
Copy the code

Get the corresponding index as follows:

{
		"v" : 2."key" : {
			"age" : 1
		},
		"name" : "age_1"."partialFilterExpression" : {
			"age" : {
				"$gt" : 35}}}Copy the code

Partial indexes can have less storage space than queries that often have to meet certain criteria. A partial index is different from a sparse index. A sparse index is based on whether a field is created, while a partial index is based on filtering conditions. Moreover, partial indexes can achieve sparse indexes through filtering conditions to determine whether a field exists.

db.employees.createIndex(
	{mobile: 1}.
  {partialFilterExpression: {mobile: {$exists: true}}}
);
Copy the code

Note that sparse indexes and partial indexes cannot exist on the same field. Also, when a field has an index, you cannot create a partial index.

The composite index

Multiple fields can be specified to create a composite index at index creation time. This is somewhat similar to SQL syndication.

db.employees.createIndex({name: 1. age: - 1});
Copy the code

A composite index can be queried by name, name, or age, while a single age query cannot use the index. Similarly for sorting, it is necessary to match the index order.

Independent index

Add {unique: true} to the attribute that creates the index.

db.employees.createIndex({name: 1}, {unique: true});
Copy the code

Indexing fails if the dataset has a field with the same attribute, including more than two documents that do not specify the field.

conclusion

It can be seen that MongoDB index not only has SQL common index, but also supports sparse index and partial index, which should be the improvement of MongoDB to improve some defects of relational database. In practice, you can choose how to use indexes according to your needs.