MySQL Execution Plan--将范围扫描转换为等值查询

将大于或小于的范围查询装换为等值查询

在生产环境，经常会遇到需要对Worker表进行多次尝试的业务，超过一定重试次数后抛弃或使用其他方式处理，在查找满足重试条件数据时，通常会使用“小于”运算符并伴随排序操作，这种场景很容易出现性能问题。

如下面查找执行次数小于最大执行次数的记录的SQL：

SELECT *
FROM worker_task
WHERE status = 3 
AND execute_times < max_execute_times
LIMIT 50;

表中数据分布为：

SELECT COUNT(1),status 
FROM worker_task 
GROUP BY status;

+----------+--------+
| COUNT(1) | status |
+----------+--------+
|        2 |      0 |
|        1 |      1 |
| 10597565 |      2 |
|    66836 |      3 |
+----------+--------+

问题分析：

上面满足status = 3条件的数据有6.6万条，由于execute_times < max_execute_times需要扫描6.6万条记录中的每一行来确定是否满足条件，因此查询需要读取全部数据并对比，严重消耗服务器IO和CPU资源，查询性能极差，且该数据量会随公司大促暴涨，很可能导致单条SQL需要扫描和对几千万上亿的数据。

优化方式：

由于每次worker尝试后，会更新execute_times的值，这种场景下，可以在表中新增一个字段表标识该记录是否需要再次重试is_over_time,并在表中增加索引idx_ status_is_over_time(status,is_over_time),查询能够通过等值查询快速定位到满足条件数据，将范围查询改为等值查找。

 

将IN语句的范围查询改为等值查询

查询满足条件并按照优先级和最早更新时间取前50行记录：

SELECT *
FROM t_task
WHERE status in(1,3,9)
ORDER BY priority,update_time
LIMIT 50;

问题分析：

查询需要对满足WHERE条件status in(1,3,9)的数据按照priority+update_time两个字段排序取TOP50，如果满足WHERE条件的数据量较大，那么读取这些数据并排序会消耗服务IO和CPU资源，导致性能问题。

优化方式1：

由于WHERE条件status in(1,3,9)需要分别扫描status=1 和status=3以及status=9的数据，如果能将该条件进行业务拆分，仅查询其中一个状态的数据再排序，那么可以通过符合索引（status，priority，pdate_time）来避免对大数据量数据排序，并通过LIMIT减少扫描数据量，降低IO和CPU资源。

 

优化后的SQL为：

SELECT *
FROM t_task
WHERE status = 1
ORDER BY priority,update_time
LIMIT 50;

 

优化方式2：

在促销期间，任务产生速度远高于任务处理速度，造成任务表大量数据积压，而优先级较高的任务需要优先处理，但优先级较高的任务的数据量较少，因此可以在应用程序端对数据进行分级处理，优先处理优先级高且更新时间早的任务，保证这些任务优先处理的情况下，对普通任务进行随机处理(注意避免任务被连续多次重试)。

查找优先级高的任务(使用排序)：

SELECT *
FROM t_task
WHERE status in(1,3,9) and priority = 9
ORDER BY update_time
LIMIT 50;

查看优先级地的任务(不使用排序)：

SELECT *
FROM t_task
WHERE status in(1,3,9) and priority <> 9
LIMIT 50;

 

总结：

优化就是使用合理方式降低扫描的数据量和排序的数据量。