目录
背景
前段时间有同事反馈某个功能查询加上某个条件后就查的特别慢,甚至直接超时了,不加的话查询很快。另外,直接拿 SQL 去数据库查询也是秒出。
和同事确认了这个查询没有其他复杂的逻辑,只能根据经验来分析一下原因。
分析过程
由于 SQL 本身执行很快,放到代码里面就很慢了,那只能先从数据库的历史 SQL 去看看有没有什么发现。
SELECT t.text,
(qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
(qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_logical_writes / qs.execution_count AS avg_writes,
(qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '%具体的表名%'
and qs.creation_time > '2023-12-05 16:00:00'
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
通过这个结果发现了一样的 SQL,并且 cpu time 很长,说明应用中命中的是这个。
(@appkey nvarchar(4000),@stime nvarchar(4000),@etime nvarchar(4000))
select * from ....
WHERE t1.CreateDate>= @stime
and t1.CreateDate<=@etime
and Appkey = @appkey
对比了一下表结构, Appkey 是 varchar(30)
, CreateDate 是 datetime
。
为什么 SQL Server 记录的 SQL 语句都是 nvarchar(4000)
?
简单验证处理一下:
-- 捕获的
exec sp_executesql N'select * ...
WHERE t1.CreateDate>=@stime and t1.CreateDate<=@etime and appkey=@appkey'
,N'@appkey nvarchar(4000),@stime nvarchar(4000),@etime nvarchar(4000)'
,@appkey=N'xxxxx', @stime='2023-12-05 00:00:00', @etime='2023-12-05 23:00:00'
-- 调整类型后的
exec sp_executesql N'select * ...
WHERE t1.CreateDate>=@stime and t1.CreateDate<=@etime and appkey=@appkey'
,N'@appkey varchar(30),@stime datetime,@etime datetime'
,@appkey=N'xxxxx', @stime='2023-12-05 00:00:00', @etime='2023-12-05 23:00:00'
捕获的,耗时 30s+,调整类型后的,耗时 ~1s。
这是一个典型的字段类型不一致导致的性能问题。
确认具体的问题后,就去看代码那里写的有问题了。
StringBuilder builder = new StringBuilder();
DynamicParameters dp = new DynamicParameters();
// ....
if (input.appKey.IsNotNullOrWhiteSpace())
{
builder.Append(" and appkey = @appkey");
dp.Add("@appkey", input.AppKey);
}
dp.Add("@stime", input.StartTime.To24HString());
dp.Add("@etime", input.EndTime.To24HString());
var list =
await conn.QueryPageAsync<xxx>(sql, dp, input.pageIndex, input.pageSize).ConfigureAwait(false);
参数化了,但是没有指定类型和长度。。。真是一股淡淡的忧伤。
if (input.appKey.IsNotNullOrWhiteSpace())
{
builder.Append(" and appkey = @appkey");
dp.Add("@appkey", input.AppKey, DbType.AnsiString, ParameterDirection.Input, 30);
}
dp.Add("@stime", input.StartTime.To24HString(), DbType.DateTime);
dp.Add("@etime", input.EndTime.To24HString(), DbType.DateTime);
调整上述后,就正常了。
一些延伸
上面用的是 DbType.AnsiString
, 其中涉及字符串有 DbType 里面有 4 个。
//
// 摘要:
// Specifies the data type of a field, a property, or a Parameter object of a .NET
// data provider.
public enum DbType
{
//
// 摘要:
// A variable-length stream of non-Unicode characters ranging between 1 and 8,000
// characters.
AnsiString = 0,
//
// 摘要:
// A type representing Unicode character strings.
String = 16,
//
// 摘要:
// A fixed-length stream of non-Unicode characters.
AnsiStringFixedLength = 22,
//
// 摘要:
// A fixed-length string of Unicode characters.
StringFixedLength = 23,
// ....
}
对不同的字符串类型我们要怎么选择呢?
通过下面的示例代码,和 sql profile 观察,当我们设置不同的 DbType 时,生成的 sql 参数类型是有差异的。
var dp = new DynamicParameters();
// nvarchar(4000)
dp.Add("appkey0", input.Appkey);
// varchar(30)
dp.Add("appkey1", input.Appkey, DbType.AnsiString, ParameterDirection.Input, 30);
// char(30)
dp.Add("appkey2", input.Appkey, DbType.AnsiStringFixedLength, ParameterDirection.Input, 30);
// nvarchar(30)
dp.Add("appkey3", input.Appkey, DbType.String, ParameterDirection.Input, 30);
// nchar(30)
dp.Add("appkey4", input.Appkey, DbType.StringFixedLength, ParameterDirection.Input, 30);
具体如下:
DbType | 数据库类型 |
---|---|
AnsiString | varchar |
String | nvarchar |
AnsiStringFixedLength | char |
StringFixedLength | nchar |
需要注意的是,对于字符串,如果不做任何指定,将会是 nvarchar(4000),这是一个默认值。
总结
参数化虽然有很多好处,可以避免 sql 注入,缓存执行计划等,但是用的不恰当的话会适得其反。
对于参数化还是要规范使用,指定和数据库一致的类型和长度,避免出现不必然的问题。