多表关联查询中，关联字段都应该创建索引吗？

前言

​ 关于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中涉及到从表的列，可以考虑结合关联列建立组合索引（注意最左匹配原则）。