mysqlorderby_MySQL中order by的执行过程

目录

前言：

在开发过程中，经常会遇到按照指定字段排序显示结果的需求。仍然以之前的订单表为例，假设查询“张三”的所有订单，按照订单价格的顺序返回前1000个订单号和价格。

1、测试数据

测试的订单表结构如下：

CREATE TABLE `my_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `oid` varchar(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `price` decimal(6,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `uid` (`uid`) USING BTREE,
  KEY `oid` (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000 DEFAULT CHARSET=utf8;

用户表数据：

上面订单表的uid与用户表的id相关联。

SQL语句可以这样写：

SELECT
	oid,
	price 
FROM
	my_order 
WHERE
	uid = 1 
ORDER BY
	price 
	LIMIT 1000;

上面的SQL语句看起来逻辑很清晰，但是你了解它的执行流程吗？在本文中，我们将了解该语句是如何执行的以及哪些参数影响执行。

2.全字段排序

为了避免全表扫描，我们需要给uid字段添加索引。将索引添加到uid字段后，我们使用命令查看这条语句的执行情况。

Extra字段中的“Using”表示需要排序。 Mysql会为每个线程分配一块内存用于排序，称为。为了说明这条SQL查询语句的执行过程，我们首先看一下uid索引的示意图。

如下图：

通常情况下，该语句的执行流程如下：

“按oid排序”的动作可能在内存中完成，也可能需要使用外部排序，具体取决于排序所需的内存和参数。

，即mysql为排序而开辟的内存()的大小。如果要排序的数据量小于 ，则在内存中进行排序。但如果排序后的数据量太大，内存无法保存，就必须使用临时磁盘文件来辅助排序。

您可以使用下面描述的方法来确定排序语句是否使用临时文件。

SET optimizer_trace = 'enabled=on';
 
SELECT VARIABLE_VALUE INTO @a FROM PERFORMANCE_SCHEMA.session_status WHERE variable_name='Innodb_rows_read';
 
SELECT oid,price FROM my_order WHERE uid=1 ORDER BY price LIMIT 1000;
 
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;
 
SELECT VARIABLE_VALUE INTO @b FROM PERFORMANCE_SCHEMA.session_status WHERE variable_name='Innodb_rows_read';
 
SELECT @b-@a;

此方法通过查看结果来确认，您可以在其中查看是否使用了临时文件。

指示排序过程中使用的临时文件的数量。你一定会感到惊讶，我当前的测试需要0个文件，这意味着排序可以直接在内存中完成。如果是n，则说明当内存无法存储时，需要使用外部排序。外部排序一般采用归并排序算法。可以简单的理解为MySQL将需要排序的数据分为n部分，每部分分别排序后存放在这些临时文件中。然后将这n个有序文件合并成一个大的有序文件。

注：如果超过需要排序的数据大小，则为0，表示可以直接在内存中完成排序。

接下来我给大家解释一下上图中另外两个值的含义。

我们的示例表中有99972条记录满足uid=1,=99972，也就是说参与排序的行数为99972行。

里面是。

同时最后一条查询语句@b-@a的返回结果为99973。

那为什么不是上面的99972呢？

这里需要注意的是，为了避免干扰，可以设置为 。否则@b-@a的结果会显示为99973。这是因为查询这个表时，需要临时表，默认值为。如果使用引擎，当从临时表中取出数据时， 的值会加1。

3.rowid排序

上述算法只读取原表的数据一次，其余操作在临时文件中进行。但是这个算法有一个问题，就是如果查询要返回的字段很多，那么放入的字段就会太多，所以同时可以放入内存的行数时间很小，会被分成很多临时文件，排序性能会降低。很穷。所以如果单行很大的话，这个方法就不够高效了。

如果MySQL认为正在排序的单行长度太大，它会怎么做？

让我们修改一个参数，让MySQL使用另一种算法。

SET max_length_for_sort_data = 16;

data是MySQL中的一个参数，专门控制用于排序的行数据的长度。意思是如果单行的长度超过这个值，MySQL就会认为单行太大，需要改变一种算法。 oid和price这两个字段的总定义长度为28，我将data设置为16，我们看一下计算过程中的变化。新算法放入的字段只是要排序的列（即价格字段）和主键id。但此时排序结果无法直接返回，因为缺少price字段的值。

整个执行流程变成如下：

与全字段排序流程图相比，rowid排序多访问了表test的主键索引一次，即第7步。

注意：最终的“结果集”只是一个逻辑概念。其实MySQL服务器就是从排序好的id中按顺序取出id，然后去原表中查找oid和price这两个字段的结果。没有必要在服务器上花更多的钱。内存存储结果直接返回给客户端。

那么基于此时执行@b-@a，会得到什么结果呢？

首先，图中的值仍然是99972，也就是说用于排序的数据是99972行。但语句@b-@a 的值变为。 （比上面@b-@a 99973多了1000行，因为除了排序过程之外，排序完成后还要根据id从原表中获取值，由于语句是limit 1000，将读取另外 1000 行）。

从结果中，还可以看到另外一条信息发生了变化。

4、全字段排序与rowid排序对比

如果MySQL确实担心排序内存太小，影响排序效率，就会使用rowid排序算法。这样在排序的过程中可以一次性排序更多的行，但是需要回到原表去获取数据。

如果MySQL认为内存足够大，会优先进行全字段排序，将所有需要的字段放入.这样查询结果排序后会直接从内存中返回，而不需要再回到原表中获取数据。这也体现了MySQL的一个设计思想：如果内存足够，就使用更多的内存，尽量减少磁盘访问。对于表来说，rowid排序将需要更多的磁盘读取才能返回表，因此不会是首选。

MySQL 中的排序是一个相对昂贵的操作。所有order by都需要排序操作吗？如果不用排序就能得到正确的结果，系统的消耗会小很多，语句的执行时间也会更短。事实上，并不是所有的order by语句都需要排序操作。从上面分析的执行过程可以看出，MySQL之所以需要生成临时表并对临时表进行排序操作，是因为原始数据是乱序的。如果能保证从uid索引中取出来的行自然是按价格升序排序的，那就不用再排序了？因此，我们可以在这个表上创建uid和price的联合索引。对应的SQL语句为：

ALTER TABLE my_order ADD INDEX un_key (uid,price);

作为与 uid 索引的比较，我们看一下该索引的图表。

在这个索引中，我们仍然可以通过树形搜索来定位第一条满足uid=1的记录，并且另外保证在后续按顺序取出“下一条记录”的遍历过程中，只要uid的值为1即可。价格的值必须是有序的。

这样，整个查询过程就变成了：

这个查询过程不需要临时表或排序。接下来我们用结果来确认一下。

从图中可以看到，Extra字段中没有Using，也就是说不需要排序。并且由于(uid,price)的联合索引本身是有序的，所以这个查询不需要读取所有99972行。只要找到前1000条符合条件的记录就可以退出。也就是说，在我们的示例中，只需要 1000 次扫描。另请参阅@b-@a；

一点评论。覆盖索引是指索引上的信息足以满足查询请求，不需要回到主键索引来检索数据。 Extra字段中有“Using index”，表示使用了覆盖索引，性能会快很多。

当然，这并不是说为了每次查询都使用覆盖索引，语句涉及到的所有字段都必须建成联合索引。毕竟索引还是有维护成本的。这是一个需要权衡的决定。

这篇关于MySQL中order by的执行流程的文章就到此结束了。更多相关MySQL order by内容请搜索编程网之前的文章或者继续浏览下面的相关文章。希望大家以后多多支持编程网！