MySQL如何優化DISTINCT

在許多情況下結合order by的distinct需要一個臨時表。

請注意因為distinct可能使用group by，必須清楚mysql教程如何使用所選定列的一部分的order by或having子句中的列。mysql 擴展了 group by的用途，因此你可以使用select 列表中不出現在group by語句中的列或運算。這代表 "對該組的任何可能值 "。你可以通過避免排序和對不必要項分組的辦法得到它更好的性能。例如，在下列問詢中，你無須對customer.name 進行分組：

mysql> select order.custid, customer.name, max(payments)

    ->        from order,customer

    ->        where order.custid = customer.custid

    ->        group by order.custid;

在標准sql中, 你必須將 customer.name添加到 group by子句中。在mysql中, 假如你不在ansi模式中運行，則這個名字就是多余的。

假如你從 group by 部分省略的列在該組中不是唯一的，那麼不要使用這個功能! 你會得到非預測性結果。

在有些情況下,你可以使用min()和max() 獲取一個特殊的列值，即使他不是唯一的。下面給出了來自包含排序列中最小值的列中的值：

substr(min(concat(rpad(sort,6,' '),column)),7)

see 3.6.4節，"擁有某個字段的組間最大值的行".

注意，假如你正在嘗試遵循標准 sql, 你不能使用group by或 order by子句中的表達式。你可以通過使用表達式的別名繞過這一限制： 

mysql> select id,floor(value/100) as val

    -> from tbl_name

    -> group by id, val order by val;

然而, mysql允許你使用group by 及 order by 子句中的表達式。例如：

mysql> select id, floor(value/100) from tbl_name order by rand();
，"具有隱含字段的group by"。

在大多數情況下，distinct子句可以視為group by的特殊情況。例如，下面的兩個查詢是等效的：

select distinct c1, c2, c3 from t1 where c1 > const; select c1, c2, c3 from t1 where c1 > const group by c1, c2, c3;由於這個等效性，適用於group by查詢的優化也適用於有distinct子句的查詢。這樣，關於distinct查詢的優化的更詳細的情況，

，"mysql如何優化group by"。
滿足group by子句的最一般的方法是掃描整個表並創建一個新的臨時表，表中每個組的所有行應為連續的，然後使用該臨時表來找到組並應用累積函數(如果有)。在某些情況中，mysql能夠做得更好，通過索引訪問而不用創建臨時表。

為group by使用索引的最重要的前提條件是 所有group by列引用同一索引的屬性，並且索引按順序保存其關鍵字(例如，這是b-樹索引，而不是hash索引)。是否用索引訪問來代替臨時表的使用還取決於在查詢中使用了哪部分索引、為該部分指定的條件，以及選擇的累積函數。

有兩種方法通過索引訪問執行group by查詢，如下面的章節所描述。在第1個方法中，組合操作結合所有范圍判斷式使用(如果有)。第2個方法首先執行范圍掃描，然後組合結果元組。

7.2.13.1. 松散索引掃描
使用索引時最有效的途徑是直接搜索組域。通過該訪問方法，mysql使用某些關鍵字排序的索引類型(例如，b-樹)的屬性。該屬性允許使用 索引中的查找組而不需要考慮滿足所有where條件的索引中的所有關鍵字。既然該訪問方法只考慮索引中的關鍵字的一小部分，它被稱為松散索引掃描。如果沒有where子句， 松散索引掃描讀取的關鍵字數量與組數量一樣多，可以比所有關鍵字數小得多。如果where子句包含范圍判斷式(關於range聯接類型的討論參見7.2.1節，"explain語法（獲取關於select的信息）")， 松散索引掃描查找滿足范圍條件的每個組的第1個關鍵字，並且再次讀取盡可能最少數量的關鍵字。在下面的條件下是可以的：

·         查詢針對一個單表。

·         group by包括索引的第1個連續部分(如果對於group by，查詢有一個distinct子句，則所有顯式屬性指向索引開頭)。

·         只使用累積函數(如果有)min()和max()，並且它們均指向相同的列。

·         索引的任何其它部分（除了那些來自查詢中引用的group by）必須為常數(也就是說，必須按常量數量來引用它們)，但min()或max() 函數的參數例外。

此類查詢的explain輸出顯示extra列的using indexforgroup-by。

下面的查詢提供該類的幾個例子，假定表t1(c1,c2,c3,c4)有一個索引idx(c1，c2，c3)：

select c1, c2 from t1 group by c1, c2;

select distinct c1, c2 from t1;

select c1, min(c2) from t1 group by c1;

select c1, c2 from t1 where c1 < const group by c1, c2;

select max(c3), min(c3), c1, c2 from t1 where c2 > const group by c1, c2;

select c2 from t1 where c1 < const group by c1, c2;

select c1, c2 from t1 where c3 = const group by c1, c2；

由於上述原因，不能用該快速選擇方法執行下面的查詢：

1.      除了min()或max()，還有其它累積函數，例如：

     select c1, sum(c2) from t1 group by c1;2.      group by子句中的域不引用索引開頭，如下所示：

     select c1,c2 from t1 group by c2, c3;3.      查詢引用了group by部分後面的關鍵字的一部分，並且沒有等於常量的等式，例如：

     select c1,c3 from t1 group by c1, c2；7.2.13.2. 緊湊索引掃描
緊湊式索引掃描可以為索引掃描或一個范圍索引掃描，取決於查詢條件。

如果不滿足松散索引掃描條件，group by查詢仍然可以不用創建臨時表。如果where子句中有范圍條件，該方法只讀取滿足這些條件的關鍵字。否則，進行索引掃描。該方法讀取由where子句定義的每個范圍的所有關鍵字，或沒有范圍條件式掃描整個索引，我們將它定義為緊湊式索引掃描。請注意對於緊湊式索引掃描，只有找到了滿足范圍條件的所有關鍵字後才進行組合操作。

要想讓該方法工作，對於引用group by關鍵字元素的前面、中間關鍵字元素的查詢中的所有列，有一個常量等式條件即足夠了。等式條件中的常量填充了搜索關鍵字中的"差距"，可以形成完整的索引前綴。這些索引前綴可以用於索引查找。如果需要排序group by結果，並且能夠形成索引前綴的搜索關鍵字，mysql還可以避免額外的排序操作，因為使用有順序的索引的前綴進行搜索已經按順序檢索到了所有關鍵字。

上述的第一種方法不適合下面的查詢，但第2種索引訪問方法可以工作(假定我們已經提及了表t1的索引idx)：

·         group by中有一個差距，但已經由條件c2 = 'a'覆蓋。

     select c1，c2，c3 from t1 where c2 = 'a' group by c1，c3;·         group by不以關鍵字的第1個元素開始，但是有一個條件提供該元素的常量：

     select c1，c2，c3 from t1 where c1 = 'a' group by c2，c3;

 

結合limit row_count和distinct後，mysql發現唯一的row_count行後立即停止。

如果不使用查詢中命名的所有表的列，mysql發現第1個匹配後立即停止掃描未使用的表。在下面的情況中，假定t1在t2之前使用(可以用explain檢查)，發現t2中的第1行後，mysql不再(為t1中的任何行)讀t2：

select distinct t1.a from t1, t2 where t1.a=t2.a;