mysql – 改进 WordPress 主题中的 MySQL Select 语句

  • A+
所属分类:WordPress技巧

我正在使用 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

最佳答案

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) .
无论先选择哪张 table ,后者都应该可以正常工作。

(更多的)

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;

有了这个配方,

如果 EXPLAIN 以 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)

主要问题:

  • GROUP BY正在增加努力。 (我通过将 JOIN 更改为 EXISTS 来消除它。)
  • IN ( 'publish', 'closed' ) -- 抑制索引的有效使用。
  • SQL_CALC_FOUND_ROWS -- 表示当它到达 5 行时它不能停止。
  • IN (1)变成= 1 ,这很好;但是 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 BY已删除
  • AND/OR 可能无法按预期工作:a AND b OR c相当于(a AND b) OR c , 但我想你想要 a AND (b OR c)
  • 您是否添加了推荐的索引?
    • 我的微信
    • 这是我的微信扫一扫
    • weinxin
    • 我的微信公众号
    • 我的微信公众号扫一扫
    • weinxin