MySQL 数据库 入门到精通 纯手敲笔记（进阶篇）

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括

https://www.bilibili.com/video/BV1Kr4y1i7ru

进阶篇

  • 存储引擎
  • 索引
  • SQL优化
  • 视图/存储过程/触发器
  • 锁
  • InnoDB引擎
  • MySQL管理

存储引擎

MySQL体系结构

连接层、服务层、引擎层、存储层

image

SHOW ENGINES;

CREATE TABLE XXX(...) ENGINE=INNODB;

存储引擎特点

InnoDB

介绍：InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。

特点

  • DML操作遵循ACID模型，支持事务。（原子性、一致性、隔离性、持久性）
  • 行级锁，提高并发访问性能。
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table

image

MyISAM

介绍：MyISAM是MySQL早期的默认存储引擎。

特点

不支持事务，不支持外键

支持表锁，不支持行锁

访问速度快

文件

xxx.sdi：存储表结构信息

xxx.MYD：存储数据

xxx.MYI：存储索引

Memory

介绍：Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

  • 内存存放

  • hash索引(默认)

文件

xxx.sdi:存储表结构信息

image

InnoDB相对MyISAM的优势：支持事务、行级锁、外键。

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：是 Mysql 的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
  • MyISAM： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。（现在大多被MongoDB等取代）
  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY 的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（现在大多被Redis取代）

索引

索引概述

优缺点

image

索引结构

MYSQL的索引是在存储引擎层实现的。

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

image

B-Tree（多路平衡查找树）

image

image

B+Tree

所有的元素都会出现在叶子节点。叶子结点形成一个单向链表。非叶子节点起到索引作用，不存储数据。

MySQL索引数据结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

一个节点就是一页，16k。

image

Hash

采用hash算法，把键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

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

image

Hash 索引特点
  1. Hash 索引只能用于对等比较 (=，in)，不支持范围查询（between，>，<，…）
  2. 无法利用索引完成排序操作
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+tree 索引
存储引擎支持

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

  • 为什么InnoDB存储引擎选择使用B+tree索引结构？

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

索引分类

image

image

聚集索引选取规则：

如果存在主键，逐渐索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引的叶子结点下面挂的是行数据。

二级索引的叶子结点下面挂的是id。

回表索引：先到二级索引拿到主键值，再到聚集索引拿到对应的行数据。

image

索引语法

  • 创建索引

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

  • 查看索引

SHOW INDEX FROM table_name;

  • 删除索引

DROP INDEX index_name ON table_name;

按照下列的需求，完成索引的创建

  1. name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
  2. phone 手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
  3. 为 profession、age、status 创建联合索引。
  4. 为 email 建立合适的索引来提升查询效率。
create index idx_user_name on tb_user(name);

-- show index from tb_user;
create unique index idx_user_phone on tb_user(phone);

create index idx_user_pro_age_sta on tb_user(profession,age,status);

create index idx_user_email on tb_user(email);

-- drop index idx_user_email on tb_user;

SQL性能分析

SQL 执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次：

SHOW GLOBAL STATUS LIKE 'Com_______';（7个下划线）

慢查询日志

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

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

# 开启 MySQL 慢日志查询开关

slow_query_log=1

# 设置慢日志的时间为 2 秒，SQL 语句执行时间超过 2 秒，就会视为慢查询，记录慢查询日志

long_query_time=2

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

profile 详情

执行一系列的业务 SQL 的操作，然后通过如下指令查看指令的执行耗时：

# 查看每一条 SQL 的耗时基本情况
show profiles;

# 查看指定 query_id 的 SQL 语句各个阶段的耗时情况
show profile for query query_id;

# 查看指定 query_id 的 SQL 语句 CPU 的使用情况
show profile cpu for query query_id;
explain 执行计划

EXPLAIN 执行计划各字段含义:

  • Key
    实际使用的索引，如果为 NULL，则没有使用索引。
  • Key_len
    表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好 。
  • rows
    MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
  • filtered
    表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
  • 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
    显示可能应用在这张表上的索引，一个或多个。 ）

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

总结：查询必须包含最左列，否则索引全部失效，如果跳过某一列，则索引部分失效，该列之前的仍然走索引。

  • 范围查询

联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效

explain select * from tb_user where profession = '软件工程' and age>30 and status = '0';
  • 索引列运算

在索引列上运算，索引失效。

select * from tb_user where substring(phone,10,2)='15';
  • 字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

select * from tb_user where phone = '17799990015';
# 注：这里表中的phone字段是varchar类型
  • 模糊查询

尾部模糊匹配，索引不会失效。头部模糊匹配，索引失效。头尾都模糊，也失效。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';
  • or连接的条件

用or隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会用到。

  • 数据评估影响

如果MySQL评估使用索引比全表扫描更慢，则不使用索引。

  • SQL提示

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

# use index : (建议使用索引)
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
# ignore index : (忽略使用索引)
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
# force index : (强制使用索引)
explain select * from tb_userforce index(idx_user_pro) where profession = '软件工程';
  • 覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

使用select *极易出现回表查询，导致性能降低。

一张表，有四个字段 (id,username,password,status)，由于数据量大，需要对以下 SQL 语句进行优化，该如何进行才是最优方案？

select id,username,password from tb_user where username = 'itcast';

简历username和password的联合索引，二级索引挂着id，不需要回表查询，性能提高。

  • 前缀索引

当字段类型为字符串（varchar，text 等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘 IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：create index idx_xxxx on table_name(column(n));

前缀长度可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from tb_user ;

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

  • 单列索引与联合索引

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

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

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

联合索引情况：

create unique index idx_phone_name on tb_user(phone,name);

image

尽量使用联合索引而非单列索引。

索引设计原则

  1. 针对于数据量较大，且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
  7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

SQL优化

插入数据

insert优化
  • 批量插入

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

  • 手动提交事务

start transaction;

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');

insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');

commit;

  • 主键顺序插入

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

大批量插入数据

使用load指令。

  • 客户端连接配置

客户端连接服务器时，需添加 --local-infile 参数，命令为：
mysql --local-infile -u root -p

  • 全局参数设置

设置全局参数 local_infile 为 1，开启本地文件导入数据功能，SQL 语句：

set global local_infile = 1;

  • 数据加载操作

执行 LOAD DATA 指令，将本地文件（路径 /root/sql1.log ）数据加载到表 tb_user ，按逗号分隔字段、换行符分隔行，命令：

load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式

在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。

页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除井且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD (默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

MERGE THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

  • 满足业务需求的情况下，尽量降低主键的长度。

  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。

  • 业务操作时，避免对主键的修改。

order by优化

  • Using filesort

通过表的索引或全表扫描，读取满足条件的命令行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回结果的排序都叫FileSort排序。

  • Using index

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

无索引时的排序查询（用于对比分析）

执行 EXPLAIN 查看执行计划，依据 age、phone 排序查询语句：

explain select id,age,phone from tb_user order by age , phone;

索引创建操作

为 tb_user 表的 age、phone 字段创建联合索引，索引名 idx_user_age_phone_aa ：

create index idx_user_age_phone_aa on tb_user(age,phone);

有索引时的升序排序查询（验证索引对升序排序的优化）

创建索引后，再次用 EXPLAIN 查看执行计划，按 age、phone 升序排序查询：

explain select id,age,phone from tb_user order by age , phone;

有索引时的降序排序查询（验证索引对降序排序的影响）

创建索引后，用 EXPLAIN 查看执行计划，按 age、phone 降序排序查询：

explain select id,age, phone from tb_user order by age desc, phone desc;

总结

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

  • 尽量使用覆盖索引。

  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)。

group by优化

索引删除

删除 tb_user 表上的联合索引 idx_user_pro_age_sta ：
drop index idx_user_pro_age_sta on tb_user;

无索引时的分组查询（分组字段：profession ）

执行 EXPLAIN 查看执行计划，按 profession 字段分组统计的查询：
explain select profession , count(*) from tb_user group by profession ;

索引创建

为 tb_user 表创建联合索引 idx_user_pro_age_sta ，涉及字段 profession、age、status ：
Create index idx_user_pro_age_sta on tb_user(profession , age , status);

有索引时的分组查询（分组字段：profession ）

创建索引后，再次用 EXPLAIN 查看执行计划，按 profession 字段分组统计的查询：
explain select profession , count(*) from tb_user group by profession ;

有索引时的多字段分组查询（分组字段：profession、age ）

创建索引后，用 EXPLAIN 查看执行计划，按 profession、age 多字段分组统计的查询：
explain select profession , count(*) from tb_user group by profession, age;

总结

在分组操作时，可以通过索引来提高效率。

分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

一个常见又非常头疼的问题就是 limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

count优化

explain select count(*) from tb_user;

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高;

InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

优化思路：自己计数。

count的几种用法
  • count (主键)

InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。

  • count (字段)

没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null 约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

  • count (1)

InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

  • count (*)

InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段)<count(主键id)<count(1) = count(")，所以尽量使用 count(*)。

update优化

update student set no = '2000100100' where id = 1 ;

update student set no = '2000100105' where name = ' 韦一笑 ';

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图/存储过程/触发器

锁

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

flush tables with read lock;

mysqldump -uroot -p1234 itcast>itcast.sql;

unlock tables;

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

1.如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

2.如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志(binlog)，会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot -p123456 itcast > itcast.sql

表级锁

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

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

1.表锁

2.元数据锁(meta data lock, MDL)

3.意向锁

表锁

对于表锁，分为两类：

1.表共享读锁(read lock)

2.表独占写锁(writelock)（排他写锁）

语法：

1.加锁：lock tables 表名... read/write.

2.释放锁：unlock tables /客户端断开连接。

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

在 MySQL5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁 (共享)；当对表结构进行变更操作的时候，加 MDL 写锁 (排他)。

image

查看元数据锁：

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

意向锁

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

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

行级锁

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

InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

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

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

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

image

image

  • 行锁 - 演示
    默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。
  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  2. InnoDB 的行锁是针对于索引加的锁，不通过索引条件检索数据，那么 InnoDB 将对表中的所有记录加锁，此时就会升级为表锁。

可以通过以下 SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
间隙锁&临键锁
  • 间隙锁 / 临键锁 - 演示
    默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读。
  1. 索引上的等值查询 (唯一索引)，给不存在的记录加锁时，优化为间隙锁 。
  2. 索引上的等值查询 (普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
  3. 索引上的范围查询 (唯一索引)-- 会访问到不满足条件的第一个值为止。

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

间隙锁：只有间隙。临键锁：包括行及相邻间隙。

InnoDB引擎

逻辑存储结构

架构

事务原理

redo log =>持久性，保证刷新脏页发生错误时数据恢复

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

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

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。

MVCC

基本概念
  • 当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行枷锁。对于我们日常的操作，如：select ... lock in share mode（共享锁），select ... for update、update、insert、delete（排它锁）都是一种当前读。

  • 快照读

简单的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：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

  • undo log

回滚日志，当insert、update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。

而update、delete的时候，产生的undo log日志不仅在回顾时需要，在快照读时也需要，不会立即被删除。

  • undo log版本链

  • readview

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

ReadView中包含了四个核心字段：

m_ids：当前活跃的事务ID集合

min_trx_id：最小活跃事务ID

max_trx_id：预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）

creator_trx_id：ReadView创建者的事务ID

image

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

READ COMMITTED：事务中每次执行快照读时生成 ReadView。

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

ibd2sdi

MySQL管理

完结撒花！

posted @ 2025-06-30 10:46  ToFuture$  阅读(104)  评论(0)    收藏  举报