翻译(十)——Indexes with Included Columns: Stairway to SQL Server Indexes Level 5

最新推荐文章于 2024-11-09 21:51:58 发布
最新推荐文章于 2024-11-09 21:51:58 发布
阅读量121 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/Angular-JS/p/7919321.html
版权

原文链接:http://www.sqlservercentral.com/articles/Stairway+Series/72276/

Indexes with Included Columns: Stairway to SQL Server Indexes Level 5

By David Durant, 2011/07/13

该系列

本文是阶梯系列的一部分:SQL Server的阶梯索引

索引数据库设计的基础,告诉开发人员使用数据库设计者的意图。 不幸的是索引时往往是后加上的性能问题出现。 这里是一个简单的系列文章,应该让任何数据库专业迅速“加速”前水平介绍聚集和非聚集索引,每个突出以下方面:

  • 总有一个入口在索引表中的每一行(我们指出一个例外规则将在稍后的水平)。 这些条目总在索引键序列。
  • 在聚集索引中,表的索引条目是实际的行。
  • 非聚集索引的条目是独立于数据行; 包括索引键列和书签索引键列映射到实际的价值表的行。

最后的一半以前的句子是正确的,但是不完整。 在这个层次,我们检查选项包括附加列集群指数,包括列。 在6级检查书签操作,我们将看到,SQL Server可能单方面将一些列添加到您的索引。

包括列

非聚集索引的列,但不是索引键的一部分,被称为包括列。 这些列不是关键的一部分,所以不影响索引条目的顺序。 同时,正如我们将看到的,它们会引起较少的开销比键列。

当创建一个非聚集索引,我们指定包含分开列键列; 如例子5.1所示。

CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate
       ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
       INCLUDE (OrderQty, UnitPrice, LineTotal)

例子5.1:创建一个非聚集索引,包括列

在这个例子中,ProductID和ModifiedDate是索引键列,OrderQty,UnitPrice和LineTotal是包含的列。

如果我们不指定了呢包括条款在上面的SQL语句,生成的指数会看起来像这样:

ProductID  ModifiedDate  Bookmark

Page n:

707年2004/07/25 = >
707年2004/07/26 = >
707年2004/07/26 = >
707年2004/07/26 = >
707年2004/07/27 = >
707年2004/07/27 = >
707年2004/07/27 = >
707年2004/07/28 = >
707年2004/07/28 = >
707年2004/07/28 = >
707年2004/07/28 = >
707年2004/07/28 = >
707年2004/07/28 = >

n + 1页:

707年2004/07/29 = >
707年2004/07/31 = >
707年2004/07/31 = >
707年2004/07/31 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >
708年2001/07/01 = >

然而,包括对SQL ServerOrderQty,UnitPrice和LineTotal列,索引是这样的:

:- Search Key Columns -:      :---  Included Columns  ---:     : Bookmark :

ProductID   ModifiedDate      OrderQty    UnitPrice   LineTotal     

Page n - 1:

707 2004/07/29 1 34.99 - 34.99 = >
707 2004/07/31 1 34.99 - 34.99 = >
707 2004/07/31 3 34.99 - 104.97 = >
707 2004/07/31 1 34.99 - 34.99 = >
708 2001/07/01 5 20.19 - 100.95 = >

Pange n:

708 2001/07/01 1 20.19 - 20.19 = >
708 2001/07/01 1 20.19 - 20.19 = >
708 2001/07/01 2 20.19 - 40.38 = >
708 2001/07/01 1 20.19 - 20.19 = >
708 2001/07/01 2 20.19 - 40.38 = >

708 2001/12/01 7 20.19 - 141.33 = >
708 2001/12/01 1 20.19 - 20.19 = >
708 2002/01/01 1 20.19 - 20.19 = >
708 2002/01/01 1 20.19 - 20.19 = >
708 2002/01/01 1 20.19 - 20.19 = >

Page n + 1:

708 2002/01/01 2 20.19 - 40.38 = >
708 2002/01/01 5 20.19 - 100.95 = > 
708 2002/02/01 1 20.19 - 20.19 = >
708 2002/02/01 1 20.19 - 20.19 = >
708 2002/02/01 2 20.19 - 40.38 = >

检查这个指数显示的内容,很明显,索引键列的行命令。 五行产品与修改日期708年1月1日2002(以粗体突出显示),例如,在索引中是连续的,其他的行ProductID/ModifiedDate组合。

你可能会问“为什么甚至包括列? 为什么不简单地添加OrderQty,UnitPrice和LineTotal索引键? “有几个优势在这些列索引中而不是在索引键,如:

  • 列不属于索引键的位置不影响在索引条目。 反过来,这降低了他们的开销在索引中。 例如,如果ProductID或ModifiedDate价值行修改,那么这一行在索引的条目必须搬迁。 但是,如果UnitPricevalue行修改,索引条目仍然需要更新,但它不需要感动。
  • 所需的努力找到一个条目(s)指数更少。
  • 指数的大小将会略小。
  • 索引的数据分布统计信息将更容易维护。

大部分这些优势将更有意义的晚年的水平,当我们观察内部结构的索引和一些额外的信息由SQL Server维护优化查询性能。

决定是否一个索引列索引键的一部分,或只是一个包含列,索引不是最重要的决定你会做。 也就是说,经常出现在列选择但不是在列表在哪里子句的查询最好放置在包含的列索引的一部分。

在成为一个覆盖指数

在四级,我们表示赞同的设计者AdventureWorksdatabase关于他们的决定SalesOrderID/SalesOrderDetailID的聚集索引SalesOrderDetail表。 大多数查询该表将请求数据命令或按销售订单分组号。 然而,一些查询,也许从仓库人员,需要在产品序列的信息。 这些查询将受益于该指数如例子5.1所示。

说明的潜在好处包括列索引,我们将着眼于对SalesOrderDetailtable两个查询,我们将执行三次,如下:

  • 运行1:没有非聚集索引
  • 运行2:使用非聚集索引不包含包含列(只有两个键列)
  • 运行3:使用非聚集索引如清单5.1中定义的

当我们在先前的水平,我们再次使用读取数作为主要的指标,但我们也使用SQL Server Management Studio的“显示实际执行计划”选项来查看每个执行的计划。 这将给我们一个额外的度量:工作量的百分比是花在non-read活动,如匹配相关数据后,读取到内存中。 这给了我们更好的理解查询的总成本。

测试第一个查询:活动产品的总数

我们的第一个查询,如例子5.2所示,是一个提供活动总数按日期为一个特定的产品。

SELECT  ProductID ,

        ModifiedDate ,

        SUM(OrderQty) AS 'No of Items' ,

        AVG(UnitPrice) 'Avg Price' ,

        SUM(LineTotal) 'Total Value'

FROM    Sales.SalesOrderDetail

WHERE   ProductID = 888

GROUP BY ProductID ,

        ModifiedDate ;

例子5.2:“产品”活动总数查询

因为索引可以影响一个查询的性能,而不是结果; 对三种不同的索引方案执行这个查询总收益率以下行设置:

ProductID ModifiedDate  Avg Price   Total Value

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
888 2003-07-01 16 602.346 - 9637.536000
888 2003-08-01 13 602.346 - 7830.498000
888 2003-09-01 19 602.346 - 11444.574000
888 2003-10-01 2 602.346 - 1204.692000
888 2003-11-01 602.346 - 10239.882000
888 2003-12-01 4 602.346 - 2409.384000
888 2004-05-01 10 602.346 - 6023.460000
888 2004-06-01 2 602.346 - 1204.692000

聚合八行输出从三十九ProductID = 888“行每个日期的表给一个输出行,一个或者更多的ProductID = 888的销售。 基本的计划进行我们的测试例子5.3所示。 在运行任何查询之前,确保你运行设置数据输入输出。

IF EXISTS ( SELECT  1

            FROM    sys.indexes

            WHERE   name = 'FK_ProductID_ModifiedDate'

                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 

    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;

GO

 

--RUN 1: Execute Listing 5.2 here (no non-clustered index)

 

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate

ON Sales.SalesOrderDetail (ProductID, ModifiedDate) ;

 

--RUN 2: Re-execute Listing 5.2 here (non-clustered index with no include)

 

IF EXISTS ( SELECT  1

            FROM    sys.indexes

            WHERE   name = 'FK_ProductID_ModifiedDate'

                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 

    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;

GO

 

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate

ON Sales.SalesOrderDetail (ProductID, ModifiedDate)

INCLUDE (OrderQty, UnitPrice, LineTotal) ;

 

--RUN 3: Re-execute Listing 5.2 here (non-clustered index with include)

例子5.3:测试产品”活动总数”查询

所需的相关工作执行查询每个索引方案如表5.1所示。

1:运行

没有非聚集索引

表“SalesOrderDetail”。 扫描数1,逻辑读1238。

非阅读活动:8%。

运行2:

——不包括列建立索引

表“SalesOrderDetail”。 扫描数1,逻辑读131。

非阅读活动:0%。

运行3:

包括列

表“SalesOrderDetail”。 扫描数1,逻辑读3。

非阅读活动:1%。

 

表5.1:运行第一个查询结果三次不同的非聚集索引可用

从这些结果可以看出:

  • 运行1需要一个完整的扫描SalesOrderDetail表; 每一行必须阅读和检查,以确定是否应该参与的结果。
  • 运行2使用非聚集索引快速找到请求的书签仅为39行,但是它必须从表中检索每个单独的行。
  • 跑3在非聚集索引,找到了所需的一切,最有利的序列ModifiedDate在ProductID。 它迅速跃升至第一个请求条目,连续读了39项,做总计算每个条目阅读,并完成。

测试第二个查询:根据日期活动总数

第一个第二个查询是相同的,除了改变在哪里条款。 这次仓库请求基于日期的信息,而不是产品。 我们必须过滤最右边搜索键列,ModifiedDate; 而不是最左边的列,ProductID。 新的查询例子5.4所示。

SELECT  ModifiedDate ,

        ProductID ,

        SUM(OrderQty) 'No of Items' ,

        AVG(UnitPrice) 'Avg Price' ,

        SUM(LineTotal) 'Total Value'

FROM    Sales.SalesOrderDetail

WHERE   ModifiedDate = '2003-10-01'

GROUP BY ModifiedDate ,

        ProductID ;

例子5.4:“活动总数按日期”查询

结果行集,部分，是:

ProductID ModifiedDate  No of Items Avg Price   Total Value

- - - - - - - - - - - - - - - - - - - - - - - - - -
:
:
782 2003-10-01 62 1430.9937 - 86291.624000
783 2003-10-01 72 1427.9937 - 100061.564000
784 2003-10-01 52 1376.994 - 71603.688000
792 2003-10-01 12 1466.01 - 17592.120000
793 2003-10-01 1466.01 - 67436.460000
794 2003-10-01 37 1466.01 - 54242.370000
795 2003-10-01 22 1466.01 - 32252.220000
:
:
(164行受影响)

条款过滤表1492预选赛行; 分组时,产生164行输出。

运行测试,遵循相同的模式如清单5.3所述,但使用新的查询清单5.4。 结果所需的相关工作执行查询每个索引方案如表5.2所示。

1:运行

没有非聚集索引

表“SalesOrderDetail”。 扫描数1,逻辑读1238。

非阅读活动:10%。

运行2:

——不包括列建立索引

表“SalesOrderDetail”。 扫描数1,逻辑读1238。

非阅读活动:10%。

运行3:

包括列

表“SalesOrderDetail”。 扫描数1,逻辑读761。

非阅读活动:8%。

表2:运行第二个查询结果三次不同的非聚集索引可用

第一次和第二次测试导致相同的计划; 一个完整的扫描theSalesOrderDetail表。 原因在四级,详细介绍在哪里条款不足够选择性受益于non-covering指数。 行包括任何一组也分散在桌子上。 表被读,每一行必须匹配组; 和操作处理器时间和内存消耗。

第三个测试发现在非聚集索引所需的一切; 但不像前面的查询,它没有发现内的连续行位于索引。 他行组成每个组内是连续的指数; 但组织本身是分散在索引的长度。 因此,SQL Server扫描索引。

扫描索引的表有两个优点:

  • 该指数小于表,需要更少的读取。
  • 行已经分组,需要更少的非阅读活动。

结论

包括列使非聚集索引成为各种覆盖索引查询,提高这些查询的性能; 有时会很显著。 包括列增加一个索引的大小,但添加其他小的开销。 任何时候你正在创建一个非聚集索引,特别是在一个外键列,问问自己——“我该什么附加列包含在这个索引吗?”

 

转载于:https://www.cnblogs.com/Angular-JS/p/7919321.html

baman1774
关注
sql性能优化：sql Server 怎么添加全文索引+语法深度剖析+测试
橙-极纪元-cplvfx-JJY.Cheng
03-15 3955
sql性能优化：sql Server 怎么添加全文索引+语法深度剖析+测试
Sql Server关于create index include带有包含列的索引的最全解释
lusklusklusk的博客
05-31 2047
官方文档 https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017 在Sq
MySqL数据库监听命令_Mysql数据库监听binlog
weixin_35445090的博客
01-18 2760
前言我们经常需要根据用户对自己数据的一些操作来做一些事情.比如如果用户删除了自己的账号,我们就给他发短信骂他,去发短信求他回来.类似于这种功能,当然可以在业务逻辑层实现,在收到用户的删除请求之后执行这一操作,但是数据库的binlog为我们提供了另外一种操作方法.要监听binlog,需要两步,第一步当然是你的mysql需要开启这一个功能,第二个是要写程序来对日志进行读取.mysql开启binlog....
SQL索引二（查看索引的缺失）
u011791378的专栏
11-23 676
如何查看缺失索引 select * from sys.dm_db_missing_index_details index_handle int 标识特定的缺失索引。 该标识符在服务器中是唯一的。 index_handle是此表的键。 database_id smallint 标...
PostgreSQL 11 新特性解读 : Indexs With Include Columns
weixin_34220834的博客
12-18 434
PostgreSQL 11 版本索引方面一个显著的新特性是创建索引时支持 INCLUDE COLUMNS ，语法如下 CREATE INDEX idx_name ON table_name USING BTREE (column_a) INCLUDE (column_b); 一、Release 中的说明 Allow indexes to INCLUDE ...
缺失索引自动创建语句
weixin_34356310的博客
04-18 164
【编者按】 本文主要介绍使用系统 SQL 实体自动创建非聚集(non-clustered)索引。作者为意大利软件工程师 GhostHost(笔名)。 本文系 OneAPM 工程师编译呈现，以下为正文。 引言 一直以来，关于索引的常见问题是：判断哪部分索引对保证数据库的良好性能是必需的。在本文中，笔者将提供针对该问题的解决方案。本文用例中的...
数据库技术及应用——SQL Server课件 7索引.ppt
01-21
【数据库技术及应用——SQL Server课件 7索引】 在数据库管理中，索引是一种至关重要的技术，特别是在SQL Server这样的关系型数据库管理系统中。索引的作用在于提高数据查询的效率，通过预先组织数据，使得数据库...
SQL Server：如何获取所有包含相关列名称的索引列表
04-02
在SQL Server数据库管理系统中，索引是用于加速数据检索的关键工具。它们可以帮助快速定位到表中的特定行，尤其是在执行查询时。本篇文章将详细介绍如何获取一个SQL Server数据库中所有索引的列表，以及这些索引所...
Transact-SQL元编程——用T-SQL生成T-SQL.pdf
09-19
Transact-SQL元编程是指在数据库编程中，利用Transact-SQL本身来动态生成Transact-SQL语句的过程。这种方法能够使我们编写一次脚本，而这个脚本可以被重复利用，适应不同的数据库结构变化，无需每次手动修改脚本。它...
sqlserver索引优化
星辉Johnson的博客
03-10 1404
/* avg_total_user_cost:减少的用户查询的平均成本 avg_user_impact:户查询可能获得的平均百分比收益 user_scans:扫描次数 user_seeks:查找次数。 equality_columns: 哪个字段缺失了索引(where 后面的筛选字段) included_columns:查询的涵盖列（简单来讲就是 select 后面的字段）。 */
如何新建索引:高开销的缺失索引
yangzhiyong77
02-13 129
索引对查询性能影响极大，不要盲目的新建索引！数据库高开销的缺失索引,根据对动态性能视图的统计信息，新建需要的索引！ select c.name as 库名,c.equality_columns, c.inequality_columns,c.included_columns, c.statement as 表名, c.avg_total_user_cost as 减少的平均成本 , ...
sql server 2008 r2 数据库占用CPU过高的调查－－－缺失索引
zyfzhangyafei的专栏
03-16 1396
sql server 2008 r2 数据库占用CPU过高的调查 使用以下语句 SELECT * FROM sys.[dm_db_missing_index_details] index_handle database_id object_id equality_columns inequality_columns included_columns...
SQL Server索引中的include列
Hehuyi_In的博客
04-29 5646
一、 几个概念 覆盖索引 返回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。 复合索引 复合索引就是包含两个或者多个字段的索引 非键列 键列就是在索引中所包含的列，当然非键列就是该索引之外的列了 二、2005的新特性 在 SQL Serve...
SQLServer2016之后增加了索引列数的限制从16个列增加到了32个列
极客神殿
05-17 958
创建带有包含列的索引 https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017 适用于：是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库 本主题说明如何通过使用 S...
INDEX--索引相关信息查看
weixin_33704591的博客
01-17 157
--============================================== --查看可能缺失的索引 SELECT mig.* ,migs.* ,mid.* FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ...
如何新建索引:高开销的缺失索引 转：
数据库
04-22 141
select c.name as 库名,c.equality_columns, c.inequality_columns,c.included_columns, c.statement as 表名, c.avg_total_user_cost as 减少的平均成本 , c.avg_user_impact as 百分比收益, c.last_user_seek as 使用后影响上次结果,...
07 Oracle数据库恢复基础解析：从检查点到归档，一步步构建数据安全防线
最新发布
超哥的博客
11-09 802
检查点是Oracle数据库中的一个特殊事件，它的作用是将内存中的数据块同步到磁盘上，以确保数据的持久性。简单来说，就是让数据库“记住”到目前为止所有的更改。Redo日志是Oracle用来记录所有数据更改的日志文件。每次对数据库进行修改时，都会先写入Redo日志，然后再写入数据文件。这样，即使系统崩溃，也能通过Redo日志恢复数据。当数据库运行在归档模式下时，一旦当前的Redo日志文件满，Oracle会将其复制到一个安全的存储位置，并标记为已归档。这样可以长期保留Redo日志，便于历史数据的恢复。
Redis - 事务
ketil27的博客
11-08 672
Redis 的事务和MySQL的事务概念上是类似的.都是把⼀系列操作绑定成⼀组.让这⼀组能够批量执 ⾏.弱化的原⼦性:redis没有"回滚机制".只能做到这些操作"批量执⾏".不能做到"⼀个失败就恢复到 初始状态".不保证⼀致性:不涉及"约束". 也没有回滚.MySQL的⼀致性体现的是运⾏事务前和运⾏后,结果都 是合理有效的,不会出现中间⾮法状态.不需要隔离性:也没有隔离级别,因为不会并发执⾏事务(redis单线程处理请求).
Mysql数据库的UDF提权
CoffeMilk的博客
11-05 1436
渗透人员编写可调用系统命令行（CMD或shell）的文件生成动态链接库（Windows系统是.dll；Linux系统是.so），且将该动态链接库内容上传到MySql的函数插件文件夹目录下；然后在通过sql语句调用该动态链接库创建一个自定义函数，最后直接调用该自定义函数sql执行查询系统命令，实现提权（即：在数据库中的查询就等价于直接在系统调用cmd或者shell中执行命令）
深入解析：Microsoft SQL Server 2008 内部机制
5. **内存管理**：SQL Server的内存管理对性能至关重要。书中将介绍缓冲池（buffer pool）、工作集（working set）和内存优化表（memory-optimized tables）的运作方式。 6. **查询执行**：执行计划的执行过程涉及...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值