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

yufulou 2011-01-18 04:04:31
有两个select ... into outfile '...',单个运行的时候,一个是个位数级的查询,查询时间可以忽略不计,一个是百万级的查询(没有任何排序,分组),查询时间为10s左右
我想把他们一起放入同一个文件,所以选用union all,结果时间变成了1分07秒…
这是正常情况吗?
...全文
1904 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
yufulou 2011-01-21
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 loveflea 的回复:]



High Performance MySQL, Second Edition P195

Optimizing UNION
MySQL always executes UNION queries by creating a temporary table and filling it
with the UNION results. MySQL can’t apply as ma……
[/Quote]

牛!
小小小小周 2011-01-21
  • 打赏
  • 举报
回复
楼上说的对,顶一个
loveflea 2011-01-21
  • 打赏
  • 举报
回复


High Performance MySQL, Second Edition P195

Optimizing 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).
yufulou 2011-01-21
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 loveflea 的回复:]

union 会生成临时表,然后再从临时表中读记录出来(中间增加了一个临时表,先写在读,而您的有个表记录比较多,所以速度变慢很多)
[/Quote]
我也这么觉得,不过,这个理论有出处吗…?
yufulou 2011-01-20
  • 打赏
  • 举报
回复
分别的:
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 |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
yufulou 2011-01-20
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 acmain_chm 的回复:]

1. 直接把你的SQL语句贴出来,这样别人就不需要去研究你的存储过程了。
2. 贴出你的EXPLAIN (带UNIOIN和不带UNION都贴出来对比)
[/Quote]


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 | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
ACMAIN_CHM 2011-01-20
  • 打赏
  • 举报
回复
1. 直接把你的SQL语句贴出来,这样别人就不需要去研究你的存储过程了。
2. 贴出你的EXPLAIN (带UNIOIN和不带UNION都贴出来对比)
yufulou 2011-01-20
  • 打赏
  • 举报
回复
用的存储过程:最后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;
END


EXPLAIN最后一个语句的结果为:

+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| 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 | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+

loveflea 2011-01-20
  • 打赏
  • 举报
回复
union 会生成临时表,然后再从临时表中读记录出来(中间增加了一个临时表,先写在读,而您的有个表记录比较多,所以速度变慢很多)
yufulou 2011-01-20
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 mr_mablevi 的回复:]

楼主show index from 这2张表看看索引信息
[/Quote]
结果少的tmp_table_site_use_count是临时表,没什么索引信息,但就几条数据,查询速度很快,可以忽略不计;
下面的从table_site拿出n百万条,就是从一个表里拿出来的,除了查找的id为主键以外,都不是索引,需要查找真实表
小小小小周 2011-01-20
  • 打赏
  • 举报
回复
楼主show index from 这2张表看看索引信息
mysqldbd 2011-01-18
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 mr_mablevi 的回复:]
引用 1 楼 acmain_chm 的回复:

你的语句是什么?

贴出EXPLAIN以供分析。

给出这些要求
[/Quote]

后生可畏啊!
小小小小周 2011-01-18
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 acmain_chm 的回复:]

你的语句是什么?

贴出EXPLAIN以供分析。
[/Quote]
给出这些要求
mysqldbd 2011-01-18
  • 打赏
  • 举报
回复
[Quote=引用楼主 yufulou 的回复:]
有两个select ... into outfile '...',单个运行的时候,一个是个位数级的查询,查询时间可以忽略不计,一个是百万级的查询(没有任何排序,分组),查询时间为10s左右
我想把他们一起放入同一个文件,所以选用union all,结果时间变成了1分07秒…
这是正常情况吗?
[/Quote]

貌似好没道理的。

你贴出explain,我们分析分析。
ACMAIN_CHM 2011-01-18
  • 打赏
  • 举报
回复
你的语句是什么?

贴出EXPLAIN以供分析。

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧