SQL性能优化之分页

讨论一下无主键分页问题。

还是以实际业务场景为例：

后台有一张存货编码Inventory，注意该表为基础档案表，涉及的控制字段极多，且由于历史原因设置的料品编码为主键

首先查询一下数据量

 

 

 数据量才不到两万，但是分页查询请求已经超时，后台抓一下sql语句，如下：

select * from (select row_number() over(order by pubufts desc) n, * from (select a.cInvCode,a.cInvName,a.cInvStd,a.cComUnitCode,a.bExpSale,a.pubufts,a.bInvQuality,a.bInvBatch,a.iMassDate,a.bPurchase,a.bSelf,a.bComsume,a.bProducing,a.bSale,a.bBomSub,b.cComUnitName,BInv.cLotPOVType,iValidDays
FROM dbo.Inventory a
left join ComputationUnit b on b.cComunitCode=a.cComUnitCode
left join KL_InvLotParam as BInv on a.cInvLotParamCode = BInv.cInvLotParamCode) as t ) as t where n >= 1 and n <= 30

反复执行后依然需要十多秒的时间，接下来看一下执行计划：

 

 

 发现主要开销在于排序Sort和整表检索Scan，再来看看sql语句，发现使用row_number使用pubufts排序，这个字段是时间戳字段，格式如下：0x0000000004450C91

 

 

再试试把排序换成有索引的主键cInvCode，查询速度不到一秒。

再次使用新的方法测试，sql如下：

SELECT * FROM Inventory 

ORDER BY cInvCode ASC OFFSET 0 ROWS FETCH NEXT 30 ROWS ONLY;

速度也正常，0.3s,90%的开销在查找，理论上再执行分页操作时，OFFSET比row_number要高的多。

但是切记OFFSET需要在排序下执行，下面是对于各个关键词的解释：

OFFSET子句：用于指定跳过的数据行；
FETCH子句：该子句在OFFSET子句之后执行，表示在跳过指定数量的数据行之后，返回一定数据量的数据行；
执行顺序：OFFSET子句必须在Order By 子句之后执行，FETCH子句必须在OFFSET子句之后执行；

 

虽然只是一个索引导致的简单问题，但是确实没想到效率会差这么多。

以后有更好的方案再继续补充吧