视图和索引的作用视图与索引

转载

智能探索者之家 2024-06-06 20:23:30

文章标签 视图和索引的作用数据数据库 Server 文章分类 数据仓库大数据

1.视图
视图(View)作为一种数据库对象，为用户提供了一个可以检索数据表中的数据方式。用户通过视图来浏览数据表中感兴趣的部分或全部数据，而数据的物理存储位置仍然在表中。
视图是一个虚拟表，并不代表任何物理数据，只是用来查看数据的窗口而已。视图并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，而不存储视图对应的数据，这些数据仍存储在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。
视图中的数据行和列都是来自于基本表，是在视图被引用时动态生成的。使用视图可以集中、简化和制定用户的数据库显示，用户可以通过视图来访问数据，而不必直接去访问该视图的基本表。
视图由视图名和视图定义两个部分组成。视图是从一个或多个表导出来的表，它实际上是一个查询结果，视图的名字和视图对应的查询存储在数据字典中。

视图的优点
1.数据安全性。
对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上。
2.查询简单化。
为复杂的查询建立一个视图，用户不必输入复杂的查询语句，只需针对此视图做简单的查询即可。那些被经常使用的查询可以被定义为视图，从而使用户不必为以后的操作每次都指定全部的条件。
3.逻辑数据独立性。
视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。
对于视图的操作，例如，查询只依赖于视图的定义，当构成视图的基本表需要修改时，只需要修改视图定义中的子查询部分，而基于视图的查询不用改变。

视图的缺点
1.性能。
SQL Server必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，SQL Server也把它变成一个复杂的结合体，需要花费一定的时间。
2.修改限制。
当用户试图修改视图的某些行时，SQL Server必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的，这些视图有如下特征：
　　a.有UNIQUE等集合操作符的视图。
　　b.有GROUP BY子句的视图。
　　c.有诸如AVG\SUM\MAX等聚合函数的视图。
　　d.使用DISTINCT关键字的视图。
　　e.连接表的视图（其中有些例外）
3.创建视图的限制：
在创建视图时，还要注意试图必须满足以下几点限制：
(1).不能将规则或者DEFAULT定义关联于视图。
(2).定义视图的查询中不能含有ORDER BY\COMPURER\COMPUTER BY 子句和INTO关键字
(3).如果视图中某一列是一个算术表达式、构造函数或者常数，而且视图中两个或者更多的不同列拥有一个相同的名字(这种情况通常是因为在视图的定义中有一个连接，而且这两个或者多个来自不同表的列拥有相同的名字)，此时，用户需要为视图的每一列指定列的名称。

创建视图：
create view viewName(参数列表/可以不写这样就是默认和下面的select一样) as select * from 表名;
例：
(1)、create table viewdemo(id int primary key , name char(10)，score float);
(2)、create view view1 as select* from viewdemo;
(3)、create view view2(name,score) as select name ,score from viewdemo;

修改视图：
alter view viewname (columns) as select columns from tableName ;
例：
alter view view2 (id ,name) as select id,name from viewdemo ;

删除视图：
drop view if exists view name;
列：
drop view view2;

2.索引

1、

索引是以表列为基础的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序，其主要目的是提高SQL Server系统的性能，加快数据的查询速度和减少系统的响应时间。索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。相反，如果没有索引，则会导致SQL Server搜索表中的所有记录，以获取匹配结果。

索引除了可以提高查询表内数据的速度以外，还可以使表和表之间的连接速度加快。例如，在实现数据参照完整性时，可以将表的外键制作为索引，这样将加速表与表之间的连接。

索引，可以理解为是一本书的目录，它记录了数据在数据库中存放的位置，就像一本书的目录，记录了，某一页在这本书的那个位置。相同地，索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据库表里所有记录的引用指针。

2、索引的好处

适当使用索引能提升数据库查询速度！

3、实例：

在创建表的时候创建索引

CREATE TABLE 表名 [ 列名称数据类型 ] [ UNIQUE | FULLTEXT ] [ INDEX | KEY ] [ 索引名称 ] ( 列名称 [ length ] ) [ ASC | DESC ]

说明： UNIQUE 、 FULLTEXT 为可选参数，分别表示唯一索引、全文索引；INDEX 与 KEY为同义词，两者的作用相同，用来指定索引；

（1）、

普通索引（index）：普通索引是MySQL的基本索引类型，允许在定义索引的列中插入重复值和空值

例：

CREATE  TABLE book 

( 

bookid   INT  NOT  NULL, 

bookname VARCHAR(100)  NOT  NULL, 

authors  VARCHAR(100)  NOT  NULL, 

info  VARCHAR(500) NULL, 

year_publication   YEAR  NOT  NULL, 

INDEX(year_publication) 

);

（2）、

唯一索引(unique)：唯一索引列的值必须唯一，但允许有空值。主键索引是一种特殊的唯一索引，不允许有空值。

例：

CREATE TABLE  book 

( 

  id  INT  NOT  NULL, 

  name  CHAR(50)  NOT  NULL, 

  UNIQUE  INDEX   UniqueIdx(id) 

);

(3)、

联合索引：组合索引即是在多个列上创建索引。查询时，只有在查询条件中使用了这些字段（创建组合索引的时候指定的哪些列）的最左边字段时，索引才会被使用。

CREATE TABLE student 

( 

  id  INT  NOT  NULL, 

  name  CHAR(50)  NOT  NULL, 

  age  INT  NOT  NULL, 

  info  VARCHAR(200), 

  INDEX   MultiIdx(id,name,age) 

);

(4)、

全文索引：MySQL只有MyISAM存储引擎支持FULLTEXT索引，并且类类型为CHAR、TEXT、VARCHAR。并且需要指定表的存储引擎为MyISAM。

例：

CREATE TABLE t4 

( 

  id  INT  NOT  NULL, 

  name  CHAR(50)  NOT  NULL, 

  age  INT  NOT  NULL, 

  info  VARCHAR(200), 

  FULLTEXT INDEX   FullindexName(info) 

) ENGINE = MyISAM ;

在已经存在的表上创建索引：

语法：

ALTER TABLE table_name ADD [ UNIQUE | FULLTEXT ] [ INDEX | KEY ]

[ inex_name ] (col_name [ length ] ,...) [ASC | DESC ]

(1)、普通索引：ALTER TABLE book ADD INDEX indexName( bookname(30) );

(2)、唯一索引：ALTER TABLE book ADD UNIQUE INDEX UniqueIdx( bookid );

(3)、组合索引：ALTER TABLE book ADD INDEX BkAndInfoIdx( authors(20), info(50) );

(4)、全文索引：ALTER TABLE t6 ADD FULLTEXT INDEX infiIdx(info);（前提是这个表的存储引擎为 MyISAM）

使用 create index 创建索引：

语法：

CREATE [ UNIQUE | FULLTEXT ] [ INDEX | KEY ] INDEX index_name

ON table_name ( col_name[ length ] , ... ) [ASC | DESC ]