前言

这篇文章继续讨论索引优化，如果没有看过上篇文章MySQL索引优化一一定看了再看这篇。

你别问我：阿紫，那你怎么不写到一篇文章里呀？

好吧，其实没有什么特别的理由，单纯是累了，想休息休息接着写。

好了继续继续。

这篇文章的主要内容：讨论一下OrderBy、Join、In、Exist相关的原理。

OrderBy

order by是怎么使用索引的？

order by同样遵循最左前缀法则，只有当order by的字段是最左字段或者跟随where条件的字段时，才能使用索引排序

排序排序，关键就在于：有序

如以下联合索引：name_age_position

image-20230120150557493

name字段是天然有序的，name值相同时，age是有序的，age相同时，position是有序的。

那应该怎么判断sql使用了索引排序呢？

如以下sql

explain select id from employees order by name;

image-20230120221351505

Extra列：

Using index：使用覆盖索引

Using filesort：将用文件排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

只要没有Extra列出现use filesort，那么就是用的索引排序

再看看使用文件排序的sql

explain select id from employees order by age;

image-20230120221742350

注意，使用了索引是使用了索引，文件排序是文件排序，这是两码事。

比如你使用了索引进行查找数据，但是查找出的数据是用的文件排序。

接下来看看一些案例

sql1

explain select * from employees where name = 'zhangsan18' order by age,position;

索引排序，age跟在name字段后,position跟在age字段后

image-20230121214814792

sql2

explain select * from employees where name = 'zhangsan18' order by position,age;

文件排序，因为该sql是先使用position字段排序，再使用age字段排序，而position字段在name相同时依旧是无序的。

image-20230121215028356

sql3

explain select * from employees where name = 'zhangsan18' and age = 18 order by position,age;

索引排序，position跟在age后，是有序的，而orderby后的age其实会被优化成常量，因为是通过age=10查询出的数据

image-20230121215301638

sql4

explain select * from employees where name = 'zhangsan18' order by age asc,position desc;

文件排序，虽然age字段可以用索引排序，但是position字段逆序排序。

image-20230121220740347

可能会不太好理解，这里结合图说明一下

image-20230121221138310

索引是先通过age字段排序，然后对age字段相同的记录，进行position逆序排序，最终查询出的结果是这样的

image-20230121221406010

所以position字段需使用文件排序。

sql5

select * from employees where name = 'zhangsan18' order by age desc,position desc;

索引排序，因为age，position字段都是逆序的，相当于是索引上从右往左遍历

image-20230121221643560

image-20230121221709624

sql6

explain select * from employees where name > 'zhangsan18' order by age,position;

文件排序，因为name走范围查询，age字段走不了索引了。同上篇索引优化一中sql5的分析

image-20230121222001356

sql7

explain select * from employees where name >= 'zhangsan18' order by age,position;

依旧是文件排序，如果你看了上文，你可能又会有疑惑了：age字段不是会走索引吗？咋是文件排序勒？

这里再强调一遍：走索引是走索引，排序是排序。

没错，在name=zhangsan18时，age,position是有序的，可以使用索引排序。

但是在name>zhangsan18时，age,position是无序的，需要使用文件排序。

image-20230121222646265

15,25,16,33：无序的对吧

好了，关于排序的案例就到这里，更多的案例就还是由你自己去探索吧

什么是文件排序？

文件排序分为单路排序和双路排序

单路排序

一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

双路排序

首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行id(主键)，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。

MySQL是选择用哪种排序的？

MySQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。

  • 如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式
  • 如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

小结

1、如果可以使用索引排序，尽量使用索引排序，但是实在没有办法进行索引排序也不要勉强，优先对where筛选语句做索引优化，因为筛选出的数据往往是很少的，排序成本很低。

2、如果没有办法使用文件排序，服务器内存又充足的情况下，那么可以适当调整下max_length_for_sort_data,让MySQL使用单路排序，这样可以减少回表，效率会好一些。