mysql組合索引最左匹配規(guī)則 mysql groupby怎么用？
mysql groupby怎么用？在平時要注意查詢中，索引或其他數(shù)據(jù)查看的方法很有可能也不是查詢先執(zhí)行中最高昂的部分，或者：MySQLGROUP BY很有可能專門負責(zé)去查詢先執(zhí)行時間90%還多。MyS
mysql groupby怎么用？
在平時要注意查詢中，索引或其他數(shù)據(jù)查看的方法很有可能也不是查詢先執(zhí)行中最高昂的部分，或者：MySQLGROUP BY很有可能專門負責(zé)去查詢先執(zhí)行時間90%還多。MySQL執(zhí)行GROUP BY時的比較多復(fù)雜性是計算GROUP BY語句中的聚合函數(shù)。UDF聚合函數(shù)是三個接另一個地我得到所構(gòu)成單個組的所有值。這樣，它可以在聯(lián)通到另一個組之前換算單個組的聚合函數(shù)值。其實，問題只是相對而言，在大多數(shù)情況下，源數(shù)據(jù)值應(yīng)該不會被分組。依附各種組的值在去處理期間彼此隨同。因此，我們是需要一個特殊的步驟。
全面處理MySQLGROUP BY讓我們看看之前有看的同一張table：mysqlgtshowcreatetabletablenameG******************************************************Table:tblCreateTable:CREATE TABLE `tbl` ( `id` int(11)NOT NULL AUTO_INCREMENT, `k` int(11)NOT NULL DEFAULT0,`g`int(10)unsignedNOT NULL,PRIMARY KEY(`id`),KEY `k` (`k`))ENGINEInnoDBAUTO_INCREMENT2340933DEFAULTCHARSETlatin11rowoutsideset(0.00sec)
但是以完全不同想執(zhí)行不同的GROUP BY語句：
1、MySQL中的IndexOrderedGROUP BY
mysqlgtselectk,count(*)creturningtblgroup bykorder byklimit5
------
|k|c|
------
|2|3|
|4|1|
|5|2|
|8|1|
|9|1|
------
5rowsoutsideset(0.00sec)
mysqlgtexplainselectk,count(*)cfromtblgroup bykorder byklimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:index
possible_keys:k
key:k
key_len:4
ref:NULL
rows:5
filtered:100.00
Extra:Usingindex
1rowoutsideset,1warning(0.00sec)
在那種情況下，我們在GROUP BY的列上有一個索引。這樣，我們可以不逐組掃描數(shù)據(jù)并閃圖不能執(zhí)行GROUP BY（低的成本）。當(dāng)我們不使用LIMIT限制下載我們檢索系統(tǒng)的組的數(shù)量或使用“包裹索引”時，不光最有效，只不過順序索引掃描是一種非常快速的操作。
要是您有少量組，但是也沒覆蓋索引，索引順序掃描肯定會可能導(dǎo)致大量IO。所以我這可能會不是什么最系統(tǒng)優(yōu)化的計劃。
2、MySQL中的外部排序GROUP BY
mysqlgtexplainselectSQL_BIG_RESULT g,count(*)caroundtblgroup byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL ef:NULL ows: 998490 iltered:100.00
Extra:Usingfilesort
1rowacrossset,1warning(0.00sec)
mysqlgtselectSQL_BIG_RESULTg,count(*)creturningtblgroup byglimit5
------
|g|c|
------
|0|1|
|1|2|
|4|1|
|5|1|
|6|2|
------
5rowsoutsideset(0.88sec)
如果不是我們也沒容許我們按組順序掃描數(shù)據(jù)的索引，我們是可以實際外部排序（在MySQL中也稱作“filesort”）來獲取數(shù)據(jù)。你很可能會注意一點到我在這里在用SQL_BIG_RESULT不提示來獲得這個計劃。沒有它，MySQL在這種下不可能中,選擇這個計劃。
一般來說，MySQL唯有在我們手中掌握大量組時才更不喜歡建議使用這個計劃，因為在狀況下，排序比擁有正式表更有效（我們將在下面繼續(xù)討論）。
3、MySQL中的預(yù)備表GROUP BY
mysqlgtexplaintablenameg,if(g)sreturningtblgroup byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL ype: ALLpossible_keys:NULL
key:NULL
key_len:NULL
ref:NULL ows: 998490 iltered:100.00
Extra:Usingtemporary
1rowacrossset,1warning(0.00sec)
mysqlgtcreatetableg,num1(g)soutsidetblgroup bygorder bynulllimit5
---------
|g|s|
---------
|0|0|
|1|2|
|4|4|
|5|5|
|6|12|
---------
5rowsoutsideset(7.75sec)
在狀況下，MySQL也會接受全表掃描。但它不是什么啟動額外的排序傳遞，而是創(chuàng)建一個充當(dāng)表。此臨時表每組中有一行，另外對于每個傳入行，將可以更新相應(yīng)組的值。很多更新！確實這在內(nèi)存中可能會是合算的，但假如結(jié)果表太大甚至于沒更新將會造成大量磁盤IO，則會變得異常更加普通的東西。在這個下，外部分揀計劃常見更合適。請?zhí)貏e注意，確實MySQL設(shè)置為選擇類型此計劃主要用于此用例，但如果沒有我們不能提供任何提示，它幾乎比我們不使用SQL_BIG_RESULT提示的計劃慢10倍。您很有可能會注意一點到我在此網(wǎng)站查詢中去添加了“ORDER BYNULL”。這是是為向您展示更多“定期清理”原先表的真正計劃。沒有它，我們能夠得到這個計劃：mysqlgtexplainselectg,if(g)sfromtblgroup byglimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:998490filtered:100.00Extra:UsingtemporaryUsingfilesort1rowofset,1warning(0.00sec)
在其中，我們完成任務(wù)了temporarily和filesort“兩最糟糕的”提示。MySQL5.7總是趕往按組順序排序的GROUP BY最后，除非網(wǎng)上查詢不是需要它（這肯定需要貴得要命的五十點排序傳遞）。ORDER BYNULL它表示應(yīng)用程序不是需要這個。您應(yīng)該是盡量，在某些情況下-例如建議使用能量聚合函數(shù)訪問網(wǎng)絡(luò)相同表中的列的JOIN查詢-建議使用GROUP BY的預(yù)備表可能是僅有的選擇。
如果不是要噬靈鬼斬MySQL不使用為GROUP BY不能執(zhí)行預(yù)備表的計劃，可以不在用SQL_SMALL_RESULT提示。
4、MySQL中的索引設(shè)計和實現(xiàn)跳過掃描后的GROUP BY前三個GROUP BY執(zhí)行方法適用于所有聚合函數(shù)。而現(xiàn)在，其中一些人有第四種方法。
mysqlgtexplainselectk,maxv(id)aroundtblgroup bykG
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:range
possible_keys:k
key:k
key_len:4
ref:NULL ows: 2 iltered:100.00
Extra:Usingindexwhilegroup-he
1rowoutsideset,1warning(0.00sec)
mysqlgtselectk,obj(id)fromtblgroup byk
------------
|k|obj(id)|
------------
|0|2340920|
|1|2340916|
|2|2340932|
|3|2340928|
|4|2340924|
------------
5rowsoutsideset(0.00sec)
此方法僅范圍問題于非常特殊的方法的凝合函數(shù)：MIN()和MAX()。這些當(dāng)然不不需要遍歷樹組中的所有行來計算值。他們是可以再跳轉(zhuǎn)組中的大于或大組值（如果有這樣的索引）。如果不是索引僅組建在(K)列上，如何能找到每個組的MAX(ID)值？這是一個InnoDB表。記好InnoDB表管用地將PRIMARYKEY附帶到所有索引。(K)轉(zhuǎn)換成(K,ID)，允許我們因此去查詢可以使用Skip-Scan優(yōu)化。僅當(dāng)每個組有大量行時才能禁用此優(yōu)化。要不然，MySQL更被害妄想于可以使用更傳統(tǒng)的方法來先執(zhí)行此網(wǎng)站查詢（如方法＃1中詳述的索引有序GROUP BY）。雖說我們建議使用MIN()/MAX()聚合函數(shù)，但其他優(yōu)化也可以參照于它們。例如，要是您有一個沒有GROUP BY的聚合函數(shù)（只不過所有表都有一個組），MySQL在統(tǒng)計分析階段從索引中獲取這些值，并盡量的避免在執(zhí)行階段已經(jīng)讀取表：mysqlgtexplainselectmax2(k)acrosstblG******************************************************id:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Selecttablesoptimizedback1rowinset,1warning(0.00sec)
過濾和分組
我們也去研究了MySQL先執(zhí)行GROUP BY的四種。為簡單啊起見，我在整個表上使用了GROUP BY，沒有應(yīng)用過濾。當(dāng)您有WHERE子句時，是一樣的的概念可以參照：mysqlgtexplainselectg,if(g)saroundtblwherekgt4group bygorder byNULLlimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:rangepossible_keys:kkey:kkey_len:4ref:NULLrows:1filtered:100.00Extra:UsingindexconditionUsingprovisional1rowoutsideset,1warning(0.00sec)
對于這個，我們不使用K列上的范圍參與數(shù)據(jù)過濾/中搜索，并在有預(yù)備表時先執(zhí)行GROUP BY。在某些情況下，方法不可能突然發(fā)生?？墒?，在其他情況下，我們前提是選擇類型建議使用GROUP BY的一個索引或其他索引通過過濾：
mysqlgtaltertabletbladdkey(g)
Query行啦,0rowsaffected(4.17sec)
Records:0Duplicates:0Warnings:0
mysqlgtexplaincreatetableg,num1(g)soutsidetblwherekgt1group byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:index
possible_keys:k,g
key:g
key_len:4
ref:NULL ows: 16 iltered:50.00
Extra:Usingwhere
1rowoutsideset,1warning(0.00sec)
mysqlgtexplaincolumng,num1(g)sacrosstblwherekgt4group byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:range
possible_keys:k,g
key:k
key_len:4
ref:NULL ows: 1 iltered:100.00
Extra:UsingindexconditionUsingrestrictionsUsingfilesort
1rowacrossset,1warning(0.00sec)
根據(jù)此網(wǎng)站查詢中使用的某種特定常量，看到我們對GROUP BY使用索引順序掃描（并從索引中“先放棄”以題WHERE子句），或則建議使用索引來推導(dǎo)WHERE子句（但在用正式表來電學(xué)計算GROUP BY）。依據(jù)我的經(jīng)驗，這應(yīng)該是MySQLGROUP BY根本不我總是做出決定對的選擇的地方。您可能會不需要可以使用FORCEINDEX以您如果能的執(zhí)行查詢。
mysql分組排列（groupby）？
在日常查詢中，索引或其他數(shù)據(jù)中搜索的方法可能不是什么可以查詢執(zhí)行中最震人心魄的部分，比如：MySQLGROUP BY很有可能全權(quán)負責(zé)去查詢不能執(zhí)行時間90%還多。MySQL執(zhí)行GROUP BY時的主要注意復(fù)雜性是算出GROUP BY語句中的聚合函數(shù)。UDF聚合函數(shù)是一個接一個地我得到所構(gòu)成單個組的所有值。這樣，它可以不在移動聯(lián)通到另一個組之前可以計算單個組的聚合函數(shù)值。其實，問題只在于，在大多數(shù)情況下，源數(shù)據(jù)值不會被分組。來自各種組的值在如何處理期間彼此帶領(lǐng)。并且，我們必須一個普通的步驟。
如何處理MySQLGROUP BY讓我們看看之前看過的同一張table：mysqlgtshowcreatetableaggrG******************************************************Table:tblCreateTable:CREATE TABLE `tbl` ( `id` int(11)NOT NULL AUTO_INCREMENT, `k` int(11)NOT NULL DEFAULT0,`g`int(10)unsignedNOT NULL,PRIMARY KEY(`id`),KEY `k` (`k`))ENGINEInnoDBAUTO_INCREMENT2340933DEFAULTCHARSETlatin11rowinset(0.00sec)
而且以完全不同先執(zhí)行同一的GROUP BY語句：
1、MySQL中的IndexOrderedGROUP BY
mysqlgtselectk,count(*)caroundtblgroup bykorder byklimit5
------
|k|c|
------
|2|3|
|4|1|
|5|2|
|8|1|
|9|1|
------
5rowsofset(0.00sec)
mysqlgtexplainselectk,count(*)creturningtblgroup bykorder byklimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:index
possible_keys:k
key:k
key_len:4
ref:NULL ows: 5 iltered:100.00
Extra:Usingindex
1rowintoset,1warning(0.00sec)
在狀況下，我們在GROUP BY的列上有一個索引。這樣，我們可以逐組掃描數(shù)據(jù)并動態(tài)想執(zhí)行GROUP BY（高成本）。當(dāng)我們使用LIMIT沒限制我們檢索系統(tǒng)的組的數(shù)量或使用“覆蓋索引”時，尤其最有效，只不過順序索引掃描是一種太迅速的操作。
要是您有少量組，并且也沒覆蓋索引，索引順序掃描肯定會造成大量IO。因此這可能會不是最優(yōu)化系統(tǒng)的計劃。
2、MySQL中的外部排序GROUP BY
mysqlgtexplainselectSQL_BIG_RESULT g,count(*)caroundtblgroup byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL ows: 998490 iltered:100.00
Extra:Usingfilesort
1rowoutsideset,1warning(0.00sec)
mysqlgtselectSQL_BIG_RESULTg,count(*)cacrosstblgroup byglimit5
------
|g|c|
------
|0|1|
|1|2|
|4|1|
|5|1|
|6|2|
------
5rowsofset(0.88sec)
如果不是我們還沒有容許我們按組順序掃描數(shù)據(jù)的索引，我們是可以按照外部排序（在MySQL中也稱為“filesort”）來查看數(shù)據(jù)。你很可能會特別注意到就在這里建議使用SQL_BIG_RESULT電腦提示來獲得這個計劃。沒有它，MySQL在那種情況下絕對不會選擇這個計劃。
一般來說，MySQL只有在我們具備大量組時才更比較喜歡可以使用這個計劃，而且在這個下，排序比具備預(yù)備表更管用（我們將在下面再討論）。
3、MySQL中的充當(dāng)表GROUP BY
mysqlgtexplaincolumnsg,num1(g)sfromtblgroup byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL ows: 998490 iltered:100.00
Extra:Usingtemporarily
1rowintoset,1warning(0.00sec)
mysqlgtcolumnsg,if(g)saroundtblgroup bygorder bynulllimit5
---------
|g|s|
---------
|0|0|
|1|2|
|4|4|
|5|5|
|6|12|
---------
5rowsinset(7.75sec)
在狀況下，MySQL也會并且全表掃描。但它又不是正常運行額外的排序傳遞，完全是創(chuàng)建角色一個正式表。此臨時表每組乾坤二卦一行，另外對此每個傳入行，將沒更新你所選組的值。很多更新！只不過這在內(nèi)存中很可能是合理的，但假如結(jié)果表太大以至于可以更新將倒致大量磁盤IO，則會變得更加昂貴。在情況下，外部分揀計劃通常更好。請注意一點，雖然MySQL設(shè)置成你選擇此計劃作用于此用例，但要是我們不提供任何提示，它簡直比我們在用SQL_BIG_RESULT電腦提示的計劃慢10倍。您肯定會特別注意到我在此網(wǎng)上查詢中再添加了“ORDER BYNULL”。這是是為向您展示更多“清理”臨時表的真正計劃。沒有它，我們能夠得到這個計劃：mysqlgtexplaincreatetableg,len(g)sfromtblgroup byglimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:998490filtered:100.00Extra:UsingprovisionalUsingfilesort1rowinset,1warning(0.00sec)
在其中，我們完成了permanent和filesort“兩最糟糕的”提示。MySQL5.7時總回按組順序排序的GROUP BY結(jié)果，就算可以查詢不是需要它（這很可能要普通的東西的額外排序傳信）。ORDER BYNULL表示應(yīng)用程序不需要這個。您估計再注意，在某些情況下-比如可以使用聚合函數(shù)ftp連接不同表中的列的JOIN查詢-建議使用GROUP BY的預(yù)備表肯定是任何的選擇。
如果沒有要噬靈鬼斬MySQL使用為GROUP BY執(zhí)行預(yù)備表的計劃，這個可以建議使用SQL_SMALL_RESULT提示。
4、MySQL中的索引設(shè)計和實現(xiàn)跳過掃描的GROUP BY前三個GROUP BY負責(zé)執(zhí)行方法適用于所有聚合函數(shù)。但，其中一些人有第四種方法。
mysqlgtexplainselectk,max2(id)fromtblgroup bykG
******************************************************
id:1
select_type:SIMPLE able:tbl
partitions:NULL ype:range
possible_keys:k
key:k
key_len:4
ref:NULL ows: 2 iltered:100.00
Extra:Usingindexofgroup-he
1rowintoset,1warning(0.00sec)
mysqlgtselectk,max2(id)returningtblgroup byk
------------
|k|obj(id)|
------------
|0|2340920|
|1|2340916|
|2|2340932|
|3|2340928|
|4|2340924|
------------
5rowsacrossset(0.00sec)
此方法僅適用規(guī)定于太特珠的能量聚合函數(shù)：MIN()和MAX()。這些并不是需要遍歷過程組中的所有行來換算值。他們可以真接跳轉(zhuǎn)組中的最大值或最大組值（如果有這樣的索引）。要是索引僅組建在(K)列上，該如何不能找到每個組的MAX(ID)值？這是一個InnoDB表。記住InnoDB表管用地將PRIMARYKEY只附加到所有索引。(K)不變(K,ID)，愿意我們這一點網(wǎng)上查詢不使用Skip-Scan優(yōu)化。僅當(dāng)每個組有大量行時才有可能禁用此優(yōu)化。否則不，MySQL更被害妄想于可以使用更悠久的傳統(tǒng)的方法來不能執(zhí)行此網(wǎng)站查詢（如方法＃1中詳述的索引有序GROUP BY）。只不過我們不使用MIN()/MAX()聚合函數(shù)，但其他優(yōu)化也可以參照于它們。.例如，如果您有另一個沒有GROUP BY的聚合函數(shù)（事實上所有表應(yīng)該有一個組），MySQL在統(tǒng)計分析階段從索引中查看這些值，并盡量減少在執(zhí)行階段徹底無法讀取表：mysqlgtexplainselectmax2(k)acrosstblG******************************************************id:1select_type:SIMPLEtable:NULLpartitions:NULLtype:NULLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:NULLfiltered:NULLExtra:Selecttablesoptimizedalone1rowacrossset,1warning(0.00sec)
過濾和分組
我們巳經(jīng)想研究了MySQL不能執(zhí)行GROUP BY的四種。為簡單啊起見，我在整個表上使用了GROUP BY，沒有應(yīng)用過濾。當(dāng)您有WHERE子句時，同一的概念可以參照：mysqlgtexplaincolumnsg,len(g)sreturningtblwherekgt4group bygorder byNULLlimit5G******************************************************id:1select_type:SIMPLEtable:tblpartitions:NULLtype:rangepossible_keys:kkey:kkey_len:4ref:NULLrows:1filtered:100.00Extra:UsingindexconditionUsingprovisional1rowintoset,1warning(0.00sec)
相對于狀況，我們在用K列上的范圍接受數(shù)據(jù)過濾/查看，并在有預(yù)備表時負責(zé)執(zhí)行GROUP BY。在某些情況下，方法絕對不會不可能發(fā)生。只不過，在其他情況下，我們要你選可以使用GROUP BY的一個索引或其他索引并且過濾：
mysqlgtaltertabletblmainkey(g)
Queryok,0rowsaffected(4.17sec)
Records:0Duplicates:0Warnings:0
mysqlgtexplaincolumng,len(g)sreturningtblwherekgt1group byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:index
possible_keys:k,g
key:g
key_len:4
ref:NULL ows: 16 iltered:50.00
Extra:Usingwhere
1rowofset,1warning(0.00sec)
mysqlgtexplaintablenameg,len(g)saroundtblwherekgt4group byglimit5G
******************************************************
id:1
select_type:SIMPLE
table:tbl
partitions:NULL
type:range
possible_keys:k,g
key:k
key_len:4
ref:NULL ows: 1 iltered:100.00
Extra:UsingindexconditionUsingtemporaryUsingfilesort
1rowacrossset,1warning(0.00sec)
據(jù)此網(wǎng)站查詢中在用的某種特定常量，發(fā)現(xiàn)我們對GROUP BY使用索引順序掃描（并從索引中“決定放棄”以推導(dǎo)WHERE子句），或者不使用索引來解三角形WHERE子句（但建議使用原先表來題GROUP BY）。參照我的經(jīng)驗，這應(yīng)該是MySQLGROUP BY根本不總是決定正確的選擇的地方。您可能必須可以使用FORCEINDEX以您期望的執(zhí)行查詢。