共計 3362 個字符，預(yù)計需要花費 9 分鐘才能閱讀完成。

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

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

count() 是一個聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果 count 函數(shù)的參數(shù)不是 NULL，累計值就加 1，否則不加。最后返回累計值。【相關(guān)推薦：mysql 視頻教程】

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

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

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

4. 對于 count(*) 來說，并不會把全部字段取出來，而是專門做了優(yōu)化。不取值，count(*) 肯定不是 null，按行累加

二、union 執(zhí)行流程

為了便于量化分析，以下面表 t1 來舉例

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 語句：

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

union 的語義是取這兩個子查詢結(jié)果的并集。并集的意思是這兩個集合加起來，重復(fù)的行只保留一行

第二行的 key=PRIMARY，說明第二個子句用到了索引 id

第三行的 Extra 字段，表示在對子查詢的結(jié)果集做 union 的時候，使用了臨時表

這個語句的執(zhí)行流程如下：

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

2. 執(zhí)行第一個子查詢，得到 1000 這個值

3. 執(zhí)行第二個子查詢：

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

取到第二行 id=999，插入臨時表成功

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

這里的內(nèi)存臨時表起到了暫存數(shù)據(jù)的作用，而且計算過程還用上了臨時表主鍵 id 的唯一性約束，實現(xiàn)了 union 的語義

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

第二行 Extra 字段顯示的是 Using index，表示只使用了覆蓋索引，沒有用臨時表

三、group by 語句詳解 1、group by 執(zhí)行流程

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

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

這個語句的邏輯是把表 t1 里的數(shù)據(jù)，按照 id%10 進行分組統(tǒng)計，并按照 m 的結(jié)果排序后輸出。explain 結(jié)果如下：

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

Using index，表示這個語句使用了覆蓋索引，選擇了索引 a，不需要回表

Using temporary，表示使用了臨時表

Using filesort，表示需要排序

這個語句的執(zhí)行流程如下：

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

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

如果臨時表中沒有主鍵為 x 的行，就插入一個記錄 (x,1)

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

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

內(nèi)存臨時表排序流程圖：

如果并不需要對結(jié)果進行排序，在 SQL 語句末尾增加 order by null：

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

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

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

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

把內(nèi)存臨時表的大小限制為最大 1024 字節(jié)，并把語句改成 id%100，這樣返回結(jié)果里有 100 行數(shù)據(jù)。但是，這時的內(nèi)存臨時表大小不夠存下這 100 行數(shù)據(jù)，也就是說，執(zhí)行過程中會發(fā)現(xiàn)內(nèi)存臨時表大小達到了上限。那么，這時候會把內(nèi)存臨時表轉(zhuǎn)成磁盤臨時表，磁盤臨時表默認使用的引擎是 InnoDB

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

group by 的語義邏輯，是統(tǒng)計不同的值的個數(shù)。但是，由于每一行的 id%100 的結(jié)果是無序的，所以就需要有一個臨時表來記錄并統(tǒng)計結(jié)果。那么，如果掃描過程中可以保證出現(xiàn)的數(shù)據(jù)是有序的就可以了

假設(shè)，現(xiàn)在有一個類似下圖的這么一個數(shù)據(jù)結(jié)構(gòu)

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

當(dāng)碰到第一個 1 的時候，已經(jīng)知道累積了 X 個 0，結(jié)果集里的第一行就是 (0,X)

當(dāng)碰到第一個 2 的時候，已經(jīng)知道累積了 Y 個 1，結(jié)果集里的第一行就是 (1,Y)

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

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

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

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

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

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

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

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

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

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

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

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

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

根據(jù)有序數(shù)組，得到數(shù)組里面的不同值，以及每個值的出現(xiàn)次數(shù)

這個語句的執(zhí)行沒有再使用臨時表，而是直接用了排序算法

“MySQL 中的 count()、union() 和 group by 語句的用法”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實用文章！