MySQL查询本地快、预发布环境慢且性能波动问题求助

问题概述 #

一条MySQL查询在本地执行仅需0.02秒，但在预发布环境执行耗时大幅增加，且执行时间随机波动（4秒~10+秒）。该查询通过Python+SQLAlchemy循环针对不同users_id执行，直接用MySQL客户端执行则速度正常。

示例查询 #

SELECT 
    table1.qid,
    table1.original_text,
    table1.resolved_text,
    table1.resolved_values
FROM table_1
INNER JOIN table_2
    ON table_1.id = table_2.variation_id
WHERE table_2.users_id = 6
    AND table_1.qid = 201;

表结构 #

CREATE TABLE table_1 (
    id int NOT NULL AUTO_INCREMENT, 
    qid int NOT NULL, 
    original_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
    resolved_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, 
    resolved_values json DEFAULT NULL, 
    PRIMARY KEY (id), 
    KEY qid_index (qid), 
    KEY original_text_index (original_text(256)),
    KEY resolved_text_index (resolved_text(256))
) ENGINE=InnoDB AUTO_INCREMENT=4644665
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE table_2 (
    id int NOT NULL AUTO_INCREMENT, 
    users_id int NOT NULL, 
    variation_id int NOT NULL, 
    PRIMARY KEY (id), 
    KEY uid_index (users_id), 
    KEY var_index (variation_id)
) ENGINE=InnoDB AUTO_INCREMENT=437879832
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

背景与执行计划 #

• 需循环针对不同users_id执行上述查询
• 预发布环境查询执行时间随机波动
• EXPLAIN显示使用了预期索引：

1   SIMPLE  table_1     NULL  ref  PRIMARY,qid_index  qid_index  4  const           1    100.00  NULL
1   SIMPLE  table_2     NULL  ref  uid_index,var_index  var_index  4  table_1.id  133  0.04  Using where

关键观察 #

• 本地环境性能正常，预发布环境变慢
• 执行阶段消耗大部分时间
• 去掉qid过滤的变体查询在预发布环境执行很快

核心疑问 #

1. 预发布环境中查询性能不稳定的原因是什么？
2. 同一查询执行时间差异巨大的原因是什么？
3. 添加qid过滤为何会导致性能大幅下降？
4. 哪些特定的服务器配置、MySQL设置或环境因素会影响该行为？

排查与解决思路 #

1. 验证执行计划选择是否合理 #

优化器可能因统计信息过时或数据分布差异，选错执行顺序。强制指定执行计划测试：

SELECT 
    table1.qid,
    table1.original_text,
    table1.resolved_text,
    table1.resolved_values
FROM table_2
STRAIGHT_JOIN table_1
    ON table_1.id = table_2.variation_id
WHERE table_2.users_id = 6
    AND table_1.qid = 201;

若此版本性能稳定且快速，说明优化器默认选择的执行顺序（先扫table_1）效率低下。

2. 更新表统计信息 #

预发布环境数据量与本地差异大，统计信息可能过时，导致优化器预估错误：

ANALYZE TABLE table_1, table_2;

3. 检查实际执行细节（MySQL 8.0+） #

用EXPLAIN ANALYZE查看实际扫描行数、耗时分布，对比预期与实际的差异：

EXPLAIN ANALYZE
SELECT 
    table1.qid,
    table1.original_text,
    table1.resolved_text,
    table1.resolved_values
FROM table_1
INNER JOIN table_2
    ON table_1.id = table_2.variation_id
WHERE table_2.users_id = 6
    AND table_1.qid = 201;

4. 优化索引设计 #

• 给table_2创建联合索引(users_id, variation_id)，覆盖WHERE过滤与JOIN关联，避免回表：

  CREATE INDEX uid_var_index ON table_2(users_id, variation_id);
  

• 给table_1创建联合索引(qid, id)，覆盖qid过滤与JOIN关联需求：

  CREATE INDEX qid_id_index ON table_1(qid, id);
  

5. 排查缓存与IO问题 #

• 查看InnoDB缓冲池配置与命中率：

  SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  SHOW ENGINE INNODB STATUS; -- 查看Buffer pool hit rate，需保持在99%以上
  

  若缓冲池过小，需调大配置以缓存更多常用数据，减少磁盘IO。

6. 排查事务与锁等待 #

执行查询时，检查是否存在锁等待：

SHOW PROCESSLIST;

若存在Waiting for table metadata lock或Waiting for row lock，说明有其他事务干扰。

7. 检查Python/SQLAlchemy执行方式 #

• 确认是否使用了连接池，避免每次查询重建连接
• 对比原生SQL与ORM执行的性能差异，排查结果集转换等额外开销
• 检查参数绑定是否正确，避免因SQL拼接导致的性能问题

疑问解答 #

1. 预发布环境性能不稳定：主要因缓存命中率波动、优化器执行计划摇摆、潜在锁等待导致。
2. 同一查询耗时差异大：缓存命中时执行快，未命中时需读磁盘；优化器根据系统负载、统计数据动态调整执行计划；偶尔的锁等待会拉长耗时。
3. 添加qid过滤变慢：优化器可能选择先扫table_1的qid_index，再关联table_2，但table_2中对应variation_id的users_id=6匹配行数远多于预期，导致嵌套循环次数暴增；或统计信息过时，优化器对行数预估错误。
4. 影响因素：InnoDB缓冲池大小、表统计信息准确性、optimizer_switch优化策略、事务隔离级别、磁盘IO性能、连接池配置、ORM执行逻辑。

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