为什么union all会降低那么多速度？

Question

有两个select ... into outfile '...'，单个运行的时候，一个是个位数级的查询，查询时间可以忽略不计，一个是百万级的查询（没有任何排序，分组），查询时间为10s左右
我想把他们一起放入同一个文件，所以选用union all，结果时间变成了1分07秒…
这是正常情况吗？

Answer 1

你的语句是什么？贴出EXPLAIN以供分析。

Answer 2

貌似好没道理的。你贴出explain，我们分析分析。

Answer 3

用的存储过程：最后union all的那两句单拿出来，上面那个就几个结果，所以是瞬间就能完成的，下面那个是百万级的，但也可以只用10秒搞定，合起来就要花1分07秒……
DROP PROCEDURE IF EXISTS procedure_table_site_use_issue_test;
CREATE PROCEDURE procedure_table_site_use_issue_test(IN out_file_path VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp_table_site_use_count;
CREATE TEMPORARY TABLE tmp_table_site_use_count SELECT s.id AS id, s.m_main_type AS m_main_type, s.m_use_count AS m_use_count, u.u_use_count AS u_use_count, s.url AS url 
FROM table_site_use_count AS u, table_site AS s where s.id = u.site_id and s.state = 1;

SET SESSION group_concat_max_len = 102400;
SELECT GROUP_CONCAT(DISTINCT site_id  SEPARATOR ',') INTO @tmp_sites_concat FROM table_site_use_count;

SET @sql_str = CONCAT('SELECT id, m_main_type, m_use_count, u_use_count, url ',
'FROM tmp_table_site_use_count ',
'UNION ALL ',
'SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url ',
'FROM table_site ',IF(@tmp_sites_concat IS NULL, '',CONCAT('WHERE id NOT IN (', @tmp_sites_concat,') ')),
'INTO OUTFILE \'',out_file_path,'\'');
PREPARE sql_query FROM @sql_str;
EXECUTE sql_query;
DEALLOCATE PREPARE sql_query;
        DROP TEMPORARY TABLE IF EXISTS tmp_table_site_use_count;
ENDEXPLAIN最后一个语句的结果为：+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type  | table                    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY      | tmp_table_site_use_count | ALL  | NULL          | NULL | NULL    | NULL |       5 |             |
|  2 | UNION        | table_site               | ALL  | PRIMARY       | NULL | NULL    | NULL | 1748669 | Using where |
| NULL | UNION RESULT | <union1,2>               | ALL  | NULL          | NULL | NULL    | NULL |    NULL |             |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+

Answer 4

1. 直接把你的SQL语句贴出来，这样别人就不需要去研究你的存储过程了。
2. 贴出你的EXPLAIN （带UNIOIN和不带UNION都贴出来对比）

Answer 5

EXPLAIN SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url 
        FROM table_site WHERE id NOT IN (1253419,1253478,1265366,1613649,1919261) 
        UNION ALL 
        SELECT id, m_main_type, m_use_count, u_use_count, url 
        FROM tmp_table_site_use_count 
        INTO OUTFILE '/tmp/main_policy_tmp_test.sql'+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type  | table                    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | PRIMARY      | table_site               | ALL  | PRIMARY       | NULL | NULL    | NULL | 1719672 | Using where |
|  2 | UNION        | tmp_table_site_use_count | ALL  | NULL          | NULL | NULL    | NULL |       5 |             |
| NULL | UNION RESULT | <union1,2>               | ALL  | NULL          | NULL | NULL    | NULL |    NULL |             |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+

Answer 6

分别的：
EXPLAIN SELECT id, m_main_type, m_use_count, u_use_count, url FROM tmp_table_site_use_count;
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table                    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tmp_table_site_use_count | ALL  | NULL          | NULL | NULL    | NULL |    5 |       |
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
EXPLAIN SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url FROM table_site WHERE id NOT IN (1253419,1253478,1265366,1613649,1919261)
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table_site | ALL  | PRIMARY       | NULL | NULL    | NULL | 1719672 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+

Answer 7

楼主show index from 这2张表看看索引信息

Answer 8

结果少的tmp_table_site_use_count是临时表，没什么索引信息，但就几条数据，查询速度很快，可以忽略不计；
下面的从table_site拿出n百万条，就是从一个表里拿出来的，除了查找的id为主键以外，都不是索引，需要查找真实表

Answer 9

union 会生成临时表，然后再从临时表中读记录出来（中间增加了一个临时表，先写在读，而您的有个表记录比较多，所以速度变慢很多）

Answer 10

有High Performance MySQL, Second Edition P195Optimizing UNION
MySQL always executes UNION queries by creating a temporary table and filling it
with the UNION results. MySQL can’t apply as many optimizations to UNION queries as
you might be used to. You might have to help the optimizer by manually “pushing down” 
WHERE, LIMIT, ORDER BY, and other conditions (i.e., copying them, as appropri-
ate, from the outer query into each SELECT in the UNION).
It’s important to always use UNION ALL, unless you need the server to eliminate dupli-
cate rows. If you omit the ALL keyword, MySQL adds the distinct option to the tem-
porary table, which uses the full row to determine uniqueness. This is quite
expensive. Be aware that the ALL keyword doesn’t eliminate the temporary table,
though. MySQL always places results into a temporary table and then reads them
out again, even when it’s not really necessary (for example, when the results could be
returned directly to the client).