我只是在学习
MySQL而且我遇到了问题.
有时,对于各种查询,MySQL开始以100%的CPU使用率计算15-20秒而不是通常会返回结果:
Query took 0.1780 sec.
它发生在非常简单的查询上.例如,此查询花了0.36秒.
(SELECT DISTINCT a1.actor
FROM actors AS a1,
actors AS a2
WHERE a1.title = a2.title
AND a1.YEAR = a2.YEAR
AND a1.actor = a2.actor
AND a1.character_name <> a2.character_name)
该表(7000行)的列表耗时0.001秒.
另一方面,当我只想将这两者结合起来时,MySQL会疯狂并开始计算30秒然后最终返回:查询耗时0.1800秒)
SELECT actor
FROM actors
WHERE actor NOT IN (SELECT DISTINCT a1.actor
FROM actors AS a1,
actors AS a2
WHERE a1.title = a2.title
AND a1.YEAR = a2.YEAR
AND a1.actor = a2.actor
AND a1.character_name <> a2.character_name)
为什么会这样?
这是另一个例子.此查询大约需要2秒钟并报告0.5
SELECT DISTINCT a1.character_name
FROM (actors AS a1
NATURAL JOIN movies AS m1),
(actors AS a2
NATURAL JOIN movies AS m2)
WHERE a1.character_name = a2.character_name
AND ( m1.title <> m2.title
OR ( m1.title = m2.title
AND m1.year <> m2.year ) )
AND m1.country <> m2.country
另一方面,此查询需要15-20秒,CPU 100%,但报告0.3秒. (唯一的区别是AND后的括号(….)
SELECT DISTINCT a1.character_name
FROM (actors AS a1
NATURAL JOIN movies AS m1),
(actors AS a2
NATURAL JOIN movies AS m2)
WHERE a1.character_name = a2.character_name
AND m1.title <> m2.title
OR ( m1.title = m2.title
AND m1.YEAR <> m2.YEAR )
AND m1.country <> m2.country
我正在使用phpMyAdmin和最新的XAMPP进行测试.
更新:
错误的查询时间似乎与phpMyAdmin有关,在命令行上我得到以下时间:
>第一个查询:MySQL:0.36秒 – PostgreSQL:0.37秒
>第二个查询:MySQL:43秒 – PostgreSQL:0.42秒
>第三个查询:MySQL:4.86秒 – PostgreSQL:0.05秒
>第4次查询:MySQL:1分5秒 – PostgreSQL:15秒
所以我有答案为什么错误地报告查询时间(phpMyAdmin或XAMPP中的错误),我感兴趣的是为什么这样的类似查询在运行时间上有这么大的差异?
更新2:
为了完整起见,我也使用PostgreSQL进行了测试