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

2,813 阅读3分钟

前言

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