mysql（九）MySQL如何執行關聯查詢

MySQL中“關聯”一次所包含的意義比一般意義上了解的要更廣泛。總的來說，MySQL認為任何一個查詢都是一次“關聯”——并不僅僅是一個查詢需要到兩個表比對才叫關聯，是以在MySQL中，每一個查詢，每一個片段（包括子查詢，甚至基于單表的SELECT）都可能是關聯。

是以了解MySQL如何執行關聯查詢至關重要。我們先來看一個UNION查詢的例子。對于UNION查詢，MySQL先将一系列的單個查詢結果放到一個臨時表中，然後再重新讀出臨時表資料來完成UNION查詢。在MySQL的概念中，每個查詢都是一次關聯，是以讀取結果臨時表時也是一次關聯。

目前MySQL關聯執行的政策很簡單：MySQL對任何關聯都執行嵌套循環關聯操作，即MySQL現在一個表中循環取出單條資料，然後再嵌套循環到下一個表中尋找比對的行，依次下去，直到找到所有表中比對的行為止。然後根據各個表比對的行，傳回查詢中需要的各個列。

按照這樣的方式查找第一個表記錄，再嵌套查詢下一個關聯表，然後回溯到上一個表，在MySQL中是通過嵌套循環的方式實作——正如其名“嵌套循環關聯”。請看下面的例子中的簡單查詢：

mysql>select tbl1.col1,tbl2.col2

          ->from tbl1 inner join tbl2 using(col3)

           ->where tbl1.col1 in(5,6);

假設MysQL按照查詢中的表順序進行關聯操作，我們則可以用下面的僞代碼表示MySQL将如何完成這個查詢：

List tbl1 = tbl中col1為5，6的行；
        for(tbrow : tb1){
            List tb2 = （tbl2中col3=tbrow中col3的行）
            for(tb2row : tb2){
                輸出【tbl1.col1,tbl2.col2】
            }
        }
           

上面的查詢對于單表和多變關聯查詢都适用，如果是一個單表查詢，那麼隻需完成上面外層的基本操作。對于外連接配接上面的執行過程仍然适用。例如，我們将上面查詢修改如下：

mysql>select tbl1.col1,tbl2.col2

         ->from tbl1 left join tbl2 using(col3)

          ->where tbl1.col1 in(5,6)

對應的僞代碼如下，我們用黑體标示出不同的部分：

List tbl1 = tbl中col1為5，6的行；
        for(tb1row : tb1){
            List tb2 = （tbl2中col3=tb1row中col3的行）
            for(tb2row : tb2){
                if(tbl1row.col3== tbl12row.col3) {
                    輸出[tbl1.col1, tbl2.col2]
                }else{
                    輸出【tbl1.col1,null】
                }
            }
        }
           

從本質上說，MySQL對所有的類型的查詢都以同樣的方式運作。例如，MySQL在FROM子句中遇到子查詢時，先執行子查詢并将其結果放到一個臨時表中,然後将這個臨時表當作一個普通表對待（正如其名“派生表”）。MySQL在執行UNION查詢時也使用類似的臨時表，在遇到又外連接配接的時候MySQL将其改為等價的左外連接配接。簡而言之MysQL會将所有的查詢類型都轉換成類似的執行計劃。

執行計劃

和很多其他關系資料庫不同，MySQL并不會生成查詢位元組碼來執行查詢。MySQL生成查詢的一棵指令樹，然後通過存儲引擎執行完成這顆指令樹并傳回結果。最終的執行計劃包含了重構查詢的全部資訊。如果對某個查詢執行explain extended後，在執行show warnings，就可以看到重構出的查詢。

任何多表查詢都可以用一棵樹表示，mysql總是從一個表開始一直嵌套循環，回溯完成所有表關聯。是以，MySQL的執行計劃總是如下圖所示：，這是一棵左側深度優先的樹。

關聯查詢中，任何的子查詢都可以被改編成左連接配接或者内連接配接，但他們的查詢效率因為查詢執行器的關系可能會有所不同，例如下面這句： mysql>select * from sakila.film          ->where film_id in(select film_id from sakila.film_actor where actor_id = 1); 我們可能以為它會先執行in語句裡面的句子，再做外部關聯，實際上，查詢執行器，因為film_id要關聯外部表，是以我們可以通過（explain extended來看這個查詢被改成了什麼樣子）： select * from sakila.film where exists(selct * from sakila.film_actor where actor_id =1 and film_actor.film_id = film.film_id);

通過explain我們可以看到，子查詢是一個相關子查詢 ---------------------------------------------------------------------------------------------------------------------------- id       select_type                                  table             type                 possible_keys 1         primary                                         film               ALl                     null 2    DEPENDENT  SUBQUERY      film_actor       eq_ref          primary,idx_fk_film_id

根據explain我們可以看到，MySQL先選擇對file表進行全表掃描，然後根據傳回的film_id逐個執行子查詢。這個時候我們可以通過重寫這個查詢： mysql>select film.* from sakila.film          ->inner join sakila.film_actor using(film_id)           ->where actor_id = 1;

但也并不是所有的左連接配接或者内連結查詢都比子查詢要快，我們這時候就要記住一條準則：通過explain檢視執行具體細節，通過explain extended檢視查詢執行器改寫後的語句,執行show warnings會将上句查詢的sql優化後的語句查詢出來。

上面的查詢對于單表和多變關聯查詢都适用，如果是一個單表查詢，那麼隻需完成上面外層的基本操作。對于外連接配接上面的執行過程仍然适用。