您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“MySQL中的count()、union()和group by語(yǔ)句的用法”的有關(guān)知識(shí),在實(shí)際案例的操作過(guò)程中,不少人都會(huì)遇到這樣的困境,接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
count()是一個(gè)聚合函數(shù),對(duì)于返回的結(jié)果集,一行行地判斷,如果count函數(shù)的參數(shù)不是NULL,累計(jì)值就加1,否則不加。最后返回累計(jì)值?!鞠嚓P(guān)推薦:mysql視頻教程】
1.對(duì)于count(主鍵id)來(lái)說(shuō),InnoDB引擎會(huì)遍歷整張表,把每一行的id值都取出來(lái),返回給server層。server層拿到id后,判斷是不可能為空的,就按行累加
2.對(duì)于count(1)來(lái)說(shuō),InnoDB引擎遍歷整張表,但不取值。server層對(duì)于返回的每一行,放一個(gè)數(shù)字1進(jìn)入,判斷是不可能為空的,按行累加
3.對(duì)于count(字段)來(lái)說(shuō),如果這個(gè)字段是定義為not null的話,一行行地從記錄里面讀出這個(gè)字段,判斷不能為null,按行累加;如果這個(gè)字段定義允許為null的話,那么執(zhí)行的時(shí)候,判斷到有可能是null,還要把值取出來(lái)在判斷一下,不是null才累加
4.對(duì)于count(*)
來(lái)說(shuō),并不會(huì)把全部字段取出來(lái),而是專(zhuān)門(mén)做了優(yōu)化。不取值,count(*)
肯定不是null,按行累加
為了便于量化分析,以下面表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)結(jié)果的并集。并集的意思是這兩個(gè)集合加起來(lái),重復(fù)的行只保留一行
第二行的key=PRIMARY,說(shuō)明第二個(gè)子句用到了索引id
第三行的Extra字段,表示在對(duì)子查詢(xún)的結(jié)果集做union的時(shí)候,使用了臨時(shí)表
這個(gè)語(yǔ)句的執(zhí)行流程如下:
1.創(chuàng)建一個(gè)內(nèi)存臨時(shí)表,這個(gè)臨時(shí)表只有一個(gè)整型字段f,并且f是主鍵字段
2.執(zhí)行第一個(gè)子查詢(xún),得到1000這個(gè)值
3.執(zhí)行第二個(gè)子查詢(xún):
拿到第一行id=1000,試圖插入臨時(shí)表中。但由于1000這個(gè)值已經(jīng)存在于臨時(shí)表了,違反了唯一性約束,所以插入失敗,然后繼續(xù)執(zhí)行
取到第二行id=999,插入臨時(shí)表成功
4.從臨時(shí)表中按行取出數(shù)據(jù),返回結(jié)果,并刪除臨時(shí)表,結(jié)果中包含兩行數(shù)據(jù)分別是1000和999
這里的內(nèi)存臨時(shí)表起到了暫存數(shù)據(jù)的作用,而且計(jì)算過(guò)程還用上了臨時(shí)表主鍵id的唯一性約束,實(shí)現(xiàn)了union的語(yǔ)義
如果把上面的語(yǔ)句中union改成union all的話,就沒(méi)有了去重的語(yǔ)義。這樣執(zhí)行的時(shí)候,就依次執(zhí)行子查詢(xún),得到的結(jié)果直接作為結(jié)果集的一部分,發(fā)給客戶(hù)端。因此也就不需要臨時(shí)表了
第二行Extra字段顯示的是Using index,表示只使用了覆蓋索引,沒(méi)有用臨時(shí)表
還是使用上面的表t1,分析下面這條SQL語(yǔ)句:
select id%10 as m, count(*) as c from t1 group by m;
這個(gè)語(yǔ)句的邏輯是把表t1里的數(shù)據(jù),按照id%10進(jìn)行分組統(tǒng)計(jì),并按照m的結(jié)果排序后輸出。explain結(jié)果如下:
在Extra字段里面,可以看到三個(gè)信息:
Using index,表示這個(gè)語(yǔ)句使用了覆蓋索引,選擇了索引a,不需要回表
Using temporary,表示使用了臨時(shí)表
Using filesort,表示需要排序
這個(gè)語(yǔ)句的執(zhí)行流程如下:
1.創(chuàng)建內(nèi)存臨時(shí)表,表里有兩個(gè)字段m和c,主鍵是m
2.掃描表t1的索引a,依次取出葉子節(jié)點(diǎn)上的id值,計(jì)算id%10的結(jié)果,記為x
如果臨時(shí)表中沒(méi)有主鍵為x的行,就插入一個(gè)記錄(x,1)
如果表中有主鍵為x的行,就將x這一行的c值加1
3.遍歷完成后,再根據(jù)字段m做排序,得到結(jié)果集返回給客戶(hù)端
內(nèi)存臨時(shí)表排序流程圖:
如果并不需要對(duì)結(jié)果進(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)始的,因此返回的結(jié)果集中第一行是id=1
這個(gè)例子里由于臨時(shí)表只有10行,內(nèi)存可以放得下,因此全程只使用了內(nèi)存臨時(shí)表。但是,內(nèi)存臨時(shí)表的大小是有限的,參數(shù)tmp_table_size就是控制整個(gè)內(nèi)存大小的,默認(rèn)是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)存臨時(shí)表的大小限制為最大1024字節(jié),并把語(yǔ)句改成id%100,這樣返回結(jié)果里有100行數(shù)據(jù)。但是,這時(shí)的內(nèi)存臨時(shí)表大小不夠存下這100行數(shù)據(jù),也就是說(shuō),執(zhí)行過(guò)程中會(huì)發(fā)現(xiàn)內(nèi)存臨時(shí)表大小達(dá)到了上限。那么,這時(shí)候會(huì)把內(nèi)存臨時(shí)表轉(zhuǎn)成磁盤(pán)臨時(shí)表,磁盤(pán)臨時(shí)表默認(rèn)使用的引擎是InnoDB
group by的語(yǔ)義邏輯,是統(tǒng)計(jì)不同的值的個(gè)數(shù)。但是,由于每一行的id%100的結(jié)果是無(wú)序的,所以就需要有一個(gè)臨時(shí)表來(lái)記錄并統(tǒng)計(jì)結(jié)果。那么,如果掃描過(guò)程中可以保證出現(xiàn)的數(shù)據(jù)是有序的就可以了
假設(shè),現(xiàn)在有一個(gè)類(lèi)似下圖的這么一個(gè)數(shù)據(jù)結(jié)構(gòu)
如果可以確保輸入的數(shù)據(jù)是有序的,那么計(jì)算group by的時(shí)候,就只需要從左到右,順序掃描,依次累加。也就是下面這個(gè)流程:
當(dāng)碰到第一個(gè)1的時(shí)候,已經(jīng)知道累積了X個(gè)0,結(jié)果集里的第一行就是(0,X)
當(dāng)碰到第一個(gè)2的時(shí)候,已經(jīng)知道累積了Y個(gè)1,結(jié)果集里的第一行就是(1,Y)
按照這個(gè)邏輯執(zhí)行的話,掃描到整個(gè)輸入的數(shù)據(jù)結(jié)束,就可以拿到group by的結(jié)果,不需要臨時(shí)表,也需要再額外排序
在MySQL5.7版本支持了generated column機(jī)制,用來(lái)實(shí)現(xiàn)列數(shù)據(jù)的關(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上的數(shù)據(jù)就是有序的了。group by語(yǔ)句就可以改成:
select z, count(*) as c from t1 group by z;
從這個(gè)Extra字段可以看到,這個(gè)語(yǔ)句的執(zhí)行不再需要臨時(shí)表,也不需要排序了
在group by語(yǔ)句中加入SQL_BIG_RESULT這個(gè)提示,就可以告訴優(yōu)化器:這個(gè)語(yǔ)句涉及的數(shù)據(jù)量很大,直接用磁盤(pán)臨時(shí)表。因?yàn)榇疟P(pán)臨時(shí)表是B+樹(shù)存儲(chǔ),存儲(chǔ)效率不如數(shù)組來(lái)得高。所以MySQL優(yōu)化器直接用數(shù)組來(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.掃描完成后,對(duì)sort_buffer的字段m做排序(如果sort_buffer內(nèi)存不夠用,就會(huì)利用磁盤(pán)臨時(shí)文件輔助排序)
4.排序完成后,就得到了一個(gè)有序數(shù)組
根據(jù)有序數(shù)組,得到數(shù)組里面的不同值,以及每個(gè)值的出現(xiàn)次數(shù)
這個(gè)語(yǔ)句的執(zhí)行沒(méi)有再使用臨時(shí)表,而是直接用了排序算法
“MySQL中的count()、union()和group by語(yǔ)句的用法”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。