sql server的驱动sendStringParameter默认值导致mybatis foreach查询很慢-线上问题详细分析_sql正常执行,foreach执行卡住_ydfind的博客

sql server的驱动sendStringParameter默认值导致mybatis foreach查询很慢-线上问题详细分析

问题说明
相关源码分析
- sendStringParametersAsUnicode参数默认值源码
- sendStringParametersAsUnicode怎么起作用的
为什么以unicode方式发送参数会慢呢？
数据准备
执行sql并分析
当查询id较多时情况呢
如何确认上面2个sql分别对应sendStringParametersAsUnicode=true/false
- java代码
setNString函数源码
终了
出问题的sql，大概这样：
@Select("<script>select id, ...,  from table_name where id in " +
            "<foreach collection='list' open='(' separator=',' close=')' item='item'>#{item}</foreach> " +
            "</script>")
    List<Data> getList(List<String> ids);
当时有600个id进行拼接，花了将近1分钟； 
若改为每200个id进行拼接，分3次执行，花了20s左右； 
若600个改为java代码拼接，不适用mybatis的foreach，则结果在秒级别！！！ 
哪什么原因导致mybatis foreach的sql执行这么慢呢？ 
最后连接数据库的参数多加sendStringParametersAsUnicode=false，就解决了问题！为什么呢？ 
先看下文档怎么解释这个参数：
 
 默认情况下会将String转为Unicode发送到服务器执行，而我们定义id的类型为varchar(64), 为了获得更好的性能，可以设置为false。 
相关源码分析
 
sendStringParametersAsUnicode参数默认值源码
 
package com.microsoft.sqlserver.jdbc;
enum SQLServerDriverBooleanProperty {
    // 我们可以看到这个参数定义了一个枚举，默认为true
    SEND_STRING_PARAMETERS_AS_UNICODE("sendStringParametersAsUnicode", true),
// 而在package com.microsoft.sqlserver.jdbc.SQLServerConnection初始化中会将sendStringParametersAsUnicode 设置为默认的true
SQLServerConnection(String parentInfo) throws SQLServerException {
        this.sendStringParametersAsUnicode = SQLServerDriverBooleanProperty.SEND_STRING_PARAMETERS_AS_UNICODE.getDefaultValue();
我们可以看到默认值是true 
sendStringParametersAsUnicode怎么起作用的
 
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement#setString(int, java.lang.String) 
public final void setString(int index, String str) throws SQLServerException {
        if (loggerExternal.isLoggable(Level.FINER)) {
            loggerExternal.entering(this.getClassNameLogging(), "setString", new Object[]{index, str});
        this.checkClosed();
        // 在这里会设置参数的值
        this.setValue(index, JDBCType.VARCHAR, str, JavaType.STRING, false);
        loggerExternal.exiting(this.getClassNameLogging(), "setString");
最后会调用到com.microsoft.sqlserver.jdbc.Parameter#setValue 
void setValue(JDBCType jdbcType, Object value, JavaType javaType, StreamSetterArgs streamSetterArgs, Calendar calendar, Integer precision, Integer scale, SQLServerConnection con, boolean forceEncrypt, SQLServerStatementColumnEncryptionSetting stmtColumnEncriptionSetting, int parameterIndex, String userSQL, String tvpName) throws SQLServerException {
        MessageFormat form;
        Object[] msgArgs;
        // sendStringParametersAsUnicode为true，且STRING类型的时候，会修改jdbcType 
        if (con.sendStringParametersAsUnicode() && (JavaType.STRING == javaType || JavaType.READER == javaType || JavaType.CLOB == javaType || JavaType.OBJECT == javaType)) {
                jdbcType = getSSPAUJDBCType(jdbcType);
sendStringParametersAsUnicode为true，且STRING类型的时候，会修改jdbcType，我们继续看getSSPAUJDBCType 
private static JDBCType getSSPAUJDBCType(JDBCType jdbcType) {
        switch(jdbcType) {
        case CHAR:
            return JDBCType.NCHAR;
         // 会将VARCHAR当作NVARCHAR处理
        case VARCHAR:
            return JDBCType.NVARCHAR;
        case LONGVARCHAR:
            return JDBCType.LONGNVARCHAR;
        case CLOB:
            return JDBCType.NCLOB;
        default:
            return jdbcType;
上面很明显会将VARCHAR当作NVARCHAR处理，即程序处理时把jdbcType变量的值修改了，最后把String转为Unicode发给服务器了。 
故设置为false，还是会当成VARCHAR处理，而我们数据库也是定义为varchar的，若有索引，查找起来会很快！！！ 
为什么以unicode方式发送参数会慢呢？
 
drop table op_demo_data
create table op_demo_data (
	id varchar(64) primary key not null,
	pv bigint not null,
	click bigint not null
declare @id varchar(64), @curid int
set @curid = 0;
while @curid <= 500000 
begin
	set @id = RIGHT('00000'+CONVERT(VARCHAR(10),@curid),8)
	insert into op_demo_data(id, pv, click) values(@id, 1, 1)
	set @curid = @curid + 1
-- 为了让测试结果更明显，需要加上下面这一行
update op_demo_data set id = '1' + id 
执行sql并分析
 
-- jdbcType=VARCHAR, 即sendStringParametersAsUnicode=false时的sql如下
exec sp_executesql N'select id, pv, click from op_demo_data where id in (@P0)        ',N'@P0 nvarchar(4000)',N'1'
-- NVARCHAR，即为true时如下：
exec sp_executesql N'select id, pv, click from op_demo_data where id in (@P0)        ',N'@P0 varchar(8000)','1'
实际执行如下：
 
 可以看到参数为nvarchar（unicode编码）时还需要对输入进行 “计算标量” 等的多余操作！，并且都是会走索引的 
我们再看下输出的信息，可以看到逻辑读取次数相差很大！所以这是为什么执行慢的原因了
 
 若参数比较多时，参数会作为Concatenation串联的输入，比如下面：
  
当查询id较多时情况呢
 
下面试验in条件里有300个id的情况
 
 
 看到虽然都是 索引查找，但nvarchar时，其实查询了50w1次，刚好是表的总数量！！！！这就是查找慢的最终原因了吧，但为什么会导致索引查找的全表查找呢？待思考… 
再比较2个执行计划，nvarchar参数的sql后面还多了一个filter，如下所示，会进行CONVERT_IMPLICIT隐式转换操作！
  
如何确认上面2个sql分别对应sendStringParametersAsUnicode=true/false
 
java代码
 
package com.ydfind;
import net.minidev.json.JSONArray;
import org.junit.Test;
import java.sql.*;
import java.util.*;
public class SQLServerTest {
    @Test
    public void testJdbc() throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // 2.加载驱动
        String url = "jdbc:sqlserver://localhost:1433;";
        String username = "sa";
        String password = "12345678";
        Properties properties = new Properties();
        properties.put("user", username);
        properties.put("password", password);
        properties.put("databaseName", "dev-data");
        // sendStringParametersAsUnicode这个参数默认为true
        properties.put("sendStringParametersAsUnicode", "true");
        Connection connect = DriverManager.getConnection(url, properties);
        // 3.sql
        String sql = "select id, pv, click from op_demo_data where id in (?)";
        // 4.statement
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        // 5.设置参数
        preparedStatement.setString(1, "1");
        // 6.执行
        ResultSet resultSet = preparedStatement.executeQuery();
        // 7.解析结果
        List<Map<String, Object>> res = new ArrayList<>();
        while (resultSet.next()) {
            Map<String, Object> map = new HashMap<>();
            map.put("id", resultSet.getString("id"));
            map.put("pv", resultSet.getLong("pv"));
            map.put("click", resultSet.getLong("click"));
            res.add(map);
        // 8.打印结果
        System.out.println("result = " + JSONArray.toJSONString(res));
执行，并在sql server profiler进行跟踪
 
 把properties.put(“sendStringParametersAsUnicode”, “true”);改为
 properties.put(“sendStringParametersAsUnicode”, “false”);再执行
 
 可以看到sendStringParametersAsUnicode为true和false的情况分别对应我们上面给出的sql。也可以使用上面的代码debug之前的源码分析。 
setNString函数源码
 
public final void setNString(int parameterIndex, String value) throws SQLException {
        if (loggerExternal.isLoggable(Level.FINER)) {
            loggerExternal.entering(this.getClassNameLogging(), "setNString", new Object[]{parameterIndex, value});
        this.checkClosed();
        // 可以看到是将参数当作NVARCHAR
        this.setValue(parameterIndex, JDBCType.NVARCHAR, value, JavaType.STRING, false);
        loggerExternal.exiting(this.getClassNameLogging(), "setNString");
可以看到是将参数当作NVARCHAR，即相当于sendStringParametersAsUnicode=true时String的默认处理！
 因此文档里也提到 
应用程序应将sendStringParametersAsUnicode属性设置为“false”并使用SQLServerPreparedStatement和SQLServerCallableStatement类的 setString、setCharacterStream 和 setClob 非国家字符方法
数据库类型为varchar时，为了提高性能，将sendStringParametersAsUnicode设置为false，并不能万事大吉，还需要用setString才行，若上面代码改为： 
		properties.put("sendStringParametersAsUnicode", "false");
        // 5.设置参数
//        preparedStatement.setString(1, "1");
		preparedStatement.setNString(1, "1");
sql server profiler里得到的sql还是sendStringParametersAsUnicode=true的情况！！！ 
exec sp_executesql N'select id, pv, click from op_demo_data where id in (@P0)        ',N'@P0 nvarchar(4000)',N'1'
数据库里varchar类型一般用来保存非中文字符，可以开启sendStringParametersAsUnicode=false；若自己创建PreparedStatement，注意setString和setNString的区别！ 
从实际的执行计划来看，并不是因为索引失效导致查询慢，即Unicode的参数发送方式，还是会走索引的。 
但随着in里id增多，nvarchar时，索引查找的数量 = 表的行数！！！原因待找中… 实际执行计划步骤看起来基本一样，但结果却相差很大 
其它解决方案：
 1）把varchar改为nvarchar
 2）用java代码自己进行拼接条件
                    sql server的驱动sendStringParameter默认值导致mybatis foreach查询很慢-线上问题详细分析问题说明源码说明sendStringParametersAsUnicode参数默认值源码sendStringParametersAsUnicode怎么起作用的问题重新数据准备代码执行问题说明出问题的sql，大概这样：@Select("&lt;script&gt;select id, ...,  from table_name where id in " +        
mybatis-plus-sample-quickstart: 快速开始示例
mybatis-plus-sample-quickstart-springmvc: 快速开始示例（Spring MVC版本）
mybatis-plus-sample-reduce-springmvc: 简化掉默认mapper类示例（Spring MVC版本）
mybatis-plus-sample-generator: 代码生成器示例
mybatis-plus-sample-crud: 完整 CRUD 示例
mybatis-plus-sample-wrapper: 条件构造器示例
mybatis-plus-sample-pagination: 分页功能示例
mybatis-plus-sample-active-record: ActiveRecord示例
mybatis-plus-sample-sequence: Sequence示例
mybatis-plus-sample-execution-analysis: Sql执行分析示例
sql语句
<select id="getListByPartIds" resultType="com.dep.pms.entity.dto.ReworkDeviceDetailDTO">
		......
        where ptm.state = 0
        and ppms.id in
				一、问题描述
项目中导入数据时，一次性导入上万条数据，再加上逻辑中对数据的各种校验造成的耗时，所以很容易造成数据库死锁或服务响应超时。之前的插入 sql 语句如下：
insert into test_table(test1, test2, test3, ...)
values
	<foreach collection="list" index="index" item="item" separator=",">
		(#{item.test1}, #{item.test2}, #{item.te
				如果 sendStringParametersAsUnicode 属性设置为“true”，则字符串参数将以 Unicode 格式发送给服务器。
如果 sendStringParametersAsUnicode 属性设置为“false”，则字符串参数将以非 Unicode 格式（例如 ASCII/MBCS 而不是 Unicode）发送给服务器。
sendStringParametersA
				上周五碰到开发的请求协助解决数据预定程序中对单头等几个表检索数据时检索条件尾数是9的数据特别慢。第一时间想到的是否如下几个问题：
1、  数据库相应数据最多；
2、  数据表进行了分区，而相应数据落在的分区性能存在问题；
3、  检索该批记录的会话很多；
但很快排除了以上几种可能，因为后续的调查由如下发现：
1、  查询了相应数据，尾数0-9的数据几乎平均分布；
2、  直接用SQLS...
				一、PreparedStatement
对于需要反复执行相同的sql语句时，Sun公司就为我们提供了另外一种对象：PreparedStatement。用它的好处就是：当数据库见到PreparedStatement的sql语句时，数据库端直接先到数据库缓冲区当中找它，如找不到，则会编译它一次。如能找到，就直接用。而对于Statement对象，每执行一次，都需要把SQL语句传输到数据库端，数据库要对每一次来的SQL语句进行编译处理。
PreparedStatement和Statement同时插入
				在使用Spring Boot与MyBatis-Plus连接SQL Server时，可能会遇到以下问题：
1. 驱动问题：首先需要引入SQL Server的JDBC驱动，可以在pom.xml文件中添加依赖。常用的驱动有`com.microsoft.sqlserver.jdbc.SQLServerDriver`和`net.sourceforge.jtds.jdbc.Driver`。确保驱动版本与SQL Server版本兼容。
2. 数据库连接配置：在application.properties或application.yml文件中，需配置SQL Server的连接信息，包括数据库地址、端口、用户名、密码等。注意要正确配置驱动名称、连接URL以及字符集等。
3. 数据库版本兼容性：确保使用的SQL Server版本与MyBatis-Plus和JDBC驱动兼容。不同版本的SQL Server可能在SQL语法、特性或驱动接口上有所区别。
4. 数据库表映射：在使用MyBatis-Plus进行ORM映射时，需要在实体类中使用注解配置数据表名、字段名和主键等信息。同时，要确认实体类和数据表的字段类型、长度、精度等匹配。
5. 数据库连接池配置：可以使用Spring Boot提供的连接池技术，如HikariCP或Tomcat连接池。根据并发需求和性能要求，配置合适的连接池大小、最大等待时间和最大空闲时间等参数。
6. 异常处理与日志记录：在连接SQL Server过程中，可能会出现连接异常、语法错误等问题。需要适当处理这些异常，并进行日志记录以便排查和分析问题。
通过解决上述问题，我们可以成功连接SQL Server数据库，并使用MyBatis-Plus进行数据操作和ORM映射。持续的测试和调试可以保证系统的稳定性和性能。