关于索引的学习（主要是聚集索引与非聚集索引）

1      文档说明

本文档为个人学习SQL索引相关，同步形成学习文档，主要是将重点放在聚集索引与非聚集索引方面，文档中包含测试索引带来的查询速度提升测试，由于服务器性能问题，仅供参考。

文档内容写的比较乱，主要是自己随便写的，其中难免有不规范甚至吐槽，且个人水平原因，难免有错漏。后续可能会对文档进行不定期补充。

该文档暂未完成，有部分内容缺失，等待后续补充。

2      索引介绍

索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引提供指向存储在表的指定的列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。

在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

 

个人理解：索引就是目录，就像仓库库存表，就好比字典，使用索引可以快速的查询表中信息，因为没有索引直接查询相当于根据查询条件，将表中全部数据一一取出与条件比对，直到遍历全表后结束并返回结果，相当于表里面数据全翻一遍；而使用索引则不同，通过建立索引，查询先在索引中找符合条件的索引值，然后根据索引值快速找到表中的记录。举例，查字典，如果没有目录（索引），查“张”字，需要从第一页开始，逐条找，从“啊”一直找到“张”，而且还要一直找到字典最后一页，才算完成；使用目录（索引），先找到“张”所在页数（索引值），然后根据页数（索引值），快速翻到对应页，找到对应记录，这样对比，查询速度提升简直了。

3      索引的利弊

最简单来讲，使用索引可以显著提升查询速度，但是使用索引会：1、增加数据库的存储空间，2、在插入和修改数据时要花费较多的时间，因为索引也要随之变动（为何不是增删改？等待使用18750000 两表进行测试）

优点：

1、大大加快数据的检索速度；（已经测试，通过18750000 两表进行相同条件查询，使用耗时大大缩短，某查询语句耗时仅为不用索引耗时的0.35%）

2、通过创建唯一性索引，保证数据库表中每一行数据的唯一性（等待测试，准备创建测试表，创建unique索引，尝试插入相同数据）；

3、加速表和表之间的连接（个人理解：select c1,c2,c3 from t1,t2,t3 where c1=c2 and c1=c3 假设t1 t2 t3表各1万行，则无索引直接查询可能的组合有为1万*1万*1万，即从t1表的第一个值开始逐条取出t2表的值进行1万行遍历，然后再从t1表的第一个值开始逐条取出t3表的值进行1万行遍历，以上遍历完成后，从t1表取第二个值再进行一次上述操作，直至t1表全部数据取完，检索数据条数很多，影响速度。使用索引，直接依索引值快速查找，加快速度）；

4、在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间（同上，待测试；已经测试order by，速度同样有明显提升）；

4      适用范围

什么时候该使用索引呢？

索引很好用，但是往往很好用的东西都不能随便滥用，索引也有其适用场合

 

 

什么时候该用聚集索引，什么时候该用非聚集索引？

 

 

 

忘记从哪里看到的，聚集索引不应该浪费在自增长id列上，比如一张表，有id、dealtype、dealdetail、dealtime等列，考虑到应用程序打开后首先默认根据dealtime列读取最近3个月记录，若表中数据量少则影响不严重，但若表中含有大量数据，则如果id列设置了聚集索引，每次打开应用程序不使用索引仍要花费较长时间，若将聚集索引设置在datetime时间列上，应用程序直接利用索引，快速显示程序界面，带给用户良好的操作体验！要不然用户打开应用程序就得等半天空白。

5      索引分类

5.1          普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如 CREATE INDEX <索引的名字> ON table_name (列的列表)

create index index_id on table_test (id)

修改表，例如ALTER TABLE table_name ADD INDEX [索引的名字]  (列的列表)

alter table table_test add index index_id (id)

创建表的时候指定索引，例如CREATE TABLE table_name ([…] ,INDEX [索引的名字] (列的列表))

create table table_test(id int,name char(10),address char(10) ,index index_id)

5.2          唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的而唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中志愿的姓（ename）上创建了唯一索引，则任何两个员工都不能同姓。

创建唯一索引的几种方式：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON table_name(列的列表)

create unique index index_id on table_test(id)

修改表，例如ALTER TABLE table_name ADD UNIQUE INDEX[索引的名字] (列的列表)

alter table table_test add unique index index_id (id)

创建表的时候指定索引，例如CREATE TABLE table_name([…],UNIQUE [索引的名字] (列的列表))

 

5.3          主键索引

主键索引要求主键中的每一个值是唯一的（创建主键自动创建主键索引）

可以设置创建主键时设置为非聚集索引

create table table_1(

id int,

name varchar(20),

address varchar(20),

gender varchar(10)

constraint pk_id primary key nonclustered(id)

)

也在创建表后手动删除原有主键，然后手动创建非聚集索引

 

5.4          聚集索引

聚集索引（CLUSTERED）：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引

 

5.5          非聚集索引

非聚集索引（NONCLUSTERED）：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引

6      创建（删除）索引

现有表table_test

create table table_test(

id int,

name char(10),

address char(20)

)

普通索引

create index index_name on table_name(id)

主键索引

主键索引在创建表设置主键时自动设置

create table table_name(

id int primary key,

name varchar(20)

)

或者

create table table_11(

id int,

name varchar(10)

constraint pk_id primary key(id)

)

 

alter table table_name add constraint pk_name primary key (column_name)

聚集索引

create clustered index clustered_index_id on table_test(id)

非聚集索引

create nonclustered index noclustered_index_id on table_test(id)

 

删除索引

drop index index_name on table_name

alter table table_name drop index index_name

alter table table_name drop constraint pk_id

这条语句仅限于当索引是由于创建表时由于设置主键而默认生成的主键索引

7      关于聚集索引与非聚集索引

7.1          聚集索引

聚集索引包含稠密索引与稀疏索引

其中对比如图（网络摘抄）

 

 

聚集索引指的是，一个文件中可以有多个索引，分别基于不同的搜索码。（搜索码：用在文件中查找记录的属性或属性集）如果包含记录的文件按照某个特定的顺序排序，那么该搜索码对应的索引就是聚集索引。

聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，而一个表只能有一种物理顺序，所以一张表只能包含一个聚集索引。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

个人理解：

聚集索引就是仓库里物品明细货架单，何货物放在何货架的何位置都有一一对应且唯一的顺序，比如安全帽一定是放在A货架的**位置，而钻头一定是放在Z货架的**位置，且货架A一定是在仓库的靠门区域，，而货架Z一定是在仓库的靠里位置（举例）

聚集索引示意图（网络摘抄）

聚集索引直接指向数据位置

 

 

 

7.2          非聚集索引

该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

示意图

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

 

 

个人理解：

非聚集索引首先指向了另一个索引，这个索引指向指针，而该指针存放无逻辑顺序无物理排序（相对聚集索引而言），通过该指针再去指向实际数据，也就是说，当表中数据发生变化时，非聚集索引只需要更新指针内容即可，不需要涉及到物理逻辑，也就是说，操作数据时不需要考虑数据在数据页的响应位置，不需要进行数据重排，数据增删改速度会增加（相对聚集索引）？

8      索引带来的查询速度测试对比

使用两张表，分别批量插入18750000行数据。

两张表均无任何索引时，使用相同的查询条件进行查询耗时基本接近

select * from table_test where name=’Jarry’

select * from table_test where name=’Jarry’

查询耗时（ms）	第一次	第二次	第三次	第四次	第五次	平均
table_test	9153	9166	9150	9046	9090	9121
table_test1	9206	9140	9046	9193	9163	9149.6

1、对比聚集索引与无索引

table_test1表的name列创建聚集索引，test表不创建任何索引，执行相同的查询语句，对比查询耗时

select * from table_test where name=’Jarry’

select * from table_test where name=’Jarry’

查询耗时：

查询耗时（ms）	第一次	第二次	第三次	第四次	第五次	平均	耗时对比
table_test	9153	9146	9106	9203	9160	9153.6	0.352%
table_test1	96	16	6	23	20	32.2

由此可以看出通过在name列设置索引，查询速度有了从纸飞机到F35的提升，可以想到，若是用户实际使用，等待9秒和等待0.096秒是截然不同的，一个是卡死了，一个是Duang~的一下立马出现，嗯这个可以有。

2、对比非聚集索引与无索引

在table_test1表 address列创建非聚集索引，test表同样不创建任何索引，使用address查询条件进行查询，比较耗时

select * from table_test where address='Jarry18750000的家'

select * from table_test1 where address='Jarry18750000的家'

比较耗时：

查询耗时（ms）	第一次	第二次	第三次	第四次	第五次	平均	效率对比
table_test	16026	9013	9246	9400	9246	10586.2	0.463%
table_test1	30	26	26	33	130	49

可以看出非聚集索引同样带来巨大的速度提升。奇怪的是第一次查询时，test表耗时较长，可能与服务器后台其他运算影响性能有关。

3、对比聚集索引与非聚集索引

在test表name列创建非聚集索引，而test1表name列已经创建聚集索引。

select * from table_test where name=’Jarry’

select * from table_test where name=’Jarry’

比较耗时：

查询耗时（ms）	第一次	第二次	第三次	第四次	第五次	平均	效率对比
table_test	46	3	0	0	0	0	0
table_test1	63	23	0	0	0	0

奇怪的是只有第一次查询的时候有较明显的耗时，第二次及以后查询耗时均为0，原因暂未知（后来把精度调整为纳秒，疯了，仍是0）。

4、对比索引在order by上耗时

相同的查询语句，只是order by 排序依据不同，对比耗时

使用test1表，聚集索引name列与无索引id列

declare @timee datetime

set @timee=GETDATE()

select top(2000) * from table_test1 order by name

select [Time for select (ms)] =DATEDIFF(ms,@timee,GETDATE())

go

 

declare @timee1 datetime

set @timee1=GETDATE()

select top(2000) * from table_test1 order by id

select [Time for select (ms)]=DATEDIFF(ms,@timee1,getdate())

比较耗时，可以看到速度提升80倍

查询耗时（ms） order by	第一次	第二次	第三次	第四次	第五次	平均	对比
name	183	216	196	190	193	195.6	80
id	15523	15950	15676	15586	15520	15651

 

使用test表，非聚集索引name与无索引id对比

declare @timee datetime

set @timee=GETDATE()

select top(2000) * from table_test order by name

select [Time for select (ms)] =DATEDIFF(ms,@timee,GETDATE())

go

 

declare @timee1 datetime

set @timee1=GETDATE()

select top(2000) * from table_test order by id

select [Time for select (ms)]=DATEDIFF(ms,@timee1,getdate())

比较耗时，可以看到速度提升76.5倍

查询耗时（ms） order by	第一次	第二次	第三次	第四次	第五次	平均	对比
name	260	183	173	213	186	203	76.52
id	15916	15396	15620	15483	15256	15534.2

 

 

附：

1、显示查询耗时语句

declare @timee datetime

set @timee=getdate()

select * from table_test where name=’Jarry’

select[Time for select(ms)]=DATEDIFF(MS,@timee,GETDATE())

2、批量向表中插入大量数据语句（奇偶不同）

if exists(select 1 from sysobjects where xtype='u' and name='table_test' )

drop table table_test

create table table_test(

id int,

name char(50),

address char(50),

gender char(10)

)

 

declare @n int

declare @name  varchar(50)--使用变量

declare @name1 varchar(50)--设置奇数用

declare @name2 varchar(50)--设置偶数用

declare @addre varchar(50)--使用变量

declare @sql varchar(200)

set @n=0

set @addre='的家'

set @name=' '

set @name1='Tom'

set @name2='Jarry'

while @n<20

begin

      set @n=@n+1

      if @n%2=1

      begin

             set @name=@name1+convert(varchar,@n)

             set @addre=@name+@addre

             set @sql='insert into table_test (id,name,address,gender) values('+convert(int,@n)+','+@name+','+@addre+',''male'')'

             set @addre='的家'

             print(@sql)

             exec(@sql)

      end

      else

      begin

             set @name=@name2+convert(varchar,@n)

             set @addre=@name+@addre

             set @sql='insert into table_test (id,name,address,gender) values('+convert(int,@n)+','+@name+','+@addre+',''female'')'

             set @addre='的家'

             print(@sql)

             exec(@sql)

      end

end