回答

收藏

通过在执行计划中删除排序运算符来优化SQL查询

技术问答 技术问答 246 人阅读 | 0 人回复 | 2023-09-14

我刚刚开始研究通过索引优化查询,因为SQL数据正在快速增长。我查看了优化器如何通过SSMS中的执行计划处理查询,并注意到正在使用Sort运算符。我听说排序运算符表示查询中的设计不正确,因为可以通过索引过早地进行排序。因此,这是一个示例表和数据,类似于我正在做的事情:, a$ i4 z6 {6 h/ V( Z
IF OBJECT_ID('dbo.Store') IS NOT NULL DROP TABLE dbo.[Store]
* k7 ]1 C- X/ u: T8 tGO
3 @- f& T) L2 F! m$ dCREATE TABLE dbo.[Store]2 d% d. y" m0 `( q
(
' Z- N& V7 U: g0 L1 `    [StoreId] int NOT NULL IDENTITY (1, 1),; w# S8 i( A: b7 U
    [ParentStoreId] int NULL,
; N# ?1 u. Z' T    [Type] int NULL,
: W. b- N5 t: {/ B1 A: q' ~' N6 W    [Phone] char(10) NULL,  o, W9 n/ i+ G) G8 m
    PRIMARY KEY ([StoreId])
4 Y; e6 [7 B- w- ]5 G)' p# z& \1 v* T4 ]- e8 I, {2 \" E
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '2223334444')
  S! ?5 Y' x4 E$ X: W/ ZINSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 0, '3334445555')! f6 ^# ^- s1 e& v4 B
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '0001112222')# Z+ o3 |7 L9 T1 ^# k+ n) U
INSERT INTO dbo.[Store] ([ParentStoreId], [Type], [Phone]) VALUES (10, 1, '1112223333')6 {$ l' j; N3 R, U
GO7 F/ h9 }, z  W0 ]1 ]
这是一个示例查询:9 E3 t7 g9 M' x  ]- H5 ?
SELECT [Phone]6 u7 B2 ]5 F# L( v! E( w& D; Q
FROM [dbo].[Store]
9 d( v+ N- W8 n7 |4 E# c- x* g; \WHERE [ParentStoreId] = 10: j* y6 W/ g, p. m/ n
AND ([Type] = 0 OR [Type] = 1)
! v+ t, {! p) C4 X5 b. TORDER BY [Phone]% E0 E- y0 x& d# o/ w5 N
我创建了一个非聚集索引来帮助加快查询速度:) R! ]- v0 Y- x: P& s% Y
CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])2 g7 x4 `! k4 Q& H
为了建立IX_Store索引,我从简单的谓词开始" Y" t  m8 M9 ]0 E2 ~6 W
[ParentStoreId] = 10
+ E9 Z9 E" b6 H& m( b# F" pAND ([Type] = 0 OR [Type] = 1)
% @9 I" K2 V9 ~& h/ r然后我[Phone]为ORDER BY添加列并覆盖SELECT输出# j6 n9 G$ C+ e; [+ v
因此,即使在建立索引时,优化器仍会使用Sort运算符(而不是索引排序),因为它[Phone]是在AFTER" ]$ ~5 g* ?3 o
[ParentStoreId]AND之后进行排序的[Type]。如果我[Type]从索引中删除该列并运行查询:' a4 c( y6 O$ b
SELECT [Phone]
. ~% }# s  R5 R4 K6 I: ?1 tFROM [dbo].[Store]
$ K. ?/ D4 U+ @; U3 i% g8 u7 ^WHERE [ParentStoreId] = 10
$ m) ]& T" Y' B7 V  w* v--AND ([Type] = 0 OR [Type] = 1)
9 g' n  H$ Y7 Q7 m& {- x0 }ORDER BY [Phone]
5 G- G8 N1 A1 [5 N& p6 \$ F然后,当然,优化器不使用Sort运算符,因为[Phone]它由排序[ParentStoreId]。& l# B# W9 b/ S0 N
因此,问题是如何创建一个覆盖查询(包括[Type]谓词)并且不让优化器使用排序的索引?9 p3 K* v$ M' J) W+ u# a
编辑:
6 N9 i1 e7 A* Y6 V0 N" n- K$ J我正在使用的表有超过2000万行
* b, L9 L! @: Y: _, [4 ~# X                ! X6 k# g8 N; x/ y. ~# d* {1 S
解决方案:8 K7 P. I: \& S3 K, P5 C
                7 h& B$ N$ A3 @" V' t

, a* e6 Y* I5 b, H, m* g
7 C# W9 H* B0 [# ^: t                首先,您应该验证排序实际上是性能瓶颈。排序的持续时间将取决于要排序的元素的数量,并且特定父存储的存储数量可能很小。(这是假定在应用where子句之后应用了sort运算符)。* X8 t- u' x9 n/ b0 O$ r: O
+ h: q6 K2 p8 j. A  B* z2 {
我听说排序运算符表示查询中的设计不正确,因为可以通过索引过早地进行排序
, p6 ~, Y! i* [, j
5 D. |: M3 F1 V0 P2 C太笼统了。通常,可以将排序运算符平移到索引中,并且,如果仅获取结果集的前几行,则可以大大降低查询成本,因为数据库不再需要获取所有匹配的行(并对它们进行排序)全部)以查找第一个,但可以按结果集顺序读取记录,并在找到足够的记录后停止。
5 K! o' [0 l3 m. A在您的情况下,您似乎正在获取整个结果集,因此排序不太可能使情况变得更糟(除非结果集很大)。另外,在您的情况下,构建有用的排序索引可能并不容易,因为where子句包含or。, Z0 n* }  {( U" Q  M
现在,如果您仍然想摆脱该排序运算符,可以尝试:
9 J3 Z- w0 q7 YSELECT [Phone]
: |& E* I8 d9 W! YFROM [dbo].[Store]5 B, s* \$ G* b2 w& }  ^7 V
WHERE [ParentStoreId] = 10/ C. \; L) X& y4 u- ^
AND [Type] in (0, 1)/ I( h  V% i7 ]+ m( {
ORDER BY [Phone]
; \" T" n, v+ [或者,您可以尝试以下索引:: ?; j; V' s! ]' {( j
CREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Phone], [Type])9 B! ~4 `; R3 u/ I# ^: i
尝试让查询优化器ParentStoreId仅对索引范围进行扫描,然后扫描索引中所有匹配的行,如果Type匹配则将其输出。但是,这可能会导致更多的磁盘I: P! l$ D1 P8 @: t+ P/ D$ T
/ O,从而降低查询速度而不是加快查询速度。! L2 k& r* E5 g! _. N( V* g
编辑 :作为最后的手段,您可以使用" L- R' {' D, y; c# P# L
SELECT [Phone]
$ t" F7 D. g$ t9 b- P% E9 [+ X1 AFROM [dbo].[Store]
! b; X* t2 y/ f  x: NWHERE [ParentStoreId] = 10
' F2 L* s2 C4 {( _5 k) I6 E* uAND [Type] = 0
1 K1 E/ S1 p- L3 x/ R1 I8 d7 S( xORDER BY [Phone]3 u& J3 i0 ^7 q5 H" G9 \
UNION ALL
: a4 K* U. y* ?: cSELECT [Phone]
+ a& y3 s+ d4 ?. n4 M4 p( ]+ cFROM [dbo].[Store]
+ a7 }/ r1 A( u  C. L+ j5 ~" sWHERE [ParentStoreId] = 10- S/ I2 u. \# O' o% N9 z: O
AND [Type] = 16 Z/ f7 D3 G# M# X  Z* X0 v
ORDER BY [Phone]: L4 n# L  |9 z! Y4 E

# I0 |( Z+ L* O4 D- n9 }: RCREATE NONCLUSTERED INDEX IX_Store ON dbo.[Store]([ParentStoreId], [Type], [Phone])
3 L4 d" j3 h, U4 L- E并在应用程序服务器上对这两个列表进行排序,您可以在其中合并(如合并排序)预排序的列表,从而避免进行完整的排序。但这实际上是一个微优化,虽然将排序本身加快了一个数量级,却不太可能对查询的总执行时间产生很大影响,因为我希望瓶颈是网络和磁盘I) m0 o3 }. U7 ~4 m/ _; e
/ O,尤其是考虑到由于索引未聚集,磁盘将执行大量随机访问。
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则