索引的選擇
①首先，清除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 ;
③全表掃描肯定是不被允許的，因此我們要考慮優化。
思路：首先需要讓where的過濾條件，用上索引；
查詢中，age.empno是查詢的過濾條件，而name則是排序的字段，因此我們來創建一個此三個字段的複合索引：
create index idx_age_empno_name on emp(age,empno,name);
再次查詢，發現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會選擇哪個？
explain SELECT SQL_NO_CACHE * FROM emp use index(idx_age_name) WHERE age =30 AND empno <101000 ORDER BY NAME ;
原因：所有的排序都是在條件過濾之後才執行的，所以如果條件過濾了大部分數據的話，幾百幾千條數據進行排序其實並不是很消耗性能，即使索引優化了排序但實際提升性能很有限。 相對的 empno<101000 這個條件如果沒有用到索引的話，要對幾萬條的數據進行掃描，這是非常消耗性能的，使用empno字段的範圍查詢，過濾性更好（empno從100000開始）！
結論： 當範圍條件和group by 或者 order by 的字段出現二選一時 ，優先觀察條件字段的過濾數量，如果過濾的數據足夠多，而需要排序的數據並不多時，優先把索引放在範圍字段上。反之，亦然。
原創文章，作者：投稿專員，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/275027.html
微信掃一掃 
支付寶掃一掃 