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

尋找的路上發表於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中涉及到從表的列,可以考慮結合關聯列建立組合索引(注意最左匹配原則)。

相關文章