SQL优化

最新推荐文章于 2025-09-06 22:56:13 发布

转载最新推荐文章于 2025-09-06 22:56:13 发布 · 55 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/MicroHeart/p/10797201.html

文章标签：

#数据库

　　最近优化一批接口，之前只优化过MySQL数据库语句，它和SQL SERVER的差别还是很大的，记录一下优化后时间差别最大的两个接口。

接口一：

　　根据条件查询十个分表，SQL语句处理后如下，其中查询条件不止三个，还有一些其他。优化前：75s

select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_0] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_1] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_2] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_3] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_4] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_5] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_6] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_7] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_8] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'
union all
select  out_trade_no,total_fee, liquidator_commission_fee  from [BankPayOrder_4_9] where 1=1 AND username like '%用户名称%' AND total_fee >=10 AND total_fee <=500 AND  Convert(nvarchar(12),addtime,112) between '20190401' and '20190429'

优化：

　　优化点一：如果username字段有索引，但是like后面第一个为"%"，查询也不会走索引。就算username字段没有索引，like后面第一个跟“%”和不是“%”的查询时间差别也很大，所以最好不使用。但是这样会导致需求变更了，导致可能查询不到想要查的。综合一下，另写了一个接口，通过用户名称模糊查询到用户Id，然后使用者选择一个用户，将此用户的Id传入。

　　优化点二：由于查询时，时间段是必传字段，所以在时间字段上加上聚集索引（表之前的结构是另外两个字段订单号、商户ID组成的聚集索引，实际并没有什么用，也发挥不了聚集索引的优点）。这里指出，如果将时间字段设置为非聚集索引，可能SQL SERVER并不走这个索引，只有当查询的时间段很短的时候，才会走时间的非聚集索引。

　　优化点三：设置了时间聚集索引，通过SQL SERVER的执行计划发现，SQL SERVER走的是时间聚集索引扫描(如图一)，导致的原因是Convert将时间装换为字符串格式了。将Convert删除，查询条件改为时间格式，通过执行计划看到此时就走聚集索引查找(如图二)。

　　　　　　　　　　　　　　　　　　　　　　图一

　　　　　　　　　　　　　　　　　　　　　　图二

　　优化后查询时间1天，300ms。由于设置的是时间聚合索引，所以时间的跨度越大，耗时越久，查询一个月的数据，耗时5s左右，因为接口是运营做统计使用，已经到能接受的程度了。

接口二：

　　优化前耗时2s，SQL语句如下：

SELECT
    A.*,
    B.LableName,
    C.Name AS CustomerName,
    D.Company AS FatherName,
    AE.BonusActivityID,
    BO.ActivityName,
    AE.IsBonus 
FROM
    Sys_Agents A
    LEFT JOIN Sys_AgentLable B ON A.AgentLabelID= B.LableID    
    LEFT JOIN AgentExtend AE ON A.AgentsID= AE.AgentsID    
    LEFT JOIN BonusActivities BO ON AE.BonusActivityID= BO.ActivityID   
    LEFT JOIN Agents_Customer C ON A.CustomerID= C.ID    
    LEFT JOIN Sys_Agents D ON A.FatherId= D.AgentsID    
WHERE
    1 = 1 
    and A.Company like '%众恒科技有限公司%'
    AND A.IsOEM=0
    AND A.AgentLevel=1
    AND CONVERT ( nvarchar ( 10 ), A.EndTime, 112 ) >'20190416'
    AND CONVERT ( nvarchar ( 10 ), A.EndTime, 112 ) <='20191222'
    AND A.CustomerID=0 
    AND A.State=1