优化思路:
方案一：为了去掉 filesort 我们可以创建特定索引
#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME); EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
方案二：尽量让 where 的过滤条件和排序使用上索引
建一个三个字段的组合索引:
DROP INDEX idx_age_name ON student; CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME); EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
此时又使用了filesort，这是为什么呢？这是因为此时filesort的性能更高。不信你可以对比执行下，看看时间的区别。结果竟然有 filesort 的 sql 运行速度，超过了已经优化掉 filesort的 sql，而且快了很多，几乎一瞬间就出现了结果。看来优化器做的工作真的特别灵活。
原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤大部分数据的话，剩下几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的 stuno < 10100 这个条件，如果没有用到索引的话，要对几万条数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择
结论:
两个索引同时存在，mysql 自动选择最优的方案。（对于这个例子，mysql 选择 idx_age_stuno_name）。但是，随着数据量的变化，选择的索引也会随之变化的 。
当【范围条件】和【group by 或者 order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
2.4 filesort的算法
排序的字段若不在索引列上，则 filesort 会有两种算法：双路排序 和 单路排序
双路排序（慢）
MySQL4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在 buffer 进行排序，再从 磁盘取其他字段 。
取一批数据，要对磁盘进行两次扫描，众所周知，IO 是很耗时的，所以在 MySQL4.1 之后，出现了第二种改进的算法，就是单路排序。
单路排序（快）
从磁盘读取查询需要的 所有列 ，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。
结论及引申出的问题
由于单路是后出的，总体而言好过双路
但是用单路有问题
在 sort_buffer 中，单路比多路要 多占用很多空间，因为单路是把所有字段都取出，所以可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 temp 文件，多路合并），排完再取 sort_buffer 容量大小，再排…从而多次I/O。
单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。
优化策略
尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M - 8M 之间调整。MySQL5.7，InnoDB 存储引擎默认值都是 1048576 字节，1MB。
image-20220701143536810
尝试提高 max_length_for_sort_data
提高这个参数，会增加改进算法的概率。
SHOW VARIABLES LIKE’%max_length_for_sort_data%';
SQL 复制
但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。如果需要返回的列的总长度大于 max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整。
Order by 时 select 是一个大忌。最好只Query需要的字段。*
当 Query 的字段大小综合小于 max_length_for_sort_data，而且排序字段不是 TEXT|BLOG 类型时，会改进后的算法——单路排序，否则用老算法——多路排序。
两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size
6. GROUP BY优化
group by 使用索引的原则几乎跟 order by 一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了
减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。
包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。
