目录
前言:
涉及到排序相关知识。
面试中可能要引导一下,排序相关内容还是需要熟悉一下的
1. 排序相关
1.1 group by name(使用文件排序)
没有加上索引:(排序有着sort_buffer)
“Using filesort”表示的就是需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。
1.2 全字段排序的简单过程解析(重点:用到了快排)
带where条件的order by 执行过程整体大致过程是:
1. 初始化sort_buffer,然后根据where条件找到记录
2.取出整行之后,将select的字段都放入到sort_buffer中
3. 继续往下查找符合条件的行放入到buffer
4.对sort buffer中的需要排序的字段进行快速排序(需要会写快排)
5.按照结果取limt的行数 返回给客户端
(因为order by,所以limit也是在排序之后 把所有符合条件的行扔到buffer排序之后才进行limit限制)
称为:全字段排序
1.3 在内存中排序还是硬盘中?(硬盘排序用到了归并排序)
按某个属性排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size。
sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。
内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法(要会写)。可以这么简单理解,MySQL 将需要排序的数据分成 若干 份,每一份单独排序后存在这些临时文件中。然后把这 若干个有序文件再合并成一个有序的大文件。
这个生成的文件份数与sort buffer size有关,size越小,那么生成的文件数越多。
1.4 如果需要查询出来的字段很多,使用rowid排序
因为查询的字段多,内存很可能放不下。然后使用临时文件归并排序的性能会很差。
所以单行很大,使用另外一种算法。
SET max_length_for_sort_data = 16;
设置一下行数据的长度,如果单行长度超过这个值,mysql会认为单行很大,换一种算法。
应该是看定义数据库的时候的字段的长度。
其实就是因为要去的内容太大了,所以就把要排序的字段和主键ID放到buffer里面,排序完了之后在根据id回表取出所需要的值。
实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。
如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。
2. 总结
如果内存够,就要多利用内存,尽量减少磁盘访问。内存足够在内存中排序直接返回,不够才会进行把主键放进去排序,然后回表取数据。
所谓全字段排序,指的是把所有的字段都扔到buffer里面去排序。
rowid排序就是需要回表再进行返回数据。
mysql排序是一个成本很高的操作,能使用别的方式就不要用order by。
但是并不是所有的orderby 都需要排序操作。因为数据无序那么就需要在临时表上面做排序操作,如果数据天然就是排序好的,
那么也就不需要再排序了。
也就是如果加上索引,那么就是使用索引树的主键ID,然后回表查询到需要的属性,加入到结果集里面去。
TIPS:使用覆盖索引可以更加简化查询操作,并且是有序的。
Q: 如果条件是where city in ("Tianjin",“Beijing”) order by name limit 100 怎么避免排序?
用到 (city,name) 联合索引的特性,把这一条语句拆成两条语句,执行流程如下:
将语句分成两个,然后在java内部贼进行归并排序。
select * from t where city=“Tianjin” order by name limit 100;
select * from t where city=“Beijing” order by name limit 100;
其实就是减少sql中进行操作,取出来再java中进行