MYSQL如何優(yōu)化group by

這篇文章主要為大家展示了“MYSQL如何優(yōu)化group by”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MYSQL如何優(yōu)化group by”這篇文章吧。

一般來(lái)都有這樣一個(gè)說(shuō)法，MYSQL 表的數(shù)據(jù)超過(guò)500萬(wàn)行就不行了，而在這個(gè)說(shuō)法之后就是MYSQL 的group by 的性能奇差無(wú)比。

如果要用一句話(huà)來(lái)說(shuō)，你把MYSQL 當(dāng)其他數(shù)據(jù)庫(kù)用了（PG, SQL SERVER ,ORACLE），所招致的結(jié)果。

select last_name,count(*) from employees where hire_date between '1990-01-01' and '2000-01-01' group by last_name limit 10;

上面是一個(gè)查詢(xún)語(yǔ)句

下面是有索引，沒(méi)索引，不同索引的查詢(xún)時(shí)間

三種情況，最后將索引落在分組字段的情況下，查詢(xún)的時(shí)間是最短的。

因?yàn)間roup by實(shí)際上執(zhí)行相同的排序操作，所以group by基本上只是排序后的分組操作，這樣，我們就可以一組一組地掃描數(shù)據(jù)，并動(dòng)態(tài)地執(zhí)行組。所以在有where 后的條件的索引和GROUP BY 的字段的索引，這樣的情況大概率的可能性選擇的是分組的索引來(lái)進(jìn)行相關(guān)的查詢(xún)。

當(dāng)然我們也可以通過(guò)，一些參數(shù)來(lái)強(qiáng)制系統(tǒng)查詢(xún)的預(yù)期結(jié)果，例如 SQL_SMALL_RESULT , SQL_BIG_RESULT , SQL_BUFFER_RESULT

我們可以看到三種強(qiáng)制的預(yù)期，

1 我們的group by 或 distinct 操作的數(shù)據(jù)結(jié)果集是比較大的，則使用big_result，MYSQL會(huì)在磁盤(pán)創(chuàng)建臨時(shí)表，并且很可能走全表掃描的方式

2 如果我們的預(yù)設(shè)的結(jié)果集比較小，則結(jié)果集會(huì)在內(nèi)存中進(jìn)行存儲(chǔ)，大家可以看到連香港的 file sort 都不在存在

3 如果希望更快的解鎖查詢(xún)的表，可以選擇buffer_result, 將盡快的將表解鎖并且將結(jié)果存儲(chǔ)在本地機(jī)，而不是 直接 send data

下面我們來(lái)看一個(gè)稍微復(fù)雜的查詢(xún)

select d.dept_no,count(s.salary) as count_salary from employees as e 

    -> left join salaries as s on e.emp_no = s.emp_no 

    -> left join dept_emp as d on d.emp_no = e.emp_no

    -> where e.gender = 'M'

    -> group by d.dept_no;

查詢(xún)的主要目的查詢(xún)是男性的每個(gè)部門(mén)的總的工資消耗

可以看到基本上查詢(xún)?cè)诓坏?秒的時(shí)間，如何優(yōu)化這樣的查詢(xún)?cè)贛YSQL中。

首先查詢(xún)的時(shí)間過(guò)長(zhǎng)是一個(gè)問(wèn)題，有的時(shí)候我們的想法一般是怎么讓這個(gè)語(yǔ)句更快的出結(jié)果，而加各種的索引，而實(shí)際中語(yǔ)句的優(yōu)化的另一種想法是怎么能讓鎖表的時(shí)間更短，看上去這兩者不矛盾，但實(shí)際當(dāng)然其實(shí)可能是兩種截然不同的思路。

例如上面的語(yǔ)句我這樣操作，首先獲得所有的部門(mén)分組信息的dept_no

將其保存在程序的緩存中，然后

通過(guò)下面的語(yǔ)句將每個(gè)部門(mén)的工資總和獲取后，在進(jìn)行累加的計(jì)算（這使用程序來(lái)做不是一件困難的事情），最后獲得總體的和上面語(yǔ)句一樣的結(jié)果，而經(jīng)過(guò)實(shí)際的操作，整體的查詢(xún)九個(gè)部門(mén)的工資最長(zhǎng)的不過(guò)0.34秒，最短的僅僅0.02秒。整體的查詢(xún)9次累加的耗時(shí)都不超過(guò)1 秒。

select d.dept_no,count(s.salary) as count_salary from employees as e  left join salaries as s on e.emp_no = s.emp_no  left join dept_emp as d on d.emp_no = e.emp_no where e.gender = 'M' and d.dept_no = 'd009';

通過(guò)這樣的查詢(xún)方法，總比死在怎么整體優(yōu)化一條SQL 要好的多，語(yǔ)句優(yōu)化，一定要靈活，不要一根筋。當(dāng)然遇到類(lèi)似的情況也要分析，如果遇到GROUP BY 就用這樣的方法，其實(shí)還是一根筋。

以上是“MYSQL如何優(yōu)化group by”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！