二 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.
待续...