第五章 视图和索引

5.1 视图概述

　　1. 视图概述

　　　　在MySQL中，视图是一个虚拟表，其内容由查询定义，即视图中的数据并不像表、索引那样需要占用存储空间，视图中保存的仅仅是一条select语句，其数据源来自于数据库表，或者其他视图。

　　　　当基本表发生变化是，视图的数据也会随之变化。

　　　　

　　　　视图与基本表之间的对应关系。

 

　　2. 视图的优势

　　　　增强数据安全性。

　　　　提高灵活性，操作变简单。

　　　　提高数据的逻辑独立性。

　　3. 视图的工作机制

　　　　当调用视图的时候，才会执行视图中的SQL，进行取数据操作。视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引用，视图的内容总是与真实表的内容是一致的。

5.2 视图定义和管理

　　1. 创建视图

　　　　创建视图需要具有create view的权限，同时应该具有查询涉及列的select权限。

　　　　语法格式：

create [algorithm = {undefined | merge | temptable}]
view 视图名 [(视图列表)]
as 查询语句
[with [cascaded | local] check option] 

　　　　【例1】下面在student表上创建一个简单的视图，视图名为student_view1。

　　　　【例2】下面在student表上创建一个名为student_view2的视图,包含学生的姓名、课程名以及对应的成绩。

 

 　　　　【例3】当我们在查询史琴雪的所有已修课程的成绩时，就可以借助视图很方便地完成查询。

 

　　　　创建视图时需要注意以下几点：

　　　　　　1.运行创建视图的语句需要用户具有创建视图(crate view)的权限。

　　　　　　2.select语句不能包含from子句中的子查询。

　　　　　　3.select语句不能引用系统或用户变量。

　　　　　　4.select语句不能引用预处理语句参数。

　　　　　　5.在存储子程序内，定义不能引用子程序参数或局部变量。

　　　　　　6.在定义中引用的表或视图必须存在。

　　　　　　7.在定义中不能引用temporary表，不能创建temporary视图。

　　　　　　8.在视图定义中命名的表必须已存在。

　　　　　　9.不能将触发程序与视图关联在一起。

　　　　　　10.在视图定义中允许使用order by。

 

　　2. 删除视图

　　　　删除视图时，只能删除视图的定义，不会删除数据。其次用户必须拥有drop权限。

　　　　语法格式：

drop view [if exists]
view_name[，view_name2]…
restrict | cascade] 

　　　　【例4】下面将删除视图student_view1

　　3. 查看视图定义

　　　　查看视图是指查看数据库中已经存在的视图的定义。查看视图必须要有show view的权限。

 　　　　查看视图的方法包括以下几条语句，他们从不同的角度显示视图的相关信息

　　　　1) describe语句，语法格式：describe 视图名称; 或者desc视图名称;

　　　　2) show table status语句,语法格式: show table status like '视图名'

　　　　3) show create view语句，语法格式：show create view '视图名'

　　　　4) 查询information_schem数据库下的views表 语法格式：select * from information_schema.views where table_name ='视图名'

　　　　 【例5】查看student_view2视图的信息

　　　　　　方式一、describe

 

　　　　　　方式二、show table status

 

 

　　　　　　方式三、show create view

 

 

　　　　　　方式四、information_schema.views

 

　　4. 修改视图定义

 　　　　修改视图是指修改数据库中已经存在表的定义。

　　　　 (1) create or replace view 语句格式

create or replace  [algorithm = {undefined | merge | temptable}]
    view 视图名[ { 属性清单 } ]
    as select 语句
    [ with [ cascaded | local ] check option];

　　　　　　【例6】修改视图student_view2的列名为姓名、选修课、成绩。

 

　　　　(2) alter 语句格式

create or replace  [algorithm = {undefined | merge | temptable}]
    view 视图名[ { 属性清单 } ]
    as select 语句
    [ with [ cascaded | local ] check option];

　　　　　　【例7】把student_view2 列的名称再改为sname,cname,grade

 

 

5.3 更新视图数据

　　更新视图数据

　　　　对视图的更新其实就是对表的更新，更新视图是指通过视图来插入（insert）、更新（update）和删除（delete）表中的数据。

　　　　通过视图更新时，都是转换到基本表来更新。

　　　　更新视图时，只能更新权限范围内的数据。

　　　　【例8】通过视图对student表进行更新。

　　　　原则：尽量不要更新视图

 　　以下情况视图无法更新

　　　　视图中包含sum()，count()等聚集函数的；

　　　　视图中包含union、union all、distinct、group by、having等关键字的；

　　　　常量视图，比如：create view view_now as select now() ；

　　　　视图中包含子查询；

　　　　由不可更新的视图导出的视图；

　　　　创建视图时algorithm为temptable类型；

　　　　视图对应的表上存在没有默认值的列，而且该列没有包含在视图里；

　　　　with [cascaded|local] check option也将决定视图是否可以更新

 

　　对视图的进一步说明

　　　　视图是在原有的表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息。

　　　　视图的作用归纳为如下几点：

　　　　　　使操作简单化：视图需要达到的目的就是所见即所需。

　　　　　　增加数据的安全性：通过视图，用户只能查询和修改指定的数据。

　　　　　　提高表的逻辑独立性：视图可以屏蔽原有表结构变化带来的影响。

 

5.4 索引概述

　　1. 索引概述

 　　　　在MySQL中，索引其实与书的目录非常的相似，由数据表中一列或多列组合而成，创建索引的目的是为了优化数据库的查询速度，提高性能的最常用的工具。

　　　　 所有MySQL列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。

　　　　 索引有两种存储类型：B型树（BTREE）索引和哈希（HARSH）索引。其中B型树为系统默认索引存储类型。

　　2. 索引的作用

　　　　（1）索引优点

　　　　　　通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

　　　　　　可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

　　　　　　可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

　　　　　　在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

　　　　　　通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

　　　　（2）索引缺点

　　　　　　创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

　　　　　　索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

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

　　　　（3）索引特征

　　　　　　索引有两个特征，即唯一性索引和复合索引。

　　　　　　唯一性索引保证在索引列中的全部数据是唯一的，不会包含冗余数据。

　　　　　　复合索引就是一个索引创建在两个列或者多个列上。

　　3. 索引的分类

　　　　普通索引：在创建普通索引时，不附加任何限制条件。

　　　　唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引。

　　　　全文索引：使用FULLTEXT参数可以设置索引为全文索引。

　　　　单列索引：在表中的单个字段上创建索引。

　　　　多列索引：多列索引是在表的多个字段上创建一个索引。

　　4. 创建索引

　　　　创建索引是指在某个表的一列或多列上建立一个索引。

　　　　直接创建索引，有以下三种方式。

　　　　在创建表的时候创建索引。

　　　　语法格式：

create table table_name
(
    属性名，数据类型 [完整性约束],
   属性名，数据类型 [完整性约束],
   . . .
   属性名，数据类型 [完整性约束],
   index | key [索引名] ( 属性名 [ ( 长度 ) ] [ asc | desc ])
);

　　　　在已存在的表上创建索引

　　　　语法格式： 

create index 索引名 ON 表名 (属性名 [ ( 长度) ] [ asc | desc ]);

　　　　使用alter table语句来创建索引。

　　　　语法格式：

alter table table_name
add index | key [索引名] ( 属性名 [ ( 长度 ) ] [ asc | desc ]) 

　　　　间接创建索引

　　　　　　通过定义主键约束或者唯一性键约束，也可以间接创建索引。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇索引。

　　　　　　主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

　　　　　　（1）普通索引

　　　　　　　　创建一个普通索引时，不需要加任何unique、fulltext或者sparial参数。

　　　　　　　　【例1】创建一个新表newTable，包含int 型的id字段、varchar(20)类型的name字段和int型的age字段。

　　　　　　（2）唯一索引(unique index)

　　　　　　　　 创建唯一性索引时，需要使用unique参数进行约束

　　　　　　　　【例5】创建新表newTable1，在表的id字段上建立名为id_index的唯一索引，以升序排列。

 

　　　　　　（3）全文索引(fulltext index)

　　　　　　　　全文索引只能创建在char、varchar或者text类型的字段上。

　　　　　　　　【例8】创建表newTable2，并指定char(20)字段类型的字段Info为全文索引。

 

　　　　　　（4）多列索引

　　　　　　　　多列索引是在多个字段上创建一个索引

　　　　　　　　【例9】创建表newTable3，在类型char(20)的name字段上和int类型的age字段上创建多列索引。　　　　　 

 

　　5. 查看索引

　　　　在实际使用索引的过程中，有时需要对表的索引信息进行查询，了解在表中曾经建立的索引。

 　　　　语法格式：

INDEX FROM table_name [FROM db_name] 

　　　　另一种语法格式：

SHOW INDEX FROM mytable FROM mydb;                                                   
SHOW INDEX FROM mydb.mytable; 

　　　　【例10】查看newTable中索引的详细信息。

 

　　6. 删除索引

　　　　创建索引后，如果用户不再使用该索引，可以删除指定表的索引。

　　　　语法格式：

drop index index_name on table_name ;                                                 
alter table table_name drop index index_name ;                                        
alter table table_name drop primary key ; 

　　　　【例11】下面删除newTable中的索引的name_index。

　　　　注意：如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

 

　　7. 设计原则和注意事项

　　　　1) 索引的设计原则

　　　　　　选择唯一性索引

　　　　　　为经常需要排序、分组和联合操作的字段建立索引

　　　　　　为常作为查询条件的字段建立索引

　　　　　　限制索引的数目

　　　　　　尽量使用数据量少的索引

　　　　　　尽量使用前缀来索引

　　　　　　删除不再使用或者很少使用的索引

　　　　2)合理使用索引注意事项

　　　　　　在经常需要搜索的列上，可以加快搜索的速度。

　　　　　　在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构

　　　　　　在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。

　　　　　　在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

　　　　　　在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

　　　　　　经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。

　　　　3)不合理使用索引的注意事项

　　　　　　不应该创建索引：

　　　　　　　　对于那些在查询中很少使用或者参考的列不应该创建索引

　　　　　　应该增加索引用：

　　　　　　　　对于那些只有很少数据值的列也不应该增加索引用

　　　　　　不应该增加索引：

　　　　　　　　对于那些定义为text、image和bit数据类型的列不应该增加索引。

　　　　　　不应该创建索引：

　　　　　　　　当修改性能远远大于检索性能时，不应该创建索引。