MySQL(Order By语句实行序次)

反面也会连续更新，学到新东西会在此中增补。
发起按序次食用，欢迎品评大概交换！
缺什么东西欢迎批评！我都会及时修改的！
感谢各位大佬写的文章让我学到许多东西！只是在各位大佬的根本加了我自己的思绪！
参考文献

Mysql order by 优化及分析实行的神器-Optimizer TraceMysql order by. 优化 - 掘金
看一遍就明白：order by详解
Mysql调优之Using filesort一样寻常情况-CSDN博客
Mysql中单路排序和双路排序详解-CSDN博客
测试情况

MySql5.7、MySql9.0.4
情况搭建

1. CREATE TABLE `staff` (
2. `id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
3. `id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
4. `name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
5. `age` INT ( 4 ) NOT NULL COMMENT '年龄',
6. `city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
7. PRIMARY KEY ( `id`),
8. INDEX idx_city ( `city` )
9. ) ENGINE = INNODB COMMENT '员工表';
11. insert staff values (1,'449006xxxxxxxx2134','小明',22,'上海');
12. insert staff values (2,'449006xxxxxxxx2134','小李',23,'深圳');
13. insert staff values (3,'449006xxxxxxxx2134','小刚',28,'东羡');
14. insert staff values (4,'449006xxxxxxxx2134','小红',20,'上海');
15. insert staff values (5,'449006xxxxxxxx2134','小芳',36,'北京');
16. insert staff values (6,'449006xxxxxxxx2134','小莉',19,'深圳');
17. insert staff values (7,'449006xxxxxxxx2134','小华',27,'振江');

复制代码

1. explain select name,age,city from staff where city = '深圳' order by age limit 10;

复制代码

全字段排序

   须要做到心中有”树“。
  

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。
MySQL 为对应的线程初始化sort_buffer，放入须要查询的name、age、city字段；
运行条件

1. MySql9.0.4
2. mysql> show variables like '%max_length_for_sort_data%';
3. +--------------------------+-------+
4. | Variable_name            | Value |
5. +--------------------------+-------+
6. | max_length_for_sort_data | 4096  |
7. +--------------------------+-------+
8. 1 row in set (0.02 sec)

复制代码

小于4096 走全字段排序 大于4096 走RowID排序

   查询所需的字段全部读取到sort_buffer中，就是全字段排序。
  sort_buffer是一块内存来的，如果数据量太大，sort_buffer放不下怎么办呢？
   磁盘暂时文件辅助排序

如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来举行排序。

RowID排序

全字段和RowID

   MySQL的一个筹划头脑：如果内存够，就要多使用内存，只管淘汰磁盘访问。
  rowID 举行两次回表。
  全字段 举行一次回表。
  全字段一次性从磁盘中读取查询须要的全部列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们举行排序，然后扫描排序后的列表输出。由于全字段服从更快，克制了二次读取数据，把随机IO变成了序次IO，但是会使用更多的空间。
  优化的方向则是覆盖索引
  全字段测试

1. ## 打开optimizer_trace，开启统计
2. set optimizer_trace = "enabled=on";
3. ## 执行SQL语句
4. select name,age,city from staff where city = '深圳' order by age limit 10;
5. ## 查询输出的统计信息
6. SELECT  * FROM `information_schema`.`OPTIMIZER_TRACE`;

复制代码

MySql9.0.4

 接下来表明一下filesort_summary内里的参数！

• memory_available: 262144 字节 (256 KB) 是分配给此排序操纵可用的最大内存。
  

• key_size: 排序键的巨细是 8 字节。也就是索引键(create index idx on student(id))
  
• row_size: 每行数据的巨细为 532 字节。和行的范例有关！
  
• max_rows_per_buffer: 每个内存缓冲区最多可以容纳 15 行数据。
  
• num_rows_estimate: 预估的行数为 15 行。
  
• num_rows_found: 实际找到并处置处罚了 2 行数据。
  
• num_initial_chunks_spilled_to_disk: 初始时没有数据块溢出到磁盘（即全部排序都在内存中完成）。
  
• peak_memory_used: 排序过程中使用的峰值内存量为 32784 字节。
  

排序模式sort_mode

• <varlen_sort_key,rowid>：使用了rowid排序模式
  
• <varlen_sort_key, additional_fields>：使用了全字段排序模式
  
• <varlen_sort_key, packed_additional_fields>：使用了打包字段排序模式（与全字段排序模式工作原理同等，差别点在于会将字段细密的分列在一起，而不是固定长度的空间）普通的讲就是好比：一个字段界说为VARCHAR(32)，不打包占用32字节，打包后大概占用 20字节。
  

MySql5.7

• Rows Estimate: 预估须要排序的行数为 936 行。
  
• Row Size: 每行数据的巨细为 532 字节。
  
• Memory Available: 可用内存为 262144 字节 (256 KB)。
  

• Chosen: 优先队列优化被选中使用 (chosen 为 true)，这意味着当效果集很大时，MySQL 使用了优先队列算法来优化 LIMIT 查询，以淘汰处置处罚的数据量和进步服从。
  
• Rows: 实际返回的行数为 2 行。
  
• Examined Rows: 实际查抄的行数也是 2 行。
  
• Number of Tmp Files: 创建的暂时文件数量为 0，体现整个排序过程都在内存中完成，无需使用磁盘上的暂时文件。
  
• Sort Buffer Size: 排序缓冲区的巨细为 5944 字节。
  
• Sort Mode: 排序模式为 <sort_key, additional_fields>，意味着排序键加上额外的字段一起被排序。
  

RowID测试

MySql9.0.4

1. mysql> show variables like '%max_length_for_sort_data%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| max_length_for_sort_data | 4096  |+--------------------------+-------+1 row in set (0.02 sec)# 设置的小一点，让它走RowID排序； 这个值小于 name,age,city 这三个值set max_length_for_sort_data = 10;## 打开optimizer_trace，开启统计
2. set optimizer_trace = "enabled=on";
3. ## 执行SQL语句
4. select name,age,city from staff where city = '深圳' order by age limit 10;
5. ## 查询输出的统计信息
6. SELECT  * FROM `information_schema`.`OPTIMIZER_TRACE`;

复制代码

   发现优化器还是走了打包字段排序。

1. set max_length_for_sort_data = 4096;

复制代码

 MySql5.7

1. mysql> show variables like '%max_length_for_sort_data%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| max_length_for_sort_data | 1024  |+--------------------------+-------+1 row in set (0.01 sec)# 设置的小一点，让它走RowID排序； 这个值小于 name,age,city 这三个值set max_length_for_sort_data = 10;## 打开optimizer_trace，开启统计
2. set optimizer_trace = "enabled=on";
3. ## 执行SQL语句
4. select name,age,city from staff where city = '深圳' order by age limit 10;
5. ## 查询输出的统计信息
6. SELECT  * FROM `information_schema`.`OPTIMIZER_TRACE`;

复制代码

1. mysql> set max_length_for_sort_data = 1024;
2. Query OK, 0 rows affected (0.00 sec)

复制代码

优化

1.调解参数：

• max_length_for_sort_data ； 修改其巨细，可以让mysql 排序选择 全局排序还是rowid 字段排序。(从 8.0.20 开始废弃了)
  
• sort_buffer_size 增长 [sort_buffer_size](MySQL :: MySQL 8.0 Reference Manual :: 7.1.8 Server System Variables) 变量值。理想情况下，该值应该充足大，以便整个效果集恰当排序缓冲区（以克制写入磁盘和归并过程）。
  
• 增长 [read_rnd_buffer_size](MySQL :: MySQL 8.0 参考手册 :: 7.1.8 服务器体系变量 --- MySQL :: MySQL 8.0 Reference Manual :: 7.1.8 Server System Variables) 变量值以便一次读取更多行。
  

 2.使用覆盖索引
若字段上有索引order by还会文件排序吗？

1. mysql> show index from staff;
2. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5. | staff |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
6. | staff |          1 | idx_city |            1 | city        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
7. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8. 2 rows in set (0.01 sec)

复制代码

 第一段代码就是走了覆盖索引。

没有where条件，order by字段须要加索引吗 ？

不加索引

1. mysql> show index from staff;
2. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
4. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5. | staff |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
6. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7. 1 row in set (0.00 sec)
9. mysql> explain select * from staff order by city;
10. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
11. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
12. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
13. |  1 | SIMPLE      | staff | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using filesort |
14. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
15. 1 row in set, 1 warning (0.00 sec)

复制代码

 加索引

1. mysql> create index idx_city on staff(city);
2. Query OK, 0 rows affected (0.03 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> explain select * from staff order by city;
6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
7. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
8. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
9. |  1 | SIMPLE      | staff | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |   100.00 | Using filesort |
10. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
11. 1 row in set, 1 warning (0.01 sec)
13. mysql> show index from staff;
14. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
15. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
16. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
17. | staff |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
18. | staff |          1 | idx_city |            1 | city        | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
19. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
20. 2 rows in set (0.00 sec)

复制代码

  无条件查询的话，纵然city上有索引，也不会使用到。由于MySQL优化器以为走平常二级索引，再去回表资源比全表扫描排序更高。以是选择走全表扫描,然后根据全字段排序大概rowid排序来举行。
  修改一下SQL语句

无条件查询,如果m值较小,是可以走索引的.由于MySQL优化器以为，根据索引有序性去回表查数据,然后得到m条数据,就可以制止循环,那么资源比全表扫描小,则选择走二级索引。
索引存储序次与order by差别等，怎样优化？

1. select name,age from staff order by age ,name desc limit 10;
2.                             age 小到大    name 大到小

复制代码

 我们知道name相对age是有序，name是小到大而我们查询的语句是从大到小就会出现文件排序。

1. mysql> create index idx_name_age on staff(name,age);
2. Query OK, 0 rows affected (0.02 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> explain select name,age from staff order by name,age desc;
6. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
7. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                       |
8. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
9. |  1 | SIMPLE      | staff | NULL       | index | NULL          | idx_name_age | 262     | NULL |    7 |   100.00 | Using index; Using filesort |
10. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
11. 1 row in set, 1 warning (0.00 sec)
13. mysql> explain select name,age from staff order by name,age desc limit 1;
14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
15. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                       |
16. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
17. |  1 | SIMPLE      | staff | NULL       | index | NULL          | idx_name_age | 262     | NULL |    7 |   100.00 | Using index; Using filesort |
18. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
19. 1 row in set, 1 warning (0.01 sec)

复制代码

 怎样优化呢修改一下索引存储序次

   说白了就算索引存储序次要和order by序次对应。 
  使用了in条件多个属性时，SQL实行是否有排序过程

1. mysql> create index idx_name_age on staff(name,age);
2. Query OK, 0 rows affected (0.02 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> show index from staff;
6. +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7. | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
8. +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
9. | staff |          0 | PRIMARY      |            1 | id          | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
10. | staff |          1 | idx_name_age |            1 | name        | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
11. | staff |          1 | idx_name_age |            2 | age         | A         |           7 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
12. +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
13. 3 rows in set (0.01 sec)

复制代码

 和我之前说的相似in('深圳') 相称于 = '深圳'
in ('深圳','122121')  有点类似于like
这是由于:in有两个条件，在满足深圳时，age是排好序的，但是把满足122121的age也加进来，就不能包管满足全部的age都是排好序的。因此须要Using filesort。

如果id为主键索引，由于主键索引是有序的，以是导致order by id 失效！  

1. select * from a order by id

复制代码

不是按照最左前缀法则

分页limit过大时，会导致大量排序怎么办?(告急/补档)

七分钟实操 MySQL 低级到高级调优 #MySQL#Java#优化#后端#源码#步伐员_哔哩哔哩_bilibili 

1. explain select * from order_info where period = 201906 order by modified desc limit 0,10;
3. limit N : 返回 N 条记录
4. offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
5. limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

复制代码

 流程是如许的：
1. 找到idx_modified索引树，从最右边结点开始往左边扫描。
2. 好比从末了一个结点，然后回表找到是否与period 匹配如果匹配的话就返回给客户端直到10条。
为什么没有选择idx_period呢？
由于排序的是modified，如果我们使用的idx_period索引，就须要把全部period信息读出来才华对modified排序。
流程是如许的：
1. 找到idx_period索引树如果数据有10w条，举行回表10w次（为什么要回表由于idx_period索引树上没有modified的信息）。
2. 再举行排序（为什么要排序？由于聚簇索引树是按照ID举行排序的而不是modified因此须要使用内存的sort buffer来举行排序）取前10条数据。
这个过程就很慢了。

1. set profiling = 1;
2. #走idx_modify索引
3. select * from order_info where period = 201906 order by modified desc limit 0,10;
4. #走idx_period索引
5. select * from order_info force index(idx_period) where period = 201906 order by modified desc limit 0,10;

复制代码

显着发现走idx_period比走idx_modify快许多。
为什么？不符合之前分析的情况呢？

 rows是预估扫描的行，顺着扫描100多行就够了。
但是实际上201906在很前面，201907、201908、....都有许多行肯定就慢了。

1. select * from order_info where period = 202201 order by modified desc limit 0,10;

复制代码

 
省的时间不止一点半点。
   如今的题目是：
  固然我修改了常数满足了淘汰时间，但是常数万一就要很前面的年份呢？
  如许我们只能逼迫走idx_period索引了对吧。
  走idx_period须要文件排序，还须要耗时间。

那么怎么克制文件排序呢？
团结！
想象一下：
同时（并发）插入了两条数据，建索引对period 和 modify
第一条数据：period：1 modify：2025:01:25:12:00:00
第二条数据：period：1 modify：2025:01:25:12:00:01
在索引树内里如果period 雷同modify按序次排。
第一条数据在前面，第二条数据在第一条数据的反面。
根据之前的分析，我们是倒序查找对吧！
还须要排序吗？固然不消了索引都给排好了，固然这是理论如今实践一下。

1. alter table order_info add index idx_period_modify(period,modified);

复制代码

快了许多啊看一下实行筹划

 走了团结索引，没有文件排序了。 
如今又有一个题目：深翻页题目

1. select * from order_info where period = 202207 order by modified desc limit 99000,1000;
3. select * from order_info where period = 202207 order by modified desc limit 0,1000;

复制代码

表明一下这个过程
我们查到数据返回给server层，通过server来判定是否要给客户端返回。
跳到99000行纪录从99001行以后读，读1000行纪录，这些纪录是客户端要的。
但是99000条纪录去哪了呢？每条我都得读，读到server，server须要判定这些是其他页的而不是客户端所须要的。因此相称于我们读了99000都是我们不须要的因此就浪费了时间
 办理方案接纳覆盖索引
非常告急！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

1. explain select * from (select id from order_info where period = 202207
2. order by modified desc limit 99000,1000) as temp
3. join order_info where temp.id = order_info.id;

复制代码

 
id 越大的越先实行
id  = 2 检察order_info表 走的是团结索引idx_period_modify 
using index代表着覆盖索引。
id 相称 从上往下实行。
id = 1 检察derived2 依赖于id = 2 查询的效果，举行all 全表扫描。
预估是100000 行但是实际上只有1000行（limit 99000,1000）
对1000主表（驱动表）和原表order_info 举行join
eq-ref 就是可以明白成唯一索引（实际上是主键）效果集只会有一个。
key是主键，关联字段ref 为 temp.id
每次join 预估都是1行

 时间又变快了！
Using filesort文件排序

情况搭建：

1. create table tb(id int, type int, weight int, index t_index(type), index w_index(weight));
3. 编写存储过程！
4. DELIMITER $
6. DROP PROCEDURE IF EXISTS fill_test_data; $
8. CREATE PROCEDURE `fill_test_data`()
9. BEGIN
10.     DECLARE i INT DEFAULT 1;
11.     DECLARE w INT DEFAULT 100;
12.     DECLARE t INT DEFAULT 1;
14.     WHILE i <= 100000 DO
15.         INSERT INTO tb VALUES (i, t, w);
16.         SET i = i + 1;
17.         SET w = (w + 10) % 1000;
18.         SET t = (t + 1) % 10;
19.     END WHILE;
20. END $
22. DELIMITER ;
24. call fill_test_data(); 需要执行一段时间

复制代码

 type和weight分别创建了索引

第一段代码：走t_index索引->回表->全字段排序/rowid排序->效果集
第二段代码：全表扫描-> 全字段排序/rowid排序->效果集

和select *好像并没有关系，由于索引表中只有type 和 主键ID weight的数据只能去原数据表拿取。
众所周知索引是有序的我们是否能把两个字段联合起来呢？

 再举行测试一下！

第一段代码/第二段代码：都是覆盖索引
第三段代码：范围扫描idx_sum索引->效果集 
 发现using filesort被清除了！
须要留意的地方

• order by 字段不是索引字段
  
• order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc;
  
• order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
  
• order by 多个字段排序时，不是按照索引序次举行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc;
  

MySQL8新特性之降序索引 

【IT老齐279】MySQL8新特性之降序索引_哔哩哔哩_bilibili

1. create table type(id int,age int,card int);
2. insert into type values(1,11,11),(2,22,22),(3,33,33),(4,44,44),(5,55,55);
3. create index idx_age_card on type(age,card);

复制代码

MySQL5.7 

1. #左侧字段单字段排序时，索引支持升降序。
2. 没有用到filesort
3. mysql> explain select * from type where age < 22 order by age;
4. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
5. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
6. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
7. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition |
8. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
9. 1 row in set, 1 warning (0.00 sec)
11. mysql> explain select * from type where age < 22 order by age desc;
12. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
13. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
15. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition |
16. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
17. 1 row in set, 1 warning (0.00 sec)
19. #覆盖索引字段排序时，索引排序。效率很高没有filesort
20. mysql> explain select * from type where age < 22 order by age,card;
21. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
22. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
23. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
24. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition |
25. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
26. 1 row in set, 1 warning (0.00 sec)
28. #但是一旦第一个左侧字段采用了desc就会产生文件排序
29. mysql> explain select * from type where age < 22 order by age desc,card;
30. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
31. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                 |
32. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
33. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition; Using filesort |
34. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
35. 1 row in set, 1 warning (0.00 sec)

复制代码

MySQL 8版本之前，全部索引都是按升序创建的。当语法自己被分析时，元数据不会被生存。
我们从索引角度来看

1. mysql> drop index idx_age_card on type;
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> create index indx_age_card on type(age desc,card asc);
6. Query OK, 0 rows affected (0.02 sec)
7. Records: 0  Duplicates: 0  Warnings: 0

复制代码

 升序索引升序分列的，降序索引降序分列的。
前向索引扫描从左到右，向后索引扫描从右到左。

 MySQL8.0

1. mysql> explain select * from type where age < 22 order by age;
2. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition; Backward index scan |
6. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
7. 1 row in set, 1 warning (0.00 sec)
9. mysql> explain select * from type where age < 22 order by age desc;
10. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
11. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
12. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
13. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition |
14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
15. 1 row in set, 1 warning (0.00 sec)

复制代码

 MySQL8与MySQL5.7的差别之处

1. #没有采用filesort
2. mysql> explain select * from type where age < 22 order by age desc,card;
3. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
4. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
5. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
6. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition |
7. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
8. 1 row in set, 1 warning (0.00 sec)
10. #order by age,card desc 同样可以采用降序索引规则，但我这里还是filesort。可能还是和成本啥的有关。
11. mysql> explain select * from type where age < 22 order by age asc,card desc;
12. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
13. | id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                 |
14. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
15. |  1 | SIMPLE      | type  | NULL       | range | idx_age_card  | idx_age_card | 5       | NULL |    1 |   100.00 | Using index condition; Using filesort |
16. +----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
17. 1 row in set, 1 warning (0.00 sec)

复制代码

MySQL8.0
不能满足:1.order by age,card 2.order by age desc,card desc
由于向前索引扫描和向后扫描都没有满足，之前创建的索引是先降序(age)再升序(card)；
除非按照规定好的索引序次，否则都是要接纳filesort。
因此之前那张图，MySQL5.7的服从要高于MySQL8.0。
那么我们再增长一个，就可以不走filesort。

1. #我们这样创建的话就都会走索引了，就不会产生filesort
2. create index idx_age_card on type(age,card)
3. create index idx_age_card on type(age desc,card)

复制代码

总结

   

• 服从最左原则
  
• 选择index枚举行排序
  
• FileSort和Index，Index服从较高，FileSort方式服从较低。
  

  留点题目

select * from a where type = 5 order by id 会发生文件排序吗？
在type 和 id 上分别建索引呢？
那么在type 和 id 上建了索引呢？