MySQL中的count()、union()和group by語(yǔ)句的用法

本篇內容介紹了“中的count()、union()和group by語(yǔ)句的用法”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

一、MySQL中count()的不同用法

count()是一個(gè)聚合函數，對于返回的結果集，一行行地判斷，如果count函數的參數不是NULL，累計值就加1，否則不加。最后返回累計值?！鞠嚓P(guān)推薦：mysql視頻教程】

1.對于count(主鍵id)來(lái)說(shuō)，InnoDB引擎會(huì )遍歷整張表，把每一行的id值都取出來(lái)，返回給server層。server層拿到id后，判斷是不可能為空的，就按行累加

2.對于count(1)來(lái)說(shuō)，InnoDB引擎遍歷整張表，但不取值。server層對于返回的每一行，放一個(gè)數字1進(jìn)入，判斷是不可能為空的，按行累加

3.對于count(字段)來(lái)說(shuō)，如果這個(gè)字段是定義為not null的話(huà)，一行行地從記錄里面讀出這個(gè)字段，判斷不能為null，按行累加；如果這個(gè)字段定義允許為null的話(huà)，那么執行的時(shí)候，判斷到有可能是null，還要把值取出來(lái)在判斷一下，不是null才累加

4.對于count(*)來(lái)說(shuō)，并不會(huì )把全部字段取出來(lái)，而是專(zhuān)門(mén)做了優(yōu)化。不取值，count(*)肯定不是null，按行累加

二、union執行流程

為了便于量化分析，以下面表t1來(lái)舉例

create table t1(id int primary key, a int, b int, index(a));

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;

END

分析下面這條SQL語(yǔ)句：

(select 1000 as f) union (select id from t1 order by id desc limit 2);

union的語(yǔ)義是取這兩個(gè)子查詢(xún)結果的并集。并集的意思是這兩個(gè)集合加起來(lái)，重復的行只保留一行

• 第二行的key=PRIMARY，說(shuō)明第二個(gè)子句用到了索引id

• 第三行的Extra字段，表示在對子查詢(xún)的結果集做union的時(shí)候，使用了臨時(shí)表

這個(gè)語(yǔ)句的執行流程如下：

1.創(chuàng )建一個(gè)內存臨時(shí)表，這個(gè)臨時(shí)表只有一個(gè)整型字段f，并且f是主鍵字段

2.執行第一個(gè)子查詢(xún)，得到1000這個(gè)值

3.執行第二個(gè)子查詢(xún)：

• 拿到第一行id=1000，試圖插入臨時(shí)表中。但由于1000這個(gè)值已經(jīng)存在于臨時(shí)表了，違反了唯一性約束，所以插入失敗，然后繼續執行

• 取到第二行id=999，插入臨時(shí)表成功

4.從臨時(shí)表中按行取出數據，返回結果，并刪除臨時(shí)表，結果中包含兩行數據分別是1000和999

這里的內存臨時(shí)表起到了暫存數據的作用，而且計算過(guò)程還用上了臨時(shí)表主鍵id的唯一性約束，實(shí)現了union的語(yǔ)義

如果把上面的語(yǔ)句中union改成union all的話(huà)，就沒(méi)有了去重的語(yǔ)義。這樣執行的時(shí)候，就依次執行子查詢(xún)，得到的結果直接作為結果集的一部分，發(fā)給客戶(hù)端。因此也就不需要臨時(shí)表了

第二行Extra字段顯示的是Using index，表示只使用了覆蓋索引，沒(méi)有用臨時(shí)表

三、group by語(yǔ)句詳解

1、group by執行流程

還是使用上面的表t1，分析下面這條SQL語(yǔ)句：

select id%10 as m, count(*) as c from t1 group by m;

這個(gè)語(yǔ)句的邏輯是把表t1里的數據，按照id%10進(jìn)行分組統計，并按照m的結果排序后輸出。explain結果如下：

在Extra字段里面，可以看到三個(gè)信息：

• Using index，表示這個(gè)語(yǔ)句使用了覆蓋索引，選擇了索引a，不需要回表

• Using temporary，表示使用了臨時(shí)表

• Using filesort，表示需要排序

這個(gè)語(yǔ)句的執行流程如下：

1.創(chuàng )建內存臨時(shí)表，表里有兩個(gè)字段m和c，主鍵是m

2.掃描表t1的索引a，依次取出葉子節點(diǎn)上的id值，計算id%10的結果，記為x

• 如果臨時(shí)表中沒(méi)有主鍵為x的行，就插入一個(gè)記錄(x,1)

• 如果表中有主鍵為x的行，就將x這一行的c值加1

3.遍歷完成后，再根據字段m做排序，得到結果集返回給客戶(hù)端

內存臨時(shí)表排序流程圖：

如果并不需要對結果進(jìn)行排序，在SQL語(yǔ)句末尾增加order by null：

select id%10 as m, count(*) as c from t1 group by m order by null;

由于表t1中的id值是從1開(kāi)始的，因此返回的結果集中第一行是id=1

這個(gè)例子里由于臨時(shí)表只有10行，內存可以放得下，因此全程只使用了內存臨時(shí)表。但是，內存臨時(shí)表的大小是有限的，參數tmp_table_size就是控制整個(gè)內存大小的，默認是16M

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把內存臨時(shí)表的大小限制為最大1024字節，并把語(yǔ)句改成id%100，這樣返回結果里有100行數據。但是，這時(shí)的內存臨時(shí)表大小不夠存下這100行數據，也就是說(shuō)，執行過(guò)程中會(huì )發(fā)現內存臨時(shí)表大小達到了上限。那么，這時(shí)候會(huì )把內存臨時(shí)表轉成磁盤(pán)臨時(shí)表，磁盤(pán)臨時(shí)表默認使用的引擎是InnoDB

2、group by優(yōu)化方法——索引

group by的語(yǔ)義邏輯，是統計不同的值的個(gè)數。但是，由于每一行的id%100的結果是無(wú)序的，所以就需要有一個(gè)臨時(shí)表來(lái)記錄并統計結果。那么，如果掃描過(guò)程中可以保證出現的數據是有序的就可以了

假設，現在有一個(gè)類(lèi)似下圖的這么一個(gè)數據結構

如果可以確保輸入的數據是有序的，那么計算group by的時(shí)候，就只需要從左到右，順序掃描，依次累加。也就是下面這個(gè)流程：

• 當碰到第一個(gè)1的時(shí)候，已經(jīng)知道累積了X個(gè)0，結果集里的第一行就是(0,X)

• 當碰到第一個(gè)2的時(shí)候，已經(jīng)知道累積了Y個(gè)1，結果集里的第一行就是(1,Y)

按照這個(gè)邏輯執行的話(huà)，掃描到整個(gè)輸入的數據結束，就可以拿到group by的結果，不需要臨時(shí)表，也需要再額外排序

在MySQL5.7版本支持了generated column機制，用來(lái)實(shí)現列數據的關(guān)聯(lián)更新。創(chuàng )建一個(gè)列z，在z列上創(chuàng )建一個(gè)索引

alter table t1 add column z int generated always as(id % 100), add index(z);

這樣，索引z上的數據就是有序的了。group by語(yǔ)句就可以改成：

select z, count(*) as c from t1 group by z;

從這個(gè)Extra字段可以看到，這個(gè)語(yǔ)句的執行不再需要臨時(shí)表，也不需要排序了

3、group by優(yōu)化方法——直接排序

在group by語(yǔ)句中加入SQL_BIG_RESULT這個(gè)提示，就可以告訴優(yōu)化器：這個(gè)語(yǔ)句涉及的數據量很大，直接用磁盤(pán)臨時(shí)表。因為磁盤(pán)臨時(shí)表是B+樹(shù)存儲，存儲效率不如數組來(lái)得高。所以MySQL優(yōu)化器直接用數組來(lái)存

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

1.初始化sort_buffer，確定放入一個(gè)整型字段，記為m

2.掃描表t1的索引a，依次取出里面的id值，將id%100的值存入sort_buffer中

3.掃描完成后，對sort_buffer的字段m做排序（如果sort_buffer內存不夠用，就會(huì )利用磁盤(pán)臨時(shí)文件輔助排序）

4.排序完成后，就得到了一個(gè)有序數組

根據有序數組，得到數組里面的不同值，以及每個(gè)值的出現次數

這個(gè)語(yǔ)句的執行沒(méi)有再使用臨時(shí)表，而是直接用了排序算法