优化这个MySQL查询？

问题描述：

下面的查询需要FOREVER执行（MacBook w/4gig ram上30小时以上） - 我正在寻找使其运行更有效的方法。任何想法都很感激！优化这个MySQL查询？

CREATE TABLE fc AS 
SELECT threadid, 
    title, 
    body, 
    date, 
    userlogin 
FROM f 
WHERE pid 
    NOT IN (SELECT pid FROM ft) ORDER BY date;

（表 “F” 是〜1千兆/ 1,843,000行，表 “金融时报” 是168MB，216000行））

这是一个更完整系统的一部分吗？任何需要您创建整个表的临时副本的解决方案都会运行不良。如果您在该查询附近发布了一些上下文示例，那么我们可能会更轻松地为您提供帮助。 – 2010-04-13 00:51:41

感谢评论比利 - 这是朋友为我写的一系列命令的一部分。这是转换过程中的最后一步（即它只会运行一次，并且所有步骤都会事先成功完成） – 2010-04-13 01:10:36

您在ft.pid上有一个索引，对不对？ – dkretz 2010-04-13 02:11:09

答

尝试外部连接（我认为MySQL的现在支持他们），而不是不是：

create table fc as 
select f.threadid 
    , f.title 
    , f.body 
    , f.date 
    , f.userlogin 
from f 
left outer join ft 
    on f.pid = ft.pid 
where ft.pid is null 
order by date

这相当于原来的一个。 Mysql会优化它。 – zsong 2010-04-13 00:55:17

@ziang - 你想。MySQL一直因臭名昭着而臭名昭着。但是如果不测试它，你就无法知道。这个技巧我运气好多了。 – dkretz 2010-04-13 02:07:33

这是一个简单的查询与一个连接... – zsong 2010-04-13 02:14:19

答

的PID添加在FC和FT表的聚集索引。

我不知道该怎么做？我做了一个快速的谷歌搜索，找不到关于如何去做的很多信息... – 2010-04-13 01:12:41

CREATE INDEX idx_pid ON fc（pid）; CREATE INDEX idx_pid ON ft（pid）; – zsong 2010-04-13 02:05:26

那么，你不需要在fc上的索引，因为这是你创建的表格。您可能需要根据您的解释计划结果告诉您的f.pid和/或ft.pid索引（请参阅下面的注释）。以下是如何创建索引。 http://dev.mysql.com/doc/refman/5.0/en/create-index.html – 2010-04-13 02:06:58

答

从EXPLAIN PLAN开始，看看优化器说什么。然后在您进行更改时重新运行以查看是否有帮助。

我敢打赌，正确的查询将在几分钟内运行。

我从来没有使用过EXPLAIN PLAN命令 - 根据这一点，它需要在select语句上运行？ http://dev.mysql.com/doc/refman/5.0/en/explain.html – 2010-04-13 01:15:30

解释计划只是简要介绍了数据库如何解析和执行SQL查询。您正在查找索引使用情况，而不是表扫描。所以......比较你的原始陈述的解释计划结果（从“select ...”到结尾的所有内容）和Adam的陈述（从“select”到结尾），看看有没有什么重要的东西会出现在你身上真的好或坏。 – 2010-04-13 02:01:51

+1用于研究查询计划分析器以了解发生了什么。 – 2010-04-13 02:02:17

答

请确保您的ft上有pid索引。听起来您正在获取完整的跨产品，而不是通过索引进行连接。

是的 - FT上有一个索引（创建了w/CREATE INDEX idx_pid ON ft（pid）） – 2010-04-13 02:31:47

答

可能会有一些隐藏成本。多长时间需要运行这个：如果它并不需要很长时间

SELECT count(*) 
FROM f 
WHERE pid 
    NOT IN (SELECT pid FROM ft);

，那么你的命令的迟缓可能是MySQL的复制所有数据作为语句的情况下，它不能只是执行，并具有回滚。（我已经看到这与SQL Server。）

此外：是否有什么不同，如果你拿出的ORDER BY子句？

答

f中有多少行与ft中的行不匹配？在最极端的情况下，如果pid在f中唯一，您的目标表fc将包含> 1.6m行。如果行的大部分将在fc最终你会在两个阶段更好做这样的：

CREATE TABLE fc AS 
SELECT threadid, 
    title, 
    body, 
    date, 
    userlogin 
FROM f 
ORDER BY date; 

DELETE FROM fc 
WHERE pid 
    IN (SELECT pid FROM ft);

顺便说一句，你能够摆脱ORDER BY子句？这种排序可能会花费很多周期，这取决于目标表中有多少行。

另一个要考虑的是EXISTS子句...

CREATE TABLE fc AS 
SELECT threadid, 
    title, 
    body, 
    date, 
    userlogin 
FROM f 
WHERE NOT EXISTS 
    (SELECT pid FROM ft 
    WHERE ft.pid = f.id) 
ORDER BY date;

......或在我的两步版本...

DELETE FROM fc 
WHERE EXISTS 
    (SELECT pid FROM ft 
WHERE ft.pid = f.id);

EXISTS可以有很多比快当子查询生成很多行时。然而，调整总是如此，基准测试是关键。

优化这个MySQL查询？

相关推荐