查询在第一次运行时花费很长时间 [英] Query taking very long the first time it runs

问题描述

我的网站在高峰访问时间有严重问题。在进行多次故障排除后，我发现问题是数据库。

我有一个主要的查询运行在索引检索数据表。

在忙碌的一天，网站需要30到45秒的时间加载第一次，但每次加载非常快，约5分钟后，它再次减速，最终

我直接在数据库上测试了查询，它的执行方式完全一样。

这将更多地与查询或MySQL配置有关吗？

它与一个小的返回结果，但在忙碌的一天，当列表

编辑：

感谢您提供的所有建议。

我稍微修改了查询，并对数据库做了一些修改，因为一些名称不明智。

在您完成所有建议后，我也修改了查询，以删除通配符搜索以及对不再需要的表格的一些冗余请求。

这里是查询本身：

  SELECT g.id，g.`name`，g.scores，g.sportFK，g.desc `，g.`date`，s.id AS streamid 
 FROM游戏AS $ g 
 LEFT JOIN流AS s ON s.gameFK = g.id 
 WHERE 
（g。 date> ='。 $ date。 'AND g.date< ='。 $ newdate。 '）
 AND g.sportFK IN（。$ sportfk。）
 ORDER BY g.date ASC
  

测试之后，查询的第一个性能仍然在33s运行，每个后续执行都在0.04s运行，这表明当峰值时间到来时对站点的影响将是相同的。

我还准备了 EXPLAIN 请求的信息。

这是GAMES表格

 游戏桌：
 
 SIZE：6MB 
 ROWS：14841 
 TYPE：INNODB 
  

这是用于'STREAMS'表

  STREAMS TABLE：
 
 SIZE：80MB 
 ROWS：135296 
 TYPE：MyISAM 
  

编辑：Martin感谢您就写作提出意见。这个DB确实从另一个源填充，也以相当规则的间隔填充，因此会有持续的READ和WRITE操作发生。我将不得不多做一些研究。

解决方案

好的，这个查询必须是每个数据库开发人员的性能恐怖： - ）

第一时间可以延长，因为查询编译和执行计划计算。您可以使用 EXPLAIN 查看它。

由于行为在5分钟内重复，我会说应该是索引本身或缺少索引的问题。

您应该指定是否在网站加载中只有 read （使用您的查询）以及写入。

索引重新计算操作。另一个可能的问题是锁定。让我们说每隔5分钟有人写数据，查询真的很复杂，所以它应该锁定表的写事务，你的阅读将等待直到提交/回滚。

• 在另一个表上使用索引进行计数，这个查询非常复杂，


• 一次加入大条件的另一个表格 - 加入本身是慢点


• 两个where子句=>两个索引


• 使用其他索引订购

您看到有两个索引。写操作只是锁表和索引并更新它们，你有5个。

我建议更好的数据库或域设计，如果你需要真正好的性能或定义经典的方法：是读或写经常？，然后重新设计。

My website has serious issues during peak visit times. After doing much troubleshooting I have figured out that the problem is the database.

I have this main query that runs on Index which retrieves a table of data.

on a busy day the site takes between 30 and 45 seconds to load the first time but every time after that it loads very quickly for about 5 minutes after which it slows down again and eventually the site goes down due to too much load.

I tested the query directly on the database and it performs exactly the same.

Would this be more to do with the query or the MySQL config?

It works perfectly fine with a small return result but on a busy day when the lists are very big its really slows down and kills the site.

EDIT:

Thank you for all the suggestions.

I have slightly reworked the query and did some ammendments to the DB because some of the names were not sensible.

After all your suggestions I have also modified the query to remove the Wildcard searches and also some redundant requests to a table that is no longer necessary.

Here is the Query itself:

SELECT g.id, g.`name`, g.scores, g.sportFK, g.`desc`, g.`date`, s.id AS streamid
       FROM games AS g
       LEFT JOIN streams AS s ON s.gameFK = g.id
             WHERE
                  (g.date >= '" . $date . "' AND g.date <= '" . $newdate . "') 
                  AND g.sportFK IN (" .  $sportfk . ") 
                  ORDER BY g.date ASC"

After testing this the first performance of the query is still running at 33s and every subsequent execution is running at 0.04s which sugegsts that the effect on the site will be the same when the peak time comes.

I have also prepared the requested information from EXPLAIN.

This is for the 'GAMES' table

GAMES TABLE:

SIZE: 6MB
ROWS: 14841
TYPE: INNODB

This is for the 'STREAMS' table

STREAMS TABLE:

SIZE: 80MB
ROWS: 135296
TYPE: MyISAM

EDIT: Martin thank you for making a point about the write. This DB does get populated from another source also at quite a regular interval so there will be constant READ and WRITE operations happening. I will have to do a little more research into this.

解决方案

Well, this query has to be performance horror for every DB developer :-)

First time can be prolong because of query compilation and execution plan calculation. You can look at it using EXPLAIN.

As the behavior is repeated in 5 minutes, I would say that there should be problem with indexes itself or in missing indexes.

You should specify whether there is only read within the website load or there is concurrent read (using your query) along with writes.

Index recalculation come into a play once you would also perform write operations. The other possible issue is locking. Let say every 5 minutes someone write data, the query is really complex so it should lock the table(s) for write transaction and your read would wait until commit/rollback.

Note that this query is really complex and it will never be fast - I mean really fast.

• there is count on another table using index
• one join to another table with large condition - join itself is slow point
• two where clauses => two indexes
• order using another index

You see that there is couple of indexes. Write operation just lock tables and indexes and update them, you have 5 of them. Inner count is not nice too.

I would recommend better DB or domain design if you would require really good performance or define classic approach: is read or write often? and than redesign it.

这篇关于查询在第一次运行时花费很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！