How limit works in MySQL5.6 and how orderbylimit is optimized

How limit works in MySQL5.6 and how orderbylimit is optimized

How Limit works in MySQL5.6

If you only need to return specific rows in a result set, limit is usually used instead of retrieving the entire result set and then removing unnecessary data, mySQL generally optimizes a statement containing limit row_count or HAVING in the following way:

◎ Only limit

If you only return a small number of rows through limit, mysql will normally use full scan, and indexes will be used in some cases. The following is the case where overwriting indexes are used:

Full table scan is used as follows:

◎ Order by and limit

If order by and limit are used together, mysql will complete this statement after finding the original row_count row in the sorting result, rather than sorting the entire result set. If index sorting is used, it can be completed very quickly. If the entire filesort must be completed, all rows matching the query will be selected and sort will be performed before the original row_count row is found. If these rows are found, mysql does not sort the remaining result sets.

◎ Distinct and limit

When limit row_count and distinct are used together, MySQL will stop searching after finding the row row_count of the original unique.

◎ Group by and limit

In some cases, group by is used for sorting certain key rows and calculating the summary information. If limit row_count is used, no additional grup by value is calculated.

◎ SQL _CALC_FOUND_ROWS and limit

As long as MySQL has returned the required number of rows to the client, it will terminate this query, unless you use SQL _CALC_FOUND_ROWS in the query.

◎ Limit 0 usage

Limit 0 returns an empty result very quickly. This function can be used to check the validity of an SQL statement.

◎ Temporary table and limit

If the server uses a temporary table in the query, it uses the limit row_count statement to calculate the required space.

Problems caused by mixed use of Order by and Limit

If there are many rows in the returned result set in the order by statement, the returned results of non-sorted columns are uncertain, that is, random, therefore, if limit is used together, the order of the result set returned each time is not fixed. For example, in the following example

Mysql>SELECT * FROM ratings order by category;

+ ---- + ---------- + -------- +

| Id | category | rating |

+ ---- + ---------- + -------- +

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

| 2 | 3 | 5.0 |

| 7 | 3 | 2.7 |

+ ---- + ---------- + -------- +

After using limit, we can find that the order of the id column and the rating column is different from that of the previous result set:

Mysql>SELECT * FROM ratings order by category LIMIT 5;

+ ---- + ---------- + -------- +

| Id | category | rating |

+ ---- + ---------- + -------- +

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 4 | 2 | 3.5 |

| 3 | 2 | 3.7 |

| 6 | 2 | 3.5 |

+ ---- + ---------- + -------- +

If you need to ensure that the same result set exists each time, you need to order by the columns you need:

Mysql>SELECT * FROM ratings order by category, id;

+ ---- + ---------- + -------- +

| Id | category | rating |

+ ---- + ---------- + -------- +

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

| 2 | 3 | 5.0 |

| 7 | 3 | 2.7 |

+ ---- + ---------- + -------- +

Mysql>SELECT * FROM ratings order by category, id LIMIT 5;

+ ---- + ---------- + -------- +

| Id | category | rating |

+ ---- + ---------- + -------- +

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

+ ---- + ---------- + -------- +

Optimization Principles used by Order by and limit

After MySQL 5.6.2, the optimizer intelligently processes the following queries.

SELECT... FROMSingle_table... ORDERNon_index_column[DESC] LIMIT [M,]N;

This type of query that returns only a small number of rows in a large result set is very common in web applications, such

SELECT col1,... FROM t1... order by name LIMIT 10;

SELECT col1,... FROM t1... order by rand () LIMIT 15;

The sorting cache has a parameter sort_buffer_size. If the size of this parameter is sufficient for the sorting result set of N rows in the preceding example (if M is also defined, that is, the result set of M + N rows is large and small), the server will avoid a File Sorting operation, so that the sorting is completed completely in the memory.

Memory sorting + limit Principle

1. Scan the table and insert the data of the columns selected for sorting in the memory to an ordered queue, such as order by col1 and col2. Then insert the data of col1 and col2 columns. If the queue is full, the data sorted at the end is squeezed out.

2. Return the records of the first N rows in the queue. If M is also defined, the records of the subsequent N rows will be returned starting from Row M.

File Sorting + limit Principle

1. Scan the table and repeat steps 2 and 3 until the end of the table.

2 select these rows until the sorting cache is filled

3. Write the first N rows in the sorting cache (if M is defined, M + N rows) to a sorting file.

Comparison between the two

In-memory sorting, the cost of table scanning is almost the same as that of File Sorting. The difference is that other overhead:

The memory sorting method involves more cpu resources when inserting data into an ordered queue, and File Sorting consumes more disk IO, the optimizer mainly considers the value of N in consideration of the balance between the two.