---

前言

数据库和服务器降配之后，系统开始了各种超时，查询超时，保存超时，本身系统逻辑也很复杂，开发了五六年了，各种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;--查看数据库无效索引
SELECTind.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
FROMsys.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
WHEREind.type_desc <> 'HEAP'
AND obj.type <> 'S'
AND OBJECTPROPERTY(obj.object_id,'isusertable'
) = 1
AND (ISNULL(indUsage.user_seeks, 0) = 0AND ISNULL(indUsage.user_scans, 0) = 0AND ISNULL(indUsage.user_lookups, 0) = 0
)
ORDER BYobj.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 > 30AND ind.name IS NOT NULL;PRINT @SQL;
-- 如果需要执行，取消以下注释
-- EXEC sp_executesql @SQL;

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

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