第十三节：数据库索引相关和EFCore的索引映射

一. 简介

1.背景

　　索引用于快速找出在某个列中有某一特定值的行，不使用索引，数据库必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多， 如果表中查询的列有一个索引，数据库能快速到达一个位置去搜索数据。

2.含义

　　索引是一个单独的，存储在磁盘上的数据结构，它们包含对数据表里所有记录的引用指针，使用索引用于快速找出在某个或多个列中有某一特定值的行，对相关列使用索引是降低查询操作时间的最佳途径。索引可以是由表或视图中的一列或多列生成的键。

　　例如：数据库中有20000条记录，现在要执行这样一个查询：SELECT * FROM tableName WHERE num=10000。如果没有索引，必须遍历整个表，直到num=10000的这一行为止；如果在num列上创建索引，SQL Server不需要任何扫描，直接在索引里面找10000，就可以得知这一行的位置。可见，索引的建立可以加快数据的查询速度。

3.索引的优点

　(1).通过创建唯一索引，可以保证数据库表的每一行数据的唯一性.（eg：表主键）

　(2).建索引最主要的目的：大大加快了数据的查询速度.

　(3).实现数据的参照完整性，可以加速表和表之间的连接.

　(4).在使用分组和排序子句进行查询时，也可以显著减少查询中分组和排序的时间

4.索引的弊端

　(1).创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加.

　(2).索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到做大文件尺寸。

　(3).对表中的数据进行增加，删除和修改的时候，索引也要动态地维护，这样就就降低了数据的维护速度。

 

二. 索引分类

　　SQL Server中的索引有两种：聚集索引和非聚集索引，它们的区别是在物理数据的存储方式上。

1.聚集索引

　　聚集索引基于数据行的键值，在表内排序和存储这些数据行。

　(1).每张表只能有一个聚集索引，因为数据行本身只能按一个顺序存储.

　(2).表中的物理顺序和索引中行的物理顺序是相同的，创建任何非聚集索引之前要先创建聚集索引，这是因为非聚集索引改变了表中行的物理顺序.

　(3).关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护.

　(4).在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，所以要保证有足够的空间创建索引

2.非聚集索引

　　非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序，非聚集索引包含索引键值和指向表数据存储位置的行定位器。可以对表或索引视图创建多个非聚集索引。通常，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择，因为索引中包含搜索的数据值在表中的精确位置的项。

　考虑使用非聚集索引的情况：

　(1).使用JOIN或者GROUP BY子句,应为连接和分组操作中所涉及的列创建多个非聚集索引,为任何外键创建一个聚集索引.

　(2).包含大量唯一值的字段.

　(3).不返回大型结果集的查询,创建筛选索引以覆盖从大型表中返回定义完善的的行子集的查询.

　(4).经常包含在查询的搜索条件(如返回完全匹配的WHERE子句)中的列.

3.其它类别索引

　　除了聚集索引和非聚集索引之外,还有一些根据其它依据划分的索引：

　(1).唯一索引：确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。聚集索引和非聚集索引都可以是唯一索引。这种唯一性和主键约束是关联的，在某种程度上，主键约束等于唯一性的聚集索引。

　(2).包含列索引：一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

　(3).索引视图：在视图上添加索引后能提高视图的查询效率。视图的索引将具体化视图，并将结果集永久存储在唯一的聚集索引中，而且存储方式相同，创建聚集索引后，可以为视图添加非聚集索引。

　(4).全文索引：一种特殊类型的基于标记的功能索引，由SQL Server全文引擎生成和维护，用于帮助在字符串数据中搜索复杂的词，这种索引的结构与数据库引擎使用的聚集索引或非聚集索引的B树结构是不同的。

　(5).空间索引：一种针对geometry数据类型的列上建立的索引，这样可以更高效的列中的空间对象执行某些操作，空间索引可以减少需要应用开销相对较大的空间操作的对象数。

　(6).筛选索引:一种经过优化的的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的的部分进行索引，与全表索引相比，设计良好的筛选索引可以提高查询性能，减少索引维护开销并可降低索引存储开销

　(7).XML索引：是与XML数据关联的索引形式，是XML二进制大对象(BLOB)的已拆分持久表示形式，XML索引可以分为主索引和辅助索引。

 

三. 设计原则

　　索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要,设计索引时,应该考虑以下原则：

　(1).索引并非越多越好，一个表中如果有大量的索引，不仅占用大量的磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。因为当表中数据更改的同时，索引也会进行调整和更新。

　(2).避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。

　(3).数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。 

　(4).在条件表达式中经常用到的、不同值较多的列上建立索引。如果在不同值较少的列上不要建立索引,比如在学生表的【性别】字段上只有【男】或【女】两个不同值，因此就无需建立索引，如果建立索引，不但不会提高查询效率，反而会严重降低更新速度。 

　(5).当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引能够确保定义的列的数据完整性，提高查询速度.

　(6).在频繁进行排序或分组（即进行GROUP BY 或ORDER BY操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

 

四. 索引的管理

1.常见指令

　(1).查看表的索引：exec sp_helpindex 'tableName';

　(2).查看索引的统计信息：DBCC SHOW_STATISTICS ('数据库名.dbo.表名',索引名);

　(3).修改索引名称：exec sp_rename '表名.旧索引名' ,'新索引名', index;

　　例如：将teacher表中的索引名称idx_nameAndgender更改为multi_index 

　　答案：exec sp_rename 'teacher.idx_nameAndgender' ,'multi_index', index

　(4).删除索引：DROP INDEX 表名.索引名

　　例如：删除表teacher中的索引 multi_index

　　答案：DROP INDEX teacher.multi_index

注：DROP INDEX 命令不能删除由 CREATE TABLE或者ALTER TABLE命令创建的主键（PRIMARY KEY）或者唯一性（UNIQUE）约束索引，也不能删除系统表中的索引.

(5).创建索引

  CREATE [UNIQUE] [CLUSTERED|NONCLUSTERRED]

  INDEX 索引名 ON {table名|view名}(column名 [ASC|DESC] [,...n])

PS：以上是索引的基本创建格式，下面补充创建索引的其它参数配置：

 CREATE INDEX命令既可以创建一个改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引。语法如下：

  CREATE [UNIQUE] [CLUSTERED   |    NONCLUSTERRED]

  INDEX index_name ON {table | view }(column [ASC | DESC ]  [,...n])

  [ INCLUDE ( column_name [ ,...n])]

  [with

   (

    PAD_INDEX={ON | OFF}

    | FILLFACTOR =fillfactor

    | SORT_IN_TEMPDB={ ON | OFF}

    |  IGNORE_DUP_KEY ={ ON  | OFF}

    |  STATISRICS_NORECOMPUTE ={ ON | OFF}

    |   DROP_EXISTING = { ON | OFF}

    |  ONLINE ={ ON | OFF}

    |  ALLOW_ROW_LOCKS ={ ON | OFF}

    |  ALLOW_PAGE_LOCKS={ ON   | OFF}

    |  MAXDOP =max_degree_of_parallelism     

   ) [,...n] ]

UNIQUE:表示在表或视图上创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。

CLUSTERED:表示创建聚集索引，在创建任何非聚集索引之前创建聚集索引，创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。

NONCLUSTERED：表示创建一个非聚集索引，非聚集索引数据行的物理顺序独立于与索引排序，每个表最多可包含999个非聚集索引，NONCLUSTERED是CREATE INDEX的默认值。

index_name:指定索引的名称，索引名称在表或视图中必须唯一，但在数据库中可不必唯一。

ON{table | view }:指定索引所属的表或视图。

Column：指定索引基于一列或者多列。指定两个或者多个列名，可为指定的列的组合值创建组合索引，{table | view}后的括号中，按排序优先级列出组合索引要包括的列。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中。

[ASC | DESC]:指定索引列的升序或降序排序方向，默认为ASC。

INCLUDE（column[,...n]）：指定要添加到非聚集索引的叶级别的非键列。

PAD_INDEX:表示指定索引填充，默认值为OFF。ON值表示fillfactor指定的可用空间百分比应用于索引的中间级页。

FILLFACTOR=fillfactor:制定一个百分比，表示在索引创建或重新生成过程中数据库引擎应使每个索引页的叶级别达到的填充程度。fillfactor必须为介于1至100直接的整数值，默认值为0；

SORT_IN_TESTDB:指定是否在tempdb中存储临时排序结果，默认值为OFF，ON值表示在tempdb中存储用于生成索引的中间排序结果，OFF表示中间排序结果与索引存储在同一数据库中。

IGNORE_DUP_KEY:指定唯一聚集索引或唯一非聚集索引执行多行插入操作时，出现重复键值的错误响应。默认值为OFF。ON表示发出一跳警告消息，但只有违反了唯一索引的行才会失败，OFF表示发出错误消息，并回滚整个INSERT事物。

STATISRICS_NORECOMPUTE:指定是否重新计算分发统计信息，默认值为OFF。ON表示不会自动重新计算过时的统计信息，OFF表示启用统计信息自动更新功能。

DROP_EXISTING:指定删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。ON表示删除并重新生成现有索引。指定的索引名称必须与当前现有的索引相同，但可以修改索引定义，例如：可以指定不同的列，排序顺序，分区方案或索引选项。OFF表示如果指定的索引名已存在，则会显示一条错误。

ONLINE={ON | OFF}:指定在索引操作期间，基础表和关联的索引是否用于查询和数据修改操纵，默认值为OFF。

ALLOW_ROW_LOCKS:指定是否允许行锁，默认值为ON，ON表示在访问索引时允许行所。数据库引擎确定何时使用行锁。OFF表示未使用行锁。

ALLOW_PAGE_LOCKS:指定是否允许页锁，默认值为ON，ON表示在访问索引时允许页锁。数据库引擎确定何时使用页锁。ＯＦＦ表示未使用页锁。

MAXDOP:指定在索引操作期间，覆盖【最大并行度】配置选项，使用MAXDOP可以限制在执行并行计划的过程中使用的处理器数量，最大数量为64.

例1：在teacher表中的t_phone列上，创建一个名称为Idx_phone的唯一聚集索引，降序排列，填充因子为30%

CREATE UNIQUE CLOSTERED INDEX Idx_phone
ON teacher (t_phone DESC)
WITH  FILLFACTOR=30;

例2：在teacher表中的t_name和t_gender列上，创建一个名称为Idx_nameAndgender的-唯一非聚集组合索引，升序排列，填充因子为10%

CREATE UNIQUE NONCLUSTERED INDEX Idx_nameAndgender
ON teacher(t_name,t_gender)
WITH FILLFACTOR=10;

 

五. EFCore使用索引

1.映射EF实体

　(1).映射生成：【Scaffold-DbContext "Server=localhost;Database=EFDB01;User ID=sa;Password=123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables T_UserInfor,T_RoleInfor -Context ypfContext -UseDatabaseNames -DataAnnotations】

　(2).覆盖：【 Scaffold-DbContext "Server=localhost;Database=EFDB01;User ID=sa;Password=123456;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables T_UserInfor,T_RoleInfor -Context ypfContext -UseDatabaseNames -DataAnnotations -Force】

 以上指令会自动映射数据库中实体的索引.

2.相关索引的映射语句

　(1).单字段索引： entity.HasIndex(e => e.userName).HasName("Index_userName");

　(2).多字段索引： entity.HasIndex(e => new { e.userName, e.addTime }).HasName("Index_userAge_addTime").IsUnique();

　(3).单子段唯一索引：entity.HasIndex(e => e.userName).HasName("Index_userName").IsUnique();

PS：SQL中对应的生成索引的语句如下：

--给userName添加非聚集索引
 CREATE NONCLUSTERED INDEX Index_userName
 ON T_UserInfor(userName)

 --给userAge和addTime添加联合唯一的非聚集索引
 CREATE UNIQUE NONCLUSTERED INDEX Index_userAge_addTime
 ON T_UserInfor(userName,addTime)
 WITH FILLFACTOR=10;

　索引的命名习惯：Index_字段名， Index_字段1名_字段2名

 

六. 测试索引对查询性能的影响

 　　后续补充

 

 

 

 

!

• 作       者 : Yaopengfei(姚鹏飞)
• 博客地址 : http://www.cnblogs.com/yaopengfei/
• 声     明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
• 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。