最好网址
wangzhi.best
当前位置：首页文章正文

Z-Blog 3万篇文章站点性能优化实战：标签映射表改造与全站缓存方案

admin3920分钟前

一、问题背景

爱来健康使用 Z-Blog PHP 1.7.5搭配随然 Air 主题，目前文章数量已积累到近3.2万篇。近期发现访问标签页（如 /tags/糖尿病/）时响应极慢，MySQL 慢查询日志频繁出现类似以下记录：

SELECT COUNT(*) AS num FROM zbp_post 
WHERE log_Type = '0' AND log_Status = '0' 
AND log_IsTop >= '0' AND log_Tag LIKE '%{31714}%';

该查询对 log_Tag 字段使用 LIKE '%{tag_id}%' 模糊匹配，导致每次访问标签页都要全表扫描 3 万行，耗时 1~3 秒，严重影响用户体验和服务器负载。

二、优化原理

Z-Blog 默认将文章所属标签以 {1}{2}{3} 的格式存储在 zbp_post.log_Tag 字段中。当访问标签页时，系统用 LIKE '%{tag_id}%' 来查找关联文章，这种写法在 MySQL 中无法使用普通索引，必然触发全表扫描。

解决方案是建立一张独立的 文章-标签映射表 zbp_post_tag_map，结构为 post_id + tag_id，并给 tag_id 建立单列索引。后续标签查询改为通过映射表 INNER JOIN 关联，由索引直接定位，响应时间从秒级降到毫秒级。

三、详细操作步骤

步骤 1：在主题 include.php 中建立映射表及同步函数

编辑主题文件 /zb_users/theme/suiranx_air/include.php，在文件最底部追加以下函数：

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}

然后在 ActivePlugin_suiranx_air 函数内注册同步钩子：

Add_Filter_Plugin('Filter_Plugin_PostArticle_Succeed', 'TagMapSync_UpdateMap');
Add_Filter_Plugin('Filter_Plugin_DelArticle_Succeed', 'TagMapSync_DeleteMap');

步骤 2：批量导入历史文章标签数据

新建文件 /www/wwwroot/wangzhanwww/sync_tag_history.php，内容如下：

<?php
require 'zb_system/function/c_system_base.php';
$zbp->Load();
TagMapSync_CreateTable();
$tablename = TagMapSync_GetTableName();

$batchSize = 500; $offset = 0;
$totalPosts = 0; $totalMaps = 0;
$startTime = microtime(true);
echo "开始同步历史文章标签映射...\n";

while (true) {
    $sql = "SELECT log_ID, log_Tag FROM {$zbp->table['Post']} ORDER BY log_ID ASC LIMIT {$offset}, {$batchSize}";
    $rows = $zbp->db->Query($sql);
    if (empty($rows)) break;
    $values = []; $postCount = 0;
    foreach ($rows as $row) {
        $post_id = (int)$row['log_ID'];
        $tagStr = $row['log_Tag'];
        if (!empty($tagStr) && preg_match_all('/\{(\d+)\}/', $tagStr, $matches)) {
            foreach ($matches[1] as $tag_id) {
                $tag_id = (int)$tag_id;
                if ($post_id > 0 && $tag_id > 0) $values[] = "({$post_id}, {$tag_id})";
            }
        }
        $postCount++;
    }
    if (!empty($values)) {
        $chunks = array_chunk($values, 1000);
        foreach ($chunks as $chunk) {
            $insertSql = "INSERT IGNORE INTO {$tablename} (post_id, tag_id) VALUES " . implode(',', $chunk);
            $zbp->db->Query($insertSql);
            $totalMaps += count($chunk);
        }
    }
    $totalPosts += $postCount; $offset += $batchSize;
    echo "已处理文章: {$totalPosts} / 累计映射: {$totalMaps}\n";
    if ($postCount < $batchSize) break;
}
$usedTime = round(microtime(true) - $startTime, 2);
echo "\n同步完成！处理文章: {$totalPosts}，插入映射: {$totalMaps}，耗时: {$usedTime} 秒\n";

SSH 进入网站根目录执行：

cd /www/wwwroot/wangzhanwww
php sync_tag_history.php

预期输出：

同步完成！处理文章: 31406，插入映射: 146781，耗时: 2.81 秒

步骤 3：为映射表添加索引

mysql -uroot -p
USE shujukumingcheng;
ALTER TABLE zbp_post_tag_map ADD INDEX idx_tag_id (tag_id);

步骤 4：修改 Z-Blog 核心路由文件（关键步骤）

先备份核心文件：

cp /www/wwwroot/wangzhanwww/zb_system/function/c_system_route.php \
   /www/wwwroot/wangzhanwww/zb_system/function/c_system_route.php.bak

编辑 zb_system/function/c_system_route.php，找到以下两处代码并替换。

第 1 处（约第 673 行）：

// 原代码
$w[] = array('LIKE', 'log_Tag', '%{' . (int) $tags . '}%');

// 替换为
$mapTable = $zbp->db->dbpre . 'post_tag_map';
$tagId = (int)$tags;
$w[] = array('CUSTOM', "log_ID IN (SELECT post_id FROM {$mapTable} WHERE tag_id = {$tagId})");

第 2 处（约第 1249 行）：

// 原代码
$w[] = array('LIKE', 'log_Tag', '%{' . $tag->ID . '}%');

// 替换为
$mapTable = $zbp->db->dbpre . 'post_tag_map';
$tagId = (int)$tag->ID;
$w[] = array('CUSTOM', "log_ID IN (SELECT post_id FROM {$mapTable} WHERE tag_id = {$tagId})");

验证是否替换成功：

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}0

预期无输出，表示系统不再生成 LIKE 查询。

步骤 5：修正文章页相关文章模板

编辑 zb_users/theme/suiranx_air/template/post-single.php，找到相关文章区块，替换为以下代码：

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}1

步骤 6：作者页覆盖索引（可选保留）

虽然作者页流量较低未做强制缓存优化，但可预先建立覆盖索引供后续备用：

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}2

步骤 7：清理临时文件

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}3

四、效果验证

1. 验证映射表数据

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}4

2. 验证标签页慢查询已清除

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}5

3. 验证热评与首页正常

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}6

确认没有 ORDER BY log_CommNums DESC 的 Query_time > 1 记录。

五、升级维护说明

重要提示：Z-Blog 系统升级会覆盖 zb_system/function/c_system_route.php，导致标签页优化失效。

升级后需重新执行步骤 4 的替换操作。建议保留以下一键修复脚本 patch_core.php 在网站根目录：

function TagMapSync_GetTableName() {
    global $zbp;
    return $zbp->db->dbpre . "post_tag_map";
}

function TagMapSync_CreateTable() {
    global $zbp;
    $tablename = TagMapSync_GetTableName();
    if ($zbp->db->type == 'sqlite') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INTEGER NOT NULL DEFAULT 0,
            tag_id INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } elseif ($zbp->db->type == 'pgsql') {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        );";
    } else {
        $sql = "CREATE TABLE IF NOT EXISTS {$tablename} (
            post_id INT NOT NULL DEFAULT 0,
            tag_id INT NOT NULL DEFAULT 0,
            PRIMARY KEY (post_id, tag_id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;";
    }
    $zbp->db->Query($sql);
}

function GetPostListByTagID($tagID, $limit = 10) {
    global $zbp;
    $tagID = (int)$tagID;
    $limit = (int)$limit;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = $tagID
        ORDER BY p.log_PostTime DESC LIMIT $limit";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostListByTagID_Page($tagID, $page = 1, $perPage = 20) {
    global $zbp;
    $tagID = (int)$tagID; $page = max(1, (int)$page);
    $perPage = max(1, (int)$perPage);
    $offset = ($page - 1) * $perPage;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT p.* FROM {$zbp->table['Post']} p
        INNER JOIN {$tablename} m ON p.log_ID = m.post_id
        WHERE m.tag_id = {$tagID} AND p.log_Status = 0
        ORDER BY p.log_PostTime DESC LIMIT {$offset}, {$perPage}";
    $array = $zbp->db->Query($sql);
    return $zbp->GetPostTypeArray($array);
}

function GetPostCountByTagID($tagID) {
    global $zbp;
    $tagID = (int)$tagID;
    $tablename = TagMapSync_GetTableName();
    $sql = "SELECT COUNT(*) as cnt FROM {$tablename} WHERE tag_id = {$tagID}";
    $result = $zbp->db->Query($sql);
    return isset($result[0]['cnt']) ? (int)$result[0]['cnt'] : 0;
}

function TagMapSync_UpdateMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Query("DELETE FROM {$tablename} WHERE post_id = $post_id");
    if (preg_match_all('/\{(\d+)\}/', $article->Tag, $m)) {
        foreach ($m[1] as $tag_id) {
            $tag_id = (int)$tag_id;
            $zbp->db->Query("INSERT INTO {$tablename} (post_id, tag_id) VALUES ($post_id, $tag_id)");
        }
    }
}

function TagMapSync_DeleteMap(&$article) {
    global $zbp;
    $post_id = (int)$article->ID;
    $tablename = TagMapSync_GetTableName();
    TagMapSync_CreateTable();
    $zbp->db->Delete($tablename, array(array('=', 'post_id', $post_id)));
}7

升级后只需执行 php patch_core.php 即可恢复。

六、总结

通过建立 zbp_post_tag_map 映射表、批量导入 3 万篇历史数据、修改系统核心路由三管齐下，成功将 Z-Blog 标签页的查询方式从 LIKE '%{tag_id}%' 全表扫描改为索引子查询，响应时间从 1~3 秒降至毫秒级。同时保留了原有热评、首页等模块的索引支持，确保网站整体功能不受影响。

如果你的 Z-Blog 站点文章数量超过 1 万篇且标签页打开缓慢，强烈建议按本文方案进行优化。

Z-Blog优化标签页慢查询MySQL索引优化全站缓存分页深度控制post_tag_map

猜你喜欢

网友评论

热门标签
关注我们

扫一扫二维码关注我们的微信公众号

侧栏广告位
页面加载时长：0.196 秒 数据库查询：23 次 占用内存：2.96 MB 峰值内存： 3.41 MB