MariaDB因WordPress查询出现Creating sort index状态卡顿问题咨询

核心问题根因 #

• Creating sort index状态产生原因：你的慢查询缺少合适的联合索引，查询过滤分类后需要对wp_terms.name字段进行全量排序，数据量较大时就会生成临时排序索引，占用大量CPU和内存资源。
• 查询未被缓存的原因：1. 你当前配置未开启MariaDB查询缓存，且MariaDB 10.5及以上版本已正式废弃查询缓存功能；2. 即便开启缓存，只要wp_terms或wp_term_taxonomy表有任何数据变更，对应缓存就会自动失效，大型站点数据更新频繁时几乎无法命中缓存。
• 打开表速率告警说明：服务刚重启15分钟处于冷启动阶段，会集中加载所有高频访问的表，此时的速率统计存在误差，不算完全误判——你当前配置中table_open_cache使用默认值仅64，大型WordPress站点通常有上百张业务表，默认值确实无法满足需求。

具体解决步骤 #

1. 优先优化慢查询（核心解决Creating sort index问题） #

首先执行以下语句添加联合索引，让查询可以直接走索引完成过滤和排序，避免生成临时索引：

ALTER TABLE wp_term_taxonomy ADD INDEX idx_tax_termid (taxonomy, term_id);
ALTER TABLE wp_terms ADD INDEX idx_name_all (name, term_id, slug, term_group);

添加索引前先执行EXPLAIN确认执行计划：

EXPLAIN SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('categories') ORDER BY t.name ASC;

如果返回结果的Extra字段存在Using filesort、Using temporary，说明索引未生效，调整索引字段覆盖所有查询用到的字段即可。

2. 调整MariaDB配置 #

打开my.cnf修改以下参数（根据你服务器实际内存调整，以下为32G内存服务器参考值）：

# 取消原有注释，修改为对应值
table_open_cache = 2048
open_files_limit = 65535
innodb_buffer_pool_size = 20G # 设为服务器总内存的50%-70%
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log-queries-not-using-indexes

修改后重启MariaDB生效。

3. 上层缓存优化 #

不要依赖数据库层的查询缓存，在WordPress侧安装Redis或Memcached对象缓存插件，将分类查询这类固定结果缓存到内存中，减少数据库请求量。

4. 近期变更排查 #

如果故障是近期突发，确认是否有以下操作：

• 升级WordPress核心、主题或插件，新增了高频全分类查询逻辑
• 分类数据量大幅上涨，超过原有索引承载上限
• 调整过数据库配置、升级过MariaDB版本

告警后续验证 #

服务运行24小时后再次查看打开表速率，如果仍然高于10次/小时，可将table_open_cache继续上调至4096即可。

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