Indexs

Els indexs ajuden al motor de la bases de dades a realitzar consultes de manera molt més eficient.

On this page

Introducció
Indexes

Introducció

La quantitat de dades contingudes en una col·lecció afecta directament el rendiment de la cerca: com més gran sigui el conjunt de dades, més difícil serà per a MongoDB trobar els documents que coincideixen amb la consulta.

Els índexs són estructures de dades especials que emmagatzemen només un petit subconjunt de les dades contingudes en els documents d’una col·lecció per separat dels mateixos documents.

MongoDB crea un índex únic al camp _id durant la creació d’una col·lecció que impedeix als clients inserir dos documents amb el mateix valor per al camp _id. Aquest index no es pot eliminar.

PENDENT

Indexes

It is common to create indexes to improve query performance. Fortunately, MongoDB indexes can be easily created from the shell.

An indexing example only makes sense if you have a collection with many documents.

Therefore, you’ll add 20,000 simple documents to a numbers collection. Since the MongoDB shell is also a JavaScript interpreter, the code to accomplish this is simple:

demo> for (i=0; i < 20000; i++) { db.numbers.insertOne({num:i}) }
{
  acknowledged: true,
  insertedId: ObjectId('6782894472c54e457acb5b06')
}

That’s a lot of documents, so don’t be surprised if the insertion takes a few seconds to complete. Once it returns, you can run a couple of queries to verify that all documents are present:

demo> db.numbers.countDocuments()
20000
demo> db.numbers.find()
[
  { _id: ObjectId('6782893c72c54e457acb0ce7'), num: 0 },
  { _id: ObjectId('6782893c72c54e457acb0ce8'), num: 1 },
  { _id: ObjectId('6782893c72c54e457acb0ce9'), num: 2 },
  { _id: ObjectId('6782893c72c54e457acb0cea'), num: 3 },
  { _id: ObjectId('6782893c72c54e457acb0ceb'), num: 4 },
  { _id: ObjectId('6782893c72c54e457acb0cec'), num: 5 },
  { _id: ObjectId('6782893c72c54e457acb0ced'), num: 6 },
  { _id: ObjectId('6782893c72c54e457acb0cee'), num: 7 },
  { _id: ObjectId('6782893c72c54e457acb0cef'), num: 8 },
  { _id: ObjectId('6782893c72c54e457acb0cf0'), num: 9 },
  { _id: ObjectId('6782893c72c54e457acb0cf1'), num: 10 },
  { _id: ObjectId('6782893c72c54e457acb0cf2'), num: 11 },
  { _id: ObjectId('6782893c72c54e457acb0cf3'), num: 12 },
  { _id: ObjectId('6782893c72c54e457acb0cf4'), num: 13 },
  { _id: ObjectId('6782893c72c54e457acb0cf5'), num: 14 },
  { _id: ObjectId('6782893c72c54e457acb0cf6'), num: 15 },
  { _id: ObjectId('6782893c72c54e457acb0cf7'), num: 16 },
  { _id: ObjectId('6782893c72c54e457acb0cf8'), num: 17 },
  { _id: ObjectId('6782893c72c54e457acb0cf9'), num: 18 },
  { _id: ObjectId('6782893c72c54e457acb0cfa'), num: 19 }
]
Type "it" for more

The countDocuments() method shows that you have inserted 20,000 documents. The subsequent query shows the first 20 results (this number may be different in your shell).

You can show additional results with the it function:

demo> it
[
  { _id: ObjectId('6782893c72c54e457acb0cfb'), num: 20 },
  { _id: ObjectId('6782893c72c54e457acb0cfc'), num: 21 },
...

The it function tells the shell to return the next set of results.

Next make a query:

demo> db.numbers.find({num:500})
[ { _id: ObjectId('6782893c72c54e457acb0edb'), num: 500 } ]

Ranges

You can also make range queries using the special $gt and $lt operators that represent greater than and less than, respectively.

Next query all documents with a numeric value greater than 19995:

demo> db.numbers.find({num: {"$gt": 19995}})
[
  { _id: ObjectId('6782894472c54e457acb5b03'), num: 19996 },
  { _id: ObjectId('6782894472c54e457acb5b04'), num: 19997 },
  { _id: ObjectId('6782894472c54e457acb5b05'), num: 19998 },
  { _id: ObjectId('6782894472c54e457acb5b06'), num: 19999 }
]

You can also combine both operators to specify upper and lower bounds:

demo> db.numbers.find({num: {$gt: 20, $lt: 25}})
[
  { _id: ObjectId('6782893c72c54e457acb0cfc'), num: 21 },
  { _id: ObjectId('6782893c72c54e457acb0cfd'), num: 22 },
  { _id: ObjectId('6782893c72c54e457acb0cfe'), num: 23 },
  { _id: ObjectId('6782893c72c54e457acb0cff'), num: 24 }
]

You can see that using a simple JSON document, you can specify a range query the same way you would in SQL. $gt and $lt are just two of a series of operators that integrate MongoDB’s query language. Others include $gte for greater than or equal to, $lte for (you guessed it) less than or equal to, and $ne for not equal to.

Of course, queries like this have little value unless they are also efficient:

Explain

When a database receives a query, it must plan how to execute it; this is called a query plan.

explain describes query paths and allows developers to diagnose slow operations by determining which indexes a query has used. Often, a query can be executed in several ways and sometimes this causes behavior you might not expect.

demo> db.numbers.find({num: {"$gt": 19995}}).explain("executionStats")
{
  explainVersion: '1',
  queryPlanner: {
    namespace: 'demo.numbers',
    parsedQuery: { num: { '$gt': 19995 } },
    ...
  executionStats: {
    executionSuccess: true,
    nReturned: 4,
    executionTimeMillis: 4,
    totalKeysExamined: 0,
    totalDocsExamined: 20000,

The “executionStats” keyword requests a different mode that provides more detailed output:

After examining the explain() output, you’ll be surprised to see that the query engine has to scan the entire collection, all 20,000 documents (totalDocsExamined), to return only four results (nReturned). The totalKeysExamined field value shows the number of index entries scanned, which is zero. Such a large difference between the number of documents scanned and the number returned marks this as an inefficient query.

In a real-world situation, where the collection and the documents themselves would probably be larger, the time required to process the query would be substantially longer than the 4 milliseconds (executionTimeInMillis) indicated here (this may be different on your machine).

What this collection needs is an index. You can create an index for the num key of the documents using the createIndex() method:

demo> db.numbers.createIndex({num: 1})
num_1

You can see that you pass a document to the createIndex() method to define the index keys. In this case, the document {num: 1} indicates that an ascending index should be created on the num key for all documents in the numbers collection.

You can verify that the index has been created by invoking the getIndexes() method:

demo> db.numbers.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { num: 1 }, name: 'num_1' }
]

The collection now has two indexes. The first is the standard _id index that is automatically created for each collection; the second is the index you created on num. The indexes for these fields are named _id_ and num_1, respectively. If you don’t provide a name, MongoDB automatically sets meaningful names.

If you run your query with the explain() method, you’ll now see the big difference in query response time, as shown in the following listing:

demo> db.numbers.find({num: {"$gt": 19995}}).explain("executionStats")
{
    ...
  executionStats: {
    executionSuccess: true,
    nReturned: 4,
    executionTimeMillis: 1,
    totalKeysExamined: 4,
    totalDocsExamined: 4,
    ...

Now that the query uses the num_1 index on num, it only scans the four documents related to the query. This reduces the total time to serve the query from 4 ms to 1 ms!

Indexes are not free; they take up some space and can make your insertions slightly more expensive, but they are an essential tool for query optimization.

MongoDB - Indexs

Introducció

Indexes

Ranges

Explain