mysql為什么groupby不能顯示全部數(shù)據(jù)？
在日常查詢中，索引或其他數(shù)據(jù)查找方法可能不是查詢執(zhí)行中開銷最大的部分。例如，MySQLGROUPBY可能負(fù)責(zé)90%以上的查詢執(zhí)行時(shí)間。MySQL執(zhí)行GROUPBY的主要復(fù)雜度是計(jì)算GROUPBY語句中的聚合函數(shù)。UDF聚合函數(shù)是逐個(gè)獲取組成單個(gè)組的所有值。這樣，它可以在移動(dòng)到另一個(gè)組之前計(jì)算單個(gè)組的聚合函數(shù)值。當(dāng)然，問題是在大多數(shù)情況下，源數(shù)據(jù)值沒有分組。不同組的值在處理過程中會(huì)相互跟隨。因此，我們需要一個(gè)特殊的步驟。
用Letamp處理MySQL組。;讓我們?cè)賮砜匆幌挛覀冎耙娺^的同一個(gè)表:MySQLshowcr:TBL創(chuàng)建Tabl:創(chuàng)建表`TBL`(`id`int(11)NOTNULLAUTO_INCREMENT，`k`int(11)NOTNULLDEFAULT0，`g`int(10)unsignedNOTNULL，PRIMARYKEY(`id`)，KEY`k`(`k`))ENGINEInnoDBAUTO_incremENT2340933DEFAULTcharsetlatin1集合中的第1行(0.00秒)
并以不同的執(zhí)行相同的GROUPBY語句:
中的索引排序分組。
mysqlselectk，count(*)cfromTBLgroupbykorderbyklimit5；
--
|k|c|
--
|2|3|
|4|1|
|5|2|
|8|1|
|9|1|
--
一組5行(0.00秒)
mysql解釋selectk，count(*)cfromTBLgroupbykord:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:指數(shù)
possibl:·k
k:·k
k:4
r:零
rows:5
filt:100.00
:使用指數(shù)
集合中的1行，1個(gè)警告(0.00秒)
在這種情況下，我們?cè)贕ROUPBY列上有一個(gè)索引。這樣我們就可以一組一組的掃描數(shù)據(jù)，動(dòng)態(tài)的執(zhí)行GROUPBY(低成本)。當(dāng)我們使用LIMIT來限制我們檢索或使用的組的數(shù)量時(shí)，這尤其有效覆蓋索引"，因?yàn)轫樞蛩饕龗呙枋且环N非常快速的操作。
如果您有少量的組并且不覆蓋索引，索引順序掃描可能會(huì)導(dǎo)致大量的IO。所以這可能不是最優(yōu)方案。
中的外部排序分組依據(jù)
mysql解釋selectSQL_BIG_RESULTg，count(*)cfromTBLgroupbyGlimit5g
***************************1.第*************************行
id:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:所有
possibl:零
k:零
k:零
r:零
rows:998490
filt:100.00
使用文件排序的:
集合中的1行，1個(gè)警告(0.00秒)
mysqlselectSQL_BIG_RESULTg，count(*)cfromTBLgroupbyglimit5；
--
|g|c|
--
|0|1|
|1|2|
|4|1|
|5|1|
|6|2|
--
一組5行(0.88秒)
如果我們不這樣做。;如果沒有允許我們按組順序掃描數(shù)據(jù)的索引，我們可以通過外部排序(也稱為"文件排序和在MySQL中)。您可能會(huì)注意到，我在這里使用SQL_BIG_RESULT提示符來獲得這個(gè)計(jì)劃。如果沒有它，MySQL在這種情況下也不會(huì)選擇這個(gè)方案。
一般來說，只有當(dāng)我們有大量的組時(shí)，MySQL更喜歡使用這種計(jì)劃，因?yàn)樵谶@種情況下，排序比擁有臨時(shí)表(我們將在下面討論)更有效。
中的臨時(shí)表GROUPBY
mysql解釋selectg，sum(g)sfromtblgroupbyglimit5G
***************************1.第*************************行
id:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:所有
possibl:零
k:零
k:零
r:零
rows:998490
filt:100.00
:使用臨時(shí)
集合中的1行，1個(gè)警告(0.00秒)
mysqlselectg，sum(g)sfrommTBLgroupbygorderbynulllimit5；
--
|g|s|
--
|0|0|
|1|2|
|4|4|
|5|5|
|6|12|
--
一組5行(7.75秒)
在這種情況下，MySQL也會(huì)執(zhí)行全表掃描。但是它不是運(yùn)行額外的排序過程，而是創(chuàng)建一個(gè)臨時(shí)表。這個(gè)臨時(shí)表在每個(gè)組中包含一行，對(duì)于每個(gè)傳入的行，相應(yīng)組的值將被更新。很多更新！雖然這在內(nèi)存上可能是合理的，但是如果結(jié)果表非常大，以至于更新會(huì)導(dǎo)致大量的磁盤IO，那么它就變得非常昂貴。在這種情況下，外部排序計(jì)劃通常更好。請(qǐng)注意，盡管MySQL默認(rèn)為這個(gè)用例選擇了這個(gè)計(jì)劃，但是如果我們不不提供任何提示，它幾乎比我們使用SQL_BIG_RESULT提示的計(jì)劃慢10倍。你可能注意到我加了"按空值排序這個(gè)問題。這是向你展示的唯一計(jì)劃清理"臨時(shí)桌子。沒有它，我們得到這個(gè)方案:mysqlexplainselectg，sum(G)sfromTBLgroupbyGlimit5g***********************1。排*************************id:1select_typ:簡(jiǎn)單tabl:TBLpartitions:NULLtyp:ALLpossible_keysecNULLk:NULLk:NULLr:NULLrows:998490filt:100.00:使用臨時(shí)；使用文件對(duì)集合中的1行進(jìn)行排序，1次警告(0.00秒)
其中，我們得到了"兩個(gè)最壞的"來自臨時(shí)和文件分類的提示。。MySQL5.7總是返回按分組順序排序的分組結(jié)果，即使查詢沒有。;我不需要它(這可能需要昂貴的額外排序過程)。ORD:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:山脈
possibl:·k
k:·k
k:4
r:零
rows:2
filt:100.00
:使用索引進(jìn)行分組
集合中的1行，1個(gè)警告(0.00秒)
mysql通過k從tbl組中選擇k，max(id)；
--
|k|最大值(id)|
--
|0|2340920|
|1|2340916|
|2|2340932|
|3|2340928|
|4|2340924|
--
一組5行(0.00秒)
這個(gè)方法只適用于非常特殊的聚合函數(shù):MIN()和m。AX().這些不需要遍歷組中的所有行來計(jì)算值。它們可以直接跳轉(zhuǎn)到組中的最小或最大組值(如果有這樣的索引)。如果索引只建立在列(k)上，如何找到每個(gè)組的MAX(ID)值？這是一個(gè)InnoDB表。請(qǐng)記住，InnoDB表有效地將主鍵附加到所有索引上。(K)變成(K，ID)，這允許我們對(duì)這個(gè)查詢使用跳過掃描優(yōu)化。只有當(dāng)每組有大量行時(shí)，才會(huì)啟用這種優(yōu)化。否則，MySQL更喜歡使用更傳統(tǒng)的方法來執(zhí)行這個(gè)查詢(比如方法#1中詳述的索引有序分組)。雖然我們使用MIN()/MAX()聚合函數(shù)，但其他優(yōu)化也適用于它們。例如，如果有一個(gè)沒有GROUPBY的聚合函數(shù)(事實(shí)上，所有的表都有一個(gè)GROUP)，MySQL在統(tǒng)計(jì)分析階段從索引中獲取這些值。并避免在執(zhí)行階段完全讀表:MySQLexplainselectmax(k)來自TBLG*********************1。第*************行。id:1select_typ:簡(jiǎn)單tabl:NULLpartitions:NULLtyp:NULLpossibl:NULLk:NULLk:NULLr:NULLrows:NULLfilt:NULL:Selecttables優(yōu)化了集合中的1行，1個(gè)警告(0.00秒)
過濾和分組
我們已經(jīng)研究了MySQL執(zhí)行GROUPBY的四種。為了簡(jiǎn)單起見，我在整個(gè)表上使用了GROUPBY，并且沒有應(yīng)用過濾。當(dāng)你有一個(gè)WHERE子句時(shí)，同樣的概念也適用:MySQLexplainselectg，sum(G)sfromTBLWH:1select_typ:簡(jiǎn)單tabl:TBLpartitions:NULLtyp:范圍可能_k:kk:kk:4r:NULLrows:1filt:100.00:使用索引條件；使用集合中的臨時(shí)1行，1個(gè)警告(0.00秒)
在這種情況下，我們使用K列上的范圍進(jìn)行數(shù)據(jù)過濾/查找，并在有臨時(shí)表時(shí)執(zhí)行GROUPBY。在某些情況下，方法并不。但是，在其他情況下，我們必須選擇使用GROUPBY索引或其他索引進(jìn)行篩選:
MySQLaltertableTBLaddkey(g)；
查詢正常，0行受影響(4.17秒)
Records:0Duplicat:0Warnings:0
mysql解釋selectg，sum(g)sfromtbl其中k1groupbyg限制5G
***************************1.第*************************行
id:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:指數(shù)
possibl:k，g
k:g
k:4
r:零
rows:16
filt:50.00
:用在哪里
集合中的1行，1個(gè)警告(0.00秒)
mysql解釋selectg，sum(G)sfromTBLwh:1號(hào)
select_typ:簡(jiǎn)單
tabl:TBL
partitions:零
typ:山脈
possibl:公司
k:·k
k:4
r:零
rows:1號(hào)
filt:100.00
:使用索引條件；使用臨時(shí)；使用文件排序
集合中的1行，1個(gè)警告(0.00秒)
根據(jù)該查詢中使用的特定常量，我們可以看到我們使用了索引順序掃描(和"丟棄"從索引解析WHERE子句)或使用索引解析WHERE子句(但使用臨時(shí)表解析GROUPBY)。根據(jù)我的經(jīng)驗(yàn)，這就是MySQLGROUPBYdoesn不要總是做出正確的選擇。您可能需要使用FORC
mysql主鍵的列可以再添加索引嗎？
看你的節(jié)目。如果查詢條件中沒有使用主鍵，可以將查詢字段設(shè)置為索引。如果你想在主鍵上設(shè)置一個(gè)索引，你不用我不需要它。主鍵具有索引的功能。