所以我有很长的运行MySQL查询:优化比较两个大型MySQL表中的数据
显然有结果:中断查询后,我发现输出文件确实被修改了,并且有数千条记录。但是,在我中断长时间运行的查询的那一刻,结果被刷新到文件中。
我使用了命令:
mysql> SELECT ar.email FROM activation_request ar WHERE ar.date_confirmed is not null AND NOT EXISTS (SELECT 1 FROM user u WHERE u.username = ar.email) INTO OUTFILE '/tmp/results_after_adding_indexes.csv';
我如何同时监控/开球?该文件是在文件系统中创建的,但它是空的(直到查询结束或中断;无论先到什么(。
某些查询以线性方式运行。 也就是说,它们以规则的速度产生另一行。
有些查询甚至在发出"第一"行之前就完成了 99% 的工作。 如果查询具有ORDER BY
,则很容易看到这一点。
某些连接会在将任何内容发送回客户端之前计算整个结果。 某些连接会分块输出。
连接问题可能是可以解决的。 但是,使用类似排序的东西"监视"查询是不切实际的。
由于INTO OUTFILE
没有用于分页输出的支持选项,因此我认为您需要编写自己的导出脚本。
(请注意,您还可以按照INTO OUTFILE
文档的说明看到LOAD DATA
完整性选项,但唯一有希望的是LINES
选项,该选项适用于自定义线路仪表。
假设您不需要记录行上的数据库引擎选项,则脚本很简单:
- 报表/导出查询的"gimme all"版本 - 除非数据集太大,在这种情况下,请包含分页循环;
- 循环结果以执行简单的转换; 在传出行
- 的途中将传出行存储在数组中;
- 每n行将该队列写入文件;
- 并确保不要仅仅因为它们在最终块中不包含n行而删除记录集的最后位。
这是你的查询,对吗? PK是id
,对吗?
SELECT l.email
FROM activation_request l
LEFT JOIN user r ON r.username = l.email
WHERE l.date_confirmed is not null
AND r.username IS NULL
使用一些应用程序代码,在id
上创建一个循环,一次可能 10,000 个:
SELECT l.email
FROM activation_request l
LEFT JOIN user r ON r.username = l.email
WHERE l.date_confirmed is not null
AND r.username IS NULL
AND l.id BETWEEN 1 AND 10000; -- added
计时;然后将最后一行更改为
AND l.id BETWEEN 10001 AND 20000;
等。
这将:
- 提供缺少用户的所需行列表,尽管是零碎的。
- 在第一个块之后,您可以推断出预期的总时间。
- 经过几个块,您可能会感觉到它的速度是否不同。
- 您可以将数据放在一起以提供进度条。
上面假设ids
是"密集的";也就是说没有删除ID。 如果存在已删除的 id,则块将不会是一致的 10000 行(而是更少的行(。 这可以通过使用SELECT id FROM .. WHERE id > $leftoff ORDER BY id .. LIMIT 10000,1
找出下一个端点来解决。
更多关于分块的信息:http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks(注意:这涉及到删除。
如果你通过NOT EXISTS
方法做几个块,你可以感觉到哪个更快。 警告:运行两次以避免缓存弄乱计时。