MySQL优化生产实践-MySQL 优化器负优化产生超慢查询(一)，优化后性能提升 2347倍

前天正在coding的时候，运维发来一个紧急消息，生产发现超慢SQL ，随后运维就把整个SQL发过来了。

简化后的SQL如下

explain  
SELECT a.billid,a.billcode,a.billdate,a.status,a.paystatus,a.billtype,a.taxedmoney,a.jtotal,a.jremain  
FROM erp\_bill\_index a  
INNER JOIN erp\_bill\_sale s ON a.billid=s.billid AND a.profileid=s.profileid  
left join erp\_bill\_relation as br on br.profileid=a.profileid and br.frombillid=a.billid and br.billtype=601  
WHERE a.did = '17438984' and a.billtype=604 AND a.status>10 AND a.billsubtype =0  
AND a.profileid=200003507 and a.billdate >= '2025-04-28' and a.billdate < '2025-07-29' and true  
GROUP BY a.billid  
ORDER BY a.billid DESC  
limit 0,5

执行计划如下：

a表billid是主键， where条件的字段上也有索引。 但通过执行计划看到，MySQL未选择过滤字段的索引。走的是order by 排序的索引，所以type是index即索引扫描， 而从extra看是消除了排序

该SQL 在生产环境执行长达110秒，但返回结果只有3行。证明where 条件的过滤性还是很好的，但为什么不走where条件的索引呢。

很久前比较详细的阅读过MySQL官方手册的Optimization部份，记得MySQL当在limit的情况下 优化器会在默认情况下尝试选择有序索引 ，且还有开关或以控制，找到官方手册中的对应部份:LIMIT Query Optimization,利用hint强制关闭MySQL优化器的优化。

关闭优化器后的SQL如下：

explain
SELECT 
/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ 
    a.billid,a.billcode,a.billdate,a.status,a.paystatus,a.billtype,a.taxedmoney,a.jtotal,a.jremain
FROM erp_bill_index a 
INNER JOIN erp_bill_sale s ON a.billid=s.billid AND a.profileid=s.profileid
left join erp_bill_relation as br on br.profileid=a.profileid and br.frombillid=a.billid and br.billtype=601
WHERE a.did = '17438984' and a.billtype=604 AND a.status>10  AND a.billsubtype =0   
AND a.profileid=200003507 and a.billdate >= '2025-04-28' and a.billdate < '2025-07-29' and  true
GROUP BY a.billid

其中利用hint /*+ SET_VAR(optimizer_switch=‘prefer_ordering_index=off’) */ 关闭了MySQL优化器的优化

关闭后执行此语句只需在0.047s

下图为我在生产环境执行的时间差异 从110.344秒 变成了 0.047秒

我在生产环境中遇到过很多优化器好心干坏事儿的情况。根据实际情况干预优化器的选择，会得到一个很好的结果。