后面也会持续更新，学到新东西会在其中补充。
建议按顺序食用，欢迎批评或者交流！
缺什么东西欢迎评论！我都会及时修改的！
感谢各位大佬写的文章让我学到很多东西！只是在各位大佬的基础加了我自己的思路！
参考文献
Mysql order by 优化及分析执行的神器-Optimizer TraceMysql order by. 优化 - 掘金
Mysql调优之Using filesort一般情况-CSDN博客
测试环境
MySql5.7、MySql9.0.4
环境搭建
CREATE TABLE `staff` (
`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',
`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',
`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',
`age` INT ( 4 ) NOT NULL COMMENT '年龄',
`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',
PRIMARY KEY ( `id`),
INDEX idx_city ( `city` )
) ENGINE = INNODB COMMENT '员工表';
insert staff values (1,'449006xxxxxxxx2134','小明',22,'上海');
insert staff values (2,'449006xxxxxxxx2134','小李',23,'深圳');
insert staff values (3,'449006xxxxxxxx2134','小刚',28,'东羡');
insert staff values (4,'449006xxxxxxxx2134','小红',20,'上海');
insert staff values (5,'449006xxxxxxxx2134','小芳',36,'北京');
insert staff values (6,'449006xxxxxxxx2134','小莉',19,'深圳');
insert staff values (7,'449006xxxxxxxx2134','小华',27,'振江');explain select name,age,city from staff where city = '深圳' order by age limit 10;
全字段排序
需要做到心中有”树“。
MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。
MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
运行条件
MySql9.0.4
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)小于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，但是会使用更多的空间。
优化的方向则是覆盖索引
全字段测试
## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
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
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，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;
发现优化器还是走了打包字段排序。
set max_length_for_sort_data = 4096;MySql5.7
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，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;
mysql> set max_length_for_sort_data = 1024;
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还会文件排序吗？
mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| staff | 1 | idx_city | 1 | city | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
第一段代码就是走了覆盖索引。
没有where条件，order by字段需要加索引吗 ？
不加索引
mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select * from staff order by city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | staff | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
加索引
mysql> create index idx_city on staff(city);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from staff order by city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | staff | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> show index from staff;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | |
| staff | 1 | idx_city | 1 | city | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
无条件查询的话，即使city上有索引，也不会使用到。因为MySQL优化器认为走普通二级索引，再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据全字段排序或者rowid排序来进行。
修改一下SQL语句
无条件查询,如果m值较小,是可以走索引的.因为MySQL优化器认为，根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
索引存储顺序与order by不一致，如何优化？
select name,age from staff order by age ,name desc limit 10;
age 小到大 name 大到小我们知道name相对age是有序，name是小到大而我们查询的语句是从大到小就会出现文件排序。
mysql> create index idx_name_age on staff(name,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select name,age from staff order by name,age desc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | staff | NULL | index | NULL | idx_name_age | 262 | NULL | 7 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select name,age from staff order by name,age desc limit 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | staff | NULL | index | NULL | idx_name_age | 262 | NULL | 7 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
如何优化呢修改一下索引存储顺序
说白了就算索引存储顺序要和order by顺序对应。
使用了in条件多个属性时，SQL执行是否有排序过程
mysql> create index idx_name_age on staff(name,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from staff;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| staff | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| staff | 1 | idx_name_age | 1 | name | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
| staff | 1 | idx_name_age | 2 | age | A | 7 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
和我之前说的相似in('深圳') 相当于 = '深圳'
in ('深圳','122121') 有点类似于like
这是因为:in有两个条件，在满足深圳时，age是排好序的，但是把满足122121的age也加进来，就不能保证满足所有的age都是排好序的。因此需要Using filesort。
假如id为主键索引，因为主键索引是有序的，所以导致order by id 失效！
select * from a order by id
不是按照最左前缀法则
分页limit过大时，会导致大量排序怎么办?(重要/补档)
七分钟实操 MySQL 初级到高级调优 #MySQL#Java#优化#后端#源码#程序员_哔哩哔哩_bilibili
explain select * from order_info where period = 201906 order by modified desc limit 0,10;
limit N : 返回 N 条记录
offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
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条数据。
这个过程就很慢了。
set profiling = 1;
#走idx_modify索引
select * from order_info where period = 201906 order by modified desc limit 0,10;
#走idx_period索引
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、....都有很多行肯定就慢了。
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按顺序排。
第一条数据在前面，第二条数据在第一条数据的后面。
根据之前的分析，我们是倒序查找对吧！
还需要排序吗？当然不用了索引都给排好了，当然这是理论现在实践一下。
alter table order_info add index idx_period_modify(period,modified);
快了很多啊看一下执行计划
走了联合索引，没有文件排序了。
现在又有一个问题：深翻页问题
select * from order_info where period = 202207 order by modified desc limit 99000,1000;
select * from order_info where period = 202207 order by modified desc limit 0,1000;
解释一下这个过程
我们查到数据返回给server层，通过server来判断是否要给客户端返回。
跳到99000行记录从99001行以后读，读1000行记录，这些记录是客户端要的。
但是99000条记录去哪了呢？每条我都得读，读到server，server需要判断这些是其他页的而不是客户端所需要的。因此相当于我们读了99000都是我们不需要的因此就浪费了时间
解决方案采用覆盖索引
非常重要！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
explain select * from (select id from order_info where period = 202207
order by modified desc limit 99000,1000) as temp
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文件排序
环境搭建：
create table tb(id int, type int, weight int, index t_index(type), index w_index(weight));
编写存储过程！
DELIMITER $
DROP PROCEDURE IF EXISTS fill_test_data; $
CREATE PROCEDURE `fill_test_data`()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE w INT DEFAULT 100;
DECLARE t INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO tb VALUES (i, t, w);
SET i = i + 1;
SET w = (w + 10) % 1000;
SET t = (t + 1) % 10;
END WHILE;
END $
DELIMITER ;
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
create table type(id int,age int,card int);
insert into type values(1,11,11),(2,22,22),(3,33,33),(4,44,44),(5,55,55);
create index idx_age_card on type(age,card);
MySQL5.7
#左侧字段单字段排序时，索引支持升降序。
没有用到filesort
mysql> explain select * from type where age < 22 order by age;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from type where age < 22 order by age desc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#覆盖索引字段排序时，索引排序。效率很高没有filesort
mysql> explain select * from type where age < 22 order by age,card;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#但是一旦第一个左侧字段采用了desc就会产生文件排序
mysql> explain select * from type where age < 22 order by age desc,card;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
MySQL 8版本之前，所有索引都是按升序创建的。当语法本身被解析时，元数据不会被保留。
我们从索引角度来看
mysql> drop index idx_age_card on type;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index indx_age_card on type(age desc,card asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
升序索引升序排列的，降序索引降序排列的。
前向索引扫描从左到右，向后索引扫描从右到左。
MySQL8.0
mysql> explain select * from type where age < 22 order by age;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition; Backward index scan |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from type where age < 22 order by age desc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
MySQL8与MySQL5.7的不同之处
#没有采用filesort
mysql> explain select * from type where age < 22 order by age desc,card;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#order by age,card desc 同样可以采用降序索引规则，但我这里还是filesort。可能还是和成本啥的有关。
mysql> explain select * from type where age < 22 order by age asc,card desc;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | type | NULL | range | idx_age_card | idx_age_card | 5 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+---------------------------------------+
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。
#我们这样创建的话就都会走索引了，就不会产生filesort
create index idx_age_card on type(age,card)
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 上建了索引呢？