sqlserver 索引的一些概括
发布时间：2021-12-03 19:42:30 所属栏目：教程 来源：互联网
导读：1.1.1 摘要 如果说要对数据库进行优化，我们主要可以通过以下五种方法，对数据库系统进行优化。 1. 计算机硬件调优 2. 应用程序调优 3. 数据库索引优化 4. SQL语句优化 5. 事务处理调优 在本篇博文中，我们将想大家讲述数据库中索引类型和使用场合，本文以SQ
|
1.1.1 摘要 如果说要对数据库进行优化，我们主要可以通过以下五种方法，对数据库系统进行优化。 1. 计算机硬件调优 2. 应用程序调优 3. 数据库索引优化 4. SQL语句优化 5. 事务处理调优 在本篇博文中，我们将想大家讲述数据库中索引类型和使用场合，本文以SQL Server为例，对于其他技术平台的朋友也是有参考价值的，只要替换相对应的代码就行了！ 索引使数据库引擎执行速度更快，有针对性的数据检索，而不是简单地整表扫描（Full table scan）。 为了使用有效的索引，我们必须对索引的构成有所了解，而且我们知道在数据表中添加索引必然需要创建和维护索引表，所以我们要全局地衡量添加索引是否能提高数据库系统的查询性能。 在物理层面上，数据库有数据文件组成，而这些数据文件可以组成文件组，然后存储在磁盘上。每个文件包含许多区，每个区的大小为64K由八个物理上连续的页组成（一个页8K），我们知道页是SQL Server数据库中的数据存储的基本单位。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。 页中存储的类型有：数据，索引和溢出。 文件和文件组 在SQL Server中，通过文件组这个逻辑对象对存放数据的文件进行管理。 1.1.2 正文 在物理层面上，数据库有数据文件组成，而这些数据文件可以组成文件组，然后存储在磁盘上。每个文件包含许多区，每个区的大小为64K由八个物理上连续的页组成（一个页8K），我们知道页是SQL Server数据库中的数据存储的基本单位。为数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。 页中存储的类型有：数据，索引和溢出。 文件和文件组 在SQL Server中，通过文件组这个逻辑对象对存放数据的文件进行管理。 图2文件组成 页中保存的数据类型有：表数据、索引数据、溢出数据、分配映射、页空闲空间、索引分配等，具体如下图所示： 页类型 内容 Data 当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。 Index 索引条目。 Text/Image 大型对象数据类型：text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。数据行超过 8 KB 时为可变长度数据类型列：varchar 、nvarchar、varbinary 和 sql_variant Global Allocation Map、Shared Global Allocation Map 有关区是否分配的信息。 Page Free Space 有关页分配和页的可用空间的信息。 Index Allocation Map 有关每个分配单元中表或索引所使用的区的信息。 Bulk Changed Map 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。 Differential Changed Map 有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。 表1页中保存的数据类型 在数据页上，数据行紧接着页头（标头）按顺序放置；页头包含标识值，如页码或对象数据的对象ID；数据行持有实际的数据；最后，页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目，每个条目记录对应行的第一个字节与页头的距离，行偏移表中的条目的顺序与页中行的顺序相反。 图4索引的叶节点和相应的表数据 如上图4所示，索引叶节点包含索引值和相应的RID（ROWID），而且叶节点通过双向链表有序地连接起来；同时我们主要到数据表不同于索引叶节点，表中的数据无序存储，它们不全是存储在同一表块中，而且块之间不存在连接。 总的来说，索引保存着具体数据的物理地址值。 索引的类型 我们知道索引的类型有两种：聚集索引和非聚集索引。 聚集索引：物理存储按照索引排序。 非聚集索引：物理存储不按照索引排序。 聚集索引 聚集索引的数据页是物理有序地存储，数据页是聚集索引的叶节点，数据页之间通过双向链表的形式连接起来，而且实际的数据都存储在数据页中。当我们给表添加索引后，表中的数据将根据索引进行排序。 假设我们有一个表T_Pet，它包含四个字段分别是：animal，name，sex和age，而且使用animal作为索引列，具体SQL代码如下： 复制代码 代码如下: ----------------------------------------------------------- ---- Create T_Pet table in tempdb. ----------------------------------------------------------- USE tempdb CREATE TABLE T_Pet ( animal VARCHAR(20), [name] VARCHAR(20), sex CHAR(1), age INT ) CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal) ----------------------------------------------------------- ---- Insert data into data table. ----------------------------------------------------------- 复制代码 代码如下: DECLARE @i int SET @i=0 WHILE(@i<1000000) BEGIN INSERT INTO T_Pet ( animal, [name], sex, age ) SELECT [dbo].random_string(11) animal, [dbo].random_string(11) [name], 'F' sex, cast(floor(rand()*5) as int) age SET @i=@i+1 END INSERT INTO T_Pet VALUES('Aardark', 'Hello', 'F', 1) INSERT INTO T_Pet VALUES('Cat', 'Kitty', 'F', 2) INSERT INTO T_Pet VALUES('Horse', 'Ma', 'F', 1) INSERT INTO T_Pet VALUES('Turtles', 'SiSi', 'F', 4) INSERT INTO T_Pet VALUES('Dog', 'Tomma', 'F', 2) INSERT INTO T_Pet VALUES('Donkey', 'YoYo', 'F', 3) SET STATISTICS PROFILE OFF SET STATISTICS TIME OFF 当我们执行完SQL查询计划时，把鼠标指针放到“聚集索引查找”上，这时会出现如下图信息，我们可以查看到一个重要的信息Logical Operation——Clustered Index Seek，SQL查询是直接根据聚集索引获取记录，查询速度最快。 图7查询结果 现在我们把表中的索引删除，重新执行查询计划，这时我们可以发现Logical Operation已经变为Table Scan，由于表中有100万行数据，这时查询速度就相当缓慢。 图9查询结果 通过上面的有聚集索引和没有的对比，我们发现了查询性能的差异，如果使用索引数据库首先查找索引，而不是漫无目的的全表遍历。 非聚集索引 在没有聚集索引的情况下，表中的数据页是通过堆(Heap)形式进行存储，堆是不含聚集索引的表；SQL Server中的堆存储是把新的数据行存储到最后一个页中。 非聚集索引是物理存储不按照索引排序，非聚集索引的叶节点（Index leaf pages）包含着指向具体数据行的指针或聚集索引，数据页之间没有连接是相对独立的页。 假设我们有一个表T_Pet，它包含四个字段分别是：animal，name，sex和age，而且使用animal作为非索引列，具体SQL代码如下： 复制代码 代码如下: ----------------------------------------------------------- ---- Create T_Pet table in tempdb with NONCLUSTERED INDEX. ----------------------------------------------------------- USE tempdb CREATE TABLE T_Pet ( animal VARCHAR(20), [name] VARCHAR(20), sex CHAR(1), age INT ) CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal) SET STATISTICS PROFILE OFF SET STATISTICS TIME OFF 如下图所示，我们发现查询计划的最右边有两个步骤：RID和索引查找。由于这两种查找方式相对于聚集索引查找要慢（Clustered Index Seek）。 图11查询计划 首先SQL Server查找索引值，然后根据RID查找数据行，直到找到符合查询条件的结果。 查询执行时间：CPU 时间= 0 毫秒，占用时间= 1 毫秒 图13堆表非聚集索引 通过上图，我们发现非聚集索引通过双向链表连接，而且叶节点包含指向具体数据行的指针。 如果我们要查找animal = ‘Dog'的信息，首先我们遍历第一层索引，然后数据库判断Dog属于Cat范围的索引，接着遍历第二层索引，然后找到Dog索引获取其中的保存的指针信息，根据指针信息获取相应数据页中的数据，接下来我们将通过具体的例子说明。 现在我们创建表employees，然后给该表添加堆表非聚集索引  （编辑：东莞站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
