某业务模块查询性能下降，SQL执行时间从100ms到5秒，请分析可能的原因并给出优化方案。

1) 【一句话结论】：查询性能下降的核心原因是SQL执行计划从高效路径（如索引扫描）切换为低效路径（全表扫描），且伴随资源竞争（锁、CPU、IO瓶颈），需通过分析执行计划、优化索引及资源隔离解决。

2) 【原理/概念讲解】：数据库执行查询时，会根据统计信息（表行数、列分布）生成执行计划（如全表扫描、索引扫描）。全表扫描会扫描整张表，导致IO和CPU消耗激增；索引失效（如查询条件未匹配索引列、统计信息不准）会导致数据库误判，选择全表扫描。资源竞争包括高并发下的锁等待、CPU瓶颈（多线程竞争CPU）、IO瓶颈（磁盘读写慢），这些都会导致查询延迟。类比：执行计划就像路线规划，全表扫描是走“绕路绕到表边再逐行看”，而索引扫描是走“直接查索引找到目标位置再回表”，绕路导致时间变长。

3) 【对比与适用场景】：

对比项	全表扫描	索引扫描
定义	扫描表所有行	仅扫描索引，再回表
特性	IO高（尤其大表），CPU低（无索引维护）	IO低（索引小），CPU高（回表）
适用场景	小表，无索引，统计信息不准	大表，有合适索引，查询条件匹配索引列
注意点	大表全表扫描延迟高	索引选择不当（如覆盖索引不足）可能无效

4) 【示例】：假设表user（id INT, name VARCHAR(50), age INT），查询语句为SELECT * FROM user WHERE age > 20。原优化状态：有索引idx_age（age列），执行计划为索引扫描（Using index）。性能下降后：统计信息更新后，数据库误判表行数为100万（实际1000万），选择全表扫描，执行时间从100ms到5秒。伪代码（MySQL）：

• 原EXPLAIN：EXPLAIN SELECT * FROM user WHERE age > 20; 结果：Type: range, Key: idx_age, Rows: 1000, Extra: Using where
• 下降后：EXPLAIN SELECT * FROM user WHERE age > 20; 结果：Type: ALL, Rows: 1000000, Extra: Using where（全表扫描）

5) 【面试口播版答案】：面试官您好，查询性能从100ms到5秒，核心原因是SQL执行计划从高效路径（如索引扫描）切换为低效路径（全表扫描），且伴随资源竞争（锁、CPU、IO）。具体分析：首先，用EXPLAIN查看执行计划，发现从“Using index”变为“Full Scan”，说明数据库不再使用索引，转而扫描整张表。原因可能包括：1. 索引失效：比如表数据量增长，统计信息未更新，导致数据库误判表大小；2. 查询条件未匹配索引列（如索引是idx_age，但查询是age > 20 + 其他列，覆盖索引不足）；3. 资源竞争：高并发下多个事务同时更新表，导致锁等待，查询阻塞。优化方案：1. 检查并更新统计信息（如ANALYZE TABLE user），确保数据库能准确评估表大小；2. 优化索引，比如添加覆盖索引（包含所有查询列），或调整索引列顺序；3. 资源隔离，如增加数据库连接池，优化锁策略（如行锁改为乐观锁），或分库分表减少单表压力。总结，需先通过EXPLAIN定位执行计划变化，再针对性优化索引和资源，解决性能问题。

6) 【追问清单】：

• 问：如何用EXPLAIN分析执行计划？答：执行EXPLAIN SELECT ...，查看Type（如ALL表示全表扫描）、Rows（预估行数）、Extra（是否使用索引）等字段，对比实际执行时间。
• 问：如果索引存在但查询仍慢，可能是什么问题？答：统计信息不准（表行数、列分布更新不及时），或索引列上有函数（如DATE_FORMAT(name)），导致索引失效。
• 问：资源竞争方面，如何排查锁等待？答：用SHOW PROCESSLIST或查询INNODB_LOCKS，查看锁等待时间、锁类型（行锁/表锁），分析阻塞原因。
• 问：优化索引后，如何验证效果？答：再次执行EXPLAIN，对比Rows与实际行数，或测试查询时间，确认是否从全表扫描变为索引扫描。
• 问：表数据量太大，分库分表是否可行？答：分库分表可减少单表数据量，但需考虑数据一致性（如分片键选择），适合超大规模数据，需评估业务场景。

7) 【常见坑/雷区】：

• 忽略执行计划，直接优化索引，导致索引无效（如统计信息未更新）。
• 未考虑资源竞争，只优化SQL，高并发下仍慢。
• 索引选择不当，如覆盖索引未包含所有查询列，导致回表次数多，增加IO。
• 忽略表数据量增长，认为索引足够，实际全表扫描成本过高。
• 未验证优化效果，优化后未测试性能，导致问题未解决。