MySQL的. WordPress.使用IN语句时查询速度慢 [英] MySQL. WordPress. Slow query when using IN statements
问题描述
我正在尝试找到一种更好的方法来编写由WordPress的WP_Query类生成的以下查询.现在，它非常很慢.
I'm trying to figure out a better way to write the following query that is generated by WordPress's WP_Query class. Right now it is very slow.
SELECT SQL_CALC_FOUND_ROWS 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)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)
INNER JOIN wp_postmeta AS mt7 ON (wp_posts.ID = mt7.post_id)
INNER JOIN wp_postmeta AS mt8 ON (wp_posts.ID = mt8.post_id)
WHERE 1=1 AND wp_posts.post_type = 'gemstone'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')
AND (wp_postmeta.meta_key = 'gemstone_active_price'
AND (mt1.meta_key = 'gemstone_status' AND CAST(mt1.meta_value AS CHAR) = 'Available')
AND (mt2.meta_key = 'gemstone_length' AND CAST(mt2.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '9')
AND (mt3.meta_key = 'gemstone_width' AND CAST(mt3.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '9')
AND (mt4.meta_key = 'gemstone_depth' AND CAST(mt4.meta_value AS DECIMAL(10,2)) BETWEEN '0' AND '7')
AND (mt5.meta_key = 'gemstone_color' AND CAST(mt5.meta_value AS CHAR) IN ('L','K','J','I','H','G','F','E','D'))
AND (mt6.meta_key = 'gemstone_clarity' AND CAST(mt6.meta_value AS CHAR) IN ('I3','I2','I1','SI2','SI1','VS2','VVS2','VVS1','IF','FL'))
AND (mt7.meta_key = 'gemstone_weight' AND CAST(mt7.meta_value AS DECIMAL(10,2)) BETWEEN '0.67' AND '1.85')
AND (mt8.meta_key = 'gemstone_active_price' AND CAST(mt8.meta_value AS DECIMAL(10,2)) BETWEEN '960' AND '2300')
)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 20
我知道它看起来像是一团糟，但是当我在WHERE子句(上面的mt5和mt6)中没有2个IN语句时，整个过程执行得非常快.问题是，我对SQL不太了解，无法找到另一种避免使用IN语句的查询编写方式.有什么想法吗?
I know it looks like a big mess, but the whole thing executes very quickly when I don't have the 2 IN statements in the WHERE clause (mt5 and mt6 above). The problem is, I don't know SQL well enough to figure out another way to write the query that avoids using the IN statements. Any ideas?
更新:
这是此查询的EXPLAIN输出，以防万一.如果有人有其他想法，我会敞开心anything.这让我完全难住了.
UPDATE:
Here is the EXPLAIN output for this query in case it will help anyone. If anyone has any other thoughts, I'm open to anything. This has me completely stumped.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wp_postmeta ref post_id,meta_key meta_key 768 const 2 Using where; Using temporary; Using filesort
1 SIMPLE mt1 ref post_id,meta_key post_id 8 db.wp_postmeta.post_id 2 Using where
1 SIMPLE mt2 ref post_id,meta_key post_id 8 db.mt1.post_id 2 Using where
1 SIMPLE mt3 ref post_id,meta_key post_id 8 db.wp_postmeta.post_id 2 Using where
1 SIMPLE mt4 ref post_id,meta_key post_id 8 db.mt2.post_id 2 Using where
1 SIMPLE mt5 ref post_id,meta_key post_id 8 db.wp_postmeta.post_id 2 Using where
1 SIMPLE mt6 ref post_id,meta_key post_id 8 db.wp_postmeta.post_id 2 Using where
1 SIMPLE mt7 ref post_id,meta_key post_id 8 db.mt3.post_id 2 Using where
1 SIMPLE mt8 ref post_id,meta_key post_id 8 db.wp_postmeta.post_id 2 Using where
1 SIMPLE wp_posts eq_ref PRIMARY,type_status_date PRIMARY 8 db.wp_postmeta.post_id 1 Using where
更新2:
经过更多的实验后，我意识到，导致查询速度下降的不仅是IN()语句.似乎一个以上的IN()与三个以上的BETWEEN...AND...语句的任何组合都会对性能产生巨大影响.
UPDATE 2:
After some more experimentation, I have realized that it's not just the IN() statements that are slowing this query down. It seems that any combination of more than one IN() with more than 3 BETWEEN...AND... statements has a dramatic impact on performance.
例如，如果我删除最后两个AND子句(相对于4.9s)，则查询将在0.04s内执行；如果我将两个AND子句与WP_Query API来实现这一点，如果我这样做了，我想知道这是否比只执行一次查询和查询更快呢?然后通过PHP过滤结果.
For example, the query executes in about 0.04s if I drop the last 2 AND clauses (versus 4.9s with them), or it executes in 0.04s if I drop the 2 AND clauses with the IN() statements. This leads me to think that a 2 query solution might be best, but I have no idea how to implement that via the WordPress WP_Query API, and if I did, I wonder if that would even be quicker than just doing one query and then filtering the results via PHP.
我讨厌用PHP进行过滤的想法，因为我在多个地方读到应该将过滤留给数据库，因为这正是数据库擅长的地方.顺便说一下，如果有什么不同，我会在具有足够处理能力(Intel i7、12 GB RAM等)的计算机上的localhost WAMP服务器上的WordPress 3.3.1安装上运行这些查询.
I hate the idea of filtering with PHP because I've read in several places that filtering should be left to the database because that's what databases are good at. By the way, if it makes any difference, I'm running these queries on a WordPress 3.3.1 installation on my localhost WAMP server on a computer with plenty of processing power (Intel i7, 12 GB RAM, etc.).
更新3:
我当时正在考虑只是放弃查询中的所有IN()子句，并通过PHP删除这些子句，但这有一些严重的缺点.除了效率低下和有代码气味外，它还不允许我正确控制分页.当所有内容都在数据库中过滤后，我可以简单地使用LIMIT子句来处理分页.当我使用PHP进行过滤时，我不知道对于任何给定的偏移量将返回多少结果.因此，所有筛选实际上都需要在数据库中完成，问题是如何进行.有人对我有其他建议吗?任何其他信息对任何人都有用吗?
UPDATE 3:
I was thinking about just giving up and removing all the IN() clauses from the query and filter for those via PHP, but that has some serious draw backs. Besides being inefficient and a code smell, it won't allow me to control my pagination correctly. When everything is filtered in the database, I can simply use the LIMIT clause to handle pagination. When I filter with PHP, I have no idea how many results will be returned for any given offset. So, all the filtering really needs to be done in the database, the question is how. Does anyone have any additional suggestions at all for me? Would any additional information be helpful to anyone?
更新4:
在寻找解决方案时，我将其发布为WordPress核心追踪系统中的一个问题( http://core.trac.wordpress.org/ticket/20134 ).那里的一位开发人员建议，对于在元查询中使用IN的任何内容，我都尝试使用分类法而不是元数据.我接受了该建议，但看到了性能方面的改进，但不幸的是，这还远远不够.旧的查询需要4秒钟以上的时间才能运行，而使用分类法则可以减少到1秒钟以上.但是，我意识到我实际上需要4个IN类型子句(而不是原始的2个).使用其他2个分类法子句，查询需要18+秒的时间来执行.所以，我回到正题.我曾经有一个想法(可能是妄想)，因为我的职位很少符合标准，所以运行速度可能如此之慢.出于测试目的，我在数据库中只有3个帖子类型为'gemstone'.可以和它有任何关系吗?
UPDATE 4:
In my search for a solution to this, I posted it as an issue in the WordPress core trac system (http://core.trac.wordpress.org/ticket/20134). One of the devs there suggested I try to use taxonomies instead of meta data for anything that I use IN for in my meta query. I took that advice, and I saw a performance improvement, but unfortunately, it wasn't nearly enough. The old query took 4+ seconds to run, and using taxonomies it got down to 1+ seconds. However, I realized that I actually need 4 IN type clauses (not the original 2). With the 2 additional taxonomy clauses, the query takes 18+ seconds to execute. So, I'm back to square one. One thought I had (that's probably delusional) is that this could be running so slow because I have so few posts that meet the criteria. For testing purposes, I only have 3 posts in the database that have the 'gemstone' post type. Could that have anything to do with it?
如果有人感兴趣，我的新SQL如下所示:
If anyone is interested, my new SQL looks like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.*
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id)
INNER JOIN wp_term_relationships AS tt2 ON (wp_posts.ID = tt2.object_id)
INNER JOIN wp_term_relationships AS tt3 ON (wp_posts.ID = tt3.object_id)
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)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
INNER JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id)
INNER JOIN wp_postmeta AS mt5 ON (wp_posts.ID = mt5.post_id)
INNER JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (71,72,73,74)
AND tt1.term_taxonomy_id IN (89,90,91,92,93,95,96,97)
AND tt2.term_taxonomy_id IN (56,50,104,53)
AND tt3.term_taxonomy_id IN (59,60,62)
)
AND wp_posts.post_type = 'gemstone'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private')
AND (wp_postmeta.meta_key = 'gemstone_weight'
AND (mt1.meta_key = 'gemstone_status' AND CAST(mt1.meta_value AS CHAR) = 'Available')
AND (mt2.meta_key = 'gemstone_length' AND CAST(mt2.meta_value AS DECIMAL(8,2)) BETWEEN '0' AND '9')
AND (mt3.meta_key = 'gemstone_width' AND CAST(mt3.meta_value AS DECIMAL(8,2)) BETWEEN '0' AND '9' )
AND (mt4.meta_key = 'gemstone_depth' AND CAST(mt4.meta_value AS DECIMAL(8,2)) BETWEEN '0' AND '7')
AND (mt5.meta_key = 'gemstone_weight' AND CAST(mt5.meta_value AS DECIMAL(8,2)) BETWEEN '0.81' AND '1.81')
AND (mt6.meta_key = 'gemstone_active_price' AND CAST(mt6.meta_value AS DECIMAL(8,2)) BETWEEN '1083.9' AND '2078.26')
)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 20
，新的EXPLAIN输出如下:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wp_postmeta ref post_id,meta_key meta_key 768 const 3 Using where; Using temporary; Using filesort
1 SIMPLE tt3 ref PRIMARY,term_taxonomy_id PRIMARY 8 db.wp_postmeta.post_id 1 Using where; Using index
1 SIMPLE tt2 ref PRIMARY,term_taxonomy_id PRIMARY 8 db.wp_postmeta.post_id 1 Using where; Using index
1 SIMPLE wp_term_relationships ref PRIMARY,term_taxonomy_id PRIMARY 8 db.tt2.object_id 1 Using where; Using index
1 SIMPLE wp_posts eq_ref PRIMARY,type_status_date PRIMARY 8 db.wp_postmeta.post_id 1 Using where
1 SIMPLE tt1 ref PRIMARY,term_taxonomy_id PRIMARY 8 db.wp_posts.ID 1 Using where; Using index
1 SIMPLE mt5 ref post_id,meta_key post_id 8 db.wp_posts.ID 2 Using where
1 SIMPLE mt6 ref post_id,meta_key post_id 8 db.wp_posts.ID 2 Using where
1 SIMPLE mt1 ref post_id,meta_key post_id 8 db.mt5.post_id 2 Using where
1 SIMPLE mt2 ref post_id,meta_key post_id 8 db.mt1.post_id 2 Using where
1 SIMPLE mt3 ref post_id,meta_key post_id 8 db.tt2.object_id 2 Using where
1 SIMPLE mt4 ref post_id,meta_key post_id 8 db.tt3.object_id 2 Using where
更新5: 由于有评论，我最近在优化此查询上又做了一次尝试，但得出的结论是SQL相当漂亮必须按原样设置很多东西.但是，在测试某些替代方法时，我发现奇怪的是查询现在运行得快得多.我还没有更新我的MySQL服务器，所以我能理解的唯一原因是WordPress以某种方式更新了它们的数据库结构以提高性能.更新4中显示的完全相同的查询现在大约需要2.4秒.我认为仍然太长了(因此我仍在使用STRAIGHT_JOIN，如下面的答案所示)，但是我对该改进感到惊讶，这让我想知道是否有其他解决方案可以进一步优化此解决方案.这是新的EXPLAIN输出.它看起来和我几乎一样，但是我真的不知道如何解释它.
UPDATE 5: Because of a comment, I recently took another stab at optimizing this query, but I was left with the conclusion that the SQL pretty much has to be setup the way it is. However, in testing some alternatives, I found that strangely the query runs much faster now. I haven't updated my MySQL server, so the only reason I can fathom is that WordPress updated their database structure in someway to improve performance. The exact same query shown in update 4 now takes approximately 2.4 seconds. Still far too long in my opinion (so I'm still using STRAIGHT_JOIN as shown in my answer below), but I was surprised by the improvement, and it makes me wonder if a different solution is out there that can optimize this even more. Here is the new EXPLAIN output. It looks almost identical to me, but I don't really know how to interpret it.
+-----+--------------+------------------------+---------+---------------------------+-----------+----------+-------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----+--------------+------------------------+---------+---------------------------+-----------+----------+-------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | wp_postmeta | ref | post_id,meta_key | meta_key | 768 | const | 5 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | wp_term_relationships | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_postmeta.post_id | 1 | Using where; Using index |
| 1 | SIMPLE | tt2 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_term_relationships.object_id | 1 | Using where; Using index |
| 1 | SIMPLE | tt3 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_term_relationships.object_id | 1 | Using where; Using index |
| 1 | SIMPLE | wp_posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | db.wp_postmeta.post_id | 1 | Using where |
| 1 | SIMPLE | tt1 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_posts.ID | 1 | Using where; Using index |
| 1 | SIMPLE | mt3 | ref | post_id,meta_key | post_id | 8 | db.tt2.object_id | 3 | Using where |
| 1 | SIMPLE | mt4 | ref | post_id,meta_key | post_id | 8 | db.tt3.object_id | 3 | Using where |
| 1 | SIMPLE | mt5 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 3 | Using where |
| 1 | SIMPLE | mt6 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 3 | Using where |
| 1 | SIMPLE | mt1 | ref | post_id,meta_key | post_id | 8 | db.mt5.post_id | 3 | Using where |
| 1 | SIMPLE | mt2 | ref | post_id,meta_key | post_id | 8 | db.mt3.post_id | 3 | Using where |
+-----+--------------+------------------------+---------+---------------------------+-----------+----------+-------------------------------------+-------+----------------------------------------------+
推荐答案
我目前偶然发现的解决方案"非常丑陋，但由于某些无法解释的原因，它可以工作.添加STRAIGHT_JOIN优化程序提示将执行时间从18+秒减少到大约0.0022秒.基于常识和这个问题(何时在MySQL中使用STRAIGHT_JOIN )，这种解决方案似乎是个坏主意，但是我尝试过的唯一有效的方法.因此，至少到目前为止，我一直坚持下去.如果有人对我为什么不应该这样做或应该尝试什么做任何想法，我很想听听他们的意见.
The "solution" I've stumbled on for now is pretty ugly, but for some inexplicable reason, it works. Adding the STRAIGHT_JOIN optimizer hint dropped the execution time from 18+ seconds to approximately 0.0022 seconds. Based on common sense and this question (When to use STRAIGHT_JOIN with MySQL), this solution seems like a bad idea, but it's the only thing I've tried that has worked. So, for now at least, I'm sticking to it. If anyone has any thoughts on why I shouldn't do this, or what I should try instead, I would love to hear them.
如果有人好奇，我可以将其实现为WordPress过滤器，如下所示:
If anyone is curious, I implemented it as a WordPress filter like so:
function use_straight_join( $distinct_clause ) {
$distinct_clause = ( $use_straight_join ) ? 'STRAIGHT_JOIN' . $distinct_clause : $distinct_clause;
return $distinct_clause;
}
add_filter( 'posts_distinct', 'use_straight_join' );
为完整起见，这是使用STRAIGHT_JOIN时查询的EXPLAIN输出.同样，我感到困惑.旧查询仅使用ref和eq_ref，据我了解，它们比range快，但是由于某些原因，这要快几个数量级.
And for completeness, here is the EXPLAIN output for the query when using STRAIGHT_JOIN. Again, I'm baffled. The old query used only ref and eq_ref which I understand to be faster than range, but this is orders of magnitude faster for some reason.
+-----+--------------+------------------------+--------+---------------------------+-------------------+----------+-----------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----+--------------+------------------------+--------+---------------------------+-------------------+----------+-----------------+-------+----------------------------------------------+
| 1 | SIMPLE | wp_posts | range | PRIMARY,type_status_date | type_status_date | 124 | NULL | 6 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | wp_postmeta | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | mt1 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | mt2 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | mt3 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | mt4 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | mt5 | ref | post_id,meta_key | post_id | 8 | db.mt3.post_id | 2 | Using where |
| 1 | SIMPLE | mt6 | ref | post_id,meta_key | post_id | 8 | db.wp_posts.ID | 2 | Using where |
| 1 | SIMPLE | wp_term_relationships | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_posts.ID | 1 | Using where; Using index |
| 1 | SIMPLE | tt1 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_posts.ID | 1 | Using where; Using index |
| 1 | SIMPLE | tt2 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.mt1.post_id | 1 | Using where; Using index |
| 1 | SIMPLE | tt3 | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | db.wp_posts.ID | 1 | Using where; Using index |
+-----+--------------+------------------------+--------+---------------------------+-------------------+----------+-----------------+-------+----------------------------------------------+
这篇关于MySQL的. WordPress.使用IN语句时查询速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
