MongoDB Index Creation

operation of the index

The work of database 80% is basically a query, and the index can help us to query the desired data more quickly. But it reduces the write speed of the data, so it is necessary to weigh the common query fields without having to index on too many fields.
The default is to use Btree to organize index files in MongoDB, and can be created by word orderby/descending order for easy sorting. Data Preparation

for (var i = 1; I <100000; i++) {
  Db.test.insert ({name: ' User ' +i,num:i,sn:math.floor (math.random () *10000000)})
}

Index Common Operations View the index of the current collection

> db.test.getIndexes ();
[
    {
        "V": 1,
        "key": {
                "_id": 1
        },
        "name": "_id_",
        "ns": "Test.test"
    }
]

MongoDB has a default _id key, which is equivalent to the "primary key" role. After the collection is created, the system automatically creates an index on the _id key, which is the default index, and the index is called "_id" and cannot be deleted.

In addition, the System.indexes collection contains detailed information about each index, so you can query for an existing index by using the following command

Db.system.indexes.find ({});

Create a single-column index

Db.collection.ensureIndex ({field:1/-1}) # 1 is a positive sequence,-1 is reverse

create multi-column indexes (combined index)

Db.collection.ensureIndex ({field1:1/-1, field2:1/-1})  

In most cases, the indexes we create are multi-column indexes, because the database finder selects only the optimal index for querying, and the query only selects one of the columns of the index for querying, and a multi-column index is more efficient than a single-column index, because it works on multiple columns. Specific multi-column index building techniques can be seen below in the << new explain analysis instance >>, in addition, MongoDB's multi-column index also follows the principle of the leftmost prefix

Db.test.ensureIndex ({"username": 1, "Age":-1})

After the index is created, queries based on username and age will use the index, or username-based queries will use the index, but only the age-based query would not use the composite index. So you can say that if you want to use a composite index, you must include the first n index columns in the composite index in the query criteria. However, if the order of key values in the query condition is inconsistent with the order of creation in the composite index, MongoDB can intelligently help us adjust that order so that the composite index can be used by the query

Db.test.find ({"Age": +, "username": "Stephen"})

For the query criteria in the example above, MongoDB will dynamically adjust the order of the query criteria document before retrieving it so that the query can use the composite index that was just created. Create sub-document index

Db.collection.ensureIndex ({' Filed.subfield ': 1/-1});

Create a unique index

Db.collection.ensureIndex ({filed:1/-1}, {unique:true});

Create a sparse index

Features of sparse indexes------If you index a field, the index is not indexed for documents that do not contain field columns.
The normal index, in contrast, considers the value of the field column of the document NULL, and builds the index.
Appropriate: When a small number of documents contain a column.

Db.tea.find ();
{"_id": ObjectId ("5275f99b87437c610023597b"), "email": "a@163.com"}
{"_id": ObjectId ("5275f99e87437c610023597c"), "email": "b@163.com"}
{"_id": ObjectId ("5275f9e887437c610023597e"), "email": "c@163.com"}
{"_id": ObjectId ("5275fa3887437c6100235980")}

Db.collection.ensureIndex ({field:1/-1},{sparse:true});

As above, the last line does not have an email column, if you add a normal index, and a sparse index, the last line of email is treated as null and the last line is ignored. Based on {email:null} to query, the former can use the index, and the latter is not used to index, is a full-table scanning process; To create a hash index

The hash index is faster than the normal index, but the inability to optimize the range query.
Suitable for random and strong hashing

Db.collection.ensureIndex ({file: ' hashed '});

Rebuilding Indexes

A table has been modified many times, resulting in a hole in the table file, as well as the index file. You can reduce the index file fragmentation and improve the efficiency of the index by rebuilding the index. Optimize table similar to MySQL

Db.collection.reIndex ()

Delete Index

Db.collection.dropIndex ({filed:1/-1});  #删除单个索引
db.collection.dropIndexes (); #删除所有索引

The application of regular expressions in indexes

Regular expressions have the flexibility to match query conditions, and if you want the regular expression to hit the index, you should be aware of:
MongoDB can hit the index of a regular expression of the prefix type (as with MySQL), for example: you need to query the mail in the user starting with Z:/^z/
If there is a user index, this kind of query is efficient, but the other, even if there is an index, will not hit the index, than that: you need to query the mail in the user contains Z:

/.*z.*/
/^.*z.*/

This kind of query is not hit to the index, when the data volume is very large, the speed is very slow
In short, ^ after the conditions must be clear, not ^.* ^[a-z] such as the beginning of the query plan explain

I study MongoDB relatively late, installed is 3.05 version, found that this version of the use of explain is very different from the tutorial, exactly what version began to change, I will not go back. New Explain introduction

The new version of the explain has three modes, as explain parameters passed in Queryplanner default executionstats allplansexecution queryplanner

Queryplanner is the default mode for the current version of explain, and Queryplanner mode does not actually query queries, but instead performs a plan analysis and selects winning plan for the query statement.

{"Queryplanner": {"plannerversion": 1, "namespace": "Game_db.game_user", "Indexfilterset":
        false,//whether there is a indexfilter for the query (see below) "Parsedquery": {"W": {"$eq": 1}
            }, "Winningplan": the detailed contents of the optimal execution plan returned by the query optimizer for that query. "Stage": "Fetch",//the stage of the optimal execution plan, where return is FETCH, can be understood as retrieving the specific document by returning the index position (see below) "Inputstage": {//the child on the previous stage
                Stage, here is Ixscan, indicating that the index scanning is in progress.  "Stage": "IXSCAN", "Keypattern": {"W": 1,//index content scanned "n": 1
                The number of bars returned? }, "IndexName": "w_1_n_1",//Index name "Ismultikey": false,//whether is Multikey, return here is false, if index is established in ARR
                Ay, here will be true "direction": "Forward",//The query order, here is forward, if the use of. Sort ({w:-1}) will display backward. "Indexbounds": {//winningplan the index range scanned, here the query condition is w:1, using the index is a joint index W and N, so w is [1.0,1.0] and N is not specified in the query condition, it is [minkey,maXKey]. "W": ["[1.0, 1.0]"], "n": ["[Minkey, Maxkey]"]}}, "Rej Ectedplans ": [{//he executes the detailed return of the plan (non-optimal and reject by the query optimizer), where the specific information is the same as in the return of the Winningplan" stage ":" FETCH "," Inpu
                    Tstage ": {" stage ":" IXSCAN "," Keypattern ": {" W ": 1,
                "V": 1}, "IndexName": "W_1_v_1", "Ismultikey": false, "Direction": "Forward", "Indexbounds": {"w": ["[1.0, 1.0]"], "V ": [" [Minkey, Maxkey] "}}]}

Indexfilterset

Indexfilter determines how the query optimizer will use Index,indexfilter for a type of query to affect only the query optimizer's execution plan analysis that can be used to try which index for that class of queries, or whether the query optimizer chooses the optimal plan based on the analysis.
If a query of a certain type is set to Indexfilter, the query optimizer ignores the index set by hint and still uses the query plan set in Indexfilter when the other index is specified at execution time by the hint.
Indexfilter can be removed by command, and will be emptied after the instance is restarted. creation of Indexfilter

Db.runcommand (
   {
      plancachesetfilter: <collection>,
      query: <query>,
      sort: <sort> ,
      projection: <projection>,
      indexes: [<index1>, <index2>, ...]
   }
)

Db.runcommand (
   {
      plancachesetfilter: "Orders",
      query: {status: "A"},
      indexes: [
         {cust_id: 1, status:1},
         {status:1, order_date:-1}
      ]
   }
)

A indexfilter,indexfilter is established for the Orders table that specifies the indexes of a query for the Orders table with only a status condition (query for status only, no sort, etc.). So the query optimizer for the following query statements will only choose from {cust_id:1,status:1} and {status:1,order_date:-1} winning plan

Db.orders.find ({status: "D"})
Db.orders.find ({status: "P"})

List of Indexfilter

You can show all the indexfilter of a collecton with the following command

Db.runcommand ({plancachelistfilters: <collection>})

deletion of Indexfilter

Indexfilter can be deleted with the following command

Db.runcommand (
   {
      plancacheclearfilters: <collection>,
      query: <query pattern>,
      sort: <sort specification>,
      projection: <projection specification>
   }
)

Stage return parameter description

Collscan #全表扫描

IXSCAN #索引扫描

FETCH #根据索引去检索指定document

shard_merge #将各个分片返回数据进行merge

SORT # Indicates a sort in memory (consistent with the old version of Scanandorder:true)

LIMIT #使用limit限制返回数

SKIP #使用skip进行跳过

idhack #针对_id进行查询

Sharding_filter #通过mongos对分片数据进行查询

Count #利用db. Coll.explain (). Count () for the count Operation

Countscan # Count does not use index to count when the stage returns

Count_scan #count使用了Index进行count时的stage返回

subpla #未使用到索引的 $or The stage of the query

TEXT #使用全文索引进行查询时候的stage返回

PROJECTION #限定返回字段时候stage的返回

executionstats

This mode is the execution state of the MongoDB query, similar to the old version of explain

  "Executionstats": {"executionsuccess": true,//Whether the execution succeeds "nreturned": 29861,//number of returned bars of the query "Executiontimemillis": 23079,//Overall execution Time MS "totalkeysexamined": 29861,//Index Scan Count "totaldocsexamined": 29861,//Document scan Count "Executio Nstages ": {" stage ":" Fetch ",//Here is a fetch to scan for documents" nreturned ": 29861,//Because it is a fetch, so this value with Executionstats.nr eturned consistent "Executiontimemillisestimate": 22685, "Works": 29862,//view source found, each operation will add 1, and will take the execution time recorded in the Executiontimemilli
      S in. "Advanced": 29861,//and at the end of the query Eof,works will add 1,advanced not add.  Normal return works will be 1 more than nreturned, when IsEOF is true (1): In addition, the return value of advanced is only +1 at the time of the hit, the skip,eof will not increase "needtime": 0, "Needfetch": 0, "saveState": 946, "restorestate": 946, "IsEOF": 1, "invalidates": 0, "docsexamined": 298
        61,//consistent with executionstats.totaldocsexamined "Alreadyhasobj": 0, "Inputstage": {"stage": "IXSCAN", "nreturned": 29861, "executiontimemillisestimate": +, "Works": 29862, "Advanced": 29861, "Needtime": 0, "Needfetch": 0, "saveState": 946, "restore State ": 946," IsEOF ": 1," invalidates ": 0," Keypattern ": {" W ": 1," n ": 1