索引

概念:索引是对数据库表中一列或多列的值进行排序的一种结构(B树结构/哈希结构),使用索引可快速访问数据库表中的特定信息
          数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录

作用:加快检索表中的数据,协助信息搜索者尽快找到符合限制条件的记录

关键字:index

从数据搜索实现的角度来看,索引也是另外一类文件/记录,它包含着可以指示出相关数据记录的各种记录。其中,每一索引都有一个相对应的搜索码,字符段的任意一个子集都能够形成一个搜索码。这样,索引就相当于所有数据目录项的一个集合,它能为既定的搜索码值的所有数据目录项提供定位所需的各种有效支持

基本概念
  • 搜索码:是记录各种字符段的一个集合,可以是一个或者多个字符段的任意序列组合,并不是唯一的一个标识记录
  • 数据目录项:索引的相关元素,在建立索引的过程中,数据目录项一般具有各种不同的选择方式
  • 记录ID:每一个/段索引在存储内容中唯一的一个标识符

分类

聚簇索引
按照数据存放的物理位置为顺序
多行检索速度快
非聚簇索引
与数据存放的物理位置无关
单行检索速度快
根据数据库的功能,可以在数据库设计器中创建三种索引:唯一索引、主键索引和聚集索引

唯一索引:是不允许其中任何两行具有相同索引值的索引
主键索引:在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型
聚集索引:在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。比如字典的按字母拼音查找为聚集索引,按偏旁部首查找为非聚集索引

每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。一个表最多可以创建250个索引(1个聚簇索引,249个非聚簇索引,如果没有聚簇索引可以创建250个非聚簇索引)

索引列:创建索引的列称为索引列,可以基于数据库表的单列或多列创建索引。多列索引可以区分其中一列有可能有相同值的行。例如,经常在同一查询中用到姓和名两列,那么在这两列上创建多列索引将会很有意义

    索引列选取:
        检查查询的where和join子句,在任一子句中包括的每一列都是索引可以选择的对象。
        检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型

    创建索引注意事项:
        a.考虑已在表上创建的索引数量,最好避免在单个表上有很多索引
        b.检查已在表上创建的索引的定义,最好避免包含共享列的重叠索引

基本特点
  1. 为表设置索引要付出代价
  2. i.增加了数据库的存储空间
    ii.在插入和修改数据时要花费较多的时间,因为索引也要随之变动

  3. 创建索引可以提高系统的性能
         i.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
         ii.可以大大加快数据的检索速度,这也是创建索引的最主要的原因
         iii.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义
         iv.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间
         v.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能

     3. 增加索引也有不利的方面
         i.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
         ii.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大
         iii.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度

优点
  • 在设计数据库时,通过创建一个惟一的索引,能够在索引和信息之间形成一对一的映射式的对应关系,增加数据的惟一性特点
  • 能提高数据的搜索及检索速度,符合数据库建立的初衷
  • 能够加快表与表之间的连接速度,这对于提高数据的参考完整性方面具有重要作用
  • 在信息检索过程中,若使用分组及排序子句进行时,通过建立索引能有效的减少检索过程中所需的分组及排序时间,提高检索效率
  • 建立索引之后,在信息查询过程中可以使用优化隐藏器,这对于提高整个信息检索系统的性能具有重要意义

缺点
  • 在数据库建立过程中,需花费较多的时间去建立并维护索引,特别是随着数据总量的增加,所花费的时间将不断递增
  • 在数据库中创建的索引需要占用一定的物理存储空间,这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间,如果有必要建立起聚簇索引,所占用的空间还将进一步的增加
  • 在对表中的数据进行修改时,例如对其进行增加、删除或者是修改操作时,索引还需要进行动态的维护,这给数据库的维护速度带来了一定的麻烦

语法
//创建索引

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX 索引名 ON 表名(列名[,列名...])
                                    
//删除索引

DROP INDEX 索引名 ON 表名

//查看表中创建的索引

SP_HELPINDEX 表名

//MySQL查看创建的索引

SHOW CREATE TABLE 表名

//MySQL查看索引详情

EXPLAIN SELECT * FROM 表名 WHERE 限制条件
EXPLAIN语句输出结果的各个行解释如下:
1)select_type行指定所使用的SELECT查询类型,有可能取值有SIMPLE、UNIQUE、PRIMARY、UNION、SUBQUERY等
2)table行指定数据库读取的数据表的名字,按被读取的先后顺序排列
3)type行指定了本数据表与其他数据表之间的关联关系,可能的取值有system、const、eq_ref、ref、range、index和all
4)possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引
5)key行是MySQL实际选用的索引
6)key_len行给出索引按字节计算的长度,key_len数值越小,表示越快
7)ref行给出了关联关系中另一个数据表里的数据列的名字
8)rows行是MySQL在执行这个查询时预计会从这个数据表里独处的数据行的个数
9)extra行提供了关联操作有关的信息

注意组合索引可起几个索引的作用,但使用时并不是随便查询哪个字段都可以使用索引,而是遵从“最左前缀”,即利用索引中最左边的列集来匹配行




视图

定义:视图是一个虚拟的表(不占用物理空间),该表提供了对一个或多个表中一系列列的访问。它是一个或多个表中派生出来的数据库对象

本质:视图是对一段查询SQL语句的定义,对视图的查询本质上都会转换为对基表的查询

存放:视图存放在系统表中
                sysobjects:存放视图名
                syscolumns:存放视图中定义的列
                sysdepends:存放视图的依赖关系
                syscomments:存放视图语句的文本

优点:把数据限定在特定的行或者列上,一定程度上实现了权限管理
          屏蔽基表的复杂性,简化数据库用户管理,使多个表看起来像一个表
          改进性能,聚合信息而非提供详细信息。例如显示一个列的和或者最大最小值

缺点:性能方面,SQL Server必须把视图的查询转化成对基表的查询,如果这个视图是由一个复杂的多表查询所定义,那么即使是视图的一个简单查询也会花费一定的时间
          修改限制方面,当用户要修改视图的某些行时,SQL Servere必须把它转化为对基表的某些行的修改。对于比较复杂的视图来说这样的修改是很麻烦的

语法:
//创建视图

CREATE VIEW 视图名
AS
    SELECT 列名1,列名2,...
    FROM 表名
    [WHERE 限制条件]
    [WITH CHECK OPTION]

//修改视图名

EXEC SP_RENAME '旧视图名','新视图名'

//MySQL查看视图基本信息(查看表信息)

DESCRIBE 视图名
SHOW TABLE STATUS LIKE '视图名'

//MySQL查看视图详细信息

SHOW CREATE VIEW 视图名

//删除视图

DROP VIEW 视图名

创建好视图之后就可以把视图当表来操作,可以使用insert、update来对表中的数据进行操作,需要注意的是视图中的字段必须包含数据表中所有的必填字段,如果是由多个表的字段构成的视图,在进行数据操作的时候切记不能一起操作,需要对每个数据表进行操作

通过视图修改数据的限制
  • 不能对某些特别的列进行操作
  • 不能在视图中修改在视图中没有参照的列
  • 不能在含有视图中没有参照的列的表中利用视图做插入操作
  • 不能影响多于一个的基表
  • 如果某一个视图在定义的时候指定了WITH CHECK OPTION选项,则在进行数据修改的时候进行验证

MySQL中视图和表的区别以及联系??
区别:
    i.视图是已经编译好的SQL语句,是基于SQL语句的结果集的可视化的表,而表不是
    ii.视图没有实际的物理记录,而基本表有
    iii.表是内容,视图是窗口
    iv.表占用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时对它进行修改,但视图只能用创建的语句来修改
    v.视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全的角度来说,视图可以防止用户接触数据表,因而用户不知道表结构
    vi.表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表
    vii.视图的建立和删除只影响视图本身,不影响对应的基本表

联系:
    视图是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有记录)都来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表抽象和逻辑意义上建立的新关系