目录

1、视图

1.1、什么是视图

1.2、创建视图

1.3、使用视图

1.4、修改视图

1.5、删除视图

1.6、视图的优点

2、MySQL存储结构

2.1、MySQL中的页

3、索引

3.1、索引的数据结构

3.2、B树 和 B+树

3.3、B+树在MySQL索引中的应用

3.4、索引分类

---

1、视图

1.1、什么是视图

视图是一个虚拟的表，它是基于一个或多个基础表或基他视图的查询的结查集

视图本身不存储数据，而是通过执行查询来动态生成数据

案例：查询用户的所有信息和考试成绩

所有有这样开发需求的程序员，都需要写这么复杂的SQL

把以上SQL定义成一个视图，用户就可以像操作普通表一样使用视图进行查询、更新和管理。

1.2、创建视图

语法：create view view_name [(column_list)] as select_statement

1. 不指定列名创建

由于定义视图时没有指定列名，这时视图中的列是由结果集决定的

为重复的列起别名就可以解决列重复的问题

2. 指定列名创建视图

指定列名之后，视图会根据指定的列名创建，查询结果集中是否重名不重要

创建完成后，可以使用指定列名排序

select * from v_student_score_v1 order by id;

1.3、使用视图

-- 查看视图
show tables;
-- 查询视图

show create view v_student_score;
-- 使用视图

select * from v_student_score;

使用真实表，在查询列表中随时可以加上其他字段，而使用视图查询，只能查到创建时指定的字段，不能再添加查询字段，所以可以使用视图隐藏不能被展示的字段

视图和真实表进行表连接查询：

select * from v_student_total_points v, student s where v.id = s.id;

1.4、修改视图

通过基本表修改数据，会影响视图

// 修改唐三藏的JAVA成绩为99分

 update score set score = 99 where student_id = 1 and course_id = 1;

// 查询视图，发现唐三藏这条记录已被修改

select * from v_student_socre;

通过视图修改数据，也会影响基本表

// 修改唐三藏的计算机⽹络成绩为99分

update v_student_socre set score = 99 where score_id = 3;

// 发现更新失败，因为创建视图时使用了order by 语句

---

// 更新视图

update v_student_socre_v1 set score = 99 where score_id = 3;

// 查看基础表数据发现已被修改

select * from score where student_id = 1 and course_id = 5;

结论：不论更新了视图还是基础表，相互都会被影响，查询的数据都是最新结果

以下视图不可更新：
◦ 创建视图时使用聚合函数的视图
◦ 创建视图时使用 DISTINCT
◦ 创建视图时使用 GROUP BY 以及 HAVING 子句
◦ 创建视图时使用 UNION 或 UNION ALL
◦ 查询列表中使用子查询
◦ 在FROM子句中引用不可更新视图

1.5、删除视图

语法：drop view 视图名;

1.6、视图的优点

1. 简单性：视图可以将复杂的查询封装成一个简单的查询。例如，针对一个复杂的多表连接查询，可以创建一个视图，用户只需查询视图而无需了解底层的复杂逻辑。

2. 安全性：通过视图，可以隐藏表中的敏感数据。例如，一个系统的用户表中，可以创建一个不包含密码列视图，普通用户只能访问这个视图，而不能访问原始表。

3. 逻辑数据独立性：视图提供了一种逻辑数据独立性，即使底层表结构发生变化，只需修改视图定义，而无需修改依赖视图的应用程序。使用到应用程序与数据库的解耦

4. 重命名列：视图允许用户重命名列名，以增强数据可读性。

2、MySQL存储结构

2.1、MySQL中的页

1. 在.ibd文件中最重要的结构体就是Page(页)，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘I/O提高性能

.ibd 文件：innodb存储引擎生成的表空间文件，后缀是.ibd

---

局部性原理：
时间局部性（Temporal Locality）：如果⼀个信息项正在被访问，那么在近期它很可能还会被再次访问。
空间局部性（Spatial Locality）：将来要⽤到的信息⼤概率与正在使⽤的信息在空间地址上是临近的。

2. 每一个页中即使没有数据也会使用 16KB 的存储空间，同时与索引的B+树中的节点对应

查看页大小：show variables like 'innodb_page_size';

3. 在MySQL中有多种不同类型的页，最常用的就是用来存储数据和索引的"索引页"，也叫做"数据页"，但不论哪种类型的页都会包含页头(File Header)和页尾(File Trailer)，页的主体信息使用数据"行"进行填充，数据页的基本结构如下图所示： 

3、索引

MySQL的索引是⼀种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过 ⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。 类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据

使用索引的目的只有一个，就是提升数据检索的效率，在应用程序的运行过程中，查询操作的频率远远高于增删改的频率。

3.1、索引的数据结构

hash

时间复杂度是0(1)，查询速度非常快，但是MySQL并没有选择HASH做为索引的默认数据结构主要原因是 HASH 不支持范围查找

二叉搜索树

中序遍历是一个有序序列，所以支持范围查找，但有几个问题导致它不适合用作索引的数据结构

1. 最坏情况下时间复杂度为O(N)
2. 节点个数过多无法保证树高

• AVL和红黑树，虽然是平衡或者近似平衡，但是毕竟是二叉结构，当节点个数过多时，无法保证树的高度
• 在检索数据时，每次访问某个节点的子节点时都会发生一次磁盘IO，而在整个数据库系统中，IO是制约数据库性能的主要因素，减少IO次数可以有效的提升性能

N叉树

每个节点可以有超过两个的子节点，可以解决树高的问题

时间复杂度：O（logN)
在数据量相同的情况下，可以有效的控制树高，也就是说可以使用更少的IO次数找到目标节点，从而提高数据库的效率。但是MySQL认为N叉树做为索引的数据结构还不够好

3.2、B树 和 B+树

B树 和 B+树都是 N 叉搜索树

B树：上述N叉树的案例就是B树

B+树：

B+树是⼀种经常用于数据库和文件系统等场合的平衡查找树，是MySQL索引采用的数据结构

时间复杂度：O（logN)
可以有效的控制树高

B+树的特点：

1. 一个节点，可以存储N个key，N个key划分出了N个区间（而不是N+1个区间)
2. 每个节点中的key的值，都会在子节点中也存在（同时该key是子节点的最大值）
3. B+树的叶子节点，是首尾相连，类似于一个链表

4. 非叶子节点只保存索引，不存数据，真实数据都保存在叶子节点中

面试题：

1. 索引使用了什么数据结构?

答：B+树

2. 介绍一下B+树

答：B+树与B树对比，B+树的优势是：
1. 叶子节点之间有一个相互连接的引用，可以通过一个叶子节点找到它相邻的兄弟节点
MySQL索引在组织叶子节点时使用的是双向链表
2. 非叶子节点的值都包含在叶子节点中
MySQL索引 非叶子节点只保存了对子节点的引用，没有保存真实的数据，所有真实的数据都保存在叶子节点中
3. 对于B+树而言，在相同树高的情况下，查找任一元素的时间复杂度都一样，性能均衡

3.3、B+树在MySQL索引中的应用

以查找id为5的记录，完整的检索过程如下：
1. 首先判断B+树的根节点中的索引记录，此时5<7，应访问左孩子节点，找到索引页2
2. 在索引页2中判断id的大小，找到与5相等的记录，命中，加载对应的数据页

以上的IO过程，加载索引页1-->加载索引页2-->加载数据页3（3次IO）

---

所有关于页的操作和访问都是在内存中进行的

理论上三层树高的B+树可以存放多少条记录：

假设一条用户数据大小为1KB，在忽略数据页中数据页自身属性空间占用的情况下，一页可以存16条数据
索引页一条数据的大小为，主键用BIGINT类型占8Byte，下一页地址6Byte，一共是14Byte,一个索引页可以保存16*1024/14=1170条索引记录
如果只有三层树高的情况，综合只保存索引的根节点和二级节点的索引页以及保存真实数据的数据页，那么一共可以保存1170*1170*16=21,902,400条记录，也就是说在两千多万条数据的表中，可以通过三次IO就完成数据的检索

3.4、索引分类

主键索引

1. 当在一个表上定义一个主键PRIMARY KEY时，会自动创建索引，索引的值是主键列的值。InnoDB使用它作为聚集索引 / 聚簇索引 / 主键索引。

2. 推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列。

3. 如果没有为表定义PRIMARY KEY，InnoDB使用第一个UNIQUE和NOT NULL的列作为聚集索引。（聚集索引可以标识数据行的唯一性）

普通索引

1. 最基本的索引类型，没有唯一性的限制。工作中通常为查询频繁的列创建索引为了提升查询效率

2. 可以包含一个列也可以包含多个列，包含多个列时称为复合索引或组合索引

唯一索引

1. 当在一个表上定义一个唯一键 UNQUE 时，自动创建唯一索引。

2. 与普通索引类似，但区别在于唯一索引的列不允许有重复值。

如果表中没有 PRIMARY KEY 或合适的 UNIQUE索引，InnoDB 会为新插入的行生成一个行号并用 6 字节的 ROW_ID 字段（数据行中的一个隐藏列之一）记录，ROW_ID 单调递增，并使用ROW_ID做为索引。这种索引也是聚集索引

非聚集索引

1. 聚集索引以外的索引称为非聚集索引或二级索引

2. 二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。

3. InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

注意：

1. 创建索引之后都会生成一棵索引树，创建多少索引生多少棵索引树

2. 创建索引后，生成的索引树，也是会占用磁盘空间的创建索引时，要慎重考虑一下需不需要
3. 索引树越多，对增、删，改的效率影响越大

非聚集索引的查询过程：
1. 通过索引查到叶子节点中的索引记录
2. 通过索引记录中的主键值，去主键索引树中找相应的完整记录，这个过程称为回表查询

select * from student where name = '张三';（回表查询）

3. 通过索引查询的列，包含在索引中，不需要回表查询了，这种的现象叫做索引覆盖

假设 name 和 sn 这两列为组合索引：

select sn from student where name = '张三';（索引覆盖）

^

问题：当前的组合索引中通过学号来查姓名索引生不生效(走不走索引)？

select name from student where sn = '100002';

答：不生效。创建索引时，name列在sn列之前，那么使用的时候也要先使用name再使用sn如果只使用sn列，那么索引就会失效，如果非要使用sn列查，可以为sn单独创建一个索引。