使用大量内部连接到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架构。

- Juan Jo

---

你能发布解释吗？ - Mihai

当然，这里有一个解释。根据用户想要应用的网站过滤器数量，将会有另一个内连接，从而可能产生总共22个内连接（很可能没有结果能够满足该过滤器）。谢谢。 - Juan Jo

这是EAV反模式的缺点。http://mikesmithers.wordpress.com/2013/12/22/the-anti-pattern-eavil-database-design/ - Jodrell

wp_postmeta表是否在(post_id, meta_key, meta_value)上有索引？如果有，这将会很有帮助。特别是如果该索引包含您实际尝试选择的数据列，并且您将选择列表限制为这些列。 - Jodrell

为什么要使用 SELECT *？应该使用 SELECT ColumnINeed1, ColumnINNeed2 ...。这样可以获取所有连接表中的列。 - Vojtěch Dohnal

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 是一种特殊类型的表，称为键值或 实体属性值存储。这里发生了什么事情? 几件事情:

1. 使用此模式，你可以得到每个帖子的一行，其中包含来自posts 表和postmeta 表中的某些列。
2. 你正在对 postmeta 表进行 LEFT JOIN，因此如果缺少属性，仍将获得一行。
3. 你正在使用别名为postmeta 的名称。在这里，它是postmeta AS color。
4. 你正在将选择器meta_key （这里是 'color' = color.meta_key）包括在联接的 ON 条件中。
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)列上创建一个复合索引。如果您正在创建WordPress插件，那么在插件安装时完成这项工作可能是一项任务。

- O. Jones

---

八年后的跟进。我和Rick James一起创建了一个插件，用于修复wp_postmeta和类似的WordPress表中至少一些索引混乱问题。https://wordpress.org/plugins/index-wp-mysql-for-speed/ - O. Jones

Answer 2

这是一个WordPress数据库，您可能不愿对模式进行广泛更改，因为这可能会破坏应用程序的其他部分或使未来的升级变得复杂。

这个查询的困难之处显示了实体属性值设计的缺点。该设计具有灵活性，允许在运行时创建新的属性，但它使针对此类数据的许多查询比使用传统表更加复杂。 WordPress的架构没有优化好。 即使在最新版本4.0中，仍存在一些天真的索引错误。

对于这个特定的查询，以下两个索引是有帮助的：

CREATE INDEX `bk1` ON wp_postmeta (`post_id`,`meta_key`,`meta_value`(255));

CREATE INDEX `bk2` ON wp_posts (`post_type`,`post_status`,`post_title`(255));

bk1索引有助于查找确切的元键和值。

bk2索引有助于避免文件排序。

这些索引不能成为覆盖索引，因为post_title和meta_value是TEXT列，而这些列太长了无法完全索引。您必须将它们更改为VARCHAR(255)。但如果应用程序依赖于在该表中存储较长的字符串，则会有破坏风险。

+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref                        | rows | filtered | Extra                 |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
|  1 | SIMPLE      | wp_posts     | NULL       | ref  | bk2           | bk2  | 124     | const,const                |    1 |   100.00 | Using index condition |
|  1 | SIMPLE      | color        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | transmission | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | model        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | brand        | NULL       | ref  | bk1           | bk1  | 1542    | wp.wp_posts.ID,const,const |    1 |   100.00 | Using index           |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+

- Bill Karwin

---

太棒了！在2020年实际应用中，我们有一个1.5GB的postmeta表格，这些简单的索引确实让我们速度得到提升。谢谢！ - AndrewK

现在是2021年！设置了wp_postmeta索引后，我的查询时间从10秒降至1.5秒。非常感谢！ - Zac

数据库没有选择它，我需要对其进行修改： CREATE INDEX `bk1` ON wp_postmeta (`post_id`,`meta_key`(191),`meta_value`(191)); - Andreas Dirnberger

（255）前缀索引在WordPress升级到utfmb4字符集后不总是有效。 （191）前缀可以使用，或者您可以使用https://wordpress.org/plugins/index-wp-mysql-for-speed/上的新插件。 - O. Jones

@O.Jones，最新的InnoDB行格式支持更大的索引前缀，并且自MySQL 5.7以来，该行格式已成为默认设置。 - Bill Karwin

@BillKarwin，完全正确。但是看看这个链接，你会感到沮丧。https://wordpress.org/about/stats/ WordPress用户中有一半以上仍停留在5.5和5.6版本。 - O. Jones

Answer 3

这里详细介绍了如何加快 wp_postmeta 的速度：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

还有为什么 wp_postmeta 的引用如此缓慢？

- Rick James

---

这是正确的链接。 - 4lxndr

@ahinze - 谢谢。（这不是我第一次这样做了。） - Rick James

一个用于添加索引的插件：https://wordpress.org/plugins/index-wp-mysql-for-speed/ - Rick James

Answer 4

为了解决在使用utf8字符集的innodb表上进行10+个连接SQL查询的性能问题，在postmeta上创建一个新的索引：

首先备份数据库。将[wp_]postmeta.meta_key长度减少到191，以避免出现“指定的键太长；最大键长度为767字节”的错误。 ALTER TABLE wp_postmeta MODIFY meta_key VARCHAR(191); 创建索引 CREATE INDEX wpm_ix ON wp_postmeta (post_id, meta_key);

- sglessard

Answer 5

这个更好吗？

SELECT
            P.*,
            C.`meta_value` color,
            T.`meta_value` transmission,
            M.`meta_value` model,
            B.`meta_value` brand
    FROM
            `wp_posts` P
        JOIN
            `wp_postmeta` C
                ON P.`ID` = C.`post_id` AND C.`meta_key` = 'color'
        JOIN
            `wp_postmeta` T
                ON P.`ID` = T.`post_id` AND T.`meta_key` = 'transmission'
        JOIN
            `wp_postmeta` M
                ON P.`ID` = M.`post_id` AND M.`meta_key` = 'model'
        JOIN
            `wp_postmeta` B
                ON P.`ID` = B.`post_id` AND B.`meta_key` = 'brand'
    WHERE
            C.`meta_value` = 'red'
        AND
            T.`meta_value` = 'auto'
        AND
            M.`meta_value` = 'model'
        AND
            B.`meta_value` = 'brand'
        AND
            P.`post_status` = 'publish'
        AND
            P.`post_type` = 'car'
    ORDER BY
            P.`post_title`

如果仍然很慢，可能需要尝试添加此索引。

CREATE INDEX `IX-wp_postmeta-post_id-meta_key-meta_value`
    ON `wp_postmeta` (`post_id`, `meta_key`, `meta_value`);

您也可以尝试将此索引添加到wp_post中。

CREATE UNIQUE INDEX `IX-wp_post-post_status-post_type-post_title-ID`
    ON `wp_post` (`post_stauts`, `post_type`, `post_title`, `ID`);

您越能限制选择列表（在SELECT和FROM之间的部分），就越好。没有必要返回大量您不会使用的数据。如果整个选择列表都被索引“覆盖”，则可以获得最佳性能。

- Jodrell

---

小心！在 postmeta 上创建唯一索引会引入 WordPress 没有的约束。 - O. Jones

Answer 6

假设您能够实际更改处理结果的代码，我会将其简化为一个查询，并使用代码来过滤结果。

SELECT [wp_posts fields you care about], wp_postmeta.meta_key, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car'
   AND wp_postmeta.meta_key IN ('color', 'transmission', 'model', 'brand')
ORDER BY wp_posts.post_title, wp_postmeta.meta_key, wp_postmeta.meta_value;

或者，你可以做类似于这样的事情...

SELECT [wp_posts fields desired], COUNT(*) AS matchCount
FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car'
    AND ((meta_key = 'color' AND meta_value = 'red')
        OR (meta_key = 'transmission' AND meta_value = 'auto')
        OR [etc...]
        )
GROUP BY wp_posts.ID
HAVING matchCount = [number of key-value pairs you're checking]
;

- Uueerdo

Answer 7

在WordPress中，有一个很好的查询工具WP_Query。要搜索文章元数据值，您可以使用以下代码：

$args = array(
    'post_type'  => 'post',
    'meta_query' => array(
            array(
                'key'     => 'fieldname',
                'value'   => 'fieldvalue',
                'compare' => 'LIKE',
            ),
        ),
    );
    $query = new WP_Query( $args );
    if ( $query->have_posts() ) {
        while ( $query->have_posts() ) {
            $query->the_post();
            $custom = get_post_custom();

            print_r($post);
            print_r($custom);
        }
    } else {
        // no posts found
    }
    wp_reset_postdata();

想要了解有关查询 API 的更多信息，请访问此网站，其中有大量示例： http://codex.wordpress.org/Class_Reference/WP_Query

- xrissz

Answer 8

为了提高性能，请尝试：

明确要拉取的列。 查看您可能需要或不需要的索引。 限制被拉取的行数。

- RoganRicheart