大坑：隐式类型转换问题

场景

• 有一张10W行的关系表
• 查询列（VARCHAR）已加索引
• 但查询速度很慢，单次查询大约500ms左右

• 自己编写SQL测试，索引成功命中

  偶然发现

• 这时发现两个诡异情况：

  • 1. 该表有两个查询组合，一个最左索引是VARCHAR，一个最左索引是INTEGER，结果INTEGER的查询耗时只有VARCHAR的十分之一（50ms）
  • 1. 在MyBatis中使用参数绑定（预编译PreparedStatement）比字符串直接拼接SQL慢！

       继续追踪

• 这次不再自己编写SQL，而是去SQL Server Profiler中直接抓取执行的SQL

• 执行抓取到的SQL，再次查看执行计划
• 结果发现索引没有生效
• 并且爆出警告：
  • 表达式(CONVERT_IMPLICIT(nvarchar(10),[tankinivrvUAT].[dbo].[user_message_rlt].[nlcid],0)=N’1301146899’)中的类型转换可能会影响查询计划选择中的“SeekPlan”
• 将结果与之前编写的SQL对比后，发现执行计划直接从 Index Seek 降级为 Index Scan

• 追加一些查询条件后，甚至降级为 Table Scan

  问题所在

• 分析警告和SQL差异后，发现关键点：CONVERT_IMPLICIT、N’’

• 一番搜索后发现，SQL Server的JDBC默认是使用Unicode字符串的
• 在执行上的表现为，JDBC发到SQL Server的所有VARCHAR类型都变成了Unicode字符串，N’’
  • 例如：exec sp_execute 4,1597895274919, N’intra-martAPP:10.2.0.47:5200’
• 这时便会涉及SQL Server的另一个概念：隐式类型转换CONVERT_IMPLICIT
• 当SQL表达式中两个数据的类型不一致时，就会根据数据类型优先级进行隐式类型转换！
• 这时就相当于对查询列进行了一次函数处理
• 这么做的结果大家都清楚：直接导致索引失效

  解决方法

1. 将字段类型从VARCHAR改为NVARCHAR
2. 在jdbc连接中指定sendStringParametersAsUnicode=false，指定字符串参数以非Unicode格式发送给SQL Server

   解决方法可能会带来的问题

• 修改字段类型的影响是最好评估的，直接评估一下新旧类型的性能差异就好
• 指定sendStringParametersAsUnicode=false是比较麻烦的
  • 设置后，使用一些非英文数字的字符可能失效（待测试具体case）
  • setNString、setNCharacterStream 和 setNClob 这类方法不会受影响，推断JDBCType.NVARCHAR类型也不会受到影响（待验证）

参考资料

• SQL Server数据类型优先级问题：https://blog.csdn.net/wh_ouyangshuang/article/details/46866161
• 官方文档-数据类型优先级：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms190309(v=sql.105)?redirectedfrom=MSDN?redirectedfrom=MSDN)
• SQLServer-JDBC类型转换：https://docs.microsoft.com/zh-cn/sql/connect/jdbc/understanding-data-type-conversions?view=sql-server-ver15
• sendStringParametersAsUnicode的说明：https://docs.microsoft.com/zh-cn/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15
• JDBC中的语言国际化设计：https://docs.microsoft.com/zh-cn/sql/connect/jdbc/international-features-of-the-jdbc-driver?view=sql-server-ver15
• setSendStringParametersAsUnicode 方法：https://docs.microsoft.com/zh-cn/sql/connect/jdbc/reference/setsendstringparametersasunicode-method-sqlserverdatasource?view=sql-server-ver15