MySQL聯表查詢優化

sql執行順序

執行FROM語句

執行ON過濾

join添加外部行

執行where條件過濾

執行group by以及分組語句，（開始使用select中的別名，后面的語句中都可以使用別名）

執行having

select列表

執行distinct去重復數據

執行order by字句

執行limit字句

多表聯合查詢優化建議

1、使用顯示連接left join（right join，inner join），盡量避免隱式連接（where逗號連接表 .... and .... and ...）這類寫法，假設三張表每張表有一千條數據，本意想查出<=1000條數據，當使用where語句查詢，就查出了1000*1000*1000=10億條數據，很大程度上浪費了內存執行時間?

ps：在不使用on語法時，join、inner join、逗號、cross join結果相同，都是取2個表的笛卡爾積。逗號與其他操作符優先級不同，所以有可能產生語法錯誤，盡量減少用逗號

2、需要哪些列就查哪些列，不要有很多冗余的列查詢出來，有的時候一張表當中有好幾十個字段，我們需要的可能就是其中的三四個或者四五個字段，在這樣的情況下，我們就直接查這幾個我們需要的字段就可以了

3、盡量避免使用 .* ，因為使用點* 需要先去數據字典當中查找你所查找的表當中所擁有的字段，再轉換成對應的字段的放在select后面查詢出來

4、優先使用大于等于，比大于執行效率高

5、查詢的時候我們應該把更具有限制條件的條件語句放在最前面，比如我們有一張學生成績表(score)，分別有學號、語數英三科成績以及總成績總共五列，要查找數學、英語優秀，語文及格，總成績再前一百名的人

select * from score where sno in(select sno from score where language>60 and math>80 and english>80 order by total_score desc)(慢) 
select sno,language,math,english,total_score from score where exist (select sno from where engilsh>=80 and math>=80 and language>=80 order by total_score desc)(快)

上面那條語句將大于60分的條件放前面，大于80的放后面，導致很多情況下多查了很多數據

就比如說一張表里有有很多字段，有一百萬條記錄，主鍵id由1到1百萬，當我們需要查找小于1000大于100的數據的時候，我們就應該把小于1000這個條件放前面，這就是相對比下最具限制性的條件

6、盡量使用連接查詢 替代 子查詢，因為子查詢需要建立/銷毀臨時表，開銷昂貴

select a.id,a.name from a where a.id in(select b.aid from b where b.id=123);
select a.id,a.name from a inner join b on a.id=b.aid wehre b.id=123;

子查詢執行表現為，外表遍歷每一條，內表都需要掃描一次，邊遍歷查詢外表，邊掃描內表；

如果數量較大，則使用連接查詢，因為子查詢會掃描多次；

如果數據量較小，則子查詢與連接查詢對比不明顯

如果需要用到子查詢：

6.1、用EXISTS（或內連接）替代IN、用NOT EXISTS（或者外連接）替代NOT IN

6.2、用EXISTS替換DISTINCT

7、where條件盡量使用索引，避免在索引列使用計算（加減乘除），避免索引列使用函數（轉換類型），避免索引列使用is（not）null，避免索引列使用通配符，否則數據庫將放棄索引，執行全表掃描

8、where代替having，優化group by

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉，如下

低效: SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP BY JOB

9、Order By語句加在索引列，最好是主鍵PK上

10、用EXISTS替換DISTINCT

當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結果

11、in是把外表和內表作hash連接，而exists是對外表作loop循環，每次loop循環再對內表進行查詢，一直以來認為exists比in效率高的說法是不準確的。如果查詢的兩個表大小相當，那么用in和exists差別不大；如果兩個表中一個較小一個較大，則子查詢表大的用exists，子查詢表小的用in（減少遍歷次數）

12、字符串型=，in，like’abc%‘索引生效；!=, not in, like'%abc', like'a%bc'索引失效

13、數值型=, !=, in, not in都可以索引生效

索引一般性建議

對于單鍵索引，盡量選擇針對當前query過濾性更好的索引

在選擇組合索引的時候，當前Query中過濾性最好的字段在索引字段順序中，位置越靠前越好

在選擇組合索引的時候，盡量選擇可以能夠包含當前query中的where子句中更多字段的索引

盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的

索引口訣

全職匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之后全失效

like百分寫最右，覆蓋索引不寫星

不等空值還有or，索引失效要少用

var引號不可丟，SQL高級也不難

審核編輯：黃飛