MySQL查询优化器--逻辑查询优化技术（三）--嵌套连接消除

1.1.1        嵌套连接消除

MySQL支持嵌套连接的消除。

 

一 存在嵌套连接

SQL语句的语义，是B和C先连接，然后再和A连接；但是，查询执行计划是A和B先连接，然后再连接C。所以，用括号表示的嵌套被消除。

mysql> EXPLAIN EXTENDED SELECT * FROM A JOIN (B JOIN C ON B.b1=C.c1) ON A.a1=B.b1 WHERE A.a1 > 1;

+----+-------------+-------+------+----------------------------------------------------+

| id | select_type | table | type | Extra                                              |

+----+-------------+-------+------+----------------------------------------------------+

|  1 | SIMPLE      | C     | ALL  | Using where                             |

|  1 | SIMPLE      | B     | ALL  | Using where; Using join buffer (Block Nested Loop) |

|  1 | SIMPLE      | A     | ALL  | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------+----------------------------------------------------+

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`a`.`a1` AS `a1`,

`test`.`a`.`a2` AS `a2`,`

test`.`b`.`b1` AS `b1`,

`test`.`b`.`b2` AS `b2`,

`test`.`c`.`c1` AS `c1`,

`test`.`c`.`c2` AS `c2`

from `test`.`a` join `test`.`b` join `test`.`c`

where ((`test`.`b`.`b1` = `test`.`c`.`c1`) and

    (`test`.`a`.`a1` = `test`.`c`.`c1`) and (`test`.`c`.`c1` > 1))

 

二 不存在嵌套连接

SQL语句的语义，是A和B先连接，然后再和C连接；查询执行计划是A和B先连接，然后再连接C。

mysql> EXPLAIN EXTENDED SELECT * FROM A JOIN B ON A.a1=B.b1 JOIN C ON B.b1=C.c1 WHERE A.a1>1;

+----+-------------+-------+------+----------------------------------------------------+

| id | select_type | table | type | Extra                                              |

+----+-------------+-------+------+----------------------------------------------------+

|  1 | SIMPLE      | C     | ALL  | Using where                             |

|  1 | SIMPLE      | B     | ALL  | Using where; Using join buffer (Block Nested Loop) |

|  1 | SIMPLE      | A     | ALL  | Using where; Using join buffer (Block Nested Loop) |

+----+-------------+-------+------+----------------------------------------------------+

被查询优化器处理后的语句为：

/* select#1 */ select `test`.`a`.`a1` AS `a1`,

  `test`.`a`.`a2` AS `a2`,

  `test`.`b`.`b1` AS `b1`,

  `test`.`b`.`b2` AS `b2`,

  `test`.`c`.`c1` AS `c1`,

  `test`.`c`.`c2` AS `c2`

from `test`.`a` join `test`.`b` join `test`.`c`

where ((`test`.`b`.`b1` = `test`.`c`.`c1`) and

  (`test`.`a`.`a1` = `test`.`c`.`c1`) and (`test`.`c`.`c1` > 1))