DDS supports a rich indexing mechanism, and the proper use of indexes can speed up data retrieval and reduce resource consumption.
Single-field index
For example, the coll table contains documents in the following format:
{ "_id" : ObjectId("64bde367d89fa22ccaa281e9"), "score" : 98, "name" : "zhang san" }
{ "_id" : ObjectId("64bde367d89fa22ccaa281ea"), "score" : 88, "name" : "li si" }As the business grows and the number of documents gradually increases, it becomes more difficult to sort by score and extract the top 10 documents because it involves full table scanning and memory sorting.
At this point, you can create an index by the score field to speed up the query. The command to create an index using mongo shell is as follows:
db.coll.createIndex({score:1})The score field contained in the command indicates the key of the index, where 1 represents ascending order and -1 represents descending order. DDS creates an index by the score field, and the underlying implementation is in the form of b-tree. When the user perform equivalent queries or sorting through the score field, DDS will automatically select an index to speed up.
Users can also check which indexes are currently included in the table by running the following command after connecting to the instance:
db.coll.getIndexes()
Multi-field composite index
Assuming that the coll table contains the following documents:
{ "_id" : ObjectId("64bde367d89fa22ccaa281e9"), "score" : 98, "name" : "zhang san" }
{ "_id" : ObjectId("64bde367d89fa22ccaa281ea"), "score" : 88, "name" : "li si" }
{ "_id" : ObjectId("64bde73ad89fa22ccaa281eb"), "score" : 98, "name" : "wang wu" }
{ "_id" : ObjectId("64bde73ad89fa22ccaa281ec"), "score" : 98, "name" : "ma liu" }If you want to query all documents with score=98 and sort them in ascending order by the name field.
At this point, you can create a composite index on the score and name fields. The command to create a composite index using mongo shell is as follows:
db.coll.createIndex({score:1, name:1})Composite index can handle more scenarios than single-field index. For example, the above index can handle not only the joint query scenario of score+name, but also the scenario of querying and sorting by the score equivalence.
But there are some considerations when using composite indexes:
Composite indexes follow the principle of prefix matching. For example, {score: 1, name: 1} The composite index can handle scenarios where the query is by score, but not by name. Therefore, special attention should be paid to the order of fields when creating indexes.
The more fields, the larger the storage space of the index. For example, in a scenario where the user only queries and sorts by the score field, there is no need to create a composite index.
Hash index
If it only involves equivalent query for a single field, you can consider creating a hash index. The way to create a hash index using the mongo shell is as follows:
db.coll.createIndex({score: "hashed"})The underlying hash index is also stored using btree, but the difference is that the key of the btree is the value after hash.
For a sharded version of the document database instance, if you create a sharded table using hash method, then a hash index based on the sharding key is automatically created on each shard server.
Hash indexes have some limitations:
Unique properties cannot be specified.
Range scanning is not possible.
If the field to be indexed is very long, the hash index is based on a 64-bit hash value, which can save the storage space of the index to a certain extent.
Index property
When creating an index, you can specify properties to complete more advanced functions. Common configuration properties include:
TTL. Enables automatic data expiration and deletion. For example, if the system running log is stored in the coll table, which contains createTime to specify the log generation time, you can create a TTL index based on this field to automatically delete data older than 1 hour: db.coll.createIndex( { "createTime": 1 }, { expireAfterSeconds: 3600 } ).
Unique. Specifies the uniqueness of a field. If the data with the same value in this field is repeatedly inserted, the error will fail, and the specified method is db.coll.createIndex( { "name": 1 }, { unique: true } ).
Partial. Only documents that meet the criteria are indexed. For example, only documents with a score greater than 80 will be indexed: db.coll2.createIndex({score:1, name:1}, {partialFilterExpression: {score: {$gt: 80}} }).