Mysql优化之视图重写

视图是数据库中基于表的一种对象，把对表的查询固化。
视图的类型：
1、用SPJ格式构造的视图，称为简单视图。
2、用非SPJ格式构造的视图，称为复杂视图。
什么是视图重写？
1.查询语句中出现视图对象
2.查询优化后，视图对象消失
3.消失的视图对象的查询语句，融合到初始查询语句中。
MySQL视图重写准则：
1、MySQL支持对简单视图进行优化
2、优化方法是把视图转为对基表的查询，然后进行类似子查询的优化
3、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_gd_1_2 AS 
    SELECT DISTINCT t1.b1, t2.b2 
    FROM t1, t2 
    GROUP BY t1.b1, t2.b2;

mysql> EXPLAIN EXTENDED SELECT * FROM t1, v_t_1_2 WHERE t1.a1<20;
+----+-------------+-------+-------+------+---------------------------------------+
| id | select_type | table | type  | key  | Extra                                 |
+----+-------------+-------+-------+------+---------------------------------------+
|  1 | SIMPLE      | t1    | range | a1   | Using index condition                 |
|  1 | SIMPLE      | t1    | ALL   | NULL | Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t2    | ALL   | NULL | Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+------+---------------------------------------+

从执行计划可以看出，把视图转换为了普通的连接查询，消除了视图进行了优化。

mysql> EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t1, t2) t12 WHERE t1.a1<20;
+----+-------------+------------+-------+------+---------------------------------------+
| id | select_type | table      | type  | key  | Extra                                 |
+----+-------------+------------+-------+------+---------------------------------------+
|  1 | PRIMARY     | t1         | range | a1   | Using index condition                 |
|  1 | PRIMARY     | <derived2> | ALL   | NULL | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | ALL   | NULL | NULL                                  |
|  2 | DERIVED     | t2         | ALL   | NULL | Using join buffer (Block Nested Loop) |

从执行计划可以看出将视图转换为两表连接查询得到一个中间对象然后又与t1表进行连接

基于表t1和t2的视图v_t_1_2，进行聚集操作：

mysql> EXPLAIN EXTENDED SELECT *, (SELECT max(a1) FROM v_t_1_2) FROM t1 WHERE t1.a1<20;
+----+-------------+-------+-------+------+-------------------------+
| id | select_type | table | type  | key  | Extra                   |
+----+-------------+-------+-------+------+-------------------------+
|  1 | PRIMARY     | t1    | range | a1   | Using index condition   |
|  2 | SUBQUERY    | NULL  | NULL  | NULL | No matching min/max row |
+----+-------------+-------+-------+------+-------------------------+

从执行计划看出视图消除了进行了优化但是子查询没有被消除

直接用视图和表做连接操作，并执行分组操作：

mysql> EXPLAIN EXTENDED SELECT a1, a3 FROM t3, v_t_1_2 WHERE a1<20 GROUP BY a1, a3;
+----+-------------+-------+-------+------+-----------------------------------------------------------------+
| id | select_type | table | type  | key  | Extra         |
+----+-------------+-------+-------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | t3    | index | a3   | Using index; Using temporary; Using filesort         |
|  1 | SIMPLE      | t1    | index | a1   | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t2    | index | a2   | Using index; Using join buffer (Block Nested Loop)         |
+----+-------------+-------+-------+------+-----------------------------------------------------------------+

将视图优化为了表连接查询

直接用视图和表做连接操作，并执行分组和去重操作操作：

mysql> EXPLAIN EXTENDED SELECT DISTINCT a1, a3 FROM t3, v_t_1_2 WHERE a1<20
GROUP BY a1, a3;

+----+-------------+-------+-------+------+-----------------------------------------------------------------+
| id | select_type | table | type  | key  | Extra         |
+----+-------------+-------+-------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | t3    | index | a3   | Using index; Using temporary; Using filesort         |
|  1 | SIMPLE      | t1    | index | a1   | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | t2    | index | a2   | Using index; Distinct; Using join buffer (Block NestedLoop)    |
+----+-------------+-------+-------+------+-----------------------------------------------------------------+

在简单视图上执行外连接操作。

mysql> EXPLAIN EXTENDED SELECT * FROM t3 LEFT JOIN v_t_1_2 V ON V.a1=t3.a3
 WHERE V.a1<20;
+----+-------------+-------+------+------+---------------------------------------+
| id | select_type | table | type | key  | Extra                                 |
+----+-------------+-------+------+------+---------------------------------------+
|  1 | SIMPLE      | t3    | ALL  | NULL | NULL                                  |
|  1 | SIMPLE      | t1    | ref  | a1   | Using index condition                 |
|  1 | SIMPLE      | t2    | ALL  | NULL | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+------+---------------------------------------+

可以看出来可以对简单视图进行优化，优化为表连接查询，即使进行非SPJ复杂操作也可优化简单视图

直接用复杂视图和表做连接操作，查询执行计划如下：
mysql> EXPLAIN EXTENDED SELECT * FROM t3, v_t_gd_1_2 WHERE t3.a3<20;
+----+-------------+------------+-------+------+---------------------------------------+
| id | select_type | table      | type  | key  | Extra                                 |
+----+-------------+------------+-------+------+---------------------------------------+
|  1 | PRIMARY     | t3         | range | a3   | Using index condition                 |
|  1 | PRIMARY     | <derived2> | ALL   | NULL | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t1         | ALL   | NULL | Using temporary; Using filesort       |
|  2 | DERIVED     | t2         | ALL   | NULL | Using join buffer (Block Nested Loop) |
+----+-------------+------------+-------+------+---------------------------------------+

从执行计划可以看出并没有优化复杂视图