当两百万条评论成为网站性能杀手
凌晨三点，我盯着监控面板上飙升的MySQL CPU占用率，手指在键盘上悬停，这个运行了四年的杰奇CMS小说站，最近两个月访问速度从800ms恶化到4.7秒，用户投诉像雪片般涌入评论区，罪魁祸首正是日益膨胀的评论表——jieqi_comment表已经突破200万行，单条查询超过2.3秒，更致命的是，上周一次服务器迁移后，有327条用户评论凭空消失,备份文件里的SQL导出格式与新版不兼容。
评论数据保留：从表结构改造开始
1 万无一失的备份策略
-- 使用mysqldump导出时强制兼容模式，避免版本差异导致的数据丢失 mysqldump -u root -p --compatible=ansi --skip-extended-insert --complete-insert \ --set-gtid-purged=OFF jieqi_db jieqi_comment > comment_backup_$(date +%Y%m%d).sql
注意：--set-gtid-purged=OFF防止主从复制环境下的GTID冲突，--skip-extended-insert确保每条INSERT独立,便于后续分表时逐行处理。
杰奇CMS评论数据迁移与保留，从数据库分表到缓存优化的完整实践
2 垂直分表：按时间维度切分评论核心字段
原表结构存在严重冗余：articlebody字段存储全文HTML，与评论业务无关，我们只保留评论业务必需的字段,将历史数据迁移到归档表：
-- 创建新评论表（核心数据） CREATE TABLE `jieqi_comment_new` ( `commentid` int(10) unsigned NOT NULL AUTO_INCREMENT, `articleid` int(10) unsigned NOT NULL DEFAULT '0', `userid` int(10) unsigned NOT NULL DEFAULT '0', `posttime` int(10) unsigned NOT NULL DEFAULT '0', `content` text NOT NULL, `goodnum` smallint(5) unsigned NOT NULL DEFAULT '0', `badnum` smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`commentid`), KEY `idx_articleid_time` (`articleid`,`posttime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 创建历史归档表（仅保留近3个月活跃数据） CREATE TABLE `jieqi_comment_archive` LIKE `jieqi_comment_new`; -- 迁移90天前的数据（根据业务需求调整时间窗口） INSERT INTO `jieqi_comment_archive` SELECT `commentid`,`articleid`,`userid`,`posttime`,`content`,`goodnum`,`badnum` FROM `jieqi_comment` WHERE `posttime` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY)); -- 删除原始表（确认数据完整后操作） RENAME TABLE `jieqi_comment` TO `jieqi_comment_bak`; RENAME TABLE `jieqi_comment_new` TO `jieqi_comment`;
3 通过视图透明访问历史数据
修改class/jieqi_comment.php中的模型查询方法：
// 在评论列表查询函数中添加联合查询逻辑
public function getCommentList($articleid, $page=1, $pagesize=20) {
$offset = ($page-1) * $pagesize;
$sql = "SELECT * FROM (
SELECT * FROM `jieqi_comment` WHERE `articleid` = {$articleid}
UNION ALL
SELECT * FROM `jieqi_comment_archive` WHERE `articleid` = {$articleid}
) AS t ORDER BY `posttime` DESC LIMIT {$offset}, {$pagesize}";
return $this->query($sql);
}
问题：直接UNION ALL会导致全表扫描，解决方案：在jieqi_comment_archive上建立与实时表相同的联合索引idx_articleid_time，并确保posttime字段使用UNIX时间戳便于范围查询。
访问速度优化：从数据库层到缓存层
1 章节表分表的正确姿势
当jieqi_article_chapter表超过500万行时,推荐按文章ID哈希分表：
// 在includes/jieqi_inc.php中定义分表函数
function getChapterTableByArticleId($articleid) {
$table_count = 10; // 分10张表
$table_index = $articleid % $table_count;
return "jieqi_article_chapter_{$table_index}";
}
// 修改章节写入类
public function insertChapter($data) {
$table = getChapterTableByArticleId($data['articleid']);
$sql = "INSERT INTO `{$table}` ...";
$this->db->query($sql);
}
注意：查询时需遍历所有分表？不，只需通过文章ID定位到指定表，因为分表键与查询条件一致，对于全局搜索（如按章节标题搜索）,需要建立ES或Sphinx搜索引擎。
2 阅读页卡顿：彻底分离静动态数据
阅读页80%的延迟来自文章内容表jieqi_article_body的BLOB字段读取,将文章内容按章节缓存到Redis：
// 在阅读控制器中添加缓存层
function getChapterContent($chapterid) {
$cache_key = "chapter_content_{$chapterid}";
$content = Redis::get($cache_key);
if (!$content) {
$sql = "SELECT `content` FROM `jieqi_article_body` WHERE `chapterid` = {$chapterid}";
$row = $this->db->getRow($sql);
$content = gzcompress($row['content'], 6); // 压缩后存储
Redis::setex($cache_key, 86400, $content); // 缓存24小时
}
return gzuncompress($content);
}
关键点：使用gzcompress将平均2KB的章节内容压缩至500字节，Redis内存占用降低75%，同时设置24小时过期,新章节发布时通过钩子清除对应缓存。
3 模板标签调用：从SQL层面优化
杰奇CMS默认的{tag:comment_count articleid="123"}会触发COUNT查询,改为使用Redis计数器：
// 在modules/article/article.php的init()方法中
function getCommentCount($articleid) {
$key = "comment_count_{$articleid}";
$count = Redis::get($key);
if ($count === false) {
$sql = "SELECT COUNT(*) FROM `jieqi_comment` WHERE `articleid` = {$articleid}
UNION SELECT COUNT(*) FROM `jieqi_comment_archive` WHERE `articleid` = {$articleid}";
$result = $this->db->getAll($sql);
$count = $result[0]['COUNT(*)'] + $result[1]['COUNT(*)'];
Redis::setex($key, 3600, $count);
}
return $count;
}
在模板中直接调用<?php echo getCommentCount(123); ?>,替换原有标签。
移动端适配与缓存配置
1 响应式改造的三大手术
- 判断设备：在
header_pc.html头部添加：<script> if(/Android|iPhone|iPad/i.test(navigator.userAgent) && window.innerWidth < 768){ location.href = '/m/'; } </script> - 图片流式加载：修改
themes/default/css/reader.css，添加：.reader-content img{ max-width: 100%; height: auto; } - 触摸优化：在
includes/function.php中注册触摸事件：document.addEventListener('touchstart', function(e) { var touchX = e.touches[0].clientX; if(touchX < 50) { /* 左侧滑动返回上一页 */ } });
2 缓存配置：别再让数据库裸奔
修改config/cache.php,启用二级缓存策略：
define('CACHE_LEVEL', 2);
define('CACHE_MC_HOST', '127.0.0.1');
define('CACHE_MC_PORT', 11211);
define('CACHE_MC_PREFIX', 'jieqi_');
// 首页缓存改为60秒，避免频繁清除
$jieqiCache['index']['life'] = 60;
// 章节列表缓存改到300秒
$jieqiCache['chapter_list']['life'] = 300;
重要：启用Memcached后，必须修改includes/db/mysql.php中的查询方法，对SELECT结果进行缓存：
public function getRow($sql, $cache_time = 0) {
if($cache_time > 0){
$cache_key = md5($sql);
$result = Memcache::get($cache_key);
if($result === false){
$result = parent::getRow($sql);
Memcache::set($cache_key, $result, 0, $cache_time);
}
return $result;
}
return parent::getRow($sql);
}
数据库定期维护：让MySQL不再便秘
1 自动碎片整理脚本
在服务器crontab中添加每周任务：
0 3 * * 1 /usr/bin/mysqlcheck -u root -p'password' --optimize jieqi_db
避坑：OPTIMIZE表时会造成锁表，建议在凌晨低峰期执行，且只对InnoDB表有效，对于MyISAM表改用REPAIR。
2 历史数据自动归档计划
创建存储过程,每月自动清理180天前的评论：
DELIMITER //
CREATE PROCEDURE archive_old_comments()
BEGIN
DECLARE cutoff INT DEFAULT UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY));
INSERT INTO `jieqi_comment_archive`
SELECT * FROM `jieqi_comment` WHERE `posttime` < cutoff;
DELETE FROM `jieqi_comment` WHERE `posttime` < cutoff;
-- 重建索引
ALTER TABLE `jieqi_comment` ENGINE=InnoDB;
END//
DELIMITER ;
调用方式：CALL archive_old_comments();
3 查询慢日志定位
永久开启慢查询记录,为后续优化提供依据：
[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2
通过mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | head -20快速找到最耗时的查询。
处理完最后一行代码已是清晨六点，监控面板上的页面加载时间曲线终于回落到0.4秒，那327条消失的评论通过归档表的binlog恢复找到了，用户数据零丢失，这次重构的关键教训是：永远不要在高峰期动主表结构，修改前务必在staging环境用生产数据模拟迁移，看着两百万评论平稳运行在新架构上，我终于可以安心休息——得先确认crontab里的自动维护脚本明天能否正确执行。
发表评论