如何高效设计个人博客数据库以提升访问性能？

个人博客数据库设计指南（符合百度E-A-T算法）

核心数据表结构设计

1 用户表（users）

存储作者及管理员信息，体现专业性（E）与可信度（T）。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,  -- 用户名（用于登录）
    display_name VARCHAR(100) NOT NULL,    -- 前端显示名称
    password_hash VARCHAR(255) NOT NULL,   -- 加密后的密码
    role ENUM('admin', 'author') NOT NULL, -- 用户角色
    bio TEXT,                              -- 个人简介（展示专业背景）
    avatar_url VARCHAR(255),               -- 头像链接（建议使用HTTPS）
    email VARCHAR(100) UNIQUE NOT NULL,
    social_links JSON,                     -- 社交媒体链接（如GitHub/LinkedIn）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2 文章表（posts）载体，需优化SEO字段并体现权威性（A）。

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,                  -- 关联作者ID
    slug VARCHAR(255) UNIQUE NOT NULL,     -- 文章唯一标识（用于URL）
    content LONGTEXT NOT NULL,             -- 正文内容（支持富文本）
    excerpt TEXT,                          -- 用于SEO描述）
    meta_keywords VARCHAR(255),            -- 关键词（逗号分隔）
    meta_description VARCHAR(160),         -- 描述（控制155-160字符）
    is_published BOOLEAN DEFAULT FALSE,    -- 发布状态
    og_image VARCHAR(255),                 -- Open Graph分享图
    canonical_url VARCHAR(255),            -- 规范链接（防重复内容）
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3 分类与标签表（categories & tags）主题，增强内容相关性。

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description VARCHAR(255)
);
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
);
-- 文章与分类/标签的关联表
CREATE TABLE post_category (
    post_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (post_id, category_id)
);
CREATE TABLE post_tag (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id)
);

增强E-A-T的扩展表

1 参考文献表（references）

添加权威引用来源,提升内容可信度。

CREATE TABLE references (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,VARCHAR(255) NOT NULL,          -- 引用来源标题
    url VARCHAR(255) NOT NULL,            -- 来源链接（需权威站点）
    snippet TEXT,                         -- 引用摘要
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

2 用户互动表（interactions）

记录阅读时长等行为数据,间接反映内容质量。

CREATE TABLE interactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_ip VARCHAR(45),                  -- 支持IPv6
    engagement_time INT DEFAULT 0,        -- 阅读时长（秒）
    is_shared BOOLEAN DEFAULT FALSE,      -- 是否分享
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

SEO优化关键设计

1. URL规范化

   • 使用slug字段生成固定化URL（如/posts/{slug}）
   • canonical_url字段避免多版本内容重复

2. 结构化数据支持

   • 在文章表中预留json_ld字段，存储Schema.org标记
   • 示例：为作者生成Person schema标记
     更新机制**
   • 通过updated_at字段标识内容新鲜度，百度优先收录更新内容

安全与性能设计

• 防垃圾评论：评论表增加is_approved审核字段
• 敏感词过滤：建立blocked_keywords表实现自动过滤
• 索引优化：

  CREATE INDEX idx_posts_published ON posts(is_published, created_at);
  CREATE INDEX idx_posts_slug ON posts(slug);

符合E-A-T的字段示例

引用说明
本文参考百度搜索质量指南与Google Search Essentials,重点优化内容真实性与来源透明度。