MySQL 查詢優化、【order by】及【group by】關鍵字優化

文章目錄

• • • • 原理
      • • order by關鍵字優化
        • • fileSort排序原理
          • 提高order by速度的方式
        • order by 案例總結
        • • 使用索引進行排序的例子
          • 不能使用索引進行排序
        • 總結
      • group by關鍵字優化

優化原則：小表驅動大表，即小的資料集驅動大的資料集

原理

select * from A where id in (select id from B)等價于

for select id from B {

for select * from A where A.id=B.id

}

當B表的資料集必須小于A表的資料集時，用in優于exists

select * from A where exists (select 1 from B where B.id=A.id)

等價于

for select * from A

for select * from B where B.id=A.id

當A表的資料集小于B表的資料集時，用exists優于in

order by關鍵字優化

order by字句盡量使用index方式排序，避免使用fileSort方式排序。

MySQL支援兩種方式的排序，fileSort和index，index效率高。它指MySQL掃描索引本身完成排序。fileSort方法效率較低。

order by滿足如下兩種情況，會使用index方式排序

• order by 語句使用索引最左前列
• 使用where字句與order by子句條件列組合滿足索引最左前列

盡可能在索引列上完成排序操作，遵照索引的最佳左字首

fileSort排序原理

如果不在索引列上排序，fileSort有兩種算法：雙路排序和單路排序。

雙路排序：MySQL4.1之前是使用雙路排序，字面意思是兩次掃描磁盤，最終得到資料。讀取行指針和order by列，對他們進行排序，然後掃描已經排序好的清單，按照清單中的值重新從清單中讀取對應的資料傳輸。

簡單的說就是從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段。

單路排序：從磁盤讀取查詢需要的所有列，按照order by在buffer對它們進行排序，然後掃描排序後的清單進行輸出，它的效率更快一些，避免了第二次讀取資料，并且把随機IO變成順序IO，但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。

單路排序效率是比雙路排序好，但是也有問題：

在sort_buffer中，單路排序比雙路排序要多占用很多空間，因為單路排序是把所有字段都取出，是以有可能取出的資料總大小超出了sort_buffer的容量，導緻每次隻能取sort_buffer容量大小的資料，進行排序(建立tmp檔案，多路合并)，排完再取再排，進而會産生多次IO。

提高order by速度的方式

order by時select * 是一個大忌，隻查詢需要的字段，這點非常重要。在這裡影響的是

• 當查詢的字段大小之和小于max_length_for_sort_data而且排序字段不是TEXT/BLOB類型時，會使用改進後的單路排序算法，否則使用多路排序算法
• 兩種算法的資料都有可能超過sort_buffer的容量，超過之後會建立tmp檔案進行合并排序，導緻多次IO。是以要提高sort_buffer_size。

• 嘗試提高sort_buffer_size

  不管哪種算法，提高這個參數都會提高效率。當然，要根據系統的能力去提高，因為這個參數是針對每個程序的。

order by 案例總結

使用索引進行排序的例子

加入一個表索引為 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

如果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

不能使用索引進行排序

• order by a asc,b desc,c desc 排序不一緻
• where d=const order by b,c 不符合最左字首規則
• where a=const order by c 丢失b索引
• where a=const order by a,d d不是索引的一部分
• where a in (…) order by b,c

總結

是以 也就是說order by排序列要符合最左字首規則或者order by排序列和where條件列 能夠構成最左字首規則也行。

group by關鍵字優化

group by實質是先排序後進行分組，遵照索引鍵的最佳左字首，是以跟order by的優化很類似