MySQL百萬級、千萬級資料多表關聯SQL語句調優

標籤：join   文法   sql語句   time   null   status   sele   自己   TE   

本文不涉及複雜的底層資料結構，通過explain解釋SQL，並根據可能出現的情況，來做具體的最佳化，使百萬級、千萬級資料表關聯查詢第一頁結果能在2秒內完成(真實業務警示系統最佳化結果)。希望讀者能夠理解SQL的執行過程，並根據過程最佳化，走上自己的"成金之路" 需要最佳化的查詢：使用explain     出現了Using temporary；     有分頁時出現了Using filesort則表示使用不了索引，需要根據下面的技巧來調整語句     rows過多，或者幾乎是全表的記錄數；     key 是 (NULL)；     possible_keys 出現過多（待選）索引。 1.使用explain文法，對SQL進行解釋，根據其結果進行調優：     MySQL 表關聯的演算法是 Nest Loop Join，是通過驅動表的結果集作為迴圈基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果：          a.EXPLAIN 結果中，第一行出現的表就是驅動表           b. 對驅動表可以直接排序，對非驅動表（的欄位排序）需要對迴圈查詢的合并結果（暫存資料表）進行排序（Important!），即using temporary;           c. [驅動表] 的定義為：1）指定了聯結條件時，滿足查詢條件的 記錄行數少的表為[驅動表]；2）未指定聯結條件時， 行數少的表為[驅動表]（Important!）。           d. 最佳化的目標是儘可能減少JOIN中Nested Loop的迴圈次數，以此保證： 永遠用小結果集驅動大結果集（Important!）！：A JOIN B，A為驅動，A中每一行和B進行迴圈JOIN，看是否滿足條件，所以當A為小結果集時，越快。                    e.NestedLoopJoin實際上就是通過驅動表的結果集作為迴圈基礎資料，然後一條一條的通過該結果集中的資料作為過濾條件到下一個表中查詢資料，然後合并結果。如果還有第三個參與Join， 則再通過前兩個表的Join結果集作為迴圈基礎資料，再一次通過迴圈查詢條件到第三個表中查詢資料，如此往複 2.兩表JOIN最佳化：     a.當無order by條件時，根據實際情況，使用left/right/inner join即可，根據explain最佳化 ；     b.當有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；     1）如果第一行的驅動表為a，則效率會非常高，無需最佳化；     2）否則，因為只能對驅動表欄位直接排序的緣故，會出現using temporary，所以此時需要使用 STRAIGHT_JOIN明確a為驅動表，來達到使用a.col上index的最佳化目的； 或者使用left join且Where條件中不含b的過濾條件，此時的結果集為a的全集， 而STRAIGHT_JOIN為inner join且使用a作為驅動表 3.多表JOIN最佳化：     a.無order by條件時，根據實際情況，使用left/right/inner join即可，根據explain最佳化；     b.有order by a.col條件時，所有join必須為left join，且每個join欄位都建立索引，同時where條件中只能有a表的條件，即將其它表的資料關聯到a中形成一張大表，再對a的全集進行過濾；          如果不能全使用left join，則需靈活使用 STRAIGHT_JOIN及其它技巧，以時間排序為例：                1)資料入庫按照平台時間入庫，自然a的資料都按時間有序；SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c  STRAIGHT_JOIN res_node r ON c.res_node_id = r.ID  STRAIGHT_JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58  STRAIGHT_JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 limit 1,10; SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c  inner JOIN res_node r ON c.res_node_id = r.ID  INNER JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58  INNER JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79  order by c.changed_time limit 1,10; 兩者結果一致 4.誤區：     a.視圖只是屏蔽或者高效集合多表資料的一種方法，視圖與表JOIN，不會起到任何效果

MySQL百萬級、千萬級資料多表關聯SQL語句調優