MySQL 5.5查询优化求助：控制台执行快API调用慢且报内部服务器错误

这问题我之前处理过类似的，咱们从查询本身、索引策略和API环境三个维度来拆解解决：

一、先搞清楚控制台和API差异的可能原因 #

• 执行环境不同：控制台大概率是直接连接数据库服务器（或同机房），而API可能通过远程网络调用，加上API服务本身的连接池、超时配置限制，一旦查询耗时超过API的超时阈值，就会触发内部服务器错误。
• 数据库负载差异：控制台执行时数据库可能处于低负载状态，而API调用往往是并发请求，数据库资源被占满后，单个查询的响应时间会被拉长。
• 会话配置差异：API的数据库连接会话可能有不同的sql_mode、缓存配置，导致优化器选择不同的执行计划。

二、查询语句与索引的核心问题 #

你的查询在日期范围缩小到2个月时索引生效，12个月时失效，本质是MySQL 5.5的优化器判断：当查询覆盖的数据量超过表的一定比例（通常20%-30%），全表扫描的成本比走索引+回表更低，所以放弃了索引。

针对性优化步骤： #

1. 优化日期条件的写法
   原语句中DATE_FORMAT(NOW()- INTERVAL 12 MONTH, '%y-%m-01')的%y是两位年份，虽然当前没问题，但用%Y（四位年份）更稳妥，同时可以改成更清晰的常量计算方式，避免优化器误判：

   SELECT DISTINCT cus_id, set_id 
   FROM orders 
   WHERE ord_dttm BETWEEN STR_TO_DATE(CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01'), '%Y-%m-%d') 
                     AND NOW() 
     AND ord_status <> 'Cancelled';
   

   （注：MySQL中DISTINCTROW和DISTINCT功能完全一致，用DISTINCT更符合常规写法）

2. 创建覆盖索引
   之前的索引无效，大概率是只建了单字段索引（比如ord_dttm），查询需要回表取cus_id、set_id和ord_status，数据量一大回表开销就爆炸。直接创建覆盖索引，让索引包含查询所需的所有字段，无需回表：

   CREATE INDEX idx_orders_dttm_status_cus_set ON orders (ord_dttm, ord_status, cus_id, set_id);
   

   这个索引的顺序很重要：先按过滤条件ord_dttm排序，再用ord_status过滤，最后包含需要返回的cus_id和set_id，查询时直接扫描索引就能得到结果。

3. 更新数据库统计信息
   MySQL优化器依赖表的统计信息选择执行计划，如果统计信息过时，会导致错误判断。执行以下命令更新：

   ANALYZE TABLE orders;
   

4. 强制索引（备选方案）
   如果优化器还是固执地选择全表扫描，可以用FORCE INDEX强制走我们创建的覆盖索引，但这是兜底方案，优先通过覆盖索引和统计信息让优化器自动选择：

   SELECT DISTINCT cus_id, set_id 
   FROM orders FORCE INDEX(idx_orders_dttm_status_cus_set)
   WHERE ord_dttm BETWEEN STR_TO_DATE(CONCAT(YEAR(NOW())-1, '-', MONTH(NOW()), '-01'), '%Y-%m-%d') 
                     AND NOW() 
     AND ord_status <> 'Cancelled';
   

5. 大表可选：分区表优化
   如果orders表数据量极大（千万级以上），可以按ord_dttm做按月分区，这样查询12个月的数据时，只会扫描对应的12个分区，大幅减少扫描的数据量：

   ALTER TABLE orders 
   PARTITION BY RANGE (TO_DAYS(ord_dttm)) (
       PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
       PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
       -- 依次添加近12个月的分区
       PARTITION p202409 VALUES LESS THAN (TO_DAYS('2024-10-01'))
   );
   

三、API端的排查点 #

• 检查超时配置：API服务的数据库连接超时、接口响应超时时间是否设置过短，比如设置成了60秒，刚好触发超时错误，适当调大超时阈值（同时配合查询优化）。
• 监控数据库负载：API调用时，查看数据库的CPU、内存、IO使用率，是否存在资源瓶颈，比如连接数满了、磁盘IO过高。

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