多表關聯查詢中，關聯欄位都應該建立索引嗎？

尋找的路上發表於2021-03-25
索引

前言

​ 關於mysql的學習，之前都是在網上看各種視訊學習的，所以有些知識點半信半疑。後來看了《高效能Mysql第三版》這本書，雖然只能消化一部分知識點，但有些疑點是可以解決的。

​ 多表關聯查詢中，關聯欄位都應該建立索引嗎？

答案：不應該。

過程分析

Mysql是如何進行關聯查詢的？

​ 當前Mysql關聯執行的策略很簡單：Mysql對任何關聯都執行__巢狀迴圈關聯__操作（類似於多個for迴圈巢狀），即Mysql先在一個表中迴圈取出單條資料，然後再巢狀迴圈到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然後根據各個表匹配的行，返回查詢中需要的各個列。

舉個例子

​ 比如下面的例子中的簡單查詢：

select tb1.col1, tb2.col2
from tb1 a 
inner join tb2 b on a.col3 = b.col3
where tb1.col1 in (5,6)

​ 假設Mysql優化器優化後按照查詢中的表順序進行關聯操作，我們可以用下面的虛擬碼來表示Mysql是如何完成這個查詢的。

outer_iter = iterator over tbl1 where col1 in (5,6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iterator over tb2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    while inner_row
	output [ outer_row.col1, inner_row.col2 ]
	inner_row = inner_iter.next
    end
    outer_row = outer_iter.next
end

​ 上面的虛擬碼執行計劃對於單表查詢和多表關聯查詢都適用。如果是一個簡單的單表查詢，那麼只需要最外層的查詢結果就Ok了。對於外連線上面的執行過程仍然適用。例如我們把上面的例子改成外連線查詢：

select tb1.col1, tb2.col2
from tb1 a 
left join tb2 b on a.col3 = b.col3
where tb1.col1 in (5,6)

​ 其執行的虛擬碼與內聯關聯執行過程類似，如下：

outer_iter = iterator over tb1 where col1 in (5,6)
outer_row = outer_iter.next
while outer_row
    inner_iter = iterator over tb2 where col3 = outer_row.col3
    inner_row = inner_iter.next
    if inner_row
       while inner_row
           output [ outer_row.col1, inner_row.col2 ]
           inner_row = inner_iter.next
       end
    else
	output[ outer_row.col1, null ]
    end
    outer_row = outer_iter.next
end

​ 從本質上來說，Mysql對所有的型別的查詢都以同樣的方式執行。例如，Mysql在FROM子句中遇到子查詢時，先執行子查詢並將其結果放在一個臨時表中，然後將這個臨時表當做一個普通標對待（正如其名“派生表”）。臨時表是沒有任何索引的，所以要寫sql時應該儘量減少子查詢，改用關聯查詢。

結論

​ 就上面簡單的例子而言，從上面的虛擬碼中可以看出來，我們需要給__tb1的col1__和__tb2的col3__這2列建立索引，而__tb1的col3__是不需要建立索引的。

總結

​ 不管是內聯還是外連線關聯查詢，為各個表建立索引應該要根據當時的條件來建立：

  • 主表（驅動表）根據where中涉及到的列建索引（比如例子中的tb1需要給col1建立索引)
  • 從表根據被關聯的列建立索引，如果where中涉及到從表的列，可以考慮結合關聯列建立組合索引（注意最左匹配原則）。

相關文章