How to use index optimization for large data paging in MySQL

Use Overlay Index

A table is built on the id,create_time to establish an index.

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

Select ID from order_manage where create_time > ' 2014-01-01 '

ORDER BY create_time DESC limit 100000,10

Select a.ID from Order_manage a

INNER JOIN (SELECT ID from order_manage

where Create_time > ' 2014-01-01 '

ORDER BY create_time desc limit 1000,10) b on a.id = b.ID

The following 2 SQL, using the inner join to a fast order of magnitude. The inner join affects the result set is still $start +30, but the process of data acquisition (sending data state) occurs in an index file, rather than a datasheet file, so the system overhead required is one order of magnitude lower than the previous normal query. The main query's impact result set is only 30, with almost no overhead. But remember, there's still too much to affect result set operations

In fact, can also be divided into 2 SQL statements to do, the first using the Overwrite index query out ID, in the use in query out the required field data.

SELECT * from Order_manage where create_time > ' 2014-01-01 '

ORDER BY create_time DESC limit 100000,10

SELECT * FROM Order_manage a

INNER JOIN (SELECT ID from order_manage

where Create_time > ' 2014-01-01 '

ORDER BY create_time desc limit 1000,10) b on a.id = b.ID

Previous page, next page optimization

Background, Common forum posts page Sql:select * from post where tagid= $tagid order by Lastpost limit $start, $end paging. Index is Tagid+lastpost composite index

 Challenge, super hot post, tens of thousands of replies, users frequently turn to the last page, limit 25770, 301 operation down, the impact of the huge result set (25770+30), slow query.

Record the largest $lastpost and the smallest of the page query results in each query as $minlastpost and $maxlastpost

Page UP query for

SELECT * from post where tagid= $tagid and lastpost< $minlastpost the ORDER by lastpost DESC limit 30;

Page DOWN to

SELECT * from post where tagid= $tagid and lastpost> $maxlastpost the order by Lastpost limit 30;

In this way, there are only 30 effects on the result set, and the efficiency is greatly improved.

Order by Sort optimization

SQL as follows:

SELECT * from user where area= ' $area ' and sex= ' $sex ' ORDER by lastlogin desc limit 0, 30;

Set up a composite index and area+sex+lastlogin the compound index of the three fields (note order), and the field of the orders by is at the end. Where Condition field, uniqueness is best at the top.

When area+sex+lastlogin a composite index (remember lastlogin at the end), the index is sorted based on the results of the merge of the Area+sex+lastlogin three fields.

In other words, a composite index is established, with one less sort operation.

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

The use of composite indexes is consistent with the left principle. Composite index of A,b,c

Abc,ab,a, you can use indexes, and you cannot use indexes in any other case.

The use principle of composite indexes is that the first condition should be the first column of the composite index must be used and cannot be boasted. AC is not available for indexing.

MSYQL Index usage Principles

Keep in mind that a data query can only use one index, and that only one index can be used if each field is indexed independently,!MSYQL selects the most optimized index. Of course you can force indexes, but that's not recommended.

When indexing and SQL optimizations are performed, the data index fields can be imagined as a single ordered sequence, and are used as the basis for analysis. In the case of composite index, composite indexes are pieced together into a field in the order of index, which is considered as the basis of the analysis.

The relationship between query condition and index determines the result set

The impact 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 the result set becomes the target result set of the actual output or operation.

The relationship between the result set and the query cost can be understood as linear correlation. Reduce the impact of half the result set, you can improve the efficiency of query! When a search query can match more than one index, select the index that affects the least result set.

SQL optimization, the core is the result set optimization, understanding the index is to enhance the judgment of the result set, based on the understanding of the SQL can be written in the SQL, the possible impact of the result set of the results of a predefined, and make appropriate optimization and adjustment.

If the index has a full hit with the query and sorting criteria, the effect set is the number following the limit ($start + $end), such as the limit 200,30 impact result set is 230. And not 30.

If the index hits only a partial query condition, or even 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 not useful to the index, but because does not involve two filtering and sorting, the system directly returns the first 10 results, the impact of the result set is still only 10, there is no efficiency impact

 If the search contains a sort condition that is not hit by an index, the system traverses the results that are hit by all indexes and sorts. For example, Select * from user order BY timeline desc limit 10; If timeline is not an index, the impact result set is a whole table, there is a need for the whole table data ordering, this efficiency impact is enormous. Again such as 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 hit area= ' Xiamen ' and then sorted in the affected result set.

Optimization based on the understanding of the influence result set, whether from data structure, code, or involve product strategy, need to carry on. The core is a small table driven large table, the use of the index to filter out the fewest result sets.

Involving limit $start, $num search, if $start large, then the impact of large results set, search efficiency will be very sad low, as far as possible in other ways rewritten as limit 0, $num; If it is not possible to rewrite the case, first from the index structure to obtain limit $start, $num or limit $start, 1, and then in operation or indexed limit 0, $num two times search.

Foreign keys and joins try not to