将数据库从MySQL升级到MariaDB后WordPress查询异常缓慢

问题背景 #

通过CPanel elevate将CentOS 7升级到AlmaLinux，同时完成MySQL到MariaDB 10.x的迁移。升级后数据库查询耗时增至原来的10倍，无索引的WHERE条件查询受影响最严重：升级前每日慢查询日志仅10-15条，当前已累积500+条。

典型示例查询（原耗时8秒，现需80秒）：

SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '_billing_phone' AND `meta_value` LIKE '9999999999';

数据库总大小约30GB，其中wp_postmeta表占10GB，升级前后数据量无变化。已尝试执行以下命令但未解决问题：

ANALYZE TABLE wp_postmeta PERSISTENT FOR ALL;
ANALYZE TABLE wp_postmeta;

排查与解决步骤 #

1. 检查MariaDB优化器配置差异 #

MySQL与MariaDB的查询优化器默认策略存在差异，无索引全表扫描场景下可能出现性能退化：

• 查看优化器开关：

  SHOW VARIABLES LIKE 'optimizer_switch';
  

  重点关注mrr=on、batched_key_access=on等选项，这类优化在无索引场景下可能反向拖慢性能，可临时关闭测试：

  SET SESSION optimizer_switch='mrr=off,batched_key_access=off';
  

• 调整缓冲区配置：全表扫描依赖join_buffer_size、sort_buffer_size，升级后默认值可能不匹配当前数据量，可适当调大（根据服务器内存调整，示例为1M）：

  SET SESSION join_buffer_size = 1048576;
  SET SESSION sort_buffer_size = 1048576;
  

2. 验证表存储引擎与统计信息 #

• 确认存储引擎：升级后存储引擎可能变更（如MyISAM与InnoDB互转），不同引擎全表扫描性能差异显著。执行以下命令查看：

  SHOW CREATE TABLE wp_postmeta;
  

  若为InnoDB，检查innodb_buffer_pool_size是否充足（建议设置为服务器可用内存的50%-70%）。
• 重建表统计信息：除ANALYZE TABLE外，可尝试低峰期执行：

  OPTIMIZE TABLE wp_postmeta;
  

  或使用MariaDB专属命令更新统计：

  UPDATE STATISTICS wp_postmeta;
  

3. wp_postmeta表专项优化 #

WordPress的wp_postmeta表结构特性导致无索引查询天生低效，升级后问题被放大：

• 创建联合索引：若查询为精确匹配（示例中LIKE无通配符），将LIKE改为=，并创建联合索引：

  CREATE INDEX idx_meta_key_value ON wp_postmeta(meta_key, meta_value(255));
  

  注：meta_value为长文本时需合理设置索引长度，避免占用过多空间。
• 全文索引替代LIKE：若必须使用带通配符的LIKE，可创建全文索引优化查询：

  ALTER TABLE wp_postmeta ADD FULLTEXT INDEX ft_meta_value(meta_value);
  

  查询语句调整为：

  SELECT * FROM wp_postmeta WHERE meta_key = '_billing_phone' AND MATCH(meta_value) AGAINST('9999999999' IN BOOLEAN MODE);
  

4. 系统层面性能排查 #

升级AlmaLinux后，磁盘IO、内存配置可能影响数据库性能：

• 检查磁盘IO：执行iostat -x 1，若%util接近100%，说明磁盘IO饱和，可切换至SSD或调整磁盘调度算法为noop/deadline。
• 检查内存状态：执行free -h，若内存不足导致频繁swap，需增加服务器内存或调整innodb_buffer_pool_size减少内存占用。

5. 对比查询执行计划 #

用EXPLAIN查看升级前后执行计划差异，定位预估错误或额外开销：

EXPLAIN SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '_billing_phone' AND `meta_value` LIKE '9999999999';

重点关注type（全表扫描应为ALL）、rows（预估扫描行数是否准确）、Extra（是否存在Using filesort/Using temporary等额外开销）。若统计信息预估不准，可执行FLUSH TABLES;后重新运行ANALYZE TABLE。

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