MySQL 8+及衍生版本查询计划失效与索引未使用问题求助

数据老陈

2026-6-12

Drupal 7站点数据库迁移后性能骤降的配置优化方案

我们有一个仅内部使用的遗留Drupal 7站点，替代系统正在开发中，当前使用兼容MySQL 5.7的Aurora2，已进入成本高昂的扩展支持阶段。尝试迁移数据到Aurora3、MySQL 8.4、MySQL 9.3及MariaDB 11.4时，出现灾难性性能下降，部分查询耗时是原版本的约40000倍，且优化器未使用可用索引。

示例查询（最近100条编辑记录）

SELECT
  n.nid,
  n.type,
  n.title,
  u.name,
  u.uid,
  n.changed AS date
FROM node n
JOIN node_revision nr ON n.vid = nr.vid
JOIN users u ON nr.uid = u.uid
ORDER BY n.changed DESC
LIMIT 100;

各版本执行耗时对比

MySQL 5.7：100 rows in set (0.002 sec)
MySQL 9.3.0：100 rows in set (1 min 37.906 sec)
11.4.5-MariaDB：100 rows in set (1 min 23.105 sec)
8.0.mysql_aurora.3.09.0：100 rows in set (1 min 52.591 sec)

执行计划对比

MySQL 5.7执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	n	NULL	index	vid	node_changed	4	NULL	100	100.00	Using where
1	SIMPLE	nr	NULL	eq_ref	PRIMARY,uid	PRIMARY	4	db_id.n.vid	1	100.00	NULL
1	SIMPLE	u	NULL	eq_ref	PRIMARY	PRIMARY	4	db_id.nr.uid	1	100.00	Using where

新版本（MySQL 9.3.0/11.4.5-MariaDB/Aurora3）执行计划

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	u	index	PRIMARY	name	182	NULL	74	Using index; Using temporary; Using filesort
1	SIMPLE	nr	ref	PRIMARY,uid	uid	4	db_id.u.uid	5636	Using where; Using index
1	SIMPLE	n	eq_ref	vid	vid	5	db_id.nr.vid	1

强制使用索引后的性能改善

添加FORCE INDEX (node_changed)后的查询：

SELECT
  n.nid,
  n.type,
  n.title,
  u.name,
  u.uid,
  n.changed AS date
FROM node n
  FORCE INDEX (node_changed)
JOIN node_revision nr ON n.vid = nr.vid
JOIN users u ON nr.uid = u.uid
ORDER BY n.changed DESC
LIMIT 100;

强制索引后执行耗时

MySQL 5.7：100 rows in set (0.002 sec)
MySQL 9.3.0：100 rows in set (0.022 sec)
11.4.5-MariaDB：100 rows in set (0.019 sec)
8.0.mysql_aurora.3.09.0：100 rows in set (0.010 sec)

上下文信息

node表：5,343,344行
node_revision表：25,558,491行
users表：74行
迁移方式：Aurora2转Aurora3用AWS蓝绿部署，其他版本用MySQL备份恢复

已尝试的优化措施

设置optimizer_switch = prefer_ordering_index=on,derived_merge=off
设置innodb_stats_persistent_sample_pages=1000
将相关表的utf8mb3转换为utf8mb4
对相关表执行ANALYZE
重建node.changed字段的索引

无法进行的操作

重写查询语句（CMS系统，性能下降是普遍问题）

若无法解决，将继续使用扩展支持的Aurora2直至替代系统完成，现寻求数据库层面的配置优化方案。

表结构信息

CREATE TABLE `node` (
  `nid` int unsigned NOT NULL AUTO_INCREMENT COMMENT '节点的唯一标识符。',
  `vid` int unsigned DEFAULT NULL COMMENT '当前node_revision的版本标识符。',
  `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `language` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uid` int NOT NULL DEFAULT '0' COMMENT '拥有该节点的users.uid；初始为创建节点的用户。',
  `status` int NOT NULL DEFAULT '1' COMMENT '布尔值，表示节点是否发布（对非管理员可见）。',
  `created` int NOT NULL DEFAULT '0' COMMENT '节点创建时的Unix时间戳。',
  `changed` int NOT NULL DEFAULT '0' COMMENT '节点最近一次保存时的Unix时间戳。',
  `comment` int NOT NULL DEFAULT '0' COMMENT '节点是否允许评论：0=不允许，1=关闭（只读），2=开放（读写）。',
  `promote` int NOT NULL DEFAULT '0' COMMENT '布尔值，表示节点是否应显示在首页。',
  `sticky` int NOT NULL DEFAULT '0' COMMENT '布尔值，表示节点是否应显示在列表顶部。',
  `tnid` int unsigned NOT NULL DEFAULT '0' COMMENT '该节点的翻译集ID，等于每个集中源帖子的节点ID。',
  `translate` int NOT NULL DEFAULT '0' COMMENT '布尔值，表示该翻译页面是否需要更新。',
  PRIMARY KEY (`nid`),
  UNIQUE KEY `vid` (`vid`),
  KEY `node_changed` (`changed`),
  KEY `node_created` (`created`),
  KEY `node_frontpage` (`promote`,`status`,`sticky`,`created`),
  KEY `node_status_type` (`status`,`type`,`nid`),
  KEY `node_title_type` (`title`,`type`(4)),
  KEY `node_type` (`type`(4)),
  KEY `uid` (`uid`),
  KEY `tnid` (`tnid`),
  KEY `translate` (`translate`),
  KEY `language` (`language`),
  KEY `idx_node_created` (`created`),
  KEY `idx_node_created_desc` (`created` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=5746465 DEFAULT CHARSET=utf8mb3 COMMENT='节点的基础表。'

CREATE TABLE `node_revision` (
  `nid` int unsigned NOT NULL DEFAULT '0' COMMENT '此版本所属的节点。',
  `vid` int unsigned NOT NULL AUTO_INCREMENT COMMENT '此版本的唯一标识符。',
  `uid` int NOT NULL DEFAULT '0' COMMENT '创建此版本的users.uid。',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `log` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `timestamp` int NOT NULL DEFAULT '0' COMMENT '创建此版本时的Unix时间戳。',
  `status` int NOT NULL DEFAULT '1' COMMENT '布尔值，表示此版本节点是否发布（对非管理员可见）。',
  `comment` int NOT NULL DEFAULT '0' COMMENT '此版本节点是否允许评论：0=不允许，1=关闭（只读），2=开放（读写）。',
  `promote` int NOT NULL DEFAULT '0' COMMENT '布尔值，表示此版本节点是否应显示在首页。',
  `sticky` int NOT NULL DEFAULT '0' COMMENT '布尔值，表示此版本节点是否应显示在列表顶部。',
  PRIMARY KEY (`vid`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=43684354 DEFAULT CHARSET=utf8mb3 COMMENT='存储节点每个已保存版本的信息。'

Explain Analyze结果（新版本）

-> Limit: 100行 (实际耗时=94688..94689 行数=100 循环次数=1)
-> 排序: n.changed DESC，限制每个块输入为100行 (实际耗时=94688..94689 行数=100 循环次数=1)
-> 流式返回结果 (成本=206946 行数=456804) (实际耗时=7476..93266 行数=5340000 循环次数=1)
-> 嵌套循环内连接 (成本=206946 行数=456804) (实际耗时=7476..90610 行数=5340000 循环次数=1)
-> 嵌套循环内连接 (成本=45982 行数=456804) (实际耗时=2.16..17600 行数=25600000 循环次数=1)
-> 使用name索引对u进行覆盖索引扫描 (成本=7.65 行数=74) (实际耗时=0.0427..0.159 行数=74 循环次数=1)
-> 过滤条件: (nr.uid = u.uid) (成本=12.3 行数=6173) (实际耗时=0.105..212 行数=345385 循环次数=74)
-> 使用uid索引查找nr的覆盖索引 (uid=u.uid) (成本=12.3 行数=6173) (实际耗时=0.104..177 行数=345385 循环次数=74)
-> 使用vid索引对n进行单行查找 (vid=nr.vid) (成本=0.252 行数=1) (实际耗时=0.00272..0.00273 行数=0.209 循环次数=25600000)
1 row in set (1 min 34.705 sec)

数据库配置优化方案

1. 调整优化器成本模型与搜索深度

让优化器更倾向于选择能避免排序的索引：

optimizer_cost_model=io
optimizer_search_depth=6

optimizer_cost_model=io：侧重IO成本计算，避免因CPU成本估算偏差导致的执行计划退化。
optimizer_search_depth=6：限制优化器搜索深度，减少复杂计划的误判概率。

2. 优化统计信息准确性

确保优化器基于真实数据选择执行计划：

innodb_stats_auto_recalc=ON
innodb_stats_transient_sample_pages=1000

定期执行ANALYZE TABLE node, node_revision, users;，确保统计信息实时更新。

3. 禁用低效优化器特性

避免新版本优化器的特性导致执行计划退化：

optimizer_switch="block_nested_loop=off,batched_key_access=off"

4. 调整内存缓冲区参数

减少排序与随机读的磁盘交互：

sort_buffer_size=2M
read_rnd_buffer_size=2M

5. 创建覆盖索引

减少回表操作，提升查询效率：

CREATE INDEX idx_node_changed_covering ON node (changed DESC, vid, nid, type, title);

6. Aurora3专属优化

启用aurora_lazy_load_joins=ON：延迟加载连接，减少不必要的数据加载。
调整aurora_max_parallel_scans=4：根据实例规格设置并行扫描数，提升大表扫描效率。

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

MySQL 8+及衍生版本查询计划失效与索引未使用问题求助

示例查询（最近100条编辑记录） #

各版本执行耗时对比 #

执行计划对比 #

MySQL 5.7执行计划 #

新版本（MySQL 9.3.0/11.4.5-MariaDB/Aurora3）执行计划 #

强制使用索引后的性能改善 #

强制索引后执行耗时 #

上下文信息 #

已尝试的优化措施 #

无法进行的操作 #

表结构信息 #

Explain Analyze结果（新版本） #

数据库配置优化方案 #

1. 调整优化器成本模型与搜索深度 #

2. 优化统计信息准确性 #

3. 禁用低效优化器特性 #

4. 调整内存缓冲区参数 #

5. 创建覆盖索引 #

6. Aurora3专属优化 #