MySQL中的视图及性能问题
来源:互联网 发布:ftp软件安卓 编辑:程序博客网 时间:2024/05/21 15:23
视图是MySQL5.0中增加的三大新功能之一(另外两个是存储过程与触发器),也是一般稍微“高级”一点的数据库所必需要有的功能。MySQL在定义视图上没什么限制,基本上所有的查询都可定义为视图,并且也支持可更新视图(当然只有在视图和行列与基础表的行列之间存在一一对应关系时才能更新),因此从功能上说MySQL的视图功能已经很完善了。
然而若要在应用中使用视图,还需要了解处理视图时的性能,而MySQL在这方面问题是比较大的,需要特别注意。首先要知道MySQL在处理视图时有两种算法,分别称为MERGE和TEMPTABLE。在执行"CREATEVIEW"语句时可以指定使用哪种算法。所谓MERGE是指在处理涉及到视图的操作时,将对视图的操作根据视图的定义进行展开,有点类似于C语言中的宏展开。比如设有以下的表(类似于博客中的评论):
CREATE TABLE `comment`(
`id` int(11)NOT NULL,
`user_id`int(11) default NULL,
`content`varchar(255) default NULL,
PRIMARYKEY (`id`),
KEY`idx_comment_uid` (`user_id`)
) ENGINE=InnoDB;
假设user_id <10000的用户为VIP用户,我们可以这样创建一个视图来表示VIP用户的评论:
CREATE VIEW vip_comment AS SELECT * FROM comment WHERE user_id< 10000;
这时我们在操作vip_comment视图时使用的就是MERGE算法。如:
mysql > EXPLAINEXTENDED SELECT count(*) FROM vip_comment WHERE user_id< 0;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type |table | type | possible_keys |key | key_len | ref | rows |Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| 1 |SIMPLE | comment | range | idx_comment_uid | idx_comment_uid |5 | NULL | 10 | Using where;Using index |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
mysql> showwarnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code |Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003| select count(0) AS `count(*)` from `test`.`comment` where((`test`.`comment`.`user_id` < 0) and(`test`.`comment`.`user_id` < 10000)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
可以看到,对vip_comment的操作已经被扩展为对comment表的操作。
一般来说在能够使用MERGE算法的时候MySQL处理视图上没什么性能问题,但并非在任何时候都能使用MERGE算法。事实上,只要视图的定义稍稍有点复杂,MySQL就没办法使用MERGE算法了。准确的说,只要视图定义中使用了以下SQL构造块就无法使用MERGE算法:
比如我们希望使用如下的视图来表示每个用户的评论数,即:
CREATE VIEW comment_count AS SELECT user_id, count(*) AS count FROMcomment GROUP BY user_id;
使用这个视图的时候,我们可能心里有个小算盘。目前我们先用这个视图顶着,如果性能确实有问题,那我们就再来搞一张comment_count的表,其中就记下来每个用户的评论数。而我们现在先用这个视图是为了将来要是改的话会方便点(这也是视图--即教科书中所谓的外模式--这个东西存在的主要原因之一,另一主要原因是便于权限控制)。但是遇到了MySQL这个蠢货,我们的算盘铁定会失败。
我们来看一下指定user_id从comment_count选取记录时的执行策略:
mysql> explainselect count(*) from comment_count where user_id = 90;
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
| id | select_type |table | type | possible_keys |key | key_len | ref |rows |Extra |
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
| 1 |PRIMARY | <derived2> |ALL |NULL |NULL | NULL | NULL| 101 | Usingwhere |
| 2 |DERIVED | comment |index |NULL | idx_comment_uid |5 | NULL | 524833 | Using index |
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
2 rows in set (4.18sec)
可以看出,MySQL首先是先执行comment_count的视图定义,将结果存储在临时表中(即DERIVED),然后再扫描这一临时表,选择出满足"user_id =90"的那一条记录。这样,虽然我们最终只需要统计90号用户的评论数,并且comment表的user_id字段上也有索引,MySQL也会扫描整个comment表,并按user_id分组计算出所有用户的评论数。一般来说,这铁定会使你的系统玩完。这里面还要注意的是即使在进行EXPLAIN时,视图的物化也是要先执行的,因此若评论很多的话EXPLAIN也是一样的慢。
这个问题的根源是MySQL的查询优化本来就存在很多问题。对于上述的查询,要达到比较好的优化效果在数据库中一般是如下处理的:
1、将对视图的操作转化为FROM子句中的子查询:
select * from (select user_id, count(*) as count from comment groupby user_id) as comment_count where user_id = 90;
2、子查询提升。因为子查询中使用了groupby,因此先将外面的条件作为提升后的having条件
select user_id, count(*) as count from comment group by user_idhaving user_id = 90;
3、由于having条件中不涉及聚集函数,转化为where条件
select user_id, count(*) as count from comment where user_id = 90group by user_id;
4、由于指定where条件后,user_id已经是一个常数,根据常数groupby没意义,因此去掉group by
select user_id, count(*) as count from comment where user_id =90;
一般从概念上要经过这四步转化,才能得到最后的优化语句。除第4步无法根据EXPLAIN输出和查询性能判断出MySQL是否进行这一优化外,前3类优化MySQL都不会进行。因此,MySQL要能够有效的处理上述查询还有很长的路要走。
PS:相对来说PostgreSQL的查询优化能力就强得多,上面的查询在PostgreSQL中就能够产生上述优化后的最终执行计划。PostgreSQL比较关注查询优化估计与PostgreSQL的学院派风格或PostgreSQL中的rulesystem有关。
然而若要在应用中使用视图,还需要了解处理视图时的性能,而MySQL在这方面问题是比较大的,需要特别注意。首先要知道MySQL在处理视图时有两种算法,分别称为MERGE和TEMPTABLE。在执行"CREATEVIEW"语句时可以指定使用哪种算法。所谓MERGE是指在处理涉及到视图的操作时,将对视图的操作根据视图的定义进行展开,有点类似于C语言中的宏展开。比如设有以下的表(类似于博客中的评论):
CREATE TABLE `comment`(
) ENGINE=InnoDB;
假设user_id <10000的用户为VIP用户,我们可以这样创建一个视图来表示VIP用户的评论:
CREATE VIEW vip_comment AS SELECT * FROM comment WHERE user_id< 10000;
这时我们在操作vip_comment视图时使用的就是MERGE算法。如:
mysql > EXPLAINEXTENDED SELECT count(*) FROM vip_comment WHERE user_id< 0;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type |table
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+--------------------------+
mysql> showwarnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code |Message
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Note
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
可以看到,对vip_comment的操作已经被扩展为对comment表的操作。
一般来说在能够使用MERGE算法的时候MySQL处理视图上没什么性能问题,但并非在任何时候都能使用MERGE算法。事实上,只要视图的定义稍稍有点复杂,MySQL就没办法使用MERGE算法了。准确的说,只要视图定义中使用了以下SQL构造块就无法使用MERGE算法:
- 聚集函数
- DISTINCT
- GROUP BY
- HAVING
- 集合操作(在MySQL中只有UNION, UNIONALL,没有EXCEPT和INTERSECT)
- 子查询
比如我们希望使用如下的视图来表示每个用户的评论数,即:
CREATE VIEW comment_count AS SELECT user_id, count(*) AS count FROMcomment GROUP BY user_id;
使用这个视图的时候,我们可能心里有个小算盘。目前我们先用这个视图顶着,如果性能确实有问题,那我们就再来搞一张comment_count的表,其中就记下来每个用户的评论数。而我们现在先用这个视图是为了将来要是改的话会方便点(这也是视图--即教科书中所谓的外模式--这个东西存在的主要原因之一,另一主要原因是便于权限控制)。但是遇到了MySQL这个蠢货,我们的算盘铁定会失败。
我们来看一下指定user_id从comment_count选取记录时的执行策略:
mysql> explainselect count(*) from comment_count where user_id = 90;
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
| id | select_type |table
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
|
|
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
2 rows in set (4.18sec)
可以看出,MySQL首先是先执行comment_count的视图定义,将结果存储在临时表中(即DERIVED),然后再扫描这一临时表,选择出满足"user_id =90"的那一条记录。这样,虽然我们最终只需要统计90号用户的评论数,并且comment表的user_id字段上也有索引,MySQL也会扫描整个comment表,并按user_id分组计算出所有用户的评论数。一般来说,这铁定会使你的系统玩完。这里面还要注意的是即使在进行EXPLAIN时,视图的物化也是要先执行的,因此若评论很多的话EXPLAIN也是一样的慢。
这个问题的根源是MySQL的查询优化本来就存在很多问题。对于上述的查询,要达到比较好的优化效果在数据库中一般是如下处理的:
1、将对视图的操作转化为FROM子句中的子查询:
select * from (select user_id, count(*) as count from comment groupby user_id) as comment_count where user_id = 90;
2、子查询提升。因为子查询中使用了groupby,因此先将外面的条件作为提升后的having条件
select user_id, count(*) as count from comment group by user_idhaving user_id = 90;
3、由于having条件中不涉及聚集函数,转化为where条件
select user_id, count(*) as count from comment where user_id = 90group by user_id;
4、由于指定where条件后,user_id已经是一个常数,根据常数groupby没意义,因此去掉group by
select user_id, count(*) as count from comment where user_id =90;
一般从概念上要经过这四步转化,才能得到最后的优化语句。除第4步无法根据EXPLAIN输出和查询性能判断出MySQL是否进行这一优化外,前3类优化MySQL都不会进行。因此,MySQL要能够有效的处理上述查询还有很长的路要走。
PS:相对来说PostgreSQL的查询优化能力就强得多,上面的查询在PostgreSQL中就能够产生上述优化后的最终执行计划。PostgreSQL比较关注查询优化估计与PostgreSQL的学院派风格或PostgreSQL中的rulesystem有关。
体验新版博客
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySQL中的视图及性能问题
- MySql中的视图的概念及应用
- mysql 视图性能
- mysql视图及应用
- mysql中的视图
- MySql中的视图重写
- (8)mysql中的视图
- mysql中的视图
- MySQL中的视图操作
- Mysql中的视图
- flex时间创建和比较总结
- Redis以及Redis的php扩展安装无错…
- 第十周项目2(从简单起)-计算1/2+2/3+3/4+...+19/20的值
- 别跟我说windows有97,2005我不知道我无知。
- 什么是ORM、什么是持久化、什么是…
- MySQL中的视图及性能问题
- a字母开头的C语言函数
- 为什么使用 Redis及其产品定位
- Redis内存使用优化与存储
- 用Python实现一个简单的WebSocket…
- linux tasklet/软中断
- json+jquery冲突解决方案
- vim使用技巧
- node.js调研与服务性能测试