改进WordPress主题中的MySQL Select语句
改进WordPress主题中的MySQL Select语句
提问于 2015-10-05 03:30:47
我正在使用来自Industrialthemes的WordPress主题引擎，可以看到首页的渲染使用了很多查询，这些查询在我的MySQL数据库中运行大约需要0.4秒。就像这样：
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1 =1
AND (wp_term_relationships.term_taxonomy_id IN (1))
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'closed')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 5;有什么方法可以改进这个查询吗？据我所知，WordPress安装已经为所有涉及的字段建立了默认索引。我在调优SQL Select语句方面的知识不是很好，所以我希望一些专家能帮助我解决这个问题。谢谢。
(来自评论)
CREATE TABLE wp_term_relationships (
object_id bigint(20) unsigned NOT NULL DEFAULT '0',
term_taxonomy_id bigint(20) unsigned NOT NULL DEFAULT '0',
term_order int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (object_id,term_taxonomy_id),
KEY term_taxonomy_id (term_taxonomy_id),
KEY idx1 (object_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci(稍后...)
CREATE TABLE wp_term_relationships (
object_id bigint(20) unsigned NOT NULL DEFAULT '0',
term_taxonomy_id bigint(20) unsigned NOT NULL DEFAULT '0',
term_order int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (object_id,term_taxonomy_id),
KEY term_taxonomy_id (term_taxonomy_id),
KEY idx1 (term_taxonomy_id,object_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci回答 1
Stack Overflow用户
发布于 2016-01-28 10:23:52
wp_term_relationships needs INDEX(term_taxonomy_id, object_id) -- in this order
wp_posts might benefit from INDEX(post_type, ID, post_status, post_date) -- in this order两者都是“覆盖”索引。
前者让JOIN高效工作，并让优化器可以选择从wp_term_relationships开始。它应该会取代KEY term_taxonomy_id (term_taxonomy_id)。
无论首先选择哪个表，后者都应该工作得很好。
(更多)
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS p.ID
FROM wp_posts AS p
WHERE p.post_type = 'post'
AND p.post_status IN ( 'publish', 'closed' )
AND EXISTS ( SELECT 1 FROM wp_term_relationships AS tr
WHERE p.ID = tr.object_id
AND tr.term_taxonomy_id IN (1) )
ORDER BY p.post_date DESC
LIMIT 0, 5;有了这个公式，
如果解释以p开始：
p: (post_date, post_type, post_status, ID)
p: (post_type, post_status, ID, post_date)
tr: (object_id, term_taxonomy_id) -- which you have如果EXPLAIN以tr开头：
p: (ID) -- which you probably have
tr: (term_taxonomy_id, object_id)主要问题：
- The
GROUP BY正在增加努力。(我通过将EXISTS.) IN ( 'publish', 'closed' )更改为index.SQL_CALC_FOUND_ROWS来消除它--这会抑制index.SQL_CALC_FOUND_ROWS的有效使用--这意味着当它得到5 rows.IN (1)变成JOIN时不能停止，这没问题；但IN (1,2)更混乱。
或者，更直截了当地说，WP还没有被设计成规模。
请添加索引并获取EXPLAIN SELECT。
来自pastebin的：
SELECT SQL_NO_CACHE p.ID
FROM wp_posts AS p
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND EXISTS
(
SELECT 1
FROM wp_term_relationships AS tr
WHERE p.ID = tr.object_id
AND EXISTS
(
SELECT 1
from wp_term_taxonomy AS tt
WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
AND tt.taxonomy = 'post_tag'
AND tt.term_id IN (548, 669) )
);这是一个不同的查询。它还需要这样做：
tt: INDEX(term_taxonomy_id, taxonomy, -- in either order
term_id) -- last还有..。
SELECT SQL_NO_CACHE wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships tr
ON (wp_posts.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt
ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE ( post_type = 'post'
AND post_status = 'publish'
AND tt.taxonomy = 'post_tag'
AND tt.term_id IN (548, 669)
)
GROUP BY wp_posts.ID;需求
tt: INDEX(taxonomy, term_id, term_taxonomy_id) -- in this order我会将这两个索引都添加到tt中，看看EXPLAINs和性能会发生什么变化。
重写查询看看这是否给了你“正确”的答案：
SELECT p.ID, p.post_name, p.post_title,
p.post_type, p.post_status,
tt.term_id as termid, tt.taxonomy
FROM wp_posts AS p
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND tt.taxonomy = 'post_tag'
AND tt.term_id IN (548, 669)
ORDER BY p.ID;备注：
GROUP BYremoved- AND/OR可能不会像预期的那样工作：
a AND (b OR c) - Did
a AND b OR c等同于(a AND b) OR c，但我认为您希望添加推荐的索引？
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32937517
复制相关文章
相似问题