emlog用户过多导致发布文章太慢的解决办法

问题描述

本文仅适用于 emlog 5.3.1

使用 emlog 搭建的博客如果注册用户过多，会导致站点统计缓存的时候运行时间过长，具体表现为发布文章，更新数据，评论之类的写操作时很慢，甚至造成运行超时错误。

原因

原因是缓存站点统计的函数是通过循环遍历用户来分别统计相关数据，每次统计都会进行数据库的连接、查询。当注册用户特别多的时候，就会进行大量的mysql连接查询，就算每次查询需要1ms， 运行过万次，也必然导致程序运行时间过长。

解决方法

如果用户量不是特别大（1000以下）则不建议修改程序内核，如有需要可通过修改 include/lib/cache.php 文件中的 mc_sta 函数修复:

/**
 * 站点统计缓存
 */
private function mc_sta() {
    $sta_cache = array();
    $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog WHERE type='blog' AND hide='n' AND checked='y' ");
    $lognum = $data['total'];

    $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog WHERE type='blog' AND hide='y'");
    $draftnum = $data['total'];     

    $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog WHERE type='blog' AND hide='n' AND checked='n' ");
    $checknum = $data['total'];         

        $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "comment WHERE hide='n' ");
    $comnum = $data['total'];   

        $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "comment WHERE hide='y' ");
    $hidecom = $data['total'];

    $data = $this->db->once_fetch_array("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "twitter ");
    $twnum = $data['total'];

    $sta_cache = array(
        'lognum' => $lognum,
        'draftnum' => $draftnum,
        'comnum' => $comnum,
        'comnum_all' => $comnum + $hidecom,
        'twnum' => $twnum,
        'hidecomnum' => $hidecom,
        'checknum' => $checknum,
    );

    $logNumQuery = $this->db->query("SELECT author,COUNT(*) AS total FROM " . DB_PREFIX . "blog WHERE hide='n' and type='blog' group by author");
    $draftNumQuery = $this->db->query("SELECT author,COUNT(*) AS total FROM " . DB_PREFIX . "blog WHERE hide='y' and type='blog' group by author");
    $commentNumQuery = $this->db->query("SELECT author,COUNT(*) AS total FROM " . DB_PREFIX . "comment AS a, " . DB_PREFIX . "blog AS b WHERE a.gid = b.gid group by b.author");
    $hidecommentNumQuery = $this->db->query("SELECT author,COUNT(*) AS total FROM " . DB_PREFIX . "comment AS a, " . DB_PREFIX . "blog AS b WHERE a.gid = b.gid and a.hide='y' group by b.author");
    $twnumQuery = $this->db->query("SELECT author,COUNT(*) AS total FROM " . DB_PREFIX . "twitter group by author");

    $dataHash = array(
        'lognum' => array(),
        'draftnum' => array(),
        'commentnum' => array(),
        'hidecommentnum' => array(),
        'twnum' => array(),
    );

    while ($row = $this->db->fetch_array($logNumQuery)) {
        $dataHash['lognum'][$row['author']] = $row['total'];
    }

    while ($row = $this->db->fetch_array($draftNumQuery)) {
        $dataHash['draftnum'][$row['author']] = $row['total'];
    }
    while ($row = $this->db->fetch_array($commentNumQuery)) {
        $dataHash['commentnum'][$row['author']] = $row['total'];
    }
    while ($row = $this->db->fetch_array($hidecommentNumQuery)) {
        $dataHash['hidecommentnum'][$row['author']] = $row['total'];
    }
    while ($row = $this->db->fetch_array($twnumQuery)) {
        $dataHash['twnum'][$row['author']] = $row['total'];
    }

    $query = $this->db->query("SELECT uid FROM " . DB_PREFIX . "user");
    while ($row = $this->db->fetch_array($query)) {
        $sta_cache[$row['uid']] = array(
            'lognum' => isset($dataHash['lognum'][$row['uid']]) ? $dataHash['lognum'][$row['uid']] : 0,
            'draftnum' => isset($dataHash['draftnum'][$row['uid']]) ? $dataHash['draftnum'][$row['uid']] : 0,
            'commentnum' => isset($dataHash['commentnum'][$row['uid']]) ? $dataHash['commentnum'][$row['uid']] : 0,
            'hidecommentnum' => isset($dataHash['hidecommentnum'][$row['uid']]) ? $dataHash['hidecommentnum'][$row['uid']] : 0,
            'twnum' => isset($dataHash['twnum'][$row['uid']]) ? $dataHash['twnum'][$row['uid']] : 0
        );
    }

    $cacheData = serialize($sta_cache);

    $this->cacheWrite($cacheData, 'sta');
}