MySql性能（7）—MySql对order by排序优化

单索引的情况下
1.1 索引覆盖
联合索引的情况下
2.1 场景描述
2.2 聚簇索引
2.3 面试实战
连表查询情况下
3.1 准备
（3.1.1）数据准备
（3.1.2）什么是驱动表
（3.1.3）三种排序方式对比
3.2 利用驱动表索引优化order by
3.3 仅对驱动表进行排序
（3.3.1）场景
（3.3.2）对于使用驱动表排序的优化
3.4 使用临时表进行排序
（3.4.1）场景
（3.4.2）对于使用临时表排序的优化
实战分析
4.1 order by id替换create_time

对于order by优化，MySQL若可以利用索引有序性进行排序，则优先使用索引进行排序。

1. 单索引的情况下

user表数据量是百万级，name列存在普通索引。

1.1 索引覆盖

使用索引覆盖去优化order by。索引覆盖指一个查询语句的执行只用从索引中就能够查到，不必从数据表中读取。

mysql> explain select id from user order by name;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | user_name | 767     | NULL | 998179 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set (0.00 sec)

但是若select *操作时，那么并不会使用索引来优化order by操作。

mysql> explain select * from user order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 998179 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set (0.00 sec)

mysql官网是这样描述的：

单索引的order by优化.png

2. 联合索引的情况下

2.1 场景描述

当复合索引的最左前缀列为过滤条件的常量过滤时，order by字段配合常量过滤字段满足最左前缀时可以使用复合索引进行排序优化。

如，建立复合索引(a,b,c)可以

1、使用复合索引扫描排序有：

from tbl_name where a=xx order by b,c;
from tbl_name where a=xx order by b;

2、过滤字段不是复合索引中的常量，但是order by列满足最左前缀是可以使用覆盖索引：

from tbl_name where a>xx order by a,b     #order by字段满足最左前缀

3、一些情况不能使用复合索引扫描排序的情况：

from tbl_name where a=xx order by b desc,c asc;  #一列为升序一列为降序
from tbl_name where a=xx order by b,d;      #order by列引用了一个不在索引中的字段
from tbl_name where a=xx order by c;  #无法组合成索引的最左前缀
from tbl_name where a=xx and b in (xx,xx) order by c;    #存在范围查询

2.2 聚簇索引

如果我们建立单列索引（A），实际上相当于在（A，ID）上建立了索引，其中ID为主键。这种情况下对于 where A=xx order by ID的查询是非常有帮助的。但是如果我们建立了复合索引（A,B）,那么就相当于在(A,B,ID)上建立了索引，那么对于where A=xx order by ID这样的查询，就使用不到索引扫描排序，只能用filesort排序(using filesort)了。

2.3 面试实战

如果有这么一条sql：

select * from table where a=1 and b>10 order by c;

如果想在abc三列上去建立一个复合索引，那么如何建立？？？

在acb列上建立索引? ? ?

不推荐这样建立索引，这样的情况相当于在ac列上建立索引！！！

实际上推荐的是在ab上建立索引。

mysql语句的各个子句中。where子句是筛选数据，order by子句是排序数据。order by排序where子句筛选后的数据集，若where子句使用的是索引访问类型（explain的type列）为index或以上的类型，那么数据集是以索引的顺序的有序集合。若order by筛选的顺序正好是索引的顺序，那么才能优化索引。

但where语句和order by语句处理数据时机不同。order by的索引最左前缀和where子句最左前缀不能相互影响。即acb复合索引中：where走ab索引，order去走c索引的情况是不存在的。

即若建立acb索引，那么where会使用a索引进行筛选，因为where不存在c列条件，那么不能使用b列进行筛选，该索引等效于ac列。

3. 连表查询情况下

如果为多表关联查询&利用索引来进行优化排序，排序列必须是对驱动表字段的排序。

当然，具体场景的还是需要依照explain执行计划来具体分析。

3.1 准备

3.1.1 数据准备

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) DEFAULT '' COMMENT '名字',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `p_id` bigint(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_pId` (`p_id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1;

CREATE TABLE `t_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(32) DEFAULT NULL COMMENT '产品名',
  `price` double DEFAULT NULL COMMENT '价格',
  `count` int(11) DEFAULT NULL COMMENT '余额',
  `type` int(11) DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

记录集中，t_product数据量少。

3.1.2 什么是驱动表

何谓驱动表，指多表关联查询时，第一个被处理的表，亦可称之为基表，然后再使用此表的记录去关联其他表。驱动表的选择遵循一个原则：在对最终结果集没影响的前提下，优先选择结果集最少的那张表作为驱动表。这个原则说的不好懂，结果集最少，这个也许我们能估出来，但对最终结果集不影响，这个就不好判断了，难归难，但还是有一定规律的：LEFT JOIN 一般以左表为驱动表（RIGHT JOIN一般则是右表），INNER JOIN 一般以结果集少的表为驱动表，如果还觉得有疑问，则可用 EXPLAIN 来找驱动表，其结果的第一张表即是驱动表。

mysql> EXPLAIN SELECT
    * 
FROM
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    p.price 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where    |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)

如上所示的sql中：t_product为驱动表。

3.1.3 三种排序方式对比

排序处理方式	执行计划extra列信息	排序性能比较
利用索引进行排序	无	高
只对驱动表进行排序	Using filesort	中
对临时表进行排序	Using temporary; Using filesort	低

3.2 利用驱动表索引优化order by

2.2中进行的描述——“如果我们建立单列索引（A），实际上相当于在（A，ID）上建立了索引，其中ID为主键。”

此时对驱动表p进行的排序，在执行计划中可以看到使用了索引来进行order by的优化。

mysql> explain SELECT
    * 
FROM
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    p.id 
    LIMIT 5;
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref       | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
|  1 | SIMPLE      | p     | index | PRIMARY       | PRIMARY | 8       | NULL      |    1 | NULL        |
|  1 | SIMPLE      | u     | ref   | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

若对被驱动表u.id进行order by 排序呢？

mysql> explain SELECT
    * 
FROM
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    u.id 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where                     |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
2 rows in set (0.00 sec)

可以看到，Using temporary; Using filesort借助临时表来进行排序。效率更加低下。

3.3 仅对驱动表进行排序

3.3.1 场景

多表关联查询中，排序字段为驱动表字段，且该字段无法有效利用索引；
被驱动表关联字段为有效索引字段，有效利用INLJ算法进行表关联

mysql> explain SELECT
    * 
FROM
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    p.price 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where    |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------+
2 rows in set (0.00 sec)

由于order by字段p.price无法有效利用索引，所以必须将满足过滤条件的记录放至sort buffer进行排序处理。在执行计划中，我们可以看到“Using filesort”表示使用filesort进行了排序处理

3.3.2 对于使用驱动表排序的优化

若SQL无法有效利用索引进行优化，且仅仅是对驱动表进行排序处理，这已然是一种相对较好的情况，我们更多的只需要关注SQL的where过滤条件是否可以有效利用索引减少驱动表需要扫描以及排序的记录数。

3.4 使用临时表进行排序

3.4.1 场景

多表关联查询中，排序字段为被驱动表字段，MySQL必须获取到多表关联的结果后才可以对这些记录进行排序处理

mysql> explain SELECT
    * 
FROM
    t_user u
    JOIN t_product p ON u.p_id = p.id 
WHERE
    u.`name` = 'xxx' 
ORDER BY
    u.id 
    LIMIT 5;
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY       | NULL    | NULL    | NULL      |    6 | Using temporary; Using filesort |
|  1 | SIMPLE      | u     | ref  | idx_pId       | idx_pId | 8       | test.p.id |    3 | Using where                     |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+---------------------------------+
2 rows in set (0.00 sec)

由于order by字段为被驱动表的u.id字段，所以必须将获取到两表join的结果集，然后进行排序，在执行计划中，我们可以看到“Using temporary; Using filesort”表示使用临时表进行了排序处理

3.4.2 对于使用临时表排序的优化

对于使用临时表进行排序的查询其资源消耗是以上说到的三种排序方式下资源消耗最大的一种排序方式。在这种模式下，优先考虑排序字段是否可以等价替换为驱动表字段，将其转换为只对驱动表进行排序；若以上手段无效，我们只能通过where过滤条件有效利用索引，通过索引过滤尽量减少SQL查询扫描数据量；select只查询需要的字段，避免select *，尽量减少磁盘临时表的使用。

4. 实战优化

4.1 order by id替换create_time

可以：若a b复合索引：

select * from table where a=‘xx’ and b='yy' order by create_time desc;

可以优化为：

select * from table where a=‘xx’ and b='yy' order by id desc;

原理：实际上，当同时命中a b时，记录便是按照主键索引排序的，此时order by id可以省去一部分的时间。由因为id是有序的，故可以替换create_time。

注意：但是只使用a索引时 order by id，也得借助文件完成排序。

历史文章

mybatis&&数据库优化&&缓存目录

文章参考

最后编辑于：2022.04.26 16:52:58

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 217,826评论 6赞 506
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,968评论 3赞 395
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 164,234评论 0赞 354
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,562评论 1赞 293
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,611评论 6赞 392
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,482评论 1赞 302
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,271评论 3赞 418
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,166评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,608评论 1赞 314
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,814评论 3赞 336
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,926评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,644评论 5赞 346
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,249评论 3赞 329
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,866评论 0赞 22
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,991评论 1赞 269
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,063评论 3赞 370
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,871评论 2赞 354