mysql-join优化

在介绍join 语句的优化思路之前，首先要理解在MySQL 中是如何实现join 的。只要理解了其实现原理，优

化就比较简单了。

在MySQL 中，只有一种join 算法，就是nested loop join，它没有很多其他数据库提供的hash Join，

也没有sort merge join。nested loop join 实际上就是通过驱动表的结果集作为循环基础数据，然后将该

结果集中的数据(联接键对应的值)作为过滤条件一条条地到下一个表中查询数据，最后合并结果。如果还有第三个

表参与join，则把前两个表的join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，

如此往复。

比如一个表t1，t2，t3 的联接，通过explain 观察到的执行计划中join 的类型(explain 中type 行的

值)是：

table join type

t1 range

t2 ref

t3 All

则相应的这个联接的算法伪代码如下：

for each row in t1 matching range {

for each row in t2 matching reference key {

for each row in t3 {

if row satisfies join conditions

send to client

}

}

}

 

4.2.3.2. 小结果集驱动大结果集

通过explain 观察MySQL 为该SQL 制定的执行计划：

select a.expenditure, b.balance from consume a, account b where a.account_id =

b.account_id and a.account_payee=13301087 and b.account_bank='icbc.beijing.fengtai';

join 中选取表consume 为驱动表。

如果通过explain 观察，发现MySQL 在join 过程中选取的驱动表不是很合适的话，建议最先通过索引，再

通过hint 技术straight_join 来干预MySQL，使其按照最优的方式选取驱动表，制定最优的执行计划。

4.2.3.3. 被驱动表的join column 最好能命中index

对于nested loop join 算法，内层循环是整个join 执行过程中执行次数最多的，如果每次内层循环中执

行的操作能节省很少的资源，就能在整个join 循环中节约很多的资源。

而被驱动表的join column 能使用索引正是基于上述考虑的。只有让被驱动表的join 条件字段被索引了，

才能保证循环中每次查询都能通过索引迅速定位到所需的行，这样可以减少内层一次循环所消耗的资源；否则只能

通过扫表等操作来找到所需要的行。

例如

select a.expenditure, b.balance from consume a, account b where a.account_id =

b.account_id and a.account_payee=13301087 and b.account_bank='icbc.beijing.fengtai';

join 过程中被驱动表join 条件列account_id 是primary key，通过主键索引每次内层循环定位所需的

行非常快且开销非常小。