WP：在其中一台服务器上缓慢搜索 SQL

WP Slow search SQL on one of the servers

提问人：errorous 提问时间：11/7/2023 最后编辑：FanoFNerrorous 更新时间：11/7/2023 访问量：65

问：

我有 2 台专用服务器，S1,24 核和 128GB RAM，以及 S2,8 核和 64GB RAM。两个服务器 CPU 都具有多线程。在 S1 上，我运行 cPanel，而 S2 仅用作数据库服务器（远程）。两台服务器都运行 MySQL 8，但 S1 具有随 cPanel 一起安装的社区版。

问题出在（基本）WP搜索查询中。

SELECT 
  SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID 
FROM 
  wp_posts 
  INNER JOIN wp_postmeta AS a ON (a.post_id = wp_posts.ID) 
  LEFT JOIN wp_postmeta AS b ON (
    (
      (b.post_id = a.post_id)
    ) 
    AND (
      (
        b.meta_key LIKE CONCAT('\_', a.meta_key)
      )
    )
  ) 
WHERE 
  1 = 1 
  AND (
    wp_posts.ID NOT IN (
      SELECT 
        object_id 
      FROM 
        wp_term_relationships 
      WHERE 
        term_taxonomy_id IN (1427, 1428, 1429)
    )
  ) 
  AND (
    (
      (b.meta_id IS NOT NULL) 
      AND (a.meta_value LIKE '%test%')
    ) 
    OR (
      (
        wp_posts.post_title LIKE '%test%'
      ) 
      OR (
        wp_posts.post_content LIKE '%test%'
      ) 
      OR (
        wp_posts.post_excerpt LIKE '%test%'
      )
    )
  ) 
  AND (
    (
      wp_posts.post_type = 'post' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'attachment' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'book' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'sfwd-courses' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'groups' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'library' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'topic' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'series' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'videos' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'material' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    ) 
    OR (
      wp_posts.post_type = 'authors' 
      AND (
        wp_posts.post_status = 'publish' 
        OR wp_posts.post_status = 'acf-disabled' 
        OR wp_posts.post_status = 'graded' 
        OR wp_posts.post_status = 'not_graded' 
        OR wp_posts.post_status = 'private'
      )
    )
  ) 
GROUP BY 
  wp_posts.ID 
ORDER BY 
  wp_posts.post_title LIKE '%test%' DESC, 
  wp_posts.post_date DESC 
LIMIT 
  0, 12

请注意，此查询是由 WP 生成的，没有其他插件，也没有任何代码可以修改此查询。话虽如此，我无法更改查询本身。

现在，WP 安装在 S1 上，并调用 S2 来运行 SQL 查询。这个搜索在 S1 上需要 4.5 秒，在 S2 上需要 39 秒。请注意，这不是连接问题，因为我已经通过登录 S2 测试了上述查询，运行 ，并直接在服务器上运行查询。mysql console

我还检查了 INDEXES 是否存在问题，但两台服务器上的 INDEXES 是相同的。可能是什么问题？

编辑：原来是数据库的迁移出错了。这里是完整的，适用于暂存（运行良好的那个）和生产（查询速度慢的那个）。EXPLAINSHOW CREATE TABLE

服务器 1（慢查询）：

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=2058873 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=64892133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


explain SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta AS a
ON ( a.post_id = wp_posts.ID )
LEFT JOIN wp_postmeta AS b
ON ( (( b.post_id = a.post_id ))
AND (( b.meta_key LIKE CONCAT( '\_', a.meta_key ) )) )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (1427,1428,1429) ) )
AND ( ( ( b.meta_id IS NOT NULL )
AND ( a.meta_value LIKE '%test%' ) )
OR ( ( wp_posts.post_title LIKE '%test%' )
OR ( wp_posts.post_content LIKE '%test%' )
OR ( wp_posts.post_excerpt LIKE '%test%' ) ) )
AND ((wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'graded'
OR wp_posts.post_status = 'not_graded'
OR wp_posts.post_status = 'private'))
OR (wp_posts.post_type = 'attachment'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_statu[...]
编号 select_type 桌子 类型 possible_keys 钥匙 key_len 裁判 行 额外
1 简单 一个 零 都 post_id 零 零 零 870451 使用临时;使用文件排序
1 简单 wp_posts 零 eq_ref 主要，post_name，type_status_date，post_parent，post_author 主要 8 proddb.a.post_id 1 使用where
1 简单 零 eq_ref <auto_distinct_key> <auto_distinct_key> 9 proddb.a.post_id 1 在哪里使用;不存在
1 简单 b 零 裁判 post_id post_id 8 proddb.a.post_id 40 使用where
2 物化 wp_term_relationships 零 都 主要，term_taxonomy_id 零 零 零 43340 使用where

SERVER 2（常规查询时间）：

CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=2058548 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


CREATE TABLE `wp_postmeta` (
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=64885169 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta AS a
ON ( a.post_id = wp_posts.ID )
LEFT JOIN wp_postmeta AS b
ON ( (( b.post_id = a.post_id ))
AND (( b.meta_key LIKE CONCAT( '\_', a.meta_key ) )) )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (1427,1428,1429) ) )
AND ( ( ( b.meta_id IS NOT NULL )
AND ( a.meta_value LIKE '%test%' ) )
OR ( ( wp_posts.post_title LIKE '%test%' )
OR ( wp_posts.post_content LIKE '%test%' )
OR ( wp_posts.post_excerpt LIKE '%test%' ) ) )
AND ((wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'acf-disabled'
OR wp_posts.post_status = 'graded'
OR wp_posts.post_status = 'not_graded'
OR wp_posts.post_status = 'private'))
OR (wp_posts.post_type = 'attachment'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_statu[...]
编号 select_type 桌子 类型 possible_keys 钥匙 key_len 裁判 行 额外
1 简单 wp_posts 零 指数 主要，post_name，type_status_date，post_parent，post_author 主要 8 零 33523 在哪里使用;使用临时;使用文件排序
1 简单 零 eq_ref <auto_distinct_key> <auto_distinct_key> 9 stagedb.wp_posts.ID 1 在哪里使用;不存在
1 简单 一个 零 裁判 post_id post_id 8 stagedb.wp_posts.ID 17 使用where
1 简单 b 零 裁判 post_id post_id 8 stagedb.wp_posts.ID 17 使用where
2 物化 wp_term_relationships 零 都 主要，term_taxonomy_id 零 零 零 41970 使用where
sql mysql wordpress 查询优化

评论

0赞 Bill Karwin 11/7/2023
两个实例上的数据是否相同？如果 S2 有更多的数据，这可以解释差异。
0赞 errorous 11/7/2023
数据大致相同，差异可忽略不计
0赞 Bill Karwin 11/7/2023
原因可能是除了数据和元数据之外的很多事情。例如，MySQL 配置、MySQL 缓冲区中当前数据的部分、服务器硬件差异、来自其他进程的并发系统负载等。
0赞 errorous 11/7/2023
我刚刚删除了 s1 上 db 中的所有表，从 s2 上的 db 导入了所有表，现在 s1 上也很慢。所以，它是数据库。除了 INDEXes 之外，还有什么可以使此查询速度减慢 10 倍？
0赞 Bill Karwin 11/7/2023
我的猜测是，这是两件事之一：（1）数据需要在缓冲池中“预热”，然后查询才能获得良好的性能，或者（2）你误认为索引是相同的。但是既然你放下了桌子，就没有办法知道它们是什么。

答：

1赞 Bill Karwin 11/7/2023 #1

您可以看到每个 EXPLAIN 报告第一行中的字段完全不同。在慢速版本中检查了 870k 行，而在性能更好的版本中检查了 33k 行。rows

1   SIMPLE  a           NULL    ALL     post_id NULL     NULL NULL    870451  100.00  Using temporary; Using filesort
                                                                      ^^^^^^
...

1   SIMPLE  wp_posts    NULL    index   PRIMARY  PRIMARY 8    NULL    33523   10.14   Using where; Using temporary; Using filesort
                                                                      ^^^^^
...

即使数据保存在缓冲池的 RAM 中，性能通常也与检查的行成正比（假设查询执行的其他部分是相同的，例如使用临时表）。

我首先要尝试的是查询中的每个表。这可能有帮助，也可能没有帮助，但它很容易做到，而且通常可以随时安全运行，即使在繁忙的服务器上也是如此。这将刷新优化程序用于选择索引的索引统计信息，因此它可以轻松解决问题。ANALYZE TABLE

您可能需要使用索引提示来强制优化程序优先使用索引，而不是求助于表扫描。

此外，我注意到 EXPLAIN 中的最后一个表正在执行表扫描，检查 43k 行。当联接表必须执行表扫描，而不是借助索引缩小搜索范围时，这通常是个坏消息。

2   MATERIALIZED wp_term_relationships NULL ALL ... NULL NULL NULL 43340 100.00  Using where
                                                                   ^^^^^

在本例中，它说表访问类型是，这意味着它已自动将子查询的结果保存到临时表中，因此至少它不必重复执行表扫描。但是，即使只进行一次表扫描，也仍然很昂贵。MATERIALIZED

因此，我建议添加一个索引来帮助子查询仅查找匹配的行。我假设您的表是 WordPress 实例的一部分，我查找了该表的定义，我看到它应该已经在列上有一个索引。我建议您仔细检查该表以确认索引实际上已定义。term_taxonomy_id

评论

0赞 errorous 11/7/2023
所有的索引都在那里。我只是优化了帖子和 postmeta 表，现在真的很好。感谢您的详细回答！
2赞 O. Jones 11/7/2023 #2

您向我们展示的查询因在大型 WordPress 安装中速度慢而臭名昭著。

tl;博士

这里有一个免费的 GPL 插件（由 Rick James 和我开发），用于升级表上的索引，使这种查询更快。https://wordpress.org/plugins/index-wp-mysql-for-speed/

示例查询还实现了 WordPress 的搜索功能。核心 WordPress 使用臭名昭著的缓慢 SQL 查询模式进行搜索。简单地说，这是一种迟钝的搜索方式，适用于拥有数百个帖子/产品/页面/其他内容的网站。你拥有的远不止于此。使用插件更改搜索算法。我喜欢 Relevanssi，但还有其他几个好的。column LIKE '%searchterm%'

请考虑添加持久性对象缓存插件。

更长的解释，索引改进：形状像这样的查询（其中有你的）

... FROM wp_posts p
    LEFT JOIN wp_postmeta m ON p.ID = m.post_id and m.meta_key = 'something'

由于前缀索引和缺少复合索引，因此 WordPress 的标准索引速度很慢。添加复合索引可以加快这些查询的速度。使聚簇索引更有帮助。我们的插件会更改您网站的索引以执行此操作。(post_id, meta_key, meta_id)

更长的解释，搜索：当您的用户在 WordPress 的搜索框中输入时，它会变成如下所示的 SQLsomething

    column_a LIKE '%something%'
 OR column_b LIKE '%something%'
 OR column_c LIKE '%something%'

带有前导通配符。这是不可优化的，因此数据库服务器会扫描整个 posts 表。（午饭后见，嗯？您唯一的办法是安装一个使用更好算法的搜索插件。Relevanssi 将您的内容拆分为单独的单词，因此可以避免这种情况。其他搜索插件也做类似的事情。无需更改主题或页面即可安装它们，并加快幕后搜索速度。%%

更长的解释，持久的对象缓存：大多数网站都有一些像您这样的查询，这些查询会经常运行。例如，像您这样的查询显示许多在线商店的前门页面。持久性对象缓存挂起这些查询的结果，并在新用户请求相同信息时使用它们。

还有一件事。由于修饰符，MySQL的使用在大型站点上也是一个性能杀手。我有一个非常简单的实验性尚未发布的插件，用于将该修饰符的结果放入持久对象缓存中。如果你想尝试一下，欢迎你来。SELECT SQL_CALC_FOUND_ROWS ... LIMIT start, countSQL_CALC_FOUND_ROWS

评论

0赞 O. Jones 11/8/2023
而且，还有一件事。在这种问题上扔硬件 - 为大量计算核心或RAM付费 - 几乎无济于事。通过算法选择让生活更美好，而不是重铁！节省您的资金。

上一个：MySQL触发器在Wordpress（WooCommerce）中不起作用？

下一个：尝试使用 xampp 使用 localhost 运行网页时出现问题