求mysql倒序并且限制查询的条数的查询语句？

2019独角兽企业重金招聘Python工程师标准>>>

最近需要在MySQL中按照指定顺序查询数据库表时候，遇到了一些小问题，记录一下：

以上是百度知道的回答，但是我并没有满足于这个答案本身，我还想再一探究竟，结果就去Google上走了一趟，一下就是FQ所得：

1）基本语法

We have seen SQL SELECT command to fetch data from MySQL table. When you select rows, the MySQL server is free to return them in any order, unless you instruct it otherwise by saying how to sort the result. But you sort a result set by adding an ORDER BY clause that names the column or columns you want to sort by.

Here is generic SQL syntax of SELECT command along with ORDER BY clause to sort data from MySQL table:

You can sort returned result on any field provided that filed is being listed out.

You can sort result on more than one field.

You can use keyword ASC or DESC to get result in ascending or descending order. By default, it's ascending order.

You can use WHERE...LIKE clause in usual way to put condition.

This will use SQL SELECT command with ORDER BY clause to fetch data from MySQL table tutorials_tbl.

Try out the following example, which returns result in ascending order.

Verify all the author names are listed out in ascending order.

2）性能优化

If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data.

MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause:

If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

If you combine LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

If you combine LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

In some cases, a GROUP BY can be resolved by reading the index in order (or doing a sort on the index) and then calculating summaries until the index value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS. The number of rows can then be retrieved with SELECT FOUND_ROWS(). See Section 13.14, “Information Functions”.

LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns if you are using a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types.

If the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

Including LIMIT may affect order of rows within each category value. For example, this is a valid query result:

In each case, the rows are sorted by the ORDER BY column, which is all that is required by the SQL standard.

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

The optimizer does handle queries (and subqueries) of the following form:

That type of query is common in web applications that display only a few rows from a larger result set. For example:

The sort buffer has a size of sort_buffer_size. If the sort elements for N rows are small enough to fit in the sort buffer (M+N rows if M was specified), the server can avoid using a merge file and perform the sort entirely in memory by treating the sort buffer as a priority queue:

Scan the table, inserting the select list columns from each selected row in sorted order in the queue. If the queue is full, bump out the last row in the sort order.

Return the first N rows from the queue. (If M was specified, skip the first M rows and return the next N rows.)

Previously, the server performed this operation by using a merge file for the sort:

Scan the table, repeating these steps through the end of the table:

Select rows until the sort buffer is filled.

Write the first N rows in the buffer (M+N rows if M was specified) to a merge file.

Sort the merge file and return the first N rows. (If M was specified, skip the first M rows and return the next N rows.)

The cost of the table scan is the same for the queue and merge-file methods, so the optimizer chooses between methods based on other costs:

The queue method involves more CPU for inserting rows into the queue in order

The merge-file method has I/O costs to write and read the file and CPU cost to sort it

The optimizer considers the balance between these factors for particular values of N and the row size.