MySQL中distinct語(yǔ)句的基本原理及其與group by的比較

更新時(shí)間：2016年01月13日 15:06:14 作者：海底蒼鷹

這篇文章主要介紹了MySQL中distinct語(yǔ)句的基本原理及其與group by的比較,一般情況下來(lái)說(shuō)group by和distinct的實(shí)現(xiàn)原理相近且性能稍好,需要的朋友可以參考下

DISTINCT 實(shí)際上和 GROUP BY 操作的實(shí)現(xiàn)非常相似，只不過(guò)是在 GROUP BY 之后的每組中只取出一條記錄而已。所以，DISTINCT 的實(shí)現(xiàn)和 GROUP BY 的實(shí)現(xiàn)也基本差不多，沒有太大的區(qū)別。同樣可以通過(guò)松散索引掃描或者是緊湊索引掃描來(lái)實(shí)現(xiàn)，當(dāng)然，在無(wú)法僅僅使用索引即能完成 DISTINCT 的時(shí)候，MySQL 只能通過(guò)臨時(shí)表來(lái)完成。但是，和 GROUP BY 有一點(diǎn)差別的是，DISTINCT 并不需要進(jìn)行排序。也就是說(shuō)，在僅僅只是 DISTINCT 操作的 Query 如果無(wú)法僅僅利用索引完成操作的時(shí)候，MySQL 會(huì)利用臨時(shí)表來(lái)做一次數(shù)據(jù)的“緩存”，但是不會(huì)對(duì)臨時(shí)表中的數(shù)據(jù)進(jìn)行 filesort 操作。當(dāng)然，如果我們?cè)谶M(jìn)行 DISTINCT 的時(shí)候還使用了 GROUP BY 并進(jìn)行了分組，并使用了類似于 MAX 之類的聚合函數(shù)操作，就無(wú)法避免 filesort 了。

下面我們就通過(guò)幾個(gè)簡(jiǎn)單的 Query 示例來(lái)展示一下 DISTINCT 的實(shí)現(xiàn)。

1.首先看看通過(guò)松散索引掃描完成 DISTINCT 的操作：

sky@localhost : example 11:03:41> EXPLAIN SELECT DISTINCT group_id 
  -> FROM group_messageG

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: NULL
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 10
    Extra: Using index for group-by
1 row in set (0.00 sec)

我們可以很清晰的看到，執(zhí)行計(jì)劃中的 Extra 信息為“Using index for group-by”，這代表什么意思？為什么我沒有進(jìn)行 GROUP BY 操作的時(shí)候，執(zhí)行計(jì)劃中會(huì)告訴我這里通過(guò)索引進(jìn)行了 GROUP BY 呢？其實(shí)這就是于 DISTINCT 的實(shí)現(xiàn)原理相關(guān)的，在實(shí)現(xiàn) DISTINCT的過(guò)程中，同樣也是需要分組的，然后再?gòu)拿拷M數(shù)據(jù)中取出一條返回給客戶端。而這里的 Extra 信息就告訴我們，MySQL 利用松散索引掃描就完成了整個(gè)操作。當(dāng)然，如果 MySQL Query Optimizer 要是能夠做的再人性化一點(diǎn)將這里的信息換成“Using index for distinct”那就更好更容易讓人理解了，呵呵。

2.我們?cè)賮?lái)看看通過(guò)緊湊索引掃描的示例：

sky@localhost : example 11:03:53> EXPLAIN SELECT DISTINCT user_id 
  -> FROM group_message
  -> WHERE group_id = 2G

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: ref
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: const
     rows: 4
    Extra: Using WHERE; Using index
1 row in set (0.00 sec)

這里的顯示和通過(guò)緊湊索引掃描實(shí)現(xiàn) GROUP BY 也完全一樣。實(shí)際上，這個(gè) Query 的實(shí)現(xiàn)過(guò)程中，MySQL 會(huì)讓存儲(chǔ)引擎掃描 group_id = 2 的所有索引鍵，得出所有的 user_id，然后利用索引的已排序特性，每更換一個(gè) user_id 的索引鍵值的時(shí)候保留一條信息，即可在掃描完所有 gruop_id = 2 的索引鍵的時(shí)候完成整個(gè) DISTINCT 操作。

3.下面我們?cè)诳纯礋o(wú)法單獨(dú)使用索引即可完成 DISTINCT 的時(shí)候會(huì)是怎樣：

sky@localhost : example 11:04:40> EXPLAIN SELECT DISTINCT user_id 
  -> FROM group_message
  -> WHERE group_id > 1 AND group_id < 10G

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 32
    Extra: Using WHERE; Using index; Using temporary
1 row in set (0.00 sec)

當(dāng) MySQL 無(wú)法僅僅依賴索引即可完成 DISTINCT 操作的時(shí)候，就不得不使用臨時(shí)表來(lái)進(jìn)行相應(yīng)的操作了。但是我們可以看到，在 MySQL 利用臨時(shí)表來(lái)完成 DISTINCT 的時(shí)候，和處理 GROUP BY 有一點(diǎn)區(qū)別，就是少了 filesort。實(shí)際上，在 MySQL 的分組算法中，并不一定非要排序才能完成分組操作的，這一點(diǎn)在上面的 GROUP BY 優(yōu)化小技巧中我已經(jīng)提到過(guò)了。實(shí)際上這里 MySQL 正是在沒有排序的情況下實(shí)現(xiàn)分組最后完成 DISTINCT 操作的，所以少了 filesort 這個(gè)排序操作。

4.最后再和 GROUP BY 結(jié)合試試看：

sky@localhost : example 11:05:06> EXPLAIN SELECT DISTINCT max(user_id) 
  -> FROM group_message
  -> WHERE group_id > 1 AND group_id < 10
  -> GROUP BY group_idG

*************************** 1. row ***************************
      id: 1
 SELECT_type: SIMPLE
    table: group_message
     type: range
possible_keys: idx_gid_uid_gc
     key: idx_gid_uid_gc
   key_len: 4
     ref: NULL
     rows: 32
    Extra: Using WHERE; Using index; Using temporary; Using filesort
1 row in set (0.00 sec)

最后我們?cè)倏匆幌逻@個(gè)和 GROUP BY 一起使用帶有聚合函數(shù)的示例，和上面第三個(gè)示例相比，可以看到已經(jīng)多了 filesort 排序操作了，正是因?yàn)槲覀兪褂昧?MAX 函數(shù)的緣故。要取得分組后的 MAX 值，又無(wú)法使用索引完成操作，只能通過(guò)排序才行了。

mysql distinct和group by誰(shuí)更好
1，測(cè)試前的準(zhǔn)備

//準(zhǔn)備一張測(cè)試表 
mysql> CREATE TABLE `test_test` ( 
 ->  `id` int(11) NOT NULL auto_increment, 
 ->  `num` int(11) NOT NULL default '0', 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Query OK, 0 rows affected (0.05 sec)

mysql> delimiter || //改變mysql命令結(jié)束符為|| 
 
//建個(gè)儲(chǔ)存過(guò)程向表中插入10W條數(shù)據(jù) 
mysql> create procedure p_test(pa int(11)) 
 -> begin 
 -> 
 -> declare max_num int(11) default 100000; 
 -> declare i int default 0; 
 -> declare rand_num int; 
 -> 
 -> select count(id) into max_num from test_test; 
 -> 
 -> while i < pa do 
 ->     if max_num < 100000 then 
 ->         select cast(rand()*100 as unsigned) into rand_num; 
 ->         insert into test_test(num)values(rand_num); 
 ->     end if; 
 ->     set i = i +1; 
 -> end while; 
 -> end||

Query OK, 0 rows affected (0.00 sec)

mysql> call p_test(100000)||

Query OK, 1 row affected (5.66 sec)

mysql> delimiter ;//改變mysql命令結(jié)束符為; 
mysql> select count(id) from test_test; //數(shù)據(jù)都進(jìn)去了

+-----------+ 
| count(id) | 
+-----------+ 
|  100000 | 
+-----------+ 
1 row in set (0.00 sec)

mysql> show variables like "%pro%";  //查看一下，記錄執(zhí)行的profiling是不是開啟動(dòng)了，默認(rèn)是不開啟的

+---------------------------+-------+ 
| Variable_name       | Value | 
+---------------------------+-------+ 
| profiling         | OFF  | 
| profiling_history_size  | 15  | 
| protocol_version     | 10  | 
| slave_compressed_protocol | OFF  | 
+---------------------------+-------+ 
4 rows in set (0.00 sec)

mysql> set profiling=1;      //開啟

Query OK, 0 rows affected (0.00 sec)

2，測(cè)試

//做了4組測(cè)試 
mysql> select distinct(num) from test_test; 
mysql> select num from test_test group by num; 
 
mysql> show profiles;  //查看結(jié)果

+----------+------------+-------------------------------------------+ 
| Query_ID | Duration  | Query                   | 
+----------+------------+-------------------------------------------+ 
|    1 | 0.07298225 | select distinct(num) from test_test    | 
|    2 | 0.07319975 | select num from test_test group by num  | 
|    3 | 0.07313525 | select num from test_test group by num  | 
|    4 | 0.07317725 | select distinct(num) from test_test    | 
|    5 | 0.07275200 | select distinct(num) from test_test    | 
|    6 | 0.07298600 | select num from test_test group by num  | 
|    7 | 0.07500700 | select num from test_test group by num  | 
|    8 | 0.07331325 | select distinct(num) from test_test    | 
|    9 | 0.57831575 | create index num_index on test_test (num) | //在這兒的時(shí)候，我加了索引 
|    10 | 0.00243550 | select distinct(num) from test_test    | 
|    11 | 0.00121975 | select num from test_test group by num  | 
|    12 | 0.00116550 | select distinct(num) from test_test    | 
|    13 | 0.00107650 | select num from test_test group by num  | 
+----------+------------+-------------------------------------------+ 
13 rows in set (0.00 sec)

上面的1-8是4組數(shù)據(jù)，并且是沒有加索引的，從中我們可以看出，distinct比group by 會(huì)好一點(diǎn)點(diǎn)
10-13是2組數(shù)據(jù)，是加了索引以后的，從中我們可以看出，group by 比distinct 會(huì)好一點(diǎn)點(diǎn)
一般情況，數(shù)據(jù)量比較大的表，關(guān)聯(lián)字段都會(huì)加索引的，，并且加索引后檢索時(shí)間只有以前的六分之一左右。

您可能感興趣的文章: