MySQL查询优化

MySQL查询优化

马草原 849 2023-06-06

MySQL查询优化

在一个元数据管理项目中，应用每次启动都需要重新从数据库加载各种配置到缓存中，但是随着接入渠道越来越多应用启动已经慢到令人发指。深究其中的数据加载，查询逻辑发现查询结果跨越了多个实体表，并且原来的代码使用了原生SQL进行查询，使用了大量的Join技术去进行多表查询，性能很差。 作为一名后端开发人员，对数据库进行增删改查的熟练使用是最基本的要求，但是有时候业务的复杂性和相关查询技术混用就很可能会导致查询接口性能瓶颈，更严重的情况可能出现大量慢SQL，导致数据库服务器崩溃等情况。 所以深究数据库的查询是作为开发经验不足的同学来说是一个急待解决的问题。

打开数据查询的黑盒

mysqlquery

常用的查询技术

提前搭建好数据库系统是查询性能优化的基石，包括并且不限于合理分配数据库服务器，合理对数据库进行分库分表等。 目前在公司代码中看到常用的几个查询框架，如MyBatis、tk.MyBatis、MyBatis-Plus、阿里灵狐、AliGenerator等。除了MyBatis外，其他的框架都可以简化服务端的开发，在一些简单场景的查询不需要去写xml文件中的代码，但是遇到一些复杂业务场景时，还是需要手动去xml文件中写复杂SQL。

查询优化

查询优化常用的方式是对where条件和order by等条件语句所涉及的字段进行加索引，但是如果简单粗暴加索引并不一定可以得到明显的优化，甚至还可能适得其反。

可以选择的优化方案

逻辑优化

逻辑优化本质是应用短路思想。在多条件查询中，梳理出用户常用的查询条件，将这些常用的查询条件组合成的子查询，优先放在整个查询的最前面，这样就避免了冗余的查询条件了。

内存查询

内存查询是将复杂的查询切分多个子查询，将每个子查询的查询结果查到内存中，在内存中进行过滤、组合得出最终的查询结果。 不过问题是，如果查询的结果比较大，或者有多个线程并发操作的同时，可能会引发OOM;

多线程查询

线程创建有三种方式，分别是继承Thread类；实现Runnalbe接口；实现Callable接口；但是实现Callable接口与前面两种又有一定的区别。Callable接口提供了call()方法，该方法可以有返回值，并且需要配合线程池的submit()、invoke()方法使用。

public MyThread implements Callable<Integer> {
    @Override
    public Integer call() {
        return ThreadLocalRandom.current.nextInt();
    }
}

pulic class CallableThreadTest{
    public void main() {
        MyThread myThread = new Mythread();
        ThreadPoolExecutor EXECUTOR = new ThreadPoolExecutor(...);
        Future<Integer> future = EXECUTOR.submit(callableTest);
        System.out.println(future.get());
    }
    
}

可以看到，线程池EXECUTOR提交任务后，获取到的是一个Future。Future就是对于具体的Runnable或者Callable任务的执行结果进行取消、查询是否完成、获取结果。必要时可以通过get方法获取执行结果，该方法会阻塞直到任务返回结果。 以一个实际的权利标签信息查询为例子：为了获取权利和标签相关的数据，需要查询合同、作品、权利、标签、入库单等等信息，如果以传统的SQL语句来查询，会涉及到多个实体表连表查询，那么数据库线程可能会全表扫描多张表，性能很差

public class FutureSelectTest {
    public static void main() {
         /Initialize thread pool 
    	ThreadPoolExecutor EXECUTOR = new ThreadPoolExecutor(...);
        Future<T1> workInfoFuture = EXECUTOR.submit(new Callable<String>() {
            @Override
            public String call() throws Exception {
                return T1;
            }
        });
        Future<T2> rightInfoFuture = EXECUTOR.submit(new Callable<String>() {
            @Override
            public String call() throws Exception {
                return T2;
            }
        });
        Future<T3> tagInfoFuture = EXECUTOR.submit(()-> getTagInfo()
        });
        // 查询其他实体表
        ...
        // 汇总单任务结果
        result.add(workInfoFuture.get(),rightInfoFuture.get(),tagInfoFuture()...);   
    }
}

如果采用上述的Future方式查询，那么又有一个问题，Future只能表示一个异步计算的结果，需求需要的最终的查询结果是多个计算结果（多个表查询汇总的结果），那么起多个任务获取多个Future，最终在内存中将所有Future拼接起来呢？这种方案虽然可行，但是违背了多线程的使用初心，多线程就是利用CPU的多核的有点，可以同时执行多个线程，但是Future虽然使用了多线程提交了任务，但是每一次调用get()（Future获取结果方式），都会阻塞其他线程，本质上还是单线程执行。所以Future会有一定局限性，那就是无法并发的执行多任务 那么有其他方式解决这种局限性吗？
CompletionService和CompletableFuturn都解决了。
CompletionService CompletionService可以一边提交任务，一边获取任务的返回结果，任务之前不会相互阻塞，可以实现先执行完的先取结果，不再依赖任务顺序了。CompletionService内部通过阻塞队列+FutureTask，实现了任务先完成可优先获取到，即结果按照完成先后顺序排序，内部有一个先进先出的阻塞队列，用于保存已经执行完成的Future，通过调用它的take()或poll()可以获取到一个已经执行完成的Future，进而通过调用Future接口实现类的get方法获取最终的结果。

public class FutureSelectTest {
    public static void main() {
        // 初始化线程池
    	ThreadPoolExecutor EXECUTOR = new ThreadPoolExecutor(...);
        // 结果集合
        List<Future<Integer>> futureList = new ArrayList<>();
        // 初始化CompletionService
        CompletionService<T> completionS = new ExecutorCompletionService<>(EXECUTOR);
        // 添加任务,异步查询其他实体表
        Future<Integer> future1 = completionS.submit(() -> getWorkInfo(arge1));
        Future<Integer> future2 = completionS.submit(() -> getRightInfo(arge2));
        Future<Integer> future3 = completionS.submit(() -> getTagInfo(arge3));
        ...
        futureList.add(future1);
        futureList.add(future2);
        futureList.add(future3);
        // 汇总单任务结果
        for (Integer future : futureList) {
            result.add(completionS.take().get());     
        } 
        System.out.println(result);
    }
}

CompletionService适合批量提交异步任务，由于内部通过阻塞队列实现，先进先出，先提交的任务会先返回执行的结果。
现在有这么一个需求：那么如果需要查询一个入库单信息，首先查询作品信息，然后根据作品的信息查询入库单著录单的信息，然后再查询权利信息等。也就是整个查询需要用到多个异步任务，如果把每一张实体表看做一次异步任务化，那么任务2的查询，需要任务1的查询结果作为入参，也就是说我需要查询任务是要按照我的执行顺序返回结果的，那么如果使用多线程去查询的化怎么实现呢？ 如果上述场景使用CompletionService可能不是一个非常好的实现方式，因为CompletionService提交任务进入了一个队列，队列的返回方式是先进先出的，不能按需求自动编排任务返回的顺序，如果使用栈+FutureTask的方式重新实现自己的CompletionService，那么就可以随意编排任务的返回结果了。

CompletableFuturn除了使用栈+FutureTask的方式去实现自己CompletionService，去解决CompletionService不能自动编排任务局限性，Java提供了CompletableFuturn的方式去实现了任务的编排能力。
CompletableFuturn提供了多种方法实现了异步任务的编排： 结果当入参：将上一阶段任务的执行结果当做下一阶段任务的入参，产生新的结果；
thenApply()或thenCompose()，这两个方法的区别就是thenCompose()重新生成了新的Completable。

// thenApply()
CompletableFuture<StoreOrderPackageLinkInfo> future = CompletableFuture.supplyAsync(() -> {
    return getWorkInfo(Long workId);
}).thenApply(workInfo -> {
    return getRight(WorkInfo workInfo);
});

// thenCompose()
CompletableFuture<WorkInfo> future1 = CompletableFuture.supplyAsync(new Supplier<WorkInfo>() {
    @Override
    public WorkInfo get() {
        return getWorkInfo(Long workId);
    }
});
CompletableFuture<RightInfo> future2 = future1.thenCompose(new Function<RightInfo, CompletionStage<WorkInfo>>() {
    @Override
    public CompletionStage<RightInfo> apply(WorkInfo workInfo) {
        return CompletableFuture.supplyAsync(new Supplier<RightInfo>() {
            @Override
            public RightInfo get() {
                return getRightInfo(workInfo);
            }
        });
    }
});
  • thenAccept()：对单个结果进行消费
  • thenAcceptBoth()：对两个结果进行消费
  • thenRun()：不关心结果，只对结果执行Action
  • 结果组合 合并两个线程任务的结果，并进一步处理。
  • 任务交互 线程交互指将两个线程任务获取结果的速度相比较，按一定的规则进行下一步处理。 applyToEither()、accpectEither()、runAfterEither()

in查询优化 使用inner join来优化in查询inner join是为了获取多表的交集，优化in查询本质是减少扫描的行数；

尽量避免的写法 查询尊崇的原则就是尽量避免全表扫描。具体哪些操作会引发全表扫描自行Google。

善于使用一些工具

SQL分析命令

explian命令：可以分析SQL的执行计划，网上有很多相关字段解释。 https://www.cnblogs.com/juno3550/p/15740254.html

数据库监控工具

公司内部的数据库查询系统是DMS系统（好像已经开源了），他自带的有分析诊断工具但是应该是仅限于内部使用，其他还有Prometheus和Grafana可以使用。
简单来说，Prometheus其实就是一个监控数据采集和存储系统，他可以利用监控数据采集组件(比如mysql_exporter)从你指定的MySQL数据库中采集他需要的监控数据，然后他自己有一个时序数据库，他会把采集到的监控数据放入自己的时序数据库中，其实本质就是存储在磁盘文件里。 我们采集到了MySQL的监控数据还不够，现在我们还要去看这些数据组成的一些报表，所以此时就需要使用Grafana了， Grafana就是一个可视化的监控数据展示系统，他可以把Prometheus采集到的大量的MySQL监控数据展示成各种精美的报表，让我们可以直观的看到MySQL的监控情况。 Prometheus+Grafana是一套各种系统监控的开源解决方案。

总结

总之，数据库性能的问题的产生、发现、解决是一个是一个难以全面实践过程，也就是说一般我们开发都是比较难以有机会去从0到1搭建一套数据库系统。

大概想一下从0到1搭建一套数据库系统都会有哪些问题呢？

  • 选择数据库服务器的配置：一般Java程序员评估数据量和并发量，交给DBA，不过程序员要心中有数，也就是要有一些经验值，哪怕是看的别人经验值。

8核16G的机器部署MySQL数据库，每秒抗一两千的并发量
8核32G的机器部署MySQL数据库，每秒抗三四千千的并发量

  • 根据业务合理设计数据表的结构，包括实体表含义、字段含义、字段数据类型与大小，设计索引等；

  • 合理分库分表：以什么字段去做分库分表，分库分表中间件的调研和选择

  • 搭建主从复制架构：为数据容灾做准备，方便进行故障转移，不过现在大公司有自己的DBA，小公司一般都直接用阿里云等云厂商的数据库服务了。

那么合理处理好上述问题，数据库性能问题的产生也就几率很小了，像是我们的元数据管理模块的加载问题就是通过多线程并发查询加载来提升服务的启动速度以及重新加载的速度（之前改配置需要走代码发布重启服务，现在支持热加载了），启动速度从之前的18分钟到现在的5分钟内（日常环境）有了很大的改善。

程序 技术 分享