首页 > 数据库 > 慎用Mysql视图

慎用Mysql视图

2011年10月29日 7,422 views 发表评论 阅读评论

慎用视图
在5.0版以上MySQL服务器提供了视图功能(包括可更新视图)。具体的语法可参考官方文档 视图 .
例如创建一个视图: mysql> CREATE VIEW test.v AS SELECT * FROM t;
注意视图并不保存任何数据,查询视图返回的结果是来自于基表存储的数据.

视图可以简化一些操作.隐藏基表的复杂,进行一些安全控制(基于列的权限控制),但如果使用不当,很可能带来性能问题.
现实中,对于小数据量来说, 什么写法性能并不是有太大区别,但随着数据量增大,视图很可能成为性能杀手,一定要慎用.

我们需了解view实现的机制. 对于包含view的sql ,优化器优化的机制有2种(.MERGE , TEMPTABLE)
1. TEMPTABLE: 创建一个临时表,把视图的结果集放如临时表, 然后sql操作这个临时表;
2. MERGE : 重写sql,合并view的sql ,这种方法更智能.


比如有视图 test.v
CREATE VIEW test.v AS SELECT * FROM t where a=1;
对于sql : select a,b,c from test.v where b=2;
第一种临时表的方式类似如下
CREATE TEMPORARY TABLE TMP_a AS SELECT * FROM t where a=1;
select * from TMP_a where b=2;
这种方式必须先查出所有视图的数据,然后才能基于这个视图的数据进行查找.显然可能有性能问题.
且外部的where条件也不能传递到内部视图的限制,临时表上也没有索引

而用第二种方式 (MERGE) ,优化后的sql类似如下
SELECT * FROM t where a=1 and b=2;
Mysql尽量使用第二种合并sql的方式,但很多情况下,由于研发同学写的sql采用临时表的方式,导致性能很差 .
我们可以用explain 的方式确认,如果explain的select_type输出显示
DERIVED(查询结果来自一个衍生表) ,那么使用的是临时表的方式.

例如:
root@localhost test>create view test_garychen_v as select * from test_garychen group by STAT_TIME;
root@localhost test>explain select * from test_garychen_v; +—-+————-+—————+——+—————+——+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+—————+——+—————+——+———+——+——+———————————+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 110 | |
| 2 | DERIVED | test_garychen | ALL | NULL | NULL | NULL | NULL | 5000 | Using temporary; Using filesort |
+—-+————-+—————+——+—————+——+———+——+——+———————————+
所以需要小心编写sql,以免使用到临时表的机制,就目前的版本5.0,5.1来说,需要注意以下要点
1. view里尽量避免使用GROUP BY, ORDER BY ,DISTINCT, 聚集函数, UNION, 子查询;
2. 包含view的sql往往比较复杂,mysql的优化器可能不智能.应以实际的性能测试为准,

对于现实中的场景,Mysql的高级特性我一般是建议尽量少用. 使用Mysql的基础的核心的功能完全可以满足绝大部分的生产需要.

 » 转载保留版权:老陈 » 《慎用Mysql视图》
 » 如果喜欢可以: 点此订阅本站
分类: 数据库 标签: , ,
  1. zzz
    2015年6月14日11:58 | #1

    写的挺好的

  1. 本文目前尚无任何 trackbacks 和 pingbacks.