如何优化指定MySQL关联查询？需为数据表添加哪些索引？

原查询语句 #

SELECT b.*, ubh.bookId, ubh.position, ubh.completedDate, ubh.userId  
FROM user_book_history AS ubh 
INNER JOIN books AS b ON ubh.bookId = b.id 
WHERE ubh.userId = ? ORDER by ubh.createDate desc

现有表结构 #

CREATE TABLE "user_book_history" (
  "id" int NOT NULL AUTO_INCREMENT,
  "bookId" int DEFAULT NULL,
  "userId" int DEFAULT NULL,
  "position" int DEFAULT '0',
  "createDate" bigint DEFAULT NULL,
  "completedDate" bigint DEFAULT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "books" (
  "id" int NOT NULL AUTO_INCREMENT,
  "amazonBuyUrl" text,
  "audioUrl" varchar(255) DEFAULT NULL,
  "author" varchar(255) DEFAULT NULL,
  "authorDesc" text,
  "body" longtext,
  "categories" varchar(255) DEFAULT NULL,
  "description" text,
  "image" text,
  "publishDate" bigint DEFAULT NULL,
  "subtitle" varchar(255) DEFAULT NULL,
  "title" varchar(255) DEFAULT NULL,
  "uploadDate" bigint DEFAULT NULL,
  "video" varchar(255) DEFAULT NULL,
  "mediaUrl" varchar(255) DEFAULT NULL,
  "subid" varchar(255) DEFAULT NULL,
  "tags" varchar(255) DEFAULT NULL,
  "free" tinyint DEFAULT '0',
  "infographicUrl" varchar(255) DEFAULT NULL,
  "vector" varchar(255) DEFAULT NULL,
  "comingSoon" tinyint DEFAULT '0',
  "isPopular" tinyint DEFAULT NULL,
  PRIMARY KEY ("id")
);

核心优化：添加针对性索引 #

1. 给user_book_history创建复合覆盖索引 #

执行以下语句创建索引：

CREATE INDEX idx_ubh_userid_createdate ON user_book_history (userId, createDate, bookId, position, completedDate);

索引设计逻辑：

• 以userId作为前缀：快速过滤出指定用户的所有历史记录，大幅减少扫描行数
• 紧接着createDate：利用索引的有序性直接完成ORDER BY ubh.createDate DESC的排序，避免MySQL执行Using filesort（磁盘排序），这是提升排序性能的关键
• 后续的bookId, position, completedDate：属于查询中需要读取或关联的字段，加入索引后形成覆盖索引，MySQL无需回表查询原数据，直接从索引中获取所有需要的内容，大幅提升查询效率

2. books表无需额外索引 #

books表的主键是id，查询中通过ubh.bookId = b.id关联时，主键索引本身就能快速定位到对应的书籍数据，因此不需要新增索引。

其他优化建议 #

• 避免SELECT b.*：如果业务上不需要书籍表的所有字段，明确写出需要的字段（比如b.title, b.author），减少数据传输量和内存占用，尤其是body这类大文本字段，不必要的读取会严重拖慢查询速度
• 修正字段非空约束：user_book_history中的userId和bookId是关联和过滤的核心字段，建议设置为NOT NULL，避免空值干扰索引效率，同时符合业务逻辑（用户历史记录必须关联用户和书籍）
• 数据类型检查：确保user_book_history.bookId和books.id的数据类型完全一致（当前都是int，这点没问题），类型不匹配会导致索引失效

内容的提问来源于stack exchange，提问作者Chris Hansen