mysql 派生表、性能、替代方案

Question

我有以下表格，

1. link_books_genres, *表结构 -> book_id,genre_id*
2. 流派, *表结构 -> genre_id,genre_name*

给定一组 book_id，我想形成以下结果，

result_set structure -> genre_id, genre_name, count(book_id).

我写了这个查询，

SELECT one.genre_id,
       one.genre_name, 
       two.count 
FROM   genres as one,(SELECT genre_id,
                   count(book_id) as count 
                   FROM link_f2_books_lists GROUP BY genre_id) as two 
WHERE  one.genre_id = two.genre_id;

我不知道这是否是最好的解决方案，但我希望尽可能对其进行优化，或者如果它格式正确，则经过验证。

附言它是通过 ruby​​ on rails 完成的，因此任何面向 rails 的方法都可以。

Answer 1

您的查询未使用 SQL-92 JOIN 语法，而是使用较旧的隐式连接语法。是时候了(20 年了)，你应该开始使用它了。

别名使用 COUNT 这样的关键字也不是很好。您可以使用 cnt 或 book_count 代替:

SELECT one.genre_id,
       one.genre_name, 
       two.cnt 
FROM   
       genres AS one
     INNER JOIN
       ( SELECT genre_id,
                COUNT(book_id) AS cnt 
         FROM   link_f2_books_lists 
         GROUP BY genre_id
       ) AS two 
           ON one.genre_id = two.genre_id ;

---

MySQL 通常使用 COUNT(*) 会快一点，所以如果 book_id 不能是 NULL，改变 COUNT(book_id ) 到 COUNT(*) 将是一个小的性能改进。

---

当然，您可以在没有派生表的情况下重写 Join:

SELECT one.genre_id,
       one.genre_name, 
       COUNT(*) AS cnt 
FROM   
       genres AS one
     INNER JOIN
       link_f2_books_lists AS two 
           ON one.genre_id = two.genre_id 
GROUP BY  one.genre_id ;

在这两个版本中，您可以将 INNER JOIN 更改为 LEFT OUTER JOIN 以便显示没有任何书籍(0 计数)的流派。但是为了获得正确的结果，请务必使用 COUNT(two.book_id) 而不是 COUNT(*)。

以上版本(和你的)将不包括这些类型(这是使用 JOIN 语法的一个很好的理由，所需的更改非常简单。尝试使用你的 WHERE版本!)

---

LEFT JOIN 版本也可以这样写:

SELECT one.genre_id,
       one.genre_name, 
       ( SELECT COUNT(*) 
         FROM   link_f2_books_lists AS two 
         WHERE  one.genre_id = two.genre_id 
       ) AS cnt 
FROM   
       genres AS one ;

---

关于性能，没有什么比自己测试更好的了。这完全取决于您使用的 MySQL 版本(较新的版本将具有更好的优化器，可以通过更多选项进行选择以创建执行计划，并且可能会将不同版本识别为等效)，表的大小，您拥有的索引，数据的分布(有多少种不同的类型？平均每种类型有多少本书？等)、您的内存(和其他 MySQL)设置以及我现在可能忘记的许多其他因素。

一个建议是，在大多数情况下，(genre_id, book_id) 上的索引对于所有版本都是有用的。

作为一般建议，在多对多表上同时使用 (genre_id, book_id) 和 (book_id, genre_id) 索引通常是好的.