MySQL index optimization

MySQL Big data paging and indexing using the overwrite index

1. A table establishes an index on the id,create_time.

2. The following 2 SQL statements are executed in the same time. Because the query field ID is overwritten by the index.

SelectId fromOrder_managewhereCreate_time >'2014-01-01'ORDER BY create_time desc limit100000,Ten 　　 Selecta.ID fromOrder_manage a INNER join (SelectId fromOrder_managewhereCreate_time >'2014-01-01'ORDER BY create_time desc limit +,Ten) b on a.id = b.ID

3. The following 2 SQL, using the inner join is a fast order of magnitude. Inner JOIN affects the result set is still $start +30, but the process of data acquisition (sending data state) takes place in the index file, not the datasheet file, which requires an order of magnitude lower than the previous normal query. The main query has only 30 impact results set, with almost no overhead. But remember, there are still too many impact result set operations

In fact, it can be divided into 2 SQL statements, the first one uses the overwrite index to query out the ID, in using in query out the required field data.

Select* fromOrder_managewhereCreate_time >'2014-01-01'ORDER BY create_time desc limit100000,Ten
Select* fromOrder_manage a INNER join (SelectId fromOrder_managewhereCreate_time >'2014-01-01'ORDER BY create_time desc limit +,Ten) b on a.id = b.ID

Previous page, next page optimization

1. Background, Common forum posts page Sql:select * from post where tagid= $tagid the order by Lastpost limit $start, $end page. Index to Tagid+lastpost Composite index
   ? Challenges, super hot posts, tens of thousands of replies, users frequently turn to the last page, limit 25770, 301 operations down, affecting the result set huge (25770+30), query slow.
2. The maximum $lastpost and the smallest of the query results in the page are recorded as $minlastpost and $maxlastpost each time the query is queried

Page up query for Select  from where  - 　　;　　The next page is the selectfromwhere; In this way, only 30 of the result set is affected, and the efficiency is greatly improved. 

Order by Sort optimization

1. The following sql:

Select  from where 0,;

To build a composite index and area+sex+lastlogin The composite index of three fields (note order), the Order by field is the last. Where condition fields, uniqueness is best to be at the top.
Area+sex+lastlogin a composite index (remember lastlogin at the end), the index is sorted based on the results of Area+sex+lastlogin three field merges.
In other words, a composite index is established, and a sort operation is reduced.

2. Keep in mind that data queries can only use one index, and each field can be indexed independently, and only one index will be used!

3. The use of composite indexes is consistent with the left principle. Compound index of A,b,c
Abc,ab,a, indexes can be used, and indexes cannot be used in other cases.
The use principle of a composite index is that the first condition should be the first column of the composite index that must be used and cannot be complimented. An index is not available for AC.

MSYQL Index usage principles
1. Keep in mind that data queries can only use one index, and each field can be indexed independently, and only one index will be used! MSYQL will select the most optimized index. Of course you can force the use of indexes, but this is not recommended.
2. In the case of index Analysis and SQL optimization, the data index field can be imagined as a single ordered sequence, as a basis for analysis. When it comes to composite indexes, composite indexes are pieced together into a single field in index order, and as a basis for analysis, they are imagined as a singular ordered sequence.
3. The relationship between the query criteria and the index determines the impact result set
The result set is not the number of output results, not the number of records returned by the query, but the number of results scanned by the index.
The higher the index efficiency, the more likely the result set is to be closer to the target result set of the actual output or operation
The relationship between the result set and the query cost can be interpreted as linear correlation. Reduce the impact of half of the result set, you can increase query efficiency! When a search query can conform to multiple indexes, select the index that affects the least set of result sets.
SQL optimization, the core is the optimization of the result set, the understanding index is to enhance the judgment of the result set, based on the understanding of the index, can be written in SQL, the SQL may affect the result set of the pre-judgment, and make appropriate optimization and adjustment.
If the index is fully hit with the query criteria and the sort criteria, the result set is the number following the limit ($start + $end), such as limit 200,30, which affects the result set of 230. Rather than 30.
If the index hits only a subset of the query criteria, even without a hit condition, in the absence of a sort condition, the result set of the index hit is traversed until all other conditions are met. For example, select * from user limit 10; Although the index is not used, but because it does not involve two filtering and sorting, the system directly returns the first 10 results, affecting the result set still only 10, there is no efficiency impact
? If the search contains a sort condition that is not hit by an index, the system iterates through the results that are hit by all indexes and sorts. For example Select * from the user order by timeline desc limit 10; If timeline is not an index, affecting the result set is a full table, there is a need to sort the whole table data, this efficiency impact is huge. Another example is Select * from user where area= ' xiamen ' ORDER by timeline desc limit 10; If area is an index, and area+timeline is not indexed, the result set is affected by all users who hit area= ' Xiamen ' and then sorted within the affected result set.

4. Understanding optimization based on impact result sets needs to be carried out regardless of data structure, code, or product strategy. The core is the small table driver large table, the use of the index to filter out the minimum result set.
5. Involving the limit $start, $num search, if the $start is huge, it will affect the result set is huge, the search efficiency is very sad low, try to rewrite it in other ways as limit 0, $num, if the system cannot be rewritten, first get the limit $start from the index structure, $ num or limit $start, 1, and then the in operation or the index-based limit 0, $num two searches.
6. Foreign keys and joins try not to use

MySQL index optimization