MySQL、SQL、表设计与索引：先把数据关系画清楚

1. 现实问题：文章列表慢，未必是 Java 慢

博客首页要显示文章标题、作者、发布时间和标签，查询从几毫秒变成几秒后，初学者常先给 Java 加缓存或线程。真正的第一步是问数据库在执行什么：表是否表达了真实关系，过滤和排序列是否有合适索引，查询是否把不需要的列和行都搬回应用。

数据库设计是后端调用链的起点。主键定义身份，外键表达关系，唯一约束拒绝重复，索引提供额外的查找路径；它们同时带来写入成本和空间成本。没有“所有列都建索引”的正确答案。

2. 最小可运行示例：表结构、查询与执行计划

CREATE TABLE article (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    author_id BIGINT NOT NULL,
    title VARCHAR(160) NOT NULL,
    status VARCHAR(20) NOT NULL,
    published_at DATETIME(6) NULL,
    created_at DATETIME(6) NOT NULL,
    UNIQUE KEY uk_article_title_author (author_id, title),
    KEY idx_article_status_published (status, published_at, id)
);

SELECT id, author_id, title, published_at
FROM article
WHERE status = 'PUBLISHED'
ORDER BY published_at DESC, id DESC
LIMIT 20;

EXPLAIN SELECT id, author_id, title, published_at
FROM article
WHERE status = 'PUBLISHED'
ORDER BY published_at DESC, id DESC
LIMIT 20;

联合索引的列顺序来自查询：先按等值条件过滤 status，再支持发布时间和 id 的稳定排序。EXPLAIN 要结合实际数据量看 key、rows、type 和 Extra，不能只看到“有索引”就宣布优化完成。生产优化前保存原始计划和代表性数据。

3. 调用链与对象变化

客户端发送 SQL 文本与参数，MySQL 解析语句、选择候选索引并生成执行计划。存储引擎按索引 B+Tree 定位叶子节点，再读取覆盖列或回表取完整数据，最终把行按协议编码返回。应用端收到的每一行只是结果快照，不等于数据库里的对象。

表设计阶段发生的是关系到物理结构的变化：author_id 是一个整数值，外键关系让它指向作者身份；联合索引复制部分列和主键形成新的排序结构。插入一行不仅写表，还要更新每个相关索引，所以读优化会增加写成本。

4. 为什么这样设计

规范化减少重复和更新异常，文章作者关系应通过 id 表达；展示字段可以在查询时 Join 或组装。反规范化有时换取读性能，但必须明确同步策略。VARCHAR 长度、时间精度、金额类型和状态表示都应该服务于业务边界，而不是照着样例随意选。

索引只对它能缩小扫描范围、支持排序或覆盖查询有帮助。联合索引遵循最左匹配的常见规则，但具体计划仍受统计信息、数据分布和查询写法影响。对 WHERE DATE(published_at) = ... 这类对列做函数的写法，要评估是否阻止索引范围扫描。

5. 项目落点：从查询用例反推 schema

博客系统先列出用例：按状态分页、按作者查文章、按标签过滤、按 slug 唯一访问。每个用例写 SQL 和返回列，再决定主键、唯一键和索引；迁移脚本纳入 Git，线上执行记录版本。Repository 不应该依赖“数据库里刚好有顺序”，必须写 ORDER BY。

练习：为 article_tag 和 tag 设计多对多表，写出按标签查询最新文章的 SQL，使用 EXPLAIN 比较没有联合索引、(tag_id, article_id) 和排序列索引的计划。记录读行数和返回行数，解释哪一个才是有效证据。

6. 易错排查

• 用 SELECT *：列扩展会放大网络和映射成本，按用例列出返回字段。
• 只看索引名不看 EXPLAIN：统计信息或函数条件可能让优化器选择全表扫描。
• 分页没有稳定排序：只按发布时间且同一时间多行时，翻页会重复或遗漏。
• 状态用数字魔法值：改为有约束的字符串或代码表，并在 Java 枚举与数据库映射间写清规则。

7. 一页复习

先从查询用例设计关系和约束，再从过滤、关联、排序反推索引，最后用 EXPLAIN 和实际数据验证。数据库返回的是行和列的结果，Java 的实体只是映射。索引既是读路径也是写成本，正确性优先于“看起来更快”。

可以把文章列表的输入输出写成一条可验证记录：输入是 status=PUBLISHED、limit=20、游标时间和 id；优化器拿到候选索引后输出一组行；应用只接收 id、author_id、title、published_at 四列。若 rows 估算远大于返回 20 行，要继续看过滤选择性和排序，而不是仅看查询时间一次是否下降。

表设计文件落在数据库迁移目录，查询文件或 Mapper 旁边记录它依赖的索引；article 的 slug 唯一约束属于正确性，status/published_at/id 联合索引属于访问路径。若先写 SQL 后补 schema，常会出现 Java 代码以为可以 null、数据库却拒绝，或者分页依赖未声明顺序。迁移、回填和索引创建要有版本号及回滚/兼容说明。

排错先在数据库客户端用固定参数复现，再看 EXPLAIN 的 type、key、rows 和 Extra；全表扫描可能来自缺索引、函数包列、隐式类型转换或统计信息过旧；索引没被选中也可能是数据分布让它不划算。分页重复还要查排序是否包含稳定 id，写入变慢则查新增索引和锁等待。每个结论都保存 SQL、样本量和计划。

练习是为标签多对多查询写两种 SQL：一种先过滤 tag_id 再 Join，另一种先查 article_id 再回表；分别建立候选索引并比较实际读行数。再把一条 SELECT * 改成 DTO 所需列，测网络字节和映射时间，说明优化发生在数据库、网络还是 Java 层。

设计文章表时，先把一行数据当作对象状态：插入前没有 id，插入后 id 由数据库生成；草稿状态没有 published_at，发布后必须同时得到 PUBLISHED 和发布时间；删除不是把行抹掉，而是让 deleted_at、查询条件和索引共同决定它是否可见。这样 Java 的 ArticleRecord、SQL 的一行和 API 的 ArticleView 都有明确的转换点。

联合索引的输入是查询的过滤、排序和返回列，输出是一个按索引键组织的查找路径。(status, published_at, id) 能从已发布范围开始，再按时间和 id 走稳定顺序，但它并不自动支持按 title 模糊搜索，也不保证所有查询都覆盖。索引越多，写入一行时要维护的结构越多，所以每个索引都要对应一个真实查询用例。

表设计还要处理重复和删除：slug 的唯一约束防止两个公开 URL 指向不同文章，article_tag 的联合主键防止同一标签关系重复，外键或应用校验防止孤儿关联。若采用软删除，唯一 slug 是否允许被删除记录占用必须先决定；数据库约束、回收策略和恢复命令需要一起写进迁移说明。

执行计划排查不只看 key 是否有值。type 表示访问方式，rows 是估算扫描量，filtered 说明过滤效果，Extra 可能提示临时表或额外排序。计划好看但线上慢，可能是统计信息、冷热数据、锁等待或网络结果过大；计划变差则保存表规模、参数分布和数据库版本，避免用一条空表实验下结论。

项目里建议为每个 Repository 方法保存 SQL 注释和返回 DTO，迁移文件按版本命名，索引变更先在副本上测写入锁。列表接口的 ORDER BY 必须包含稳定 id，分页游标同时携带时间和 id；否则新文章插入时，第二页可能重复第一条或跳过一条。数据库层做正确性，Service 层做公开状态和权限，二者不要互相代替。

错误诊断可按输入到结果倒查：结果缺作者查 Join 条件和外键；查询扫描全表查函数、类型转换和联合索引顺序；插入失败查唯一/非空约束与应用默认值；翻页错乱查时间精度和 tie-breaker。练习是先生成一万行代表性文章，再记录优化前后的 rows、耗时、写入成本和返回字节，写出一段“为什么采用/不采用该索引”的复盘。

进阶附录：覆盖索引、分页与分区

覆盖索引能让查询直接从索引拿到所需列，减少回表，但会增大索引。深分页用 OFFSET 可能扫描并丢弃大量行，可以用 (published_at, id) 作为游标做 keyset pagination。分区适合有明确生命周期和访问范围的大表，不是索引失效时的第一反应。