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

野性酷女 2022-09-05 05:19 398阅读 0赞

### 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，就解决了问题！为什么呢？

先看下文档怎么解释这个参数：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]  
默认情况下会将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'

实际执行如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]  
可以看到参数为nvarchar（unicode编码）时还需要对输入进行 “计算标量” 等的多余操作！，**并且都是会走索引的**

我们再看下输出的信息，可以看到逻辑读取次数相差很大！所以这是为什么执行慢的原因了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 2]  
若参数比较多时，参数会作为Concatenation串联的输入，比如下面：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 3]

## 当查询id较多时情况呢 ##

下面试验in条件里有300个id的情况  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 5]  
看到虽然都是 索引查找，但nvarchar时，其实查询了50w1次，刚好是表的总数量！！！！这就是查找慢的最终原因了吧，但为什么会导致索引查找的全表查找呢？待思考…

再比较2个执行计划，nvarchar参数的sql后面还多了一个filter，如下所示，会进行CONVERT\_IMPLICIT隐式转换操作！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 6]

## 如何确认上面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进行跟踪  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 7]  
把properties.put(“sendStringParametersAsUnicode”, “true”);改为  
properties.put(“sendStringParametersAsUnicode”, “false”);再执行  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 8]  
可以看到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代码自己进行拼接条件

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]: /images/20220829/280ffdd751d742f2aabf6ff06da87125.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]: /images/20220829/0b3488b4b67341ee93a2d0e3c8bc931a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 2]: /images/20220829/55edd84690014fe8a05eb066488554f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 3]: /images/20220829/11832cab32ff43c79cfcbbbcf7ee0c73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 4]: /images/20220829/9a1491cf5d744bfd89198045dd7016da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 5]: /images/20220829/8d7c1a87d9954a5db44742337f659c85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 6]: /images/20220829/797174a9564f435a92fa449e1e6ca009.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 7]: /images/20220829/ddbdd4137a674812840001c975ffff8e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 8]: /images/20220829/75419942aa834af78aa20d433a40e978.png