临时表

上个星期; 我正在处理一个问题，其中与OLAP登台相关的子查询在生产服务器中运行了大约2个多小时，最后确定为了让查询在不到7秒的时间内运行。

这有点有趣; MySQL子查询世界中的一种已知问题，以及MySQL对中间派生表结果进行更多控制的限制之一。

有时我们可以重写子查询，以便不涉及派生表复杂性; 但不好的是; 此特定子查询是UPDATE语句的一部分; 因此，由于其自​​身的限制，并非所有子查询都可以重写，特别是当它们是UPDATE或DELETE语句的一部分时。

问题：

这是问题查询的子集（取自复杂的大查询），你可以看到它运行大约6分钟，我用于在Mac上测试的小数据子集（所有表都是基于InnoDB的）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

-------------- SELECT     SUM(aggrpt.imps) as imps,     SUM(aggrpt.clicks) as clicks,     SUM(aggrpt.pos) as pos   FROM aggrpt LEFT JOIN (     SELECT     DISTINCT ext_group_id, group_id     FROM sub ) sub2  ON(sub2.ext_group_id=aggrpt.adgroupid)   GROUP BY aggrpt.report_date, aggrpt.campaignid, aggrpt.adgroupid, aggrpt.keywordid ORDER BY NULL INTO OUTFILE '/tmp/test-sub.txt' --------------   Query OK, 47827 rows affected (6 min 47.48 sec)

如何解决 - 解决方案：

通过索引连接列，将派生表（在上面的情况下为sub2）移动到视图没有帮助; 和时间或多或少相同。如下所述的两种替代方法使得相同的查询在不到10秒的时间内运行。

1. 通过创建外部表并添加索引而不是使用派生表
2. 在派生表临时结果之上添加索引（在MySQL服务器内部作为查询执行的一部分）。

情况1：

这是一个已知的替代方案，许多人在生产中使用它，通过在MySQL中完全避免派生表和/或子查询（其他开源数据库，如PostgreSQL在子查询中运行良好），通过创建（临时）表派生表。唯一能产生重大影响的是在这个中间表上添加一个索引; 没有索引，它执行的时间大致相同。

转自：http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

-------------- CREATE TEMPORARY TABLE sub_temp SELECT DISTINCT ext_group_id, group_id FROM sub -------------- Query OK, 72385 rows affected (0.81 sec) Records: 72385  Duplicates: 0  Warnings: 0 -------------- alter table sub_temp add index i_ext_group_id(ext_group_id) -------------- Query OK, 72385 rows affected (0.10 sec) Records: 72385  Duplicates: 0  Warnings: 0 -------------- SELECT     SUM(aggrpt.imps) as imps,     SUM(aggrpt.clicks) as clicks,     SUM(aggrpt.pos) as pos   FROM aggrpt LEFT JOIN sub_temp sub ON(sub.ext_group_id=aggrpt.adgroupid)   GROUP BY     aggrpt.report_date,     aggrpt.campaignid,     aggrpt.adgroupid,     aggrpt.keywordid ORDER BY NULL INTO OUTFILE '/tmp/test-sub-temp.txt' --------------   Query OK, 47827 rows affected (10.41 sec)

如你看到的; 这是一个很大的改进，从407秒（6分47.48秒）到10秒; 但在临时表上没有索引i_ext_group_id ; 然后时间将照常在6分钟。

案例2：

服务器引擎应该足够聪明，可以在中间结果临时表上添加隐式索引（在本例中为sub2，列为i_ext_group_id的索引）; 但MySQL不支持此功能，或者至少应允许人们使用FORCE / USE INDEX指定一个，以便服务器可以在临时表结果上添加一个。

它应该添加一个隐式索引，以防它加入更大的外部表/结果（成本估算一对一反向查找与每个条目的完整扫描）。如果与派生的一个（一个或多个）相比，它加入较小的外部表结果，则不需要添加隐式索引

例如; 我修补了MySQL以支持这种语法，因此它会自动在派生表sub2结果上添加隐式索引（小hack），并且查询会在7秒内立即返回。

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SELECT     SUM(aggrpt.imps) as imps,     SUM(aggrpt.clicks) as clicks,     SUM(aggrpt.pos) as pos   FROM aggrpt LEFT JOIN (     SELECT     DISTINCT ext_group_id, group_id     FROM sub ) sub2 <strong>USE INDEX(ext_group_id)</strong> ON(sub2.ext_group_id=aggrpt.adgroupid)   GROUP BY aggrpt.report_date, aggrpt.campaignid, aggrpt.adgroupid, aggrpt.keywordid ORDER BY NULL INTO OUTFILE '/tmp/test-sub-force.txt' --------------   Query OK, 47827 rows affected (7.18 sec)

这又是一个很大的进步; 但再次使用USE / FORCE INDEX是一个黑客攻击; 他的也可以用列名提示; 在查询处理中对临时结果数据进行隐式索引的基本简单方法（前后物化数据取决于成本估算）

对MYSQL优化器的更改

但是这篇文章的想法是显示中间临时表中的索引对查询性能的重要性 ; 和优化器应该足够聪明，可以隐式识别和添加一个。逻辑应该与当前优化器通过估计成本选择索引与非索引扫描的方式类似，或者更倾向于依赖于派生数据的提示。