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

最新推荐文章于 2024-03-26 15:18:18 发布

I_m_you_papa

最新推荐文章于 2024-03-26 15:18:18 发布

阅读量1.4k

点赞数

分类专栏： mysql 文章标签： sql优化

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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

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 从大到小进行更新，修改后的代码如下：

package com.hc.zhxf.config;

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 语句。

package com.hc.zhxf.config;

public class Test extends AbstractDataFixer {

	public Test(int maxId, int pageSize, boolean reverse) {
		super(maxId, pageSize, reverse);

	}

	@Override
	public void doFix(int startId, int endId) {
		System.err.println(Thread.currentThread().getName() + "--->startId:" + startId + "***" + "endId:" + endId);
	}

}

package com.hc.zhxf.config;

public class Test2 {
	public static void main(String[] args) {
		int maxId = 10;
		int pageSize = 3;
		Test test1 = new Test(maxId, pageSize, true);
		Test test2 = new Test(maxId, pageSize, false);

		Thread thread1 = new Thread(test1);
		Thread thread2 = new Thread(test2);

		thread1.start();
		thread2.start();
	}
}

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

转载：https://mp.weixin.qq.com/s/QXVCD45vIRPm9QIrQd4eEQ?tdsourcetag=s_pcqq_aiomsg

I_m_you_papa

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
线上百万级数据查询接口优化过程

最近遇到一个数据查询接口性能低下的问题，需要进行优化，从解决方案的调研与梳理到方案的确定，再到最终方案的执行落地，我将优化的过程完整的记录了下来，与大家分享学习，希望能给大家有所帮助和启发。PS：以下我所描述的所有表和字段都是虚拟的。问题产生我们有很多上报的数据，数据量比较大。这些数据保存在 report_info 表中的，表结构如下所示：create table re...
复制链接

扫一扫