MySQL查询优化器--逻辑查询优化技术（四）--连接的消除

14.1.7 连接的消除

MySQL不支持连接的消除。示例如下。

 

第一步：创建环境，观察数据有不同

CREATE TABLE B (b1 INT, b2 VARCHAR(2), PRIMARY KEY(b1));

CREATE TABLE A (a1 INT, a2 VARCHAR(2), FOREIGN KEY(a1) REFERENCES B(b1) );

CREATE TABLE C (c1 INT, c2 VARCHAR(2));

 

INSERT INTO B VALUES(1, 'B1');

INSERT INTO B VALUES(2, 'B2');

INSERT INTO B VALUES(3, 'B3');

 

INSERT INTO A VALUES(1, 'A1');

INSERT INTO A VALUES(null, 'A2');

INSERT INTO A VALUES(3, 'A3');

 

INSERT INTO C VALUES(1, 'C1');

INSERT INTO C VALUES(2, 'C2');

INSERT INTO C VALUES(NULL, 'C3');

 

第二步，执行查询观察结果集，结果相同

mysql> SELECT A.*, C.* FROM A,B,C WHERE A.a1=B.b1 AND B.b1=C.c1;

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

| a1   | a2   | c1   | c2   |

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

|    1 | A1   |    1 | C1   |

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

1 row in set (0.06 sec)

 

mysql> SELECT A.*, C.* FROM A,C WHERE A.a1=C.c1 AND A.a1 IS NOT NULL;

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

| a1   | a2   | c1   | c2   |

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

|    1 | A1   |    1 | C1   |

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

1 row in set (0.00 sec)

 

mysql> SELECT A.*, C.* FROM A,C WHERE A.a1=C.c1;

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

| a1   | a2   | c1   | c2   |

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

|    1 | A1   |    1 | C1   |

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

1 row in set (0.00 sec)

 

第三步，查看执行计划，确认是否对B表使用了连接消除

mysql> EXPLAIN EXTENDED SELECT A.*, C.* FROM A,B,C WHERE A.a1=B.b1 AND B.b1=C.c1;

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

| id | select_type | table | type   | possible_keys | key     | key_len | ref       |

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

|  1 | SIMPLE      | C     | ALL    | NULL          | NULL    | NULL    | NULL    |

|  1 | SIMPLE      | B     | eq_ref | PRIMARY       | PRIMARY | 4       | test.C.c1|

|  1 | SIMPLE      | A     | ALL    | a1            | NULL    | NULL    | NULL    |

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

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

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

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

在B关系上做了“eq_ref”扫描，B没有被去除

 

只有A和C连接，其效果等同于上一条SQL，说明B的存在和是否使用对结果集没有影响。

mysql> EXPLAIN EXTENDED SELECT A.*, C.* FROM A,C WHERE A.a1=C.c1 AND A.a1 IS NOT NULL;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref       |

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

|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL      |

|  1 | SIMPLE      | A     | ref  | a1            | a1   | 5       | test.C.c1 |

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

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

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

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

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

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

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

where ((`test`.`a`.`a1` = `test`.`c`.`c1`) and (`test`.`c`.`c1` is not null))

 

mysql> EXPLAIN EXTENDED SELECT A.*, C.* FROM A,C WHERE A.a1=C.c1;

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

| id | select_type | table | type | possible_keys | key  | key_len | ref  |

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

|  1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL |

|  1 | SIMPLE      | A     | ALL  | a1            | NULL | NULL    | NULL |

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

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

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

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

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

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

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

where (`test`.`a`.`a1` = `test`.`c`.`c1`)

最后2个查询，其语义其实相同，等值连接（A.a1=C.c1），如果遇到“NULL=NULL”的情况，通常的处理都是“FALSE”，所以，对于“A.a1 IS NOT NULL”条件的添加，只是从语义上保证了其结果的正确，在实际应用中，没有作用。但查询计划不同，a1列上的非空判断条件，对查询计划有作用。

 

第四步：再看A外连接B然后连接C，查询目标列没有B的列，B不被消除。对比A、B、C做内连接时的SQL执行计划，內连接没有消除B，外连接不支持连接消除。

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

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

| id | select_type | table | type   | possible_keys | Extra     |

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

|  1 | SIMPLE      | C     | ALL    | NULL          | NULL

|  1 | SIMPLE      | A     | ALL    | a1            | Using where; Using join buffer (Block Nested L

|  1 | SIMPLE      | B     | eq_ref | PRIMARY       | Using index

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

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

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

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

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

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

from `test`.`a` left join `test`.`b` on((`test`.`b`.`b1` = `test`.`c`.`c1`)) join `test`.`c`

where (`test`.`a`.`a1` = `test`.`c`.`c1`)