我正在使用来自 mysql 的信息创建图表。问题是，数据库的负载非常大，当您有数百人或更多人同时访问图表时，就会出现大量的停顿问题。

查询看起来像我在下面列出的两个查询:

$stmt=$db->prepare("SELECT COUNT(*) AS cnt
                    FROM stream_updates 
                        INNER JOIN members 
                    ON members.username=stream_updates.username 
                    WHERE (stream_updates.time between (NOW() - INTERVAL 1 DAY) AND NOW())
                    AND (members.username = :user) OR members.username IN (
                           SELECT friend2 as username FROM list_friends 
                           WHERE (friend1 = :user AND friend2 <> :user) 
                              UNION
                           SELECT friend1 as username FROM list_friends
                           WHERE (friend2 = :user AND friend1 <> :user)
                    )");
$stmt->bindParam(':user', $username);
$stmt->execute();
$textcnt1 = $stmt->fetchColumn();

$stmt=$db->prepare("SELECT COUNT(*) AS cnt
                    FROM stream_updates 
                        INNER JOIN members 
                    ON members.username=stream_updates.username 
                    WHERE (stream_updates.time between (NOW() - INTERVAL 2 DAY) AND (NOW() - INTERVAL 1 DAY))
                    AND (members.username = :user) OR members.username IN (
                           SELECT friend2 as username FROM list_friends 
                           WHERE (friend1 = :user AND friend2 <> :user) 
                              UNION
                           SELECT friend1 as username FROM list_friends
                           WHERE (friend2 = :user AND friend1 <> :user)
                    )");
$stmt->bindParam(':user', $username);
$stmt->execute();
$textcnt2 = $stmt->fetchColumn();

所以它的工作方式很简单，7 个查询根据发布时间获取 7 个不同的数字。这 7 个查询中的每一个都针对 5 个不同的数据库运行，总共有 35 个查询。这仅适用于页面上同时执行其他查询和执行其他任务的单个图表。

我当时在想，也许我可以将查询值存储为每 30 分钟过期一次的 cookie，以减轻一点负担，但我希望其他有更多经验的人可以提供一些关于如何做到这一点的见解？

时间和用户名列已编入索引，我不打算使用存储过程。

有没有更好的方法可以做到这一点，这样我就不会进行 35 次单独的数据库查询？

使用 SQL FIDDLE 更新

http://sqlfiddle.com/#!9/fa369f6/1

上面的 fiddle 只显示了一个示例查询。只需切换即可测试各种查询。

(NOW() - INTERVAL 1 DAY) AND NOW())

到

(NOW() - INTERVAL 2 DAY) AND (NOW() - INTERVAL 1 DAY))

澄清查询

所以这些查询的作用很简单。每个都会对给定表上与给定用户匹配的所有行进行计数，并基于该用户的 friend 。

在 sqlfiddle 示例中，返回值为 3，因为“favorites”表中所有帖子的计数再次匹配了表 list_friends 中的用户及其所有 friend 返回 3。如果我们将另一个用户添加到表中，但是执行不在 friend 列表中包含该用户，这些帖子将不被计算在内。

所有这一切都按预期工作，但 35 个查询似乎有相当大的负载，即使它们是索引列也是如此。

id   |     select_type     |    table     |  type   |  possible_keys  |   key    |  key_len  |        ref         |  rows  |  Extra
-------------------------------------------------------------------------------------------------------------------------------------
1    |       PRIMARY       |  favorites   |   ALL   |      NULL       |   NULL   |  NULL     |         NULL       |    9   |   
1    |       PRIMARY       |    members   |  eq_ref |    username     | username |    257    | favorites.username |    1   | Using where; Using index
2    | DEPENDENT SUBQUERY  | list_friends |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |    3   |    Using where
3    |   DEPENDENT UNION   | list_friends |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |    3   |    Using where
NULL |    UNION RESULT     |  <union2,3>  |   ALL   |      NULL       |   NULL   |    NULL   |        NULL        |  NULL  |

最佳答案

让我们看看您的解释结果，注意 possible_keys 列。

The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table ... If this column is NULL (or undefined in JSON-formatted output), there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again.

如果在 list_friends 表中索引 friend1、friend2 字段，您可能会看到性能的大幅提升。特别是考虑到它被使用了两次。

ALTER TABLE list_friends ADD INDEX f1f2(friend1,friend2)

您可能还受益于您最喜欢的表的time 字段的索引。

这些操作将使你的每个查询运行得更快，至于需要运行它们 35 次，不清楚为什么你实际上要运行它们 7 次并且在 5 个不同的数据库上。

关于mysql - 维护 mysql 数据库和服务器负载，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/39760112/