MySQL数据库-索引和视图

一、视图

1.什么是视图

• MySQL中的视图（view）是一种虚拟表，其内容由查询定义，视图本身并不包含数据。
• 视图看起来和真实的表完全相同，但其中的数据来自定义视图时用到的基本表，并且在打开视图时动态生成，类似对常用的复杂多表连接查询的结果截图拍照，之后需要用到时只需看一下视图即可无需重新查询，以节省资源
• 视图是一种数据库对象，其内没有存储任何数据，它只是对表的一个查询

2.为什么需要视图

例如经常要对student和score表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于成绩 数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决。

3.视图的作用和优点

（1）作用：

• 控制安全
• 保存查询数据

（2）优点：

• 简化操作：通过视图可以使用户将注意力集中在他所关心的数据上，使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。
• 提高数据的安全性：在设计数据库时可以针对不同的用户定义不同的视图，使用视图的用户只能访问他们被允许查询的结果集。
• 数据独立：视图的结构定义好之后，如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响。

4.创建视图

（1）语法

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

# 说明：
    1、or replace：如果要创建的视图名称已存在，则替换已有视图。
    2、algorithm：可选参数，表示视图选择的算法，默认算法是 undefined
        (1)undefined：未定义指定算法
        (2)merge:更新视图表数据的同时会更新真实表的数据
        (3)temptable:只能查询不能更新
    3、view_name：新建的视图名称。
    4、column_list：可选，表示视图的字段列表。若省略，则使用 select 语句中的字段列表。
    5、as select_statement：创建视图的 select 语句。
    6、with check option：表示更新视图时要保证该视图的 where 子句为真。
        比如定义视图：create view v1 as select * from salary > 5000;
        如果要更新视图，则必须保证 salary 字段的值在 5000 以上，否则报错。
        (1)cascaded:必须满足所有针对该视图的条件才可以更新
        (2)local:只需满足本视图的条件就可以更新

（2）示例

• 准备数据：

mysql> select * from student;
+------+--------+------+------+---------+------------+
| sno  | sname  | ssex | sage | monitor | birth      |
+------+--------+------+------+---------+------------+
| s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 |
| s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 |
| s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 |
| s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 |
| s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 |
| s006 | 孙德胜 | 男   |   22 | s005    | 2009-05-25 |
| s007 | 刘琦玉 | 男   |   20 | s005    | 2009-06-21 |
| s008 | 李波   | 男   |   20 | s005    | 2009-10-21 |
| s009 | 李晨   | 男   |   19 | s010    | 1998-03-30 |
| s010 | 王子涵 | 女   |   23 | s010    | 2007-05-01 |
| s011 | 孙德胜 | 女   |   24 | s010    | 2008-05-25 |
+------+--------+------+------+---------+------------+
11 rows in set (0.00 sec)

mysql> select * from sc;
+------+------+-------+
| sno  | cno  | score |
+------+------+-------+
| s001 | c001 | 85.65 |
| s001 | c002 | 77.45 |
| s001 | c003 | 60.00 |
| s002 | c002 | 72.35 |
| s003 | c001 | 74.12 |
| s003 | c002 | 85.05 |
| s004 | c001 | 45.50 |
| s005 | c001 | 99.00 |
+------+------+-------+
8 rows in set (0.00 sec)

• 创建单表的视图

mysql> create view v_student as select sno,sname,ssex,year(now())-year(birth) as age from student;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_student;
+------+--------+------+------+
| sno  | sname  | ssex | age  |
+------+--------+------+------+
| s001 | 张玲丽 | 女   |   15 |
| s002 | 吴鹏   | 男   |   15 |
| s003 | 李锐   | 男   |   16 |
| s004 | 赵丁雯 | 女   |   16 |
| s005 | 陈晓晓 | 女   |   15 |
| s006 | 孙德胜 | 男   |   15 |
| s007 | 刘琦玉 | 男   |   15 |
| s008 | 李波   | 男   |   15 |
| s009 | 李晨   | 男   |   26 |
| s010 | 王子涵 | 女   |   17 |
| s011 | 孙德胜 | 女   |   16 |
+------+--------+------+------+
11 rows in set (0.00 sec)

• 创建多表连接的视图

mysql> create view v_score as select student.*,score from student
 join sc on student.sno=sc.sno;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_score;
+------+--------+------+------+---------+------------+-------+
| sno  | sname  | ssex | sage | monitor | birth      | score |
+------+--------+------+------+---------+------------+-------+
| s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 60.00 |
| s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 77.45 |
| s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 | 85.65 |
| s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 | 72.35 |
| s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 | 85.05 |
| s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 | 74.12 |
| s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 | 45.50 |
| s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 | 99.00 |
+------+--------+------+------+---------+------------+-------+
8 rows in set (0.00 sec)

• 创建视图，字段起别名

mysql> create or replace view v_avg(sex,avg_score) as select ssex
,round(avg(score),2) from student inner join sc on student.sno=sc
.sno group by ssex;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_avg;
+------+-----------+
| sex  | avg_score |
+------+-----------+
| 女   |     73.52 |
| 男   |     77.17 |
+------+-----------+
2 rows in set (0.00 sec)

5.更新视图

更新视图中的数据，实际上是更新创建视图时用到的基本表中的数据

（1）以下视图不可更新：

• 包含以下关键字的 SQL 语句：聚合函数、distinct、group by 、having、union 或 uinon all
• select 中包含子查询
• from 一个不可更新的试图
• where 子句的子查询引用了 from 子句中的表

（2）示例

• 创建视图，限制更新

mysql> create or replace view v_age as select sno,sname,ssex,sage
 from student where sage>20 with check option;    # 增加限制更新参数
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_age;
+------+--------+------+------+
| sno  | sname  | ssex | sage |
+------+--------+------+------+
| s004 | 赵丁雯 | 女   |   21 |
| s006 | 孙德胜 | 男   |   22 |
| s010 | 王子涵 | 女   |   23 |
| s011 | 孙德胜 | 女   |   24 |
+------+--------+------+------+
4 rows in set (0.00 sec)

mysql> update v_age set sage=24 where sno='s011';    # 符合条件更新视图
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from student;    # 查看视图的基本表，数据已变更
+------+--------+------+------+---------+------------+
| sno  | sname  | ssex | sage | monitor | birth      |
+------+--------+------+------+---------+------------+
| s001 | 张玲丽 | 女   |   20 | s010    | 2009-12-03 |
| s002 | 吴鹏   | 男   |   19 | s010    | 2009-10-11 |
| s003 | 李锐   | 男   |   19 | s003    | 2008-02-13 |
| s004 | 赵丁雯 | 女   |   21 | s003    | 2008-06-24 |
| s005 | 陈晓晓 | 女   |   18 | s005    | 2009-07-26 |
| s006 | 孙德胜 | 男   |   22 | s005    | 2009-05-25 |
| s007 | 刘琦玉 | 男   |   20 | s005    | 2009-06-21 |
| s008 | 李波   | 男   |   20 | s005    | 2009-10-21 |
| s009 | 李晨   | 男   |   19 | s010    | 1998-03-30 |
| s010 | 王子涵 | 女   |   23 | s010    | 2007-05-01 |
| s011 | 孙德胜 | 女   |   24 | s010    | 2008-05-25 |
+------+--------+------+------+---------+------------+
11 rows in set (0.00 sec)

mysql> update v_age set sage=18 where sno='s011';    # 不符合条件更新视图，报错！
ERROR 1369 (HY000): CHECK OPTION failed 'mydb9_stusys.v_age'

• 视图中聚合函数不可更新

mysql> select * from v_student;
+------+--------+------+------+
| sno  | sname  | ssex | age  |
+------+--------+------+------+
| s001 | 张玲丽 | 女   |   15 |
| s002 | 吴鹏   | 男   |   15 |
| s003 | 李锐   | 男   |   16 |
| s004 | 赵丁雯 | 女   |   16 |
| s005 | 陈晓晓 | 女   |   15 |
| s006 | 孙德胜 | 男   |   15 |
| s007 | 刘琦玉 | 男   |   15 |
| s008 | 李波   | 男   |   15 |
| s009 | 李晨   | 男   |   26 |
| s010 | 王子涵 | 女   |   17 |
| s011 | 孙德胜 | 女   |   16 |
+------+--------+------+------+
11 rows in set (0.00 sec)

mysql> update v_student set age=30 where sno='s001';    # 报错！
ERROR 1348 (HY000): Column 'age' is not updatable

• 对分组和having字段不可更新

mysql> select * from v_avg;
+------+-----------+
| sex  | avg_score |
+------+-----------+
| 女   |     73.52 |
| 男   |     77.17 |
+------+-----------+
2 rows in set (0.00 sec)

mysql> update v_avg set avg_score=80 where sex='女';    # 报错!
ERROR 1288 (HY000): The target table v_avg of the UPDATE is not updatable

6.视图使用规则

• 视图必须有唯一命名
• 在mysql中视图的数量没有限制
• 创建视图必须从管理员那里获得必要的权限
• 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
• 在视图中可以使用order by，但是如果视图内已经使用该排序子句，则视图的order by将覆盖前面的order by
• 视图不能索引，也不能关联触发器或默认值
• 视图可以和表同时使用

7.修改视图

（1）通过create or replace view 命令修改视图

mysql> desc v_student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno   | char(4) | YES  |     | NULL    |       |
| sname | char(4) | YES  |     | NULL    |       |
| ssex  | char(2) | YES  |     | NULL    |       |
| age   | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> create or replace view v_student as select sno,sname,ssex,
sage from student;    # 将age直接读取
Query OK, 0 rows affected (0.01 sec)

mysql> desc v_student;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno   | char(4) | YES  |     | NULL    |       |
| sname | char(4) | YES  |     | NULL    |       |
| ssex  | char(2) | YES  |     | NULL    |       |
| sage  | int     | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

（2）通过alter view 命令修改视图

mysql> alter view v_student as select sno,sname,ssex,sage from st
udent where ssex='女';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_student;
+------+--------+------+------+
| sno  | sname  | ssex | sage |
+------+--------+------+------+
| s001 | 张玲丽 | 女   |   20 |
| s004 | 赵丁雯 | 女   |   21 |
| s005 | 陈晓晓 | 女   |   18 |
| s010 | 王子涵 | 女   |   23 |
| s011 | 孙德胜 | 女   |   24 |
+------+--------+------+------+
5 rows in set (0.00 sec)

8.删除视图

drop view [if exists] view_name;

二、索引

1.什么是索引

索引是一种特殊的文件，用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式，通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

2.索引特点

• 索引是存放在模式（schema）中的一个数据库对象
• 索引在数据库中用来加速对表的查询
• 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
• 索引与表独立存放，但不能独立存在，必须属于某个表
• 索引由数据库自动维护，表被删除时，该表上的索引自动被删除

3.索引分类

（1）按照算法分类：

• Hash索引

• B+Tree索引

（2）按照功能分类：

常用索引：

• 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引
• 普通索引INDEX：加速查找，最常用的索引，允许重复
• 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
• 唯一索引UNIQUE：加速查找+约束（不能重复）
• 全文索引（FULLTEXT）：仅可用于 MyISAM 表，建立于char字段

4.索引优缺点

优点：

• 索引可以提高检索数据的速度，这也是创建索引的最主要的原因
• 对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度
• 使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间

缺点：

• 创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加
• 每一个索引要占一定的磁盘存储空间
• 增加、删除和修改数据时，要动态的维护索引，会降低数据的维护速度

5.索引的设计原则

为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。

设计原则：

• 选择惟一性索引
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引
• 限制索引的数目
• 尽量使用数据量少的索引
• 尽量使用字段前缀来索引，即限制索引长度，索引长度：对表中特定字段的前N个字符创建索引。通常用于减少索引的大小，并且可以提高查询性能
• 删除不再使用或者很少使用的索引

6.创建索引

（1）创建表的时候创建索引

create table 表名 (
            字段名1 数据类型 [完整性约束条件…],
            字段名2 数据类型 [完整性约束条件…],
            [unique | fulltext | spatial] index | key
            [索引名] (字段名[(长度)] [asc | desc])
);

（2）在已经存在的表上创建索引

create [unique | fulltext | spatial] index 索引名
            ON 表名 ( 字段名[(长度)] [asc | desc] );

（3）使用alter table 语句来创建索引

alter table 表名 add [unique | fulltext | spatial] index
                    索引名 (字段名[(长度)] [asc | desc]);

（4）示例1：建表时创建索引

# 创建表的同时创建普通索引
mysql> create table index1_tb( id int, name varchar(20), sex boolean, index(id));

# 创建表的同时创建唯一索引
mysql> create table index2_tb( id int unique, name varchar(20), unique index
index2(id asc) );

# 创建单列索引 (即普通的单列索引)
mysql> create table index3_tb( id int, subject varchar(30), index
index3(subject(10)) );

# 创建多列索引 (即普通的多列索引)
# 注意：使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
mysql> create table index4_tb( id int, name varchar(20), sex char(4), index
index4(name,sex) );

（5）示例2：键表后添加索引

# 在创建完表后为其添加索引
mysql> create unique index un_index on index1_tb(name);

mysql> alter table index3_tb add primary key(id);

7.查看索引

（1）格式

# 查询索引
show create table 表名 \G

# 查询某张表中索引情况
show index from table_name;

# 使用计划查询SQL使用索引情况
explain select * from 表名 where id=1 \G

# 使用系统表查看所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;

# 使用系统表查看单张表的所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and
a.table_name like '%表名%’;

（2）示例

mysql> show create table index1_tb \G

mysql> show index from index3_tb;

mysql> explain select * from index1_tb where id=1 \G
# 注意possible_keys和key 这两个属性，possible_keys：MySQL在搜索数据记录时可以选用的各个索引，
key：实际选用的索引

8.删除索引

语法：

drop index 索引名 on 表名

注意：一些不再使用的索引会降低表的更新速度，影响数据库的性能，对于这样的索引，应该将其删除

9.索引原理

（1）概述

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上，这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

（2）索引hash算法

• 优点：通过字段的值计算的hash值，定位数据非常快
• 缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较

（3）索引二叉树算法

• 特性：分为左子树、右子树和根节点，左子树比根节点值要小，右子树比根节点值要大
• 缺点：有可能产生不平衡 类似于链表的结构 

（4）索引平衡二叉树算法

优点：

• 它的左子树和右子树都是平衡二叉树
• 左子树比中间小，右子树比中间值大
• 左子树和右子树的深度之差的绝对值不超过1

缺点：

• 插入操作需要旋转
• 支持范围查询，但回旋查询效率较低，比如要查找大于8的，会回旋到父节点7、10。
• 如果存放几百条数据的情况下，树高度越高，查询效率会越慢

（5）索引BTREE树算法

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，Btree结构可以有效的解决之前的相关算法遇到的问题。

注意：InnoDB引擎使用B+Tree，InnoDB的叶节点的data域存放的是数据，相比MyISAM效率要高一些， 但是比较占硬盘内存大小。