第六章 - 索引和视图

第六章 - 索引和视图

6.1 索引

在MySQL数据库中，索引(Index)是影响数据性能的重要因素之一，设计高效的、合理的索引可以显著提高数据信息的查询速度和应用程序的性能。

视图（View）是一个存储指定查询语句的虚拟表，视图中数据来源于由定义视图所引用的表，并且能够实现动态引用，即表中数据发生变化，视图中的数据随之变化。

本章将介绍索引和视图等数据库对象的基本概念和常用操作。

索引由数据库表中一列或多列组合而成的一种特殊的数据库结构，利用索引是可以用来快速查询数据库表中的特定记录信息。在MySQL中，所有的数据类型都可以被索引。

6.1.1 索引概念

MySQL的索引是为了加速对数据进行检索而创建的一种分散的、物理的数据结构。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据行的存储位置指针。
索引是依赖于表建立的，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。

数据库中的索引的形式与图书的目录相似，键值就像目录中的标题，指针相当于页码。索引的功能就像图书目录能为读者提供快速查找图书页面内容一样，不必扫描整个数据表而找到想要的数据行。

当MySQL数据库在执行一条查询语句的时候，默认的执行过程是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果查询语句涉及多个表连接，包括了许多搜索条件（例如大小比较、like匹配等），而且表数据量特别大的时，在没有索引的情况下，MySQL需要执行的扫描行数会很大，速度也会很慢。

索引一旦创建，将由数据库自动管理和维护。例如，向表中插入、更新和删除一条记录时，数据库会自动在索引中做出相应的修改。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。实际过程中，当MySQL执行查询时，查询优化器会对可用的多种数据检索方法的成本进行估计，从中选用最有效的查询计划。

在数据库中使用索引的优点如下。

（1）加速数据检索：索引能够以一列或多列值为基础实现快速查找数据行。

（2）优化查询：查询优化器是依赖于索引起作用的，索引能够加速连接、排序和分组等操作。

（3）强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。

需要注意的是，索引并不是越多越好，要正确认识索引的重要性和设计原则，创建合适的索引。

6.1.2 索引分类

按照分类标准的不同，MySQL的索引有多种分类形式。 MySQL的索引通常包括普通索引（index）、唯一性索引（unique）、主键（primary key）、全文索引（fulltext）和空间索引（spatial）等类型。

1. 普通索引（index）

索引的关键字是index。普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。

2. 主键索引（primary key）

是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。也可通过修改表的方法增加主键，但一个表只能有一个主键索引。

3. 唯一性索引（unique）

unique索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。在一个表上可以创建多个unique索引。

4. 全文索引（fulltext）

全文索引是指在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。该索引只能对char、varchar和text类型的列编制索引，并且只能在MyISAM表中编制。即MySQL中只有MyISAM存储引擎支持全文索引。在MySQL默认情况下，对于中文作用不大。

5. 空间索引（spatial）

空间索引是对空间数据类型的字段建立的索引。MySQL中的空间数据类型有4种，分别是geometry、point、linestring和polygon。MySQL使用spatil关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为not null，空间索引只有在存储引擎MyISAM的表中创建。对于初学者来说，这类索引很少会用到。

如果按照创建索引键值的列数分类，索引还可以分为单列索引和复合索引。

如果按照存储方式分类，MySQL的索引分为B-Tree索引和Hash索引。

MySQL的MEMORY数据引擎还支持Hash索引。Hash索引相对于B-Tree索引，检索效率要高上不少。

但MySQL Hash索引本身的特殊性也带来了很多限制和弊端，主要有以下内容。

(1) 仅仅能满足“=” ，“in”和“<=>”查询，不能使用范围查询。

(2) 无法被用来避免数据的排序操作。

(3) 无法被用来避免数据的排序操作。

(4) 不能利用部分索引键查询。 

(5) 在任何时候都不能避免表扫描。

(6) 遇到大量Hash值相等的情况后，性能并不一定就会比B-Tree索引高。

说明：

(1) MySQL中运算符“ <=>”除了能够像常规的“=”运算符一样，对两个值进行比较外，还能够用于比较null值。

(2) 运算符“ <=>”和“=”号的相同点。像常规的“=”运算符一样，两个值进行比较，结果是0（不相等）或1（相等）。例如，'A'<=>'B'的值为0，'a'<=>'a'的值为1。

(3) 运算符“ <=>”和“=”号的不同点。和“=”运算符不同的是，null与常量进行比较运算，其值直接处理为null。使用“ <=>”运算符时，例如，'a' <=> null 的值0   null<=> null 的值为 1；相当于 'a' is null和null is null。而'a' is not null 则相当于  not('a' <=> null)。

6.1.3 设置索引的原则

在数据表中创建索引，为使索引的使用效率更高，必须考虑在哪些字段上创建索引和创建什么类型的索引。首先要了解以下常用的基本原则。

(1) 一个表创建大量索引，会影响insert、update和delete语句的性能。应避免对经常更新的表创建过多的索引，要限制索引的数目。

(2) 若表的数据量大，对表数据的更新较少而查询较多，可以创建多个索引来提高性能。在包含大量重复值的列上创建索引，查询的时间会较长。

(3) 经常需要排序、分组和联合操作的字段一定要建立索引，即将用于join、 where判断和order by排序的字段上创建索引。

(4)在视图上创建索引可以显著的提升查询性能。

(5)尽量不要对数据库中某个含有大量重复的值的字段建立索引，在这样的字段上建立索引有可能降低数据库的性能。

(6)在主键上创建索引，在InnoDB中如果通过主键来访问数据效率是非常高的。每个表只能创建一个主键索引。

(7) 要限制索引的数目，对于不再使用或者很少使用的索引要及时删除。

(8)InnoDB数据引擎的索引键最长支持是767字节，MYISAM数据引擎支持1000字节。

6.1.4 创建索引

创建索引通常有3种命令方式，即创建表时附带创建索引、通过修改表来创建索引和使用alter table语句来创建索引。

1. 利用create index语句创建3种索引

如果基表已经创建完毕，就可以使用create index语句建立索引。

创建索引基本形式如下:

create [unique|fulltext|spatial] index index_name 
on table_name (index_col_name,...) 

创建索引时，可以使用col_name(length)语法对前缀编制索引。前缀包括每列值的前length个字符。对于char和varchar列，只用1列的一部分就可创建索引。blob和text列也可以编制索引，但是必须给出前缀长度。

因为多数名称的前10个字符通常不同，所以前缀索引不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高insert操作的速度。

【例6.1】为便于按电话进行查询，为student表的phone列上建立一个升序普通索引phone_index。

mysql> use teaching;
mysql> create index phone_index  on student(phone asc);

【例6.2】在course表的cname列上建立一个唯一性索引cname_index。

mysql> create  unique  index  cname_index on course (cname);

【例6.3】在score表的studentno和courseno列上建立一个复合索引sc_index。

mysql> create  index  sc_index  on  score(studentno,courseno);

2. 创建表时创建索引

创建表时可以直接创建索引，这种方式最简单、方便。

【例6.4】创建teacher1表的tname字段建立一个唯一性索引tname_index，一个前缀索引dep_index。

mysql> use mysqltest;
mysql> create table if not exists teacher1 (
    ->  teacherno  char(6) not null comment '教师编号',
    ->  tname  char(8) not null comment'教师姓名',
    ->  major  char(10) not null comment '专业',
    ->  prof char(10) not null comment '职称',
    ->  department char(16) not null comment '部门',
    ->  primary key (teacherno),
    ->  unique  index  tname_index(tname),
    ->  index  dep_index(department(5))
    ->  );

3. 通过alter table语句创建索引

【例6.5】在teacher1表上建立teacherno主键索引（假定未创建主键索引），建立tname和prof的复合索引。

mysql> alter table teacher1
    -> add primary key(teacherno),
    -> add index mark(tname, prof);

如果主键索引已经创建，则会出现如下信息：

ERROR 1068 (42000): Multiple primary key defined

说明：

(1) 只有表的所有者才能给表创建索引。索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。

(2) 主键索引必定是唯一的，唯一性索引不一定是主键。一张表上只能一个主键，但可以有一个或者多个唯一性索引。

(3) 当给表创建unique约束时，MySQL会自动创建唯一索引。创建唯一索引时，应保证创建索引的列不包括重复的数据，并且没有两个或两个以上的空值（null）。因为创建索引时将两个空值也视为重复的数据，如果有这种数据，必须先将其删除，否则索引不能被成功创建。 

(4) 若要查看表中已经创建索引的情况，可以使用show index from table_name语句实现。

6.1.5 删除索引

删除不再需要的索引，可以通过drop语句来删除索引，也可用alter table语句删除。

1. drop index

利用drop index语句删除索引的语法格式如下：

drop  index  index_name  on  table_name ; 

例如，删除teacher1表的mark索引。

mysql> drop index mark on teacher1; 

2. alter table

利用alter table语句删除索引的语法格式如下：

alter [ignore] table table_name 
| drop primary key
| drop index index_name
| drop foreign key fk_symbol

利用alter table语句同样可以删除前面表中创建的索引。例如：

mysql> alter table  course  drop  index  cname_index; 

6.2 视图的创建和管理

6.2.1 视图

视图是从一个或者多个表及其他视图中通过select语句导出的虚拟表，数据库中只存放了视图的定义，而并没有存放视图中的数据。

浏览视图时所对应数据的行和列数据来自定义视图查询所引用的表，并且在引用视图时动态生成。通过视图可以实现对基表数据的查询与修改。

视图为数据库用户提供了很多的便利，主要包括以下几个方面:

(1) 简化数据查询和处理

视图可以为用户集中多个表中的数据，简化用户对数据的查询和处理。

(2) 屏蔽数据库的复杂性

数据库表的更改不影响用户对数据库的使用，用户也不必了解复杂的数据库中的表结构。例如，那些定义了若干张表连接的视图，就将表与表之间的连接操作对用户隐蔽起来了。

(3) 安全性

如果想要使用户只能查询或修改用户有权限访问的数据，也可以只授予用户访问视图的权限，而不授予访问表的权限，这样就提高了数据库的安全性。

6.2.2 创建视图

创建视图是指在指定的数据库表上建立视图。视图可以建立在一张表上，也可以建立在多张表或既有视图上。

要求创建用户具有针对视图的create view权限，以及针对由select语句选择的每一列上的某些权限。创建视图的语法形式 。

语法形式如下：

create [or replace][algorithm ={ undefined|merge|temptable }]
view  view_name [(column_list)]
as select_statement
[ with[ cascaded|local] check option ] ;

创建视图有如下限制：

(1) 在视图的from子句中不能使用子查询。

(2) 在视图的select语句不能引用系统或用户变量。

(3) 在视图的select语句不能引用预处理语句参数。

(4) 在视图定义中允许使用order by，但是，如果从特定视图进行了选择，而该视图使用了具有自己order by的语句，它将被忽略。

(5)在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用check table语句。

(6)在定义中不能引用temporary表，不能创建temporary视图。

(7)不能将触发程序与视图关联在一起。

1. 在单表上创建视图

MySQL中可以在单个表上创建视图。

【例6.6】在teacher表上创建一个简单的视图，视图名称为teach_view1。

mysql> create  view teach_view1
    -> as select * from  teacher;

可以利用select语句查询视图teach_view1的数据如下。

mysql> select * from teach_view1;

2. 在多表上创建视图

MySQL数据库中也可以在两个或两个以上的表上创建视图。

【例6.7】在student表、course表和score表上创建一个名为stu_score1的视图。视图中保留18级的女生的学号、姓名、电话、课程名和期末成绩。

mysql> 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’;

此视图保存在3个表的数据，可以利用select语句查询视图stu_score1的数据如下。

mysql> select * from stu_score1;

3. 在已存在的视图上创建视图

【例6.8】创建视图teach_view2，统计计算机学院的教师中的教授和副教授的教师号、教师名和专业。

mysql> create view  teach_view2
    -> as select  teacherno, tname, major
    -> from  teach_view1
    -> where  prof  like  ‘%教授’  and  department=‘计算机学院’;

说明：

(1) 定义视图时基本表可以是当前数据库的表，也可以来自于另外一数据库的基本表，只要在表名前添加数据库名称即可，如mysql.student02。

(2) 定义视图时可在视图名后面指明视图列的名称，名称之间用逗号分隔，但列数要与select语句检索的列数相等。例如，定义视图teach_view2可以写成如下方式：
    create view  teach_view2(教师号,教师名,专业)
    as select  teacherno, tname, major ……

(3) 使用视图查询时，若其基本表中添加了新字段，则该视图将不包含新字段。

(4)如果与视图相关联的表或视图被删除，则该视图将不能再使用。

6.2.3 查看视图的定义

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有show view的权限，MySQL数据库下的user表中保存着这个信息。

查看视图的方法包括describe语句、show table status语句、show create view语句和查询information_schema数据库下的views表等。

1. describe语句查看视图基本信息

可以使用describe语句可以查看表的基本定义。

同样可以使用describe语句可以用来查看视图的基本定义。

describe语句查看视图的基本形式与查看表的形式是一样的。

mysql> describe stu_score1;

2. 利用show table status语句查看视图基本信息

MySQL中，可以使用show table status语句来查看视图的信息。

其语法形式如下：

show table status like ‘view_name’;

mysql> show table status like 'teach_view1';

3. 利用show create view语句查看视图详细信息

MySQL中，show create view语句可以查看视图的详细定义。

语法形式如下:

show create view view_name 

mysql> show create view teach_view2;

4. 在views表中查看视图详细信息

MySQL数据库中，所有视图的定义都存在information_schema数据库下的views表中。

例如，查询information_schema.views 表，可以查看到数据库中所有视图的详细信息。

代码如下：

select * from information_schema.views;

-- *表示查询所有的列的信息；

-- information_schema.views表示information_schema数据库下面的views表。 

6.2.4 修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。

MySQL中通过create or replace view语句和alter语句来修改视图。

1. create or replace view语句修改视图

MySQL中，create or replace view语句可以用来修改视图。该语句的使用非常灵活。

在视图已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。

create or replace view语句的语法形式如下：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

mysql> CREATE VIEW view_create_replace
    -> (id, category)
    -> AS 
    -> SELECT id, t_category FROM t_goods_category;
Query OK, 0 rows affected (0.01 sec)

在使用CREATE OR REPLACE VIEW语句修改MySQL视图时，需要注意以下几点：

(1) 视图只能修改其实际查询的SELECT语句中的内容，而不能修改其FROM、WHERE、GROUP BY和HAVING子句。

(2) 视图一旦被修改，则其与其它视图和关联表之间的关系可能会受到影响，因此需要谨慎操作。

(3) 如果在修改视图时需要引用另一个视图或存储过程，建议在执行前将该视图或存储过程替换成其实际的查询语句或代码，以避免可能出现的错误和不可预测的行为。

2. alter语句可修改视图

在MySQL中，alter语句可以修改表的定义，可以创建索引。不仅如此，alter语句还可以用来修改视图。
alter语句修改视图的语法格式如下：

alter [algorithm = {undefined|merge|temptable}]
view view_name [(column_list)]
as select语句
[with [cascaded|local]check option];

【例6.9】修改视图teach_view2，统计计算机学院和材料学院的教师中的教授和副教授的教师号、教师名、和专业，并在视图名后面指明视图列名称。

mysql>  alter view  teach_view2(教师号,教师名,专业)
    -> as select  teacherno, tname, major
    -> from  teach_view1
    -> where  prof  like  '%教授'  
    -> and  (department=‘计算机学院’ or department=‘材料学院’ );

可以通过用select语句查看视图teach_view2的数据如下。

mysql> select  * from  teach_view2;

6.2.5 删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。

MySQL数据库中，用户必须拥有drop权限才能使用drop view语句来删除视图。

对需要删除的视图，使用drop view语句进行删除。drop view命令可以删除多个视图，各视图名之间用逗号分隔。

基本格式如下：

drop view [if exists]视图名列表 [restrict|cascaded]

例如，删除视图V1_student命令如下：

drop view V1_student; 

6.3 视图的应用

视图的使用主要包括视图的检索，以及通过视图对基表进行插入、修改、删除操作。视图的检索几乎没有什么限制，但是对通过视图实现表的插入、修改、删除操作则有一定的限制条件。

6.3.1 使用视图管理表数据

1. 使用视图进行查询

使用视图进行查询实际上就是把视图作为数据源，实现查询功能。

【例6.10】通过视图stu_view2，查询选修课程号为c08123、且成绩在80分以上的18级男生学生的学号、课程号和成绩。

mysql> select 学号, 姓名,课程号,成绩
    -> from stu_view2
    -> where 课程号='c08123' and 成绩 > 80;

2. 使用视图进行统计计算

【例6.11】创建视图course_avg，统计各门课程平均成绩，并按课程名称降序排列。

mysql> create  view  course_avg
-> as select  cname  课程名,  avg(final)  平均成绩
-> from  score join course  on  score.courseno= course.courseno
-> group  by   cname  desc;
mysql> select * from course_avg;

3. 使用视图修改基本表数据

使用视图修改表数据，是指在视图中进行insert、update和delete等操作而修改基表的数据。通过视图修改表数据时，要有执行相关操作的权限。

【例6.12】通过视图teach_view1，对基表teacher进行插入、更新和删除数据的操作。

mysql> insert into teach_view1(teacherno,tname,major,prof,department)
    -> values (‘t06027’ , ‘陶期年’ , ‘纳米技术’ , ‘教授’ , ‘材料学院’ );

mysql> update teach_view1  set  prof = ‘副教授’  where  teacherno = ‘t07019’;

mysql> delete  from  teach_view1  where  teacherno = ‘t08017’;

使用select语句查询teacher表，可以看到基表中的数据也相应地进行了修改。

mysql> select  *  from  teacher ;

【例6.13】视图stu_score1依赖于源表student、course和score等3张表，包括studentno、sname、 phone、cname和final等5个字段，通过stu_score1修改基本表student中的学号为18125121107的电话号码。

mysql> update stu_score1 set phone='132123456777'
    -> where  studentno  =‘18125121107’;

通过查看student 表，可以看到相应成绩已做了更改。

mysql> select  studentno,sname, phone  from  student
    -> where  studentno  ='18125121107' ;

说明：

(1) 视图若只依赖于一个基表，则可以直接通过更新视图来更新基本表的数据。

(2)若一个视图依赖于多张基表，则一次只能修改一个基表的数据，不能同时修改多个基本表的数据。

(3) 如果视图包含下述结构中的任何一种，都是不可修改的： 
    - 视图的列含有聚合函数（avg、count、sum、min、max）。
    - 视图的列是通过表达式并使用列计算出其他列。
    - 含有distinct关键字。
    - 含有group by子句、order by子句、having子句。
    - 含有union运算符。
    - 视图的列位于选择列表中的子查询。
    - from子句中包含多个表。
    - select语句中引用了不可更新视图。
    - where子句中的子查询，引用from子句中的表。

6.3.2 检查视图的应用

在MySQL数据库中，视图可分为普通视图与检查视图。

前面介绍的视图都没有使用with check option子句，当没有with_check_option时，表示with_check_option的值为0。即为普通视图，普通视图不具备检查功能。

如果使用了with check option子句，在通过检查视图更新基表数据时，只有满足检查条件的更新语句才能成功执行。

【例6.14】编程在teaching数据库中创建一个名称为V_ dept的视图，包含所有部门为“计算机学院”的老师的数据信息，需限制插入数据中部门必须为“计算机学院”。

分析：该程序通过单表生成的视图V_dept向基表teacher中插入一条记录，并通过查询语句显示基表中的所有数据。

--在“查询编辑器”中输入以下程序，创建V_dept视图。
mysql> create view V_dept
    -> AS
    -> select teacherno,tname,major,prof, department
    -> from teacher
    -> where department ='计算机学院'
    -> with check option;

--通过视图V_ dept 向基表teacher中插入数据。
mysql> insert into V_dept
    -> values(‘t08017’,‘时观’,‘金融管理’,‘副教授’,‘计算机学院’);

mysql> select * from teacher where tname='时观';

本例由于创建了with check option 检查条件约束，当插入记录时所有“部门”信息不符合条件的记录无法插入和修改，并显示错误提示信息。

--通过视图V_sex向基表teacher中插入数据行('t08037','时刻','软件技术','讲师','软件学院')。
mysql> insert into V_dept
    -> values('t08037','时刻','软件技术','讲师','软件学院');

ERROR 1369 (HY000): check option failed 'teaching.v_dept'

执行结果表明，通过检查更新表数据时，检查视图对更新数据进行了先行检查，若更新语句的数据不满足检查条件，则检查视图就会抛出异常，更新失败。