如何优化这个低性能的MySQL查询?
我目前正在使用以下 jsPerf 查询。在可能的情况下,您不知道 jsPerf — 有两个表:包含测试用例/修订的 pages
和包含测试内测试的代码片段的 tests
案例。
目前pages
中有937条记录,tests
中有3817条记录。
正如您所看到的,加载使用此查询的“Browse jsPerf”页面需要相当长的时间。
该查询执行大约需要 7 秒:
SELECT
id AS pID,
slug AS url,
revision,
title,
published,
updated,
(
SELECT COUNT(*)
FROM pages
WHERE slug = url
AND visible = "y"
) AS revisionCount,
(
SELECT COUNT(*)
FROM tests
WHERE pageID = pID
) AS testCount
FROM pages
WHERE updated IN (
SELECT MAX(updated)
FROM pages
WHERE visible = "y"
GROUP BY slug
)
AND visible = "y"
ORDER BY updated DESC
我已为 WHERE
子句中出现的所有字段添加了索引。我应该添加更多吗?
如何优化这个查询?
PS 我知道我可以用 PHP 实现一个缓存系统——我可能会的,所以请不要告诉我:)我也很想知道如何改进这个查询。
I’m currently using the following query for jsPerf. In the likely case you don’t know jsPerf — there are two tables: pages
containing the test cases / revisions, and tests
containing the code snippets for the tests inside the test cases.
There are currently 937 records in pages
and 3817 records in tests
.
As you can see, it takes quite a while to load the “Browse jsPerf” page where this query is used.
The query takes about 7 seconds to execute:
SELECT
id AS pID,
slug AS url,
revision,
title,
published,
updated,
(
SELECT COUNT(*)
FROM pages
WHERE slug = url
AND visible = "y"
) AS revisionCount,
(
SELECT COUNT(*)
FROM tests
WHERE pageID = pID
) AS testCount
FROM pages
WHERE updated IN (
SELECT MAX(updated)
FROM pages
WHERE visible = "y"
GROUP BY slug
)
AND visible = "y"
ORDER BY updated DESC
I’ve added indexes on all fields that appear in WHERE
clauses. Should I add more?
How can this query be optimized?
P.S. I know I could implement a caching system in PHP — I probably will, so please don’t tell me :) I’d just really like to find out how this query could be improved, too.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
使用:
Use:
您想了解如何使用 EXPLAIN。这将执行 sql 语句,并显示正在使用哪些索引以及正在执行哪些行扫描。目标是减少行扫描的数量(即数据库逐行搜索值)。
You want to learn how to use EXPLAIN. This will execute the sql statement, and show you which indexes are being used, and what row scans are being performed. The goal is to reduce the number of row scans (ie, the database searching row by row for values).
您可能想一次尝试一个子查询,看看哪一个最慢。
此查询:
使其按 slug 对结果进行排序。这可能很慢。
You may want to try the subqueries one at a time to see which one is slowest.
This query:
Makes it sort the result by slug. This is probably slow.