• 售前

  • 售后

热门帖子
入门百科

使用 SQL Server 过滤索引进步查询语句的性能分析

[复制链接]
伊索谗言 显示全部楼层 发表于 2021-8-14 14:22:58 |阅读模式 打印 上一主题 下一主题

各人好,我是只谈技能不剃头的 Tony 老师。
Microsoft SQL Server 过滤索引(筛选索引)是指基于满意特定条件的数据行进行索引。与全表索引(默认创建)相比,设计精良的筛选索引可以进步查询性能、淘汰索引维护开销并可低沉索引存储开销。本文就给各人先容一下 Microsoft SQL Server 中的过滤索引功能。
在创建过滤索引之前,我们必要了解它的实用场景。
       
  • 在某个字段中只有少量相干值必要查询时,可以针对值的子集创建过滤索引。 比方,当字段中的值大部门为 NULL 而且查询只从非 NULL 值中进行选择时,可以为非 NULL 数据行创建筛选索引。 由此得到的索引与对相同字段定义的全表非聚集索引相比,前者更小且维护开销更低。   
  • 表中含有分类数据行时,可以为一种或多种种别的数据创建筛选索引。 通过将查询范围缩小为表的特定区域,这可以进步针对这些数据行的查询性能。别的,由此得到的索引与全表非聚集索引相比,前者更小且维护开销更低。
我们在创建索引时可以通过一个 WHERE 子句指定必要索引的数据行,从而创建一个过滤索引。比方,对于以下订单表 orders:
  1. CREATE TABLE orders (
  2.   id INTEGER PRIMARY KEY,
  3.   customer_id INTEGER,
  4.   status VARCHAR(10)
  5. );
  6. BEGIN       
  7.   DECLARE @counter INT = 1
  8.   WHILE @counter <= 1000000
  9.   BEGIN
  10.     INSERT INTO orders
  11.     SELECT @counter, (rand() * 100000),
  12.           CASE
  13.             WHEN (rand() * 100)<1 THEN 'pending'
  14.             WHEN (rand() * 100)>99 THEN 'shipped'
  15.             ELSE 'completed'
  16.           END
  17.     SET @counter = @counter + 1
  18.   END  
  19. END;
复制代码
订单表中统共有 100 万个订单,通常绝大部门的订单都处于完成状态。一般环境下,我们只必要针对某个用户未完成的订单进行查询跟踪,因此可以创建一个基于用户编号和状态的部门索引:
  1. CREATE INDEX full_idx ON orders (customer_id, status);
复制代码
然后我们查看以下查询语句的实验筹划:
  1. SET STATISTICS PROFILE ON
  2. SELECT *
  3. FROM orders
  4. WHERE customer_id = 5043
  5. AND status != 'completed';
  6. id    |customer_id|status |
  7. ------+-----------+-------+
  8. 743436|       5043|pending|
  9. 947848|       5043|shipped|
  10. Rows        Executes        StmtText        StmtId        NodeId        Parent        PhysicalOp        LogicalOp        Argument        DefinedValues        EstimateRows        EstimateIO        EstimateCPU        AvgRowSize        TotalSubtreeCost        OutputList        Warnings        Type        Parallel        EstimateExecutions
  11. 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
  12. 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. SELECT ix.name AS "Index name",
  2. SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)"
  3. FROM sys.dm_db_partition_stats AS sz
  4. INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id
  5. AND sz.index_id = ix.index_id
  6. INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
  7. WHERE tn.name = 'orders'
  8. GROUP BY ix.name;
  9. Index name                  |Index size (MB)|
  10. ----------------------------+---------------+
  11. full_idx                    |      26.171875|
  12. PK__orders__3213E83F1E3B8A3B|      29.062500|
复制代码
接下来我们再创建一个部门索引,只包罗未完成的订单数据,从而淘汰索引的数据量:
  1. CREATE INDEX partial_idx ON orders (customer_id)
  2. WHERE status != 'completed';
复制代码
索引 partial_idx 中只有 customer_id 字段,不必要 status 字段。同样可以查看一下索引 partial_idx 占用的空间大小:
  1. SELECT ix.name AS "Index name",
  2. SUM(sz.used_page_count) * 8/1024.0 AS "Index size (MB)"
  3. FROM sys.dm_db_partition_stats AS sz
  4. INNER JOIN sys.indexes AS ix ON sz.object_id = ix.object_id
  5. AND sz.index_id = ix.index_id
  6. INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
  7. WHERE tn.name = 'orders'
  8. GROUP BY ix.name;
  9. Index name                  |Index size (MB)|
  10. ----------------------------+---------------+
  11. full_idx                    |      26.171875|
  12. partial_idx                 |       0.289062|
  13. PK__orders__3213E83F1E3B8A3B|      29.062500|
复制代码
索引只有 0.29 MB,而不是 26 MB,由于绝大多数订单都处于完成状态。
以下查询显式了实用过滤索引时的实验筹划:
  1. SELECT *
  2. FROM orders WITH ( INDEX ( partial_idx ) )
  3. WHERE customer_id = 5043
  4. AND status != 'completed';
  5. Rows        Executes        StmtText        StmtId        NodeId        Parent        PhysicalOp        LogicalOp        Argument        DefinedValues        EstimateRows        EstimateIO        EstimateCPU        AvgRowSize        TotalSubtreeCost        OutputList        Warnings        Type        Parallel        EstimateExecutions
  6. 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
  7. 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
  8. 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
  9. 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. -- 300 ms
  2. SELECT count(*)
  3. FROM orders WITH ( INDEX ( full_idx ) )
  4. WHERE status != 'completed';
  5. -- 10 ms
  6. SELECT count(*)
  7. FROM orders WITH ( INDEX ( partial_idx ) )
  8. WHERE status != 'completed';
复制代码
另外,过滤索引还可以用于实现其他的功能。比方,我们可以将索引 partial_idx 定义为唯一索引,从而实现每个用户只能存在一个未完成订单的束缚。
  1. DROP INDEX partial_idx ON orders;
  2. TRUNCATE TABLE orders;
  3. CREATE UNIQUE INDEX partial_idx ON orders (customer_id)
  4. WHERE status != 'completed';
  5. INSERT INTO orders(id, customer_id, status) VALUES (1, 1, 'pending');
  6. INSERT INTO orders(id, customer_id, status) VALUES (2, 1, 'pending');
  7. SQL 错误 [2601] [23000]: 不能在具有唯一索引“partial_idx”的对象“dbo.orders”中插入重复键的行。重复键值为 (1)。
复制代码
用户必须完成一个订单之后才气继承天生新的订单。
通过以上先容可以看出,过滤索引是一种经过优化的非聚集索引,尤实在用于从特定命据子会合选择数据的查询。
到此这篇关于使用 SQL Server 过滤索引进步查询语句的性能分析的文章就先容到这了,更多相干SQL Server索引进步语句性能内容请搜索草根技术分享以前的文章或继承欣赏下面的相干文章希望各人以后多多支持草根技术分享!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作