主页 / dba / 问题 / 15800
Accepted
codecool
Asked: 2012-03-30 06:34:36 +0800 CST

当表自连接 5-6 次时，Group_by 和有 vs 连接

  • 772

现在这个问题来找我的情况。架构是

    Table User_Read_Book
       user_id | book_id

现在我想获得读过某些书籍的用户。假设给我读过书 1 和 2 的用户。要测试的书数最多可以达到 10。

我写的第一个查询：

Select user_id from User_Read_Book Where book_id In (1,2) Group by user_id Having count(book_id) = 2

第二个查询：

Select  user_id from User_Read_Book as U join User_Read_Book as U1 On
 U.user_id = U1.user_id And U1.book_id = 1 where U.book_id = 2

正如在这个答案https://stackoverflow.com/a/621891中所说的那样，在group by的情况下更喜欢加入并且我做了第二个查询。

但是我的问题是当匹配的数字很大时哪个更好。假设你必须找到读过 7 本书的用户

   Having Count(book_id) = 7
   or
   6 joins to the same table.

我知道在对大型实时数据进行测试时，最好回答这个问题。专家对此有何看法？

database-design performance

2 个回答

  1. Best Answer

    有 7 本书，我的猜测是 7 个连接比GROUP BY / HAVING.

    但这取决于 DBMS、版本、优化器的设置、数据库设置、您拥有的 RAM、硬盘的性能、索引碎片、服务器的整体压力以及可能的其他几个参数。更重要的是，即使之前的所有设置都仍然设置，这取决于您的数据（及其分布）和查询的具体参数。例如，如果这 7 本书是哈利波特的 7 本书，并且您的所有用户都是哈利波特的粉丝，那么GROUP BY/HAVING可能会更快。

    另外，当你可以测试时，你不应该相信其他人，不管他们可能（期待）是什么专家。为什么不使用服务器中的数据和设置来测试两种方式的性能，使用可变数量的书籍（和标题）？

    还要检查这个问题（使用类似的查询），其中显示了其他几种（超过 10 种）方式（并在 PostgrSQL 中进行了基准测试）：How to filter SQL results in a has-many-through relationship

    更新

    “猜测”的解释 7 Joins 通常比GROUP BY / HAVING：

    想象一下，您有 100 万用户和大约 100 万本书。现在，平均而言，一个用户已经阅读了 100 本书（在您的数据库中，完全是虚构的数据和分布）。因此，该表有大约 100M 行。

    现在，GROUP BY查询将具有类似WHERE book_id IN (1,2,3,4,5,6,7). 让我们假设它book_id=1是最受欢迎的（圣经）并且有大约 10 万读者，而其他 6 个不那么受欢迎，每个有 100 到 1000 名读者。这会将要分组的行限制在 100K 到 106K 之间。这（大致）转换为 SQL 引擎从正确的索引读取 106K 数据，然后执行GROUP BY user_id. 所以，（它可能会选择使用(user_id, book_id)索引），它会对 - 进行大约 100K 计算，COUNT(book_id)并拒绝任何不是7.

    在 7JOIN查询中，它有更多的选项。优化器可以选择使用另一个索引，即(book_id, user_id)一个。想象一下“取出”这个大索引的 7 个较小部分，(1, user_id)部分（记住：其中有 100K 数据（user_ids）），(2, user_id)部分（这里少于 1000 个数据），...，直到(7, user_id)部分（少于 1000数据也在这里）。所以现在，它必须以某种方式组合这 7 个索引部分（这只是 7 个用户 ID 列表）并找到所有 7 个列表中的用户 ID。有一些聪明的算法可以做到这一点，没有必须对 7 个列表进行完整的阅读（完整扫描）。请注意，即使是首先组合 6 个较小列表的愚蠢算法，最终也可能只有少数用户 ID（假设只有 1 个）。要查找这 1 个 user_id 是否在大（第一个）列表中，只需要二进制搜索（记住它不是真正的列表，它是一个索引，这就是索引的好处，您可以在其中快速搜索）。因此，即使只有 100 个 user_id，在 100K 大列表/索引中进行 100 次搜索也只需要不到 100*17 的操作 ( log(100K) ~= 17)。这是 1700 次操作，远少于GROUP BY100K 次操作。不需要COUNT(*)。

    因此，使用连接，如果大多数书籍不是很受欢迎（或者只有一本书，我们很幸运），查询将非常有效，因为它必须查看极少数地方的索引。
    （另一个想法是，使用 Group By 方法，查询已经计算了 - 在拒绝它们之前 - 有多少书阅读了所有那些阅读过 1 或 2 或 ... 或 6 本书的用户。但我们不在乎他们是否阅读 1 或 6。我们只需要知道他们是否已阅读所有 7 ！）

    如果所选的 7 本书都非常受欢迎，情况就不同了。现在，7 个索引部分都很大，将它们组合起来可能比使用GROUP BY在一个索引上只使用一次传递的方法效率低。
    （另一个想法说 Group By 现在是有效的，因为几乎所有 Count 计算都将是 a 7，因此浪费了非常少的计算）

    • 4

  2. 表现

    这在很大程度上取决于实际数据（是否有很多人每人读过几本书，或者有几个人读过很多书）、偏差（是否有一些阅读能力强的读者？）以及您查询的书籍 - 就像提到的 ypercube圣经。

    这是在优化器真正开始并决定完全重写您的查询之前，因为它发现某些东西可能会更快......

    原则上，多个连接很可能会在表上进行多项选择，每次为每本书获取一组用户，然后在这些集合之间进行相交以查找所有集合中存在的用户。或者它会首先为一本书选择所有用户，当结果列表足够小并且存在正确的索引时，查询将使用索引从第一个列表中检查每个用户是否已阅读过的所有书籍。

    Group-By/Having 子句很可能会导致对包含其中一本书的所有行进行大量索引访问，然后对它们进行分组和计数。或者对于大量书籍，更有可能导致全表扫描，并且只计算所有相关行，从而生成用户列表。

    所以我的猜测是 - 如果您希望结果列表中有大量用户并且正在搜索您的很多用户已经阅读的书籍组合，那么在 IO 中进行全表扫描可能会更快（内存消耗应该可以忽略不计...）-另一方面，如果您的列表中有相当特殊且很少阅读的书籍，和/或生成的命中集将是非常少的用户，则多个 JOIN 访问可能更快如果优化器可以快速缩减它并且不需要那么多的 IO 访问......

    总体而言，影响性能的主要因素很可能是磁盘读取的数量和这些读取的实际位置（非连续），并且大量的索引访问可能会影响您的性能，即使具有多个连接的算法应该理论上会更快。

    但是正如 ypercube 所说，解决这个问题的唯一正确方法是对实际数据（不是暂存数据，而是非常接近您的真实/预期客户数据的数据）运行基准测试

    可用性/可维护性

    如果你问我在实际生产代码中会使用什么？显然，唯一可行的选择是 GROUP BY/HAVING 替代方案，因为它很灵活（您可以将数组/列表绑定为变量并搜索随机数量的书籍），您还可以搜索 7 本书中的 5 本书，等等上。使用 join 解决方案，您的代码中将有 7 个不同的查询，每个查询针对许多书籍......并且对于其他用例非常不灵活

    此外，当用代码编写时，GROUP BY/HAVING 解决方案非常惯用。通过更清晰的注释和格式，任何程序员都会立即理解查询。SELF-JOIN 怪物将更难理解和维护......

    • 2

相关问题