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

时间:2021-5-25 作者:qvyue

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

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

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_reason
from report_info i
left join report_handle1 h1 on h1.report_uuid=i.report_uuid
left join report_handle2 h2 on h2.report_uuid=i.report_uuid
where 
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_reason
from report_info i
left 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 
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_reason
from report_info i
left join report_handle1 h1 on h1.report_uuid=i.report_uuid
left join report_handle2 h2 on h2.report_uuid=i.report_uuid
where 
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 h2
set 
i.h1_success=h1.is_success,
i.h2_success=h2.is_success
where i.report_uuid=h1.report_uuid
and 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++ 

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

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

存在的问题

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

update report_info i,report_handle1 h1
set 
i.h1_success=h1.is_success
where i.report_uuid=h1.report_uuid
and i.id between #{startId} and #{endId};

update report_info i,report_handle2 h2
set 
i.h2_success=h2.is_success
where i.report_uuid=h2.report_uuid
and 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++ 

然后我们创建两个 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++ 

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

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

声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:qvyue@qq.com 进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。