C#+EF+SqlServer性能优化笔记

前言

数据库和服务器降配之后，系统开始了各种超时，查询超时，保存超时，本身系统逻辑也很复杂，开发了五六年了，各种shi代码，慢sql，以下是一些经验的记录，便于以后忘了可以看看，也希望能帮到你！

一、C#EF 代码优化

1.接口代码改异步

代码（示例）：

[ResponseType(typeof(object)), Route("api/OpenApi/AddDataApi"), HttpPost]
public async Task<IHttpActionResult> AddDataApi(RuqestModel model)
{
	if(model == null)
	{
		return BadRequest();
	}
	var res = await AddDataAsync(model);//增加await，调用异步方法
	
	return OK(res);
}

private async Task<int> AddDataAsync(DbContext db, Tables model, long currentId)
{
    db.Tables.Add(model);
	await db.SaveChangesAsync();//增加await，调用异步保存方法
	return 1;
}

2.查询异步，只查询需要的数据

代码（示例）：

//1、异步方法可以提示效率
//2、通过select方法只查询Value字段，在大数据量的情况下，减少了传输的数据，可以提示效率
var Config = await db.MdmConfigs.Where(x => x.IsDeleted == false && x.Code == "Code1").Select(x => new { x.Value }).FirstOrDefaultAsync();

3.查询数据判断时

代码（示例）：

//1、判断数据是否存在时
var Config = await db.MdmConfigs.Where(x => x.IsDeleted == false && x.Code == "Code1").FirstOrDefaultAsync();

if(Config == null)
{
	return BadRequest();
}
//可以优化为：
var IsExist= await db.MdmConfigs.AnyAsync(x => x.IsDeleted == false && x.Code == "Code1");

if(!IsExist)
{
	return BadRequest();
}

4.直接使用sql查询

代码（示例）：

//复杂查询可以直接使用sql查询，下面的sql是简写
var config = db.Database.SqlQuery<MdmConfig>("SELECT * FROM MdmConfig WHERE IsDeleted = 0 and Code = 'Code1'").FirstOrDefault();

二、数据库优化

1.减少关联表，一些基础数据，字典表可以考虑放到redis中，在代码中映射

2.增加索引，删除无效索引，查看索引碎片率，重建索引

代码（示例）：

--查看表索引详情
EXEC sp_helpindex 'YourSchema.YourTableName';

--查看数据库缺失索引
USE DbName
GO
SELECT 
    mid.statement AS TableName,
    mid.equality_columns AS EqualityColumns,
    mid.inequality_columns AS InequalityColumns,
    mid.included_columns AS IncludedColumns,
    migs.user_seeks AS UserSeeks,
    migs.user_scans AS UserScans,
    migs.avg_user_impact AS AvgUserImpact,
    migs.avg_total_user_cost AS AvgTotalUserCost,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.OBJECT_ID) + '_' + CAST(mid.index_handle AS VARCHAR(10)) + 
    ' ON ' + mid.statement + 
    ' (' + ISNULL(mid.equality_columns, '') + 
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + 
    ISNULL(mid.inequality_columns, '') + ')' + 
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS CreateIndexStatement
FROM 
    sys.dm_db_missing_index_details AS mid
JOIN 
    sys.dm_db_missing_index_groups AS mig ON mid.index_handle = mig.index_handle
JOIN 
    sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle = migs.group_handle
WHERE 
  mid. statement  LIKE '%DbName%' -- 确保只针对当前数据库运行
ORDER BY 
    migs.avg_user_impact DESC;
    

--查看数据库无效索引
SELECT
	ind.index_id,
	obj.name AS TableName,
	ind.name AS IndexName,
	ind.type_desc,
	indUsage.user_seeks,
	indUsage.user_scans,
	indUsage.user_lookups,
	indUsage.user_updates,
	indUsage.last_system_seek,
	indUsage.last_user_scan,
	'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand
FROM
	sys.indexes AS ind
INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id
LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id
AND ind.index_id = indUsage.index_id
WHERE
	ind.type_desc <> 'HEAP'
AND obj.type <> 'S'
AND OBJECTPROPERTY(
	obj.object_id,
	'isusertable'
) = 1
AND (
	ISNULL(indUsage.user_seeks, 0) = 0
	AND ISNULL(indUsage.user_scans, 0) = 0
	AND ISNULL(indUsage.user_lookups, 0) = 0
)
ORDER BY
	obj.name,
	ind.name


------------查看索引碎片率并大于30%
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
    ind.name AS IndexName,
    indexstats.index_type_desc AS IndexType,
    indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
    INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent >30 ------------碎片率
ORDER BY indexstats.avg_fragmentation_in_percent DESC


--重新组织索引（把第1步里的表名TableName复制进来，并调整阈值）
ALTER INDEX ALL ON [TableName] 
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON); 

--查看索引碎片率并大于30%并进行整理
DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT 
    @SQL += 'ALTER INDEX [' + ind.name + '] ON [' + OBJECT_NAME(ind.OBJECT_ID) + '] REBUILD WITH (ONLINE=ON, FILLFACTOR=90);' + CHAR(13)
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN 
    sys.indexes AS ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id
WHERE 
    indexstats.avg_fragmentation_in_percent > 30
    AND ind.name IS NOT NULL;

PRINT @SQL;
-- 如果需要执行，取消以下注释
-- EXEC sp_executesql @SQL;

3.更深层可以参考这篇文章：

数据库优化 ——关于tempdb系统库影响IO和数据库性能的一些问题