mysql - 给定用例的最佳 MySQL 表模式

Question

我有两个表 - books 和 images。 books 表有很多列 - 包括 id(主键)、name(不唯一)、releasedate 等。images 表有两列 - id(这不是唯一的，即一个书 id 可能有多个与之关联的图像，我们需要所有这些图像.该列有一个非唯一索引)和poster(这是唯一的主键，所有图像都在同一个桶中，因此不能重名)。我的要求是给定一个书名，找到与之相关的所有图像(连同发行年份和每张图像的 bucketname，bucketname 只是一个数字例)。

我正在运行这个查询:

select books.id,poster,bucketname,year(releasedate) from books 
inner join images where images.bookId = books.id and books.name = "<name>";

示例结果集可能如下所示:

如您所见，有两个匹配结果 - 一个具有 id 2 和 year 1989，有 5 张图像，另一个具有 id 261009，年 2013 和一张图片。

问题是，查询非常慢。在零负载下，MySQL 控制台本身大约需要 0.14 秒(在生产中可能有多个并发请求，它们可能会排队，导致进一步延迟)，这对于自动完成来说是 Not Acceptable 。谁能告诉我如何通过向表中添加正确的索引/键来优化查询？如果 MySQL 无法做到这一点，关于正确的 Redis 模式的建议也会很有用。

编辑:大约没有。 images 中的行数 - 480k，books 中的行数 - 285k。将来，自动完成将显示书籍作者和书名的结果，因此需要扩展查询以考虑到一个单独的表 authors，其中每个作者都有一个 id 和 name，就像一本书。

Answer 1

为了获得最佳性能，您需要使用合适的覆盖索引。例如:

... on `books` (`name`,`id`,`releasedate`)
... on `images` (`bookid`,`poster`,`bucketname`)

我们希望 name 作为索引中的前导列，因为 WHERE 子句中的相等谓词。我们希望 id 和 releaseate 也包含在索引中，使其成为“覆盖索引”，这样查询就可以从索引中满足，而不需要访问底层表的页面来检索值。

由于 ON 子句中的引用，我们希望 bookid 作为前导列。同样，在索引中提供 poster 和 bucketname 使其成为“覆盖”索引。使用 EXPLAIN 查看查询执行计划。

另请注意，如果在 images 中未找到匹配行，则内部联接操作不会从 books 返回行。如果我们想要从 books 中返回一行，即使没有图像可用，我们可以使用外部连接。

我会这样写查询:

SELECT b.id
     , i.poster
     , i.bucketname
     , YEAR(b.releasedate)
  FROM books b
  LEFT
  JOIN images i
    ON i.bookid = b.id
 WHERE b.name = ?