将大于或小于的范围查询装换为等值查询
在生产环境,经常会遇到需要对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;
总结:
优化就是使用合理方式降低扫描的数据量和排序的数据量。