MySQL高級(jí) 第11章排序分組優(yōu)化

where 條件和 on的判斷這些過(guò)濾條件，作為優(yōu)先優(yōu)化的部門，是要被先考慮的！其次，如果有分組和排序，那么也要考慮grouo by 和order by。

1. 無(wú)過(guò)濾不索引

create index idx_age_deptid_name on emp (age,deptid,name);

explain select * from emp where age=40 order by deptid;

explain ?select * from emp order by age,deptid;

explain ?select * from emp order by age,deptid limit 10;

4. 索引的選擇

①首先，清除emp上面的所有索引，只保留主鍵索引！

drop index idx_age_deptid_name on emp;

②查詢：年齡為30歲的，且員工編號(hào)小于101000的用戶，按用戶名稱排序

explain SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

③全表掃描肯定是不被允許的，因此我們要考慮優(yōu)化。

思路：首先需要讓where的過(guò)濾條件，用上索引；

查詢中，age.empno是查詢的過(guò)濾條件，而name則是排序的字段，因此我們來(lái)創(chuàng)建一個(gè)此三個(gè)字段的復(fù)合索引：

create index idx_age_empno_name on emp(age,empno,name);

再次查詢，發(fā)現(xiàn)using filesort依然存在。

原因： empno是范圍查詢，因此導(dǎo)致了索引失效，所以name字段無(wú)法使用索引排序。

所以，三個(gè)字段的符合索引，沒(méi)有意義，因?yàn)閑mpno和name字段只能選擇其一！

 

④解決： 魚與熊掌不可兼得，因此，要么選擇empno,要么選擇name

drop index idx_age_empno_name on emp;

create index idx_age_name on emp(age,name);

create index idx_age_empno on emp(age,empno);

兩個(gè)索引同時(shí)存在，mysql會(huì)選擇哪個(gè)？

原因：所有的排序都是在條件過(guò)濾之后才執(zhí)行的，所以如果條件過(guò)濾了大部分?jǐn)?shù)據(jù)的話，幾百幾千條數(shù)據(jù)進(jìn)行排序其實(shí)并不是很消耗性能，即使索引優(yōu)化了排序但實(shí)際提升性能很有限。 ?相對(duì)的 empno<101000 這個(gè)條件如果沒(méi)有用到索引的話，要對(duì)幾萬(wàn)條的數(shù)據(jù)進(jìn)行掃描，這是非常消耗性能的，使用empno字段的范圍查詢，過(guò)濾性更好（empno從100000開(kāi)始）！

 

結(jié)論： 當(dāng)范圍條件和group by 或者 order by ?的字段出現(xiàn)二選一時(shí) ，優(yōu)先觀察條件字段的過(guò)濾數(shù)量，如果過(guò)濾的數(shù)據(jù)足夠多，而需要排序的數(shù)據(jù)并不多時(shí)，優(yōu)先把索引放在范圍字段上。反之，亦然。

5. using filesort?

5.1 mysql的排序算法

①雙路排序

MySQL 4.1之前是使用雙路排序,字面意思就是兩次掃描磁盤，最終得到數(shù)據(jù)，讀取行指針和orderby列，對(duì)他們進(jìn)行排序，然后掃描已經(jīng)排序好的列表，按照列表中的值重新從列表中讀取對(duì)應(yīng)的數(shù)據(jù)輸出。

從磁盤取排序字段，在buffer進(jìn)行排序，再?gòu)拇疟P取其他字段。

簡(jiǎn)單來(lái)說(shuō)，取一批數(shù)據(jù)，要對(duì)磁盤進(jìn)行了兩次掃描，眾所周知，I\O是很耗時(shí)的，所以在mysql4.1之后，出現(xiàn)了第二種改進(jìn)的算法，就是單路排序。

②單路排序

從磁盤讀取查詢需要的所有列，按照order by列在buffer對(duì)它們進(jìn)行排序，然后掃描排序后的列表進(jìn)行輸出，

它的效率更快一些，避免了第二次讀取數(shù)據(jù)。并且把隨機(jī)IO變成了順序IO,但是它會(huì)使用更多的空間，

因?yàn)樗衙恳恍卸急４嬖趦?nèi)存中了。

③單路排序的問(wèn)題

由于單路是后出的，總體而言好過(guò)雙路。但是存在以下問(wèn)題：

在sort_buffer中，方法B比方法A要多占用很多空間，因?yàn)榉椒˙是把所有字段都取出, 所以有可能取出的數(shù)據(jù)的總大小超出了sort_buffer的容量，導(dǎo)致每次只能取sort_buffer容量大小的數(shù)據(jù)，進(jìn)行排序（創(chuàng)建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……從而多次I/O。

???結(jié)論：本來(lái)想省一次I/O操作，反而導(dǎo)致了大量的I/O操作，反而得不償失。

5.2 如何優(yōu)化

①增大sort_butter_size參數(shù)的設(shè)置

不管用哪種算法，提高這個(gè)參數(shù)都會(huì)提高效率，當(dāng)然，要根據(jù)系統(tǒng)的能力去提高，因?yàn)檫@個(gè)參數(shù)是針對(duì)每個(gè)進(jìn)程的??1M-8M之間調(diào)整。

②增大max_length_for_sort_data參數(shù)的設(shè)置

mysql使用單路排序的前提是排序的字段大小要小于max_length_for_sort_data。

提高這個(gè)參數(shù)，會(huì)增加用改進(jìn)算法的概率。但是如果設(shè)的太高，數(shù)據(jù)總?cè)萘砍鰏ort_buffer_size的概率就增大，明顯癥狀是高的磁盤I/O活動(dòng)和低的處理器使用率。（1024-8192之間調(diào)整）。

③減少select 后面的查詢的字段。

當(dāng)Query的字段大小總和小于max_length_for_sort_data 而且排序字段不是?TEXT|BLOB 類型時(shí)，會(huì)用改進(jìn)后的算法——單路排序，?否則用老算法——多路排序。

兩種算法的數(shù)據(jù)都有可能超出sort_buffer的容量，超出之后，會(huì)創(chuàng)建tmp文件進(jìn)行合并排序，導(dǎo)致多次I/O，但是用單路排序算法的風(fēng)險(xiǎn)會(huì)更大一些,所以要提高sort_buffer_size。

6. 使用覆蓋索引

覆蓋索引：SQL只需要通過(guò)索引就可以返回查詢所需要的數(shù)據(jù)，而不必通過(guò)二級(jí)索引查到主鍵之后再去查詢數(shù)據(jù)。

7. group by

group by 使用索引的原則幾乎跟order by一致 ，唯一區(qū)別是groupby 即使沒(méi)有過(guò)濾條件用到索引，也可以直接使用索引。