Shulou(Shulou.com)06/01 Report--

Index: a special data structure that stores a small portion of a table's data for fast query.

Advantages:

1. The amount of data that the server needs to scan is greatly reduced.

2. Indexes can help servers avoid sorting or using temporary tables

3. Index can convert random io to sequential io.

Index evaluation: Samsung (very good)

One star: if the index can put the relevant records together

Two stars: the storage order of the data in the index is the same as that in the search standard

Samsung: if the index contains all the data needed in the query: (overwrite index)

DBA Book: index Design and Optimization of Relational Database

Index category:

Sequential index

Hash index: maps the index to the hash bucket, which is done through the hash function

Criteria for evaluating the index:

Access type: hash index for equivalence comparison and sequential index for range comparison

Access time:

Insert duration:

Delete duration:

Space overhead:

Sequential index:

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

Nonclustered index: the order specified in the search code is not consistent with the order of records in the record file

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

According to whether an index entry is created for each record in the index, it can be divided into dense index and sparse index.

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

Secondary index must be dense index

B+ tree index: sequential index

Balance Tree: balanced tree index

Properties of sequential indexes:

Full value match: Name='user12'

Match leftmost prefix: invalid Name LIKE 'User1%',: Name LIKE'% User1%'

Match column prefix: invalid Name LIKE 'User1%',: Name LIKE'% User1%'

Match range valu

Exactly match one column and the range matches another column:

Query that accesses only the index

Hash index:

Hash function:

Random distribution

Uniform distribution

Applicable scenarios:

Exact match: =, IN ()

Mysql: full-text index, fulltext

Sphinx,lucene

Spatial index: you must use the spatial index function to get the corresponding query results.

Primary key, unique key

Mysql: creating an index

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 creates an index:

The id field has a primary index

Create an index in Name:

View all:

The primary index is created by default on id

Unique index:

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

Sparse index: sparse index

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

Mongodb:

The index can be created on collection or in a subdocument

Mongodb index type:

Single key index

Combinatorial index

Multi-key index:

Spatial index

Text indexing

Hash index

Hash Index:

Delete the index:

Or

Db.mycoll.dropIndexes (): delete all indexes of mycoll

Query process: explain

Scanned 100 data.

After creating the index, only one data is scanned

Specify the index:

Create a composite index:

Query through the combined 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", "User19"]] "Age": [[{"$minElement": 1}, {"$maxElement": 1}]}, "server": "stu:27017"}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.