事务

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

事务操作

1
2
3
4
5
6
7
8
9
10
11
12
# 方式一：修改事务的提交方式
# 关闭自动提交事务
SET AUTOCOMMIT=FALSE;
 
# 方式二：手动控制事务
# 开启事务
start transaction;

# 提交事务
commit;
# 回滚事务
ROLLBACK;

事务的四大特性

image-20240707163655606

并发事务问题

image-20240707163922407

事务的隔离级别

image-20240707165500254

1
2
3
4
5
# 查看事务的隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务的隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

存储引擎

MySQL的体系结构

image-20240707172243657

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，索引存储引擎也可被称为表类型。

在建表时指定存储引擎

image-20240707181247443

存储引擎特点

InnoDB

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

特点

  • DML操作遵循ACID模型，支持事务。
  • 行级锁，提高性能
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性。

文件

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

InnoDB逻辑存储结构

image-20240709054021933

MyISAM和Memory

MyISAM介绍

  • MyISAM是MySQL早期的存储引擎

特点

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

文件

  • xxx.sdi：存储表结构信息
  • xxx.MYD：存储数据
  • xxx.MYI：存储索引

Memory介绍

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

特点

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

文件

  • xxx.sdi：存储表结构

image-20240707183424391

存储引擎的选择

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

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

索引

索引概述

索引（index）是帮助MySQL高校获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这些数据结构就是索引。

索引的优缺点

image-20240707211809877

了解什么是索引吗？

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

可以提高检索的效率，降低IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

索引的结构

MySQL索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下集中：

image-20240707212047220

存储引擎支持。

image-20240707212109070

二叉树

image-20240707212505718

B-Tree(多路平衡查找树)

image-20240707212845196

B+Tree

image-20240707215119892

image-20240707215313298

MySQL中的B+Tree

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

image-20240707215706535

为什么MySQL要使用B+Tree?

二叉树和红黑树会导致树的层级很高。

  1. B树和B+Tree每一个节点实际上可以理解为是一个文件页，mysql默认给一个文件页分配16k存储（一个区是1M，所以一个区有64个页）。
  2. B树的每个节点都会存储data数据，而B+Tree的话非叶子节点是存储的索引（冗余），不存储data数据，这样每一页文件页能存储的节点就很多，树的高度就可以得到很好的控制，树的高度越高，从磁盘load节点到内存对比的次数就会越多，磁盘I/O是费时。
  3. 所以B+Tree在树高度相同的情况下能够存储更多的索引数据，间接的减少了磁盘的I/O操作，B+Tree的I/O次数会更加稳定一些。
  4. 还有就是从范围查询的角度上来说B+Tree也具备绝对的优势，因为B+Tree在每个相邻的叶子节点之间都有互相指向
  5. B+Tree在全表扫描的情况下也是比较占优势的，因为B+Tree的数据都是存储在非叶子节点的，所以只需要扫描叶子节点就可以拿到全部数据了，B Tree的话就需要从头遍历整颗树。

Hash

image-20240707220128798

索引的分类

image-20240707220753421

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

image-20240707220945883

聚集索引的选取规则

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

image-20240707222156731

索引的语法

  • 创建索引，默认使用ASC进行升序排序，如果两个字段相同，在根据第二个字段进行升序排序。
1
CREATE [UNIQUE | FULLTEXT] INDEX index_name on table_name(index_col_name [ASC|DESC],...);
  • 查看索引
1
SHOW INDEX FROM table_name;
  • 删除索引
1
DROP INDEX index_name ON table_name;

SQL性能分析

  • SQL的执行频率

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

1
SHOW GLOBAL STATUS LIKE "COM_______";

  • 慢查询日志

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

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

1
2
3
4
5
6
7
8
# 查看是否开启了慢查询日志
SHOW variables like "slow_query_log";

# 修改 /etc/my.cnf文件加上下面两条设置
# 开启慢查询日志的开关
slow_query_log=1
# 设置慢查询日志的时间为2s,SQL语句执行时间超过2s,就会视为慢查询，记录慢查询日志
long_query_time=2

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

  • profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费去哪里了。通过have_profiling参数能够看到当前MySQL是否支持profile操作。

1
select @@have_profiling;

默认profiling是关闭的，可以通过set语句在session|global级别开启profile。

1
set profiling=1;

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

1
2
3
4
5
6
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句每个阶段的耗时情况
show profile from query query_id;
# 查看指定query_id的SQL语句CPU使用情况。
show profile cpu from query query_id;
  • explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

1
2
# 直接在select语句之前加上关键字 EXPLAIN 或 DESC
EXPLAIN select 字段列表 from 表 where 条件;

image-20240708173258695

每个字段的含义。

  • 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。

    主键或唯一索引会出现const，

    主键或唯一索引查询eq_ref，只能返回一条数据。

    非唯一索引会出现ref。

    range，范围查询。

    index：索引树扫描。

    all则进行全表扫描。

  • possible_keys

    显示这张表可能用到的索引，一个或多个。

  • key

    实际使用的索引，如果为NULL，则没有使用索引。

  • key_len

    表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

  • rows

    MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

  • filtered

    表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。

  • Extra

    额外的信息。

    image-20240726183223568

索引的使用

联合索引

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。和顺序没有关系。

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

image-20240708190009817

索引失效

范围查询

联合索引中，出现范围查询（>,<）,范围查询右侧的列索引失效。如果条件允许尽量使用>=或<=。

image-20240708190326177

索引列运算

不要在索引列上进行运算操作，索引将失效。

image-20240708190637204

字符串不加引号（类型转换）

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

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部匹配，索引失效。

image-20240708191126339

or的连接条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。一侧有索引，一侧没有索引，整个索引都会失效，只有两侧都有索引，才会生效。

数据分布影响

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

说白了也就是你所要查的数据占所有数据的比例，对于索引而言，比例大了走索引，比例小了走全表。

比如说我查询一个普通索引的数据是否为空，如果空值占比大于50%走索引，小于50%走全表。

SQL 提示

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

  • use index：指定用哪个索引

    image-20240708215642802

  • ignore index：忽略哪个索引

    image-20240708215708461

  • force index：强制用哪个索引

    image-20240708215734441

覆盖索引

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

image-20240708222126746

image-20240708222155275

  • 使用聚集索引

image-20240708223533431

  • 使用覆盖索引（不需要回表）

image-20240708223608536

  • 使用了索引，但是进行了回表查询。

image-20240708223740660

前缀索引

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

1
create index idx_xxxx on table_name(column(n));

前缀长度

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

image-20240708234211496

单列索引和联合索引

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

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

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

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

image-20240709000345181

索引的设计原则

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

总结（面试考）

  1. 针对那些表建立索引？

    数据量大（大于100w），查询频率高。

  2. 针对表中的那些字段建立索引？

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

  3. 针对这些字段该建立什么样的索引？

    • 列唯一，建立唯一索引。
    • 如果能建立联合索引，就尽量建立联合索引，避免单列索引。
    • 如果是字符串长度过长或大文本字段，尽量建立前缀索引。

SQL的优化

插入的优化

  • 批量插入

image-20240709045915127

  • 手动控制事务

image-20240709045933561

  • 主键顺序插入

image-20240709045950162

大批量插入数据

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下:

image-20240709050215150

1
2
3
4
5
6
7
8
# 客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -uroot -p
# 查看
select @@local_infile;
# 设置全局参数local_infile=1,开启从本地加载文件导入数据的开关。
set global local_infile=1
# 执行load指令将准备好的数据，加载到表结构中。
load data local infile '/root/sql.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

主键优化

  • 数据组织方式

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

image-20240709052033373

  • 页分裂

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

image-20240709052622092

image-20240709052638865

image-20240709052654867

image-20240709052725502

  • 页合并

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

image-20240709054303568

image-20240709054339365

image-20240709054350600

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

主键的设计原则

  • 满足业务要求的情况下，尽量降低主键的长度（如果主键长度越大，二级索引存储的id所占长度页越大）。
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
  • 业务操作时，避免对主键的修改。

order by的优化

  1. using filesort ：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
  2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

image-20240709055415029

没走索引时

image-20240709055500159

走索引后

image-20240709055608592

使用法则

  1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
  2. 尽量使用覆盖索引。（避免使用select *）
  3. 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC).
  4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sortbuffer_size(默认256k)。

group by优化

image-20240709061555538

1
2
# 满足最左前缀法则
explain selecct profession,age,count(*) from tb_user where profession='软件工程' group by age;

limit 优化

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

1
2
3
4
5
6
7
8
# 用时0.00秒
select * from tb_sku limit 0,10;
# 用时3秒
select * from tb_sku limit 1000000,10;
# 用时10秒
select * from tb_sku limit 5000000,10;
# 用时18秒
select * from tb_sku limit 9000000,10;

使用覆盖索引加子查询优化。

1
2
# 优化,耗时9秒
select s.* from tb_sku s inner join (select id from tb_sku limit 9000000,10) i on s.id = i.id;

count 优化

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

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

优化思路，自己计数。（例如使用Reids，插入的时候计数+1）

count的几种方式及效率对比

image-20240709063127063

count(*) = count(1) > count(id) > count(字段)

推荐使用count(*)

update 优化

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

在对记录修改时，更新的条件一定要有索引，否则行锁会升级成表锁，而且不能失效，否则也会升级为表锁。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访
问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

  1. 全局锁：锁定数据库表中的所有表。
  2. 表级锁：每次操作锁住整张表。
  3. 行级锁：每次操作锁住对应的行数据。

全局锁

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

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

image-20240709104311022

1
2
3
4
# 加上了全局锁
flush tables with read lock;
# 释放全局锁
unlock tables;

image-20240709104444084

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

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

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

表级锁

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

对于表级锁，主要有以下集中：

  1. 表锁
  2. 元数据锁（meta data lock，MDL）
  3. 意向锁

表锁

对于表锁，分为两类。

1
2
# 加锁：lock tables 表名... read/write。
# 释放锁：unlock table | 客户端连接断开。
  • 表共享读锁（读锁）

一个客户端给表加了读锁后，自己的客户端和别的客户端都不能改，只能读。

image-20240709111456962

  • 表独占写锁（写锁）

一个客户端给表加了写锁后，其他客户端既不能读也不能写，自己客户端不受影响。

image-20240709111640344

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

元数据锁（meta data lock，MDL）

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

在MySQL5.5中引入了MDL，

当对一张表进行增删改查的时候，加MDL读锁(共享)。

当对表结构进行变更操作的时候，加MDL写锁(排他)。

image-20240709112146188

SHARED_READ：共享读锁。

查看元数据锁。

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

image-20240725233735466

意向锁

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

线程A在修改一条记录时，会先把这一行的行锁先加上，然后会对表加上意向锁，并发情况下，线程B此时也进来想加表锁，此时会查看表的意向锁情况判断能不能加锁成功，如果说加的表锁和意向锁是兼容的，加锁成功；如果不兼容，会进入阻塞状态。当线程A把行锁释放，意向锁是否后，线程B才会加锁成功。

image-20240709114146384

image-20240709114204404

  1. 意向共享锁：由语句select ... lock in share mode添加。
  2. 意向排他锁：由inser，update，delete、select ... for update添加。（for update 给查询的语句加锁）

意向锁和表锁的兼容情况：

  1. 意向共享锁（IS）：与表锁共享读锁( read)兼容，与表锁排它写锁（ write)互斥。
  2. 意向排他锁（IX）∶与表锁共享读锁（read）及表排它写锁(write)都互斥。意向锁之间不会互斥。

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

1
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，RR隔离级别下都支持。

image-20240709153553987

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

image-20240709153531439

  1. 临键锁(Next-Key Lock)：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

image-20240709153500667

行锁

lnnoDB实现了以下两种类型的行锁:

  • 共享锁（S）:允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。（共享锁和共享锁兼容，和排他锁互斥。）

  • 排他锁（X)∶允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

    （第一个事务获取了排他锁，第二个事务就不能获取共享锁及排他锁。）

image-20240709164854956

image-20240709154214560

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

  1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

    image-20240726100927357

  2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

    image-20240726101529823

    此时，name字段没有加索引，此时就会对所有记录加锁，就会升级成为表锁。

S：代表共享锁。

X：代表排他锁。

REC_NOT_GAP：代表没有间隙

GAP：间隙锁

可以使用如下命令查看，意向锁级行锁： 

1
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. 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁

    1
    2
    # 表中没有id=5的记录时，会给5之前的间隙加上间隙锁，比如说5的后面是8，前面是3的情况下，会给3~8之间加上间隙锁。
    update stu set age = 10 where id=5;

  2. 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。

    1
    2
    3
    4
    5
    # 给查询语句加上一把共享锁，
    # 1. 首先对ID=3（例如说age=3的记录ID=3）的记录加上一条行锁（共享锁）
    # 2. 把3之前的数据加上临键锁，如果ID=3前面是1，就会把1到3之间的间隙锁住
    # 3. 如果ID=3的数据后面是ID=7,那么会给ID=3和ID=7之间加上间隙锁，不包含ID=7的数据。
    select * from stu where age = 3 lock in share mode;

    image-20240726104301418

    先给id=3的记录加了行锁，还会将id=3和它之前的间隙锁住，3和它之后的间隙锁住。

  3. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

    1
    2
    3
    4
    # 对ID=19的记录加行锁（共享锁）
    # 会给ID=19到ID=25（例如ID>19有3条记录，最大的ID是25）加上临键锁
    # 给ID>25的数据加上临键锁
    select * from stu where id>19 lock in share mode;

    image-20240726104911093

注意:

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

查看

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

总结：

索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。(对不存在的记录)

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

select * from stu where age = 3 lock in share mode;

如果age是唯一索引，并且不存在这条记录时，锁住的是之间的间隙。

如果age是普通索引，并且存在这条记录时，先把记录锁住，然后在锁住前后的范围。

InnoDB引擎

逻辑存储结构

image-20240709172901882

  • 表空间（ 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日志中，然后这个隐藏列就相当于一个指针,可以通过它来找到该记录修改前的信息。

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

image-20240709173651895

内存架构

image-20240709174034881

image-20240709174345237

image-20240709174512486

image-20240709174743499

磁盘结构

image-20240709175115148

image-20240709175600296

image-20240709210020951

image-20240709210211562

image-20240709210711925

事务的原理

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

image-20240709190322769

redo log就是为了保证我们在进行脏页刷新发生错误时，进行数据恢复，从而保证事务的持久性。

image-20240709204920679

image-20240726120340018

回滚日志

当事务失败需要进行回滚时，主要就是依赖于undo log实现的。MVCC

image-20240709211350597

MVCC

  • 当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:
select …lack in share mode(共享锁)，select … far update、update、insert、delete(排他锁)都是一种当前读。（读取到的是最新的数据记录） 

1
2
3
4
5
6
7
8
9
10
# 事务A
begin;   # 1.
select * from stu;  # 3. 查询到的记录ID=1的记录name是Spring
select * from stu;  # 6. 查询到ID=1的记录name字段还是Spring，保证了可重复读
select * from stu lock in share mode;  # 7. 读取到ID=1的name="JSP"，读取到了最新的数据。

# 事务B
begin;   # 2. 
update stu set name="JSP" where id = 1; # 4.
commit;  # 5.
  • 快照读

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

  1. Read Committed：每次select，都生成一个快照读。

  2. Repeatable Read：开启事务后第一个select语句才是快照读的地方。

  3. Serializable：快照读会退化为当前读。

  • MVCC

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

隐藏字段

image-20240709213158865

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

查看表的表空间文件可以看到隐藏第字段。

ibd2sdi 表空间文件

undo log 日志

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

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

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

undo log日志有两点作用

数据回滚，MVCC

undo log 版本链

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

  1. 在修改记录之前，先会记录一条undo log 日志（记录数据变更之前的样子），DB_TRX_ID修改为2，DB_ROLL_PTR会将地址值修改成这条记录的上一个版本（0x00001）。

  2. 在事务2提交事务后，事务3修改也会生成一条undo log 日志，DB_TRX_ID修改为3，DB_ROLL_PTR会指向（0x00002）,而（0x00002）中的DB_ROLL_PTR会修改成（0x00001）；

image-20240709215349122

image-20240709215643784

image-20240709215811962

image-20240709215921485

与此类似，事务4与事务5。

image-20240709220110687

具体返回哪个版本不是由undo log日志决定的。而是由MVCC实现原理的第三个组件readView决定。

readView

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

image-20240709221150761

image-20240709221721415

不同的隔离级别，生成ReadView的时机不同:
READ COMMITTED：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

读已提交隔离级别（RC）

RC隔离级别下，在事务中每一次执行快照读时生成ReadView。

事务5查询ID=30的第一次快照读。

  1. 在事务五创建的ReadView中，事务3、事务4还没提交，所以m_ids:[3,4,5]
  2. min_trx_id：最小活跃ID是3
  3. max_trx_id：预分配ID，当前最大事务的ID+1，所以为6。
  4. creator_trx_id：ReadView创建者的事务ID，所以为5。

image-20240709222328406

image-20240709222817603

提取的版本。

第一个快照读。

image-20240709223255957

第一轮

1
2
3
4
4 == 5  # false
4 < 3 # false
4 > 6 # false
3 <= 4 <= 6 # 要求不在m_ids中，false

第二轮

1
2
3
4
3 == 5  # false
3 < 3 # false
3 > 6 # false
3 <= 4 <= 6 # 要求不在m_ids中，false

对比后发现0x00002的数据对上了，所以它查询就会把0x00002的数据直接返回。

可重复读（RR）

RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

image-20240709224252929

image-20240709224528669