慢查询定义:
- SQL 执行结果满足以下任一条件
- 单次执行的最大耗时超过 3s
- 单次执行最大扫描扫描行数超过 10 万行或全表扫描
- 单次执行的最大返回行数超过 5 万行
- 单次执行的最大返回字节数超过 10MB
- 多次执行的平均耗时超过 1s
1. 优化实例
1.1 case 1
1.1.1 现象
扫描行数多(1400w+),执行耗时长(14s+)
1.1.2 原因分析
表中存在(deal_id, poi_id)的唯一联合索引,查询语句如下:
SELECT DISTINCT poi_id FROM table1 WHERE deal_id IN (?);
查看执行计划如下:
使用了联合索引(deal_id, poi_id),但deal_id索引区分度不高,in(?) 传入太多的deal_id导致扫描行数过多,耗时增加。
1.1.3 优化思路
- 将dealIds拆分为多个小的subDealIdList;
- 每个subDealIdList在线程池中查询(CompletableFuture.supplyAsync,注:如果dealIds的元素数量小于分片batchsize,则在当前线程中查询,避免线程上下文切换带来的开销);
- 给每个线程设置统一的超时时间:3s(绝大多数sql查询都能在3s内返回结果)(为了不影响业务的正常服务,实际上将超时时间配置到lion上,设为10s,后续可以通过线上sql的执行时间来动态调整);
- 由于大客绑定门店数(>1k)较多,所以筛选出大客和非大客,分别设置不同的batch size查询;(大客是指一个商品“deal_id”绑定的门店“poi_id”数量超过1k的商品);
- batch size:由于扫描行数无法定量计算,所以根据返回行数进行临界值估计,非大客最大返回行数为1k,最大返回行数不得超过5w,所以非大客batch size最大为50,大客batch size设置为5。
1.2 case 2
1.2.1 现象
全表扫描,执行耗时长达68s+
1.2.2 原因分析
查询语句如下:
SELECT id, deal_id AS dealId, poi_id AS poiId, status FROM table2 WHERE id > ? AND deal_id IN (?) ORDER BY id ASC LIMIT ?;
查看执行计划,发现type为index,key为primary,说明为主键索引的全索引扫描,也就是全表扫描(因为聚簇索引的值本身就是完整数据行):
对于该sql语句,执行顺序为:
- 条件过滤:根据 deal_id IN (?) 条件过滤记录。
- 排序:将过滤后的结果集按照id进行升序排序。
- limit:应用limit子句,仅返回过滤、排序后的前N条记录。
如果抛开业务场景,只针对sql查询语句进行优化,我们肯定希望它能够走索引。目前表中存在的索引中比较合适的是idx_deal_id_id,可以通过force index (idx_deal_id_id)强制使用索引查询,但强制使用索引存在一定的风险:
- 性能下降:如果强制使用的索引并不适合当前的查询条件,可能会导致查询性能下降
- 维护复杂性:随着时间的推移和数据分布的变化,最初适合的索引可能变得不再有效。强制使用索引可能会使数据库维护变得更加复杂,需要更多的监控和调整。
- 降低可移植性:强制使用索引可能会使应用程序与数据库的特定实现紧密耦合,降低代码的可移植性。
- 影响数据库优化器的决策:数据库优化器通常能够很好地决定使用哪个索引,强制使用索引可能会限制优化器的智能决策能力。
- 普适性:强制使用索引无法确保它在所有情况下都能提供预期的性能提升,这可能会增加开发和测试的工作量。
业务代码:
List<Result> result = new ArrayList<>();
boolean isEnd = false;
Long lastId = null;
do {
List<Result> list = Collections.emptyList();
list = searchMapper.query(inputData, status, lastId, limit_size);
if (CollectionUtils.isEmpty(list)) {
return result;
}
result.addAll(list);
if (list.size() < limit_size) {
isEnd = true;
}
lastId = list.get(list.size() - 1).getId();
} while (!isEnd);
return result;
针对当前的业务场景,从业务代码可以看出,目的是为了查询全量数据,但是却用了分页查询的方法,并limit了返回的数据量(猜测是为了避免一次sql查询返回的数据量太多而使用分页查询全量数据)。
- 为了每次从上一次分页的最后一个id开始查,每次sql查询都需要按照id进行排序;
- WHERE deal_id IN (?) ORDER BY id ASC LIMIT m, n 会先根据deal_id查出全部数据,然后排序,最后再分页。耗时原因主要在于业务代码每次都是传全部的dealIds,第一次分页查询时查的是全量的数据,并且没有id的限制。这样造成的结果就是:越往后分页查询越快,但是部分数据会在前面的分页查询中被反复查询。由于第一次分页就查出了全量数据,后面的分页查询都是多余重复的。
1.2.3 优化思路
查询全量数据根本没必要分页查。如果是为了限制返回的数据量大小,或者担心一次性查询全量数据会造成慢查询,可以将in(?)中传入的参数分批查询,同时可以使用并发查询来降低网络开销。
此外,和case1中的情况类似,大客deal_id绑定的poi_id数量很多,会导致单次sql查询返回的记录数过多,所以也可以针对大客和非大客设置不同的batchsize。
2. 并发分批查询通用工具
由于业务代码中多处涉及此类慢sql问题,故将「并发分批查询」部分的代码封装成工具类 ConcurrentBatchQueryUtil
供团队后续优化使用。
2.1 优化过程
- 往sql查询语句的in(?)中一次性传入太多查询参数可能会导致以下问题:
-
扫描行数过多,查询耗时增加,从而造成慢sql问题,影响用户体验的同时给数据库带来压力;
-
索引失效:如果数据查询的范围太大,或当in查询中需要匹配的值数量过多时,MySQL会认为通过全表扫描来查找数据比使用索引更有效率;
-
索引失效的原因:
在MySQL数据库中,当IN或OR子句中的参数数量过多时,可能会导致索引失效的问题。MySQL优化器根据系统变量eq_range_index_dive_limit的值来决定是使用索引下潜(index dive)还是索引统计(index statistics)来预估查询的影响行数。- 索引下潜(index dive):当IN子句的范围区间较少时,优化器会访问存储引擎,查看每个区间中的记录数,这种方式相对准确,但不适合快速预估大量值。
- 索引统计(index statistics):当IN子句的范围区间较多时,优化器会使用统计数据来估算匹配的记录数,这种方式计算简单且快速,但预估的准确性较低。
- 系统变量eq_range_index_dive_limit:这个变量提供了一个阈值,当IN或OR子句中的参数数量低于这个阈值时,优化器采用index dive方式预估影响行数;当参数数量大于或等于这个阈值时,采用index statistics方式预估。
- 默认值:在MySQL 5.6版本中,默认值是10,在5.7版本中,默认值是200。
- 问题:如果eq_range_index_dive_limit设置过小,且索引分布极不均匀,可能会导致MySQL选择错误的执行计划,从而引发性能问题。
- 解决方案:控制IN或OR语句中的参数个数,避免超过阈值。建议在代码层面采取措施,如使用组件拦截或编码规范,来限制参数个数,以避免触发索引失效的风险。
参考:https://blog.csdn.net/peter7_zhang/article/details/121134070
-
-
一次性返回数据量太大造成网络延迟;
-
针对以上问题,可以采用的解决方案有:
- 分批查询:将大量参数分批处理,每次只查询一部分参数;
- 使用临时表:将参数放入一个临时表中,然后使用IN (SELECT column FROM temp_table)来执行查询;(TODO 这种优化方案的原理是什么?)
- 使用JOIN:如果参数与另一个表有关联,可以使用JOIN来代替IN;(TODO 这种优化方案的原理是什么?)
考虑实现复杂度和通用性,采用分批查询解决慢sql问题。
-
简单使用分批查询会增加网络开销,造成等待时间增加,因此采用多线程进行并发分批查询。
-
使用多线程查询数据库可能会因为某个线程执行失败,导致查询结果不完整,返回给调用方错误数据。因此需要在调用线程中对执行失败的线程进行判断,并进行兜底重试。
一开始的实现逻辑为使用
CompletableFuture
获取每个线程的查询结果,通过future.isDone()
和future.join()
判断任务是否成功执行完成。但因为future无法获取对应的subInputData,所以如果某个线程执行失败或future获取结果失败,需要对全部inputData进行重试,从而造成重复查询,增加耗时。
为了只针对执行失败线程的subInputData进行重试,封装FutureWithInput
工具类,用于获取CompletableFuture
对应的inputData。
import java.util.concurrent.CompletableFuture;
/**
* future 工具类 T入参,U对应future
*/
public class FutureWithInput<T, U> {
private final T input;
private final CompletableFuture<U> future;
public FutureWithInput(T input, CompletableFuture<U> future) {
this.input = input;
this.future = future;
}
public T getInput() {
return input;
}
public CompletableFuture<U> getFuture() {
return future;
}
}
在当前业务场景下,大客商品相较于普通商品绑定的门店数更多,在数据库中对应的记录也就更多,因此过滤出大客id和非大客id,设置不同的batchsize分别执行查询逻辑,同时要实现大客和普通商品的并行查询以及失败重试。
2.2 入参
为了将并发结构和具体的接口解耦,避免在逻辑结构中依赖实际调用数据,封装了ConcurrentBatchQueryUtil
工具类,将mapper查询接口以函数式接口Function<List<T>, List<R>> searchFunc
的形式作为入参传入。
ConcurrentBatchQueryParam
:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
/**
* 并发批量查询参数
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ConcurrentBatchQueryParam<T, R> {
private Function<List<T>, List<R>> searchFunc;
private List<T> inputData;
private Integer batchSize;
private ExecutorService executorService;
private Integer timeout;
private TimeUnit timeUnit;
}
RetryQueryParam
:
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class RetryQueryParam<T, R> {
private Function<List<T>, List<R>> searchFunc;
private Integer timeout;
private TimeUnit timeUnit;
}
2.3 具体实现
public static <T, R> List<R> concurrentBatchQuery(ConcurrentBatchQueryParam<T, R> concurrentBatchQueryParam)
:根据传入的查询参数进行并发分批查询,并针对执行失败的线程在当前线程进行重试;public static <T, R> List<R> collectConcurrentResultsWithRetries(List<FutureWithInput<List<T>, List<R>>> futuresWithInput, RetryQueryParam<T, R> retryQueryParam)
:收集并发查询结果,并针对执行失败的线程在当前线程进行重试;
ConcurrentBatchQueryUtil
:
import com.google.common.collect.Lists;
import xxx.bo.ConcurrentBatchQueryParam;
import xxx.bo.RetryQueryParam;
import xxx.FutureWithInput;
import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.*;
import java.util.function.Function;
/**
* 并发批量查询数据库工具类
*/
public class ConcurrentBatchQueryUtil {
private static Logger LOG = LoggerFactory.getLogger(ConcurrentBatchQueryUtil.class);
public static <T, R> List<R> concurrentBatchQuery(ConcurrentBatchQueryParam<T, R> concurrentBatchQueryParam) {
Function<List<T>, List<R>> searchFunc = concurrentBatchQueryParam.getSearchFunc();
List<T> inputData = concurrentBatchQueryParam.getInputData();
Integer batchSize = concurrentBatchQueryParam.getBatchSize();
ExecutorService executorService = concurrentBatchQueryParam.getExecutorService();
if (CollectionUtils.isEmpty(inputData)) {
return Collections.emptyList();
}
if (inputData.size() <= batchSize) {
return new ArrayList<>(searchFunc.apply(inputData)); // 返回结果未去重
}
List<List<T>> partitionList = Lists.partition(inputData, batchSize);
List<FutureWithInput<List<T>, List<R>>> futuresWithInput = new ArrayList<>();
partitionList.forEach(partition -> {
CompletableFuture<List<R>> future = CompletableFuture.supplyAsync(() -> searchFunc.apply(partition), executorService);
futuresWithInput.add(new FutureWithInput<>(partition, future));
});
return collectConcurrentResultsWithRetries(futuresWithInput, new RetryQueryParam<>(searchFunc, concurrentBatchQueryParam.getTimeout(), concurrentBatchQueryParam.getTimeUnit()));
}
public static <T, R> List<R> collectConcurrentResultsWithRetries(List<FutureWithInput<List<T>, List<R>>> futuresWithInput, RetryQueryParam<T, R> retryQueryParam) {
List<R> result = new ArrayList<>();
Function<List<T>, List<R>> searchFunc = retryQueryParam.getSearchFunc();
CompletableFuture<Void> allFutures = CompletableFuture.allOf(
futuresWithInput.stream().map(FutureWithInput::getFuture).toArray(CompletableFuture[]::new)
);
try {
allFutures.get(retryQueryParam.getTimeout(), retryQueryParam.getTimeUnit()); // 统一等待 避免每个future单独get,可能导致的耗时长问题
} catch (InterruptedException | ExecutionException | TimeoutException e) {
LOG.error("并发执行异常: {}", e.getMessage(), e);
}
// 检查每个 future 的状态,并对失败的 future 重新进行处理
futuresWithInput.forEach(futureWithInput -> {
CompletableFuture<List<R>> future = futureWithInput.getFuture();
if (future.isDone()) {
try {
List<R> tmpResult = future.join();
if (tmpResult != null) {
result.addAll(tmpResult);
}
} catch (CompletionException ex) { // 任务执行过程中发生异常
LOG.error("Failed future for input {} due to: {}", futureWithInput.getInput(), ex.getCause().getMessage(), ex.getCause());
result.addAll(searchFunc.apply(futureWithInput.getInput()));
}
} else {
LOG.error("Future for input {} did not complete in time", futureWithInput.getInput());
result.addAll(searchFunc.apply(futureWithInput.getInput()));
}
});
return result;
}
}
2.4 使用示例
// 定义mapper调用逻辑的Function
Function<List<Integer>, List<Result>> searchFunc = (inputData) -> searchMapper.query(inputData, poiStatus);
ConcurrentBatchQueryParam<Integer, Result> concurrentBatchQueryParam = new ConcurrentBatchQueryParam<>(searchFunc, inputData, batch_size, executor, timeout, TimeUnit.MILLISECONDS);
List<Result> result = ConcurrentBatchQueryUtil.concurrentBatchQuery(concurrentBatchQueryParam);
3. 参考
https://blog.csdn.net/peter7_zhang/article/details/121134070
https://www.cnblogs.com/lingyejun/p/17100242.html
https://juejin.cn/post/7088153125663703071