MongoDB (2) MongoDB boost

1. Index

Most of MongoDB's indexes are built on Btree, and each collection can have up to 64 indexes

1) Single-field index creation

Db.user.createIndex ({age:1})//1 represents ascending, 1 means descending

Db.user.createIndex ({age:1},{"unique": true})//Unique index

Db.person.createIndex ({age:1}, {spare:ture}) Create a sparse index//index for the field that does not contain the field.

Db.user.createIndex ({age:1}, ...)

Parameter description:

Background,boolean, indexing is established in the background so that other database activities are not blocked when indexing. The default value is False.

Unique,boolean, create a unique index. The default value is False.

Name,string, specifying the name of the index. If not specified, MongoDB generates an indexed field with the name and sort order concatenation.

Sparse,boolean, the index is not enabled for field data that does not exist in the document. The default value is False.

V,index version, the revision number of the index.

Weights,document, index weight value between 1 and 99,999, indicating the score weight of the index relative to other indexed fields

2) composite Index creation

Db.person.createIndex ({age:1, name:1})

3) Delete Index

Db.user.dropIndex ({age:1/-1})//Remove Index

Db.collection.dropIndexes ();//Delete all indexes of the collection

4) Some common methods of indexing

Db.user.reindex ()//purpose similar to MySQL defragmentation, reducing voids.

Db.user.getIndexes ()//View Index

Db.user.totalIndexSize ()//view the amount of space the index occupies

5) explain () optimization analysis

Db.user.find ({age:{$gt: 9990}}). Explain ("executionstats")//execution plan

Remark: https://docs.mongodb.com/v3.2/reference/explain-results/#queryplanner

{"Queryplanner": {"plannerversion": 1,//Plan Version "namespace": "Admin.user",//Library. Collection            "Indexfilterset": false,//whether the index is used, false means no "parsedquery": {//parse query condition, i.e. filter condition            "Age": {"$GT": 9990}, "Winningplan": {//Auto-optimized execution plan "Stage": "Collscan",//Scan mode: Collscan full table Scan, IXSCAN Index Scan, FETCH root index to retrieve documents, Shard_merge Merge shard results, etc., see Remarks "F            Ilter ": {//Filter condition" age ": {" $GT ": 9990}    }, "direction": "Forward"//direction forward}, "Rejectedplans": []//Rejected execution plan }, "Executionstats": {//Execution plan Information "executionsuccess": true,//execution successful status "Nret  Urned ": 9,//returns the number of result sets" Executiontimemillis ": 2,//Execution time, MS" totalkeysexamined ": 0,//Index Scan entry       "totaldocsexamined": 10004,//Document Scan entry "Executionstages": {"stage": "Collscan ",///slightly, with Queryplanner's Winningplan" filter ": {" age ": {" $GT ": 999 0}}, "nreturned": 9,//returns the number of result sets "Executiontimemillisesti Mate ": 0,//Estimated execution time, MS" Works ": 10006,//number of working units" advanced ": 9,/ /returns the number of intermediate results "needtime": 9996, "Needyield": 0, "saveState": +, "restore            State ": +," IsEOF ": 1," invalidates ": 0," direction ":" Forward ",//direction "docsexamined": 10004//Document Scan}}, "ServerInfo": {"host": "Fatale", "Port    ": 27017," version ":" 3.6.7-14-g7f3489f445 "," gitversion ":" 7f3489f445318e468be4534d7e5eedced033d9a3 "   }, "OK": 1}

Note:

Stage: The following parameters are available for scanning mode

Collscan,ixscan,fetch,shard_merge,sort,limit,skip,idhack,sharding_filter,count,countscan,count_scan,subpla, Text,projection;

Don't want to see: collscan (full table sweep), sort (using sort but no index), Skip,subpla ($or not used for index)

Want to see: IXSCAN index Scan, FETCH root index to retrieve document

MongoDB (2) MongoDB boost