python - 大表上的连接查询非常慢(机器 = Xeon 2.4 GHz，16 核，64 GB RAM)

Question

这是我的查询:

SELECT  email_data.id, email_data.source_file, email_data.report_id,
        email_data.filePath, email_data.fileName, email_data.size,
        email_data.emailID, email_data.msgID, email_data.cus, email_data.subject,
        email_data.sentto, email_data.emailFrom, email_data.hdrs, email_data.cc,
        email_data.bcc, email_data.extracted, email_data.DateTime,
        email_data.TimeStamp, email_data.OriginalDateTime, email_data.ParentID,
        email_data.reply_to, email_data.MD5Hash, email_data.duplicated,
        email_data.TimeZone, email_data.AttachName, email_data.fqdn, 
        attach_data.id, attach_data.source_file, attach_data.report_id,
        attach_data.filePath, attach_data.fileName, attach_data.size, attach_data.ext,
        attach_data.emailID, attach_data.cus, attach_data.extracted,
        attach_data.MD5Hash, attach_data.duplicated
FROM email_data 
LEFT JOIN attach_data
ON (email_data.emailID = attach_data.emailID);

两个表的组合有 50k + 记录(email_data 有 22k 记录，其他有 30k + 记录)。

以上查询耗时 90 多分钟，仍未完成。

这个:

SELECT email_data.id, attach_data.id 
FROM email_data 
LEFT JOIN attach_data 
ON (email_data.emailID = attach_data.emailID);

需要 2 分 22 秒:

我做错了什么？看来 MySQL 没有使用足够的内存来加速，它只使用了 16 个内核中的 1 个。

如何配置它以使用所有可用资源？

或者我应该查询 ID(如第二个查询)并在我的代码中循环并选择它们中的每一个吗？会导致同样的结果吗？

我需要所有这些字段和所有行，我正在将它们转换为自定义 CSV 格式，以便可以将其导出到其他软件。

列:

mysql> show columns from email_data;
+------------------+----------+------+-----+---------+----------------+
| Field            | Type     | Null | Key | Default | Extra          |
+------------------+----------+------+-----+---------+----------------+
| id               | int(11)  | NO   | PRI | NULL    | auto_increment |
| source_file      | longtext | YES  |     | NULL    |                |
| report_id        | int(11)  | YES  |     | NULL    |                |
| filePath         | longtext | YES  |     | NULL    |                |
| fileName         | longtext | YES  |     | NULL    |                |
| size             | int(11)  | YES  |     | NULL    |                |
| emailID          | longtext | YES  |     | NULL    |                |
| msgID            | longtext | YES  |     | NULL    |                |
| cus              | longtext | YES  |     | NULL    |                |
| subject          | longtext | YES  |     | NULL    |                |
| sentto           | longtext | YES  |     | NULL    |                |
| emailFrom        | longtext | YES  |     | NULL    |                |
| hdrs             | longtext | YES  |     | NULL    |                |
| cc               | longtext | YES  |     | NULL    |                |
| bcc              | longtext | YES  |     | NULL    |                |
| extracted        | longtext | YES  |     | NULL    |                |
| DateTime         | char(1)  | YES  |     | NULL    |                |
| TimeStamp        | int(11)  | YES  |     | NULL    |                |
| OriginalDateTime | char(1)  | YES  |     | NULL    |                |
| ParentID         | longtext | YES  |     | NULL    |                |
| reply_to         | longtext | YES  |     | NULL    |                |
| MD5Hash          | longtext | YES  |     | NULL    |                |
| duplicated       | char(1)  | YES  |     | NULL    |                |
| TimeZone         | char(1)  | YES  |     | NULL    |                |
| AttachName       | longtext | YES  |     | NULL    |                |
| fqdn             | longtext | YES  |     | NULL    |                |
+------------------+----------+------+-----+---------+----------------+

attach_data 几乎相同

Answer 1

几乎可以肯定 attach_data.emailID 缺少索引。考虑到查询引擎必须遍历电子邮件数据的每一行，如果索引丢失，它必须遍历 attach_data 的每一行，即使在找到匹配项之后也是如此。

您应该对您的查询运行一个EXPLAIN 来查看MySql 实际在做什么。如果索引缺失，您将进行 22,000 x 30,000 次比较，或大约 6.6 亿次比较来构建结果数据集。如果您的 ID 是字符串，那么您的旅程就很漫长了。

如果您执行索引 attach_data.emailId，您会将比较次数减少到大约 22,000 x log(30,000)，或大约 330,000 次比较。巨大的差异。使用 HASH 索引将使这更快(下限是 22,000 次比较)。如果索引丢失，您可以在事后附加它们。

老实说，您应该考虑 LIMIT 跳过并获取一个结果窗口。这将为您省去很多与客户端之间来回传输数据的麻烦。您可能会发现这种流量会导致慢速连接超时(我同意另一位发帖人的观点，奇怪的是您没有超时)

更新

圣牛。看到您对问题的更新，您绝对应该只拉回非长文本字段，遍历这些字段并一次拉回一个长文本字段。但是鉴于您的需要是将 mysql 表转储到 csv，我建议您查看 mysqldump .它可以为您将数据库备份为 CSV 文件。