mysql - 使用大量内部连接改进查询 wp_postmeta，一个键/值表

Question

我正在使用一个执行以下查询的 wordpress 网站，但我看到这个查询正在执行许多内部连接，并且该网站需要很长时间才能加载并且下降很多，我一直在尝试创建一个产生的查询同样的结果，但还没有成功

我想知道有什么更好的方法来做到这一点

SELECT *
FROM wp_posts
INNER JOIN wp_postmeta color ON wp_posts.ID = color.post_id 
INNER JOIN wp_postmeta transmission ON wp_posts.ID = transmission.post_id 
INNER JOIN wp_postmeta model ON wp_posts.ID = model.post_id 
INNER JOIN wp_postmeta brand ON wp_posts.ID = brand.post_id 

AND color.meta_key = 'color' 
AND color.meta_value = 'red' 
AND transmission.meta_key = 'transmission' 
AND transmission.meta_value = 'auto' 
AND model.meta_key = 'model' 
AND model.meta_value = 'model' 
AND brand.meta_key = 'brand' 
AND brand.meta_value = 'brand'

AND wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'car'
ORDER BY wp_posts.post_title

这是解释输出。

+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table         | type   | possible_keys               | key      | key_len | ref                          | rows | Extra                                        |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | color         | ref    | post_id,meta_key            | meta_key | 768     | const                        |  629 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts      | eq_ref | PRIMARY,type_status_date,ID | PRIMARY  | 8       | tmcdb.color.post_id          |    1 | Using where                                  |
|  1 | SIMPLE      | brand         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.wp_posts.ID            |    4 | Using where                                  |
|  1 | SIMPLE      | transmission  | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.color.post_id          |    4 | Using where                                  |
|  1 | SIMPLE      | model         | ref    | post_id,meta_key            | post_id  | 8       | tmcdb.transmission.post_id   |    4 | Using where                                  |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+

Wordpress schema here.

Answer 1

您似乎正在尝试获取一个结果集，其中每个类型为 car 的帖子一行。似乎您想在帖子中显示每辆车的各种属性，而这些属性隐藏在 postmeta 中。

专业提示:永远不要在软件中使用SELECT *，除非您完全知道为什么要这样做。特别是对于包含大量 JOIN 操作的查询，SELECT * 会返回大量无意义和冗余的列。

对于 WordPress postmeta 表，有一个查询设计技巧需要了解。如果你想获得一个特定的属性，这样做:

 SELECT p.ID, p.post_title,
        color.meta_value AS color
   FROM wp_posts AS p
   LEFT JOIN wp_postmeta AS color ON p.ID = color.post_id AND 'color' = color.meta_key
  WHERE p.post_status = 'publish'
    AND /* etc etc */

在做您想做的事情时，理解这种模式非常重要。此模式是必需的，因为 postmeta 是一种特殊类型的表，称为 key-value或 entity-attribute-value店铺。这里发生了什么？一些事情:

1. 使用此模式，您可以为每个帖子获取一行，其中包含 posts 表中的一些列和 postmeta 表中的特定属性。
2. 您正在 LEFT JOINpostmeta 表中，因此如果缺少该属性，您仍然会得到一行。
3. 您正在为 postmeta 表使用别名。这是 postmeta AS color。
4. 您在连接的 ON 条件中包含了 meta_key 的选择器(这里是 'color' = color.meta_key)。
5. 您在 SELECT 子句中使用别名来呈现具有适当列名的 postmeta.meta_value 项目。这是 color.meta_value AS color。

一旦您习惯了使用这种模式，您就可以将它堆叠起来，使用级联的 LEFT JOIN 操作，以获得许多不同的属性，就像这样。

     SELECT wp_posts.ID, wp_posts.post_title, wp_posts.whatever,
            color.meta_value        AS color,
            transmission.meta_value AS transmission,
            model.meta_value        AS model,
            brand.meta_value        AS brand
       FROM wp_posts

  LEFT JOIN wp_postmeta  AS color 
         ON wp_posts.ID = color.post_id        AND color.meta_key='color'

  LEFT JOIN wp_postmeta  AS transmission
         ON wp_posts.ID = transmission.post_id AND transmission.meta_key='transmission'

  LEFT JOIN wp_postmeta  AS model
         ON wp_posts.ID = model.post_id        AND model.meta_key='model'

  LEFT JOIN wp_postmeta  AS  brand
         ON wp_posts.ID = brand.post_id        AND brand.meta_key='brand'

      WHERE wp_posts.post_status = 'publish'
        AND wp_posts.post_type = 'car'
   ORDER BY wp_posts.post_title

我已经对这个查询做了一堆缩进，以便更容易看到模式。您可能更喜欢不同的缩进样式。

很难知道您问题中的查询为何会出现性能问题。这可能是因为您得到了所有 INNER JOIN 操作的组合爆炸，然后被过滤。但无论如何，您显示的查询可能没有返回任何行。

如果您仍然遇到性能问题，请尝试在 (post_id, meta_key, meta_value) 列的 postmeta 上创建复合索引。如果您要创建 WordPress 插件，那可能是在插件安装时要做的工作。