慢SQL优化(1)——in(?)

慢查询定义:

  1. SQL 执行结果满足以下任一条件
  2. 单次执行的最大耗时超过 3s
  3. 单次执行最大扫描扫描行数超过 10 万行或全表扫描
  4. 单次执行的最大返回行数超过 5 万行
  5. 单次执行的最大返回字节数超过 10MB
  6. 多次执行的平均耗时超过 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 优化思路

  1. 将dealIds拆分为多个小的subDealIdList;
  2. 每个subDealIdList在线程池中查询(CompletableFuture.supplyAsync,注:如果dealIds的元素数量小于分片batchsize,则在当前线程中查询,避免线程上下文切换带来的开销);
  3. 给每个线程设置统一的超时时间:3s(绝大多数sql查询都能在3s内返回结果)(为了不影响业务的正常服务,实际上将超时时间配置到lion上,设为10s,后续可以通过线上sql的执行时间来动态调整);
  4. 由于大客绑定门店数(>1k)较多,所以筛选出大客和非大客,分别设置不同的batch size查询;(大客是指一个商品“deal_id”绑定的门店“poi_id”数量超过1k的商品);
  5. 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 优化过程

  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问题。

  1. 简单使用分批查询会增加网络开销,造成等待时间增加,因此采用多线程进行并发分批查询

  2. 使用多线程查询数据库可能会因为某个线程执行失败,导致查询结果不完整,返回给调用方错误数据。因此需要在调用线程中对执行失败的线程进行判断,并进行兜底重试

    一开始的实现逻辑为使用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

  • 14
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值