MySQL优化实战之优化器罢工

1、发现问题

昨天跟踪线上慢SQL,发现有一条SQL始终走全表扫描，造成查询非常慢，查询时间达到了10分钟。

2、问题复现

SQL语句非常长。估计有上百行。这里就不贴原语句了，通过分析，找到了根本原因，我这里利用MySQL demo库sakila来重现

有如下语句

explain 
SELECT  
COUNT(*) FROM PAYMENT 
where staff_id in(1,2)
and rental_id in (41,42,43,44)
and customer_id in(39,40,41,42,43,44,45,46);
复制

查看执行计划。走的是全表扫描。

查看该表结构

show create table PAYMENT;
复制

我where 条件的三个字段都有索引 ，为什么该语句会走全表扫描呢？

当时我在生产环境是百思不得其解。找了好久好久原因。这里就直接的揭晓原因

explain 后去执行show warining;

show warnings;
复制

可以看到，除了改写的语句，还有一条警告，正是由于这个警告是造成全表扫描的根本原因。

生产环境是由于SQL太长，已超过了range_optimizer_max_mem_size默认值，在这里为了好重现。我改变了range_optimizer_max_mem_size的值

SET @@SESSION.range_optimizer_max_mem_size = 500;
复制

用SET_VAR Hint其实更好观察这个问题

explain 
SELECT  
 /*+ 
 SET_VAR(range_optimizer_max_mem_size = 500) 
*/
COUNT(*) FROM PAYMENT 
where staff_id in(1,2)
and rental_id in (41,42,43,44)
and customer_id in(39,40,41,42,43,44,45,46);
复制

当range_optimizer_max_mem_size = 500 时全表扫描

explain 
SELECT  
 /*+ 
 SET_VAR(range_optimizer_max_mem_size = 5000) 
*/
COUNT(*) FROM PAYMENT 
where staff_id in(1,2)
and rental_id in (41,42,43,44)
and customer_id in(39,40,41,42,43,44,45,46);
复制

当range_optimizer_max_mem_size = 5000 时 正常走了索引

这里我发现一个官方文档的错误。官方文档指出 range_optimizer_max_mem_size 不支持 SET_VAR，但上面可以看出我实际操作是可以用SET_VAR的。

这里官方文档明显错了。各位大佬可自行尝试。我测试的版本为 8.0.32

具体range_optimizer_max_mem_size 变量相关与范围优化说明可参见文末官方文档链接。

参考：
mysql系统变量
mysql范围优化