数据库索引相关和 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 索引可以分为主索引和辅助索引。
C/C++Linux服务器开发高级架构师/C++后台开发架构师免费学习地址
【文章福利】另外还整理一些C++后台开发架构师 相关学习资料,面试题,教学视频,以及学习路线图,免费分享有需要的可以点击领取
三. 设计原则
索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要,设计索引时,应该考虑以下原则:
(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:以上是索引的基本创建格式,下面补充创建索引的其它参数配置:
例 1:在 teacher 表中的 t_phone 列上,创建一个名称为 Idx_phone 的唯一聚集索引,降序排列,填充因子为 30%
例 2:在 teacher 表中的 t_name 和 t_gender 列上,创建一个名称为 Idx_nameAndgender 的-唯一非聚集组合索引,升序排列,填充因子为 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 中对应的生成索引的语句如下:
索引的命名习惯:Index_字段名, Index_字段 1 名_字段 2 名
原文链接:第十三节:数据库索引相关和 EFCore 的索引映射 - Yaopengfei - 博客园
评论