mysql數據庫分組,GROUP BY,查詢實例

2024-07-24 12:36:55

字體：大中小

來源：轉載

供稿：網友

mysql數據庫分組為分三種方式，使用臨時表實現 GROUP BY、緊湊(Tight)索引掃描實現 GROUP BY、松散(Loose)索引掃描實現 GROUP BY,但我常用的是使用了

1.使用松散(Loose)索引掃描實現 GROUP BY

何謂松散索引掃描實現 GROUP BY 呢?實際上就是當 MySQL 完全利用索引掃描來實現 GROUP BY 的時候，并不需要掃描所有滿足條件的索引鍵即可完成操作得出結果。

下面我們通過一個示例來描述松散索引掃描實現 GROUP BY，在示例之前我們需要首先調整一下 group_message 表的索引，將 gmt_create 字段添加到 group_id 和 user_id 字段的索引中,代碼如下:

sky@localhost : example 08:49:45> create index idx_gid_uid_gc

-> on group_message(group_id,user_id,gmt_create);

Query OK, rows affected (0.03 sec)

Records: 96 Duplicates: 0 Warnings: 0

sky@localhost : example 09:07:30> drop index idx_group_message_gid_uid

-> on group_message;

Query OK, 96 rows affected (0.02 sec)

Records: 96 Duplicates: 0 Warnings: 0

然后再看如下 Query 的執行計劃,代碼如下:

sky@localhost : example 09:26:15> EXPLAIN

-> SELECT user_id,max(gmt_create)

-> FROM group_message

-> WHERE group_id < 10

-> GROUP BY group_id,user_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: 8

ref: NULL

rows: 4

Extra: Using where; Using index for group-by

1 row in set (0.00 sec)

我們看到在執行計劃的 Extra 信息中有信息顯示“Using index for group-by”，實際上這就是告訴我們，MySQL Query Optimizer 通過使用松散索引掃描來實現了我們所需要的 GROUP BY 操作。

下面這張圖片描繪了掃描過程的大概實現,要利用到松散索引掃描實現 GROUP BY,需要至少滿足以下幾個條件:

◆GROUP BY 條件字段必須在同一個索引中最前面的連續位置;

◆在使用GROUP BY 的同時，只能使用 MAX 和 MIN 這兩個聚合函數;

◆如果引用到了該索引中 GROUP BY 條件之外的字段條件的時候，必須以常量形式存在;

為什么松散索引掃描的效率會很高?

因為在沒有WHERE子句，也就是必須經過全索引掃描的時候，松散索引掃描需要讀取的鍵值數量與分組的組數量一樣多，也就是說比實際存在的鍵值數目要少很多。而在WHERE子句包含范圍判斷式或者等值表達式的時候,松散索引掃描查找滿足范圍條件的每個組的第1個關鍵字，并且再次讀取盡可能最少數量的關鍵字。

2.使用緊湊(Tight)索引掃描實現 GROUP BY

緊湊索引掃描實現 GROUP BY 和松散索引掃描的區別主要在于他需要在掃描索引的時候，讀取所有滿足條件的索引鍵，然后再根據讀取惡的數據來完成 GROUP BY 操作得到相應結果,代碼如下:

　sky@localhost : example 08:55:14> EXPLAIN

-> SELECT max(gmt_create)

-> FROM group_message

-> WHERE group_id = 2

-> GROUP BY user_idG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: ref

possible_keys: idx_group_message_gid_uid,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.01 sec)

這時候的執行計劃的 Extra 信息中已經沒有“Using index for group-by”了,但并不是說 MySQL 的 GROUP BY 操作并不是通過索引完成的,只不過是需要訪問 WHERE 條件所限定的所有索引鍵信息之后才能得出結果,這就是通過緊湊索引掃描來實現 GROUP BY 的執行計劃輸出信息.

在 MySQL 中,MySQL Query Optimizer 首先會選擇嘗試通過松散索引掃描來實現 GROUP BY 操作,當發現某些情況無法滿足松散索引掃描實現 GROUP BY 的要求之后,才會嘗試通過緊湊索引掃描來實現.

當 GROUP BY 條件字段并不連續或者不是索引前綴部分的時候,MySQL Query Optimizer 無法使用松散索引掃描,設置無法直接通過索引完成 GROUP BY 操作,因為缺失的索引鍵信息無法得到,但是,如果 Query 語句中存在一個常量值來引用缺失的索引鍵,則可以使用緊湊索引掃描完成 GROUP BY 操作,因為常量填充了搜索關鍵字中的“差距”,可以形成完整的索引前綴,這些索引前綴可以用于索引查找,而如果需要排序GROUP BY結果,并且能夠形成索引前綴的搜索關鍵字,MySQL還可以避免額外的排序操作,因為使用有順序的索引的前綴進行搜索已經按順序檢索到了所有關鍵字.

3.使用臨時表實現 GROUP BY

MySQL 在進行 GROUP BY 操作的時候要想利用所有,必須滿足 GROUP BY 的字段必須同時存放于同一個索引中,且該索引是一個有序索引(如 Hash 索引就不能滿足要求),而且,并不只是如此,是否能夠利用索引來實現 GROUP BY 還與使用的聚合函數也有關系.

前面兩種 GROUP BY 的實現方式都是在有可以利用的索引的時候使用的,當 MySQL Query Optimizer 無法找到合適的索引可以利用的時候,就不得不先讀取需要的數據，然后通過臨時表來完成 GROUP BY 操作,代碼如下:

sky@localhost : example 09:02:40> EXPLAIN

-> SELECT max(gmt_create)

-> FROM group_message

-> WHERE group_id > 1 and group_id < 10

-> GROUP BY user_idG

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: group_message

type: range

possible_keys: idx_group_message_gid_uid,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

這次的執行計劃非常明顯的告訴我們 MySQL 通過索引找到了我們需要的數據,然后創建了臨時表,又進行了排序操作,才得到我們需要的 GROUP BY 結果.

當 MySQL Query Optimizer 發現僅僅通過索引掃描并不能直接得到 GROUP BY 的結果之后，他就不得不選擇通過使用臨時表然后再排序的方式來實現 GROUP BY了.

在這樣示例中即是這樣的情況,group_id 并不是一個常量條件,而是一個范圍,而且 GROUP BY 字段為 user_id,所以 MySQL 無法根據索引的順序來幫助 GROUP BY 的實現,只能先通過索引范圍掃描得到需要的數據,然后將數據存入臨時表,然后再進行排序和分組操作來完成 GROUP BY.

講了這么多其實最簡單的就是,查詢dedecms,織夢,程序的欄目標題表,以欄目id分組,代碼如下:

SELECT *

FROM `dede_archives`

GROUP BY `typeid`

LIMIT 0 , 30

這樣即可了,一些相關group by 實例,代碼如下,--按某一字段分組取最大(小)值所在行的數據,數據如下:

name val memo

a 2 a2(a的第二個值)

a 1 a1--a的第一個值

a 3 a3:a的第三個值

b 1 b1--b的第一個值

b 3 b3:b的第三個值

b 2 b2b2b2b2

b 4 b4b4

b 5 b5b5b5b5b5

*/

--創建表并插入數據：

create table tb(name varchar(10),val int,memo varchar(20))

insert into tb values('a', 2, 'a2(a的第二個值)')

insert into tb values('a', 1, 'a1--a的第一個值')

insert into tb values('a', 3, 'a3:a的第三個值')

insert into tb values('b', 1, 'b1--b的第一個值')

insert into tb values('b', 3, 'b3:b的第三個值')

insert into tb values('b', 2, 'b2b2b2b2')

insert into tb values('b', 4, 'b4b4')

insert into tb values('b', 5, 'b5b5b5b5b5')

go

--一、按name分組取val最大的值所在行的數據。

--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name

--方法2：

select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)

--方法3：

select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

--方法4：

select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

--方法5

select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

/*

name val memo

---------- ----------- --------------------

a 3 a3:a的第三個值

b 5 b5b5b5b5b5

*/

本人推薦使用1,3,4,結果顯示1,3,4效率相同,2，5效率差些,不過我3,4效率相同毫無疑問,1就不一樣了,想不搞了.

二、按name分組取val最小的值所在行的數據。

方法1:select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name

方法2:select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)

方法3:select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name

方法4:select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name

方法5:select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name

name val memo

---------- ----------- --------------------

a 1 a1--a的第一個值

b 1 b1--b的第一個值

三、按name分組取第一次出現的行所在的數據.

select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name

/*

name val memo

---------- ----------- --------------------

a 2 a2(a的第二個值)

b 1 b1--b的第一個值

*/

四、按name分組隨機取一條數據.

select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name/*

name val memo

---------- ----------- --------------------

a 1 a1--a的第一個值

b 5 b5b5b5b5b5

*/

五、按name分組取最小的兩個(N個)val

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val

select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name

/*

name val memo

---------- ----------- --------------------

a 1 a1--a的第一個值

a 2 a2(a的第二個值)

b 1 b1--b的第一個值

b 2 b2b2b2b2

*/

六、按name分組取最大的兩個(N個)val

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val

select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name

/*  Vevb.com

name val memo

---------- ----------- --------------------

a 2 a2(a的第二個值)

a 3 a3:a的第三個值

b 4 b4b4

b 5 b5b5b5b5b5

*/

七,假如整行數據有重復,所有的列都相同,例如下表中的第5,6兩行數據完全相同,按name分組取最大的兩個(N個)val,數據如下:

name val memo

a 2 a2(a的第二個值)

a 1 a1--a的第一個值

a 1 a1--a的第一個值

a 3 a3:a的第三個值

a 3 a3:a的第三個值

b 1 b1--b的第一個值

b 3 b3:b的第三個值

b 2 b2b2b2b2

b 4 b4b4

b 5 b5b5b5b5b5