避免页面重载时过多的SQL查询

Question

我有一个网站页面，在加载时，对具有150,000,000行的表激发10个不同的查询。

通常情况下，页面加载时间不到2秒--但如果我刷新太频繁，就会产生很多查询，这会使页面加载时间降低多达10秒。

我如何避免触发所有这些查询，因为它会杀死我的数据库？

我还没有缓存。该网站的工作方式如下。我有一个存储了所有URI的表。如果用户输入URL，我将从被调用的URL中获取URI，并检查表中是否存储了URI。如果URI存储在表中，我将从关系数据库中的其他表中提取相应的数据。

其中一个PHP文件的示例代码从其他表中提取信息，如下所示

<?php
set_time_limit(2);
define('MODX_CORE_PATH', '/path/to/modx/core/');
define('MODX_CONFIG_KEY','config');
require_once MODX_CORE_PATH . 'model/modx/modx.class.php';

// Criteria for foreign Database
$host = 'hostname';
$username = 'user';
$password = 'password';
$dbname = 'database';
$port = 3306;
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$dbname;port=$port;charset=$charset";
$xpdo = new xPDO($dsn, $username, $password);

// Catch the URI that is called
$pageURI = $_SERVER["REQUEST_URI"];

// Get the language token saved as TV "area" in parent and remove it
if (!isset($modx)) return '';

$top = isset($top) && intval($top) ? $top : 0;
$id= isset($id) && intval($id) ? intval($id) : $modx->resource->get('id');
$topLevel= isset($topLevel) && intval($topLevel) ? intval($topLevel) : 0;
if ($id && $id != $top) {
    $pid = $id;
    $pids = $modx->getParentIds($id);
    if (!$topLevel || count($pids) >= $topLevel) {
        while ($parentIds= $modx->getParentIds($id, 1)) {
            $pid = array_pop($parentIds);
            if ($pid == $top) {
                break;
            }
            $id = $pid;
            $parentIds = $modx->getParentIds($id);
            if ($topLevel && count($parentIds) < $topLevel) {
                break;
            }
        }
    }
}
$parentid = $modx->getObject('modResource', $id);
$area = "/".$parentid->getTVValue('area');
$URL = str_replace($area, '', $pageURI);
$lang= $parentid->getTVValue('lang');

// Issue queries against the foreign database:
$output = '';
$sql = "SELECT epf_application_detail.description FROM epf_application_detail INNER JOIN app_uri ON epf_application_detail.application_id=app_uri.application_id WHERE app_uri.uri = '$URL' AND epf_application_detail.language_code = '$lang'";
foreach ($xpdo->query($sql) as $row) {
    $output .= nl2br($row['description']);
}
return $output;

Answer 1

在不知道您使用的是什么语言的情况下，这里有一些伪代码可以帮助您入门。

您可以创建一个名为"cache“的单独的表，而不是每次页面加载时都触发一个大型查询。您可以运行查询，然后将查询中的数据存储在该缓存表中。然后，当您的页面加载时，您可以查询缓存表，它将小得多，并且当您多次刷新页面时，不会使事情停滞。

伪代码(可以使用cronjob或其他工具按一定的时间间隔执行，以保持缓存新鲜。)：

为每个查询运行您的十个大型查询，将结果添加到cache，如下所示：

query_id  |  query_data
----------------------------------------------------
      1   |  {whatever your query data looks like}

然后，当页面加载时，让每个查询从cache收集数据

需要注意的是，对于缓存表，您需要经常刷新它。(可以是获取更多数据的频率，也可以是设置的时间间隔，例如每5分钟或更长时间。)

Answer 2

您应该做一些服务器端缓存和优化。

如果我是你，我会为你的数据库安装Memcached。

我也会考虑一些静态缓存，比如Varnish，它会将每个页面缓存为静态HTML，并在第二个页面(和第三个页面，第四个页面，...)请求不必由PHP和MySQL处理，当你第二次加载它时(当然是在缓存生命周期内)，它的加载速度会快很多。

最后，您可以通过安装APC (或其他操作码缓存)来帮助PHP端更好地处理数据。