sql优化 进来看看

Question

现在我有60W条数据 获取数据很慢 我用了存储过程优化还是很慢
大家给点其他建议看看有什么办法能提高查询效率呢我现在用的SELECT mail_id,mail_IsShow,(select top 1 Buy_Ptype from BuyDetails where buy_mail=mail_id  ) as Ptype,mail_name,mail_erpTime,mail_isold,(select count(0) from BuyDetails where buy_mail=mail_id) as Buy_Num,
                        mail_Country,mail_Cometo,(select top 1 Buy_Time from BuyDetails where buy_mail=mail_id order by Buy_Time desc) as lastTime,(select top 1 GoodsName from BuyDetails join OrderGoods on BuyDetails.Buy_order=OrderGoods.orderid where buy_mail=mail_id  order by BuyDetails.Buy_Time desc) as GoodsName,(select sum(Buy_RMBMoney) from BuyDetails where Buy_mail=mail_id) as sumRMBMoney,
                                (select avg(Buy_RMBMoney) from BuyDetails  where Buy_mail=mail_id) as avgRMBMoney
FROM (SELECT row_number() over(ORDER BY mail_id) as rownum,mail_id,mail_IsShow,(select top 1 Buy_Ptype from BuyDetails where buy_mail=mail_id  ) as Ptype,mail_name,mail_erpTime,mail_isold,(select count(0) from BuyDetails where buy_mail=mail_id) as Buy_Num,
                        mail_Country,mail_Cometo,(select top 1 Buy_Time from BuyDetails where buy_mail=mail_id order by Buy_Time desc) as lastTime,(select top 1 GoodsName from BuyDetails join OrderGoods on BuyDetails.Buy_order=OrderGoods.orderid where buy_mail=mail_id  order by BuyDetails.Buy_Time desc) as GoodsName,(select sum(Buy_RMBMoney) from BuyDetails where Buy_mail=mail_id) as sumRMBMoney,
                                (select avg(Buy_RMBMoney) from BuyDetails  where Buy_mail=mail_id) as avgRMBMoney
    FROM (SELECT  mail_id,mail_IsShow,(select top 1 Buy_Ptype from BuyDetails where buy_mail=mail_id  ) as Ptype,mail_name,mail_erpTime,mail_isold,(select count(0) from BuyDetails where buy_mail=mail_id) as Buy_Num,
                        mail_Country,mail_Cometo,(select top 1 Buy_Time from BuyDetails where buy_mail=mail_id order by Buy_Time desc) as lastTime,(select top 1 GoodsName from BuyDetails join OrderGoods on BuyDetails.Buy_order=OrderGoods.orderid where buy_mail=mail_id  order by BuyDetails.Buy_Time desc) as GoodsName,(select sum(Buy_RMBMoney) from BuyDetails where Buy_mail=mail_id) as sumRMBMoney,
                                (select avg(Buy_RMBMoney) from BuyDetails  where Buy_mail=mail_id) as avgRMBMoney
          FROM Email WHERE 1=1  AND mail_isshow=0 AND mail_Cometo like '%aaaaa%'  AND mail_isold=0 ) as temp Where 1=1  ) as A
    WHERE rownum BETWEEN (1-1)*500+1 and 1*500 ORDER BY mail_id

Answer 1

like '%aaaaa%'可以改成like 'aaaaa%'吗？

Answer 2

这SQL写的。最简单的改法，先不关联其他表，先分页 后关联

Answer 3

(select top 1 Buy_Ptype from BuyDetails where buy_mail=mail_id  ) as Ptype,
(select count(0) from BuyDetails where buy_mail=mail_id) as Buy_Num,
(select top 1 Buy_Time from BuyDetails where buy_mail=mail_id order by Buy_Time desc) as lastTime,
(select top 1 GoodsName from BuyDetails join OrderGoods on BuyDetails.Buy_order=OrderGoods.orderid where buy_mail=mail_id  order by BuyDetails.Buy_Time desc) as GoodsName,
(select sum(Buy_RMBMoney) from BuyDetails where Buy_mail=mail_id) as sumRMBMoney,
(select avg(Buy_RMBMoney) from BuyDetails  where Buy_mail=mail_id) as avgRMBMoney
---------------
类似这样的，都可以在分页后在关联。 用join 的方式

Answer 4

把like去掉，like很耗费资源的，where后面的条件如果能多加，尽量多加几个速度也可以相应的提升

Answer 5

用join on 会出现笛卡尔积的，因为有1个邮件有多个订单，1个订单有多个产品
我用子查询的目的就是避免出现这个问题。但就是速度慢了。

Answer 6

分页的存储过程：
create proc PageNum
@PageSize int, --每页显示条数
@PageIndex int, --当前是第几页
@totalRows int output, --总行数
@totalPages int output --总页数
as
declare @startId int
declare @endId int
set @startId = @PageSize*(@PageIndex-1)+1
set @endId = @startId+@PageSize-1
select @totalRows=COUNT(*) from Books
set @totalPages=@totalRows/@PageSize
if(@totalRows%@PageSize != 0) --如果总行数除每页显示数据量的值不等于0,则总页数得加1
begin
set @totalPages=@totalPages+1
end
declare @IndexTable table(Id int identity(1,1) ,nId int)
insert into @IndexTable select Id from Books
select * from @IndexTable as t ,Books as b where t.Id>=@startId and t.Id<=@endId and t.nId=b.Id
go
 
declare @tr int --总行数
declare @tp int --总页数
exec PageNum 4,2,@tr output ,@tp output
print '这是总页数'+convert(varchar,@tp) --数据转换
print @tr   

Answer 7

建议采用分页。
like，in之类的少用。
嵌套那么多层就不能靠临时表减少点层次嵌套？

Answer 8

我是用的存储过程，只不过用的是row_number() over(ORDER BY mail_id) 没有用count(0) 这样的方式来分页
如果count(0) 这样分页获取数据信息快的话， 能分析下他们2者的优劣之处吗？

Answer 9

大哥不要建议啊 说点实在的嘛 thanks 就像非哥 和 突破自我一样

Answer 10

像这种(select top 1 Buy_Ptype from BuyDetails where buy_mail=mail_id  ) as Ptype应该就是个定值吧
直接赋给个变量，没必要每次都查一遍吧……