Mongo index learning notes

Time:2021-3-16

Index usage scenarios

Excellent: speed up the query

Inferior: the addition, deletion and modification will cause additional expenses and space occupation

Tips: returns more than half of the data in the set, and the efficiency of full table scanning is high

Index basis

Basic operation

View index: db.test.getIndexes ()

Create index: db.test.ensureIndex ({“username”:1},{“background”:true,”name”:”index_ test_ Name “}) / / when there is a large amount of data, it can be executed in the background without blocking

Delete index: db.test.dropIndex ({“username”:1})

View index size: db.test.totalIndexSize ()

attribute

Index order:

1 is positive order, – 1 is reverse order

Pay attention to the order (ID: 1, age:-1 )

Index properties:

Uniqueness

db.test.ensureIndex({x:1,y:1},{unique:true})

sparsity

db.test.ensureIndexx({},{sparse:true/false})
Not sparse (default)
1. Insert data without index field, null; 
2. Non existent fields can be filtered: db.test.find ({m:{$ exist:ture }})
Sparse:

Optimization analysis method

explain

Learn how the system handles requests

Cursor returns the cursor type (basiccursor or btreecursor)
Nscanned number of documents scanned
N number of documents returned
Millis time (MS)
The index used by indexbounds

hint

Force an index

db.test.find({"age":20}).hint({"name":1,"age":1}) // .hint(name_1_age_1)

profile

Set the log level to record slow queries

Tips

  1. Automatic adjustment of query condition order
  2. Can hit index (/ ^ Z /) for prefix regular expression
  3. Index the key that needs a lot of sort to avoid loading all data into memory
  4. $NE and $Nin do not use indexes

Index type

_ ID index

Generate unique fields by default

Single key index

The value is a single value

db.test.ensureIndex({x:1})

Multi key index

Value has multiple records, such as arrays, embedded documents

db.test.insert({x:[1,2,3,4]})

Each index field can contain at most one array

Y: {_ ID: 1, a: [1,2], B: 1, category: "a array"} and{_ id:2, a:1, b:[1,2], category:"B array"}
N: {_id:3, a:[1,2], b:[1,2], category:"AB both array"}

query

//Array query
The array contains: db.fruitshop.find ({"fruits":"apple"})
Contains multiple: db.fruitshop.find ({"fruits":{"$all":["apple", "banana"]}})
Exact match: db.fruitshop.find ({"fruits": ["apple", "orange", "pear"]}) // the order is consistent with the quantity
Location specific element query: db.fruitshop.find ({"fruits.1":"orange"})
Query array length: db.fruitshop.find ({"fruits": {"$size": 3}) // size cannot be used with other operators, such as' $GT '
Return fixed length: db.fruitshop.find ({"fruits": {"$slice": 2}) // the first two
              db.fruitshop.find ({"fruits": {"$slice": - 1}) // the last one
              db.fruitshop.find ({"fruits": {"$slice": [3,6]}) // the 4th to 7th, if there is no data, return []

//Embedded document
Perfect match: db.staff.find ({"name": {"first": "Joe", "middle": "Bush"}) // the order is consistent with the quantity
Key value pair query: db.staff.find ({" name.first ":"joe"," name.middle ":" Bush "}) // point representation. When inserting, the key name cannot contain a point (constraint)
多层内嵌: elemMatch  db.blogs.find({"comment":{"$elemMatch":{"author":"joe", "score":{"$gte":3}}}}) //Embedded document中匹配author和score条件
          where   db.fruitshop.find ({"$where": function() {}) // low performance, each document is converted into a JavaScript object and put into function execution

Composite index

Multiple conditions, from left to right

{a:1,b:1,c:1} => {a:1},{a:1,b:1},{a:1,b:1,c:1}

db.test.ensureIndex({x:1,y:1})

Expired index

After a period of time, delete the corresponding data (user login information, stored log)

db.test.ensureIndex({time:1},{expireAfterSeconds:30})

limit

Field type must be Isodate or Isodate array (the smallest time in the array)
Cannot be a composite index (cannot specify two expiration times)
Inaccurate deletion time (the background process runs once every 60s)

Full text index

String or string array can be searched

//Indexing
db.test.ensureIndex({title:"text"})
db.test.ensureIndex ({key1: "text", key2: "text"}) // create a full-text index for multiple fields
db.test.ensureIndex ({$* *: "text"}) // full text index for all fields

//Find
You do not need to specify a field name: db.test.find ({"$text": {"$search": "coffee"}) // only one full-text index can be created for each data set (for one, multiple and all fields)
Find multiple keywords (spaces represent or operate): db.test.find ({"$text":{"$search":"aa bb cc"}})
Specifies that the word does not contain (- for non operation): db.test.find ({"$text":{"$search":"aa bb -cc"}})
And relation operation: db.test.find ({"$text":{"$search":"\"aa\" \"bb\" \"cc\""}})
Similarity query: db.test.find The higher the score of ({"$text": {"$search": "AA BB"}, {"score: {" $meta ":" textscore "}) // score field, the higher the correlation
            db.test.find ({"$text": {"$search": "AA BB"}, {"score": {"$meta": "textscore"}). Sort ({"score": {"$meta": "textscore"}) // score correlation ranking
            
//Restrictions
Only one $text can be specified per query
With $text, hint doesn't work
Poor Chinese support (enterprise version can support)

Geolocation index

Store the location of the point, you can find other points by location

2D index
Used to store and find points on a plane

db.test.ensureIndex({w:"2d"})

//Use latitude and longitude
Value range: longitude [- 180180] latitude [- 90,90]
db.test.insert({w:[180,90]})

//Inquiry
Use $near to query the closest point to a point (100 by default)
    db.test.find({"$near":[x,y]})
    db.test.find ({W: {"$near": [x, y], "$maxdistance": "Z"}}) // limit the maximum return distance
 
Using $geowithin to query points within a shape
    Rectangle ($box: [[x1, Y1], [X2, Y2]]) db.test.find ({w:{"$geoWithin:{"$box":[[0,0],[3,3]]}}"}})
    Circle ($Center: [[x, y], R]) db.test.find ({w:{"$geoWithin":{"$center":[0,0],5}}})
    Polygon ($polygon: [[x1, Y1], [X2, Y2],..) db.test.find ({w:{"$geoWithin":{"$polygon":[[0,0],[0,1],[2,5],[6,1]]}}})
    
Use $geonear query to return data such as maximum distance and average distance

Related extensions:
Implementation principle of geographic location index

2dsphere index
Used to store and find points on the sphere

db.test.ensureIndex({key:"2dsphere"})