mysql回表两次的场景_Mysql - ORDER BY详解

0 索引

1 概述

2 索引扫描排序和文件排序简介

3 索引扫描排序执行过程分析

4 文件排序

5 补充说明

6 参考资料

1 概述

MySQL有两种方式可以实现ORDER BY：

1.通过索引扫描生成有序的结果

2.使用文件排序(filesort)

围绕着这两种排序方式，我们试着理解一下ORDER BY的执行过程以及回答一些常见的问题。(下文仅讨论InnoDB存储引擎)

2 索引扫描排序和文件排序(filesort)简介

我们知道InnoDB存储引擎以B+树作为索引的底层实现，B+树的叶子节点存储着所有数据页而内部节点不存放数据信息，并且所有叶子节点形成一个(双向)链表。

举个例子，假设userinfo表的userid字段上有主键索引，且userid目前的范围在1001~1006之间，则userid的索引B+树如下：(这里只是为了举例，下图忽略了InnoDB数据页默认大小16KB、双向链表，并且假设B+树度数为3、userid顺序插入)

现在我们想按照userid从小到大的顺序取出所有用户信息，执行以下SQL

SELECT *

FROM userinfo

ORDER BY userid;

MySQL会直接遍历上图userid索引的叶子节点链表，不需要进行额外的排序操作。这就是用索引扫描来排序。

但如果userid字段上没有任何索引，图1的B+树结构不存在，MySQL就只能先扫表筛选出符合条件的数据，再将筛选结果根据userid排序。这个排序过程就是filesort。

下文将详细介绍这两种排序方式。

3 索引扫描排序执行过程分析

介绍索引扫描排序之前，先看看索引的用途

SQL语句中，WHERE子句和ORDER BY子句都可以使用索引：WHERE子句使用索引避免全表扫描，ORDER BY子句使用索引避免filesort(用“避免”可能有些欠妥，某些场景下全表扫描、filesort未必比走索引慢)，以提高查询效率。

虽然索引能提高查询效率，但在一条SQL里，对于一张表的查询 一次只能使用一个索引(注：排除发生index merge的可能性)，也就是说当WHERE子句与ORDER BY子句要使用的索引不一致时，MySQL只能使用其中一个索引(B+树)。

也就是说，一个既有WHERE又有ORDER BY的SQL中，使用索引有三个可能的场景:

只用于WHERE子句 筛选出满足条件的数据

只用于ORDER BY子句 返回排序后的结果

既用于WHERE又用于ORDER BY，筛选出满足条件的数据并返回排序后的结果

举个例子，我们创建一张order_detail表 记录每一笔充值记录的userid(用户id)、money(充值金额)、create_time(充值时间)，主键是自增id：

CREATE TABLE `order_detail` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) NOT NULL,

`money` float NOT NULL,

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

KEY `userid` (`userid`),

KEY `create_time` (`create_time`)

) ENGINE