Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use ORDER BY in MySQL. It is very detailed and has a certain reference value. Friends who are interested must finish it!

Generally speaking, there are two ways to sort ORDER BY in MySQL, one is to use ordered index to obtain ordered data, and the other is to sort the data in memory through the corresponding sorting algorithm.

The following will analyze the two sorting implementation methods and implementation diagrams through examples:

Suppose you have two tables, Table An and B, with the following structure:

Sky@localhost: example 01:48:21 > show create table A\ G

* * 1. Row *

Table: A

Create Table: CREATE TABLE `A` (

`c1`int (11) NOT NULL default'0'

`c2` char (2) default NULL

`c3` varchar (16) default NULL

`c4` datetime default NULL

PRIMARY KEY (`c1`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

Sky@localhost: example 01:48:32 > show create table B\ G

* * 1. Row *

Table: B

Create Table: CREATE TABLE `B` (

`c1`int (11) NOT NULL default'0'

`c2` char (2) default NULL

`c3` varchar (16) default NULL

PRIMARY KEY (`c1`)

KEY `Blocc2ind` (`c2`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1, the use of ordered index for sorting, in fact, when the BY conditions of our Query and the Index index keys (or the previous index keys) used in the Query execution plan are exactly the same, and the index access mode is rang, ref or index, MySQL can directly obtain the sorted data by using the index order. BY in this way is basically the best way to sort, because MySQL does not need to do the actual sorting operation.

Suppose we perform the following SQL on Table An and B:

Sky@localhost: example 01:44:28 > EXPLAIN SELECT A.* FROM AMagi B

-> WHERE A.c1 > 2 AND A.c2

< 5 AND A.c2 = B.c2 ORDER BY A.c1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: A type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ref possible_keys: B_c2_ind key: B_c2_ind key_len: 7 ref: example.A.c2 rows: 2 Extra: Using where; Using index 我们通过执行计划可以看出，MySQL实际上并没有进行实际的排序操作，实际上其整个执行过程如下图所示： 通过相应的排序算法，将取得的数据在内存中进行排序方式，MySQL 比需要将数据在内存中进行排序，所使用的内存区域也就是我们通过 sort_buffer_size 系统变量所设置的排序区。这个排序区是每个 Thread 独享的，所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。 第二种方式在 MySQL Query Optimizer 所给出的执行计划（通过 EXPLAIN 命令查看）中被称为 filesort。在这种方式中，主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给 客户端。在 MySQL 中 filesort 的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在 sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序。 在 MySQL4.1 版本之前只有第一种排序算法，第二种算法是从 MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的 sort buffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，MySQL 主要通过比较我们所设定的系统参数 max_length_for_sort_data 的大小和 Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data 更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义 max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为 MySQL 使用了传统的第一种排序算法而导致，在加大了 max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。 我们再看看 MySQL 需要使用 filesort 实现排序的实例。 假设我们改变一下我们的 Query，换成通过A.c2来排序，再看看情况： sky@localhost : example 01:54:23>

EXPLAIN SELECT A.* FROM A Magi B

-> WHERE A.c1 > 2 AND A.c2

< 5 AND A.c2 = B.c2 ORDER BY A.c2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: A type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ref possible_keys: B_c2_ind key: B_c2_ind key_len: 7 ref: example.A.c2 rows: 2 Extra: Using where; Using index MySQL 从 Table A 中取出了符合条件的数据，由于取得的数据并不满足 ORDER BY 条件，所以 MySQL 进行了 filesort 操作，其整个执行过程如下图所示： 在 MySQL 中，filesort 操作还有一个比较奇怪的限制，那就是其数据源必须是来源于一个 Table，所以，如果我们的排序数据如果是两个（或者更多个） Table 通过 Join所得出的，那么 MySQL 必须通过先创建一个临时表（Temporary Table），然后再将此临时表的数据进行排序，如下例所示： sky@localhost : example 02:46:15>

Explain select A.* from A Magi B

-> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: A

Type: range

Possible_keys: PRIMARY

Key: PRIMARY

Key_len: 4

Ref: NULL

Rows: 3

Extra: Using where; Using temporary; Using filesort

* 2. Row * *

Id: 1

Select_type: SIMPLE

Table: B

Type: ref

Possible_keys: B_c2_ind

Key: B_c2_ind

Key_len: 7

Ref: example.A.c2

Rows: 2

Extra: Using where

The output of this execution plan is a little strange. For some reason, does MySQL Query Optimizer display the "Using temporary" process in the first line of the operation on Table A just to reduce the output of the execution plan by one line?

The above is all the contents of the article "how to use ORDER BY in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.