线上百万级数据查询接口优化过程
最近遇到一个数据查询接口性能低下的问题，需要进行优化，从解决方案的调研与梳理到方案的确定，再到最终方案的执行落地，我将优化的过程完整的记录了下来，与大家分享学习，希望能给大家有所帮助和启发。
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 <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_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 <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_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 <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 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++ <= 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 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++ <= 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 语句。
至此整个优化的过程已经全部分析结束了。
