链接：https://pan.baidu.com/s/1Rbf6oWZB6VSoDdTgJGqUpw 

提取码：8fwr

MySQL优化篇：排序分组优化 where 条件和on的判断这些过滤条件，作为优先优化的部分，是要被先考虑

的。其次，如果有分组和排序，那么也要考虑group by 和order by。 1、order by关键字排序优化 ORDER BY子句，尽量使用index方式排序，避免使用FileSort方式排

序

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

1.1 无过滤不索引 using ãlesort说明进行了手工排序。原因在于没有where 作为过滤条件！

结论：无过滤，不索引。where ，limit都相当于一种过滤条件，所以才能使用

上索引。

1.2 顺序错，必排序 create index idx_age_deptid_name on emp(age,deptid,name); explain select * from emp where age=40 order by deptid; explain select * from emp order by age,deptid; explain select * from emp order age,deptid limit 10; 1234 explain select * from emp where age=45 order by deptid,name; explain select * from emp where age=45 order by name,deptid; explain select * from emp where age=45 order by deptid,empno; 123

第二条SQL语句中，where两侧列的顺序可以变换，效果相同，但是order by 列

的顺序不能随便变换。

第三条SQL语句中，empno字段并没有建立索引，因此也无法使用到索引，此字

段需要排序

第四条SQL语句中，deptid作为过滤条件的字段，无法使用索引，因此排序没法

用上索引

1.3 方向反，必排序

如果可以用上索引的字段都是用正序或者逆序，实际上是没有任何影响的，无非

将结果集调换顺序。

如果排序的字段，顺序有差异，就需要将差异的部分，进行一次倒置顺序，因此

还是需要手动排序。

1.4 索引的选择 （1）清除emp表上的所有索引，只保留主键索引 （2）执行以下查询语句

查询：年龄为30岁的，且员工编号小于101000的用户，按照用户名称排序

（3）全表扫描是不被允许的，需要进行优化

思路：首先需要让where 的过滤条件，用上索引，查询中，age，empno是查询

的过滤条件，而name则是排序的字段，因此创建一个此三个字段的组合索引：

再次查询，发现using ælesort依然存在。

原因：empno是范围查询，因此导致了索引失效，所以name字段无法使用索引

排序。

所以，三个字段的组合索引，没有意义，因为empno和name字段只能选择其

一。

（4）解决办法：要么选择empno，要么选择name

两个索引同时存在，mysql会选择哪一个呢？执行上述SQL语句进行查看：

原因：所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数

据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序

但实际提升性能有限。相对的empno < 101000这个条件如果没有用到索引的

话，要对几万条的数据进行扫描，这是非常消耗性能的，使用empno字段的范围

查询，过滤性更好（empno从100000开始）

1 create index idx_age_empno_name on emp(age,empno,name);

结论：当范围条件和group by 或者order by 的字段出现二选一的时候，优先观

察条件字段的过滤数量，如果过滤的数量足够多，而需要排序的数据并不多时，

优先把索引放在范围字段上。反之，亦然。

1.5 using ælesort （1）MySQL的排序算法 1）双路排序 MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，

读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照

列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。

简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时

的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

2）单路排序

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然

后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并

且把随机 IO 变成了顺序 IO,但是它会使用更多的空间，因为它把每一行都保存

在内存中了。

3）单路排序存在的问题

由于单路是后出的，总体而言好过双路。但是存在以下问题：

在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字

段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次

只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），

排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。

结论：本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿 失。（2）优化策略 <1> 增大sort_buffer_size参数地设置

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提

高，因为这个参数是针对每个进程的 1M~8M 之间调整。

<2> 增大max_length_for_sort_data参数的设置 mysql 使用单路排序的前提是排序的字段大小要小于

max_length_for_sort_data。提高这个参数，会增加用改进算法的概率。但是如

果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的

磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）。

<3> 减少select后面的查询的字段 Order by时使用select * 是一个大忌只Query需要的字段，这非常重要，在这里

的影响如下

当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字

段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则

用老算法——多路排序。

两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建

tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险

会更大一些,所以要提高 sort_buffer_size。