MsSql:性能优化总结

1.存储过程，多条件，分页查询 ，最优化查询代码

2.如何快速定位锁表以及优化经历

 

 

 

1.存储过程，多条件，分页查询 ，最优化查询代码

create proc DoctorDetailed

@CreaterTime time,--开始时间
@EndTime time,--结束时间
@page int,--页码
@rows int,--页数
@DoctorName Nvarchar(20),--医生名字
 

AS
begin

declare @sql1 nvarchar(400)
declare @sql2 nvarchar(400)
declare @page1 int
declare @rows1 int



 --此处为分页计算

 set @page1=(@page-1)*@rows+1
 set @rows1= @page*@rows

set @sql1 =' SELECT * FROM (select ROW_NUMBER() OVER(ORDER BY '排序字段' desc  ) AS rownum,*
　　　　　　　 from( select * from as '需要查询的内容' ) as A where 1=1'
             
          
      
set @sql2='          
            
     ) AS Dwhere where 1=1 '

--多条件查询，判断是否字符串传进来为空，不为空则加上条件查询
   IF @DoctorName!=''
        BEGIN
            SET @sql1=@sql1+ ' AND Dwhere.条件查询的列名字（例子：Dwhere.DoctorName）='''+@DoctorName+''' '
        END
  --分页条件查询   此处传进来的 页数 需要 转换
   SET @sql2=@sql2+ 'AND  rownum  between '+cast(@page1 as varchar(10))+' and '+cast(@rows1 as varchar(10))+''


 end

 

 

2.如何快速定位锁表以及优化经历

背景:目前业务上,执行某个操作,出现死锁情况

第一步:使用数据库的 Sql service Profile 进行死锁跟踪  https://www.cnblogs.com/for-easy-fast/articles/13026559.html 这里有详细使用技巧

 

 

 

 

 

第二步： Deadlock gaph，会以xml文件 记录死锁的详细信息

 

第三步：根据死锁详细分析得到,业务主表在插入的时候创建了一个触发器, 

　　　　触发器会判断Aid 是否为空,如果为空 根据传进来的Bid  进行条件更新 语句如: update 表 set Aid=guid() where 表.Bid=Bid Bid不是主键Id,也没有索引

第三步：这个更新语句 为什么会发生死锁呢: 

　　　　update 表 set Aid=guid() where 表.Bid=Bid  更新语句是要先查询的

满足死锁的四个必要条件

    1、互斥： 某种资源一次只允许一个进程访问，即该资源一旦分配给某个进程，其他进程就不能再访问，直到该进程访问结束。

    2、占有且等待： 一个进程本身占有资源（一种或多种），同时还有资源未得到满足，正在等待其他进程释放该资源。

    3、不可抢占： 别人已经占有了某项资源，你不能因为自己也需要该资源，就去把别人的资源抢过来。

    4、循环等待： 存在一个进程链，使得每个进程都占有下一个进程所需的至少一种资源。

 

【更新语句在进行字段更新的时候,会先根据这个Bid,扫描数据,  假如有一百条数据, 

在根据Bid,进来一路人甲查询前50条的时候 会锁住前50条，　　　　　　　　　　　1.互斥，2.占有且等待，3.不可抢占

这时候路人乙同时进来(有并发了),也执行相关操作, 查询后面 10条,锁住了这十条数据,1.互斥，2.占有且等待，3.不可抢占

路人甲要查这后面十条,等你路人乙释放,路人乙要查询前面50条,等你路人甲释放 】     4.循环等待

 

以上四个条件都满足了,于是死锁发生

 

解决办法：

给 update 表 set Aid=guid() where 表.Bid=Bid   Bid，加非聚集索引 

因为没加之前,查询速度慢,没查到就不释放数据,                                                                     锁住了所有的查询数据

加了非聚集索引之后,查询速度快, 根据非聚集索引快速定位到这条数据的聚集索引,找到之后 只锁住该条数据

 

 

这时候又一个人进来(有并发了),也执行相关操作,