MySQL 进阶篇

体系结构

• 连接层

  • 最上层，负责客户端和连接服务，完成连接处理、授权认证以及相关的安全方案
  • 为安全接入的客户端验证其所具有的操作权限

• 服务层

  • 完成大多数核心服务功能，如 SQL 接口
  • 完成缓存的查询
  • SQL 的分析和优化
  • 部分内置函数的执行
  • 所有夸存储引擎的功能（过程、函数）

• 引擎层

  • 存储引擎真正负责了 MySQL 中数据的存储和提取
  • 服务器通过 API 和存储引擎进行通信
  • 不同的存储引擎具有不同的功能（且索引也不同）

• 存储层

  • 将数据存储在文件系统之上
  • 完成与存储引擎的交互

存储引擎

概述

存储数据、简历索引、更新/查询数据等技术的实现方式，

查询使用的存储引擎

show engines;

InnoDB

兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，为默认存储引擎

• 支持事务（DML 操作遵循 ACID 模型：原子性、一致性、持久性、隔离性）
• 行级锁，提高并发访问性能
• 支持外键约束

使用 .idb ​ 文件存储数据，每一张表都对应一个表空间文件，存储该表的表结构（frm，sdi）、数据、索引

逻辑存储结构

• Tablespace 表空间-> segment 段-> extent 区 1M -> page 页 16K -> row 行

• 表空间：InnoDB 存储引擎逻辑结构的最高层，ibd 文件就是表空间文件，在表空间中可以包含多个 Segment 段。
• 段：表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB 中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• 区：区是表空间的单元结构，每个区的大小为 1M。 默认情况下， InnoDB 存储引擎页大小为 16K， 即一个区中一共有 64 个连续的页。
• 页：页是组成区的最小单元，页也是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行：InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段

MyISAM

早期的默认引擎

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

使用三个文件存储

• ​.MYI​ 存储索引
• ​.MYD​ 存储数据
• ​.sdi ​ 存储表结构

Memory

数据存放在内存，用于临时表或缓存使用，不支持持久化

• 内存存放
• hash 索引（默认）

​.sdi ​ 存储表结构

存储引擎选择

• InnoDB：对事务的完整性、并发条件下要求数据的一致性要求高；数据包含很多更新、删除操作
• MyISAM：对事务的完整性和数据的一致性要求低；以读操作和插入操作为主，只有很少的更新或删除操作 -> 存储非核心事务 -> NoSQL（MongoDB）
• Memory：访问速度快，用于临时表和缓存，对表的大小有限制，且无法保障数据的安全性 -> Redis

基本上都是使用 InnoDB

索引

索引概述

索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）

数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构即索引

• 无索引情况：全表扫描
• 有索引情况：根据实现的索引结构高效查询

优势	劣势（基本可以忽略）
提高数据检索的效率，降低数据库的 IO 成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT、UPDATE、DELETE 时，效率降低

索引结构

MySQL 的索引再存储引擎层实现，不同的存储引擎有不同的索引结构

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree（空间索引）	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES

一般索引认为就是 B+ 树

B 树

B 树是一种多叉路衡查找树，相对于二叉树，B 树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为 5(5 阶)的 b-tree 为例，那这个 B 树每个节点最多存储 4 个 key，5 个指针

• 5 阶的 B 树，每一个节点最多存储 4 个 key，对应 5 个指针。
• 一旦节点存储的 key 数量到达 5，就会裂变，中间元素向上分裂。
• 在 B 树中，非叶子节点和叶子节点都会存放数据。

B+ 树

B+Tree 是 B-Tree 的变种，以一颗最大度数（max-degree）为 4（4 阶）的 b+tree 为例

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree 相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

MySQL 对于 B+ 树的优化

在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能，利于排序

Hash

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为 hash 碰撞），可以通过链表来解决。（拉链法）

• Hash 索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在 hash 冲突的情况)只需要一次检索就可以了，效率通常要高于 B+tree 索引

Memory 存储引擎支持 hash 索引。而 InnoDB 中具有自适应 hash 功能，hash 索引是 InnoDB 存储引擎根据 B+Tree 索引在指定条件下自动构建的。

为什么使用 B+ 树

1. 相对于二叉树，层级更少，搜索效率高

2. 对于 B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

3. 相对 Hash 索引，B+tree 支持范围匹配及排序操作

‍

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

聚集索引和二级索引

在 InnoDB 存储引擎中，根据索引的存储形式，可分为

分类	含义	特点
聚集索引(ClusteredIndex)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引（辅助索引）(SecondaryIndex)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid ​ 作为隐藏的聚集索引

• 聚集索引的叶子节点下挂的是这一行的数据
• 二级索引的叶子节点下挂的是该字段值对应的主键值

回表查询

如果根据二级索引查询，那么会先查询二级索引的 B+ 树得到对应主键值，再从聚集索引的 B+ 树中查找得到数据，即查找两次

查询主键的语句效率 > 查询二级索引语句的效率

主键索引的存储效率

假设：一行数据大小为 1k，一页中可以存储 16 行这样的数据。InnoDB 的指针占用 6 个字节的空间，主键假设为 bigint，占用字节数为 8。

• 高度为 2：$n * 8 + (n + 1) * 6 = 16*1024$, 算出 n 约为 1170

$1171* 16 = 18736$

也就是说，如果树的高度为 2，则可以存储 18000 多条记录。

• 高度为 3：$1171 * 1171 * 16 = 21939856$

也就是说，如果树的高度为 3，则可以存储 2200w 左右的记录

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...)

• 单列索引：只关联一个字段
• 联合/组合索引：关联多个字段

查看索引

SHOW INDEX FROM table_name

删除索引

DROP INDEX index_name ON table_name

SQL 性能分析

SQL 执行频率

show [session|global] status

查看服务器状态信息

• 如果是以增删改为主，可以考虑不对其进行索引的优化。
• 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志。

MySQL 的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log

开启慢查询日志，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动 MySQL 服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log​

systemctl restart mysqld

通过慢查询日志，就可以定位出执行效率比较低的 SQL，从而有针对性的进行优化

profile 详情

​show profiles​ 能够查询时间耗费

查询是否支持

select @@have_profiling;

开启

set profiling = 1;

查询消耗

show profiles;
-- SQL语句各个阶段的耗时
show profile for query query_id;
-- CPU 使用情况
show profile cpu for query query_id;

explain​ 执行计划

可以获取 MySQL 如何执行 select 语句，比如表如何连接和连接顺序

在 select 语句之前加上 explain ​ 或 desc​

• id：select 查询的序列号，表示执行 select 子句或操作表的顺序

  • id 相同，从上到下执行
  • id 不同，值越大，越先执行

• select_type：表示 select 类型

  • ​SIMPLE​：简单表，不使用表连接或子查询
  • ​PRIMARY​：主查询（外层查询）
  • ​UNION​：UNION ​ 中的第二个或后面的查询语句
  • ​SUBQUERY​：select​/where​ 之后包含了子查询

• type：连接类型。以下按性能由好到差排列

  • ​NULL​：不访问任何表
  • ​system​ const​ eq_ref​ ref​ range​ index​ all​

• possible_key：可能应用在这张表上的索引

• key：实际使用的索引

• key_len：索引中使用的字节数，

• rows：MySQL 认为必须要执行查询的行数，在 InnoDB 引擎表中为估计值

• filtered：表示返回结果的行数占需读取行数的比例，值越大越好（因为查询的都是需要的，相当于优化极佳）

索引使用原则

最左前缀法则

建立了联合索引，才需要遵循最左前缀法则

• 查询从索引的最左列开始，且不跳过索引中的列；如果跳跃某一列，其后的索引将会失效（即只有左边的索引生效）
• 如果最左列不存在，则索引会全部失效
• 注意：上述的最左是指建立联合索引时在 () ​ 内指定的顺序，在编写语句时 where= ​ 之后的顺序没有影响

范围查询

联合索引中出现范围查询（>​，<​），会导致范围查询右侧的索引失效

避免方法：使用小于等于 <= ​ 或大于等于 >=​

索引失效

• 索引列运算：在索引上进行运算操作（如函数操作，比如 substring()​）
• 字符串不加引号
• 头部模糊查询（%​ _​）会导致索引失效（尾部模糊 like 'x%' ​ 不会），即应当规避头部模糊查询
• 用 or ​ 分割的条件，只有两侧字段都有索引才会生效
• 数据分布影响：如果 MySQL 评估使用索引比全表更慢，则不使用索引，直接全表扫描（会受具体数据影响）

SQL 提示

在 SQL 语句中加入一些人为的提示来达到优化操作的目的

建议 MySQL 使用索引（仅是建议）

use index()

忽略指定的索引

ignore index()

强制使用索引

force index()

覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经全部都能找到

比如已经建立 (profession, age) ​ 联合索引和 id ​ 主键索引

-- 其中根据(profession, age)联合索引进行查询，在二级索引（辅助索引）中能够查找到id
-- 而最后需要的列只有id和profession，即已经被覆盖了，不需要再由id进行回表查询
select id, profession from user where profession = "x" and age = 10;

-- 此时name未建立索引，那么通过联合索引查询后获得id，仍然需要使用id（聚集索引）进行回表查询，效率降低
select id, profession, name from user where profession = "x" and age = 10;

所以应该尽可能地使查询值覆盖索引

前缀索引

当字段类型为字符串（varchar, text, longtext）时，有时需要索引很长的字符串（大文本字段），会导致查询时浪费大量磁盘 IO 并影响查询效率。可以只将字符串的一部分前缀，从而节省索引空间和提高索引效率

create index idx_xxx on table_name(column(n));

索引选择性

• 前缀长度：可以根据索引的选择性来决定

• 选择性：不重复的索引值（基数）和数据表的记录总数的比值

  • 索引选择性越高查询效率越高
  • 唯一索引的选择性是 1，是最好的索引选择性，效率也最高
  • 但是注意此时的效率也会和字符串长度变长导致的效率降低冲突，需要均衡，所以需要用到前缀索引

-- 选择性计算
select count(distinct field_name) / count(*) from table_name;

前缀索引查询流程

• 在建立辅助索引时，选取前缀
• 在查找聚集索引后，还需要验证是否是对应数值

单列索引和联合索引

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1. 针对数据量较大（十万、百万级），且查询频繁的表建立索引

2. 对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引

4. 对于长字符串的字段，根据字段特点建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时尽量可以覆盖索引，节省存储空间、避免回表查询、提高查询效率

6. 控制索引数量，索引结构会影响增删改（DML）的效率

7. 注意考虑索引列能否存储 NULL​，优化器会根据是否能存储 NULL ​ 进行不同的优化

‍

SQL 优化

插入数据

小批量插入数据 insert​

1. 将多个单条语句合并为一个批量语句（千级别及以下）

2. 手动控制事务，提交多个批量语句

start transaction;
insert into table values();
insert into table values();
commit;

3. 主键顺序插入性能高于乱序插入，尽量实现主键顺序插入

大批量插入数据 load​

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

主键顺序插入性能高于乱序插入

主键优化

数据组织方式

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table, IOT)

• 行数据存储在聚集索引的叶子节点上
• 数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认 16K。一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不下，将会存储到下一个页中，页与页之间会通过指针连接。

页分裂

每个页可以为空，或至少包含 2 行数据，如果一行数据过大，会导致行溢出，根据主键排列

主键顺序插入：

1. 从磁盘中申请页，插入。

2. 当前页未满，继续插入

3. 当前页已满，再申请新页。页与页之间通过双向指针连接

‍

主键乱序插入：

1. 由于索引结构的叶子节点有序排列，若发生下图情况
   

2. 页 1 已满，但是元素 50 应当插入 47 之后，此时只能开辟新的页 3
   

3. 然后将页 1 中后一半的数据移到页 3，再在页 3 中插入新值，同时修改指针，使得页 1 之后为页 3，页 3 之后为页 2
   

上述操作即页分裂

页合并

对数据进行删除时：

• 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用
• 当页中删除的记录达到 MERGE_THRESHOLD​（默认为页的 50%），InnoDB 会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，使用 AUTO_INCREMENT ​ 自增主键
• 尽量不要使用 UUID 和自然编号（身份证号）作为主键
• 业务操作时，避免修改主键

order by ​ 优化

两种排序方法

1. ​Using filesort​：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序

2. ​Using index​：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

using index 的性能更高，应该尽可能优化为 using index

现有联合索引 (A, B)​

1. ​order by C​，没有索引，using filesort

2. ​order by A​，符合最左原则，using index

3. ​order by A, B​，符合最左原则，using index

4. ​order by A, C​，符合最左原则，但是 C 未建立索引，同时有 using index, using filesort

5. ​order by B, A​，B 不符合最左原则，A 符合最左原则（拆开来看），同时有 using index, using filesort

6. ​order by A desc, B desc​，同时使用降序排序，则进行反向扫描，using index

7. ​ order by A, B desc ​，两个索引不同序排序，同时有 using index, using filesort（如果要实现这样的效果，可以在建立索引时指定顺序）

  create index idx_table_A_B on table_name(A asc, B desc);

‍

优化原则

1. 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则

2. 尽量使用覆盖索引

3. 多字段排序，存在部分升序部分降序时，需要注意联合索引的建立规则

4. 如果无法避免 using filesort，对于大数据量排序应当增大排序缓冲区大小 sort_buffer_size​

group by ​ 优化

存在索引优化出现提示：using temporary​

• 可以使用索引优化
• 满足最左前缀法则

limit ​ 优化

在数据量比较大时，如果进行 limit 分页查询，在查询时，越往后，分页查询效率越低

优化思路：

• 一般分页查询时，通过创建覆盖索引能够比较好地提高性能

• 通过覆盖索引加子查询形式进行优化

  • 先通过子查询查询 limit 划分的 id
  • 再通过 where 对应数据

select s.* from table_name t, (select id from table_name order by id limit 100000, 10) a where t.id = a.id;

count​ 优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*)​ 的时候会直接返回这个数，效率很高； 但是如果是带条件的 count 也慢
• InnoDB 引擎执行 count(*)​ 的时候，需要把数据一行一行地从引擎里面读出，然后累积计数

count ​ 原理

​count()​ 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

​count(主键)​	InnoDB 引擎会遍历整张表，把每一行的主键 id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为 null)
​count(字段)​	没有 not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加。有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
​count(数字)​	InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”（不论数字是什么都会累加）进去，直接按行进行累加。
​count(*)​	InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序，count(字段)​ < count(主键 id)​ < count(1)​ ≈ count(*)​，尽量使用 count(*)​

update​ 优化

使用事务进行 update ​ 时，InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

应尽量根据主键和索引进行更新

-- 根据索引定位，使用行锁
update course set name = 'a' where id = 1;
-- 不根据索引定位，使用表锁
update course set name = 'a' where name = 'b';

视图、存储过程、触发器

• 视图

  • 虚拟存在的表，不保存查询结果，只保存查询的 SQL 逻辑
  • 简单、安全、数据独立

• 存储过程

  • 事先定义并存储在数据库中的一段 SQL 语句集合
  • 减少网络交互、提高性能、封装重用

• 存储函数

  • 有返回值的存储过程
  • 可悲存储过程替换

• 触发器

  • 可以在表数据进行 DML 语句之前或之后触发
  • 保证数据完整性、日志记录、数据校验

视图

概述

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的

视图只保存了查询的 SQL 逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条 SQL 查询语句上

作用

1. 简化操作（相当于封装函数）：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件

2. 安全（相当于 VO 类）：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

3. 数据独立（相当于使用常量管理）：视图可帮助用户屏蔽真实表结构变化带来的影响（真实表中的字段改名，但是通过视图起别名可以屏蔽这种变化）

视图不是物理表（类似于接口），查询视图时仍会访问原始表，因此性能和原始表一样，复杂嵌套视图可能性能差。

使用

创建视图

CREATE [OR REPLACE] VIEW view_name[(colomns_name)] AS SELECT语句
[ WITH [CASCADED | LOCAL ] CHECK OPTION ];

查询视图

-- 查看创建视图语句
SHOW CREATE VIEW view_name;

-- 查看视图数据
SELECT * FROM view_name;

修改视图

-- 方式一
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 [CASCADED | LOCAL] CHECK OPTION ​ ​ 子句创建视图时，MySQL 会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。即：当通过这个视图进行 INSERT 或 UPDATE 时，插入或修改后的行必须仍能被该视图选出来。

MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。

为了确定检查的范围，MySQL 提供了两个选项： CASCADED​ 和 LOCAL​，默认值为 CASCADED​。

CASCADED​ 级联

比如，v2 视图是基于 v1 视图的

• 如果在 v2 视图创建的时候指定了检查选项为 cascaded​，但是 v1 视图创建时未指定检查选项。 则在执行检查时，不仅会检查 v2，还会级联检查 v2 的关联视图 v1
• 如果 v2 视图不指定检查选项，而 v1 视图指定了为 cascaded​，执行检查时不会检查 v2，但是会检查 v1

LOCAL​ 本地

• 如果在 v2 视图创建的时候指定了检查选项为 local​ ，但是 v1 视图创建时未指定检查选项。 则在执行检查时，只会检查 v2，不会检查 v2 的关联视图 v1。
• ​local ​ 会递归地寻找当前视图创建时依赖的其他视图，若有指定选项则会进行检查，否则找下一个
• 就是不会强加检查给关联视图，使用 cascaded 相当于为没有指定 CHECK OPTION 的视图指定了 CHECK OPTION

视图的更新

• 要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系

• 如果视图包含以下条件，则不可更新

  • 使用了聚合函数或窗口函数（sum()​ min()​ max()​ count()​）
  • ​DISTINCT​
  • ​GROUP BY​：不是一一对应
  • ​HAVING​
  • ​UNION​ 或 UNION ALL​

存储过程

概述

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

• 封装，复用：将 SQL 语句封装在存储过程中，需要时直接调用
• 可以接收参数，也可以返回数据
• 减少网络交互，提升效率：如果涉及到执行多条 SQL，每一次执行都需要一次网络传输。封装在存储过程中，只需要一次网络交互（理想情况）

使用

创建

CREATE PROCEDURE procedure_name [(参数列表)]
BEGIN
	-- SQL
    select count(*) from table_name;
END;

调用

CALL procedure_name();

查看创建语句

-- 查询指定数据库的存储过程及状态信息
SELECT *
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'classical_practice';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE procedure_1;

删除

DROP PROCEDURE procedure_1;

在命令行中创建存储过程时，会将存储的 SQL 末尾 ; ​ 判断为命令结束符导致提前结束输入，需要使用 delimiter ​ 来修改 SQL 的结束符

-- 将结束符修改为 $$
delimiter $$

CREATE PROCEDURE procedure_name [(参数列表)]
BEGIN
	-- SQL
    select count(*) from table_name;
-- 需要使用 $$来结尾
END$$

变量

MySQL 中存在三种变量类型：

• 系统变量
• 用户定义变量
• 局部变量

系统变量

MySQL 服务器提供，不是用户定义的，属于服务器层面。分为

• 全局变量（GLOBAL）
• 会话变量（SESSION）：一个 console 是一个会话，仅在当前会话内生效

查看系统变量

-- 查看所有系统变量
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;

-- 模糊匹配查找
SHOW SESSION VARIABLES LIKE 'auto%';
SHOW GLOBAL VARIABLES LIKE 'auto%';

-- 指定查找
SELECT @@autocommit;
SELECT @@local.autocommit;

设置系统变量

SET SESSION autocommit = 0;
SET @@SESSION.autocommit = 1;

不指定 SESSION/GLOBAL，默认为 SESSION

MySQL 服务重启后，设置的全局变量会失效。需要持久化则需在 /etc/my.cnf ​ 配置

用户定义变量

用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@ 变量名” 使用就可以。其作用域为当前连接（会话）

赋值

SET @var_age = 18;

SET @var_name := 'Exusiai', @var_age := 19;

SELECT @var_name := 'Texas';

SELECT s_name INTO @var_name FROM student where s_id = 1;

使用

SELECT @var_name;
-- 或者直接使用 @var_name 进行填充

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为 NULL

局部变量

根据需要定义的在局部生效的变量，访问之前，需要 DECLARE ​ 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN ... END ​ 块

使用

create procedure p()
begin
    -- 声明
    declare stu_count int default 0;
    -- 赋值
    set stu_count = 1;
    set stu_count = 2;
    select count(*) into stu_count from student;
    select stu_count;
end;

call p();

if​

用于条件判断

create procedure p_if()
begin
    declare score int default 0;
    declare result varchar(10);

    select s_score into score from score where s_id = 1 and c_id = 1;
    if score >= 90 then
        set result := 'Perfect';
    elseif score >= 60 then
        set result := 'Great';
    else
        set result := 'Bad';
    end if;
    select score, result;
end;

call p_if();

参数

• ​IN​：输入
• ​OUT​：输出
• ​INOUT​：输入输出（类似指针）

create procedure p_arg(in score int, out result varchar(10))
begin
    if score >= 90 then
        set result := 'Perfect';
    elseif score >= 60 then
        set result := 'Great';
    else
        set result := 'Bad';
    end if;
    select score, result;
end;

select s_score into @score from score where s_id = 1 and c_id = 2;

call p_arg(@score, @result);
select @result;

输入输出案例

create procedure p_inout(inout score double)
begin
    set score := score * 0.5;
end;

select s_score into @score from score where s_id = 1 and c_id = 3;

call p_inout(@score);
select @score;

case​

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，执行statement_list2， 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

while​

是有条件的循环控制语句。满足条件后，再执行循环体中的 SQL 语句。

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

repeat​

有条件的循环控制语句, 当满足 until 声明的条件的时候，则退出循环 -> do while​

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

loop​

简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环

• ​LEAVE​ ：配合循环使用，退出循环 -> break​
• ​ITERATE​：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环 -> continue​

[begin_label:] LOOP
	-- SQL逻辑...
END LOOP [end_label];

cursor​ 游标

存储查询结果集的数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN​、FETCH​ 和 CLOSE​

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
-- 打开游标
OPEN 游标名称 ;
-- 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
-- 关闭游标
CLOSE 游标名称 ;

使用例

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name, profession
                                from tb_user
                                where age <=
                                      uage;
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro
    (
        id         int primary key auto_increment,
        name       varchar(100),
        profession varchar(100)
    );
    open u_cursor;
    while true
        do
            fetch u_cursor into uname,upro;
            insert into tb_user_pro values (null, uname, upro);
        end while;
    close u_cursor;
end;
call p11(30);

handler​

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
	... statement ;
handler_action 的取值：
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
condition_value 的取值：
	SQLSTATE sqlstate_value: 状态码，如 02000
	SQLWARNING: 所有以01开头的SQLSTATE代码的简写
	NOT FOUND: 所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
	-- SQL语句
	RETURN ...;
END ;

characteristic：

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL ：不包含 SQL 语句
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

在 university 数据库中，创建一个函数，用于根据学生 ID 返回该学生的总学分。

DELIMITER //  
  
CREATE FUNCTION GetStudentTotalCredits(input_student_id VARCHAR(5))  
    RETURNS INT  
	-- READS SQL DATA: 指定该函数会读取数据库中的数据，但不会修改数据。这是必需的特性声明，告诉 MySQL 服务器该函数的行为。
    READS SQL DATA    
    -- DETERMINISTIC: 表示对于相同的输入参数，函数总是返回相同的结果。这有助于 MySQL 优化查询执行计划。
    DETERMINISTICBEGIN  
    
	-- 声明一个整型变量total_credits，初始值为0
    DECLARE total_credits INT DEFAULT 0;  
  	-- COALESCE函数用于处理NULL值，当SUM结果为NULL时（如没有符合条件的记录），返回0
    SELECT COALESCE(SUM(c.credits), 0)  
    INTO total_credits  
    FROM takes t  
             JOIN course c ON t.course_id = c.course_id  
    WHERE t.id = input_student_id  
      AND t.grade IS NOT NULL  
      AND t.grade != 'F';  
  
    RETURN total_credits;  
END //  
  
DELIMITER ;

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的 SQL 语句集合。这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作

• 使用别名 OLD​ 和 NEW​ 来引用触发器中发生变化的记录内容
• 只支持行级触发（影响了几行，触发几次），不支持语句级触发

触发器类型	​NEW​/OLD​
INSERT	​NEW​ 将要或者已经新增的数据
UPDATE	​OLD​ 修改之前的数据，NEW​ 将要或已经修改后的数据
DELETE	​OLD​ 将要或者已经删除的数据

使用

创建

create trigger trigger1
    before insert
    on course
    for each row
begin
    insert into course_log (course_id, course_name) value (NEW.c_id, NEW.c_name);
end;

查看

show triggers;

删除

drop trigger trigger1

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

按照锁的粒度分：

• 全局锁：锁定数据库中所有表
• 表级锁：锁定被操作的整张表
• 行级锁：锁定被操作的行

核心思路在于：读读共享，读写互斥

全局锁

对整个数据库实例加锁

• 加锁后整个实例处于只读状态
• 后续的 DML，DDL，已经更新操作的事务提交都会被阻塞；可以执行 DQL

使用场景：全库的逻辑备份（数据备份就使用了查询操作）

使用

加全局锁

flush tables with read lock;

数据备份，在操作系统命令行环境下执行

mysqldump -h 127.0.0.1 -u root -p 123456 database_name > database_name.sql

释放锁

unlock tables;

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在 InnoDB 引擎中，可以在备份时加上参数 –single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -h 127.0.0.1 -u root -p 123456 database_name > database_name.sql

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM、InnoDB、BDB 等存储引擎中

分类：

• 表锁
• 元数据锁（meta data lock, MDL）
• 意向锁

表锁

• 表共享读锁（read lock）
• 表独占写锁（write lock）

-- 加锁
lock tables table_name read/write;
-- 解锁
unlock tables;

对当前客户端无影响，对于其他客户端：

• 读锁

  • 不会影响读，会阻塞写

• 写锁

  • 会阻塞读和写

元数据锁

MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。

MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性

元数据：表结构

• 当对一张表进行增删改查的时候，加 MDL 读锁(共享)
• 当对表结构进行变更操作的时候，加 MDL 写锁(排他）

对应 SQL	锁类型	说明
lock tables xxx read /write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select、select … lock in share mode	SHARED_READ	与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
insert、update、delete、select … for update	SHARED_WRITE	与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
alter table …	EXCLUSIVE	与其他的 MDL 都互斥

• 当执行 SELECT、INSERT、UPDATE、DELETE 等语句时，添加的是元数据共享锁（SHARED_READ /SHARED_WRITE），之间是兼容的
• 会阻塞元数据排他锁（EXCLUSIVE）

查看元数据锁情况：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

例：客户端 A 加上了行锁，此时客户端 B 想要加上表锁，则必须遍历所有行来判断是否有行锁。因此引入意向锁，标记表中是否存在行锁，则不需要再遍历整张表

• 意向共享锁(IS) : 由语句 select … lock in share mode 添加 。 与表锁共享锁(read) 兼容，与表锁排他锁(write) 互斥
• 意向排他锁(IX) : 由 insert、update、delete、select…for update 添加。与表锁共享锁(read) 及排他锁(write) 都互斥，意向锁之间不会互斥

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

查看意向锁和行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此进行 update 和 delete。支持 RC（Read Commit）、RR（Repeatable Read）隔离级别

2. 间隙锁（Gap Lock）：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生幻读。支持 RR 隔离级别

3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据和间隙。支持 RR

‍

行锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

S 和 S 共享，X 和 X、X 和 S 互斥

加锁规则：

SQL	行锁类型	说明
insert	排他锁	自动加锁
update	排他锁	自动加锁
delete	排他锁	自动加锁
select	不加锁
select … lock in share mode	共享锁	手动添加 lock in share mode
select … for update	排他锁	手动添加 for update

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
• InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁

间隙锁&临键锁

• 索引上的等值查询（唯一索引），给不存在的记录加锁时, 优化为间隙锁

• 索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

  • 等值：临键锁
  • 对不满足值之前的间隙加间隙锁

• 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止

  如查询 >= 目标值：

  • [目标值]：行锁
  • (目标值, 最大值]：临键锁
  • (最大值, 正无穷)：正无穷的临键锁

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

InnoDB 引擎

逻辑存储结构

表空间

表空间是 InnoDB 存储引擎逻辑结构的最高层， 如果用户启用了参数 innodb_file_per_table(在 8.0 版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个 mysql 实例可以对应多个表空间，用于存储记录、索引等数据

段

段，分为：

• 数据段（Leaf node segment）
• 索引段（Non-leaf node segment）
• 回滚段（Rollback segment）

InnoDB 是索引组织表，数据段就是 B+ 树的叶子节点， 索引段即为 B+ 树的非叶子节点

段用来管理多个 Extent（区）

区

区，表空间的单元结构，每个区的大小为 1M。 默认情况下， InnoDB 存储引擎页大小为 16K， 即一个区中一共有 64 个连续的页

页

页，是 InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区

行

行，InnoDB 存储引擎数据是按行进行存放的。

在行中，默认有两个隐藏字段：

• Trx_id：每次对某条记录进行改动时，都会把对应的事务 id 赋值给 trx_id 隐藏列
• Roll_pointer：每次对某条引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息

架构

内存结构

在专用服务器上，通常将多达 80％的物理内存分配给缓冲池

参数设置：

show variables like 'innodb_buffer_pool_size';

Buffer Pool 缓冲池

• 缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘 IO，加快处理速度

• 缓冲池以 Page 页为单位，底层采用链表数据结构管理 Page。根据状态，将 Page 分为三种类型：

  • free page：空闲 page，未被使用
  • clean page：被使用 page，数据没有被修改过
  • dirty page：脏页，被使用 page，数据被修改过，也中数据与磁盘的数据产生了不一致

Change Buffer 更改缓冲区

• Change Buffer，更改缓冲区（针对于非唯一二级索引页），在执行 DML 语句时，如果这些数据 Page 没有在 Buffer Pool 中，不会直接操作磁盘，而会将数据变更存在更改缓冲区 Change Buffer 中，在未来数据被读取时，再将数据合并恢复到 Buffer Pool 中，再将合并后的数据刷新到磁盘中
• 二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘 IO。有了 ChangeBuffer 之后，可以在缓冲池中进行合并处理，减少磁盘 IO

Adaptive Hash Index 自适应哈希索引

• 用于优化对 Buffer Pool 数据的查询

• MySQL 的 innoDB 引擎中虽然没有直接支持 hash 索引，但是提供了自适应 hash 索引

  • hash 索引在进行等值匹配时，一般性能是要高于 B+ 树的，hash 索引一般只需要一次 IO 即可，而 B+ 树可能需要几次匹配；但是 hash 索引不适合做范围查询、模糊匹配等

• InnoDB 存储引擎会监控对表上各索引页的查询，如果观察到在特定的条件下 hash 索引可以提升速度，则建立 hash 索引，称之为自适应 hash 索引（无需人工干预，系统自行完成）

Log Buffer 日志缓冲区

• 用来保存要写入到磁盘中的 log 日志数据（redo log 、undo log），默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O

• innodb_log_buffer_size：缓冲区大小

• innodb_flush_log_at_trx_commit：日志刷新到磁盘时机，取值主要包含以下三个：

  • 0：每秒将日志写入并刷新到磁盘一次
  • 1：日志在每次事务提交时写入并刷新到磁盘，默认值
  • 2：日志在每次事务提交后写入，并每秒刷新到磁盘一次

磁盘结构

System Tablespace 系统表空间

• 系统表空间是更改缓冲区的存储区域
• 如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据
• 参数：innodb_data_file_path

File-Per-Table Tablespaces 独立表空间

• 如果开启了 innodb_file_per_table 开关 ，则每个表的文件表空间包含单个 InnoDB 表的数据和索引 ，并存储在文件系统上的单个数据文件中
• 开关参数：innodb_file_per_table ，该参数默认开启，即每创建一个表都会产生一个表空间文件

General Tablespaces 通用表空间

• 通用表空间，需要通过 CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间

创建表空间

create tablespace ts_name add datafile 'file_name' engine = engine_name;

创建表时指定表空间

create table xxx ... tablespace ts_name;

Undo Tablespaces 撤销表空间

• 撤销表空间，MySQL 实例在初始化时会自动创建两个默认的 undo 表空间（初始大小为 16MB），用于存储 undo log 日志

Temporary Tablespaces 临时表空间

• InnoDB 使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据

Doublewrite Buffer Files 双写缓冲区

• InnoDB 将数据页从 Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据

Redo Log 重做日志

• 重做日志，是用来实现事务的持久性

• 该日志文件由两部分组成

  • 重做日志缓冲（redo logbuffer）：内存
  • 重做日志文件（redo log）：磁盘

• 当事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用

后台线程

核心作用：将内存区内的数据在合适的时间装载入磁盘区

Master Thread 核心后台线程

• 负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中, 保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo 页的回收

IO Thread IO 线程

• 在 InnoDB 存储引擎中大量使用了 AIO（异步 IO）来处理 IO 请求, 这样可以极大地提高数据库的性能，而 IOThread 主要负责这些 IO 请求的回调

线程类型	默认个数	职责
Read Thread	4	读
Write Thread	4	写
Log Thread	1	日志缓冲区刷新到磁盘
Insert Buffer Thread	1	写缓冲区刷新到磁盘

查看状态信息：

show engine innodb status \G;

Purge Thread

• 用于回收事务已经提交的 undo log

Page Cleaner Thread

• 协助 Master Thread 刷新脏页到磁盘

事务原理

• 原子性：undo log
• 一致性：redo log
• 隔离性：undo log + redo log
• 持久性：锁 + MVCC

事务概念

• 事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

• 特性

  • 原子性（Atomicity） ：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性（Consistency） ：事务完成时，必须使所有的数据都保持一致状态（数据内容以及格式）
  • 隔离性（Isolation） ：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  • 持久性（Durability） ：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

• 原子性、一致性、持久化：redo log、undo log
• 持久性：锁、MVCC

Redo Log

• 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性

问题描述：

• 在一个事务中执行多个增删改的操作时，InnoDB 会先操作缓冲池中的数据，如果缓冲区没有对应的数据，会通过后台线程将磁盘中的数据加载出来，存放在缓冲区中，然后将缓冲池中的数据修改，修改后的数据页称为脏页
• 脏页则会在一定的时机，通过后台线程刷新到磁盘中，从而保证缓冲区与磁盘的数据一致
• 但缓冲区的脏页数据并不是实时刷新的，而是一段时间之后将缓冲区的数据刷新到磁盘中，假如刷新到磁盘的过程出错了，而提示给用户事务提交成功，数据却没有持久化下来，就出现问题了：没有保证事务的持久性

redo log 解决问题：

• 当对缓冲区的数据进行增删改之后，首先将操作的数据页的变化记录在 redo log buffer 中
• 在事务提交时，将 redo log buffer 中的数据刷新到 redo log 磁盘文件中
• 过一段时间之后，如果刷新缓冲区的脏页到磁盘时发生错误，就可以借助于 redo log 进行数据恢复，保证了事务的持久性
• 如果脏页成功刷新到磁盘或者涉及到的数据已经落盘，此时 redolog 没有作用，可以删除，所以存在的两个 redolog 文件循环写

（即一个兜底程序，没发生错误时不会用到）

WAL

• 操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘
• redo log 是日志文件在，往磁盘文件中写入数据是顺序写的
• 顺序写的效率，要远大于随机写。这种先写日志的方式，称之为 WAL（Write-Ahead Logging）

Undo Log

• 回滚日志，用于记录数据被修改前的信息 , 作用包含两个 : 提供回滚（保证事务的原子性）和 MVCC（多版本并发控制）

• undo log 和 redo log 记录物理日志不一样，它是逻辑日志。

  • 可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录
  • 当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚

• Undo log 销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除 undo log，因为这些日志可能还用于 MVCC

• Undo log 存储：undo log 采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含 1024 个 undo log segment

（优于快照，可以联想 Redis 的 AOF 和 RDB）

MVCC 多版本并发控制

MVCC 概念

当前读

• 读取的是记录的最新版本
• 读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
• 日常的操作如：select … lock in share mode(共享锁)，select … for update、update、insert、delete(排他锁)都是当前读

在默认的 RR 隔离级别下，事务 A 中依然可以读取到事务 B 最新提交的内容，因为在查询语句后面加上了 lock in share mode 共享锁，此时是当前读操作

快照读

简单的 select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

• Read Committed：每次 select，都生成一个快照读（即后面读取都是读取快照，不会受其他事务影响）

• Repeatable Read：开启事务后第一个 select 语句才是快照读的地方（之后重复读取都是读取的快照内容，所以实现可重复读）

• Serializable：快照读会退化为当前读（因为不涉及并发了）

MVCC

• 全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突
• 快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能
• MVCC 的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log 日志、readView

实现原理

隐藏字段

隐藏字段	作用
DB_TRX_ID	最近修改事务 ID，记录插入这条记录或者最后一次修改该记录的事务 ID，自增
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合 undo log
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，就会生成隐藏主键

查看 idb 文件的表结构信息：

ibd2sdi idb_name.ibd

undo log

回滚日志：在 insert、update、delete 的时候产生的便于数据回滚的日志

• insert：undo log 只在回滚时需要，在事务提交后可被立即删除
• update、delete：undo log 在回滚和快照读时需要，不会被立即删除

版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录

注意：此处的记录不是数据，而是 SQL 语句

readview

ReadView（读视图）是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

字段	作用
m_ids	当前活跃的事务 ID 集合（未提交的）
min_trx_id	最小活跃事务 ID
max_trx_id	预分配事务 ID，当前最大事务 ID+1（自增）
creator_trx_id	ReaadView 创建者的事务 ID

版本链数据的访问规则：根据当前事务 ID（trx_id）来判断

核心在于：尽可访问本事务的或者已经提交的数据

条件	是否可以访问	说明
trx_id == creator_trx_id	可以	数据是当前这个事务更改的
trx_id < min_trx_id	可以	数据已经提交
trx_id > max_trx_id	不可以	事务是在 ReadView 生成后才开启
min_trx_id <= trx_id <= max_trx_id	若 trx_id 不在 m_ids，则可以	数据已经提交

在进行匹配时，会从 undo log 的版本链，从上到下进行挨个匹配，当满足可访问条件时终止

不同的隔离级别，生成 ReadView 的时机不同：

• READ COMMITTED：在事务中每一次执行快照读时生成 ReadView。
• REPEATABLE READ：仅在事务中第一次执行快照读时生成 ReadView，后续复用该 ReadView

MySQL 管理

系统数据库

MySQL 工具

客户端工具

mysqladmin

mysqlbinlog

mysqlshow

mysqldump

mysqlimport

mysqlsource