7. MySQL 视图、索引

【 1. 视图 View 】

1.1 视图原理

• 视图（View） 是一种虚拟存在的表的显示形式，在数据库中的作用类似于窗户，用户可以通过这个窗口看到只对自己有用的数据，那些 对用户没用或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，提高了查询效率，也保证了系统和数据的安全。
• 视图、数据、真实表的关系
  • 数据库中只存放了视图的定义，视图不包含数据 ，同真实表一样，视图也由列和行构成，视图上呈现的数据都存放在定义视图查询所引用的真实表中，每次使用视图时，都必须执行查询中所需的任何一个检索操作，数据库会从真实表中取出对应的数据 。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。
    视图中的数据是依赖于真实表中的数据的，一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。
  • 使用视图的用户不接触数据表，不知道表结构 。
  • 视图的建立和删除只影响视图本身，不影响对应的基本表 。
  • 视图与表在本质上虽然不相同，但 视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。

• 例如，有两张表：公司的部门表（包括部门号、部门名称、功能和办公地址）和员工表（包括员工的工作号、部门号、姓名、性别、出生日期和家庭住址）。由于各部门领导的权力范围不同，因此，各部门的领导只能看到各自部门的员工信息；而且，领导可能不关心员工的生日和家庭住址，为了达到这个目的，可以为各部门的领导建立一个视图，通过该视图，领导只能看到本部门员工的指定信息。
• 在此之上举例，我们可以为生产部门建立一个名为 product view 的视图，通过视图 product view，生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些 部门表的信息和员工表的信息依然存在于各自的表中，而视图 product_view 中不保存任何数据信息，当部门表和员工表的信息发生改变时，视图 product_view 显示的信息也会发生相应的变化。

• 使用视图的时候，还应该注意以下几点：
  • 创建 视图需要足够的访问权限。
  • 视图可以从多个表中获取指定字段的数据。
  • 创建视图的数目没有限制。
  • 视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
  • 视图不能索引，也不能有关联的触发器、默认值或规则。
  • 视图可以和表一起使用。
  • MySQL 的视图不支持输入参数的功能，因此交互性上还有欠缺。但对于变化不是很大的操作，使用视图可以很大程度上简化用户的操作。

1.2 创建视图 CREATE VIEW

• 创建视图是指 在已经存在的 MySQL 数据库表上建立视图，视图可以建立在一张表中，也可以建立在多张表中。
• 基本语法
  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  • <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。对于创建视图中的 SELECT 语句的指定存在以下限制：
    • 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
    • SELECT 语句不能引用系统或用户变量。
    • SELECT 语句不能包含 FROM 子句中的子查询。
    • SELECT 语句不能引用预处理语句参数。

CREATE VIEW <视图名> AS <SELECT语句>

• 视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
• 视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。 ORDER BY 子句可以用在视图中，但若该视图检索数据的 SELECT 语句中也含有 ORDER BY 子句，则该视图中的 ORDER BY 子句将被覆盖。
• 视图定义中 不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。
• WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

1.2.1 创建基于单表的视图

• 实例1
  1. 在 tb_student_info 表上创建一个名为 view_students_info 的视图
     CREATE VIEW view_student_info AS SELECT * FROM tb_student_info;
  2. 查看视图 view_student_info。
     SELECT * FROM view_student_info;
     
• 实例2
  1. 在 tb_student_info 表上创建一个名为 v_student_info 的视图。
     CREATE VIEW v_student_info
(s_id,s_name,s_age,s_sex,s_height)
AS SELECT id,name,age,sex,height
FROM tb_student_info;
  2. 查看视图 view_student_info 。
     可以看到，实例1的view_student_info 和实例2的 v_student_info 两个视图中的字段名称不同，但是数据却相同。因此，在使用视图时，可能用户不需要了解基本表的结构，更接触不到实际表中的数据，从而保证了数据库的安全。
     SELECT * FROM v_student_info;
     

1.2.2 创建基于多表的视图

• 实例
  在表 tb_student_info 和表 tb_department 上创建视图 v_students_info。
  1. 先创建 tb_department 数据表

1.3 查看视图

• 视图内容和属性的查看和数据表一样，这里给出数据表的查看方式链接：查看数据表 。
• 视图用于查询主要应用在以下几个方面：
  使用视图重新格式化检索出的数据。
  使用视图简化复杂的表连接。
  使用视图过滤数据。

1.3.1 查看视图的内容

• 实例1
  SELECT *FROM v_student_info;
  

1.3.2 查看视图的详细信息

• 实例1
  DESCRIBE v_student_info;
  
• 实例2
  SHOW CREATE VIEW v_student_info \G
  

1.4 修改视图 ALTER VIEW

• 修改视图是指修改 MySQL 数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。
• 基本语法
  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  • <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
  • 对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

ALTER VIEW <视图名> AS <SELECT语句>

1.4.1 修改视图内容

• 视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据，即：对视图的修改就是对基本表的修改 ，因此在修改时，要满足基本表的数据定义。
• 对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。
• 如果视图包含以下结构中的任何一种，它就是 不可更新 的：
  • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
  • DISTINCT 关键字。
  • GROUP BY 子句。
  • HAVING 子句。
  • UNION 或 UNION ALL 运算符。
  • 位于选择列表中的子查询。
  • FROM 子句中的不可更新视图或包含多个表。
  • WHERE 子句中的子查询，引用 FROM 子句中的表。
  • ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。
• 实例1：ALTER 语句重构视图。
  使用 ALTER 语句修改视图 view_student_info。
  ALTER VIEW view_student_info
AS SELECT id,name,age
FROM tb_student_info;
  
• 实例2： UPDATE 语句更新视图。
  使用 UPDATE 语句更新视图 view_student_info。
  UPDATE view_student_info SET age=20 WHERE id=1;
  

1.4.2 修改视图名称

• 修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

1.5 删除视图 DORP VIEW

• 删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。
  基本语法
  • <视图名>指定要删除的视图名。
  • DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

DROP VIEW <视图名1> [ , <视图名2> …]

• 实例
  1. 删除 v_student_info 视图。
     DROP VIEW IF EXISTS v_student_info;
  2. 再次查看 v_student_info 视图，提示不存在。
     SHOW CREATE VIEW v_student_info;
     

【 2. 索引 Index 】

2.1 索引原理

• 问题背景

  • 传统的 顺序访问 是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。
    例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
  • 索引访问 是通过遍历索引来直接访问表中记录行的方式：使用这种方式的前提是在数据表的列上创建索引（索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序），查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。
    例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统 先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的 速度快，一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

• 索引（Index） 是一种特殊的数据库结构，由数据表中的一列或多列组合而成（索引是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表 ）。可以 通过索引列快速查询数据表中有某一特定值的记录。如果在表中查询的列有一个索引，MySQL 能快速到达索引列的位置去搜寻数据，而不必查看所有数据；否则，数据库系统将读取每条记录的所有信息进行匹配。因此，使用索引可以很大程度上提高数据库的查询速度，有效的提高数据库系统的性能。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

• 索引的优缺点

2.2 创建索引

• 创建索引是指在某个表的一列或多列上建立一个索引，可以提高对表的访问速度。

2.2.1 CREATE INDEX 直接创建索引

• 使用专门用于创建索引的 CREATE INDEX 语句，在一个已有的表上创建索引，但该语句不能创建主键。
  基本语法
  • <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的 。
  • <表名>：指定要创建索引的表名。
  • <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
  • <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
  • ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

2.2.2 CREATE TABLE 在创建表时创建索引

• 可以在创建表（CREATE TABLE）的同时创建索引。

2.2.3 ALTER TABLE 在修改表时创建索引

• CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以 在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以在 ALTER TABLE 语句中添加以下语法成分的某一项或几项，向已有的表添加索引。

实例：创建表时创建普通索引

1. 创建一个表 tb_stu_info，在该表的 height 字段创建普通索引。
   CREATE TABLE tb_stu_info
(
id INT NOT NULL,
name CHAR(45) DEFAULT NULL,
dept_id INT DEFAULT NULL,
age INT DEFAULT NULL,
height INT DEFAULT NULL,
INDEX(height)
);
2. 显示这个表的详细属性。
   可以看到 height 字段 有 KEY 索引。
   SHOW CREATE TABLE tb_stu_info\G
   

实例：创建表时创建唯一索引

1. 创建一个表 tb_stu_info2，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。
   CREATE TABLE tb_stu_info2
(
id INT NOT NULL,
name CHAR(45) DEFAULT NULL,
dept_id INT DEFAULT NULL,
age INT DEFAULT NULL,
height INT DEFAULT NULL,
UNIQUE INDEX(height)
);
2. 显示这个表的详细属性。
   可以看到 id 字段 有 UNIQUE KEY 索引。
   SHOW CREATE TABLE tb_stu_info2\G
   

2.3 查看索引 SHOW INDEX

• 基本语法
  • <表名>：指定需要查看索引的数据表名。
  • <数据库名>：指定需要查看索引的数据表所在的数据库，可省略。
    比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

SHOW INDEX FROM <表名> [ FROM <数据库名>]

• 实例
  查看 tb_stu_info2 数据表的索引信息。
  SHOW INDEX FROM tb_stu_info2\G
  
• 其中各主要参数说明如下：

2.4 修改索引

• 在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

2.5 删除索引

• 不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。
• 如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；
  如果组成索引的所有列都被删除，那么整个索引将被删除。

2.5.1 DROP INDEX 删除索引

• 基本语法
  • <索引名>：要删除的索引名。
  • <表名>：指定该索引所在的表名。

DROP INDEX <索引名> ON <表名>

• 实例
  1. 删除表 tb_stu_info 中的索引。
     DROP INDEX height ON tb_stu_info;
  2. 查看表 tb_stu_info 的详细属性。
     SHOW CREATE TABLE tb_stu_info\G
     

2.5.2 ALTER TABLE 删除索引

• ALTER TABLE 语句也可以用于删除索引，具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。
  • DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
  • DROP INDEX index_name：表示删除名称为 index_name 的索引。
  • DROP FOREIGN KEY fk_symbol：表示删除外键。
• 实例
  1. 删除表 tb_stu_info2 中名称为 height 的索引。
     ALTER TABLE tb_stu_info2 DROP INDEX height;
  2. 查看表 tb_stu_info 的详细属性。
     SHOW CREATE TABLE tb_stu_info2\G