mysql之filesort原理详解数据库
在执行计划中,可能经常看到有Extra列有filesort,这就是使用了文件排序,这当然是不好的,应该优化,但是,了解一下他排序的原理也许很有帮助,下面看一下filesort的过程:
1、根据表的索引或者全表扫描,读取所有满足条件的记录。
2、对与每一行,存储一对值到缓冲区(排序列,行记录指针),一个是排序的索引列的值,即order by用到的列值,和指向该行数据的行指针,缓冲区的大小为sort_buffer_size大小。
3、当缓冲区满后,运行一个快速排序(qsort)来将缓冲区中数据排序,并将排序完的数据存储到一个临时文件,并保存一个存储块的指针,当然如果缓冲区不满,则不会重建临时文件了。
4、重复以上步骤,直到将所有行读完,并建立相应的有序的临时文件。
5、对块级进行排序,这个类似与归并排序算法,只通过两个临时文件的指针来不断交换数据,最终达到两个文件,都是有序的。
6、重复5直到所有的数据都排序完毕。
7、采取顺序读的方式,将每行数据读入内存,并取出数据传到客户端,这里读取数据时并不是一行一行读,读如缓存大小由read_rnd_buffer_size来指定。
这就是filesort的过程,采取的方法为:快速排序 + 归并排序,但有一个问题,就是,一行数据会被读两次,第一次是where条件过滤时,第二个是排完序后还得用行指针去读一次,一个优化的方法是,直接读入数据,排序的时候也根据 这个排序,排序完成后,就直接发送到客户端了,过程如下:
1、读取满足条件的记录
2、对于每一行,记录排序的key和数据行指针,并且把要查询的列也读出来
3、根据索引key排序
4、读取排序完成的文件,并直接根据数据位置读取数据返回客户端,而不是去访问表
这也有一个问题:当获取的列很多的时候,排序起来就很占空间,因此,max_length_for_sort_data变量就决定了是否能使用这个排序算法
建议:
1、对于使用filesort的慢查询,可以改小一些max_length_for_sort_data来使用第一个方法
2、对于想要加快order by 的顺序,有以下一些策略:
a、增加sort_buffer_size的大小,如果大量的查询较小的话,这个很好,就缓存中就搞定了
b、增加read_rnd_buffer_size大小,可以一次性多读到内存中
c、列的长度尽量小些
d、改变tmpdir,使其指向多个物理盘(不是分区)的目录,这将机会循环使用做为临时文件区
原文:IT虾米网
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/3947.html
相关文章
- MySQL 数据库定时自动备份详解数据库
- XAMPP操作指南:简单管理MySQL数据库(xampp管理mysql)
- MySQL分布式数据库搭建指南(mysql分布式数据库搭建)
- MySQL:定义字符串的精彩之处(mysql定义字符串)
- 深入浅出学习 MySQL(学习mysql)
- 配置文件MySQL配置文件.cnf:妙用不断(mysql.cnf)
- MySQL结果判断:判断你的行动。(mysql结果判断)
- 查看MySQL数据库的密码方法(查看mysql的密码)
- 利用MySQL外键与主键构建强大的数据库(mysql外键与主键)
- MySQL数据库批量更新技巧分享(mysql数据库批量更新)
- 体验MySQL,福利免费(免费数据库mysql)
- 使用yum快速安装MySQL数据库(mysql安装yum)
- MySQL数据库编码设置方法(mysql如何设置编码)
- MySQL实现跨数据库自动复制(mysql 跨数据库复制)
- 如何快速启动MySQL数据库(如何启动mysql数据库)
- MySQL中OR条件的使用方法(mysql中or条件)
- MySQL连接两个表的ID实现数据关联和查询(mysql两表id连接)
- MySQL一句语句,操作数据库轻松不烦(mysql 一条语句)
- MySQL如何实现一列多值存储(mysql一列多值)
- 顺利完成MySQL下载,迎接更稳定的数据库应用体验(mysql下载成功后)
- MySQL 应用下载快捷高效可靠的数据库解决方案(mysql下载app)
- 解决方法MySQL无法创建存储过程的问题(mysql不能建存储过程)
- MySQL中非主键自增的使用方法(mysql不是主键的自增)