MySQL查询优化器--逻辑查询优化技术（一）--视图重写

视图重写

MySQL支持对视图的优化。优化方法是把视图转为对基表的查询，然后进行类似子查询的优化。通常能优化简单视图，复杂视图不能优化。

 

示例：

创建表

CREATE TABLE t1 (a1 int UNIQUE, b1 int);

CREATE TABLE t2 (a2 int UNIQUE, b2 int);

CREATE TABLE t3 (a3 int UNIQUE, b3 int);

创建简单视图

CREATE VIEW v_t_1_2 AS SELECT * FROM t1, t2;

CREATE VIEW v_t_2_3 AS SELECT * FROM t3, t2;

 

对比组一：

基于表t1和t2的视图v_t_1_2，视图重写。

mysql> EXPLAIN EXTENDED SELECT *, (SELECT max(a1) FROM v_t_1_2) FROM t1 WHERE t1.a1<20;

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

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

  `test`.`t1`.`b1` AS `b1`,

  (/* select#2 */

    select max(`test`.`t1`.`a1`)

from `test`.`t1` join `test`.`t2`)

AS `(SELECT max(a1) FROM v_t_1_2)`  //视图被子查询替换

from `test`.`t1`

where (`test`.`t1`.`a1` < 20)

 

同上一条等价语义，只是视图被用于定义视图的表替换，视图被重写，与上一条SQL完全等价。

mysql> EXPLAIN EXTENDED SELECT *, (SELECT max(a1) FROM t1, t2) FROM t1 WHERE t1.a1<20;

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

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

  `test`.`t1`.`b1` AS `b1`,

  (/* select#2 */

    select max(`test`.`t1`.`a1`)

    from `test`.`t1` join `test`.`t2`)

  AS `(SELECT max(a1) FROM t1, t2)`

from `test`.`t1`

where (`test`.`t1`.`a1` < 20)

 

对比组二：

直接用视图和表做连接操作，视图被重写。

mysql> EXPLAIN EXTENDED SELECT * FROM t1, v_t_1_2 WHERE t1.a1<20;

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

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

  `test`.`t1`.`b1` AS `b1`,

  `test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`,

  `test`.`t2`.`a2` AS `a2`,

  `test`.`t2`.`b2` AS `b2`

from `test`.`t1` join `test`.`t1` join `test`.`t2`

where (`test`.`t1`.`a1` < 20)

 

等价于上一条视图的子查询，没有视图存在，查询执行计划不完全相似，子查询没有被重写

mysql> EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t1, t2) t12 WHERE t1.a1<20;

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

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

  `test`.`t1`.`b1` AS `b1`,

  `t12`.`a1` AS `a1`,

  `t12`.`b1` AS `b1`,

  `t12`.`a2` AS `a2`,

  `t12`.`b2` AS `b2`

from `test`.`t1` join (/* select#2 */

  select `test`.`t1`.`a1` AS `a1`,

    `test`.`t1`.`b1` AS `b1`,

    `test`.`t2`.`a2` AS `a2`,

    `test`.`t2`.`b2` AS `b2`

  from `test`.`t1` join `test`.`t2`) `t12`

where (`test`.`t1`.`a1` < 20)

 

对比组三：复杂视图的应用

创建复杂视图。

CREATE VIEW v_t_gd_1_2 AS SELECT DISTINCT t1.b1, t2.b2 FROM t1, t2 GROUP BY t1.b1, t2.b2;

带有GROUPBY的复杂视图没有被重写。

mysql> EXPLAIN EXTENDED SELECT * FROM t1, v_t_gd_1_2 WHERE t1.a1<20;

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

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

  `test`.`t1`.`b1` AS `b1`,

  `v_t_gd_1_2`.`b1` AS `b1`,

  `v_t_gd_1_2`.`b2` AS `b2`

from `test`.`t1` join `test`.`v_t_gd_1_2`

where (`test`.`t1`.`a1` < 20)

 

对比组四：视图重写后，被作为子查询优化

CREATE VIEW v_exists_1_2 AS SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1=t2.a2);

对有半连接语义的视图执行连接操作，查询优化器把视图重写后对变为子查询的进一步优化。

mysql> EXPLAIN EXTENDED SELECT * FROM t1, v_exists_1_2 WHERE t1.a1<20 AND v_exists_1_2.a1<10;

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

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

  `test`.`t1`.`b1` AS `b1`,

  `test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` join `test`.`t1`  //视图变为了定义视图的基表

where ((`test`.`t1`.`a1` < 20) and

  (`test`.`t1`.`a1` < 10) and

  exists(/* select#3 */ //视图的查询条件合并到查询的WHERE条件中

    select 1

    from `test`.`t2`

    where (`test`.`t1`.`a1` = `test`.`t2`.`a2`))

)

把视图定义部分的内容单独执行，即使在条件t1.a1<10作用下，半连接也没有被优化。

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1=t2.a2) AND t1.a1<10;

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

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

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (exists(/* select#2 */

        select 1 from `

        test`.`t2`

        where (`test`.`t1`.`a1` = `test`.`t2`.`a2`))  //半连接没有被优化

      and

      (`test`.`t1`.`a1` < 10)

)