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

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

1. 無過濾不索引

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歲的，且員工編號小于101000的用戶，按用戶名稱排序

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

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

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

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

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

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

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

所以，三個字段的符合索引，沒有意義，因為empno和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);

兩個索引同時存在，mysql會選擇哪個？

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

 

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

5. using filesort?

5.1 mysql的排序算法

①雙路排序

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

從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段。

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

②單路排序

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

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

因為它把每一行都保存在內(nèi)存中了。

③單路排序的問題

由于單路是后出的，總體而言好過雙路。但是存在以下問題：

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

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

5.2 如何優(yōu)化

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

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

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

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

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

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

當Query的字段大小總和小于max_length_for_sort_data 而且排序字段不是?TEXT|BLOB 類型時，會用改進后的算法——單路排序，?否則用老算法——多路排序。

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

6. 使用覆蓋索引

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

7. group by

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