如何优化WordPress分页并降低指定分类SQL查询耗时？

先看你给出的原查询SQL：

SELECT wp_posts.ID     
FROM wp_posts  LEFT JOIN wp_term_relationships 
ON (wp_posts.ID = wp_term_relationships.object_id)     
WHERE 1=1  AND (wp_term_relationships.term_taxonomy_id IN (262)) 
AND ((wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')))     
GROUP BY wp_posts.ID     
ORDER BY wp_posts.post_date DESC     
LIMIT 0, 15;

第一步：精简SQL语句 #

• 替换LEFT JOIN为INNER JOIN：WHERE条件明确限定了term_taxonomy_id = 262，LEFT JOIN会先保留所有wp_posts数据再过滤，改成INNER JOIN只匹配关联的有效记录，减少数据处理量。
• 移除冗余的GROUP BY：默认WordPress中一篇文章不会重复关联同一个分类，GROUP BY wp_posts.ID完全多余，去掉后能避免分组排序的额外开销。

优化后的SQL：

SELECT wp_posts.ID     
FROM wp_posts 
INNER JOIN wp_term_relationships 
ON wp_posts.ID = wp_term_relationships.object_id     
WHERE wp_term_relationships.term_taxonomy_id = 262
AND wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish'     
ORDER BY wp_posts.post_date DESC     
LIMIT 0, 15;

第二步：添加针对性索引（核心性能提升） #

根据查询条件和排序逻辑，创建两个联合索引：

1. 给wp_term_relationships创建索引，优先按分类ID过滤，再关联文章ID：

CREATE INDEX idx_term_rel_tax_object ON wp_term_relationships(term_taxonomy_id, object_id);

2. 给wp_posts创建覆盖索引，包含查询条件、排序字段和返回字段，避免回表查询：

CREATE INDEX idx_posts_type_status_date ON wp_posts(post_type, post_status, post_date DESC, ID);

第三步：解决深层分页性能问题 #

如果分页到较靠后的页面（比如LIMIT 1000,15），OFFSET会跳过大量数据导致性能下降，建议改用游标分页：

SELECT wp_posts.ID     
FROM wp_posts 
INNER JOIN wp_term_relationships 
ON wp_posts.ID = wp_term_relationships.object_id     
WHERE wp_term_relationships.term_taxonomy_id = 262
AND wp_posts.post_type = 'post' 
AND wp_posts.post_status = 'publish'
AND (wp_posts.post_date < '上一页最后文章的日期' OR (wp_posts.post_date = '上一页最后文章的日期' AND wp_posts.ID < '上一页最后文章的ID'))
ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC     
LIMIT 15;

额外建议 #

启用WordPress缓存插件（比如WP Super Cache、Redis缓存），直接缓存分页页面的HTML结果，从根源减少SQL查询次数。

内容的提问来源于stack exchange，提问作者lock skt