• 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;

    总结:

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

  • 相关阅读:
    VueRouter-404错误配置
    VueRouter-组件复用
    VueRouter-动态路由
    vue-router基本使用
    Vue小作业--图书管理
    Vue的过滤器
    vue的生命周期
    vue中插槽
    vue自定义组件v-model
    Vue组件中自定义事件
  • 原文地址:https://www.cnblogs.com/gaogao67/p/11419814.html
Copyright © 2020-2023  润新知