mysql视图特性分析_MySQL View 视图分析

为什么MySQL中很少使用视图功能？

首先MySQL使用视图有什么好处：

清晰简单，可以让简单的语句逻辑更清晰

可复用，可以让部分复杂的sql逻辑多次复用，统一更新

安全，可以隐藏掉一些私密的表结构

那么MySQL中的视图性能如何呢？结合以下的MySQL官方文档看一下视图的主要算法：

The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.

For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

If no ALGORITHM clause is present, the default algorithm is determined by the value of the derived_merge flag of the optimizer_switch system variable. For additional discussion, see Section 8.2.2.3, “Optimizing Derived Tables, View References, and Common Table Expressions”.

那么可以看到，主要有两种方式：MERGE和TEMPTABLE

MERGE

把视图中的sql合并到查询sql中，例如官方例子中：

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS

SELECT c1, c2 FROM t WHERE c3 > 100;

SELECT * FROM v_merge;

以上sql等价于

select * from (SELECT c1, c2 FROM t WHERE c3 > 100);

TEMPTABLE

临时表算法是先将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表

执行计划分析

CREATE TABLE `user_info` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL DEFAULT '',

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

先试用MERGE方式：

create or replace ALGORITHM =MERGE view v as select * From user_info;

执行以下sql：

mysql> explain SELECT * FROM user_info where id =1;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM v where id =1;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.00 sec)

可以看到执行计划相同，并都使用了索引

再试用TEMPTABLE方式：

create or replace ALGORITHM =TEMPTABLE view v as select * From user_info;

mysql> explain SELECT * FROM user_info where id =1;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | SIMPLE | user_info | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |

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

1 row in set, 1 warning (0.01 sec)

mysql> explain SELECT * FROM v where id =1;

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

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

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

| 1 | PRIMARY | | NULL | ref | | | 8 | const | 1 | 100.00 | NULL |

| 2 | DERIVED | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL |

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

2 rows in set, 1 warning (0.00 sec)

可以看到，查询视图的时候，首先使用的是查询了全表放到临时表中

总结

使用视图时，如果使用TEMPTABLE会影响数据库的优化，比如索引等情况

使用视图时，比较不容易看到视图的逻辑，也不利于开发人员对sql的优化