如何通过多个JOIN来提高查询性能

Question

我有一个查询（用于创建视图），它使用几个联接来获取每一列。对于每组添加的联接，性能会快速下降（指数级别？）。

如何使这个查询更快呢？请查看查询中的注释。

如果有帮助，这是使用WordPress DB模式。

下面是 EXPLAIN 的截图

产品表

+--+----+
|id|name|
+--+----+
|1 |test|
+--+----+

元数据表

+----------+--------+-----+
|product_id|meta_key|value|
+----------+--------+-----+
|1         |price   |9.99 |
+----------+--------+-----+
|1         |sku     |ABC  |
+----------+--------+-----+

TERM_RELATIONSHIPS表

+---------+----------------+
|object_id|term_taxonomy_id|
+---------+----------------+
|1        |1               |
+---------+----------------+
|1        |2               |
+---------+----------------+

分类术语表

+----------------+-------+--------+
|term_taxonomy_id|term_id|taxonomy|
+----------------+-------+--------+
|1               |1      |size    |
+----------------+-------+--------+
|2               |2      |stock   |
+----------------+-------+--------+

术语表

+-------+-----+
|term_id|name |
+-------+-----+
|1      |500mg|
+-------+-----+
|2      |10   |
+-------+-----+

查询

SELECT 
  products.id,
  products.name,
  price.value AS price,
  sku.value AS sku,
  size.name AS size
FROM products

/* These joins are performing quickly */

INNER JOIN `metadata` AS price ON products.id = price.product_id AND price.meta_key = 'price'
INNER JOIN `metadata` AS sku ON products.id = sku.product_id AND sku.meta_key = 'sku'

/* Here's the part that is really slowing it down - I run this chunk about 5 times with different strings to match */

INNER JOIN `term_relationships` AS tr ON products.id = tr.object_id
  INNER JOIN `term_taxonomy` AS tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'
    INNER JOIN `terms` AS size
    ON tt.term_id = size.term_id

- dloewen

---

"OR" 可能会破坏索引使用，尝试使用 UNION 重写。 - KM.

展示每个表的 DESC tableName 输出。 - Manu

请提供您的SQL查询语句，我将为您生成其执行计划。 - Manu

@Manu 我在问题中添加了EXPLAIN结果的图像。谢谢。 - dloewen

确定的解释是来自不同的查询！表tt2，tt3不存在。在你修复之前为-1。 - Tomas

Answer 1

您的性能问题很可能是由于与“term_taxonomy”表的连接引起的。
所有其他连接似乎都使用主键（在那里您可能已经有了有效的索引）。

因此，我的建议是在 term_taxonomy_id 和 term_id 上添加一个复合索引（或者如果必须： taxonomy）。像这样：

CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy
ON term_taxonomy( term_taxonomy_id, taxonomy);

希望这能帮到你。

- carleson

---

可以。+1。另外，为metadata (product_id, mate_key)制作一个复合索引也是个好主意，因为拥有广泛的索引多样性意味着MySQL优化器有更多选项来创建更好的执行计划。 - Karolis

太棒了！在产品ID和元键上在元数据表中创建复合索引，使查询的执行时间不到1秒，与之前的30多秒相比有了巨大的改进！ - dloewen

@Karolis，你能单独发布你的解决方案吗？这样我就可以给你赏金了。同时感谢carleson提供的提示，帮助找到了解决方案。 - dloewen

@dloewen 嗯，我认为carleson给了你正确的方向，我的评论只是为了让他的答案更全面 :-) - Karolis

Answer 2

确保所有带有"ON"条件语句的列都已经被索引。 这将极大地提高速度。

- akkig

Answer 3

以下脚本已按照 SQL Server 规则格式化 - 您可以根据 MySQL 规则进行更改并尝试 -

SELECT 
  P.id,
  P.name,
  PIVOT_METADATA.price,
  PIVOT_METADATA.sku,
  size.name AS size
FROM products P (NOLOCK)

INNER JOIN term_relationships AS tr (NOLOCK)
    ON P.id = tr.object_id

INNER JOIN term_taxonomy AS tt (NOLOCK)
    ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'

INNER JOIN terms AS size (NOLOCK)
    ON tt.term_id = size.term_id

INNER JOIN METADATA (NOLOCK)
    PIVOT
    (
        MAX(value)
        FOR [meta_key] IN (price,sku)
    )AS PIVOT_METADATA
    ON P.id = PIVOT_METADATA.product_id

我觉得您的查询可能出现瓶颈——您多次连接“Metadata”。由于表格中存在一对多的关系，所以“Metadata”连接2次并不会影响性能，但是当您连接更多的表格时，由于一对多的关系导致的行数增加，性能也会下降。

我尝试要达到的目标——尽可能满足1对1的关系。为此，我在“Metadata”上进行了数据透视，并将价格和sku作为列。现在，“Metadata”中的产品ID应该只有一行数据。另外，我还确保在最后连接透视表。

请尝试一下我的方法。请告诉我预期的性能、您拥有的记录数量以及使用我的答案后得到的性能。

- Suyash Khandwe

---

谢谢，我想尝试一下，但是MySQL中没有PIVOT，所以我不确定该如何适应。 - dloewen

Answer 4

试试这个：

SELECT p.id, p.name, MAX(CASE m.meta_key WHEN 'price' THEN m.value ELSE '' END) AS price, 
       MAX(CASE m.meta_key WHEN 'sku' THEN m.value ELSE '' END) AS sku, s.name AS size
FROM products p 
INNER JOIN `metadata` AS m ON p.id = m.product_id  
INNER JOIN `term_relationships` AS tr ON p.id = tr.object_id 
INNER JOIN `term_taxonomy` AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'
INNER JOIN `terms` AS s ON tt.term_id = s.term_id
GROUP BY p.id;

如果您仍然发现查询速度慢，请添加我的查询的EXPLAIN计划，以便我可以找出哪些列需要INDEX。

- Saharsh Shah

---

请注意，CASE符合SQL标准。 - Strawberry

Answer 5

    Declare @query as NVARCHAR(MAX)
    set @query = ('SELECT 
    products.id,
    products.name,
    price.value AS price,
    sku.value AS sku,
    size.name AS size
    FROM products
    INNER JOIN metadata AS price ON products.id = price.product_id AND price.meta_key = price
    INNER JOIN metadata AS sku ON products.id = sku.product_id AND sku.meta_key = sku
    INNER JOIN term_relationships AS tr ON products.id = tr.object_id
    INNER JOIN term_taxonomy AS tt
    ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = size
    INNER JOIN terms AS size
    ON tt.term_id = size.term_id
    into #t')

    exec(@query);
    select * from #t

我希望上述方法可以减少时间利用，或者创建一个具有您选择的所有字段的临时表，并通过连接到所有其他表的临时表来更新临时表，这也可能是有效的，好吧，我不能确定，但因为您的问题似乎很有趣，所以我也在等待您的结果。

- JB9

---

我看不出来这样做的意义在哪里？由于你有相同的查询，然后还使用了一个临时表，所以性能会变得更差。如果将大查询拆分为较小的子集，则可以通过使用临时表来加快缓慢的查询，但在这种情况下并非如此。 - carleson

Answer 6

METADATA_TABLE和TERM_RELATIONSHIP_TABLE没有任何主键。当这些表中有大量记录时，您的查询性能将受到影响。

提高性能的检查点。

1. 所有表都应该有主键。这是因为表中的行将被物理排序。
2. 对于涉及少量表的小型查询，将主键保留在表中就足够了。如果您仍然希望提高性能，请为诸如term_relationships表的*object_Id字段*等列创建非聚集索引。在参与连接操作的表中，应为那些列创建非聚集索引。

但是，需要注意的是，在发生多个插入和更新的表上，非聚集索引应该非常少。

这不是一个简单的问题，不能仅基于运行时间来回答。还有其他因素会影响答案，特别是存储过程运行的环境是否具有重要的事务性。

您可以在此处找到更多信息。

- gokul

---

所有的表都有一个主键，并且我要连接的每一列都被索引了。 - dloewen

Answer 7

我建议以下几点：

• 考虑从业务层面减少连接；
• 如果无法从“顶部”（业务层面）进行操作，并且数据不是实时的，我建议准备一个内存表（我知道这个解决方案并不理想）。然后直接从内存表中选择您的数据。

根据我的经验：

• “连接”是性能杀手，您的数据越大，感受到的痛苦就越多；
• 尽量摆脱连接，而不是通过保留连接来提高查询性能，除非必须这样做。通常我会从“顶部”到“底部”修复这些问题。
• 如果以上所有方法都不起作用，最后一种建议是考虑使用“map/reduce + 全文搜索”，如果值得这样做的话。

（请原谅我没有提供改进查询性能的解决方案。）

- Joshua