乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql實(shí)例MySQL中distinct語句的基本原理及其與group by的比較

作者：VEPHP   時(shí)間 2017-09-24

《Mysql實(shí)例MySQL中distinct語句的基本原理及其與group by的比較》要點(diǎn)：
本文介紹了Mysql實(shí)例MySQL中distinct語句的基本原理及其與group by的比較，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

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

下面我們就通過幾個(gè)簡(jiǎn)單的 Query 示例來展示一下 DISTINCT 的實(shí)現(xiàn).MYSQL必讀

1.首先看看通過松散索引掃描完成 DISTINCT 的操作：MYSQL必讀

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ì)告訴我這里通過索引進(jìn)行了 GROUP BY 呢?其實(shí)這就是于 DISTINCT 的實(shí)現(xiàn)原理相關(guān)的,在實(shí)現(xiàn) DISTINCT的過程中,同樣也是需要分組的,然后再?gòu)拿拷M數(shù)據(jù)中取出一條返回給客戶端.而這里的 Extra 信息就告訴我們,MySQL 利用松散索引掃描就完成了整個(gè)操作.當(dāng)然,如果 MySQL Query Optimizer 要是能夠做的再人性化一點(diǎn)將這里的信息換成“Using index for distinct”那就更好更容易讓人理解了,呵呵.MYSQL必讀

2.我們?cè)賮砜纯赐ㄟ^緊湊索引掃描的示例：MYSQL必讀

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)

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

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

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

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

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 值,又無法使用索引完成操作,只能通過排序才行了.MYSQL必讀

mysql distinct和group by誰更好
1,測(cè)試前的準(zhǔn)備MYSQL必讀

//準(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 ;

??
MYSQL必讀

Query OK, 0 rows affected (0.05 sec)

?
MYSQL必讀

mysql> delimiter || //改變mysql命令結(jié)束符為|| 
 
//建個(gè)儲(chǔ)存過程向表中插入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必讀

mysql> call p_test(100000)|| 

Query OK, 1 row affected (5.66 sec)

?
MYSQL必讀

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必讀

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必讀

mysql> set profiling=1;      //開啟 

Query OK, 0 rows affected (0.00 sec)

2,測(cè)試MYSQL必讀

//做了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í)間只有以前的六分之一左右.MYSQL必讀

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/3310.html


    標(biāo)簽：