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

问题说明

出问题的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
end
-- 为了让测试结果更明显，需要加上下面这一行
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代码自己进行拼接条件