【MySql】---索引与视图
【MySql】---索引与视图
一、索引
1、理解索引
- MySQL的索引视为了加速对数据进行检索而创建的一种分散的、物理的数据结构。
- 索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。
- 表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面，索引就存放在索引页面上。
- 索引的作用：
- 加速数据检索：索引能够以一列或者多列值为基础实现快速查找数据行。
- 优化查询：查询优化器是依赖于索引起作用的，索引能够加速连接、排序和分组等电器哦啊诺。
- 强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。
2、索引的分类
①普通索引（index）
索引的关键字是index，普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
②主键索引（primary key）
主键索引是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，也可以通过修改表的方法增加主键，但是一个表只能有一个主键索引。
③唯一性索引（unique）
unique索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一，在一个表上可以创建多个唯一性索引。
④全文索引（fulltext）
全文索引是指在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值。
⑤空间索引（spatial）
空间索引是对空间数据类型的字段建立的索引。MySQL中的空间数据类型有4种，分别是geometry、point、linestring和polygon。
3、设置索引的原则
- 一个表创建大量索引，会影响insert、update和delete语句的性能。应避免对经常更新的表创建过多的索引，要限制索引的数目。
- 若表的数据量大，对标数据的更新较少而查询较多，可以创建多个索引来提高性能。在包含大量重复值的列上创建索引，查询的时间会较长。
- 经常需要排序、分组和联合操作的字段一定要建立索引，即将用于join、where判断和order by排序的字段上创建索引。
- 在视图上创建索引可以显著地提升查询性能。
- 尽量不要对数据库中某个含由大量重复值的字段建立索引，在这样的字段上建立索引，会降低数据库性能。
- 在主键上创建索引，在InnoDB中如果通过主键来访问数据效率是非常高的。每个表只能创建一个主键索引。
- 要限制索引的数目，对于不再使用或者很少使用的索引要及时的删除。
- InnoDB数据引擎的索引键最长支持767字节，MYISAM数据引擎支持1000字节。
4、创建与删除索引
①通过create index创建
create [unique|fulltext|spatial] index index_name
on table_name (index_col_name,...)
-
index_col_name格式：
- col_name[(length)] [asc | desc]
创建索引时，可以使用col_name(length)语法对前缀编制索引。前缀包括每列值得前length个字符。对于char和varchar列，只用一列的一部分就可以创建索引。blob和text列也可以编制索引，但是必须给出前缀长度。因为多数名称的前10个字符通常不同，所以创建索引不会比使用列的全名创建索引的速度慢很多。
另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省大量的磁盘空间。
例：为便于按电话进行查询，在student表的phone列上建立一个升序普通索引phone_index、
create index phone_index on student(phone asc);
例：在course表的cname列上建立一个唯一性索引cname_index
create unique index cname_index on course (cname);
例：在score表的studentno和courseno列上建立一个复合索引sc_index。
create index sc_index on score(studentno,courseno);
②通过alter table语句创建
alter table teacher1
add index mark(tname,prof);
③删除索引
drop index index name on table_name;
二、视图的创建和管理
视图是从一个或者多个表及其他视图中通过select语句导出的虚拟表，数据库中只存放了视图的定义，而并没有存放视图的数据。浏览视图的时所对应的行和列数据来自定义视图查询所用的表，并且在引用视图时动态生成。通过视图可以实现对基表数据的查询与修改。
视图的便利：
- 简化数据查询和处理。视图可以为用户集中多个表中的数据，简化用户对数据的查询和处理。
- 屏蔽数据库的复杂性。数据库表的更改不影响用户对数据库的使用，用户页不需要了解复杂的数据库中的表结构。比如那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐藏起来了。
- 安全性。如果想要使用户只能查询或者修改用户有权限访问的数据，也可以只授予用户访问视图的权限，而不授予访问表的权限，这样就提高了数据库的安全性。
1、创建视图
创建视图指的是，在指定的数据库表上建立视图。视图可以创建在一张表上，也可以创建在多张表或既有视图上。
要求创建用户具有针对视图的create view权限，以及针对由select语句选择的每一列上的某些权限。
语法形式
create [or replace][alogorithm = {undefined|merge|temptable}]
view view_name [(column_list)]
as select_statement
[with[ cascaded|local] check option];
- or replace子句：替换已有的视图
- view_name：视图名，视图属于数据库。
- algorithm：视图算法选择，undefined表示mysql自动选择，merge表示将合并视图定义和视图语句，使得视图定义的某一部分取代语句的对应部分。temptable表示将视图结果存储到临时表，然后利用临时表执行语句。
- select_statement：用来创建视图的select语句，它给出了视图的定义。这个语句可以从基本表或者其他视图进行选择。默认情况下，由select语句检索的列名将用作视图列名。如果想为视图列定义另外的名称，可使用可选的column_list子句，列出由都好隔开的列名称即可。但要注意，column_list中的名称数目必须等于select语句检索的列数。
- cascaded|local：可选参数，cascaded为默认值，表示更新视图的时候要满足所有相关视图和表的条件。local表示更新视图时满足该视图本身的定义即可。
- [with check option]：要求具有针对视图的create view语句权限，以及针对由select语句选择列上的某些权限。对在select语句中使用其他来源的列，必须具有select语句权限，如果还有[or replace]语句，则必须具有drop权限。
- 在视图定义中命名的表必须以及存在，视图必须具有唯一的列名，不得有重复，就像基本表那样。还要有如下限制：
- 在视图的from子句中不能使用子查询
- 在视图的select语句中不能引用系统或者用户变量
- 在视图的select语句中不能引用预处理语句参数
- 在视图定义中允许使用order by，但是，如果从特定试图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。
- 在定义中引用的表或者视图必须存在，但是，创建了视图之后，能够舍弃定义引用的表或者视图。要想检查视图定义是否存在这类问题，可以使用check table语句。
- 在定义中不能引用temporary表，不能创建temporary视图。
- 不能将触发程序与视图关联在一起。
在单表上创建视图
create view teach_view1
as select * from teacher;
在多表上创建视图
在student、course、score表上创建一个名为stu_score1的视图。视图中保留18级女生的学号、姓名、课程名和期末成绩
create view stu_score1
as select student.studentno,sname,phone,cname,final
from score join student on student.studentno = score.studentno
join course on course.courseno = score.courseno
where sex = '女' and left(student.studentno,2) = '18';
在已经存在的视图上创建视图
创建视图teach_view2，统计计算机学院的教师中的教授和副教授的教师号、教师名和专业。
create view teach_view2
as select teacherno,tname,major
from teach_view1
where prof like '%教授' and department='计算机学院';
说明：
-
定义视图时候，基本表可以使当前数据库的表，也可以是来自于另外一个数据库的基本表，只要在表前面添加数据库名称即可，比如mysql.student02
-
定义视图时候，可以再试图后面指明视图列的名称，名称之间使用逗号分割，但列数要与select语句检索的列数相等。例如：
create view teach_view2(教师号,教师名,专业) as selct teacherno,tname,major.... -
使用视图查询的时候，如果其基本表中添加了新字段，则该视图将不包含新字段。
-
如果与视图相关联的表或者视图被删除，则该视图将不能再使用。
2、查看视图的定义
查看视图指的是查看数据库中已经存在的视图的定义，必须要有show view的权限，mysql的user表中保存着这个信息。查看视图的方法包括describe语句、show table status语句、show create view语句和查询information_schema数据库下面的views表等等。
3、修改视图
alter [algorithm = {undefined|merge|temptable}]
view view_name{(column_list)}
as select 语句
[with [cascaded|local]check optioon]
alter view teach_view2(教师号,教师名,专业)
as select teacherno,tname,major
from teach_view1
where prof like '%教授'
and (department='计算机学院' or department='材料学院')
4、删除视图
drop view [if exists]viewname1[,...] [restrict|cascaded]
三、视图的应用
视图的使用主要包括视图的检索，以及通过视图对基表进行插入、修改、删除操作。视图的检索几乎没有什么限制，但是对通过视图实现表的插入、修改、删除操作则有一定的限制条件。
1、使用视图管理表数据
使用视图进行查询
使用视图进行查询实际上就是把视图作为数据源，实现查询功能。
例：通过stu_view2查询选修课程号为c08123、且成绩在80分以上的18级男生的学号、课程号和成绩
select 学号,姓名,课程号,成绩
from stu_view2
where 课程号='c08123' and 成绩>80;
使用视图进行统计计算
create view course_avg
as selct cname 课程名,avg(final) 平均成绩
from score join course on score.courseno = course.courseno
group by cname desc;
使用视图修改基本表数据
在视图中进行insert、update和delete等操作而修改基本表的数据。
insert into teach_view1(teacherno,tname,major,prof,department)
values('t0627','张三','纳米技术','教授','材料学院')
update teach_view1 set prof = '副教授' where teacherno = 't08019'
delete from teach_view1 where teacherno = '111'
说明：
- 视图如果只依赖于一个基表，则可以直接通过视图来更新基本表的数据。
- 如果一个视图依赖于多张基表，则一次只能修改一个基表的数据，不能同时修改多个基表的数据。
- 如果视图中包含下述结构中的任何一种，都是不可以修改的：
- 视图的列含有聚合函数
- 视图的列是通过表达式并使用列计算出其他列
- 含有distinct关键字
- 含有group by 子句、order by和having子句
- 含有union运算符
- 视图的列位于选择列表中的子查询
- from子句中包含多个表
- select语句中引用了不可更新的视图
- where子句中的子查询，引用from子句中的表
2、检查视图的应用
视图分为普通视图和检查视图。当没有with_check_option时，表示with_check_option的值为0。即为普通视图，普通视图不具备检查功能，如果使用了with check option子句，在通过检查视图更新基表数据的时候，只有满足检查条件的更新语句才能执行成功。
例：在teaching数据库中创建一个名称为V_dept的视图，包含所有部门为"计算机学院"的老师的数据信息，需要限制插入数据中心部门必须为计算机学院
create view V_dept
As
select teacherno,tname,major,prof,department
from teacher
where department='计算机学院'
with cheack option;
