SQL Server索引使用

最新推荐文章于 2024-03-26 10:01:04 发布
最新推荐文章于 2024-03-26 10:01:04 发布
阅读量5.9k 收藏 6
点赞数
分类专栏： SQLServer
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ceclar123/article/details/8465240
版权

一、索引设计注意事项

（1）、检查WHERE条件和连接条件

当一条SQL语句提交时，查询优化器会根据表的信息查询最优的访问机制

1、优化器首先识别WHERE子句与连接条件中包含的列

2、优化器检索这些列上的索引

3、优化器通过从索引上的维护统计来确定子句的选择性以及评估索引的有效性

4、优化器根据前面几个步骤的信息，选择估计开销最低的方式

DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT p.ProductID,p.Name,p.StandardCost,p.Weight,p.Color,p.ProductNumber 
FROM Production.Product AS p
WHERE p.ProductID=871

表 'Product'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。


这里采用的数据库是微软提供的示例数据库AdventureWorks2008。

WHERE条件里面有ProductID，该字段上面有一个聚簇索引(主键)，根据优化器的选择，最终选择该索引


DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT pm.Name,p.ProductID,p.Name,p.StandardCost,p.Weight,p.Color,p.ProductNumber 
FROM Production.Product AS p
INNER JOIN Production.ProductModel AS pm ON pm.ProductModelID=p.ProductModelID
WHERE p.ProductID=871

表 'ProductModel'。扫描计数 0，逻辑读取 2 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Product'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。


 


ProductModelID是ProductModel的主键，也是Product的外键 ，ProductID是Product的主键，查看执行计划从上到下，从右到左。

首先根据ProductID=871查找Product表，这里使用聚簇索引没有问题。

第二部就是ProductModel表，这里根据连接条件来，发现也有可用的聚簇索引，所以上图使用了两个聚簇索引。

 

（2）、使用窄索引

在实际中，一般使用整形列来作为索引列，像VARCHAR、NVARCHAR等可能会很大，一般不用来作为索引列。

窄索引可以在8KB的索引页面容纳更多的行，这样有许多好处

A、减少IO数量（读取更少的8KB页面）

B、是数据库缓存更有效，SQL Server可以缓存更少的索引页面，从而减少内存中索引页面的逻辑读

C、减少数据库存储空间

首先创建一个表：

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='test' AND type='U')
	DROP TABLE dbo.test
GO
CREATE TABLE dbo.test
(
	obj_id INT,
	obj_name VARCHAR(200)
)
GO
INSERT INTO dbo.test(obj_id,obj_name)
SELECT object_id,name FROM sys.objects
GO
IF EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.test') AND name='ix_obj_id')
	DROP INDEX ix_obj_id ON dbo.test
GO	
CREATE INDEX ix_obj_id ON dbo.test(obj_id)
GO

通过动态函数查看该索引的具体信息：

SELECT 
	ix.name,ix.type_desc,ixStats.page_count,ixStats.record_count,ixStats.index_level
FROM sys.indexes AS ix
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008'),OBJECT_ID(N'dbo.test'),NULL,NULL,'DETAILED') AS ixStats
	ON ix.index_id=ixStats.index_id
WHERE ix.name='ix_obj_id'

name	        type_desc	page_count	record_count	index_level
ix_obj_id	NONCLUSTERED	2	        583	        0
ix_obj_id	NONCLUSTERED	1	        2	        1

下面修改建表语句：


 

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='test' AND type='U')
	DROP TABLE dbo.test
GO
CREATE TABLE dbo.test
(
	obj_id CHAR(500),
	obj_name VARCHAR(200)
)
GO
INSERT INTO dbo.test(obj_id,obj_name)
SELECT object_id,name FROM sys.objects
GO
IF EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.test') AND name='ix_obj_id')
	DROP INDEX ix_obj_id ON dbo.test
GO	
CREATE INDEX ix_obj_id ON dbo.test(obj_id)
GO


obj_id字段改成了CHAR(500) 了，肯定比INT型大了，索引的统计信息如下：

SELECT 
	ix.name,ix.type_desc,ixStats.page_count,ixStats.record_count,ixStats.index_level
FROM sys.indexes AS ix
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks2008'),OBJECT_ID(N'dbo.test'),NULL,NULL,'DETAILED') AS ixStats
	ON ix.index_id=ixStats.index_id
WHERE ix.name='ix_obj_id'	

name	        type_desc	page_count	record_count	index_level
ix_obj_id	NONCLUSTERED	39	        583	        0
ix_obj_id	NONCLUSTERED	3	        39	        1
ix_obj_id	NONCLUSTERED	1	        3	        2


可以很明显的看出页面数量多了很多。

 

(3)、检查列的唯一性

在一个很小范围上面加上索引对性能是没有好处的，查询优化器是不能使用该索引有效减少返回的行数，有时候优化器经过判断不会采用该索引。

DBCC freeproccache
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM Person.Person 
WHERE PersonType='IN'

表 'Person'。扫描计数 1，逻辑读取 3816 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:CPU 时间 = 78 毫秒，占用时间 = 1787 毫秒。


这里根据PersonType='IN'来查询，由于没有索引可用，只有通过聚簇索引扫描来筛选了。

在PersonType字段上面加上索引

IF EXISTS(SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID(N'Person.Person') AND name='IX_personType')
	DROP INDEX IX_personType ON Person.Person
GO
CREATE INDEX IX_personType ON Person.Person(PersonType)
GO

然后强制使用该索引：


DBCC freeproccache
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM Person.Person WITH(INDEX(IX_personType))
WHERE PersonType='IN'

表 'Person'。扫描计数 1，逻辑读取 56644 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间: CPU 时间 = 94 毫秒，占用时间 = 1870 毫秒。


 

根据二者比较发现加这个索引不仅没有提高查询效率，反而在逻辑读与时间上还不如按照聚簇索引扫描

 （4）、列的数据类型

通常优先考虑整形

 

（5）、复合索引列的顺序

复合索引，首先是在第一列进行索引键排序，然后在此基础上按照第二列进行排序，因此第一列的位置是很重要的。在Oracle中复合索引需要注意前缀性与可选性，那么在SQL Server中也有类型的注意问题

A、列的唯一性，也就是相同值的个数，想性别之类的字段不仅在单一索引中不要用，在复合索引中也不建议使用

B、列的宽度，前面已经说过，最好使用INT型

C、列的数据类型，当然优先考虑整形

这里建一个索引，但是列的顺序不一样：

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Address]') AND name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode')
	DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] WITH ( ONLINE = OFF )
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] 
(
	[AddressLine1] ASC,	
	[City] ASC,
	[PostalCode] ASC,
	[StateProvinceID] ASC,
	[AddressLine2] ASC
	
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
----------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Address]') AND name = N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode')
	DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] WITH ( ONLINE = OFF )
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address] 
(
	[StateProvinceID] ASC,
	[City] ASC,
	[PostalCode] ASC,
	[AddressLine1] ASC,		
	[AddressLine2] ASC
	
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


地址大小：州——>城市——>邮政编码——>街道地址

下面采用第一种索引：

DBCC freeproccache
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM Person.Address WHERE AddressLine1='1970 Napa Ct.'

表 'Address'。扫描计数 1，逻辑读取 9 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

采用第二种索引：


DBCC freeproccache
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM Person.Address WHERE AddressLine1='1970 Napa Ct.'

表 'Address'。扫描计数 1，逻辑读取 218 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。

可以比较这两种方式建立的索引的好坏
 

（6）、索引类型

SQL Server主要有两种索引类型，聚簇索引与非聚簇索引。

局促索引的叶子页面和表的数据页面相同，一个表只能有一个聚簇索引，主键默认是聚簇索引。

非聚簇索引建立在聚簇索引之上，里面包含一个指向数据行的指针

 

聚簇索引注意事项：

A、首先创建聚簇索引，然后在创建非聚簇索引，因为非聚簇索引的索引行上存有聚簇索引的索引键

B、保持窄索引，通常使用整形

C、一步重建聚簇索引，由于非聚簇索引对聚簇索引的依赖，因此聚簇索引不能先删除再创建，最好使用DROP_EXISTING子句

D、聚簇索引对检索一定范围、读取预先排好序的数据最有效

E、对于频繁更新、宽字段、太多并行的顺序插入的字段不建议使用

 

非聚簇索引注意事项：

在使用非聚簇索引的时候，如果检索的列不属于非聚簇索引的一部分，那么将会定义一个查找——书签查找

A、非聚簇索引适用于从一个大表读取少量数据

B、针对不使用与聚簇索引的列，可以考虑使用非聚簇索引

C、非聚簇索引不使用检索大量的行的查询

 

sql server 索引的使用
03-18
sql server 索引的使用 --创建索引create index(索引关键字) indexName(索引名) on userInfo(userName,userAge) create index indexName on userInfo(userName,userAge) --查看索引 sp_helpindex userInfo --创建唯一性索引 create unique index indexAge on userInfo(userAge) --删除索引 drop index indexName on userInfo
V1.0-sqlServer索引使用总结.docx
07-30
SQL Server 索引使用总结 本文档总结了 SQL Server 中索引的使用方法、分类和注意事项，并提供了实践测试的示例代码。 索引的使用目的 索引的使用目的主要是为了提高查询速度和效率。通过创建索引，可以快速地...
SQL Server 索引结构及其使用（三）[转]
04-24 333
SQL Server 索引结构及其使用（三）作者：freedk一、深入浅出理解索引结构二、改善SQL语句实现小数据量和海量数据的通用分页显示存储过程　　建立一个 Web 应用，分页浏览功能必不可少。这个问题是数据库处理中十分常见的问题。经典的数据分页方法是:ADO 纪录集分页法，也就是利用ADO自带的分页功能（利用游标）来实现分页。但这种分页方法仅适用于较小数据
Sybase SQL Server索引的使用和优化
汪涛的专栏
04-14 2987
在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡 量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所 接受的优化方法。 　　在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引
SQL Server--索引
yuanlanjun
07-30 263
一,索引的概述 1,概念: 数据库索引是对数据表中一个或多个列的值进行排序的结构,就像一本书的目录一样,索引提供了在行中快速查询特定行的能力. 2,优缺点: 2.1优点: 1,大大加快搜索数据的速度,这是引入索引的主要原因. 2,创建唯一性索引,保证数据库表中每一行数据的唯一性. 3,加速表与表之间的连接,特别是在实现数据的参考完整性方面特别有意义. 4,在使用分组...
SQL索引的使用
笑看人生的博客
08-08 1998
通过explain ， 查看执行计划，执行SQL时是否使用索引 1.避免索引失效 1). 全值匹配 ，对索引中所有列都指定具体值。 该情况下，索引生效，执行效率高。 2). 最左前缀法则 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。 匹配最左前缀法则，走索引： 违背最左前缀法则 ， 索引失效： 如果符合最左法则，但是出现跳跃某一列，只有最...
如何获得SQL Server索引使用情况
01-21
问题： ... 　在SQLServer中，许多新的DMVs被引入，供你窥探一些过往版本中不能或很难看到的数据。其中一个新函数和一个新视图是提供索引使用情况： 　Sys.dm_db_index_operational_stats和sys.
Sql Server 索引使用情况及优化的相关Sql语句分享
12-15
本文将分享几个用于分析和优化SQL Server索引的SQL语句。 首先，我们可以使用以下语句来分析数据库中碎片率超过25%的索引： ```sql DECLARE @dbid int SELECT @dbid = db_id() SELECT o.name as tablename, s.* ...
SQL Server索引使用的几个误区.pdf
09-19
标题中提到的“SQL Server索引使用的几个误区.pdf”指明了文档的主题是关于SQL Server数据库中索引的使用，同时强调了一些常见错误和误解。这部分内容通常会涉及索引在数据库中的作用、种类以及如何正确使用索引来...
SQL SERVER数据库全文索引
记事本
12-18 1077
//<![CDATA[Sys.WebForms.PageRequestManager._initialize(AjaxHolder$scriptmanager1, document.getElementById(Form1));Sys.WebForms.PageRequestManager.getInstance()._updateControls([tAjaxHolder$
SQL Server 索引结构及其使用
07-28
SQL+Server+索引结构及其使用 欢迎下载
SQLSERVER全文索引的使用
11-29
该文档详述如何创建全文索引优化模糊查询，使用全文索引的填充因子避免漏查多查
sqlserver中索引的使用方法
最新发布
IT深耕十余载，大道之简
03-26 1233
需要注意的是，索引虽然可以提高查询效率，但也会占用额外的存储空间并增加插入、删除和更新操作的开销。同时，也需要根据实际情况调整和优化索引策略，以达到最佳的查询性能。通过创建合适的聚集索引和非聚集索引，并根据实际需求进行索引的删除和优化，可以实现更高效的数据管理和查询操作。在SQL Server中，索引的使用方法和典型案例主要涉及到索引的创建、删除以及优化查询性能等方面。以图书馆管理为例，假设有一个包含大量图书信息的表，需要频繁查询某本书的信息或者根据作者查询相关书籍。
SQL Server索引结构及使用方法
Kevin yan的专栏
10-26 675
  一、深入浅出理解索引结构 实际上，你能把索引理解为一种特别的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的差别： 其实，我们的汉语字典的正文本身就是个
SQL sever中的索引
m0_71406734的博客
10-14 4894
SQL sever中的索引
sqlserver 索引的使用
qq_34417247的博客
06-26 528
拿汉语字典的目录页（索引）打比方：正如汉语字典中的汉字按页存放一样，SQL Server中的数据记录也是按页存放的，每页容量一般为4K。为了加快查找的速度，汉语字（词）典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），我们可以选择按拼音或笔画查找方式，快速查找到需要的字（词）。：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。•索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。
SqlServer数据库索引的使用
xianweizuo的博客
03-29 451
网上看到的文章，感觉分析的不错，记录下来：https://www.cnblogs.com/Brambling/p/6754993.html
sql--索引使用规则
qq_43470725的博客
09-02 792
sql
SQL-索引的使用
weixin_42060331的博客
03-30 908
3.1 索引的使用：索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引。一个查询只使用一次索引，where中如果使用了索引，order by就不会使用。like做字段比较时只有前缀确定时才会使用索引。索引创建、删除与使用：（注意：要使用大写）索引不包含有NULL值的列。
SQLServer索引基础详解
"SQL Server索引基础教程" 在SQL Server中，索引是提高查询性能的关键元素。它们允许数据库系统快速定位和访问数据，而无需扫描整个表。索引的基础知识包括对数据页的理解，这是SQL Server存储数据的基本单位。 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值