【MySQL】我必须得告诉大家的MySQL优化原理2（中）视图与视图背后的……

最新推荐文章于 2024-09-11 13:16:52 发布

星辰_mya

最新推荐文章于 2024-09-11 13:16:52 发布

阅读量1.9k

点赞数 1

分类专栏： -----MySQL

-----MySQL 专栏收录该内容

49 篇文章 1 订阅

订阅专栏

视图

对于一些关联表的复杂查询，使用视图有时候会大大简化问题，因此在许多场合下都可以看到视图的身影，但视图真如我们所想那样简单吗？它和直接使用JOIN的SQL语句有何区别？视图背后的原理又了解多少？

视图本身是一个虚拟表，不存放任何数据，查询视图的数据集由其他表生成。MySQL底层通过两种算法来实现视图：临时表算法（TEMPTABLE）和合并算法（MERGE）。

临时表算法就是将SELECT语句的结果存放到临时表中，当需要访问视图的时候，直接访问这个临时表即可。

视图的实现算法是视图本身的属性决定的，跟作用在视图上的SQL没有任何关系。一般来说，只要原表记录和视图中的记录无法建立一一映射的关系时，MySQL都将使用临时表算法来实现视图。比如创建视图的SQL中包含GROUP BY、DISTINCT、UNION、聚合函数、子查询的时候，视图都将采用临时表算法（这些规则在以后的版本中，可能会发生改变，具体请参考官方手册）。

合并算法则是重写包含视图的查询，将视图定义的SQL直接包含进查询SQL中。通过两个简单的示例来体会两个算法的差异，创建如下视图：

// 视图的作用是查询未支付订单
CREATE VIEW unpay_order AS
SELECT * FROM sales WHERE status = 'new'
WITH CHECK OPTION;   // 其作用下文会讲：就是表示通过视图更新的行

现要从未支付订单中查询购买者为csc的订单，可以使用如下查询：

// 查询购买者为csc且未支付的订单
SELECT order_id,order_amount,buyer FROM unpay_order WHERE buyer = 'csc';

使用临时表来模拟视图：

CREATE TEMPORARY TABLE tmp_order_unpay AS SELECT * FROM sales WHERE status = 'new'; SELECT order_id,order_amount,buyer FROM tmp_order_unpay WHERE buyer = 'csc';

使用合并算法将视图定义的SQL合并进查询SQL后的样子：

SELECT order_id,order_amount,buyer FROM sales WHERE status = 'new' AND buyer = 'csc';

MySQL可以嵌套定义视图，即在一个视图上在定义另一个视图，可以在EXPLAN EXTENDED之后使用SHOW WARNINGS来查看使用视图查询重写后的结果。如果采用临时表算法实现的视图，EXPLAIN中会显示为派生表（DERIVED），注意EXPLAIN时需要实际执行并产生临时表，所以有可能会很慢。

明显地，临时表上没有任何索引，而且优化器也很难优化临时表上的查询，因此，如有可能，尽量使用合并算法会有更好的性能。那么问题来了：合并算法（类似于直接查询）有更好的性能，为什么还要使用视图？

为什么使用视图

首先视图可以简化应用上层的操作，让应用更专注于其所关心的数据。其次，视图能够对敏感数据提供安全保护，比如：对不同的用户定义不同的视图，可以使敏感数据不出现在不应该看到这些数据的用户视图上；也可以使用视图实现基于列的权限控制，而不需要真正的在数据库中创建列权限。再者，视图可以方便系统运维，比如：在重构schema的时候使用视图，使得在修改视图底层表结构的时候，应用代码还可以继续运行不报错。

基于此，使用视图其实更多的是基于业务或者维护成本上的考虑，其本身并不会对性能提升有多大作用（注意：此处只是基于MySQL考虑，其他关系性数据库中视图可能会有更好的性能，比如ORACLE和MS SQL SERVER都支持物化视图，它们都比MySQL视图有更好的性能）。而且使用临时表算法实现的视图，在某些时候性能可能会非常糟糕，比如：

// 视图的作用是统计每日支出金额，DATE('2017-06-15 12:00:23') = 2017-06-15
CREATE VIEW cost_per_day AS
SELECT DATE(create_time) AS date,SUM(cost) AS cost FROM costs GROUP BY date;

现要统计每日的收入与支出，有类似于上面的收入表，可以使用如下SQL：

SELECT c.date,c.cost,s.amount
FROM cost_per_day AS c
JOIN sale_per_day AS s USING(date)
WHERE date BETWEEN '2017-06-01' AND '2017-06-30'

这个查询中，MySQL先执行视图的SQL，生成临时表，然后再将sale_per_day表和临时表进行关联。这里WHERE字句中的BETWEEN条件并不能下推到视图中，因而视图在创建时，会将所有的数据放到临时表中，而不是一个月数据，并且这个临时表也不会有索引

当然这个示例中的临时表数据不会太大，毕竟日期的数量不会太多，但仍然要考虑生成临时表的性能（如果costs表数据过大，GROUP BY有可能会比较慢）。而且本示例中索引也不是问题，通过上一篇我们知道，如果MySQL将临时表作为关联顺序中的第一张表，仍然可以使用sale_per_day中的索引。但如果是对两个视图做关联的话，优化器就没有任何索引可以使用，这时就需要严格测试应用的性能是否满足需求。

我们很少会在实际业务场景中去更新视图，因此印象中，视图是不能更新的。但实际上，在某些情况下，视图是可以更新的。可更新视图是指通过更新这个视图来更新视图涉及的相关表，只要指定了合适的条件，就可以更新、删除甚至是向视图中插入数据。

通过上文的了解，不难推断出：更新视图的实质就是更新视图关联的表，将创建视图的WHERE子句转化为UPDATE语句的WHERE子句，只有使用合并算法的视图才能更新，并且更新的列必须来自同一个表中。回顾上文创建视图的SQL语句，其中有一句：WITH CHECK OPTION，其作用就是表示通过视图更新的行，都必须符合视图本身的WHERE条件定义，不能更新视图定义列以外的列，否则就会抛出check option failed错误。

相比于其它关系型数据库的视图，MySQL的视图在功能上会弱很多，比如ORACLE和MS SQL SERVER都支持物化视图。物化视图是指将视图结果数据存放在一个可以查询的表中，并定期从原始表中刷新数据到这张表中，这张表和普通物理表一样，可以创建索引、主键约束等等，性能相比于临时表会有质的提升。但遗憾的是MySQL目前并不支持物化视图，当然MySQL也不支持在视图中创建索引。

小结：

视图是一个虚拟表，不存储数据;

基于业务和成本上的考虑来使用视图；

如果条件合适视图是可以更新的（合并算法）;

临时表算法和合并算法是视图的两种实现算法，采用哪种由视图本身属性决定;

作者：CHEN川
链接：https://www.jianshu.com/p/01b9f028d9c7
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。