MySQL SQL Tuning:深入理解Order By

标签: mysql sql tuning | 发表时间:2013-10-01 22:49 | 作者:linwaterbin
出处:http://blog.csdn.net
在MySQL中ORDER BY按先后顺序有2种实现方式,先走索引无排序,如果不行,则用FILESORT
走索引无排序需要满足2个条件:
①排序字段和执行计划中所利用INDEX的索引键(或前面几个索引键)完全一致
②表访问方式为index、ref或range [注释:explain输出中的Type可看出]
最快的排序就是不去排序,这也证明,索引的另一个非常重要的优势:
索引能够降低排序成本,而排序涉及到的资源有CPU和内存,也就是
索引不仅能降低IO开销,加速查询,也能极大降低CPU开销!

如果无法利用索引完成排序操作,则将走filesort,即使完全在内存中排序不需要磁盘文件亦如是
对于filesort有2种算法:
① two-pass
  
   原理:
   先读取行指针和排序字段,进行排序,而后依据排序结果再去读取所需要的数据
   优点:
   排序的数据量较小,完全可以在内存中完成
   缺点:
   第二次读取时,会发生大量的随机IO,太昂贵

② single-pass 
   
   原理:
   一次性把sql中涉及到的字段全部读出,然后依据排序字段排序,最后直接返回排序结果
   优点:
   只需一次顺序IO,无须任何随机IO,大大降低IO开销
   缺点:
   内存容不下那么多的数据,可能会先放在磁盘上,对大数据却分,单个小块排序
   排完放回磁盘,待所有单块排完,最后进行结果集merge,再返回排序结果
   
当查询所需列和排序列的总和大于max_length_for_sort_data时或者所需列是BLOB/TEXT,则选择two-pass
single-pass总体而言表现较佳,如果希望使用,则不妨把max_length_for_sort_data加大 
这里需要注意,计算所需列和排序列长度时,都是按最大长度给定,比如varchar,utf-8
所以,有时候甚至我们会看到排序消耗的临时存储空间比磁盘上原表要大多倍啊
另外,在Join时,order by所有列都来自关联的第一个表时,Extra有using filesort
除此之外的一切Join情况,Extra会有using temporary,using filesort 
因为MySQL会先把所有被关联的数据读到临时表,再filesort

如果别无选择只能filesort,那么如何加快order by?
①加大sort_buffer_size
②加大read_rnd_buffer_size【注释:如果是single-pass可以不必考虑】
③只选择必要的列,只为列选择适合的数据类型
④加大tmpdir



参考文章:
① http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html
② http://isky000.com/database/mysql_order_by_implement
③ 高性能MySQL(3)版:P220-222,P368-369


By 迦叶
2013-10-1
Good Luck
作者:linwaterbin 发表于2013-10-1 22:49:13 原文链接
阅读:120 评论:0 查看评论

相关 [mysql sql tuning] 推荐:

MySQL SQL Tuning:深入理解Order By

- - CSDN博客数据库推荐文章
在MySQL中ORDER BY按先后顺序有2种实现方式,先走索引无排序,如果不行,则用FILESORT. 走索引无排序需要满足2个条件:. ①排序字段和执行计划中所利用INDEX的索引键(或前面几个索引键)完全一致. ②表访问方式为index、ref或range [注释:explain输出中的Type可看出].

使用ORACLE SQL Tuning advisor快速优化低效的SQL语句

- - CSDN博客数据库推荐文章
ORACLE10G以后版本的SQL Tuning advisor可以从以下四个方面给出优化方案.   (1)为统计信息丢失或失效的对象收集统计信息.   (2)考虑优化器的任何数据偏差、复杂谓词或失效的统计信息.   (3)重新构建 SQL 以优化性能.   (4)提出新索引建议. 1、为SQL_id创建一个STA(SQL Tuning advisor) 分析任务(使用SYS用户执行).

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

ORACLE SQL TUNING各种技巧及复杂实例

- - 数据库 - ITeye博客
ORACLE的优化器共有3种:. CHOOSE (选择性). 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息,以保证数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关.

mysql记录耗时的sql

- - 数据库 - ITeye博客
mysql记录耗时的sql. mysql可以把耗时的sql或未使用索引的sql都记录在slow log里,供优化分析使用. 1.mysql慢查询日志启用:. mysql慢查询日志对于跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句,那如何打开mysql的慢查询日志记录呢. 这说明slow log功能没有启用,要启用需要修改mysql的配置文件,在配置文件"[mysqld]"里添加如下俩参数:.

全国省市县 SQL (mysql)

- - 学习笔记
此城市区域内容可以配合ip.taobao.com api 使用.. 有些字段没有添加索引,用的时候根据实际情况做修改. 使用mysql来实现lbs(地理位置服务)功能. 10款对开发者有帮助的Android应用. DeployPHP 系列第 1 部分:优化 PHP 和 Oracle. 在 Oracle 和 PHP 中使用 LOB.

mysql的sql优化(前奏)

- - 数据库 - ITeye博客
要优化mysql首先要知道什么地方需要优化,然后才能针对具体问题进行优化. 什么分库分表,建立索引....摆脱不要那么官方好吗. 1.学会和培养使用mysql的查看命令的使用习惯. 什么你忘记如何创建表的语句了. SHOW CREATE TABLE Name: 'SHOW CREATE TABLE' Description: Syntax: SHOW CREATE TABLE tbl_name Shows the CREATE TABLE statement that creates the named table.

【ActiveMQ Tuning】Prefetch Limit

- - 博客园_首页
   摘要:ActiveMQ优化 客户端优化 预取限制. 原文: http://fusesource.com/docs/broker/5.4/tuning/GenTuning-Consumer-Prefetch.html. Overview:图列4.1阐明了Broker在等待之前发送给客户端消息的反馈的行为.

【ActiveMQ Tuning】Serializing to Disk

- - 博客园_首页
     翻译自: http://fusesource.com/docs/broker/5.4/tuning/PersTuning-SerialToDisk.html.      KahaDB message store:KahaDB 是ActiveMQ Broker 为了高性能而推荐使用的消息存储机制.

Mysql查看sql是否走事务

- - CSDN博客数据库推荐文章
可以看到监控日志是否开启的选项(off是关闭)on是开启. 接下来就可以直接进入shell中根据sql特征来查看我们sql会话信息. 126002192 Query select pk fromT_GANTT_CHART where pk =47 for update

//忽略其他的数据了.