MySQL中的视图及性能问题

2007-08-14 22:28:59|  分类： MySQL |  标签：数据库  postgresql  mysql  |字号 订阅

视图是MySQL 5.0中增加的三大新功能之一（另外两个是存储过程与触发器），也是一般稍微“高级”一点的数据库所必需要有的功能。 MySQL在定义视图上没什么限制，基本上所有的查询都可定义为视图，并且也支持可更新视图（当然只有在视图和行列与基础表的行列之间存在一一对应关系时才能更新），因此从功能上说MySQL的视图功能已经很完善了。

然而若要在应用中使用视图，还需要了解处理视图时的性能，而MySQL在这方面问题是比较大的，需要特别注意。首先要知道 MySQL在处理视图时有两种算法，分别称为MERGE和TEMPTABLE。在执行"CREATE VIEW"语句时可以指定使用哪种算法。 所谓MERGE是指在处理涉及到视图的操作时，将对视图的操作根据视图的定义进行展开，有点类似于C语言中的宏展开。比如设有以下的表（类似于博客中的评论）：
CREATE TABLE `comment` (
  `id` int(11) NOT NULL,
  `user_id` int(11) default NULL,
  `content` varchar(255) default NULL,
  PRIMARY KEY  (`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 > EXPLAIN EXTENDED 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> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| 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算法：

• 聚集函数
• DISTINCT
• GROUP BY
• HAVING
• 集合操作（在MySQL中只有UNION, UNION ALL，没有EXCEPT和INTERSECT）
• 子查询

确实，在视图定义比较复杂的情况下，要对视图操作进行有效的优化是非常困难的。因此 在这个时候，MySQL使用了一种以不变应万变的方法，即先执行视图定义，将其结果使用临时表保存起来，这样后续对视图的操作就转化为对临时表的操作。不能不说从单从软件设计的角度看，这样的方法非常的优雅，然而从性能角度，这一方法也是非常的差。

比如我们希望使用如下的视图来表示每个用户的评论数，即：
CREATE VIEW comment_count AS SELECT user_id, count(*) AS count FROM comment GROUP BY user_id;
使用这个视图的时候，我们可能心里有个小算盘。目前我们先用这个视图顶着，如果性能确实有问题，那我们就再来搞一张comment_count的表，其中就记下来每个用户的评论数。而我们现在先用这个视图是为了将来要是改的话会方便点（这也是视图--即教科书中所谓的外模式--这个东西存在的主要原因之一，另一主要原因是便于权限控制）。但是遇到了MySQL这个蠢货，我们的算盘铁定会失败。
我们来看一下指定user_id从comment_count选取记录时的执行策略：
mysql> explain select 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 | Using where | 
|  2 | DERIVED     | comment    | index | NULL          | idx_comment_uid | 5       | NULL | 524833 | Using index | 
+----+-------------+------------+-------+---------------+-----------------+---------+------+--------+-------------+
2 rows in set (4.18 sec)
可以看出，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 group by user_id) as comment_count where user_id = 90;
2、子查询提升。因为子查询中使用了group by，因此先将外面的条件作为提升后的having条件
select user_id, count(*) as count from comment group by user_id having user_id = 90;
3、由于having条件中不涉及聚集函数，转化为where条件
select user_id, count(*) as count from comment where user_id = 90 group by user_id;
4、由于指定where条件后，user_id已经是一个常数，根据常数group by没意义，因此去掉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中的rule system有关。