MySQL--视图优化(二)

续： MySQL--视图优化(一)

 

  
二 MySQL视图优化方式
 
测试用例：
创建2张表，创建一个简单视图、一个复杂视图、一个使用UNOION操作的视图，并插入少量数据。
CREATE TABLE t1 (id1 INT, a1 INT UNIQUE, b1 INT, PRIMARY KEY(id1));
CREATE TABLE t2 (id2 INT UNIQUE, a2 INT UNIQUE, b2 INT);
CREATE VIEW v_t1_t2 AS SELECT * FROM t1, t2;
CREATE VIEW v_t1_t2_goad AS SELECT DISTINCT t1.b1, t2.b2 FROM t1, t2 GROUP BY t1.b1, t2.b2;
CREATE VIEW v_UNION AS SELECT * FROM t1 UNION SELECT * FROM t2;
INSERT INTO t1 VALUES (1,1,1),(2,2,2),(3,3,3);
INSERT INTO t2 VALUES (1,1,1),(2,2,2),(30,30,30);
 
1 V5.6之前的版本
以V5.5为例，MySQL对于视图的处理，尽在mysql_make_view()这样的一个函数中完成。
 
1.1 对于 v_UNION这样的视图，MySQL语法分析阶段后期调用mysql_parse()首先解决视图展开的处理，对于可以展开的视图（即可以采用上拉技术进行扁平化处理），通过调用open_and_lock_tables()->open_table()并调用open_new_frm()读取视图的定义。
 
1.2 然后，调用mysql_make_view()完成对于视图的语法分析，并对可以merge（即可把视图进行上拉优化，上拉的相关思路/原理，可参见：《MySQL子查询优化---详解--1》， http://blog.163.com/li_hx/blog/static/18399141320150253652874/）的视图定义体中的查询语句进行merge操作（表对象上拉到父层，WHERE条件合并到上层）。所以，在5.5版本，对于视图的优化，尽在mysql_make_view()这个函数中。
 
1.3 间接地，可以侧面地对于Q1（http://blog.163.com/li_hx/blog/static/18399141320158220574035/）问题得出一个结论：MySQL V5.5对于视图和派生表的处理方式“可能不一样”。
 
1.4 可以merge的条件
不是所有的视图都是可以执行“视图重写”优化的。这点可以参考“《数据库查询优化器的艺术》一书第28页2.2.2视图重写”一节。
 
MySQL V5.5对于可merge的视图的条件是：
1）视图创建时，没有指定 VIEW_ALGORITHM_TMPTABLE，即视图不使用临时表
2）视图定义中不带有GROUP/HAVING/DISTINCT/LIMIT/聚集函数等子句
从第2条的条件看，这相当于在说：MySQL不支持对复杂视图进行优化。
 
1.5 对于派生表的处理方式
首先，看如下的事例：
mysql> EXPLAIN SELECT * FROM t1, (SELECT * FROM t2) as tt;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra             |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------------+
|  1 | PRIMARY     | t1         | ALL  | NULL          | NULL | NULL    | NULL |    3 |                   |
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using join buffer |
|  2 | DERIVED     | t2         | ALL  | NULL          | NULL | NULL    | NULL |    3 |                   |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------------+
3 rows in set (1 min 25.10 sec)
 
这是一个简单的2表连接，MySQL V5.5把tt这个派生表单独处理，然后再与t1进行连接。即不能直接进行t1 和t2表的连接操作，这样，t1外表驱动了一个物化了的表，SQL的运行效率低下。这个简单事例，足以证明MySQL早期版本的优化器是何等的弱，为人诟病确也正常。
 
1.6 MySQL中派生表与SQL标准的差异
MySQL的查询执行计划对于 select_type列中的DERIVED解释如下：
DERIVED ： Derived table SELECT (subquery in FROM clause)
这表明DERIVED是一个源自FROM子句的查询。而SQL标准并没有把derived table限定到FROM子句中，注意这二者之间的差别（意味着MySQL在这点上没有完全遵循SQL标准，^_^）。
 
例如，对于MySQL，可以通过如下实例来验证MySQL对于DERIVED定义的局限性。
mysql> EXPLAIN SELECT *,(SELECT a2 FROM t2) FROM t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL   | NULL          | NULL | NULL    | NULL |    3 |             |
|  2 | SUBQUERY    | t2    | index | NULL          | a2   | 5       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
在如上的查询执行计划中，(SELECT a2 FROM t2)被当作了子查询而不是DERIVED table.

 

待续...