数据库优化<六>SQL优化之SELECT优化 ——filesort

原创 2013年12月06日 00:33:30

在执行计划中,可能经常看到有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,使其指向多个物理盘(不是分区)的目录,这将机会循环使用做为临时文件区

             



MySQL系列—如何优化你的 SQL SELECT 语句性能

SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循帕累托原则。20%的努力很可能会给你带来80%的性能提升,而为了获得另外20%的性能提升你可能需要花费80%的时间。除非你在金星工作,...
  • u012758088
  • u012758088
  • 2016年07月31日 18:03
  • 1066

MySql中explain的时候出现using filesort,优化之(转)

原文地址:http://blog.csdn.net/imzoer/article/details/8485680 在使用order by关键字的时候,如果待排序的内容不能由所使用的索引直接完成...
  • lijingkuan
  • lijingkuan
  • 2017年02月26日 13:56
  • 316

数据库优化<七>SQL优化之SELECT优化 ——group by 优化

在数据库查询中,group by语句经常使用,而这个语句的使用是最耗性能的,按常理, 我们生活中要这样做也很麻烦,有两种情形:         1、有索引的情况         2、无索引的情况 对于...
  • Hello_ok_google
  • Hello_ok_google
  • 2013年12月06日 15:41
  • 6791

优化 SQL SELECT 语句性能的 6 个简单技巧

本文由 伯乐在线 - xianhu 翻译,进林 校稿。未经许可,禁止转载! 英文出处:msiman.ga。欢迎加入翻译小组。 SELECT语句的性能调优有时是一个非常耗时的任务,在我看来它遵循...
  • werm520
  • werm520
  • 2015年05月04日 16:09
  • 10516

优化1——数据库优化面试题

1.实践中如何优化mysql 1) SQL语句及索引的优化 2) 数据库表结构的优化 3) 系统配置的优化 4) 硬件优化   2.索引的底层实现原理和优化 在 DB2 数据库中索引...
  • u010796790
  • u010796790
  • 2016年08月12日 20:01
  • 11492

数据库优化面试题

优化1——数据库优化面试题 1.实践中如何优化MySQL 1) SQL语句及索引的优化 2) 数据库表结构的优化...
  • a724888
  • a724888
  • 2017年03月08日 19:32
  • 8892

SQL数据库优化方面的经验

1、用PreparedStatement一般来说比用Statement性能高,一个sql发给服务器去执行,涉及步骤:语法检查,语义分析,编译,缓存。 2、有外键约束会影响插入和删除性能,如果程序能够保...
  • daodaipsrensheng
  • daodaipsrensheng
  • 2016年08月29日 10:42
  • 5126

数据库性能优化之SQL语句优化

一、问题的提出在应用系统开发初期,由于开发数据库数据比较少,对于查询SQL语句,复杂视图的的编写等体会不出SQL语句各种写法的性能优劣,但是如果将应用系统提交实际应用后,随着数据库中数据的增加,系统的...
  • u011225629
  • u011225629
  • 2016年01月10日 15:40
  • 49969

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找资料、修改、排版,希望大家阅读之后,感觉...
  • zhushuai1221
  • zhushuai1221
  • 2016年06月23日 09:43
  • 23110

数据库select in语句怎么优化

SELECT COUNT(*) FROM receivefax t WHERE t.fax_number IN (SELECT td.telephone FROM tmp_duanxin_ljx_20...
  • lifushan123
  • lifushan123
  • 2015年05月13日 00:49
  • 318
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据库优化<六>SQL优化之SELECT优化 ——filesort
举报原因:
原因补充:

(最多只允许输入30个字)