用的存储过程:最后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 | | +----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
union 会生成临时表,然后再从临时表中读记录出来(中间增加了一个临时表,先写在读,而您的有个表记录比较多,所以速度变慢很多)
有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).
貌似好没道理的。你贴出explain,我们分析分析。
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 | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
2. 贴出你的EXPLAIN (带UNIOIN和不带UNION都贴出来对比)
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 | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
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 |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
下面的从table_site拿出n百万条,就是从一个表里拿出来的,除了查找的id为主键以外,都不是索引,需要查找真实表
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).