如何对Mybatis里的SQL进行优化？

背景

MyBatis是一款开源的持久层框架，它封装了JDBC操作数据库时的常用API，并提供了ORM映射的功能，使得开发者可以使用Java对象来操作数据库。开发者可以通过XML配置或注解方式自定义SQL语句和参数映射规则，从而使得应用开发人员无需过多的了解数据库，就可以开发出功能完善应用程序。

但是天下没有免费的午餐，使用Mybatis也使得应用开发人员进行SQL调优变得困难。通过Mybatis开发的应用程序，传统方式下需要通过模拟应用的接口调用、启动数据库的SQL日志、获取应用的SQL查询语句、对获取的SQL进行优化，这让整个SQL调优的流程复杂且费时。

PawSQL为了方便应用开发人员进行SQL性能调优，提供了基于Mybatis的mapper文件创建SQL优化任务的功能，让应用开发人员在页面上通过鼠标操作，完成其应用的SQL性能优化。

工作原理

Mapper文件

在 Mybatis 中，Mapper 文件是一种用于配置 SQL 语句和 SQL 操作的配置文件。

Mapper 文件通常包含四个主要部分：

1. 命名空间，用于指定对应操作的实体类或其接口类的全路径名。
2. SQL语句片段，可以通过 select、insert、update、delete 等标签来定义不同的 SQL 语句。
3. 映射语句定义，可以通过映射语句来将操作的参数和返回值与上述 SQL 语句进行绑定的方式。
4. 流程控制标签，如<if>、where、foreach等标签，根据不同的条件动态生成SQL语句，以便满足不同的需求。

通过使用 Mapper 文件，应用开发人员可以实现简便、灵活的 SQL 操作，同时也能更好地维护 SQL 与 Java 代码之间的解耦。

Mapper文件=>SQL语句

为了对Mapper中的SQL进行优化，我们需要对Mapper文件进行解析，排列组合所有可能的合法SQL语句，并对其中的变量进行替换，以便生成合法的SQL语句，并进行优化。

• SQL片段排列组合

譬如对于以下的mapper文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.example.mapper.CustInfoMapper">
   <select id="custInfo">
      select * from customer
       <where>
           <if test="nationkey != null and nationkey != ''">
              and c_nationkey = #{nationkey}
           </if>
           <choose>
               <when test="C_MKTSEGMENT != null and C_MKTSEGMENT != ''">
                  and c_mktsegment = #{C_MKTSEGMENT}
               </when>
               <otherwise>
                  and c_phone LIKE '139%'
               </otherwise>
           </choose>
       </where>
   </select>
</mapper>

由于<if>标签里的片段满足条件才出现，所以c_nationkey = ? 可出现可不出现；而<choose>标签里的内容必须且只能出现其中一个，所以其排列组合只可能出现下面的四种情况。

select * as cnt from customer where c_phone like '139%';
select * as cnt from customer where c_nationkey =  #{nationkey} and c_phone like '139%';
select * as cnt from customer where c_mktsegment = #{C_MKTSEGMENT};
select * as cnt from customer where c_nationkey = #{nationkey} and c_mktsegment = #{C_MKTSEGMENT};

通过排列组合方式产生的SQL，有可能会有一些SQL永远不会在真实的生产环境中出现。但这些SQL可能会对SQL优化的过程产生影响，譬如推荐了一些无用的索引，或是索引中出现了一些不会被使用的列。所以用户需要根据自己具体的业务场景，在PawSQL的SQL筛选预览页面，排除掉那些不会真实出现的SQL组合。

• 变量替换

对于mapper文件中的变量，我们会根据它所在的上下文，推测其数据类型，并根据数据库的统计信息（如有）选择一个合适常量来进行替换。譬如对于以上的四个SQL，我们会将#{nationkey}替换为整型常量，而将#{C_MKTSEGMENT}替换为字符串常量，所以最终提交到PawSQL优化引擎的是这样的SQL。

select * as cnt from customer_n where c_phone like '139%';
select * as cnt from customer_n where c_nationkey = 128 and c_phone like '139%';
select * as cnt from customer_n where c_mktsegment = 'A234913';
select * as cnt from customer_n where c_nationkey = 16 and c_mktsegment = 'B123498';

上手指南 - 从Mapper获取待优化SQL

我们知道，使用PawSQL进行SQL优化分为三步，
第一步定义工作空间，
第二步录入待优化SQL，使用Mapper文件进行SQL优化发生在此步中。
第三步配置优化选项、进行优化。

PawSQL目前支持六种SQL录入方式，包括本文介紹的Mapper文件。

• 这里我们选择Mapper文件的录入方式, 点击上传按钮上传对应的Mapper.xml文件

• 点击创建查询语句集按钮来到'待优化SQL预览'页面，在此页面中，您需要排查一些永远都不会出现的SQL组合，以避免生成无效的优化建议。

• 后续的步骤是一致的，点击下一步，完成SQL优化任务的执行。

以上就是使用Mapper文件创建PawSQL优化的全部内容了, 到这里创建自己的账号快去试试吧！

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，Opengauss等，提供的SQL优化产品包括

• PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
• PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
• PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以独立安装部署，并通过http/json的接口提供SQL优化服务。PawSQL Engine以docker镜像的方式提供部署安装。

扫描关注PawSQL公众号，和数据库大咖交流SQL优化经验，体验PawSQL最新功能。