mysql的预编译-每日运维

一条sql在mysql接收到最终执行完毕返回的过程

词法和语义解析：MySQL 会对 SQL 语句进行词法分析，将 SQL 语句分割成不同的词法单元，如关键字、标识符、运算符、常量等。然后进行语义分析，检查 SQL 语句的语法是否正确，以及表名、列名、别名等是否存在，是否有权限访问等。
优化 SQL 语句，制定执行计划：MySQL 会对 SQL 语句进行优化，选择最合适的执行路径，如选择索引、确定连接顺序、确定连接方法等。优化器会生成一个执行计划，描述如何执行 SQL 语句。
执行并返回结果：MySQL 会根据执行计划，调用存储引擎的接口，对表或索引进行操作，获取数据，并进行计算、排序、分组、聚合等操作，最后将结果返回给客户端。

什么是mysql的预编译？

mysql的预编译即定义一个带"?"的sql模板，"?"定义为词法中的参数部分，使用模板时将当前参数值替换"?"，接着执行替换后的sql。另外也存储了sql模板的一些解析信息。

mysql执行预编译sql的过程

将预编译语句发送给数据库服务器，由服务器进行词法和语义解析，优化执行计划，并将编译后的结果缓存起来，返回一个预处理语句对象（PreparedStatement）。
当需要执行预编译语句时，只需将具体的参数值传入预处理语句对象，由服务器填充占位符，并直接执行，无需再次编译。
当预编译语句相同时，可以重复使用同一个预处理语句对象，只需传入不同的参数值即可。

以下所有sql的mysql版本都为5.7

服务端预编译示例代码


#定义预编译sql，名称为this_is_a_statement
prepare this_is_a_statement from 'select * from user where id=?'; 

#设置一个参数，名称为id，值为2
set @id=2;

#执行名称为this_is_a_statement的预编译sql，使用参数:id
execute this_is_a_statement using @id;

#释放名称为this_is_a_statement的预编译sql
deallocate prepare this_is_a_statement;

总结为以下几个步骤

准备阶段：客户端将 SQL 语句模板（带有占位符“?”）发送给服务器，服务器进行语法检查并初始化内部资源，返回一个语句标识符给客户端。例如，`prepare this_is_a_statement from 'select * from user where id=?';
执行阶段：客户端设置参数值，并将语句标识符和参数值发送给服务器，服务器根据语句标识符找到对应的 SQL 语句模板，将参数值替换占位符，执行 SQL 语句，并返回结果给客户端。例如，set @id=2;和execute this_is_a_statement using @id;
释放阶段：客户端释放语句标识符，服务器删除对应的 SQL 语句模板和内部资源。例如，deallocate prepare this_is_a_statement;

为了看出预编译是否被使用到，我们临时在当前绘画开启mysql日志记录。

set global general_log=on;

查询配置是否开启成功

show variables like 'general_log';

查询默认的日志文件存放地址

show variables like 'general_log_file';

mysql的预编译-1

先执行没有预编译过的sql

SELECT * FROM user where id = 1;

SELECT * FROM user where id = 2;

日志如下：

再执行预编译的sql

#定义预编译sql，名称为this_is_a_pre_statement
prepare this_is_a_pre_statement from 'select * from user where id=?'; 

#设置一个参数，名称为id，值为1
set @id=1;

#执行名称为this_is_a_pre_statement的预编译sql，使用参数:id
execute this_is_a_pre_statement using @id;

#设置一个参数，名称为id，值为
set @id=2;

#执行名称为this_is_a_pre_statement的预编译sql，使用参数:id
execute this_is_a_pre_statement using @id;

#释放名称为this_is_a_pre_statement的预编译sql
deallocate prepare this_is_a_pre_statement;

日志如下，可以看到两种方式的执行日志并不同。

mybatis中的sql预编译

mybatis是java程序员最常用的orm框架，我们看下mybatis执行sql的预编译情况。

jdbc为jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT执行代码如下：

就是执行了两条查询sql，一个是查id为1，一个是查id为2的数据

public class MybatisMain {

    public static void main(String[] args) throws Exception {
        testXmlConfig();
    }

    public static void testXmlConfig() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        
        User selectUser = new User();
        selectUser.setId(1);
        User user2 = mapper.selectBySelective(selectUser);
        selectUser.setId(2);
        User user3 = mapper.selectBySelective(selectUser);
    }
}

看看mysql执行日志，意思是mybatis没有使用到预编译吗，非也

将jdbc更改为jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT&useServerPrepStmts=true&cachePrepStmts=true再执行相同代码，日志如下

可以看到，此时的sql是预编译后执行的。即java代码也能配置预编译，jdbc配置这两个参数即可。
useServerPrepStmts=true:开启服务端预编译。

cachePrepStmts=true:缓存预编译对象。如果不对预编译对象进行缓存，每次执行sql都会进行一次预编译，sql的执行效率便大打折扣。

我们不妨思考，为什么平时自己项目的jdbc链接都不配置这两个参数，是预编译不香吗，并不是，因为这种配置是服务端预编译，没有默认使用的原因如下。

1.如果连接服务端的客户端实例、类型过多，存储在服务端的预编译对象是庞大的，增加了维护成本，对服务端的使用造成影响。
2.mysql早期版本不支持服务端预编译

jdbc默认使用了其他预编译方式，也就是客户端预编译。mybatis中的预编译，默认的就是使用的客户端预编译，在java实例所处机器，会将有动态参数的sql进行预编译，存储在preparedStatement中。执行一次查询时,将预编译模板中的？替换实际参数值后为最终sql，将最终sql发送给服务端mysql，这样即用到了预编译，也减轻了服务端的资源压力。

我们将jdbc还原为jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT，重新执行以上代码

public class MybatisMain {

    public static void main(String[] args) throws Exception {
        testXmlConfig();
    }

    public static void testXmlConfig() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        
        User selectUser = new User();
        selectUser.setId(1);
        User user2 = mapper.selectBySelective(selectUser);
        selectUser.setId(2);
        User user3 = mapper.selectBySelective(selectUser);
    }
}

可以看到这次查询拿到的是ClientPreparedStatement，即客户端预编译statement对象。

mybatis执行客户端预编译类型sql的过程：

1.mybatis启动时，会将代码中的sql存储为MappedStatement对象，一条sql对应一个。

2.执行某一条查询sql时，获取到sql对应的MappedStatement对象，判断是否预编译类型，如果是，会将"?"符号替换为实际参数值，如果参数值类型为字符串类型，则会对参数拼接两个单引号，再向数据库进行查询。

解释下mybatis的MappedStatement，负责存储我们代码中的sql，一个sql对应一个MappedStatement对象。MappedStatement对象在启动时便进行创建。

mybatis/jdbc使用客户端预编译对象的好处

提高可读性：客户端预编译可以提高 SQL 语句的可读性，因为 SQL 语句中的值用占位符（“?”）替代，可以清晰地看出 SQL 语句的结构和逻辑，而不会被具体的值干扰。
提高安全性：客户端预编译可以提高 SQL 语句的可维护性，因为 SQL 语句中的值用占位符（“?”）替代，可以方便地修改参数值，而不需要修改 SQL 语句本身。

为什么mybatis使用#符号能提高安全性

主要是避免了sql注入问题，我们知道参数可以设置为#{id}或者${id}，说下#{id}为什么能解决sql注入问题。

#符号的转换

SqlSourceBuilder的parse方法会解析出#{}格式的字符串，进行处理

GenericTokenParser的parse方法，这一段代码的意思就是如果当前字符串是#{xxx}的形式，则调用handleToken方法，这里由于是SqlSourceBuilder调用的parse所以调用也是SqlSourceBuilder的handleToken方法

SqlSourceBuilder的handleToken方法，可以看到就是将参数名称存起来，然后返回一个"?"

返回的"?"append进存储sql的stringbuilder中。替换了原来的#{xxx},这也是#{xxx}被替换为"?"的原因

那么替换后的"?"又是在哪里转为实际值的呢

追踪到SimpleExecutor的prepareStatement，这里是对当前查询创建一个statement对象，存储查询信息

进入handler的parameterize方法，追踪调用了DefaultParameterHandler的setParameters方法，可以看到根据参数名，获取到了对应参数，并且调用了typeHandler的setParameters

继续debug进setNotNullParatemer方法，handler的setParameter方法,PreparedStatement的setxxx方法，PreparedStatementLogger的setColumn方法，调用了mysql-connect-java包的ClientPreparedQueryBindings类的setInt，设置参数list索引值为当前参数值。在最后查询mysql的时候再将'?'替换为参数值，且只能作为mysql词法中的参数类型。