sqlserver 高级查询(利用 SQL Server 过滤索引提高查询语句的性能分析)
sqlserver 高级查询
利用 SQL Server 过滤索引提高查询语句的性能分析大家好,我是只谈技术不剪发的 Tony 老师。
Microsoft SQL Server 过滤索引(筛选索引)是指基于满足特定条件的数据行进行索引。与全表索引(默认创建)相比,设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。本文就给大家介绍一下 Microsoft SQL Server 中的过滤索引功能。
在创建过滤索引之前,我们需要了解它的适用场景。
- 在某个字段中只有少量相关值需要查询时,可以针对值的子集创建过滤索引。 例如,当字段中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时,可以为非 NULL 数据行创建筛选索引。 由此得到的索引与对相同字段定义的全表非聚集索引相比,前者更小且维护开销更低。
- 表中含有分类数据行时,可以为一种或多种类别的数据创建筛选索引。 通过将查询范围缩小为表的特定区域,这可以提高针对这些数据行的查询性能。此外,由此得到的索引与全表非聚集索引相比,前者更小且维护开销更低。
我们在创建索引时可以通过一个 WHERE 子句指定需要索引的数据行,从而创建一个过滤索引。例如,对于以下订单表 orders:
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 万个订单,通常绝大部分的订单都处于完成状态。一般情况下,我们只需要针对某个用户未完成的订单进行查询跟踪,因此可以创建一个基于用户编号和状态的部分索引:
CREATE INDEX full_idx ON orders (customer_id, status);
然后我们查看以下查询语句的执行计划:
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 占用的空间大小:
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|
接下来我们再创建一个部分索引,只包含未完成的订单数据,从而减少索引的数据量:
CREATE INDEX partial_idx ON orders (customer_id) WHERE status != 'completed';
索引 partial_idx 中只有 customer_id 字段,不需要 status 字段。同样可以查看一下索引 partial_idx 占用的空间大小:
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,因为绝大多数订单都处于完成状态。
以下查询显式了适用过滤索引时的执行计划:
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 执行以下查询的时间:
-- 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 定义为唯一索引,从而实现每个用户只能存在一个未完成订单的约束。
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 过滤索引提高查询语句的性能分析的文章就介绍到这了,更多相关SQL Server索引提高语句性能内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
- SQL Server变量
- SQL SERVER存储过程调试
- sql server锁原理(Sql Server 死锁的监控分析解决思路)
- sql设置注释(SqlSever 注释符 单行注释与多行注释)
- 无法启动sql serveragent(SQL Agent服务无法启动的解决方法)
- SQL SERVER与MySQL数据类型的对应关系
- mysql里修改密码命令(MySQL修改账号密码方法大全小结)
- SQL Server中查询CPU占用高的SQL语句
- SQLserver中cube:多维数据集实例详解(SQLserver中cube:多维数据集实例详解)
- 分享mysql设计规范(MySQL 可扩展设计的基本原则)
- c语言获取系统日期函数(C# ling to sql 取多条记录最大时间)
- sql中的条件判断查询(SQL 多条件查询几种实现方法详细介绍)
- mysql 安装阿里云(详解如何在阿里云服务器安装Mysql数据库)
- mysql索引建立及应用(MYSQL创建索引,这些知识应该了解)
- mysql分区表的优缺点(MySQL数据表分区策略及优缺点分析)
- docker怎么创建mysql服务(Docker部署MySQL8集群一主二从的实现步骤)
- 没钱只能吃土(没钱要吃土了幽默短信发朋友圈)
- 今年考高会很难吗(今年高考会考试吗)
- 盘古开天地 他创造了世界,谁创造了盘古 盘古是伏羲吗(盘古开天地他创造了世界)
- 关于队徽 你了解这些么 二(关于队徽你了解这些么)
- 冬天来了手脚冰凉 真不是因为上辈子你是折翼的天使(冬天来了手脚冰凉)
- 0 1 岁婴儿最强作息指南,照着做养出天使宝宝(01岁婴儿最强作息指南)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9