mysql进阶-存储引擎篇
本篇是将基础篇的知识进行深化了解底层机制的同时讲解企业中涉及到的高层级知识。
存储引擎
1、MySQL体系结构
- 连接层
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 - 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。 - 引擎层
存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。 - 存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
2、存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基干表的，而不是基于库的，所以存储引擎也可被称为表类型。
在创建表时，指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释]
字段n 字段n类型[COMMENT 字段n注释]) ENGINE=INNODB [COMMENT 表注释];
2.查看当前数据库支持的存储引擎
SHOW ENGINES;
3、存储引擎特点
介绍:
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在mysql5.5版本之后，InnoDB是默认的mysql存储引擎。
特点:
DML操作遵循ACID模型，支持事务;
行级锁提高并发访问性能;
支持外键Foreign key约束，保证数据的完整性和正确性;
文件:
InnoDB引擎的每张表都会对应这样一个表空间文件-表名.ibd，存储该表的表结构(frm、sdi)、数据和索引。
参数:Innodb file per table
cmd命令下打开.ibd文件
ibd2sdi xxx表名.ibd
若报不是内部或外部命令 先检查电脑环境变量是否已经配置
-
MyISAM介绍
MyISAM是MySQL早期的默认存储引擎。
特点:
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
文件:
xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引 -
Memory介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点:
内存存放，访问速度快
hash索引(默认)
文件:
xxx.sdi:存储表结构信息(因为存放在内存中所以只有表结构信息文件)
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 5.6版本之后支持 | 支持 | - |
| 空间索引 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
-
InnoDB(绝大部分):是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
-
MVISAM(一般选择MogoDB):如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那会么选择这个存储引擎是非常合适的。
-
MEMORY(一般选择redis):将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
视图/存储过程/触发器
视图
介绍:
视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图的作用:
1、简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2、安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
简单来说就是可以将用户对数据库的权限缩小到特定的行和列进行管理，保证敏感数据的安全性。
3、数据独立
视图可以帮助用户屏蔽真实表结构带来的影响。如：在创建视图时对字段取别名，这样就屏蔽了基表的变化对业务的影响。
视图的更新条件:
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新:
1.聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
2.DISTINCT
3.GROUP BY
4.HAVING
5.UNION 或者 UNION ALL
语法:
创建
CREATE [OR REPLACE] VIEW 视图名称(列名列表) AS SELECT语句 [WITH[CASCADED|LOCAL] CHECK OPTION];
查询
查看创建视图的SQL语句:SHOW CREATE VIEW;
视图名称查看视图数据:SELECT * FROM 视图名称...
修改
方式一:CREATE [OR REPLACE]VIEW 视图名称((列名列表)】AS SELECT语句[WITH[CASCADED|LOCAL]CHECK OPTION]
方式二:ALTER VIEW 视图名称[(列名列表)】AS SELECT语句[WITH[CASCADED|LOCAL]CHECK OPTION]
删除
DROP VIEW [IF EXISTS]视图名称 [,视图名称]
视图检查选线:
当使用WITH CHECK OPTION子句创建视图时，MYSQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。
MYSQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。
为了确定检查的范围，mysql提供了两个选项:CASCADED和 LOCAL，默认值为 CASCADED。
CASCADED(级联传递):
使用CASCADED创建视图时不光会检查当前视图还会检查当前视图所依赖的视图的条件。
根据student表创建v1视图
create or replace view v1 as select id,name from student where id <= 20;
基于v1创建v2视图，并增加视图检查规则
create or replace view v2 as select id,name from v1 where id >= 10 with cascaded check option;
对v2视图插入数据
insert into v2 values(25,'Tom');//失败-原因传递了v1的校验规则，超出了id值的校验范围
在基于v2创建视图v3但不加检查
create or replace view v3 as select id,name from v2 where id <= 15 ;
对v3插入的数据满足v2的要求，但不满足v3的要求
insert into v3 values(19,'Tom');//成功-因为v3没有检查选项，所以v3的检查选项不影响插入
insert into v3 values(22,'Tom');//失败-不满足v1的要求
LOCAL:(不传递)
根据student表创建v1视图
create or replace view v1 as select id,name from student where id <= 20;
基于v1创建v2视图，并增加视图检查规则
create or replace view v2 as select id,name from v1 where id >= 10 with LOCAL check option;
对v2视图插入数据
insert into 2 values(25,'Tom');//成功-原因不传递v1的校验
将v1的视图修改为增加校验
create or replace view v1 as select id,name from student where id <= 20 with LOCAL check option;
insert into 2 values(25,'Tom');//失败-此时要同时满足v1和v2的条件
存储过程
用的不多了 ，有空再加吧
存储函数
用的不多了 ，有空再加吧
触发器
用的不多了 ，有空再加吧
InnonDB引擎
逻辑存储结构
- 表空间(ibd文件):
一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。 - 段
段分为数据段(Leafnode segment)、索引段(Non-leafnodesegment)、回滚段(Rollbacksegment).
InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。 - 区
表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。 - 页
页是InnoDB存储引擎磁盘管理的最小单元，每个页大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。 - 行
InnoDB存储引擎数据是按行进行存放的，行中保存着很多字段。
Trx_id:每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
Roll_pointer:每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
架构
MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是lnnoDB架构图，左侧为内存结构，右
侧为磁盘结构。
架构图:
- 内存架构
Buffer Pool:
缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据。
在执行增删改查操作时，先操作缓冲池中的数据(若缓冲池没有数据，则从磁盘加载并缓存)，然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型:
free page:空闲page，未被使用。
clean page:被使用page，数据没有被修改过。
dirty page:脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
Change Buffer:
更改缓冲区针对于非唯一二级索引页(主键或非唯一索引不操作更改缓冲区)，在执行DML(增删改)语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，
而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到BufferPool中，再将合并后的数据刷新到磁盘中。
Change Buffer的意义
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。
同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。
有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。
Adaptive Hash Index:
自适应hash索引，用于优化对Buffer Pool数据的查询。
InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。
自适应哈希索引，无需人工干预，是系统根据情况自动完成。
参数:adaptive hash index
Log Buffer:日志缓冲区，用来保存要写入到磁盘中的log日志数据(redolog、undolog)，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。
如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。
参数:
innodb_log_buffer_size:缓冲区大小
innodb_flush_log_at_trx_commit:日志刷新到磁盘时机
语法
show variables like '%log_buffer_size%';//查看缓冲区大小
show variables like '%innodb_flush%';//查看日志刷新到磁盘时机
日志刷新到磁盘时机对应值解释
1:日志在每次事务提交时写入并刷新到磁盘
0:每秒将日志写入并刷新到磁盘一次。
2:日志在每次事务提交后和每秒都会刷新到磁盘一次。
- 磁盘架构
示意图:
System Tablespace:
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。
(在MYSQL5.x版本中还包含InnoDB数据字典、undolog等)
系统表空间查询文件参数:innodb_data_file_path
show show variables like '%data file path%';
File-Per-Table Tablespaces:（每个表的独立的表空间存放在这里）
每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中
查询是否开启参数：innodb_file_per_table(on开启off关闭)
show show variables like '%file_per_table%';//开启代表每一张表都会生成独立的表空间文件
General Tablespaces:
通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时可以指定该表空间。
语法:
//创建表空间并指定文件
CREATE TABLESPACE 表空间名称 ADD DATAFILE 'file_name.ibd' ENGINE=innodb;//DATAFILE 后跟.ibd文件名
//创建表并指定
CREATE TABLE xxx ... TABLESPACE 表空间名称;
Undo Tablespaces:
撤销表空间，mysql实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undolog日志。
TemporaryTablespaces:
InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
Doublewrite Buffer Files:
双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。
双写缓冲区文件:
Redo Log:
重做日志，用来实现事务的持久性。
日志文件由两部分组成:重做日志缓冲(redo logbuffer)以及重做日志文件(redo log),前者是在内存中，后者在磁盘中。
当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时进行数据恢复。
Redo Log不会一直保存，它会每隔一段时间进行清理，以循环的方式重做日志文件。
涉及的两个文件:
- 后台线程
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收。
- 后台线程架构
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收。
IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能，而I0Thread主要负责这些IO请求的回调。
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read Thread | 4 | 负责读操作 |
| Write Thread | 4 | 负责写操作 |
| Log Thread | 1 | 负责日志缓冲区刷新到磁盘 |
| Insert Thread | 1 | 负责将写缓冲区的内容刷新到磁盘 |
 |
Purge Thread
主要用于回收事务已经提交了的undolog，在事务提交之后，undo log可能不用了，就用它来回收。
Page Cleaner Thread
协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。
inoonDB体系架构总结:
当业务在操作时会先操作内存缓冲区，若缓冲区没有数据会先去磁盘中加载数据然后存储在缓冲区中。
缓冲区的数据会以一定频率或时机调取后台线程刷新到磁盘当中，最终在磁盘中进行持久话保留
- 事务原理
原理:
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败败。
特性:
原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
**原子性、一致性、持久性是通过redo.log和undo.log两份日志来实现的。
redo log 事务性
redo log是重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redolog buffer)以及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
隔离性是通过锁机制和MVCC多版本并发控制来实现的。**
undo log 原子性
回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚 和 MVCC(多版本并发控制)。
undo log和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁:undo log在事务执行时产生，事务提交时，并不会立即删除undolog，因为这些日志可能还用于MVCC。
Undo log存储:undolog采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo logsegment。
MVCC多版本并发控制
当前读:
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
select.... lock in share mode(共享锁)，select ... for update、update、insert、delete(排他锁)都是一种当前读。
快照读:
简单的select(不加锁)就是快照读，快照读是读取的记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read committed:每次select，都生成一个快照读。
Repeatable Read:开启事务后第一个select语句才是快照读的地方
Serializable:快照读会退化为当前读。
MVCC基本概念
全称 Multi-Version ConcurrencyControl，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MVSOL实现
MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。
MVCC实现原理
记录中的隐藏字段:
|隐式字段|含义|
|DB_TRX_ID|最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。|
|DB_ROLL_PTR|回滚指针，指向这条记录的上一个版本，用于配合undolog，指向上一个版本。|
|DB_ROW_ID|隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。|
undo log 回滚日志
回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。
undo log 版本链
不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
readview组件
ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。
ReadView中包含了四个核心字段:
| 字段 | 含义 |
|---|---|
| m_ids | 当前活跃的事务ID集合 |
| min_trx_id | 最小活跃事务ID |
| max_trx_id | 预分配事务ID，当前最大事务ID+1(因为事务ID是自增的) |
| creator_trx_idReadView | 创建者的事务ID |
trx_id:代表当前事务的id
trx_id == creator trx id?可以访问该版本=> 原因:说明数据是当前这个事务更改的。
trx_id<min trx id?可以访问该版本=>原因:说明数据已经提交了。
trx_d>max trx id?不可以访问该版本=>原因:说明该事务是在ReadView生成后才开启。
min_trx_id <=trx_id <= max_trx_id?如果trx_id不在m_ids中是可以访问该版本的=>原因:说明数据已经提交。
注意，不同的隔离级别，生成Readview的时机不同
READ COMMITTED:在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ:仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。
