专栏首页A周立SpringCloud线上百万级数据查询接口优化过程

线上百万级数据查询接口优化过程

点击上方 IT牧场 ,选择 置顶或者星标技术干货每日送达!>>>技术讨论群<<<

最近遇到一个数据查询接口性能低下的问题,需要进行优化,从解决方案的调研与梳理到方案的确定,再到最终方案的执行落地,我将优化的过程完整的记录了下来,与大家分享学习,希望能给大家有所帮助和启发。

PS:以下我所描述的所有表和字段都是虚拟的。

问题产生

我们有很多上报的数据,数据量比较大。这些数据保存在 report_info 表中的,表结构如下所示:

create table report_info (    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',    `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    `report_uuid` varchar(256) NOT NULL COMMENT '上报信息唯一id',    `other_fields` varchar(256) NOT NULL COMMENT '其他字段',    PRIMARY KEY (`id`),      KEY `idx_report_uuid` (`report_uuid`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='上报的信息';

上面的结构中我用 other_fields 来统一表示其他业务字段。

上报的数据,我们需要在页面上进行查询,所以我们对 report_info 表有一个简单的查询,有若干个查询条件。

查询语句很简单,一个单表查询即可实现,对查询条件中的字段根据实际情况增加一些索引进行优化,6百万的数据量分页查询的时延大概在 1s 左右,基本上可以接受。

随着业务的发展,我们需要对上报的数据进行处理,例如进行 process1 和 process2 的处理,并且需要将处理的结果保存起来,包括处理是成功还是失败,失败的原因。

所以我们又新建了两个关联表 report_handle1 和 report_handle2。

report_handle1 表结构如下所示:

create table report_handle1 (    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',    `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    `report_uuid` varchar(256) NOT NULL COMMENT '上报信息唯一id',    `is_success` tinyint(4) NOT NULL COMMENT '处理结果 0:失败 1:成功',    `fail_reason` varchar(256) NOT NULL COMMENT '失败原因',    `other_fields` varchar(256) NOT NULL COMMENT '其他字段',    PRIMARY KEY (`id`),      KEY `idx_report_uuid` (`report_uuid`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='流程1的处理结果';

report_handle2 的结构类似,都包含 is_success 和 fail_reason 字段,只是 other_fields 不同。

PS:这里只是讨论优化的过程,具体的表结构设计不作为本篇文章的讨论范围。

以下将 report_handle1 和 report_handle2 简称为 h1 和 h2。

现在我们需要将流程1和流程2的处理结果在页面上展示出来,那将原来的语句做一个修改,根据 report_uuid 与 h1 和 h2 进行 left join,将 h1 和 h2 表中的结果返回,如下所示:

select i.other_fields,h1.is_success as h1_success,h1.fail_reason as h1_fail_reason,h2.is_success as h2_success,h2.fail_reason as h2_fail_reasonfrom report_info ileft join report_handle1 h1 on h1.report_uuid=i.report_uuidleft join report_handle2 h2 on h2.report_uuid=i.report_uuidwhere <i.condition>order by i.id desc

目前这样也没有问题,查询的性能和原来的单表查询没有太大的变化。

随着业务的发展我们又需要查询流程1(或流程2)中执行成功(或失败)的记录,即页面上需要增加两个查询字段,分别对应 h1 和 h2 中 is_success 字段。

这下我们的查询语句就变成了这样:

select i.other_fields,h1.is_success as h1_success,h1.fail_reason as h1_fail_reason,h2.is_success as h2_success,h2.fail_reason as h2_fail_reasonfrom report_info ileft join report_handle1 h1 on h1.report_uuid=i.report_uuid and h1.is_success=#{h1Success}left join report_handle2 h2 on h2.report_uuid=i.report_uuid and h2.is_success=#{h2Success}where <i.condition>order by i.id desc

原来的查询语句虽然也对 h1 和 h2 表进行了关联查询,但是都会走索引,而且查询条件也都是针对 report_info 表,所以性能不会有太大的问题。

但是现在要将 h1 和 h2 中的 is_success 字段作为查询条件,那就相当于对三张表做了关联查询,然后再对三张表中的字段进行过滤,并且 h1 和 h2 中的 is_success 字段区分度很低,只有 0 和 1 两种值,所以加索引意义也不大。

上述的语句在线上执行超时,因为三张表的数据量都是百万级的,所以必须要重新设计查询方案。

优化方案

出现了问题,那就需要找优化的方案,通过自己思考和咨询其他小伙伴,一共收集到很多优化的方案,下面我列举一些:

一、冗余查询字段

我首先想到的就是在 report_info 表中冗余两个查询字段,分别对应 h1 和 h2 中的 is_success 字段,这样就将原来的关联查询转换成了单表查询,优点肯定是性能上的飞跃提升,缺点是要对现有的代码进行修改,两个流程处理完之后要更新 report_info 表中的冗余字段的值,但是更新不是太大,可以接受。

二、使用数据仓库

第二种方案是将原来的数据同步到数据仓库中,在数据仓库中做查询,不过这种方案涉及到的改动比较大,而且我也没有研究过数据仓库的玩法,存在一定的改造成本。

三、分库分表

第三种方案是对现有的库表设计进行拆分,但是目前的数据量还不至于要进行拆分,而且分库分表依据什么进行拆分还需要根据业务进行分析,拆分后又会引入新的问题,代码复杂度肯定会升高,虽然现在已经有很多分库分表的中间件,但是不到万不得已还是不要使用分库分表。

四、使用中间表

第四种方案是使用数据库同步机制将数据同步到一个中间表,然后直接查询该中间表。该方案显得很笨,但是

五、使用 es 或者 solr

第五种方案,将数据保存到 es 或者 solr 等搜索引擎中,把数据拍平,通过搜索引擎进行筛选项的查询,拿到结果后,再结合 mysql 查询出最终结果返回给前端页面。

通过分析各种方案的复杂情况,对现有系统的调整,以及引入的新框架或者服务等各个方面,最简单,对现有代码改动最小的就是第一种方案。

优化过程

确定了优化的方案后,我们就可以进行实际的改造了。

一、新增冗余字段

首先我们在 report_info 表中新加两个冗余字段,例如 h1_success 和 h2_success ,修改后的 report_info 表结构如下所示:

create table report_info (    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',    `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',    `report_uuid` varchar(256) NOT NULL COMMENT '上报信息唯一id',    `other_fields` varchar(256) NOT NULL COMMENT '其他字段',    `h1_success` tinyint(4) NOT NULL COMMENT '流程1处理结果 0:失败 1:成功',    `h2_success` tinyint(4) NOT NULL COMMENT '流程2处理结果 0:失败 1:成功',    PRIMARY KEY (`id`),      KEY `idx_report_uuid` (`report_uuid`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='上报的信息';

二、修改处理逻辑

接着我们需要将原来的处理逻辑进行修改,要再原来的流程1和流程2处理完之后,根据 report_uuid 去更新冗余字段的值。

三、修改查询语句

最后我们只需要将我们原来的关联查询的语句修改为单表查询即可,如下所示:

select i.other_fields,i.h1_success,h1.fail_reason as h1_fail_reason,i.h2_success,h2.fail_reason as h2_fail_reasonfrom report_info ileft join report_handle1 h1 on h1.report_uuid=i.report_uuidleft join report_handle2 h2 on h2.report_uuid=i.report_uuidwhere <i.condition>and i.h1_success=#{h1Success}and i.h2_success=#{h2Success}order by i.id desc

修改后,现在的查询性能和原来的没有太大的变化,时延可以接受。

历史数据订正

优化方案是确定了,并且代码上也进行了调整,但是新加的冗余字段对于历史数据是没有值的,所以需要从关联表中把冗余字段的值更新到 report_info 表中去。

最简单的就是执行一个 update 语句,如下所示:

update report_info i,report_handle1 h1,report_handle2 h2set i.h1_success=h1.is_success,i.h2_success=h2.is_successwhere i.report_uuid=h1.report_uuidand i.report_uuid=h2.report_uuid

咋一看上去好像没什么问题,但是仔细想一想你就会发现如果在线上执行这样一条语句,将会造成怎样灾难性的后果。

对于线上数据需要进行订正的,可以通过代码分批次修正,为什么要分批次修正,主要是因为一次性更新涉及到的记录数太多很可能把db搞死。

比如线上有几百万的历史数据需要进行订正,如果一次性更新会产生过大的事务,可能会把db搞死。具体的可能会对 slave 造成影响,也可能将 innodb 的系统表空间撑得很大。

而 undo 是按照 segment 为基础单元申请 buffer 空间的,如果一个或几个 segment 能够满足事务的大小,就会复用,所以小事务会循环利用已有的 segment,但是如果已有的 segment 不能满足当前事务的大小就需要重新申请新的 segment,所以大的事务会申请超级大的 buffer,最终就会导致 innodb 的系统表空间被撑得很大。

所以如果我们要对历史数据进行订正的话,应该避免一次性更新太多的数据,咨询了一个 dba 朋友,他建议每次更新 2000 条左右的记录。

数据修订程序

确定了数据修订的方案后,我们就可以着手来写我们的数据修订的程序了。

首先我们确定了需要分批次进行订正,那么我们可以像分页查询数据一样,定义总记录数,页数,以及每页的大小,根据主键 id 来分批次,然后通过一个循环来执行每一批中的数据订正即可。

定义下面这样一个类来执行具体的数据订正,如下列代码所示:

public class DataFixer {    private int maxId;    private int pageSize;    private int totalPages;    public DataFixer(int maxId, int pageSize) {        this.maxId = maxId;        this.pageSize = pageSize;        this.totalPages = (maxId - 1) / pageSize + 1;    }
    public void fix() {        int startId = 1;        int endId;        if (totalPages == 1) {            endId = maxId;            doFix(startId, endId);        } else {            int currentPage = 1;            endId = currentPage * pageSize;            while (currentPage++ <= totalPages) {                doFix(startId, endId);                startId = endId + 1;                if (currentPage == totalPages) {                    endId = maxId;                } else {                    endId = currentPage * pageSize;                }            }        }    }
    /**     * 执行数据订正的方法     */    private void doFix(int startId, int endId){        // 执行具体的订正方法    }}

在 doFix 方法中我们只需要执行下面的 sql 即可:

update report_info i,report_handle1 h1,report_handle2 h2set i.h1_success=h1.is_success,i.h2_success=h2.is_successwhere i.report_uuid=h1.report_uuidand i.report_uuid=h2.report_uuidand i.id between #{startId} and #{endId}

存在的问题

上面的订正语句存在的一个问题是一次更新了两个字段,这样需要一次关联两张表,可能会比较慢,事务会更大,我们能否将这条大语句拆分成两个更小的语句呢。答案是可以的,如下所示:

update report_info i,report_handle1 h1set i.h1_success=h1.is_successwhere i.report_uuid=h1.report_uuidand i.id between #{startId} and #{endId};
update report_info i,report_handle2 h2set i.h2_success=h2.is_successwhere i.report_uuid=h2.report_uuidand i.id between #{startId} and #{endId};

这样就将一条大的 update 语句拆成了两条相对小的语句,然后我们通过两个线程去执行效果应该会好很多。

优化程序

这样的话我们就需要对我们的程序进行优化,将原来的类修改为一个 Runnable,如下所示:

public abstract class AbstractDataFixer implements Runnable {    private int maxId;    private int pageSize;    private int totalPages;    public AbstractDataFixer(int maxId, int pageSize) {        this.maxId = maxId;        this.pageSize = pageSize;        this.totalPages = (maxId - 1) / pageSize + 1;    }
    @Override    public void run() {        int startId = 1;        int endId;        if (totalPages == 1) {            endId = maxId;            doFix(startId, endId);        } else {            int currentPage = 1;            endId = currentPage * pageSize;            while (currentPage++ <= totalPages) {                doFix(startId, endId);                startId = endId + 1;                if (currentPage == totalPages) {                    endId = maxId;                } else {                    endId = currentPage * pageSize;                }            }        }    }
    /**     * 执行数据订正的方法     */    public abstract void doFix(int startId, int endId);}

然后我们创建两个 AbstractDataFixer 的实例,分别实现 doFix 的方法,例如 Handle1DataFixer 的 doFix 方法调用第一条 update 语句,Handle2DataFixer 的 doFix 方法调用第二条 update 语句。

这样我们就可以用两个线程来同步执行两个字段的更新操作,事务也比较小,更新应该会比较快。

继续优化

到这里可能有的同学觉得应该差不多了,但是通过两个线程来执行的话,会不会有问题呢?假设 id 的范围是 1 到 1000 那么两个线程在 id 从小到大执行的过程中,可能会 “相遇” 多次,当对同一个 id 执行 update 操作时是会对这行记录进行锁定的,这时两个线程就会存在竞争的关系,一个线程在锁定了行记录的时候,另一个线程想更新这行记录就只能等待。

那有没有好的办法减少两个线程之间的竞争关系呢,答案肯定是有的,一个简单的方法就是,让一个线程从小到大更新,另一个线程从大到小更新,这样的话,两个线程至多只会 “相遇” 一次,这样就能大大降低竞争关系。

分析清楚了具体的原理之后,实现起来就很简单了,只需要在原来的代码中增加一个 reverse 属性,表示是否需要进行方向更新,即 id 从大到小进行更新,修改后的代码如下:

public abstract class AbstractDataFixer implements Runnable {    private int maxId;    private int pageSize;    private int totalPages;    private boolean reverse;    public AbstractDataFixer(int maxId, int pageSize, boolean reverse) {        this.maxId = maxId;        this.pageSize = pageSize;        this.totalPages = (maxId - 1) / pageSize + 1;        this.reverse = reverse;    }
    @Override    public void run() {        int startId = reverse ? maxId : 1;        int endId;        if (totalPages == 1) {            endId = reverse ? 1 : maxId;            if (reverse) {                doFix(endId, startId);            } else {                doFix(startId, endId);            }        } else {            int currentPage = 1;            endId = reverse ? (maxId - currentPage * pageSize + 1) : currentPage * pageSize;            while (currentPage++ <= totalPages) {                if (reverse) {                    doFix(endId, startId);                } else {                    doFix(startId, endId);                }                startId = reverse ? endId - 1 : endId + 1;                if (currentPage == totalPages) {                    endId = reverse ? 1 : maxId;                } else {                    endId = reverse ? (maxId - currentPage * pageSize + 1) : currentPage * pageSize;                }            }        }    }
    /**     * 执行数据订正的方法     */    public abstract void doFix(int startId, int endId);}

然后要做的跟之前的一样,定义两个 Fixer 实现类,分别执行 handle1 的 update 语句和 handle2 的 update 语句。

至此整个优化的过程已经全部分析结束了。

干货分享

最近将个人学习笔记整理成册,使用PDF分享。关注我,回复如下代码,即可获得百度盘地址,无套路领取! •001:《Java并发与高并发解决方案》学习笔记;•002:《深入JVM内核——原理、诊断与优化》学习笔记;•003:《Java面试宝典》•004:《Docker开源书》•005:《Kubernetes开源书》•006:《DDD速成(领域驱动设计速成)》•007:全部•008:加技术群讨论

本文分享自微信公众号 - IT牧场(itmuch_com)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • AWS CTO对过去十年的经验总结 – 十条军规

    AWS(Amazon Web Service) 开始于 2006 年 3 月 14 日 Amazon S3 的发布,距今已有十年时间。回首过去十年,我们在构建和...

    用户1516716
  • Docker系列教程28-实战:使用Docker Compose运行ELK

    https://docs.docker.com/compose/samples-for-compose/#samples-tailored-to-demo-co...

    用户1516716
  • Spring中的9种设计模式汇总

    又叫做静态工厂方法(StaticFactory Method)模式,但不属于23种GOF设计模式之一。

    用户1516716
  • 专栏 | 香侬科技独家对话Facebook人工智能研究院首席科学家Devi Parikh

    Facebook 人工智能研究院(FAIR)首席科学家 Devi Parikh 是 2017 年 IJCAI 计算机和思想奖获得者(IJCAI 两个最重要的奖项...

    机器之心
  • Java集合--阻塞队列(LinkedBlockingQueue)

    贾博岩
  • teb,peb 数据结构原型

    战神伽罗
  • Vue(CLI3.0)多环境配置问题2020

    程序员不务正业
  • 5G火车站来了!上海虹桥火车站5G网络建设正式启动

    继5G公交车后,5G火车站要来了。2月18日,华为官方和上海移动宣布,上海虹桥火车站启动5G网络建设,这是首个采用5G室内数字系统建设的火车站。

    镁客网
  • H3C--MSTP配置

    3C 系列以太网交换机支持用户配置 MST 域名、VLAN 映射表、MST 域的修订级别当以上三者完全相同时,这两台或者多台交换机才能属于同一个MST 域。

    py3study
  • 神经阅读理解与超越:基础篇

    原文链接:https://chendq-thesis-zh.readthedocs.io/en/latest/

    Sam Gor

扫码关注云+社区

领取腾讯云代金券