MySQL高级特性

POSTED 2024-12-07
UPDATE 2024-12-07

WORDS 30830
READ 1小时18分钟
VIEW 110

MySQL

MySQL 的体系结构

连接层：最上层，是客户端和链接服务，完成一些类似于连接处理、授权认证及相关的安全方案。服务器会为每个安全接入的客户端验证其操作权限。
服务层：第二层负责大部分核心功能，如 SQL 接口、查询缓存、SQL 分析优化、内置函数执行等。跨存储引擎的功能也在此层实现，例如过程、函数等。
引擎层：引擎层负责 MySQL 中数据的存储和读取，服务通过 API 和存储引擎进行通信。不同的存储引擎具备不同的功能，可以根据需求的不同，选择不同的存储引擎。
存储层：将数据存储在文件系统上，完成与存储引擎的交互。

存储引擎

存储引擎是存储数据、建立索引、更新/查询数据等操作的实现方式。存储引擎是基于表的，也可以被称为表类型。在 MySQL 5.5 版本之后，默认的存储引擎是 InnoDB。如果需要使用其它存储引擎需要在建表时指定。

# 查询当前数据库支持的存储引擎
show engines;

Engine	Support	Comment	Transactions	XA	SavePoints
存储引擎名称	是否支持	说明	事务支持	XA协议支持	保存点支持

# 创建表 使用 MyISAM 存储引擎
create table myisam_table (
    id int,
    name varchar(24)
) engine = MyISAM;

存储引擎特点

InnoDB：兼顾高性能和高可靠性的通用存储引擎，其特点是：DML 操作遵循 ACID 模型，且支持事务、支持行级锁、支持外键 FOREIGN KEY 约束，确保数据的完整和正确。在存储时，InnoDB 引擎的每张表都会对应一个表空间文件，其文件格式为 <table_name>.idb，用于存储表的表结构、数据和索引。可以通过参数 innodb_file_per_table 控制。InnoDB 的存储结构为 TableSpace -> Segment -> Extent -> Page -> Row，其中 Extent 和 Page 的存储大小限制分别为 1M 和 16K，Row 对应真正的数据行。
MyISAM：MySQL 早期默认的存储引擎，特点是查询速度快，但是不支持事务、外键和行级锁，只支持表锁。在存储时，MyISAM 的每张表会对应三个文件，分别是 <table_name>.MYD、<table_name.MYI> 和 <table_name>.sdi，其中 MYD 文件是表存储的数据、MYI 文件则是表的索引、sdi 文件是表结构。
Memory：Memory 引擎的数据存储在内存中，只适合作为临时表或缓存使用，其特点默认支持 hash 索引，查询速度非常快。在存储时，只有一个 sdi 文件用于存储表结构。

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+Tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	支持	支持	-
空间使用	高	低	-
内存使用	高	低	中等
批量插入速度	低	高	高
外键	支持	-	-

存储引擎选择

如果对事务的完整性和并发环境下数据的一致性要求较高，并且数据操作除了查询和插入之外，还有频繁的删除和更新操作，那么 InnoDB 引擎比较适合；相反，如果只是以读和插入操作为主，并且对事务的完整性、数据的一致性要求不高，那么使用 MyISAM 引擎可以获得更快的速度，例如日志记录和操作记录；Memory 引擎通常只用于临时表和缓存中，不推荐用于存储业务数据。

索引

索引是可以用于高效获取数据的有序结构。除数据外，数据库系统还维护着满足特定查找算法的数据结构，用于实现高级查找算法，这种数据结构就是索引。

优点：

提高数据检索的效率，降低数据库 I/O 成本
通过索引列对数据进行排序，降低数据排序成本

缺点：

索引列需要占用空间
索引会降低表的更新速度，在进行插入、修改和删除操作时，需要同时维护索引数据结构

索引结构

MySQL 的索引实在存储引擎层实现的，不同的存储引擎有不同的结构，主要有：

B+tree 索引：最常见的索引类型，InnoDB、MyISAM、Memory 引擎都支持。
Hash 索引：使用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询，只有 Memory 引擎支持。
R-Tree：空间索引，MyISAM 引擎的特殊索引类型，主要用于地理空间数据类型
Full-text：全文索引，通过倒排建立索引，用于快速匹配文档，MyISAM 和 5.6 版本之后的 InnoDB 引擎支持。

B+tree

B-tree, 可视化 B+tree, 可视化

B+tree 可以看做是 B-tree 的优化版本，相较于 B-tree 的主要区别在于：

所有的数据都会出现在叶子节点（节点分裂时，向上分裂的节点会继续保存在叶子节点中）
叶子节点之间会相互连接，形成单向链表

MySQL 索引数据结构对 B+tree 进行了优化，将叶子节点的单向指针改成了双向指针，同时，尾叶子节点也添加了指向头叶子节点的指针。将原 B+tree 中叶子节点之间形成的单向链表优化为了双向循环链表，提高了区间访问的性能。

Hash

MySQL 中的 Hash 索引是通过哈希表来实现的，当出现哈希冲突时，通过链式地址解决。Hash 索引只能用于对等比较，不支持模糊和范围查询，同时也无法利用索引进行排序操作。 Hash 索引只支持 Memory 引擎，在 InnoDB 中，存储引擎会根据 B+tree 在指定条件下自动构建 Hash 索引。

索引分类

分类	说明	特点	关键字
主键索引	针对主键创建的索引	默认自动创建，只能有一个	`PRIMARY`
唯一索引	避免表中某数据项的值重复	可以有多个	`UNIQUE`
常规索引	快速查找特定数据	可以有多个
全文索引	用于查找文本中的关键字	可以有多个

在 InnoDB 引擎中，根据索引的存储形式，又可以分为两种：

聚集索引 Clustered Index：数据和索引一起存储，索引结构的叶子节点保存行数据，必须有，且只能有一个。
二级索引 Secondary Index：数据和索引分开存储，索引机构的叶子节点保存的是对应的主键，可以存在多个。

聚集索引的选取规则：

如果存在主键，主键就是聚集索引
不存在主键，将使用第一个唯一索引作为聚集索引
如果没有主键也没有唯一索引，那么会自动生成一个 rowid 作为隐藏的聚集索引

索引语法

创建索引

# index_name：索引名称，同一张表中不能重复
# table_name：创建索引的表名称
# column_name：需要创建索引的列名称，为多个则为联合索引
# create unique index 创建唯一索引
# create fulltext index 创建全文检索索引
create index <index_name> on <table_name> (<column_name>...);

查看索引

show index from <table_name>;

删除索引

drop index <index_name> on <table_name>;

SQL性能分析

执行频次

MySQL 数据库可以通过状态统计命令查询当前数据库各操作命令的访问频次

# [global | session] 表示全局或当前回话
# 会返回 SELECT INSERT DELETE UPDATE COMMIT 等操作的次数
show global status like 'Com_______';

慢查询日志

慢查询日志记录了执行时间超过了指定参数的所有 SQL 查询语句，其默认时间为 10s 且没有开启，可以通过命令来查询慢查询日志的开启状态。

show variables like 'slow_query_log';

开启慢查询日志需要编辑 MySQL 配置文件，在 /etc/my.cnf 中添加慢查询配置

# 开启慢查询日志
slow_query_log=1

# 设置慢查询的时间为 3s
long_query_time=2

慢查询日志的默认存储路径为 /var/lib/mysql/xxxxx-slow.log 。

profile详情

show profiles 可以输出 SQL 查询时耗费时间的操作，从而针对性的进行 SQL 优化。

# 查询数据库是否支持 profile 操作
select @@have_profiling;

profiling 默认是关闭的，需要通过 set 语句来开启

# session | global 会话开启或全局开启
set session profiling = 1;

然后可以通过以下命令查看 SQL 的执行耗时

# 查看每一条 sql 的耗时情况
show profiles;

# 查询指定 sql 语句各阶段的耗时情况
show profile for query <query_id>;

# 查询指定 sql 语句CPU的使用情况
show profile cpu for query <query_id>;

explain执行计划

在任意的 SQL 语句前面加上 EXPLAIN 或 DESC 命令即可获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。EXPLAIN 执行计划返回的字段含义：

id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id 相同，顺序执行；id 不同，值越大的越先执行）
select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，不使用表连接或者子查询）、PRIMARY （主查询，外层的查询）、UNION （UNION 中第二个或者后面的查询语句）、SUBQUERY（子查询）等
type：表示连接类型，性能从高到低的连接类型为 NULL -> system -> const -> eq_ref -> ref -> range -> index -> all。当使用唯一索引时连接类型为 const，非唯一索引则为 ref；index 代表使用了索引，但会扫描整个索引；all 则为全表扫描，性能最差
possible_key：显示这张表中可能会用到的索引，可以有一个或多个
key：实际使用的索引，如果为 NULL 则代表没有使用索引
key_len：索引中使用的字节数，为索引字段最大可能长度，并非实际使用长度。在不损失精确性的前提下，长度越短越好
rows：MySQL 认为必须要执行查询的行数，在 InnoDB 引擎的表中，是一个估计值
filtered：查询结果返回的行数占据读取行数的百分比，值越大越好

索引使用

准备一张测试表，并创建索引

# 测试表
create table tb_user  
(  
    id          bigint auto_increment comment '主键ID'  
        primary key,  
    name        varchar(64) not null comment '名称',  
    phone       varchar(24) not null comment '手机号',  
    email       varchar(64) null comment '邮箱',  
    city        varchar(24) null comment '城市',  
    age         bigint      null comment '年龄',  
    gender      bigint      null comment '性别',  
    status      bigint      null comment '状态',  
    create_time datetime    null comment '创建时间',  
    constraint idx_user_phone  
        unique (phone)  
)  
    comment '用户表';  

# 联合索引
create index idx_user_city_age_status on tb_user (city, age, status);  

create index idx_user_email on tb_user (email);  
create index idx_user_name on tb_user (name);

# 唯一索引
create index unique idx_user_phone on tb_user (phone);

最左前缀法则

如果需要使用多列（联合）索引，那么查询需要从索引的最左列开始，且不能跳过索引中间的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。

最左前缀法则只要查询条件中存在最左列就会走索引，和 SQL 查询中列的位置没有关系，最左列在 WHERE 语句的任意位置都可以。（其它索引字段也同理，WHERE 语句的字段顺序不需要和索引顺序一致）

可以使用下列 SQL 语句测试最左前缀法则

# 会走索引 符合最左前缀
select * from tb_user where city = '徐汇区';  

# 会走索引 使用 city 和 age 列的索引
select * from tb_user where city = '徐汇区' and age = 20;  

# 会走索引 但是由于跳过了 age 列，status列的索引会失效
select * from tb_user where city = '徐汇区' and status > 3;  

# 会走索引 三列的索引都会使用
select * from tb_user where city = '徐汇区' and age = 20 and status = 0;  

# 不走索引 全表扫描
select * from tb_user where age = 20 and status = 0;

# 会走索引 三列的索引都会使用 
select * from tb_user where status = 0 and age = 20 and city = '徐汇区';

索引失效

在联合索引中，如果出现范围查询 > | <，那么范围查询右侧的列索引会失效。

# 由于 age 字段使用了范围查询 会导致其之后所有列的索引都失效
select * from tb_user where city = '徐汇区' and age > 20 and status = 0;

将范围查询的 > | < 改用为 >= | <= ，右侧的列索引不会失效

# 将 > 改为 >= 不会导致后续索引失效
select * from tb_user where city = '徐汇区' and age >= 21 and status = 0;

在索引列上进行任何运算操作，也会导致索引失效

# 查询所有手机号后缀为22的用户
# 由于对索引列使用了substring()函数进行运算，索引失效改用全表扫描
select * from tb_user where substring(phone, 10, 2) = '22';

对于字符串类型字段，在查询时查询条件不加引号会涉及隐式类型转换，也会导致索引失效

# 查询字段数据类型为char，查询条件为number，涉及隐式类型转换，索引会失效
select * from tb_user where phone = 13513734122;

对于模糊匹配，除了仅尾部模糊匹配 xxx% 的情况外，索引都会失效

# 查询手机号以189开头的用户 使用尾部模糊匹配 索引会生效
select * from tb_user where phone like '189%';

# 查询手机号以22结尾的用户 头部模糊匹配 索引失效
select * from tb_user where phone like '%22';

如果在添加在条件查询中使用 OR 语句，如果 OR 前条件中的列有索引，后面的列中没有索引的话，那么 OR 语句涉及到的索引都不会被使用

# id列有索引 age列的索引不符合最左原则 会导致索引失效
select * from tb_user where id = 10 or age = 20;

# or两侧的条件都有索引，索引不会失效
select * from tb_user where id = 10 or city = '徐汇区';

数据分布对于 MySQL 的索引使用也有影响，当 MySQL 评估全表扫描比索引更快时，也不会使用索引。比如查询一个字段是否为 NULL 时，如果此字段的 NULL 值数据明显少于非 NULL 数据，那么查询会走索引。NOT NULL 也同理

# 所有的手机号都大于00000000000，使用索引会比全表扫描更慢，MySQL会直接使用全表扫描
select * from tb_user where phone > '00000000000';

# 如果 city 字段为null的数据明显少于不为null的数据 那么查询会走索引
# 如果null数据大于非null数据 那么MySQL会全表扫描
select * from tb_user where city is null;

SQL提示

SQL 提示就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的，是优化数据库的一个重要手段。

假如某个字段存在多个索引时，可以通过 use index 和 ignore index 向 MySQL建议使用某个索引查询和忽略某个索引，只是建议，具体是否使用需要取决于 MySQL评估。强制指定使用某个索引进行查询，需要使用 force index 。

# 为 city 字段添加一个单列索引
create index idx_user_city on tb_user (city);

# 查询还是默认使用 idx_city_age_status 索引
select * from tb_user where city = '徐汇区';

# 假如需要使用单列索引进行查询，以下三种方法都可以
# 建议使用 idx_user_city 索引查询
select * from tb_user use index (idx_user_city) where city = '徐汇区';

# 建议忽略 idx_user_city_age_status 索引
select * from tb_user ignore index (idx_user_city_age_status) where city = '徐汇区'

# 指定使用 idx_user_city 索引查询
select * from tb_user force index (idx_user_city) where city = '徐汇区';

覆盖索引

在一次查询中，查询使用了索引，并且查询需要返回的列在索引中可以全部找到，那么就称为覆盖索引。SELECT 查询应该尽量使用覆盖索引，减少 SELECT * 。

判断查询是否使用了覆盖索引可以通过 EXPLAIN 的 Extra 列判断：

Using index condition：查找使用了索引，但是需要回表查询数据
Using where; Using index：查找使用了索取且需要的数据都能在索引中找到，不需要回表

# 需要的数据id和city都能在索引中获取，不需要回表
select id, city from tb_user where city = '青浦区' and age >= 20 and status = 0;

# name字段不存在于查询使用的索引中，需要回表查询数据
select id, city，name from tb_user where city = '青浦区' and age >= 20 and status = 0;

前缀索引

当字段类型为 varchar 或 text 时，有时候需要索引很长的字符串，这会导致索引变得很大，浪费大量的磁盘 IO 。可以只对字符串的一部分前缀建立索引，减低索引占用并提高查询性能，前缀的长度可以根据索引列不重复的索引值和总记录数的比值确定，比值越高则查询效率越高。唯一索引的选择性（比值）为1，性能最好。

# 查询 email 字段不同长度的选择性
# 长度越短索引大小越小，选择性也可能越低
select count(distinct substring(email, 1, 3)) / count(email) from tb_user;

# 为 email 字段建立一个前缀长度为3的索引
create index idx_user_email_prefix on tb_user(email(3));

单列索引和联合索引

如果存在多个查询条件，考虑针对查询字段建立索引时，尽量使用联合索引。假如分别建立了两个字段的单列索引，MySQL 在查询时，也只会使用某一列的索引，而其它列需要回表查询。

联合索引在存储时会在每个节点保存所有索引列的数据，当只查询联合索引列包含的数据时，MySQL 会自动覆盖索引不会回表查询。

索引设计

针对数据量大，查询比较频繁的表建立索引
针对常作为查询条件、排序和分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果字段是字符串类型且长度较长，考虑建立前缀索引
尽量使用联合索引，减少单列索引
控制索引的数量，索引越多，维护索引结构的代价越大，增删改的效率越低
如果索引列不能存储 NULL 值，要使用 NOT NULL 进行约束，有利用优化器更有效的使用索引

SQL优化

插入数据

insert优化

需要进行大量数据的插入时，使用批量插入，单次插入的数据条数尽量控制在 500-1000 行，如果数据量过大，进行分批批量插入。
存在大量的单次插入时，可以选择手动控制事务，避免 MySQL 在每次单次插入时都开启和插入事务，减少事务操作带来的性能影响。
主键在没有设置自增的情况下，尽量对主键进行顺序插入。

大批量插入数据

如果一次性需要插入大批量数据，使用 insert 语句可能耗时较久，可以使用 MySQL 提供的 load 命令进行插入

# 连接服务端时，需要加上参数 --local-infile
mysql --local-infile -u root -p

# 设置参数 local-infile 为 1，开启本地文件数据导入
set global local_infile=1;

# 使用load指定插入数据
# 插入 /opt/example.csv 中的数据
# fields terminated by 指定字段数据间的分隔符
# line terminated by 指定行数据间的分隔符
load data local infile '/opt/example.csv' into table tb_user fields terminated by ',' line terminated by '\n';

主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。InndDB 存储引擎的页可以被任意填充数据，每个非空页至少包含 2-N 行数据（如果某一行的数据过大，会产生行溢出）。主键在顺序插入时，由于数据是有序的，只需要将数据添加到尾部即可，每个页的存储空间可以最大化利用；如果是乱序插入，则会产生页分裂现象。当删除一行记录时，记录并没有被物理删除，只是将标记 flaged 改为删除并且其占用的空间可以被其它记录声明使用。当某个页中被标记删除的记录达到 MERGE_THRESHOLD （默认为页的50%）时，InnoDB 会判断是否可以合并左右两边相邻的页以优化空间使用，这种现象叫作页合并。

主键的设计原则：

满足业务需求的情况下，尽量降低主键的长度
插入数组时，尽量选择顺序插入，主键尽量使用自增类型
尽量不要使用 UUID 或其它自然主键作为表的主键，比如身份证号
业务操作时，尽量不要修改主键

order by优化

MySQL 中有两种方式可以完成排序操作：

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的都叫 FileSort 排序。
Using Index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高。索引在创建时，底层的 B+tree 默认会按照升序排序，这就会导致逆序排序时会反向扫描索引。

# 创建一个联合索引用于排序
create index idx_user_age_phone on tb_user(age, phone);

由于索引默认采用升序排序，当排序使用 age 或 age，phone 时，都会使用索引。使用索引进行排序也需要遵守最左原则。

# 只能查询索引包含的字段，否则因为需要回表查询 InnoDB引擎会直接使用全表扫描排序
# 可以使用索引排序
select id, age, phone from tb_user order by age;

# 可以使用索引，反向扫描
select id, age, phone from tb_user order by age desc;

# 可以使用索引
select id, age, phone from tb_user order by age, phone;

# age 字段可以使用索引，phone字段需要全表扫描排序
# age, phone desc 也同理
select id, age, phone from tb_user order by age desc, phone;

针对不同字段不同排序规则的情况，也可以创建不同排序规则的索引来优化排序性能

# 针对字段创建不同排序规则的索引
create index idx_user_age_asc_phone_desc on tb_user(age asc , phone desc);  

# 会直接使用索引排序
explain select id, age, phone from tb_user order by age, phone desc;

group by优化

group by 索引优化也是遵从最左原则

# 符合最左原则，直接使用索引
explain select city, count(*) as total from tb_user group by city;  

# 不符合最左原则，会使用索引也会使用临时表
explain select age, count(*) as total from tb_user group by age ;  

# 使用索引
explain select city, age, count(*) as total from tb_user group by city, age ;  

# 加上where条件会直接使用索引，不会使用临时表
explain select age, count(*) as total from tb_user where city = '江北区' group by age ;

limit优化

limit 分页在大数据量的情况下，其偏移量越大耗时越长。假如查询偏移量为 1000000, 10，MySQL 在查询时需要先排序前 1000010 条数据，再返回 1000000 - 1000010 10条记录，其它记录则丢弃，查询非常耗费性能。一般分页查询时，通过创建覆盖索引加子查询的形式进行优化。

# 会排序扫描900010行数据
select * from tb_example limit 900000, 10;

# 先通过子查询查询对应的主键id，因为id有唯一索引，不需要回表查询
# 再通过id查询对应数据，也因为有主键索引，查询耗时较短
select * from tb_example as te, (select id from tb_example order by id limit 900000, 10) te2 where te.id = te2.id;

count优化

以常用的查询全表行数据量为例

select count(*) from tb_example;

在 MyISAM 引擎中，MyISAM 会把表的总行数写到磁盘中，执行 count(*) 会直接返回这个数，时间复杂度为 $O(1)$

$O(1)$

。而对于

InnoDB

引擎，执行

count(*) 需要将逐行读取数据然后累积计数，时间复杂度为 $O(n)$

，在大数据量的情况下非常耗时。 count() 的几种用法：

count(*)：InnoDB 引擎有对应优化，不会取出字段和值，而是直接在服务器进行按行累加。
count(主键)：遍历表中的所有数据，取出主键 id 值返回给服务层，服务层拿到结果后，按行累加。
count(field)：字段有 not null 约束的情况下，直接过程和 count(主键) 一致；如果没有，那么在服务层还需要判断行字段数据是否为 null ，不为 null 才会累加计数。
count(1)：遍历整张表，但不取值。服务层对于返回的每一行都会放一个数字 1，然后按行累加。

update优化

InnoDB 引擎在更新数据时，默认使用行锁保证数据的一致性。但是，如果 update 语句的更新条件字段没有索引，那么 InnoDB 引擎会将行级锁升级为表锁，锁住整张表。在执行 update 语句时，要使用有索引的字段做为更新条件。

视图

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

基本操作

创建视图

# 创建名为 user_v_1 的视图，使用tb_user表中age <= 20的数据id和name列作为视图数据
# or replace 为可选项，如果存在则表示创建视图或替换视图
create [or replace] view user_v_1 as select id, name from tb_user where age <= 20;

查询视图

# <view_name> 表示具体的视图名称
# 查看创建视图语句
show create view <view_name>;

# 查询视图数据
select * from <view_name>;

# 查询表的操作也适用于查询视图
select * from user_v_1 where length(name) >= 2 order by id desc limit 0, 5;

修改视图

修改视图可以直接创建视图的 create view 语句，但是必须添加 or replace 参数，或者使用 alter 语句直接修改已经存在的视图。

# 直接修改已经存在的视图
alter view <view_name> as <select...>

删除视图

drop view <view_name>

视图中的数据可以进行插入、修改和删除操作，由于视图不直接存储数据，其操作的本质是向视图所关联的表中进行数据的插入、修改和删除。

检查选项

当使用了 with check option 创建视图时，MySQL 会通过视图检查正在更改的每个行，使其符合视图的定义。向视图中插入数据时，默认情况下所插入的数据不符合视图创建时的条件也可以成功插入，如果想要只允许符合条件的数据插入视图，可以在视图创建时添加 with cascaded check option 子句。

# 插入数据的id不符合视图的条件 默认情况下可以插入
insert into user_v_1 values (31, '乔峰');

# 添加 with cascaded check option 选项后，只能插入符合条件的数据
create view user_v_1 as select id, name from tb_user where id < 30 with cascaded check option;

MySQL 允许基于视图创建视图，会检查依赖视图中的规则以确保一致性，对于其检查的范围，MySQL 提供了两个选项：

cascade：默认，当嵌套创建视图时，cascade 选项会让 MySQL 检查当前操作是否符合当前视图和其关联的上级视图条件（无论上级视图的条件有没有 with check option 选项都会检查）。
local：检查当前视图的条件，对于上级视图，如果存在检查选项就检查，不存在则不检查。

# 建立视图1
create view stu_v_1 as select id, name from tb_student where id < 20;  
# 基于视图1建立视图2
create view stu_v_2 as select id, name from stu_v_1 where id > 10 with cascaded check option ;

# 向视图二中插入数据会失败
# cascaded 会让MySQL同时检查视图1和其所有上级视图的条件
insert into stu_v_2 values (21, '张三丰');

# 创建视图3 向视图3插入数据时，由于其上级视图存在 with cascaded 所以会检查视图2和视图1的条件
create view stu_v_3 as select id, name from stu_v_1 where id > 15;

# 创建视图4
create view stu_v_4 as select id，name from stu_v_1 where id > 10 with local check option;

# stu_v_4 条件符合，由于使用local检查选项其上级stu_v_1视图不存在检查选项，那么不会检查 stu_v_1的条件 插入成功
insert into stu_v_4 values (22, '周芷若');

更新和作用

要使视图可以更新，视图中的行与基础表中的行必须是一对一的关系。如果在创建视图时包含以下任何一项情况，则视图不可更新。

聚合函数或窗口函数
DISTINCT
GROUP BY
HAVING
UNION 或 UNION ALL

视图的一般有以下几个作用：

简化查询操作，将复杂的查询语句定义为视图，后序只需查询视图即可
授权控制，通过视图可以控制用户所能查询和修改的数据
数据独立，可以通过视图减小表结构变化带来的影响

存储过程

存储过程是事先通过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化开发人员的工作，减少数据在数据库和服务器之间的流转，可以有限的提高数据处理效率。可以将存储过程看作为数据库对 SQL 代码的封装和重用。

基本操作

创建存储过程

create procedure <存储过程名称>([参数列表...])  
begin  
	<需要调用的sql语句>
end;

# 建立一个无参的存储过程
create procedure user_p1()  
begin  
    select count(*) from tb_user;  
end;

调用存储过程

call <存储过程名称> ([参数列表]);

# 调用创建的存储过程
call user_p1();

查看存储过程

# 查询指定数据库的存储过程和状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = <database_name>;

# 查询某个存储过程的定义
show create procedure <procedure_name>;

删除存储过程

drop procedure <procedure_name>;

语法

变量

MySQL 中变量可以分为三类，分别是：

系统变量：MySQL 服务器提供的，属于服务器层面。分为全局变量 GLOBAL 和会话变量 SESSION，MySQL 在重启之后，所设置的全局参数会重置为默认值。
用户自定义变量：根据需求自己定义的变量，不用提前声明，直接使用 @变量名 使用即可。其作用范围为当前会话 session。用户自定义变量不需要声明或初始化，获取到的默认值为 NULL。
局部变量：定义的局部生效的变量，访问之前，需要使用 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的作用域是在其内声明的 BEGIN - END 块中。

# 查看所有系统变量 session | global
show session variables;

# 模糊匹配查看系统变量
show session variables like '';

# 查看单个系统变量 global或session变量都可以查询
select @@<global|session>.<variables_name>

# 设置系统变量 session | global
set session <variables_name> = <value>;
set @@<global|session>.<variables_name> = <value>;


# 设置用户自定义变量，set指令可以同时设置多个变量
set @<name> = <value>, @<name> = <value>; 
set @<name> := <value>;  

# 将查询到的字段赋值给变量
select name into @<name> from tb_student where id = 1;

# 使用用户自定义变量
select @<name1>, @<name2>;


# 声明局部变量 type是数据库内的数据类型 default是可选的，为变量赋一个默认值
declare <name> <type> [default] <value>;
declare stu_count int default 0;

# 局部变量赋值 和赋值用户自定义变量一致 只是无须@符
set <name> = <value>;
set <name> := <value>;
select count(*) into stu_count from tb_student;

# 局部变量只能在 begin end 块内使用
create procedure stu_p2()  
begin  
    declare stu_count int default 0;  
    select count(*) into stu_count from tb_student;  
    select stu_count;  
end;

判断

MySQL 存储过程中用于流程控制的关键字有 if 和 case ，if 和主流编程语言中的 if 使用一致。

if <条件> then
	<sql语句...>
elseif <条件> then
	<sql语句>
else
	<sql语句>
end if;

case 则和 SQL 语句中的 CASE() 函数使用一致

# 语法一 和switch类型逻辑
case <条件变量>
	when <条件值> then <sql语句>
	when <条件值> then <sql语句>
	else <sql语句>
end case;

# 语法二
case 
	when <条件> then <sql语句>
	when <条件> then <sql语句>
	else <sql语句>
end case;

以一个判断成绩等级的小功能为例，分别使用 if 和 case 实现。

create procedure stu_p2()
begin
    declare score int default 0;
    declare result varchar(3) default '';
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
end;

create procedure stu_p2()  
begin  
    declare score int default 0;  
    declare result varchar(3) default '';  
    case  
        when score >= 85 then set result = '优秀';  
        when score >= 60 then set result = '及格';  
        else set result = '不及格';  
    end case;  
end;

参数

存储过程中的参数可以分为三类：

IN：输入参数，调用存储过程时需要传入的值，不指定参数类别时，默认为 IN
OUT：输出参数，可以作为存储过程的返回值
INOUT：可以作为输入参数或输出参数

# 创建需要两个参数的存储过程
create procedure stu_p2(in score int, out result varchar(3))  
begin  
    if score >= 85 then
        set result := '优秀';  
    elseif score >= 60 then  
        set result = '及格';  
    else  
        set result = '不及格';  
    end if;  
end;

# 使用自定义变量接收存储过程的返回参数
call stu_p2(90, @result_level); 
# 输出
select @result_level;


# inout 参数可以使用也可以返回
create procedure stu_p3(inout score double)  
begin  
    set score = score >> 1;  
end;  

set @score = 180;  
call stu_p3(@score);
# 存储过程会直接修改score变量的值，这里查询出来是处理后的结果
select @score;

循环

while 循环，满足条件时进行循环

while <循环条件> do 
	<sql语句>
end while;

repeat 循环和 while 循环相反，在满足条件时退出循环

repeat
	<sql语句>
until <循环条件> end repeat;

loop 循环，可以看作是简单的死循环，没有循环条件，需要配合 LEAVE 和 ITERATE 指定使用

LEAVE：跳出循环，类似于 break
ITERATE：进行下一次缓存，类似于 continue

<loop_label>:loop
	<sql语句>
end loop <loop_label>;

一个简单的累加和存储过程，分别使用三种循环实现

# while循环
create procedure stu_p2(in num int, out sum int)  
begin  
    declare i int default 1;  
    set sum := 0;  
    while i <= num do  
        set sum := sum + i;  
        set i := i + 1;  
    end while;  
end;  

# repeat循环
create procedure stu_p3(in num int, out sum int)  
begin  
    declare i int default 0;  
    set sum := 0;  
    repeat  
        set sum := sum + i;  
        set i := i + 1;  
    until i > num end repeat;  
end;

# loop循环
create procedure stu_p4(in num int, out sum int)  
begin  
    declare i int default 1;  
    set sum := 0;  
    total:loop  
        if i > num then  
            leave total;  
        end if;  
        set sum = sum + i;  
        set i = i + 1;  
    end loop total;  
end;

游标和条件处理

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

# 声明游标
declare <cursor_name> cursor for <sql查询语句>;

# 打开游标
open <cursor_name>;

# 获取游标记录
fetch <cursor_name> into <变量>...;

# 关闭游标
close <cursor_name>;

在循环处理游标中的数据时，没有条件可以判断当前游标中是否还存在数据，换句话说就是游标中的数据处理完成后，循环还会继续执行。但是由于数据已经被处理完，在 fetch 游标时会抛出错误。为了能控制循环的结束，需要借助条件处理程序来处理流程控制结构在执行遇到问题时的解决方法。其语法为 declare <handler_action> handler for <condition_value> <sql语句>;，其中 handler_action 可能的值为：

CONTINUE：继续执行当前程序
EXIT：终止执行当前程序 condition_value 可能的值为：
SQLSTATE <sqlstate_value>：状态码，如 02000
SQLWARNING：所有以 01 开头的状态码简写
NOT FOUND：所有以 02 开头的状态码简写
SQLEXCEPTION：除 SQLWARNING 和 NOT FOUND 外所有状态码的简写

create procedure user_p1()  
begin  
    declare uname varchar(64);  
    declare ucity varchar(24);  
    # 创建游标  
    declare user_cursor cursor for select name, city from tb_user where age <= 50;  
  
    # 从空游标中获取数据会返回 02000 状态码  
    # 使用条件处理程序直接关闭游标并退出  
    declare exit handler for sqlstate '02000' close user_cursor;  
  
    drop table if exists tb_user_city;  
  
    create table tb_user_city (  
        id int auto_increment primary key ,  
        name varchar(24) not null ,  
        city varchar(24) not null  
    );  
  
    # 打开游标  
    open user_cursor;  
    while true do  
        # 获取游标中的数据并添加到临时表中  
        fetch user_cursor into uname, ucity;  
        insert into tb_user_city values (null, uname, ucity);  
    end while;  
  
    close user_cursor;  
end;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的，语法如下：

# 创建存储函数
create function <func_name>([args])
# 返回数据
returns <type> [characteristic]
begin  
    <sql语句>  
    return ...;  
end;

characteristic 特性值说明：

deterministic：相同的输入参数返回结果相同
no sql：不包含 SQL 语句
read sql data：包含读取数据的语句，但不包含写入数据的语句

# 创建一个计算累加和的存储函数
create function add_sum(num int)  
returns int deterministic  
begin  
    declare sum int default 0;  
    declare i int default 1;  
    while i <= num do  
        set sum = sum + i;  
        set i = i + 1;  
    end while;  
    return sum;  
end;  

# 调用存储函数
select add_sum(10);

触发器

触发器是与表有关的数据库对象，作用是在增、删、改之前或之后触发并执行特定的 SQL 语句。触发器的特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。在 MySQL 中使用别名 OLD 和 NEW 引用触发器中发生变化的数据内容，目前 MySQL 中的触发器只支持行级触发，不支持语句级触发。

类型	说明
`INSERT` 触发器	`NEW` 表示即将或者已经新增的数据
`UPDATE` 触发器	`OLD` 表示修改前的数据，`NEW` 表示即将修改或修改完成的数据
`DELETE` 触发器	`OLD` 表示即将或已经删除的数据
触发器的基本语法：

# 创建触发器
# before 表示在操作之前触发 after表示在操作成功后触发
# insert | update | delete 分别对应触发器的类型
# for each row 表示行级触发器
create trigger name  
    <before|after> <insert|update|delete>
    on <table_name>  
    for each row  
begin  
  <sql语句>
end;

# 查看触发器
show triggers;

# 删除触发器
drop trigger <trigger_name>;

创建一张日志表，将 tb_user 表的数据变更记录添加到 tb_user_logs 日志表中

create table tb_user_logs (  
    id int not null auto_increment primary key ,  
    operation varchar(24) not null comment '操作类型',  
    operation_time datetime not null comment '操作时间',  
    operation_id int not null comment '操作的记录id',  
    operation_params varchar(512) comment '操作参数'  
) comment '用户操作记录表';

创建 INSERT 触发器

# 创建一个insert类型的触发器，在数据成功插入后触发
create trigger tb_user_insert_trigger  
    after insert  
    on tb_user  
    for each row  
begin
	# 使用NEW可以拿到插入的行数据
    insert into tb_user_logs value (null, 'INSERT', now(), NEW.id,  
                                    concat('id:', NEW.id, ',name:', NEW.name, ',phone:', new.phone));  
end;

创建 UPDATE 触发器

# update触发器，在更新之后触发
create trigger tb_user_update_trigger  
    after update  
    on tb_user  
    for each row  
begin
	# 记录更新前和更新后的参数
    insert into tb_user_logs value (null, 'UPDATE', now(), NEW.id,  
                                    concat('old name:' OLD.name, 'NEW name:', NEW.name));  
end;

创建 DELETE 触发器

# delete触发器 在删除记录后触发
create trigger tb_user_delete_trigger  
    after delete  
    on tb_user  
    for each row  
begin  
	# 删除只有OLD可用
    insert into tb_user_logs value (null, 'DELETE', now(), OLD.id,  
                                    concat('name:' OLD.name, ' phone:', OLD.phone));  
end;

锁

在 MySQL 中，锁用于保证数据并发访问的一致性和有效性，同时也是数据库并发访问性能的一个重要因素。按照锁的粒度，MySQL 中的锁可以分为三类：

全局锁：锁定数据库中的所有表
表级锁：每次操作锁定整张表
行级锁：每次操作锁定对应的行数据

全局锁

全局锁是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的 DML 写语句和 DDL 语句、有更新操作的事务提交语句都会被阻塞。典型使用场景是作全库的数据备份，获取一致性视图，确保备份数据的完整性。

# 开启全局锁
flush tables with read lock;  

# 释放全局锁
unlock tables;

对 MySQL 加全局锁可能会造成以下问题：

如果在主库上进行备份，那么会造成加锁期间除查询外的所有操作都会阻塞，线上业务基本不可用。
如果对从库进行加锁，在加速期间不能执行从主库同步过来的日志，会导致主从延迟。如果只是单纯的需要进行数据备份，在 InnoDB 引擎中，可以在备份时加上参数 --single-transaction 实现不加锁的一致性数据备份。

表级锁

表级锁加锁时，会锁住整张表。锁的粒度大，发生锁冲突的概率最高，同时并发度最低。可以使用在 MyISAM 、InnoDB、DBD 等存储引擎中。表级锁主要分为三类：

表锁
元数据锁（MDL）
意向锁

表锁

表锁分为两类，分别是表共享读锁（read lock）和表独占写锁（write lock），其加锁和释放锁的语法都相同。如果对表加了读锁，那么所有客户端都只能对加锁的表进行查询操作；如果是加了写锁，那么加锁的客户端可以对表进行查询和修改操作，其余客户端的所有操作都会阻塞（包括查询操作）。

# 对表加锁 read表示读锁 write表示写锁
lock tables <table_name> <read|write>;

# 释放锁，当客户端断开连接也会释放锁
unlock tables;

元数据锁

元数据锁 MDL 加锁过程是系统自动控制的，无需显式使用，在访问一张表时 MySQL 会自动加锁。 MDL 锁的主要作用是维护表元数据的一致性，在表上有活动事务时，不可以对元数据进行写入操作。当对一张表进行增删改查时，会对表加 MDL 读锁；当对表结构进行变更操作时，加 MDL 写锁（排它锁）。

SQL	锁类型	说明
`lock tables <table_name> <read\|write>`	`SHARED_READ_ONLY` / `SHARED_NO_READ_WRITE`
`select`	`SHARED_READ` （读锁）	和 `READ`、`WRITE` 兼容，和 `EXCLUSIVE` 互斥
`update` 、`insert`、`delete`	`SHARED_WRITE`（读锁）	同上
`alter table`	`EXCLUSIVE` （写锁）	和其它 `MDL` 互斥

# 查看元数据锁
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION from performance_schema.metadata_locks;

意向锁

为了避免 MDL 在执行时，行锁和表锁冲突，InnoDB 引入了意向锁，使得在加表锁时不用去检查表中的每一行数据是否加了行锁，意向锁用来减少表锁对于行锁的检查，其有两种类型：

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

当线程 A 开启事务更新表 tb_user 内某行数据时，MySQL 会自动为当前数据行添加行锁并开启一个意向锁，意向锁的类型由开启行锁的 SQL 语句来决定。此时，如果线程 B 相对 tb_user 表加表锁，MySQL 会先判断要加的表锁类型和意向锁是否兼容，如果兼容那么会直接开启表锁，如果不兼容，线程 B 的加锁操作会阻塞，直到线程 A 的行锁和意向锁释放。

# 查看意向锁和行锁的加锁情况
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA from performance_schema.data_locks;

行级锁

行级锁每次操作只会锁住对应的数据行，锁的粒度最小，发生锁冲突的概率最低，并发度也是最高的，但是只在 InnoDB 引擎中可用。在 InnoDB 引擎中，行锁是通过对索引上的索引项加锁来实现的。对于行级锁，主要分为以下三类：

行锁 Record Lock：锁定单个行记录的锁，防止其它事务对此进行 update 和 delete 操作。在 RC 和 RR 隔离级别下都支持。
间隙锁 Gap Lock：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其它事务在这个间隙中进行 insert 产生幻读，在 RR 隔离级别下支持。
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据和数据前面的间隙 Gap，在 RR 隔离级别下支持。

行锁

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

共享锁 S：允许一个事务去读一行（共享锁之间兼容），阻止其它事务获得相同数据集的排它锁（和排它锁互斥）。
排它锁 X：允许获取排它锁的事务更新数据，阻止其它事务获得相同数据集的排它锁和共享锁（和共享锁与排它锁都互斥）。

`SQL`	行锁类型	说明
`insert、update、delete`	排它锁	自动加锁
`select`	不加锁
`select ... lock in share mode`	共享锁	手动加锁
`select ... for update`	排它锁	手动加锁
默认情况下，`InnoDB` 在 `RR` 事务隔离级别运行，使用 `next-key` 进行搜索和索引扫描，以防止幻读。

针对使用唯一索引进行检索或对已存在的记录进行等值匹配时，自动优化为行锁
InnoDB 的行锁是针对于索引加锁，如果条件查找数据不使用索引，那么 InnoDB 将对表中的所有记录加锁，此时会升级为表锁
索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
索引上的等值查询（普通索引），向右遍历直到最后一个值不满足查询需求时，next-key lock 退化为间隙锁
索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止

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

InnoDB引擎

逻辑存储结构

MySQL 中的逻辑存储结构从大到小分为 5 级：

表空间（ibd文件）：一个 MySQL 实例可以对应多个表空间，用于存储记录、索引等数据。
段：分为数据段 Leaf node segment、索引段 Non-leaf node segment、回滚段 Rollback segment，数据段就是 B+ 数的叶子节点，索引段为 B+ 树中的非叶子节点。段用来管理多个区 Extent
区：表空间的单元结构，每个区的大小为 1M，默认情况下一个区中有 64 个连续的页
页：InnoDB 存储引擎磁盘管理的最小单元，默认大小为 16KB。为了保证页的连续性，InnoDB 存储引擎每次向磁盘申请 4-5 个区
行：InnoDB 存储引擎中数据是按行进行存放的

架构

内存架构：

Buffer Pool（缓冲池）：是主存中的一个区域，用于缓存磁盘上经常操作的真实数据。在执行操作时，先操作缓冲池中的数据（若缓存没有命中，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少大量的磁盘 IO。缓冲池以 Page 页为单位，底层使用链表数据结构管理 Page 。根据状态，将 Page 分为三种状态：
- free page：空闲页，未被使用
- clean page：被使用页，数据没有被修改过
- dirty page：脏页，被使用的页，数据被修改过，页中数据和磁盘数据产生了不一致
Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行 DML 语句时，如果这些数据 Page 没有在 Buffer Pool 中，不会直接操作磁盘，而是将数据变更存在更改缓冲区中，在未来数据被读取时，再将数据合并恢复到 Buffer Pool 中，然后再将合并的数据刷新到磁盘。
Adaptive hash index（自适应哈希索引）：用于优化对 Buffer Pool 的查询速度。InnoDB 会监控表上各索引页的查询，如果观察到哈希索引可以提升速度，则建立哈希索引，所以称之为自适应哈希索引。可以使用参数 adaptive-hash-index 控制是否开启子使用哈希索引。
Log Buffer（日志缓冲区）：保存要写入到磁盘中的日志数据（redo log、undo log），默认大小为 16MB，缓冲区中的日志会定时刷新到磁盘中。可以使用参数 innodb_log_buffer_size（缓冲区大小）和 innodb_flush_at_trx_commit（刷新到磁盘时机，0：每次事务提交时刷新；1：每秒刷新；2：同时开启 0 和 1）控制缓冲区日志刷新到磁盘的频率。磁盘架构：
System Tablespace：系统表空间是 Change Buffer 的存储区域
File-Per-Table Tablespace：每个表的文件表空间包含单个 InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中
General Tablespace：通用表空间，需要通过 create tablespace 创建通用表空间。在创建表时，可以指定是否使用该表空间
Undo Tablespace：撤销表空间，初始化时会自动创建两个默认的 undo 表空间（初始大小为 16MB），用于存储 undo log 日志
Temporary Tablespace：存储用户创建的临时表等数据，会使用会话临时表空间和全局临时表空间
Doubleweite Buffer Files：双写缓冲区，将数据页从 Buffer Pool 刷新到磁盘前，先将数据页写入双写缓冲区中，便于系统异常时恢复数据
Redo Log：重做日志，用来实现事务的持久性。由两部分组成，重做日志缓冲 redo log buffer 和重做日志文件 redo log，分别保存在内存和磁盘中。当事务提交后会把所有修改信息都存到该日志，用于刷新脏页到磁盘发生错误时进行数据恢复

后台线程

Master Thread：核心后台线程，负责调动其它线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性。此外还包括脏页的刷新、合并插入缓存、undo 页的回收。

IO Thread：InnoDB 引擎大量使用了 AIO 来处理 IO 请求，IO Thread 主要负责处理这些 IO 请求的回调。

线程类型	数量	说明
`Read Thread`	4	负责读操作
`Write Thread`	4	负责写操作
`Log Thread`	1	负责刷新日志缓冲区
`Insert buffer thread`	1	负责刷新写缓冲区内容
`Purge Thread`：用于回收事务已经提交后的 `undo log`。

Page Cleaner Thread：协助 Master Thread 刷新脏页到磁盘的线程，用于减轻主线程的压力，减少阻塞。

事务原理

事务代表一组操作的集合，是一个不可分割的工作单位，会把所有操作作为一个整体向系统提交或撤销操作请求，所有操作要么同时成功，要么同时失败。事务的四大特性，简称 ACID：

原子性 Atomicity ：事务是不可分割的最小操作单元
一致性 Consistency：事务完成时，所有的数据都必须保持一致状态
隔离性 Isolation：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性 Durability：事务一旦提交或回滚，对数据的改变就是永久的其中原子性、一致性和持久性是通过 redo log 和 undo log 实现的，而持久性则是通过锁和 MVCC 实现。

redo log

详见 磁盘架构 -> redo log

undo log

回滚日志，用于记录数据被修改前的信息，主要作用包含两个：回滚和 MVCC。 undo log 是逻辑日志，当对数据库直接增删改操作时，在 undo log 会记录一条相反操作的记录（比如新增数据就会记录一条删除新增数据的日志）。当执行 rollback 时，直接从 undo log 中读取到对应的逻辑内容回滚即可。 undo log 在事务提交时产生，事务提交时，undo log 并不会立即被删除，因为这些日志可能还用于 MVCC。当 insert 时产生的日志在事务提交后可以被立即删除；而 update、delete 时，因为快照读需要，不会被立即删除。 undo log 采用段的方式进行管理和记录，存放在磁盘架构中的 rollback segment 段中，内部包含 1024 个 undo log segment。当不同事务或相同事务对同一条记录进行修改时，会导致该记录的 undo log 生成一条记录版本链表，链表的头部是最新的旧记录，尾部是最早的旧记录。

MVCC

MVCC 是 InnoDB 引擎中的多版本并发控制系统，其中的一些概念：

当前读：读取的是记录的最新版本，读取时还要保证其它并发事务不能修改当前记录，会对读取的记录进行加锁。select ... lock in share mode、select ... for update、update、insert、delete 都是一种当前读。
快照读：简单的 select 就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁、非阻塞。对于不同的隔离级别，有不同的实现：
- Read Committed：每次 select 都生成一个快照读
- Repeatable Read：开启事务后第一个 select 是快照读的数据
- Serializable：快照读会退化为当前读
MVCC：维护一个数据的多个版本，使得读写操作没有冲突，快照读为 MySQL 实现 MVCC 提供了一个非阻塞读功能，其具体实现还依赖数据记录中的三个隐式字段、undo log 日志、readView。

隐藏字段

名称	说明
`DB_TRX_ID`	最近修改事务 `ID`，记录插入这条记录或者最后一次修改该记录的事务 `ID`
`DB_ROLL_PTR`	回滚指针，指向这条记录的上一个版本，用于配合 `undo log` 记录链，可以追溯到最旧的版本记录
`DB_ROW_ID`	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

readView

ReadView 读视图是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交）ID。ReadView 包含 4 个核心字段：

m_ids：当前活跃的事务 ID 集合
min_trx_id：最小获取事务 ID
max_trx_id：预分配事务 ID，因为事务 ID 是自增的，所以是当最大事务 ID + 1
creator_trx_id：ReadView 创建者的事务 ID undo log 版本链的数据访问规则（trx_id 代表当前事务 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 中，那么可以访问，数据已经提交对于不同的隔离级别，生成 ReadView 的时机不同：
READ COMMITTED：在事务中每一次执行快照读时生成 ReadView
REPEATABLE READ：在事务中第一次执行快照读时生成 ReadView，后续继续复用

MySQL管理

MySQL 安装完成后，其自带的 4 个数据库作用如下：

数据库	说明
`mysql`	存储 `MySQL` 服务器正常运行所需要的各种信息
`information_schema`	提供访问数据库元数据的各种表和视图，包含数据库、表、字段类型和访问权限等
`performance_schema`	为 `MySQL` 提供一个底层监控功能，用于收集数据库服务器性能参数
`sys`	包含了利用 `performance_schema` 进行性能调优和诊断的视图

本文使用 CC BY-NC-SA 4.0 许可协议，转载请注明出处

MySQL高级特性

MySQL 的体系结构

存储引擎

存储引擎特点

存储引擎选择

索引

索引结构

B+tree

Hash

索引分类

索引语法

SQL性能分析

执行频次

慢查询日志

profile详情

explain执行计划

索引使用

最左前缀法则

索引失效

SQL提示

覆盖索引

前缀索引

单列索引和联合索引

索引设计

SQL优化

插入数据

insert优化

大批量插入数据

主键优化

order by优化

group by优化

limit优化

count优化

update优化

视图

基本操作

检查选项

更新和作用

存储过程

基本操作

语法

变量

判断

参数

循环

游标和条件处理

存储函数

触发器

锁

全局锁

表级锁

表锁

元数据锁

意向锁

行级锁

行锁

InnoDB引擎

逻辑存储结构

架构

后台线程

事务原理

redo log

undo log

MVCC

隐藏字段

readView

MySQL管理

关联文章

MySQL基础入门

在Linux上使用Docker实现MySQL读写分离

0 条评论