前言

工作過(guò)程中,各種業(yè)務(wù)需求在訪問(wèn)數(shù)據(jù)庫(kù)的時(shí)候要求有order by排序。有時(shí)候不必要的或者不合理的排序操作很可能導(dǎo)致數(shù)據(jù)庫(kù)系統(tǒng)崩潰。如何處理好order by排序呢？本文從原理以及優(yōu)化層面介紹 order by 。

一 MySQL中order by的原理

  1 利用索引的有序性獲取有序數(shù)據(jù)

  當(dāng)查詢語(yǔ)句的 order BY 條件和查詢的執(zhí)行計(jì)劃中所利用的 Index 的索引鍵（或前面幾個(gè)索引鍵）完全一致,且索引訪問(wèn)方式為 rang,ref 或者 index 的時(shí)候,MySQL 可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。這種方式的 order BY 基本上可以說(shuō)是最優(yōu)的排序方式了,因?yàn)?MySQL 不需要進(jìn)行實(shí)際的排序操作。需要注意的是使用索引排序也有很多限制。這個(gè)在后文中中解釋。

  2 利用內(nèi)存/磁盤文件排序獲取結(jié)果

  由于沒(méi)有可以利用的有序索引取得有序的數(shù)據(jù),MySQL需要通過(guò)相應(yīng)的排序算法,將取得的數(shù)據(jù)在sort_buffer_size系統(tǒng)變量所設(shè)置大小的排序區(qū)進(jìn)行排序,這個(gè)排序區(qū)是每個(gè)Thread 獨(dú)享的,所以說(shuō)可能在同一時(shí)刻在 MySQL 中可能存在多個(gè) sort buffer 內(nèi)存區(qū)域。
  在MySQL中filesort 的實(shí)現(xiàn)算法有兩種:

  1) 雙路排序:是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進(jìn)行排序。
  2) 單路排序:是一次性取出滿足條件行的所有字段，然后在sort buffer中進(jìn)行排序。

  在 MySQL4.1 版本之前只有第一種排序算法,第二種算法是從MySQL4.1開始的改進(jìn)算法,主要目的是為了減少第一次算法中需要兩次訪問(wèn)表數(shù)據(jù)的IO操作,將兩次變成了一次,但相應(yīng)也會(huì)耗用更多的 sort buffer 空間。典型的以空間換時(shí)間的優(yōu)化方式。當(dāng)然,MySQL4.1開始的以后所有版本同時(shí)也支持第一種算法,MySQL主要通過(guò)比較系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query語(yǔ)句所取出的字段類型大小總和來(lái)判定需要使用哪一種排序算法。如果max_length_for_sort_data更大,則使用第二種優(yōu)化后的算法,反之使用第一種算法。所以如果希望 order BY 操作的效率盡可能的高,需要注意max_length_for_sort_data參數(shù)的設(shè)置。

二 優(yōu)化order by

當(dāng)無(wú)法避免排序操作時(shí),又該如何來(lái)優(yōu)化呢？很顯然,優(yōu)先選擇第一種using index 的排序方式，在第一種方式無(wú)法滿足的情況下，盡可能讓 MySQL 選擇使用第二種單路算法來(lái)進(jìn)行排序。這樣可以減少大量的隨機(jī)IO操作,很大幅度地提高排序工作的效率。

1 加大 max_length_for_sort_data 參數(shù)的設(shè)置

  在 MySQL 中,決定使用老式排序算法還是改進(jìn)版排序算法是通過(guò)參數(shù) max_length_for_ sort_data 來(lái)決定的。當(dāng)所有返回字段的最大長(zhǎng)度小于這個(gè)參數(shù)值時(shí),MySQL 就會(huì)選擇改進(jìn)后的排序算法,反之,則選擇老式的算法。所以,如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段,就可以加大這個(gè)參數(shù)的值來(lái)讓 MySQL 選擇使用改進(jìn)版的排序算法。

2 去掉不必要的返回字段

  當(dāng)內(nèi)存不是很充裕時(shí),不能簡(jiǎn)單地通過(guò)強(qiáng)行加大上面的參數(shù)來(lái)強(qiáng)迫 MySQL 去使用改進(jìn)版的排序算法,否則可能會(huì)造成 MySQL 不得不將數(shù)據(jù)分成很多段,然后進(jìn)行排序,這樣可能會(huì)得不償失。此時(shí)就須要去掉不必要的返回字段,讓返回結(jié)果長(zhǎng)度適應(yīng) max_length_for_sort_data 參數(shù)的限制。

3 增大 sort_buffer_size 參數(shù)設(shè)置

  這個(gè)值如果過(guò)小的話,再加上你一次返回的條數(shù)過(guò)多,那么很可能就會(huì)分很多次進(jìn)行排序,然后最后將每次的排序結(jié)果再串聯(lián)起來(lái),這樣就會(huì)更慢,增大 sort_buffer_size 并不是為了讓 MySQL選擇改進(jìn)版的排序算法,而是為了讓MySQL盡量減少在排序過(guò)程中對(duì)須要排序的數(shù)據(jù)進(jìn)行分段,因?yàn)榉侄螘?huì)造成 MySQL 不得不使用臨時(shí)表來(lái)進(jìn)行交換排序。

但是這個(gè)值不是越大越好：

1 Sort_Buffer_Size 是一個(gè)connection級(jí)參數(shù),在每個(gè)connection第一次需要使用這個(gè)buffer的時(shí)候,一次性分配設(shè)置的內(nèi)存。
2 Sort_Buffer_Size 并不是越大越好,由于是connection級(jí)的參數(shù),過(guò)大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)內(nèi)存資源。
3 據(jù)說(shuō)Sort_Buffer_Size 超過(guò)2M的時(shí)候,就會(huì)使用mmap() 而不是 malloc() 來(lái)進(jìn)行內(nèi)存分配,導(dǎo)致效率降低。

最新評(píng)論