开发者

SQL Server中索引的用法详解

开发者 https://www.devze.com 2022-12-13 10:14 出处:网络 作者: springsnow
目录一、索引的介绍什么是索引?1、聚集索引和非聚集索引2、索引的利弊3、索引的存储机制二、设置索引的权衡1、什么情况下设置索引2、什么情况下不要设置索引三、聚集索引1、使用SS编程客栈MS创建聚集索引2、使用T-S
目录
  • 一、索引的介绍
    • 什么是索引?
    • 1、聚集索引和非聚集索引
    • 2、索引的利弊
    • 3、索引的存储机制
  • 二、设置索引的权衡
    • 1、什么情况下设置索引
    • 2、什么情况下不要设置索引
  • 三、聚集索引
    • 1、使用SS编程客栈MS创建聚集索引
    • 2、使用T-SQL创建聚集索引
  • 四、非聚集索引
    • 1、SSMS创建方法同上,T-SQL创建方法如下:
    • 2、添加索引选项
  • 五、示例
    • 六、管理索引

      一、索引的介绍

      什么是索引?

      索引是一种磁盘上的数据结构,建立在表或视图的基础上。使用索引可以使数据的获取更快更高校,也会影响其他的一些性能,如插入或更新等。

      索引主要分为两种类型:

      1、聚集索引和非聚集索引

      字典的目录就是一个索引,按照拼音查询想要的字就是聚集索引(物理连续,页码与目录一一对应),偏旁部首就是一个非聚集索引(逻辑连续,页码与目录不连续)。

      聚集索引存储记录是物理上连续存在的,而非聚集索引是逻辑上的连续,物理存储并不连续。

      聚集索引一个表中只能有一个,而非聚集索引一个表中可以有多个。

      2、索引的利弊

      使用索引是为了避免全表扫描,因为全表扫描是从磁盘上读取表的每一个数据页,如果有索引指向数据值,则只需要读少次数的磁盘就可以。

      带索引的表在数据库中占用更多的空间,同样增编程客栈、删、改数据的命令所需时间会更长。

      3、索引的存储机制

      书中的目录是一个字词以及所在的页码列表,数据库中的索引是表中的值以及各值存储位置的列表。

      聚集索引是在数据库中新开辟一个物理空间,用来存放他排列的值,当有新数据插入时,他会重新排列整个物理存储空间。

      非聚集索引只包含原表中的非聚集索引的FzXWLETm列和指向实际物理表的一个指针。

      数据表的基本结构

      当一个新的数据表创建时,系统将在磁盘中分配一段以8k为单位的连续空间。当一个8k用完的时候,数据库指针会自动分配一个8k的空间,每个8k的空间称为一个数据页,并分配从0-7的页号,每个文件的第0页记录引导信息叫页头,每8个数据页由64k组成形成扩展区。全部数据页的组合形成堆。

      SQL Server规定行不能跨越数据页,所以每行记录的最大数量只能是8k,这就是为什么char和varchar这两种字符类型容量要限制在8k以内的原因,存储超过8k的数据应使用text类型,其实text类型的字段值不能直接录入和保存,它是存储一个指针,指向由若干个8k的数据页所组成的扩展区,真正的数据其实放在这些数据页中。

      二、设置索引的权衡

      1、什么情况下设置索引

      • 定义主键的数据列(sql server默认会给主键一个聚集索引)。
      • 定义有外键的数据列
      • 对于经常查询的数据列
      • 对于需要在指定范围内频繁查询的数据列
      • 经常在where子句中出现的数据列
      • 经常出现在关键字 order by、group by、distinct后面的字段。

      2、什么情况下不要设置索引

      • 查询中很少涉及的列,重复值比较多的列。
      • 开发者_NewSQL
      • text、image、bit数据类型的列
      • 经常存取的列
      • 经常更新操作的表,索引一般不要超过3个、最多不要5个。虽说提高了访问速度,但会影响更新操作。

      三、聚集索引

      1、使用SSMS创建聚集索引

      展开要创建索引的表->右击索引->选择新建索引->聚集索引->新建索引点添加->选择列->选择升序或降序->输入名字->确定。

      默认情况下,生成主键的同时将自动创建一个聚集索引。

      2、使用T-SQL创建聚集索引

      create clustered index index_name /*聚集索引名*/
      on table_name
      (
          id desc
      )
      with(drop_existing=on); /*如果存在则删除*/

      每张表或者视图只能包含一个聚集索引,因为聚集索引改变了数据存储与排列方式。

      无论是聚集还是非聚集索引,都将信息存储在平衡树或B-树中,B-树识别类似数据并将他们组合在一起,正是由于B-树中的检索基于键值,因此索引可以提升数据访问的速度。B-树将具有类似键的组合起来,所以数据库引擎只需搜索少量页面即可找到目标记录。

      四、非聚集索引

      每张表上可以有多个非聚集索引,可以在某个列上创建一个索引,也可以在已经是现有索引组成部分的多列上创建索引。

      1、SSMS创建方法同上,T-SQL创建方法如下:

      create nonclustered index fei /*聚集索引名*/
      on defualt
      (
          hits desc
      )

      2、添加索引选项

      fillfactor:用于在创建索引时,每个索引页的数据占索引大小的百分比,默认100.当需要频繁修改表时,建议设置为70-80,不经常更新时建议90。

      五、示例

      create table ceshi --新建表
      (
          id int identity(1,1) primary key,
          name varcwww.devze.comhar(20),
          code varchar(20),
          [date] datetime
      )
      
      --插入10w条测试数据
      declare @n int
      set @n = 1
      while @n <100000
       begin
         insert into ceshi (name,code,[date]) values ('name'+cast(@n as varchar(20)),'code'+cast(@n as varchar(20)),getutcdate())
        set @n=@n+1
      end
      
      --查看数据
      set statistics io on --查看磁盘io
      set statistics time on --查看sql语句分析编译和执行时间
      select * from ceshi
      
      --查看索引情况
      exec sp_helpindex ceshi
      
      select * from ceshi where name = 'name1'

      ctrl+l 查看执行计划 聚集索引扫描开销100%,考虑优化为索引查找,在name上建立非聚集索引。

      --建立非聚集索引
      create index name_index on ceshi
      (
          n编程客栈ame
      )
      --再次查看索引情况 多出来新建的非聚集索引
      exec sp_helpindex ceshi
      
      --在运行上面的语句
      select * from ceshi where name = 'name1'
      --明显发现速度变快了 , ctrl+l 发现聚集索引和非聚集索引各占50%

      六、管理索引

      --查看该表中的索引
      exec sp_helpindex ceshi 
      --改名
      exec sp_rename 'ceshi.name_index','new_name' 
      --删除索引
      drop index ceshi.new_name
      --检查碎片
      dbcc showcontig(ceshi,new_name)
      --整理碎片
      dbcc indexdefrag(webDB,ceshi,new_name) 
      --更新表中所有索引的统计
      update statistics ceshi 

      到此这篇关于SQL Server索引的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

      0

      精彩评论

      暂无评论...
      验证码 换一张
      取 消