在我们的应用程序中,我们有一个网格,用户可以在其中分页大量记录(1000万到2000万).网格支持在多个列(20)中按升序和降序排序.许多值也不是唯一的,因此应用程序也会将id排序为打破平局,以确保行始终显示在同一页面上.例如,如果用户想要按窗口小部件大小排序(从最大的开始),应用程序将生成一个看起来有点像这样的查询：
SELECT TOP 30
* -- (Pretend that there is a list of columns here)
FROM Test
-- WHERE widgetSize > 100
ORDER BY
widgetSize DESC,id ASC
此查询需要大约15秒才能运行(使用缓存数据),主要成本似乎是通过widgetSize排序~1.3m行.为了调整这个查询,我发现如果我添加WHERE子句仅限于最大的widgetSizes(在上面的查询中注释掉),那么查询只需要大约800ms(所有前50,000个结果都有一个小部件大小> 100).
为什么没有WHERE子句的查询这么慢？我已经检查了widgetSize列上的统计信息,它们显示前739行有一个WidgetSize> 506.由于只需要30行,sql服务器是否可以不使用此信息来推断它只需要对小部件大小的行进行排序？
我知道我可以通过在widgetSize和id上添加索引来更快地执行此特定查询,但是此索引仅在此特定方案中有用,并且如果(例如)用户反转排序方向则变得毫无价值.此表包含许多其他列,每个索引都很大(~200mb),因此我无法为每个可能的排序顺序添加索引.
有没有办法让我可以在不为每个可能的排序顺序添加索引的情况下执行这些查询查询？ (用户可以按20列中的任何一个排序)
以下脚本创建上表并使用一些代表性数据填充它.该表比实际表格窄得多,但仍然展示了我所看到的性能.在我的PC上,使用where子句的查询需要大约200ms,而没有where caluse的查询大约需要800ms.
CREATE TABLE Test
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,widgetSize INT NOT NULL
)
CREATE TABLE #Data
(
widgetSize INT NOT NULL,recordCount INT NOT NULL
)
INSERT INTO #Data (widgetSize,recordCount)
VALUES
(40826,1),(30317,(28513,(24255,(20247,(20245,(16445,(15719,(8489,(8486,(4753,(4424,(4409,(3738,(3732,(3725,4),(3691,(3678,(3655,(3653,3),(3575,(3572,(3569,(2919,(2903,(2804,(2795,(2765,(2732,(2731,(2677,(2631,(2624,(2548,(2544,(2531,2),(2516,(2512,(2503,(2502,(2472,(2467,(2460,(2452,(2442,(2439,(2412,(2411,(2405,(2382,(2375,(2348,(2341,(2322,(2321,(2316,(2314,(2291,(2284,(2258,(2251,(2232,(2229,7),(2222,(2204,(2186,(2173,(2145,(2143,(2113,(2110,(2089,(2082,(2080,(2056,(2054,(2052,(2019,(1991,(1900,(1870,(1869,(1856,(1826,(1802,(1792,(1786,(1784,(1781,(1780,(1771,(1758,(1756,(1749,(1742,(1740,(1729,(1728,(1726,(1718,(1717,(1707,(1701,(1696,(1694,(1688,(1679,(1649,(1632,(1621,(1616,(1588,(1584,(1554,(1539,(1525,(1516,(1515,(1476,(1467,(1463,(1406,(1390,(1370,(1350,(1338,(1335,(1326,(1325,(1316,(1315,(1311,(1308,(1305,(1302,(1299,(1298,(1285,(1283,(1282,(1270,(1261,(1255,(1251,(1250,(1242,(1220,(1219,(1217,(1216,(1193,(1190,(1164,(1147,(1137,(1134,(1133,(1128,(1120,(1113,(1105,(1099,6),(1098,(1096,(1095,(1092,(1082,(1061,(1050,(1040,(1007,(987,(966,(960,(954,(952,(951,(950,(924,(923,(917,(916,(907,(902,(900,(896,(892,(889,(879,(876,(874,(868,(861,8),(860,(854,(853,(852,(851,(847,(846,(843,13),(839,(838,(837,(825,(824,(820,(819,(818,5),(817,9),(814,(811,(809,(807,(804,(798,(795,(794,(791,(789,(788,(782,(778,(770,(769,(768,(763,(760,(756,(755,(753,(751,(748,(747,(746,(745,(744,(743,(742,(741,(737,(735,(734,(733,(731,(730,(728,(727,(726,(724,(721,(718,(714,(710,(707,(706,(703,(697,(696,(692,(686,(684,(683,(680,(678,(674,(672,(671,(669,(668,(667,(666,(665,(663,(662,(661,(658,(657,(656,(655,(654,(652,(651,(650,(649,(644,(643,(642,(641,(637,(636,(632,(631,(630,(629,(627,(625,(624,(623,(620,(618,(617,(616,(615,(614,(612,(605,(603,(601,(595,(594,(593,(590,(588,(587,(586,(583,(582,(580,(578,(577,(576,(575,(574,(573,(572,(571,(570,(569,(568,(567,(566,(565,(564,(563,(562,(560,(559,(558,(557,(556,(555,(554,(553,(552,(551,(550,(549,(548,(547,(546,(544,(543,(542,(541,(538,(536,(534,(533,(532,(531,(530,(529,11),(528,(527,(526,(525,(524,(523,(522,(521,(520,(518,12),(517,(515,(514,(513,(511,16),(510,(509,(508,(507,(506,41),(505,(504,(503,(502,(501,(500,(499,(498,(497,(496,10),(495,(494,(493,(492,(491,(490,(489,(488,(487,(486,(485,(484,(483,(482,(481,(480,(479,(478,(477,(476,19),(475,(474,(473,(472,(471,(470,(469,(468,(467,(466,(465,(464,(463,(462,(461,(460,(459,(458,(457,(456,(455,(454,(453,(452,(451,20),(450,51),(449,(448,(447,(446,(445,(444,(443,80),(442,(441,(440,(439,(438,14),(437,58),(436,(435,(434,(433,(432,(431,(430,30),(429,21),(428,(427,18),(426,(425,(424,(423,(422,(421,(420,(419,(418,15),(417,(416,22),(415,(414,(413,(412,(411,(410,68),(409,62),(408,(407,(406,(405,(404,(403,(402,31),(401,24),(400,(399,(398,(397,(396,(395,(394,(393,(392,(391,(390,(389,(388,26),(387,(386,27),(385,23),(384,25),(383,(382,(381,69),(380,(379,34),(378,(377,(376,(375,(374,35),(373,32),(372,43),(371,28),(370,(369,(368,(367,(366,36),(365,45),(364,42),(363,82),(362,(361,33),(360,29),(359,(358,(357,17),(356,(355,(354,(353,(352,(351,(350,(349,(348,(347,(346,(345,(344,(343,(342,(341,(340,(339,(338,(337,(336,(335,(334,(333,(332,(331,(330,(329,(328,(327,(326,50),(325,(324,(323,(322,(321,(320,(319,(318,(317,(316,(315,(314,(313,39),(312,(311,(310,(309,(308,(307,(306,(305,(304,(303,(302,38),(301,47),(300,(299,(298,(297,46),(296,(295,(294,(293,(292,(291,(290,(289,(288,(287,(286,(285,(284,(283,(282,(281,(280,(279,(278,(277,(276,(275,(274,(273,53),(272,(271,(270,(269,(268,(267,40),(266,143),(265,(264,(263,(262,(261,(260,52),(259,96),(258,(257,(256,(255,(254,(253,(252,64),(251,(250,(249,(248,(247,(246,131),(245,108),(244,(243,(242,(241,(240,(239,57),(238,55),(237,(236,(235,(234,(233,(232,(231,(230,(229,(228,(227,(226,(225,54),(224,90),(223,91),(222,60),(221,277),(220,70),(219,(218,(217,100),(216,185),(215,98),(214,(213,(212,(211,77),(210,150),(209,175),(208,(207,199),(206,158),(205,(204,85),(203,129),(202,75),(201,59),(200,73),(199,123),(198,72),(197,155),(196,193),(195,66),(194,119),(193,(192,(191,(190,(189,284),(188,(187,79),(186,118),(185,93),(184,92),(183,194),(182,152),(181,(180,134),(179,(178,121),(177,(176,140),(175,262),(174,159),(173,(172,(171,(170,116),(169,168),(168,297),(167,171),(166,214),(165,474),(164,176),(163,(162,215),(161,310),(160,(159,183),(158,208),(157,377),(156,248),(155,804),(154,452),(153,133),(152,224),(151,826),(150,299),(149,367),(148,427),(147,413),(146,1190),(145,796),(144,450),(143,334),(142,308),(141,707),(140,580),(139,601),(138,403),(137,351),(136,411),(135,547),(134,528),(133,506),(132,306),(131,485),(130,419),(129,832),(128,1034),(127,894),(126,1168),(125,313),(124,787),(123,1079),(122,984),(121,1086),(120,1525),(119,1007),(118,539),(117,1596),(116,1307),(115,2081),(114,1256),(113,2200),(112,1184),(111,535),(110,1404),(109,1219),(108,1675),(107,1765),(106,1784),(105,890),(104,931),(103,1769),(102,1720),(101,1528),(100,1639),(99,1955),(98,1434),(97,979),(96,2295),(95,2516),(94,3043),(93,2972),(92,3493),(91,1873),(90,1047),(89,2228),(88,2328),(87,1804),(86,5243),(85,2256),(84,1602),(83,898),(82,2025),(81,2207),(80,2559),(79,2720),(78,3302),(77,5410),(76,994),(75,2767),(74,3343),(73,3951),(72,4116),(71,6164),(70,2992),(69,2066),(68,18269),(67,13159),(66,13142),(65,7387),(64,8759),(63,4887),(62,1847),(61,10239),(60,6990),(59,8785),(58,8161),(57,10081),(56,4899),(55,1744),(54,9916),(53,8713),(52,9529),(51,8827),(50,10255),(49,6392),(48,2253),(47,9939),(46,12083),(45,12103),(44,12667),(43,19758),(42,9699),(41,5450),(40,26566),(39,41836),(38,48441),(37,49562),(36,71987),(35,32390),(34,7159),(33,179598),(32,158675),(31,132676),(30,151839),(29,139014),(28,632065),(27,7800),(26,259440),(25,215240),(24,170986),(23,157141),(22,167304),(21,20408),(20,11949),(19,267541),(18,208096),(17,174708),(16,156445),(15,153569),(14,73937),(13,73821),(12,310246),(11,231829),(10,179047),(9,145506),(8,133433),(7,108736),(6,73381),(5,84825),(4,86641),(3,86172),(2,87690),(1,148110),(0,7960761),(-1,861),(-2,365),(-3,356),(-4,578),(-5,293),(-6,(-7,414),(-8,748),(-9,113),(-10,782),(-11,705),(-12,711),(-13,915),(-14,(-15,(-16,(-17,(-18,56),(-19,(-20,(-21,(-22,(-23,(-24,(-25,44),(-26,(-27,(-28,(-29,(-30,(-31,(-58,(-59,(-60,(-61,(-64,(-70,(-97,(-145,(-234,(-239,(-240,(-272,(-273,(-274,(-276,(-1094,(-1096,(-1337,(-1341,(-3545,(-3547,(-10962,(-10964,(-255449,(-255470,(-365104,(-365105,1)
DECLARE c CURSOR FOR
SELECT widgetSize,recordCount FROM #Data
OPEN c
DECLARE @widgetSize INT
DECLARE @rowCount INT
FETCH NEXT FROM c INTO @widgetSize,@rowCount
WHILE @@FETCH_STATUS = 0
BEGIN
;WITH cte AS
(
SELECT rowNumber = 1
UNION ALL
SELECT rowNumber + 1
FROM cte
WHERE rowNumber < @rowCount
)
INSERT INTO Test
(
widgetSize
)
SELECT
@widgetSize
FROM cte
OPTION (MAXRECURSION 0)
FETCH NEXT FROM c INTO @widgetSize,@rowCount
END
CLOSE c
DEALLOCATE c
DROP TABLE #Data
CREATE STATISTICS WidgetSize
ON Test (WidgetSize) WITH FULLSCAN
解决方法
这类问题没有神奇的解决方案.为了避免可能昂贵的排序,必须有一个可以提供所请求顺序的索引(并且优化器必须选择使用该索引).如果没有支持索引,最好的sql Server本身就可以在排序结果集之前限制符合条件的行(基于WHERE子句).如果没有WHERE子句,这意味着对表中的所有行进行排序.
I’ve checked the statistics on the widgetSize column and they show that the top 739 rows have a WidgetSize > 506
该语句中的“前739”行可能是指统计直方图中的第一个条目,按RANGE_HI_KEY排序.直方图建立在有序流上(使用排序).没有关于这些行在表中的位置的信息.即使在表扫描中首先遇到这些行,引擎也没有选择,只能完全完成扫描以确保它不会遇到更高排序的值.
As only 30 rows are required can sql server not use this information to deduce that it only needs to sort rows with a widget size which is large?
要查找30个最大的行,sql Server必须检查每一行(符合WHERE子句). sql Server无法选择符合“足够大”的任意“最小值”,即使这样做,也无法在没有适当索引的情况下找到这些行.
实际上,Top N Sort N N = 100确实使用替换策略,其中只有大于当前最小值的传入值被放置在排序缓冲区中,但与从中读取行的成本相比,这是一个小优化.表并将它们传递给排序.
原则上,引擎可以将动态过滤器(在排序缓冲区中存在的当前最小值)下推到表扫描中,以尽早限制行,但是这没有实现.要解决此问题,类似的想法包括在widgetSize的不同值上创建索引视图,其中行数与每个值匹配：
CREATE VIEW dbo.WidgetSizes
WITH SCHEMABINDING
AS
SELECT
T.widgetSize,NumRows = COUNT_BIG(*)
FROM dbo.Test AS T
GROUP BY
T.widgetSize;
GO
CREATE UNIQUE CLUSTERED INDEX CUQ_WidgetSizes_widgetSize
ON dbo.WidgetSizes (widgetSize);
如果相对较少的不同值(如样本数据的情况),则此索引视图将远小于widgetSize上的等效非聚簇索引.然后,可以使用此信息来评估要过滤的最小widgetSize,同时仍然保证将找到至少30行.
第一页
对于30行的第一页,实现如下所示：
DECLARE
@TopRows bigint = 30,@Minimum integer;
SELECT TOP (1)
@Minimum = Filtered.widgetSize
FROM
(
SELECT * FROM
(
SELECT
WS.widgetSize,WS.NumRows,-- sql Server 2012 or later
SumNumRows = SUM(WS.NumRows) OVER (
ORDER BY WS.widgetSize DESC)
FROM dbo.WidgetSizes AS WS WITH (NOEXPAND)
) AS RunningTotal
WHERE
RunningTotal.SumNumRows >= @TopRows
) AS Filtered
ORDER BY
Filtered.SumNumRows ASC;
SELECT TOP (@TopRows)
T.id,T.widgetSize
FROM dbo.Test AS T
WHERE T.widgetSize >= @Minimum
ORDER BY
T.widgetSize DESC,T.id ASC;
执行计划：
这显着改善了执行时间,大部分剩余成本与表扫描和下推过滤器相关联.通过创建非聚簇列存储索引(sql Server 2012以后)可以进一步提高性能：
CREATE NONCLUSTERED COLUMNSTORE INDEX
NCCI_Test_id_widgetSize
ON dbo.Test (id,widgetSize);
在我的笔记本电脑上,在列存储索引上以批处理模式执行扫描和过滤将执行时间从大约300毫秒减少到仅20毫秒：
第一页查询返回的最后一行包含widgetSize = 2903和id = 327：
查找接下来的30行(第2页)只需要对上一个查询进行简单修改：
DECLARE
@TopRows bigint = 30,SumNumRows = SUM(WS.NumRows) OVER (
ORDER BY WS.widgetSize DESC)
FROM dbo.WidgetSizes AS WS WITH (NOEXPAND)
WHERE
-- Added
WS.widgetSize < 2903
) AS RunningTotal
WHERE
RunningTotal.SumNumRows >= @TopRows
) AS Filtered
ORDER BY
Filtered.SumNumRows ASC;
SELECT TOP (@TopRows)
T.id,T.widgetSize
FROM dbo.Test AS T
WHERE
T.widgetSize >= @Minimum
AND
(
-- Added
T.widgetSize < 2903
OR (widgetSize = 2903 AND id > 327)
)
ORDER BY
T.widgetSize DESC,T.id ASC;
这会产生与原始查询的明显扩展相同的结果：
SELECT TOP 30
* -- (Pretend that there is a list of columns here)
FROM Test
WHERE widgetSize < 2903
OR (widgetSize = 2903 AND id > 327)
ORDER BY
widgetSize DESC,id ASC;
使用索引视图和非聚簇列存储索引的查询在25ms内完成,而原始版本则超过2000ms.
传统的索引方案
或者,如果您要创建(最小的,非覆盖的)非聚簇索引以支持最常见的排序请求,那么查询优化器将使用它们来满足TOP(30)查询的可能性非常大.索引压缩可用于最小化这些附加索引的大小.
