VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >
  • 利用 SQL Server 过滤索引提高查询语句的性能分析

本文就给大家介绍一下 Microsoft SQL Server 中的过滤索引功能,本文通过场景模拟分析给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧

大家好,我是只谈技术不剪发的 Tony 老师。

Microsoft SQL Server 过滤索引(筛选索引)是指基于满足特定条件的数据行进行索引。与全表索引(默认创建)相比,设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。本文就给大家介绍一下 Microsoft SQL Server 中的过滤索引功能。

在创建过滤索引之前,我们需要了解它的适用场景。

  • 在某个字段中只有少量相关值需要查询时,可以针对值的子集创建过滤索引。 例如,当字段中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时,可以为非 NULL 数据行创建筛选索引。 由此得到的索引与对相同字段定义的全表非聚集索引相比,前者更小且维护开销更低。
  • 表中含有分类数据行时,可以为一种或多种类别的数据创建筛选索引。 通过将查询范围缩小为表的特定区域,这可以提高针对这些数据行的查询性能。此外,由此得到的索引与全表非聚集索引相比,前者更小且维护开销更低。

我们在创建索引时可以通过一个 WHERE 子句指定需要索引的数据行,从而创建一个过滤索引。例如,对于以下订单表 orders:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

CREATE TABLE orders (

  id INTEGER PRIMARY KEY,

  customer_id INTEGER,

  status VARCHAR(10)

);

 

BEGIN  

  DECLARE @counter INT = 1

  WHILE @counter <= 1000000

  BEGIN

    INSERT INTO orders

    SELECT @counter, (rand() * 100000),

          CASE

            WHEN (rand() * 100)<1 THEN 'pending'

            WHEN (rand() * 100)>99 THEN 'shipped'

            ELSE 'completed'

          END

    SET @counter = @counter + 1

  END

END;

订单表中总共有 100 万个订单,通常绝大部分的订单都处于完成状态。一般情况下,我们只需要针对某个用户未完成的订单进行查询跟踪,因此可以创建一个基于用户编号和状态的部分索引:

1

CREATE INDEX full_idx ON orders (customer_id, status);

然后我们查看以下查询语句的执行计划:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SET STATISTICS PROFILE ON

 

SELECT *

FROM orders

WHERE customer_id = 5043

AND status != 'completed';

id    |customer_id|status |

------+-----------+-------+

743436|       5043|pending|

947848|       5043|shipped|

 

Rows    Executes    StmtText    StmtId  NodeId  Parent  PhysicalOp  LogicalOp   Argument    DefinedValues   EstimateRows    EstimateIO  EstimateCPU AvgRowSize  TotalSubtreeCost    OutputList  Warnings    Type    Parallel    EstimateExecutions

2   1   SELECT * FROM [orders] WHERE [customer_id]=@1 AND [status]<>@2    1   1   0   NULL    NULL    NULL    NULL    1.405213    NULL    NULL    NULL    0.003283546 NULL    NULL    SELECT  0   NULL

2   1     |--Index Seek(OBJECT:([hrdb].[dbo].[orders].[full_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] < 'completed' OR [hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] > 'completed') ORDERED FORWARD) 1   2   1   Index Seek  Index Seek  OBJECT:([hrdb].[dbo].[orders].[full_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] < 'completed' OR [hrdb].[dbo].[orders].[customer_id]=(5043) AND [hrdb].[dbo].[orders].[status] > 'completed') ORDERED FORWARD  [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] 1.405213    0.003125    0.0001585457    27  0.003283546 [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] NULL    PLAN_ROW    0   1

输出结果显示查询利用索引 full_idx 扫描查找所需的数据。

我们可以查看一下索引 full_idx 占用的空间大小:

1

2

3

4

5

6

7

8

9

10

11

12

13

SELECT ix.name AS "Index name",

SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)"

FROM sys.dm_db_partition_stats AS sz

INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id

AND sz.index_id = ix.index_id

INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id

WHERE tn.name = 'orders'

GROUP BY ix.name;

 

Index name                  |Index size (MB)|

----------------------------+---------------+

full_idx                    |      26.171875|

PK__orders__3213E83F1E3B8A3B|      29.062500|

接下来我们再创建一个部分索引,只包含未完成的订单数据,从而减少索引的数据量:

1

2

CREATE INDEX partial_idx ON orders (customer_id)

WHERE status != 'completed';

索引 partial_idx 中只有 customer_id 字段,不需要 status 字段。同样可以查看一下索引 partial_idx 占用的空间大小:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

SELECT ix.name AS "Index name",

SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)"

FROM sys.dm_db_partition_stats AS sz

INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id

AND sz.index_id = ix.index_id

INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id

WHERE tn.name = 'orders'

GROUP BY ix.name;

 

Index name                  |Index size (MB)|

----------------------------+---------------+

full_idx                    |      26.171875|

partial_idx                 |       0.289062|

PK__orders__3213E83F1E3B8A3B|      29.062500|

索引只有 0.29 MB,而不是 26 MB,因为绝大多数订单都处于完成状态。

以下查询显式了适用过滤索引时的执行计划:

1

2

3

4

5

6

7

8

9

10

SELECT *

FROM orders WITH ( INDEX ( partial_idx ) )

WHERE customer_id = 5043

AND status != 'completed';

 

Rows    Executes    StmtText    StmtId  NodeId  Parent  PhysicalOp  LogicalOp   Argument    DefinedValues   EstimateRows    EstimateIO  EstimateCPU AvgRowSize  TotalSubtreeCost    OutputList  Warnings    Type    Parallel    EstimateExecutions

2   1   SELECT *   FROM orders WITH ( INDEX ( partial_idx ) )  WHERE customer_id = 5043  AND status != 'completed'  1   1   0   NULL    NULL    NULL    NULL    1.124088    NULL    NULL    NULL    0.03279812  NULL    NULL    SELECT  0   NULL

2   1     |--Nested Loops(Inner Join, OUTER REFERENCES:([hrdb].[dbo].[orders].[id]))    1   2   1   Nested Loops    Inner Join  OUTER REFERENCES:([hrdb].[dbo].[orders].[id])   NULL    1.124088    0   4.15295E-05 24  0.03279812  [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id], [hrdb].[dbo].[orders].[status] NULL    PLAN_ROW    0   1

2   1          |--Index Seek(OBJECT:([hrdb].[dbo].[orders].[partial_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043)) ORDERED FORWARD)   1   3   2   Index Seek  Index Seek  OBJECT:([hrdb].[dbo].[orders].[partial_idx]), SEEK:([hrdb].[dbo].[orders].[customer_id]=(5043)) ORDERED FORWARD, FORCEDINDEX    [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id] 9.935287    0.003125    0.0001679288    15  0.003292929 [hrdb].[dbo].[orders].[id], [hrdb].[dbo].[orders].[customer_id] NULL    PLAN_ROW    0   1

2   2          |--Clustered Index Seek(OBJECT:([hrdb].[dbo].[orders].[PK__orders__3213E83F1E3B8A3B]), SEEK:([hrdb].[dbo].[orders].[id]=[hrdb].[dbo].[orders].[id]) LOOKUP ORDERED FORWARD)  1   5   2   Clustered Index Seek    Clustered Index Seek    OBJECT:([hrdb].[dbo].[orders].[PK__orders__3213E83F1E3B8A3B]), SEEK:([hrdb].[dbo].[orders].[id]=[hrdb].[dbo].[orders].[id]) LOOKUP ORDERED FORWARD, FORCEDINDEX [hrdb].[dbo].[orders].[status]  1   0.003125    0.0001581   16  0.02946366  [hrdb].[dbo].[orders].[status]  NULL    PLAN_ROW    0   9.935287

我们比较通过 full_idx 和 partial_idx 执行以下查询的时间:

1

2

3

4

5

6

7

8

9

-- 300 ms

SELECT count(*)

FROM orders WITH ( INDEX ( full_idx ) )

WHERE status != 'completed';

 

-- 10 ms

SELECT count(*)

FROM orders WITH ( INDEX ( partial_idx ) )

WHERE status != 'completed';

另外,过滤索引还可以用于实现其他的功能。例如,我们可以将索引 partial_idx 定义为唯一索引,从而实现每个用户只能存在一个未完成订单的约束。

1

2

3

4

5

6

7

8

9

10

DROP INDEX partial_idx ON orders;

TRUNCATE TABLE orders;

 

CREATE UNIQUE INDEX partial_idx ON orders (customer_id)

WHERE status != 'completed';

 

INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending');

 

INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending');

SQL 错误 [2601] [23000]: 不能在具有唯一索引“partial_idx”的对象“dbo.orders”中插入重复键的行。重复键值为 (1)。

用户必须完成一个订单之后才能继续生成新的订单。

通过以上介绍可以看出,过滤索引是一种经过优化的非聚集索引,尤其适用于从特定数据子集中选择数据的查询。

到此这篇关于利用 SQL Server 过滤索引提高查询语句的性能分析的文章就介绍到这了

 

原文:https://www.jb51.net/article/217440.htm

相关教程