mysql - 优化 MySQL 中的多对多查询

Question

我有一个名为“items”的表，看起来像这样......

id | name
––––––––––––
1  | APPLES 
2  | BANANAS
3  | ORANGES
4  | PEARS

...和一个名为“对”的联结表，在项目之间创建多对多关系...

id | item1_id | item2_id
––––––––––––––––––––––––
1  | 1        |  2 
2  | 1        |  4
3  | 2        |  3
4  | 2        |  4
5  | 4        |  3

我有以下查询来查找与给定项目配对的项目...

SELECT * FROM items i
WHERE
  i.id IN (SELECT item1_id FROM pairs WHERE item2_id = 4)
OR
  i.id IN (SELECT item2_id FROM pairs WHERE item1_id = 4)

返回类似...

id | name
––––––––––––
1  | APPLES 
3  | ORANGES

...完成这项工作，但是，它运行得相当慢(使用大约 100 个项目的小型测试数据集，1000 个配对已经花费了大约 75 毫秒)。

我的问题是 – 是否可以进一步优化以加快速度(例如使用连接而不是嵌套查询)？

感谢您的帮助。

Answer 1

内部查询优化器在创建执行计划方面做得很好，尽管您可以查看该计划并找出瓶颈。诸如以不同方式表达相同查询之类的事情在一天结束时通常不会产生巨大差异。即使是看起来非常疯狂的查询，您也会惊讶于优化器处理它们的能力以及同一查询的两个看似不同的表达式最终如何导致相同的结果。将其更改为使用连接可能会导致相同或相似的执行计划。

所以我首先要做的是在您的 item1_id 列上创建一个索引，并在您的 item2_id 列上创建一个单独的索引。这将有助于提高那些 where 子句的性能。然后，如果仍然不能满足您的要求，请查看 the Optimization chapter in the MySQL docs (对于您使用的 MySQL 的任何版本)完整的可能策略。请注意，过早避免大量优化对您有好处，尤其是当您的应用程序很复杂时。一旦您的应用程序处于主要工作状态，您将能够更好地识别和解决瓶颈。但在任何开发阶段，索引始终是简单且值得迈出的第一步。