MongoDB in the database (index) detailed _mongodb

Index: A special data structure that stores a small portion of the data in a table to enable quick queries

Advantages:

1, greatly reducing the amount of data the server needs to scan

2, the index can help the server avoid sorting or use temporary tables

3, the index can convert random io to sequential IO

Index Evaluation: Samsung (very good)

One star: Index if the relevant records can be placed together

Two stars: The order of the data stored in the index is the same as the order of the search criteria

Samsung: If the index contains all the data needed in the query: (Overwrite index)

DBA Book: Design and optimization of relational database indexing

Index Category:

Sequential index

Hash Index: Maps the index to a hash bucket, and the mapping is done through a hash function

Criteria for Evaluating indexes:

Access type: Do equivalence comparison with hash index, with range comparison with sequential index

Access time:

Insert Length:

Delete Length:

Space overhead:

Sequential index:

Clustered index: If the order of records in a record file is sorted in the order specified by the corresponding search code, the clustered index page becomes the primary index

Nonclustered index: The specified order in the search code is inconsistent with the order of records in the record file

A data file with a clustered index, also known as an indexed sequential file

Can be divided into dense and sparse indexes, depending on whether an index entry is created for each record in the index

Multi-level index: (But for frequently modified data, poor performance)

Secondary index must be a dense index

B + Tree index: Sequential index

Balance Tree: Index of balanced trees

Attributes of Sequential indexes:

Full value match:Name='user12'

Match the leftmost prefix:Name LIKE  'User1%',无效：Name LIKE '%User1%'

Match column prefix:Name LIKE  'User1%',无效：Name LIKE '%User1%'

Match Range value

Match a column exactly and the range matches another column:

Queries that access only the index

Hash index:

hash function:

Distributed random

Evenly distributed

Applicable scenario:

Exact match: =,in (),<=>

Mysql: Full-text Indexing, fulltext

Sphinx,lucene

Spatial index: The spatial index function must be used to obtain the corresponding query result

Primary key, Unique key

Mysql: Creating Indexes

CREATE INDEX index_name on table (col1,...)

ALTER TABLE ADD index

ALTER TABLE DROP INDEX

DROP index index_name from table show

indexes from table

MONGODB CREATE INDEX:

The ID field has a primary index

To create an index in name:

View all:

The default creates a primary index on the ID

Unique index:

db.collection.ensureIndex({"user_id":1},{unique:true})

Sparse Index: Sparse index

db.collection.ensureIndex({"user_id":1},{sparse:true})

Mongodb:

An index can be created on collection or in a subdocument

MongoDB Index Type:

Single Key index

Composite Index

Multi-Key index:

Spatial index

Text Index

Hash index

Hash index:

To delete an index:

Or

Db.mycoll.dropIndexes (): Deletes all indexes of Mycoll

Query process: Explain

Scanned 100 of data

After the index is created, only one data is scanned

Specify index:

To create a composite index:

Query by combining index:

> Db.testcoll.find ({Name: ' User19 '}). Hint ({name:1,age:1}). Explain ()
{
 "cursor": "Btreecursor name_1_age _1 ",
 " Ismultikey ": false,
 " n ": 1,
 " nscannedobjects ": 1,
 " nscanned ": 1,
 " Nscannedobjectsallplans ": 1,
 " Nscannedallplans ": 1,
 " Scanandorder ": false,
 " indexonly ": false,
 " Nyields ": 0,
 " nchunkskips ": 0,
 " Millis ": 0,
 " indexbounds ": {
  " Name ": [
   [
    " User19 ", c18/> "User19"
   ]
  ],
  "age": [
   [
    {
     "$minElement": 1
    },
    {
     "$maxElement" : 1
    }
   ]
  ]
 },
 "server": "stu:27017"
}