wordpress 中的 mysql 慢查询和超时

Question

我不是sql专家。我的 wordpress 开始返回超时并且响应非常慢。当我开始挖掘时，我注意到 slow_query 日志可以告诉我很多信息。不幸的是我有很多缓慢的查询。例如:

# Time: 140425 17:03:29
# User@Host: geektime[geektime] @ localhost []
# Query_time: 7.024031  Lock_time: 0.000432 Rows_sent: 0  Rows_examined: 0
SET timestamp=1398434609;

SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS order1 ON order1.post_id = wp_posts.ID
AND order1.meta_key = '_event_start_date'
LEFT JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'
WHERE 1=1
  AND wp_posts.post_type = 'event'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'future'
       OR wp_posts.post_status = 'draft'
       OR wp_posts.post_status = 'pending')
  AND ((wp_postmeta.meta_key = '_event_start_date'
        AND CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17')
       OR (mt1.meta_key = '_event_end_date'
           AND CAST(mt1.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17'))
GROUP BY wp_posts.ID
ORDER BY order1.meta_value,
         order2.meta_value ASC;

列 post_id、meta_id 和 meta_key 在 wp_postmeta 表中有索引。ID、post_name、post_type、post_status、post_date、post_parent、post_author 和 guid 列在 wp_posts 表中编入索引。

然而，列 ID 和 GUID 被索引了两次，这是不是很糟糕？

并且有 4 个索引具有相同的 key_name: type_status_date，是不是坏了？

我怎么可能在 wp_posts 中有 60K 行而在 wp_postmeta 中有 3M 行？

我知道有很多问题要问，但我真的试图通过在线研究来理解。

提前致谢。

Answer 1

however, the columns ID and GUID are indexed twice, is it bad?

有两个不同的列，所以没有，除非你的意思是它们都有 两个 索引——在这种情况下是的，它很糟糕并且可能是你的主题或插件之一的错误(或 WP 本身的先前错误)。

and there are 4 indexs with the same key_name: type_status_date, is it bad?

同上:如果您指的是四个相同的索引，则它要么是主题，要么是插件，要么是 WP 错误，您可以安全地删除重复项。

How could it be that I have 60K rows in wp_posts and 3M rows in wp_postmeta?

因为 WP 元 API 糟糕并强制执行称为实体属性值(也称为 EAV)的数据库反模式:

http://en.wikipedia.org/wiki/Entity-attribute-value_model

粗略的谷歌搜索 SO 将产生大量线程，这些线程解释了为什么将数据存储在 EAV 或等效(json、hstore、xml 等)中是一个坏主意，如果这些东西需要出现在例如where、join 或 order by 子句。

您可以通过突出显示的慢查询形式亲眼看到效率低下的问题。该查询四次连接元表，两次使用强制转换运算符来引导——并且它在那个时候将值强制转换为 char 而不是 date。雪上加霜的是，它然后继续使用存储在其中的值对行进行排序。这是导致性能不佳的秘诀。

可悲的是，几乎没有办法摆脱这种污水令人厌恶的恶臭，除非编写自己的插件来创建适当的表来存储、索引和查询您需要的数据，而不是使用 WP 元 API，它很糟糕引用疯狂，以及使用它导致的腐烂的 SQL。

当您从头开始重写您正在使用的插件时，作为临时胶带和 WD-40 措施，您可以做的一件事是在一个或多个过滤器上抛出回调您会在一堆乱七八糟的类方法中找到 WP_Query#get_posts()。例如，保存完整和最终 SQL 查询的 posts_request 过滤器允许您使用 regex-foo 根据自己的喜好重写任何内容。这不是 Elixir :这样做可以让您修复错误，例如按字典顺序排序的整数值等，以及在非常偶然的查询优化中进行折腾；多一点。

编辑:在重新阅读您的查询后，我认为您在最后一点方面很幸运。您的特定查询具有以下可憎之处:

INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS order1 ON order1.post_id = wp_posts.ID
AND order1.meta_key = '_event_start_date'
LEFT JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'

其中两个具有共同的 _event_start_date，因此您可以将其分解:

SELECT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
       AND wp_postmeta.meta_key = '_event_start_date'
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
       AND mt1.meta_key = '_event_end_date'
INNER JOIN wp_postmeta AS order2 ON order2.post_id = wp_posts.ID
AND order2.meta_key = '_event_start_time'
WHERE 1=1
  AND wp_posts.post_type = 'event'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'future'
       OR wp_posts.post_status = 'draft'
       OR wp_posts.post_status = 'pending')
  AND (CAST(wp_postmeta.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17'
       OR CAST(mt1.meta_value AS CHAR) BETWEEN '2014-04-11' AND '2014-04-17')
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value,
         order2.meta_value ASC;