Mysql优化_ORDER BY的优化讲解(单路排序和双路排序)

ORDER BY 子句尽量使用Index方式排序,避免使用FileSort方式排序,尽可能在索引列上外城排序操作,遵照索引键的最佳左前缀。如果不在索引列上,FileSort有两种算法,Mysql就要启动双路排序和单路排序。

什么是双路排序和单路排序?

  • 双路排序:Mysql4.1之前是使用双路排序,字面的意思就是两次扫描磁盘,最终得到数据,读取行指针和ORDER BY列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段,在buffer进行排序,再从磁盘读取其他字段。文件的磁盘IO非常耗时的。

  • 单路排序:在Mysql4.1之后,出现了第二种算法,就是单路排序。从磁盘读取查询所需要的所有列,按照ORDER BY在buffer对它进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在了内存里。

但是如果单路排序算法一次拿不出数据,那么就还比双路排序更消耗IO,效率更慢!

什么情况下会导致单路排序失效呢?

  • 在sort_buffer中,单路排序要比双路排序占很多空间,因为单路排序把所有的字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能读取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再次排序...从而多次I/O。偷鸡不成蚀把米。
    比如:内存就是2M,一次查1000条数据刚好,也就是最大1000条数据,但是一次要查5000条,那么不够了,照这样需要查5次刚好,如果把2M改为10M,那么就刚好了

提高ORDER BY速度的技巧

1:ORDER BY时不要使用SELECT *,只查需要的字段。

  • 当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法---单路排序,否则用老算法---多路排序。假设只需要查10个字段,但是SELECT *会查80个字段,那么就容易把sort_buffer缓冲区用满。
  • 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size大小。

2:增大sort_buffer_size参数大小
不管用哪种算法,提高这个参数都会提高效率。当然要根据系统能力去提高,因为这个参数是针对每个进程的。

3:增大max_length_for_sort_data参数大小
提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。

为排序使用索引

MYSQL两种排序方式:文件排序和扫描有序索引排序
Mysql能为排序和查询使用相同的索引。也就是创建索引先把数据排序了,查询的时候再利用索引,一举两得。

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC</pre>

如果where使用缩印的最左前缀定义为常量,则order by 能使用索引

WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b,c</pre>

不适用索引进行排序

ORDER BY a ASC,b DESC, c DESC //排序不一致
WHERE g = const ORDER BY b,c //丢失a索引
WHERE a = const ORDER BY c //丢失b索引
WHERE a = const ORDER BY a,d //d不是索引的一部分
WHERE a in (....) ORDER BY b,c //对于排序来说,多个相等条件也是范围查询

GROUP BY的优化

  • GROUP BY实质上是先排序后进行分组,遵照索引的最佳左前缀。
    当无法使用索隐裂,考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。
    WHERE 高于 HAVING,能写在WHERE限定的条件就不要去HAVING限定了。
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 216,544评论 6 501
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 92,430评论 3 392
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 162,764评论 0 353
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 58,193评论 1 292
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 67,216评论 6 388
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 51,182评论 1 299
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 40,063评论 3 418
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,917评论 0 274
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 45,329评论 1 310
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,543评论 2 332
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,722评论 1 348
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 35,425评论 5 343
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 41,019评论 3 326
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,671评论 0 22
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,825评论 1 269
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,729评论 2 368
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,614评论 2 353