博客数据库完整维护手册

作者: iTxGo | 发布于: 2025-12-15 12:18 | 更新于: 2025-12-17 15:35 | 分类: 未分类 | 浏览: 104 |

📖 使用说明

本手册包含完整的数据库维护流程，按照步骤执行即可。

⚠️ 执行前必须备份数据库
📝 每个步骤都有明确的 SQL 代码
✅ 每个步骤后都有验证方法

🔄 维护流程总览

1. 备份数据库（必须）
   ↓
2. 数据冗余分析（检查问题）
   ↓
3. 数据清理修复（解决问题）
   ↓
4. 创建索引（提升性能）
   ↓
5. 创建视图（简化查询）
   ↓
6. 数据库维护（优化存储）
   ↓
7. 验证效果（确认成功）

⚠️ 步骤 0：备份数据库（必须执行！）

每季度维护（30分钟）

-- 1. 完整的数据冗余分析（运行步骤1的全部27项）

-- 2. 根据分析结果执行清理（运行步骤2相应的清理语句）

-- 3. 检查数据增长趋势
SELECT 
    DATE(created_at) as date,
    COUNT(*) as articles
FROM articles
WHERE created_at >= datetime('now', '-90 days')
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- 4. 查看热门内容
SELECT * FROM v_hot_articles;
SELECT * FROM v_user_statistics ORDER BY article_count DESC LIMIT 10;
SELECT * FROM v_category_statistics ORDER BY article_count DESC;

-- 5. 数据库性能分析
EXPLAIN QUERY PLAN SELECT * FROM v_article_list LIMIT 10;
EXPLAIN QUERY PLAN SELECT * FROM comments WHERE article_id = 1;

-- 6. 完整维护流程
ANALYZE;
-- 压缩数据库（通过菜单）
PRAGMA integrity_check;

🚨 故障排查

问题1：执行 SQL 时出现 "cannot VACUUM from within a transaction"

原因： VACUUM 命令不能在事务中执行

解决方法：

不要在 SQL 编辑器中直接运行 VACUUM
使用菜单：工具(T) → 压缩数据库
或者关闭 DB Browser 重新打开后再执行

问题2：视图创建失败 "no such table"

原因： 视图依赖的表不存在或名称错误

解决方法：

-- 检查表是否存在
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

-- 删除所有视图重新创建
DROP VIEW IF EXISTS v_article_details;
DROP VIEW IF EXISTS v_article_list;
DROP VIEW IF EXISTS v_hot_articles;
DROP VIEW IF EXISTS v_comment_details;
DROP VIEW IF EXISTS v_user_statistics;
DROP VIEW IF EXISTS v_category_statistics;
DROP VIEW IF EXISTS v_tag_statistics;
DROP VIEW IF EXISTS v_site_statistics;

-- 然后重新执行步骤4的创建视图SQL

问题3：索引创建失败 "already exists"

原因： 索引已经存在

解决方法：

-- 使用 IF NOT EXISTS，重新运行索引创建SQL即可
-- 或者先删除再创建
DROP INDEX IF EXISTS idx_articles_created_at;
CREATE INDEX idx_articles_created_at ON articles(created_at DESC);

问题4：清理后数据丢失

原因： 清理脚本删除了不应该删除的数据

解决方法：

-- 从备份恢复
-- 1. 关闭 DB Browser
-- 2. 用备份文件替换当前的 blog.db
-- 3. 重新打开，重新执行维护流程

问题5：查询很慢，索引没有生效

原因： 需要更新统计信息

解决方法：

-- 更新统计信息
ANALYZE;

-- 检查索引是否存在
SELECT name FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%';

-- 查看执行计划
EXPLAIN QUERY PLAN
SELECT * FROM articles WHERE author_id = 1 ORDER BY created_at DESC;

💻 PHP 代码使用示例

示例1：首页文章列表

<?php
// 优化前：多次查询
$articles = $db->query("SELECT * FROM articles ORDER BY created_at DESC LIMIT 10")->fetchAll();
foreach ($articles as &$article) {
    // 每篇文章都查询一次作者
    $author = $db->query("SELECT username FROM users WHERE id = ?")->fetch([$article['author_id']]);
    $article['author_name'] = $author['username'];
    
    // 每篇文章都查询点赞数
    $likes = $db->query("SELECT COUNT(*) as count FROM likes WHERE article_id = ?")->fetch([$article['id']]);
    $article['like_count'] = $likes['count'];
}

// 优化后：使用视图，一次查询
$articles = $db->query("SELECT * FROM v_article_list LIMIT 10")->fetchAll();
// 直接使用 $article['author_name'], $article['like_count'], $article['comment_count']
?>

示例2：文章详情页

<?php
// 优化后：使用视图
$article_id = (int)$_GET['id'];
$article = $db->prepare("SELECT * FROM v_article_details WHERE id = ?");
$article->execute([$article_id]);
$article = $article->fetch();

// 文章包含所有需要的信息：
// - author_name（作者名）
// - category_name（分类名）
// - like_count（点赞数）
// - comment_count（评论数）
// - tags（标签，逗号分隔）

// 获取评论列表
$comments = $db->prepare("SELECT * FROM v_comment_details WHERE article_id = ? ORDER BY created_at DESC");
$comments->execute([$article_id]);
$comments = $comments->fetchAll();
?>

示例3：检查用户是否已点赞

<?php
// 优化后：索引加速查询
function hasUserLiked($db, $article_id, $user_id = null, $ip = null) {
    if ($user_id) {
        // 登录用户
        $stmt = $db->prepare("SELECT id FROM likes WHERE article_id = ? AND user_id = ?");
        $stmt->execute([$article_id, $user_id]);
    } else {
        // 游客
        $stmt = $db->prepare("SELECT id FROM likes WHERE article_id = ? AND ip_ Windows 备份方法

```batch
:: 创建备份文件夹
mkdir C:\backups

:: 复制数据库文件（修改路径为你的实际路径）
copy "C:\phpstudy\WWW\blog\blog.db" "C:\backups\blog_%date:~0,4%%date:~5,2%%date:~8,2%.db"

在 DB Browser 中备份

点击菜单：文件(E) → 导出 → 数据库到SQL文件
选择保存位置，命名为：blog_backup_20241107.sql

Linux/Mac 备份

# 创建备份
cp /path/to/blog.db /path/to/backups/blog_$(date +%Y%m%d).db

# 或使用 SQLite 命令
sqlite3 /path/to/blog.db ".backup /path/to/backups/blog_$(date +%Y%m%d).db"

✅ 验证备份： 确认备份文件存在且大小正常

📊 步骤 1：数据冗余分析（15-20分钟）

1.1 在 DB Browser 中操作

打开 DB Browser for SQLite
点击 打开数据库(D)，选择 blog.db
点击 执行SQL(X) 标签页
复制下面的 SQL 代码，粘贴到编辑器
点击 执行按钮（▶）

1.2 完整分析 SQL 代码

-- ========================================
-- 数据冗余分析（27个检查项）
-- 有结果 = 发现问题，无结果 = 正常
-- ========================================

-- 1. 重复用户名检测
SELECT '1. 重复用户名' as check_item, username, COUNT(*) as count
FROM users
GROUP BY LOWER(username)
HAVING COUNT(*) > 1;

-- 2. 重复邮箱检测
SELECT '2. 重复邮箱' as check_item, email, COUNT(*) as count
FROM users
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

-- 3. 重复分类名称检测
SELECT '3. 重复分类' as check_item, name, COUNT(*) as count
FROM categories
GROUP BY LOWER(name)
HAVING COUNT(*) > 1;

-- 4. 重复标签名称检测
SELECT '4. 重复标签' as check_item, name, COUNT(*) as count
FROM tags
GROUP BY LOWER(name)
HAVING COUNT(*) > 1;

-- 5. 重复配置键检测
SELECT '5. 重复配置' as check_item, key, COUNT(*) as count
FROM config
GROUP BY key
HAVING COUNT(*) > 1;

-- 6. 孤立文章检测（作者已删除）
SELECT '6. 孤立文章-作者' as check_item, a.id, a.title, a.author_id
FROM articles a
LEFT JOIN users u ON a.author_id = u.id
WHERE u.id IS NULL;

-- 7. 孤立文章检测（分类已删除）
SELECT '7. 孤立文章-分类' as check_item, a.id, a.title, a.category_id
FROM articles a
WHERE a.category_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = a.category_id);

-- 8. 孤立评论检测（文章已删除）
SELECT '8. 孤立评论-文章' as check_item, c.id, c.content, c.article_id
FROM comments c
LEFT JOIN articles a ON c.article_id = a.id
WHERE a.id IS NULL;

-- 9. 孤立评论检测（用户已删除）
SELECT '9. 孤立评论-用户' as check_item, c.id, c.user_id, c.author_name
FROM comments c
WHERE c.user_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = c.user_id);

-- 10. 孤立父评论检测
SELECT '10. 孤立父评论' as check_item, c.id, c.content, c.parent_id
FROM comments c
WHERE c.parent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM comments p WHERE p.id = c.parent_id);

-- 11. 孤立文章标签关联（文章）
SELECT '11. 孤立标签关联-文章' as check_item, at.article_id, at.tag_id
FROM article_tags at
LEFT JOIN articles a ON at.article_id = a.id
WHERE a.id IS NULL;

-- 12. 孤立文章标签关联（标签）
SELECT '12. 孤立标签关联-标签' as check_item, at.article_id, at.tag_id
FROM article_tags at
LEFT JOIN tags t ON at.tag_id = t.id
WHERE t.id IS NULL;

-- 13. 孤立附件检测
SELECT '13. 孤立附件' as check_item, id, filename, article_id
FROM attachments
WHERE article_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM articles a WHERE a.id = attachments.article_id);

-- 14. 孤立点赞记录（文章）
SELECT '14. 孤立点赞-文章' as check_item, l.id, l.article_id
FROM likes l
LEFT JOIN articles a ON l.article_id = a.id
WHERE a.id IS NULL;

-- 15. 孤立点赞记录（用户）
SELECT '15. 孤立点赞-用户' as check_item, l.id, l.user_id
FROM likes l
WHERE l.user_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = l.user_id);

-- 16. 点赞计数不一致检测
SELECT '16. 点赞计数不一致' as check_item,
    a.id, a.title,
    COALESCE(lc.count, 0) as cached_count,
    COUNT(l.id) as actual_count
FROM articles a
LEFT JOIN likes_count lc ON a.id = lc.article_id
LEFT JOIN likes l ON a.id = l.article_id
GROUP BY a.id, a.title, lc.count
HAVING COALESCE(lc.count, 0) != COUNT(l.id);

-- 17. 缺失点赞计数记录
SELECT '17. 缺失点赞计数' as check_item, a.id, a.title, COUNT(l.id) as actual_likes
FROM articles a
LEFT JOIN likes l ON a.id = l.article_id
LEFT JOIN likes_count lc ON a.id = lc.article_id
WHERE lc.article_id IS NULL
GROUP BY a.id, a.title
HAVING COUNT(l.id) > 0;

-- 18. 空内容文章检测
SELECT '18. 空内容文章' as check_item, id, title
FROM articles
WHERE content IS NULL OR TRIM(content) = '';

-- 19. 空评论检测
SELECT '19. 空评论' as check_item, id, article_id
FROM comments
WHERE content IS NULL OR TRIM(content) = '';

-- 20. 未使用的标签检测
SELECT '20. 未使用标签' as check_item, t.id, t.name
FROM tags t
LEFT JOIN article_tags at ON t.id = at.tag_id
WHERE at.tag_id IS NULL;

-- 21. 未使用的分类检测
SELECT '21. 未使用分类' as check_item, c.id, c.name
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
WHERE a.id IS NULL;

-- 22. 置顶但未设置置顶时间
SELECT '22. 置顶时间缺失' as check_item, id, title, is_pinned, pinned_at
FROM articles
WHERE is_pinned = 1 AND pinned_at IS NULL;

-- 23. 未置顶但有置顶时间
SELECT '23. 置顶时间多余' as check_item, id, title, is_pinned, pinned_at
FROM articles
WHERE is_pinned = 0 AND pinned_at IS NOT NULL;

-- 24. 更新时间早于创建时间
SELECT '24. 时间逻辑错误' as check_item, id, title, created_at, updated_at
FROM articles
WHERE updated_at < created_at;

-- 25. 重复的点赞记录
SELECT '25. 重复点赞' as check_item, article_id, user_id, ip_address, COUNT(*) as count
FROM likes
GROUP BY article_id, COALESCE(user_id, 'NULL'), COALESCE(ip_address, 'NULL')
HAVING COUNT(*) > 1;

-- 26. 综合统计报告
SELECT '26. 综合统计' as check_item, '用户总数' as metric, CAST(COUNT(*) AS TEXT) as count FROM users
UNION ALL SELECT '26. 综合统计', '文章总数', CAST(COUNT(*) AS TEXT) FROM articles
UNION ALL SELECT '26. 综合统计', '评论总数', CAST(COUNT(*) AS TEXT) FROM comments
UNION ALL SELECT '26. 综合统计', '分类总数', CAST(COUNT(*) AS TEXT) FROM categories
UNION ALL SELECT '26. 综合统计', '标签总数', CAST(COUNT(*) AS TEXT) FROM tags
UNION ALL SELECT '26. 综合统计', '点赞总数', CAST(COUNT(*) AS TEXT) FROM likes
UNION ALL SELECT '26. 综合统计', '附件总数', CAST(COUNT(*) AS TEXT) FROM attachments;

-- 27. 文章数据完整性检查
SELECT '27. 文章完整性' as check_item,
    id, title,
    CASE WHEN author_id IS NULL THEN '✗' ELSE '✓' END as has_author,
    CASE WHEN LENGTH(TRIM(content)) > 0 THEN '✓' ELSE '✗' END as has_content,
    view_count,
    (SELECT COUNT(*) FROM comments WHERE article_id = articles.id) as comments,
    (SELECT COUNT(*) FROM likes WHERE article_id = articles.id) as likes
FROM articles
ORDER BY id;

✅ 验证结果：

检查 1-25 项：有结果表示发现问题，记录下来
检查 26 项：查看数据总量
检查 27 项：查看文章完整性

🧹 步骤 2：数据清理修复（10-15分钟）

⚠️ 警告：此步骤会删除数据，请确认已备份！

2.1 执行清理 SQL

在新的 SQL 编辑窗口中执行：

-- ========================================
-- 数据清理修复脚本（28个操作）
-- ⚠️ 会删除冗余数据，执行前必须备份！
-- ========================================

-- 1. 删除重复用户名（保留ID最小的）
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY LOWER(username)
);

-- 2. 删除重复邮箱
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY LOWER(email)
);

-- 3. 删除重复分类（先更新文章引用）
UPDATE articles
SET category_id = (
    SELECT MIN(id) FROM categories
    WHERE LOWER(name) = LOWER((SELECT name FROM categories WHERE id = articles.category_id))
)
WHERE category_id IN (
    SELECT id FROM categories
    WHERE id NOT IN (SELECT MIN(id) FROM categories GROUP BY LOWER(name))
);

DELETE FROM categories
WHERE id NOT IN (SELECT MIN(id) FROM categories GROUP BY LOWER(name));

-- 4. 删除重复标签（先更新关联）
UPDATE article_tags
SET tag_id = (
    SELECT MIN(id) FROM tags
    WHERE LOWER(name) = LOWER((SELECT name FROM tags WHERE id = article_tags.tag_id))
)
WHERE tag_id IN (
    SELECT id FROM tags WHERE id NOT IN (SELECT MIN(id) FROM tags GROUP BY LOWER(name))
);

DELETE FROM article_tags
WHERE rowid NOT IN (SELECT MIN(rowid) FROM article_tags GROUP BY article_id, tag_id);

DELETE FROM tags
WHERE id NOT IN (SELECT MIN(id) FROM tags GROUP BY LOWER(name));

-- 5. 删除重复配置键
DELETE FROM config
WHERE rowid NOT IN (SELECT MIN(rowid) FROM config GROUP BY key);

-- 6. 删除孤立文章（作者已删除）
DELETE FROM articles
WHERE author_id NOT IN (SELECT id FROM users);

-- 7. 清除孤立分类引用
UPDATE articles
SET category_id = NULL
WHERE category_id IS NOT NULL
  AND category_id NOT IN (SELECT id FROM categories);

-- 8. 删除孤立评论
DELETE FROM comments
WHERE article_id NOT IN (SELECT id FROM articles);

-- 9. 清除孤立用户引用
UPDATE comments
SET user_id = NULL
WHERE user_id IS NOT NULL
  AND user_id NOT IN (SELECT id FROM users);

-- 10. 清除孤立父评论引用
UPDATE comments
SET parent_id = NULL, reply_to_name = NULL
WHERE parent_id IS NOT NULL
  AND parent_id NOT IN (SELECT id FROM comments);

-- 11. 删除孤立文章标签关联
DELETE FROM article_tags
WHERE article_id NOT IN (SELECT id FROM articles)
   OR tag_id NOT IN (SELECT id FROM tags);

-- 12. 清除孤立附件引用
UPDATE attachments
SET article_id = NULL
WHERE article_id IS NOT NULL
  AND article_id NOT IN (SELECT id FROM articles);

-- 13. 删除孤立点赞记录
DELETE FROM likes
WHERE article_id NOT IN (SELECT id FROM articles)
   OR (user_id IS NOT NULL AND user_id NOT IN (SELECT id FROM users));

-- 14. 删除孤立点赞计数
DELETE FROM likes_count
WHERE article_id NOT IN (SELECT id FROM articles);

-- 15. 删除空评论
DELETE FROM comments
WHERE content IS NULL OR TRIM(content) = '';

-- 16. 删除未使用的标签
DELETE FROM tags
WHERE id NOT IN (SELECT DISTINCT tag_id FROM article_tags);

-- 17. 修复置顶时间
UPDATE articles
SET pinned_at = CURRENT_TIMESTAMP
WHERE is_pinned = 1 AND pinned_at IS NULL;

UPDATE articles
SET pinned_at = NULL
WHERE is_pinned = 0 AND pinned_at IS NOT NULL;

-- 18. 修复更新时间
UPDATE articles
SET updated_at = created_at
WHERE updated_at < created_at;

-- 19. 删除重复点赞
DELETE FROM likes
WHERE id NOT IN (
    SELECT MIN(id) FROM likes
    GROUP BY article_id, COALESCE(user_id, 0), COALESCE(ip_address, '')
);

-- 20. 重建点赞计数（完全重建）
DELETE FROM likes_count;

INSERT INTO likes_count (article_id, count)
SELECT article_id, COUNT(*) FROM likes GROUP BY article_id;

INSERT OR IGNORE INTO likes_count (article_id, count)
SELECT id, 0 FROM articles
WHERE id NOT IN (SELECT article_id FROM likes_count);

-- 21. 修复浏览计数NULL
UPDATE articles
SET view_count = 0
WHERE view_count IS NULL;

-- 22. 规范化用户名和邮箱
UPDATE users
SET username = TRIM(username), email = TRIM(email);

-- 23. 规范化分类和标签
UPDATE categories SET name = TRIM(name);
UPDATE tags SET name = TRIM(name);

✅ 验证清理结果：

-- 验证外键完整性（应该无结果）
SELECT 'articles_author' as check, COUNT(*) as count
FROM articles a LEFT JOIN users u ON a.author_id = u.id WHERE u.id IS NULL
UNION ALL
SELECT 'comments_article', COUNT(*)
FROM comments c LEFT JOIN articles a ON c.article_id = a.id WHERE a.id IS NULL
UNION ALL
SELECT 'likes_article', COUNT(*)
FROM likes l LEFT JOIN articles a ON l.article_id = a.id WHERE a.id IS NULL;

-- 验证点赞计数准确性（应该无结果）
SELECT a.id, COALESCE(lc.count, 0) as cached, COUNT(l.id) as actual
FROM articles a
LEFT JOIN likes_count lc ON a.id = lc.article_id
LEFT JOIN likes l ON a.id = l.article_id
GROUP BY a.id, lc.count
HAVING COALESCE(lc.count, 0) != COUNT(l.id);

📈 步骤 3：创建索引（5分钟）

-- ========================================
-- 创建关键索引（15个）
-- ========================================

-- 文章索引
CREATE INDEX IF NOT EXISTS idx_articles_created_at ON articles(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_view_count ON articles(view_count DESC);
CREATE INDEX IF NOT EXISTS idx_articles_pinned ON articles(is_pinned, pinned_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_category ON articles(category_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_articles_author ON articles(author_id, created_at DESC);

-- 评论索引
CREATE INDEX IF NOT EXISTS idx_comments_article ON comments(article_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comments_user ON comments(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_id, created_at ASC);

-- 点赞索引
CREATE INDEX IF NOT EXISTS idx_likes_article ON likes(article_id);
CREATE INDEX IF NOT EXISTS idx_likes_user_article ON likes(user_id, article_id);
CREATE INDEX IF NOT EXISTS idx_likes_ip_article ON likes(ip_address, article_id);

-- 标签索引
CREATE INDEX IF NOT EXISTS idx_article_tags_article ON article_tags(article_id);
CREATE INDEX IF NOT EXISTS idx_article_tags_tag ON article_tags(tag_id);

-- 附件索引
CREATE INDEX IF NOT EXISTS idx_attachments_article ON attachments(article_id);
CREATE INDEX IF NOT EXISTS idx_attachments_upload_time ON attachments(upload_time DESC);

✅ 验证索引创建：

-- 查看已创建的索引
SELECT name, tbl_name 
FROM sqlite_master
WHERE type = 'index' AND name LIKE 'idx_%'
ORDER BY tbl_name, name;

👁️ 步骤 4：创建视图（5分钟）

-- ========================================
-- 创建实用视图（8个）
-- ========================================

-- 视图1: 文章详情
DROP VIEW IF EXISTS v_article_details;
CREATE VIEW v_article_details AS
SELECT 
    a.id, a.title, a.content, a.created_at, a.updated_at, a.view_count,
    a.is_pinned, a.comments_enabled,
    u.username as author_name, c.name as category_name,
    COALESCE(lc.count, 0) as like_count,
    (SELECT COUNT(*) FROM comments WHERE article_id = a.id) as comment_count,
    (SELECT GROUP_CONCAT(t.name, ', ') FROM article_tags at 
     JOIN tags t ON at.tag_id = t.id WHERE at.article_id = a.id) as tags
FROM articles a
LEFT JOIN users u ON a.author_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN likes_count lc ON a.id = lc.article_id;

-- 视图2: 文章列表
DROP VIEW IF EXISTS v_article_list;
CREATE VIEW v_article_list AS
SELECT 
    a.id, a.title, SUBSTR(a.content, 1, 200) as excerpt,
    a.created_at, a.view_count, a.is_pinned,
    u.username as author_name, c.name as category_name,
    COALESCE(lc.count, 0) as like_count,
    (SELECT COUNT(*) FROM comments WHERE article_id = a.id) as comment_count
FROM articles a
LEFT JOIN users u ON a.author_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN likes_count lc ON a.id = lc.article_id;

-- 视图3: 热门文章
DROP VIEW IF EXISTS v_hot_articles;
CREATE VIEW v_hot_articles AS
SELECT 
    a.id, a.title, a.view_count,
    COALESCE(lc.count, 0) as like_count,
    (SELECT COUNT(*) FROM comments WHERE article_id = a.id) as comment_count,
    (COALESCE(lc.count, 0) * 3 + 
     (SELECT COUNT(*) FROM comments WHERE article_id = a.id) * 2 + 
     a.view_count) as hot_score,
    a.created_at, u.username as author_name
FROM articles a
LEFT JOIN users u ON a.author_id = u.id
LEFT JOIN likes_count lc ON a.id = lc.article_id
WHERE a.created_at >= datetime('now', '-7 days')
ORDER BY hot_score DESC LIMIT 10;

-- 视图4: 评论详情
DROP VIEW IF EXISTS v_comment_details;
CREATE VIEW v_comment_details AS
SELECT 
    c.id, c.article_id, c.content, c.created_at,
    COALESCE(u.username, c.author_name) as author_name,
    c.parent_id, c.reply_to_name,
    a.title as article_title
FROM comments c
LEFT JOIN users u ON c.user_id = u.id
LEFT JOIN articles a ON c.article_id = a.id;

-- 视图5: 用户统计
DROP VIEW IF EXISTS v_user_statistics;
CREATE VIEW v_user_statistics AS
SELECT 
    u.id, u.username, u.email, u.gender, u.bio, u.is_admin,
    u.created_at as join_date,
    (SELECT COUNT(*) FROM articles WHERE author_id = u.id) as article_count,
    (SELECT COUNT(*) FROM comments WHERE user_id = u.id) as comment_count,
    (SELECT COUNT(*) FROM likes WHERE user_id = u.id) as like_given_count,
    (SELECT SUM(view_count) FROM articles WHERE author_id = u.id) as total_views
FROM users u;

-- 视图6: 分类统计
DROP VIEW IF EXISTS v_category_statistics;
CREATE VIEW v_category_statistics AS
SELECT 
    c.id, c.name,
    COUNT(a.id) as article_count,
    SUM(a.view_count) as total_views,
    MAX(a.created_at) as last_article_date
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
GROUP BY c.id, c.name;

-- 视图7: 标签统计
DROP VIEW IF EXISTS v_tag_statistics;
CREATE VIEW v_tag_statistics AS
SELECT 
    t.id, t.name,
    COUNT(at.article_id) as article_count
FROM tags t
LEFT JOIN article_tags at ON t.id = at.tag_id
GROUP BY t.id, t.name;

-- 视图8: 站点统计
DROP VIEW IF EXISTS v_site_statistics;
CREATE VIEW v_site_statistics AS
SELECT 
    (SELECT COUNT(*) FROM articles) as total_articles,
    (SELECT COUNT(*) FROM users) as total_users,
    (SELECT COUNT(*) FROM comments) as total_comments,
    (SELECT COUNT(*) FROM categories) as total_categories,
    (SELECT COUNT(*) FROM tags) as total_tags,
    (SELECT SUM(view_count) FROM articles) as total_views,
    (SELECT COUNT(*) FROM likes) as total_likes,
    (SELECT COUNT(*) FROM articles WHERE DATE(created_at) = DATE('now')) as today_articles,
    (SELECT COUNT(*) FROM comments WHERE DATE(created_at) = DATE('now')) as today_comments;

✅ 验证视图创建：

-- 查看已创建的视图
SELECT name FROM sqlite_master WHERE type='view' AND name LIKE 'v_%' ORDER BY name;

-- 测试视图
SELECT * FROM v_site_statistics;
SELECT * FROM v_article_list LIMIT 5;

⚙️ 步骤 5：数据库维护（5分钟）

5.1 更新统计信息

-- 更新统计信息（让索引发挥作用）
ANALYZE;

5.2 完整性检查

-- 检查数据库完整性（期望返回 ok）
PRAGMA integrity_check;

-- 验证外键约束（期望无结果）
PRAGMA foreign_key_check;

5.3 压缩数据库

⚠️ VACUUM 无法在 SQL 编辑器中执行，必须通过菜单操作：

点击菜单：工具(T) → 压缩数据库
等待完成（10-30秒）
完成后会显示 "压缩完成"

5.4 保存更改

点击菜单：文件(E) → 写入更改(W)
或按快捷键：Ctrl + S

✅ 步骤 6：验证效果（5分钟）

-- ========================================
-- 优化效果验证
-- ========================================

-- 1. 数据库信息
SELECT 
    'Database Size (MB)' as metric,
    ROUND(page_count * page_size / 1024.0 / 1024.0, 2) as value
FROM pragma_page_count(), pragma_page_size()
UNION ALL
SELECT 'Total Tables', CAST(COUNT(*) AS TEXT)
FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT 'Total Indexes', CAST(COUNT(*) AS TEXT)
FROM sqlite_master WHERE type='index' AND name LIKE 'idx_%'
UNION ALL
SELECT 'Total Views', CAST(COUNT(*) AS TEXT)
FROM sqlite_master WHERE type='view' AND name LIKE 'v_%';

-- 2. 数据统计
SELECT * FROM v_site_statistics;

-- 3. 索引效果测试（查看执行计划）
EXPLAIN QUERY PLAN
SELECT * FROM articles WHERE author_id = 1 ORDER BY created_at DESC LIMIT 10;

-- 期望看到：SEARCH articles USING INDEX idx_articles_author

-- 4. 视图功能测试
SELECT '最新文章' as test, * FROM v_article_list LIMIT 3
UNION ALL
SELECT '热门文章', * FROM v_hot_articles LIMIT 3;

-- 5. 性能对比（记录执行时间）
-- 在 DB Browser 底部会显示 "耗时 XX ms"
SELECT COUNT(*) FROM articles;
SELECT * FROM v_article_details LIMIT 10;
SELECT * FROM comments WHERE article_id = 1;

✅ 验证清单：

数据库大小已减小
索引已创建（15个）
视图已创建（8个）
执行计划显示使用索引
视图查询正常工作

📅 定期维护计划

每周维护（5分钟）

-- 1. 更新统计信息
ANALYZE;

-- 2. 清理旧的游客点赞（90天前）
DELETE FROM likes 
WHERE user_id IS NULL 
  AND created_at < datetime('now', '-90 days');

-- 3. 快速检查
SELECT * FROM v_site_statistics;

每月维护（15分钟）

-- 1. 数据冗余检查（运行步骤1的前10项检查）
-- 如发现问题，运行步骤2的相应清理语句

-- 2. 更新统计信息
ANALYZE;

-- 3. 压缩数据库
-- 通过菜单：工具(T) → 压缩数据库

-- 4. 完整性检查
PRAGMA integrity_check;
PRAGMA foreign_key_check;

-- 5. 备份数据库
-- 复制 blog.db 到备份文件夹

标签：

iTxGo🍃 - xBlog™️

文章内容📄

博客数据库完整维护手册

📖 使用说明

🔄 维护流程总览

⚠️ 步骤 0：备份数据库（必须执行！）

每季度维护（30分钟）

🚨 故障排查

问题1：执行 SQL 时出现 "cannot VACUUM from within a transaction"

问题2：视图创建失败 "no such table"

问题3：索引创建失败 "already exists"

问题4：清理后数据丢失

问题5：查询很慢，索引没有生效

💻 PHP 代码使用示例

示例1：首页文章列表

示例2：文章详情页

示例3：检查用户是否已点赞

在 DB Browser 中备份

Linux/Mac 备份

📊 步骤 1：数据冗余分析（15-20分钟）

1.1 在 DB Browser 中操作

1.2 完整分析 SQL 代码

🧹 步骤 2：数据清理修复（10-15分钟）

2.1 执行清理 SQL

📈 步骤 3：创建索引（5分钟）

👁️ 步骤 4：创建视图（5分钟）

⚙️ 步骤 5：数据库维护（5分钟）

5.1 更新统计信息

5.2 完整性检查

5.3 压缩数据库

5.4 保存更改

✅ 步骤 6：验证效果（5分钟）

📅 定期维护计划

每周维护（5分钟）

每月维护（15分钟）

用户登录

用户注册

文章内容📄

博客数据库完整维护手册

📖 使用说明

🔄 维护流程总览

⚠️ 步骤 0：备份数据库（必须执行！）

每季度维护（30分钟）

🚨 故障排查

问题1：执行 SQL 时出现 "cannot VACUUM from within a transaction"

问题2：视图创建失败 "no such table"

问题3：索引创建失败 "already exists"

问题4：清理后数据丢失

问题5：查询很慢，索引没有生效

💻 PHP 代码使用示例

示例1：首页文章列表

示例2：文章详情页

示例3：检查用户是否已点赞

在 DB Browser 中备份

Linux/Mac 备份

📊 步骤 1：数据冗余分析（15-20分钟）

1.1 在 DB Browser 中操作

1.2 完整分析 SQL 代码

🧹 步骤 2：数据清理修复（10-15分钟）

2.1 执行清理 SQL

📈 步骤 3：创建索引（5分钟）

👁️ 步骤 4：创建视图（5分钟）

⚙️ 步骤 5：数据库维护（5分钟）

5.1 更新统计信息

5.2 完整性检查

5.3 压缩数据库

5.4 保存更改

✅ 步骤 6：验证效果（5分钟）

📅 定期维护计划

每周维护（5分钟）

每月维护（15分钟）