我确信我一定在做一些愚蠢的事情,但通常情况下,我无法弄清楚它是什么。
我正在尝试运行此查询:
SELECT `f`.`FrenchWord`, `f`.`Pronunciation`, `e`.`EnglishWord`
FROM (`FrenchWords` f)
INNER JOIN `FrenchEnglishMappings` m ON `m`.`FrenchForeignKey`=`f`.`id`
INNER JOIN `EnglishWords` e ON `e`.`id`=`m`.`EnglishForeignKey`
WHERE `f`.`Pronunciation` = '[whatever]';
当我运行它时,发生的事情对我来说似乎很奇怪。我得到的查询结果很好,大约 2 秒内 0.002 行。
但是,我也得到了一个巨大的 CPU 峰值,SHOW PROCESSLIST
显示了该查询的两个相同进程,状态为"复制到磁盘上的 tmp 表"。这些似乎无休止地运行,直到我杀死它们或系统冻结。
所涉及的表都不大 - 每个表在 100k 到 600k 行之间。 tmp_table_size
和max_heap_table_size
都是16777216。
编辑:声明上的EXPLAIN
给出:
+编辑将发音的键减少到112
+----+-------------+-------+--------+-------------------------------------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+
| 1 | SIMPLE | f | ref | PRIMARY,Pronunciation | Pronunciation | 112 | const | 2 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | m | ref | tmpindex,CombinedIndex,FrenchForeignKey,EnglishForeignKey | tmpindex | 4 | dict.f.id | 1 | Using index |
| 1 | SIMPLE | e | eq_ref | PRIMARY,id | PRIMARY | 4 | dict.m.EnglishForeignKey | 1 | |
+----+-------------+-------+--------+-------------------------------------------------------------+-----------------+---------+----------------------------+------+----------------------------------------------+
如果有人能指出可能导致这种情况的原因,我将不胜感激。我真的不明白的是 MySQL 在做什么 - 当然,如果查询完成,那么它不需要做任何其他事情吗?
更新
感谢您的所有回复。我从他们身上学到了一些东西。在遵循 nrathaus 的建议后,这个查询的速度大大加快了。我在法语单词中添加了一个包含unhex(md5(发音((的发音哈希二进制(16(列。索引的 keylen 为 16(而发音上的 varchar 索引为 600+(,现在查询速度要快得多。
正如解释所说,你的密钥大小是巨大的:602,这需要MySQL写下数据。
你需要减少(大大(凯伦,我相信建议低于 128。
我建议您创建一个名为MD5_FrenchWord的列,其中包含法语单词的MD5值。然后将此列用于分组依据。这假设您正在寻找相似之处,当您分组时,而不是实际值
您滥用了GROUP BY
.除非SELECT
子句中还有汇总函数(如MAX(something)
或COUNT(*)
(,否则此子句完全没有意义。
尝试删除GROUP BY
,看看是否有帮助。
目前尚不清楚您要用GROUP BY
做什么.但是,如果您尝试删除结果集,则可以尝试SELECT DISTINCT
。
进一步研究这个问题,似乎您可能会从几个复合索引中受益。
首先,能否确保表声明在尽可能多的列中NOT NULL
?
其次,您正在从法语单词表中检索发音、法语单词和 id,因此请在该表上尝试使用此复合索引。然后,您的查询将能够直接从索引中获取所需的内容,从而节省一堆磁盘 io。请注意,在复合索引声明中首先提到发音,因为这是您要搜索的值。这允许MySQL对索引进行查找,并直接从索引中获取所需的其他信息,而无需返回表本身。
(Pronunciation, FrenchWord, id)
您正在通过 id 从英语单词中检索英语单词。因此,同样的推理可以适用于这个复合指数。
(id, Englishword)
最后,一旦您使用SELECT DISTINCT
,我就无法判断您的 ORDER BY 是做什么用的。您可以尝试摆脱它。但这可能不会有什么区别。
试一试。 如果您的MySQL服务器在进行这些更改后仍在抖动,则您遇到了某种配置问题。