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))