DB index, index overlay, index optimization

########### index ###########

@see Http://mp.weixin.qq.com/s/4W4iVOZHdMglk0F_Ikao7A

Clustered Indexes (clustered index): A clustered index determines the physical ordering of data on disk, and a table can have only one clustered index, typically constrained by primary key.

Example : T_user the index on the UID in the scene.

Nonclustered Indexes (non-clustered index): It does not determine the physical ordering of data on disk, the index contains only indexed data, and a row locator Row-locator, the line locator, A pointer that can be understood as a physical sort of a clustered index, through which the row data can be found.

For example , find the business needs of young mm:

Select UID from T_user, where Age >, and Age < 26;

The index created on age is the nonclustered index.

Federated Index : an index established on multiple fields that accelerates the retrieval of a review query condition

For example , log in to your business needs:

Select UID, login_time from T_user where

Login_name=? and passwd=?

A federated index (login_name, passwd) can be established.

Federated indexes can meet the leftmost query requirements, such as a combined index (a, B, c) that accelerates a | (A, B) | (A, B, c) three sets of query requirements.

This is why it is not possible to establish a co-index (passwd, login_name) so that there is almost no passwd single-condition query requirement in business, and there are many login_name single-condition query requirements.

question :

Select UID, login_time from T_user where

passwd=? and login_name=?

Can I hit (login_name, passwd) This federated index?

Answer: Yes , the leftmost query requirement does not mean that the SQL statement must be written in the Order of the index (this is a misunderstanding of many friends)

Index Overlay : The queried column, the data can be obtained from the index, rather than through the row locator row-locator to the row to get, that "query column to be built by the index overlay", which can speed up the query.

########## #索引优化 ############

@see http://mp.weixin.qq.com/s/ZWez27EmVw_u7GzNbvXuYw

For example, log in to your business needs:

Select UID, login_time from T_user where

Login_name=? and passwd=?

A Federated index (login_name, passwd, login_time) can be established, because Login_time is already established in the index, the UID and login_time queried will not have to go to the row to obtain data, thereby speeding up the query.

Finally, to say, login to this business scenario, login_name Unique, build this single-column index is good.

Suppose the order Business table structure is:
Order (OID, date, UID, status, money, time, ...)
which
OID, order ID, primary key
Date, day of the order, with normal index, management background often according to date query
UID, user ID, normal index, user inquiry order
Status, order state, with normal index, management background often according to status query
Money/time, order Amount/time, query field, no index
...

Suppose there are three states of order: 0 placed, 1 paid, 2 completed
Business requirements, querying for unfinished orders, which SQL is faster?
SELECT * FROM order where status!=2
SELECT * from order where status=0 or Status=1
SELECT * from order where status in (0,1)
SELECT * FROM order where status=0
UNION ALL
SELECT * FROM order where Status=1

Conclusion: Scenario 1 is the slowest, the scheme 2,3,4 can hit the index

But...

One: union all must be able to hit the index
SELECT * FROM order where status=0
UNION ALL
SELECT * FROM order where Status=1
Description
Just tell MySQL what to do, MySQL consumes the least CPU
Programmers don't often write SQL (union ALL)

Two: Simple in to hit index
SELECT * from order where status in (0,1)
Description
Let MySQL think, query optimization consumes more CPU than union all, but can be ignored
Programmers most often write SQL (in), this example, the most recommended to write

Third: For or, the new version of MySQL can hit the index
SELECT * from order where status=0 or Status=1
Description
Let MySQL think, query optimization consumes more CPU than in, don't give the burden to MySQL
It is not recommended that programmers frequently use or, not all or both hits the index
For older versions of MySQL, it is recommended to query the analysis under

Four, for! =, negative queries must not hit the index
SELECT * FROM order where status!=2
Description
Full table scan, lowest efficiency, slowest in all scenarios
Negative queries are not allowed. when using a negative query, be sure to bring along other forward query criteria that can filter large amounts of data, so it is possible to use negative queries with them.

Example:

SELECT oid from T_order WHERE uid=123 and status! = 1;

Order table 5000w data, but Uid=123 will quickly filter the amount of data to a very small number of levels (UID indexed), then the next negative query condition does not matter, scan of the line itself is very small.

However, if you want to query all orders other than the completed order:

SELECT oid from T_order WHERE status! = 1;

That's it, right now. Cpu100%,status indexes are invalidated and negative queries cause full table scans.

V. OTHER PROGRAMMES
SELECT * from order where status < 2
In this specific case, it's really fast, but:
This example only up to 3 states, the actual business more than the 3 states, and the status of the "value" just satisfies the partial order relationship, in case of other states, SQL should not rely on the value of the enumeration, the scheme is not universal
This SQL is poor readability, poor understanding, poor maintainability, strongly not recommended

DB index, index overlay, index optimization