聚集、非聚集索引、唯一索引、复合索引、系统自建索引

深藏阁楼爱情的钟 2022-04-14 05:48 282阅读 0赞

说明：红色字体表示要特别注意点

索引是在数据库表或者视图上创建的对象，目的是为了加快对表或视图的查询的速度。

按照存储方式分为：聚集与非聚集索引  
按照维护与管理索引角度分为：唯一索引、复合索引和系统自动创建的索引。

索引的结构是由：根节点--->非叶节点--->非叶节点--->叶节点

**1、聚集索引**:表中存储的数据按照索引的顺序存储,检索效率比普通索引高,但对数据新增/修改/删除的影响比较大。逻辑顺序决定了表中相应行的**物理顺序**。  
特点：  
　 (1) 一个表可以最多可以创建249个索引  
　 (2) 先建聚集索引才能创建非聚集索引  
     (3) 非聚集索引数据与索引不同序  
     (4) 数据与索引在不同位置  
     (5) 索引在叶节点上存储,在叶节点上有一个＂指针＂直接指向要查询的数据区域  
     (6) 数据不会根据索引键的顺序重新排列数据

(7)如果在该字段上进行范围查询，或者该表很少做增删改  
  
　创建聚集索引的语法：  
   create NONCLUSTERED INDEX idximpID ON EMP(empID)  
**２、非聚集索引**:不影响表中的数据存储顺序,检索效率比聚集索引低,对数据新增/修改/删除的影响很少  
。是通过**二叉树**的数据结构来描述的，逻辑顺序，特点：  
   (1) 无索引，数据无序  
   (2) 有索引，数据与索引同序   
   (3) 数据会根据索引键的顺序重新排列数据  
   (4) 一个表只能有一个索引  
   (5) 叶节点的指针指向的数据也在同一位置存储  
语法：  
create CLUSTERED INDEX idxempID on emp(empID)  
**３、惟一索引：**惟一索引可以确保索引列不包含重复的值.  
可以用多个列，但是索引可以确保索引列中每个值组合都是唯一的  
**姓   名**  
李   二  
张   三  
王   五  
语法： create unique index idxempid on emp(姓，名)  
  
**4、复合索引**：如果在两上以上的列上创建一个索引，则称为复合索引。  
那么，不可能有两行的姓和名是重复的  
语法：  
create index indxfullname on addressbook(firstname,lastname)

**注意**：如果把复合的聚集索引字段分开查询。  
带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）：  
    （1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''  
    查询速度：2513毫秒  
    （2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5'' and neibuyonghu=''办公室''  
    查询速度：2516毫秒  
    （3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''  
    查询速度：60280毫秒  
 　从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而**如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的**。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：**无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。**  
  
**5、系统自建的索引:**在使用T\_sql语句创建表的时候使用PRIMARY KEY或UNIQUE约束时，会在表上自动创建一个惟一索引  
自动创建的索引是无法删除的  
语法：  
create table ABc  
( empID int primary key,  
   firstname varchar(50)UNIQUE,  
   lastname   varchar(50)UNIQUE,  
)  
这样的结果就出来了三个索引，但只有一个聚集索引哦  
  
  
６、创建索引的方法：  
1、企业管理器中  
(1)右击某个表，所有任务---管理索引，打开管理索引，单击“新建”就可以创建索引  
(2)在设计表中进行设计表，管理索引/键  
(3)在关系图中，添加表后右击关系图中的某个表，就有“索引/键”  
(4)通过向导,数据库---创建索引向导  
(5)通过T-SQL语句  
2、能过“索引优化向导”来优化索引的向导，通过它可以决定选择哪些列做为索引列  
　  
 二、**何时使用聚集索引或非聚集索引**

下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

<table> 
 <tbody> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;" align="center">动作描述</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;" align="center">使用聚集索引</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;" align="center">使用非聚集索引</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">列经常被分组排序</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">返回某范围内的数据</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">一个或极少不同值</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">小数目的不同值</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">大数目的不同值</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">频繁更新的列</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">外键列</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">主键列</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
  <tr> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">频繁修改索引列</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">不应</td> 
   <td style="border-collapse:collapse;border:#c0c0c0 1px solid;">应</td> 
  </tr> 
 </tbody> 
</table>