个人中心
gpt4 book ai didi

利用 SQL Server 过滤索引提高查询语句的性能分析

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章利用 SQL Server 过滤索引提高查询语句的性能分析由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

利用 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 过滤索引提高查询语句的性能分析的文章就介绍到这了,更多相关sql server索引提高语句性能内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！ 。

原文链接：https://blog.csdn.net/horses/article/details/109437027 。

最后此篇关于利用 SQL Server 过滤索引提高查询语句的性能分析的文章就讲到这里了,如果你想了解更多关于利用 SQL Server 过滤索引提高查询语句的性能分析的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： Oracle创建只读账号的详细步骤
文章推荐： 2021夏天气息满分的文案 很简短的关于夏季的朋友圈
文章推荐： 甜到炸的高级情话文案 很撩人的高级句子
文章推荐： SqlServer数据库远程连接案例教程
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com