1024programmer Java Usage of various indexes in mongo

Usage of various indexes in mongo

mongo various indexes Usage

Premise:
To view the status of the database, use: db.stats();
To view the status of a collection, use: db.collection.stats()

  1. Index creation and deletion

db.records.createIndex( { score: 1 } )

The score specified here is 1, which means asc sorting according to the score. If it is -1, desc sorting is tested.
Pay attention when creating the index Be careful to use db.tableName.ensureIndex({index}, {background: true}) as much as possible. MongoDB provides two access methods for index creation – one is the background method – which does not require long-term write locks. ,The other is the non-background method,It needs to occupy the lock for a long time. Using the background method can solve the problem. For example, ,Create an index for very large table posts, Do not use it
How to kill the out-of-control index creation process: http://www.jianshu.com/p/f9d71763e844
/> 2. Create a combined index:

db.collection.createIndex( { : , : , ... }

2.1. Create a unique index. The function of a unique index is to ensure the uniqueness of a field in the collection
db.collection.createIndex({username:1}, {unique:true})
If you create a unique index for an existing table , and you don’t care about retaining duplicate data, you can Use db.collection.createIndex({username:1}, {unique:true, dropDups:true})
3. Get the index of the current collection

db.collection.getIndexes();

4. Delete index

db.collection .dropIndex(index name) deletes all indexes: db.collection.dropIndexes()

5. Analyze indexes

db.collection.find().explain(analysis mode)

The analysis mode has the following values: queryPlanner (default), executionStats, allPlansExecution.
Index output analysis

{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "wm.scoreHistory", // Query Table"indexFilterSet" : false, / / Whether there is indexfilte for this query"parsedQuery" : {"memberId" : {"$eq" : ObjectId("589ad1c89cd0b3003635e4d9")}},"winningPlan" : { // Optimized queryer"stage" : "FETCH",// The stage of the optimal execution plan, the stage has several modes, what this means is the current query FETCH efficiency Fastest"inputStage" : { // To describe the word stage"stage" : "IXSCAN", // stage mode"keyPattern" : { //content of index"memberId" : 1,"createdAt" : - 1},"indexName" : "memberId_1_createdAt_-1", //The name of the index"isMultiKey" : false, //Is it a combined index"isUnique" : false,// Is it a unique index"isSparse" : false, // Is it a sparse index?"isPartial" : false,"indexVersion" : "direction" : "forward","indexBounds" : {"memberId" : ["[ObjectId('589ad1c89cd0b3003635e4d9'), ObjectId( '589ad1c89cd0b3003635e4d9')]"],"createdAt" : ["[MaxKey, MinKey]"]},"keysExamined" : 46,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0}}},"serverInfo" : {"host" : "f4acb39dab64","port" : 27017,"version " : "3.2.7","gitVersion" : "4249c1d2b5999ebbf1fdf3bc0e0e3b3ff5c0aaf2"},"ok" : 1
}

The first layer,executionTimeMillis
The most intuitive explain return value is the executionTimeMillis value,refers to the execution time of our statement&# xff0c; Of course, we hope that the lower the value, the better. There are 3 executionTimeMillis, namely: executionStats.executionTimeMillis,
executionStats.executionStages.executionTimeMillisEstimate,
executionStats.executionStages.inputStage.executionTimeMillisEstimate.
The second layer&#xff0c ;The number of index and document scans and the number of entries returned by the query
This mainly discusses three return items,nReturned, totalKeysExamined, and totalDocsExamined,respectively representing the entries returned by the query, index scan entries, and document scan entries . These all intuitively affect executionTimeMillis – the less we need to scan the faster.
For a query,our most ideal state is:

nReturned=totalKeysExamined=totalDocsExamined

Third layer,stage status analysis
So what affects totalKeysExamined and totalDocsExamined? are stage types. The types are listed as follows:
COLLSCAN: Full table scan
IXSCAN: Index scan
FETCH: Retrieve the specified document based on the index
SHARD_MERGE: Merge the returned data from each fragment
SORT: Indicates that it is sorted in memory
LIMIT: Use limit to limit the number of returns
SKIP: Use skip to skip
br /> IDHACK: Query against _id
SHARDING_FILTER: Query sharded data through mongos
COUNT: Use db.coll.explain().count() and the like Perform count operation
COUNTSCAN:count stage return when counting without using Index
COUNT_SCAN:count stage return when using Index for counting
SUBPLA: is not used Indexed $or query stage return
TEXT: stage return when querying using full-text index
PROJECTION: stage return when limiting the return field
For ordinary query&#xff0c ;I hope to see the combination of stages (using indexes when querying):
Fetch+IDHACK
Fetch+ixscan
Limit+&# xff08;Fetch+ixscan)
PROJECTION+ixscan
SHARDING_FITER+ixscan
COUNT_SCAN
Do not want to see the following stage:
/> COLLSCAN (full table scan), SORT (use sort but no index), unreasonable SKIP, SUBPLA (\$or without index), COUNTSCAN (count without using index)
6. Specify Index

db.collection.find ().hint({"score":1}) or db.collection.find().hint("score_1") Index name

7. Unique index, when creating a unique index for a field After indexing, within the collection, this field cannot store the same value,

db.collection.createIndex({"userid":1} ,{"unique":true})

When mongo wants to index a field,If in a document Without this field, this document will be indexed as null. Because a unique index cannot have duplicate values, it must be used in conjunction with a sparse index, such as :

db.collection.ensureIndex({a:1},{unique:true,sparse:true })

  1. Sparse index, when a certain field of a collection is not necessary and some records are not, this is a sparse index, which will not be included in the index. Documentation for the labels field.

db.collection.createIndex({labels :1},{sparse:true })

  1. Know the fixed collection, and the collection size is specified when it is created. If there is no more space, the oldest document will be automatically deleted to free up space. (Similar to a circular queue)

db.createCollection("member",{"capped":true,"size" :100,"max":10 });

Create :Size: storage space is: 100kb Max: number of records, maximum number of records

e class=” hljs css”>db.collection. ensureIndex({a:1},{unique:true,sparse:true})

  1. Sparse index, when a certain field in a collection is not necessary and some records do not have it, a sparse index is needed. Documents without labels field will not be included in the index. .

db.collection.createIndex({labels :1},{sparse:true })

  1. Know the fixed collection, and the collection size is specified when it is created. If there is no more space, the oldest document will be automatically deleted to free up space. (Similar to a circular queue)

db.createCollection("member",{"capped":true,"size" :100,"max":10 });

Create :Size: storage space is: 100kb Max: number of records, maximum number of records

This article is from the internet and does not represent1024programmerPosition, please indicate the source when reprinting:https://www.1024programmer.com/746484

author: admin

Previous article
Next article

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact Us

Contact us

181-3619-1160

Online consultation: QQ交谈

E-mail: [email protected]

Working hours: Monday to Friday, 9:00-17:30, holidays off

Follow wechat
Scan wechat and follow us

Scan wechat and follow us

Follow Weibo
Back to top
首页
微信
电话
搜索