视图处理算法问题一例

1.场景描述：

    视图直接查询需要3分钟出结果，将视图中的SQL拿出来，执行是毫秒级

    MySQL版本：5.6.16

2.问题再现：

视图语句：

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `jtorder`@`%` 
    SQL SECURITY DEFINER
VIEW `view_staff_oper` AS
    ( select distinct
        `e`.`SYSTEM_USER_ID` AS `system_user_id`,
        ` c`.`PROD_ID` AS `prod_offer_id`
    from
        (((`tb_sys_staff_scope` `a`
        join `tb_sys_oper_group` `b`)
        join `tb_sys_oper_prod` ` c`)
        join ` system_user` `e`)
    where
        ((`b`.`OPER_GROUP_ID` = `a`.`OPER_GROUP_ID`)
            and (` c`.`OPER_ID` = `b`.`OPER_LIST_ID`)
            and (`e`.`SYSTEM_USER_ID` = `a`.`SYSTEM_USER_ID`)))

查看执行计划：

explain   SELECT * FROM view_staff_oper;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY		ALL	NULL	NULL	NULL	NULL	85550212800	NULL
2	DERIVED	c	ALL	NULL	NULL	NULL	NULL	100	Using temporary
2	DERIVED	b	ALL	NULL	NULL	NULL	NULL	2082	Using where; Using join buffer (Block Nested Loop)
2	DERIVED	a	ALL	NULL	NULL	NULL	NULL	410904	Using where; Using join buffer (Block Nested Loop)
2	DERIVED	e	eq_ref	PRIMARY	PRIMARY	6	jtorder.a.SYSTEM_USER_ID	1	Using index; Distinct

explain select distinct
        `e`.`SYSTEM_USER_ID` AS `system_user_id`,
        ` c`.`PROD_ID` AS `prod_offer_id`
    from
        (((`tb_sys_staff_scope` `a`
        join `tb_sys_oper_group` `b`)
        join `tb_sys_oper_prod` ` c`)
        join ` system_user` `e`)
    where
        ((`b`.`OPER_GROUP_ID` = `a`.`OPER_GROUP_ID`)
            and (` c`.`OPER_ID` = `b`.`OPER_LIST_ID`)
            and (`e`.`SYSTEM_USER_ID` = `a`.`SYSTEM_USER_ID`))

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	c	ALL	NULL	NULL	NULL	NULL	100	Using temporary
1	SIMPLE	b	ALL	NULL	NULL	NULL	NULL	2082	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	410904	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	e	eq_ref	PRIMARY	PRIMARY	6	jtorder.a.SYSTEM_USER_ID	1	Using index

比较可以看到，当使用视图查询的时候，产生了笛卡尔积，为什么会产生派生表呢？

3.问题分析：

查看了很多相关文档，最终在mysql官方手册讲到：

18.4.2 View Processing Algorithms

http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html

简单介绍一下：

视图的处理算法有三种：

    1.MERGE：会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。( 具体规则后面再研究下)

    2.TEMPTABLE：视图结果将被置于临时表中，然后使用它执行语句

    3.UNDEFINED：模糊的，mysql自动进行选择，如果可能，mysql会优先选择MERGE，因为MERGE通常更有效

考虑MERGE与UNDEFINED的区别，为什么要有这两种方式。

如果视图中包含如下的函数，则MERGE算法将不能被使用：

    1.聚合函数 count()，SUM()等

    2.DISTINCT

    3.GROUP BY

    4.HAVING

    5.LIMIT

    6.UNION OR UNION ALL

    7.select列中含有子查询

    8.只有 literal values，即（create view as select 1 as aa，类似这样没有基础表）

从我们这个例子可以看到，由于使用了DISTINCT，因此在查询的时候必须构造一个TEMP表，这样当进行全视图查询的情况下，性能将非常差。

重建视图：

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `jtorder`@`%` 
    SQL SECURITY DEFINER
VIEW `view_baof` AS 
    ( select  --去掉distinct
        `e`.`SYSTEM_USER_ID` AS `system_user_id`,
        ` c`.`PROD_ID` AS `prod_offer_id`
    from
        (((`tb_sys_staff_scope` `a`
        join `tb_sys_oper_group` `b`)
        join `tb_sys_oper_prod` ` c`)
        join ` system_user` `e`)
    where
        ((`b`.`OPER_GROUP_ID` = `a`.`OPER_GROUP_ID`)
            and (` c`.`OPER_ID` = `b`.`OPER_LIST_ID`)
            and (`e`.`SYSTEM_USER_ID` = `a`.`SYSTEM_USER_ID`)))

查看执行计划：

没有再生成TEMP表了

4.总结：

    在视图中尽量避免使用：

          1.聚合函数 count()，SUM()等

          2.DISTINCT

          3.GROUP BY

          4.HAVING

          5.LIMIT

          6.UNION OR UNION ALL

          7.select列中含有子查询

虽然在视图的外查询有条件，从测试的结果来看，性能较不带条件有明显提升，但是相对于MERGE的带条件方式还是有点慢。

来自为知笔记(Wiz)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25704976/viewspace-1319994/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25704976/viewspace-1319994/