MySQL笔记

何鸭子
321 阅读25分钟

MySQL

存储引擎

存储引擎简介

innoDB MyISAM Memory 三种存储引擎，默认innodeDB

存储引擎特点

InnoDb

  1. 介绍

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

  2. 特点

    • 支持ACID：原子性、隔离性、持久性、一致性
    • 支持事务：读未提交（什么都没解决），读已提交（解决脏读），重复读（解决脏读、不可重复读），序列化（解决脏读、不可重复读、幻读）
    • 行级锁，提高并发访问性能
    • 支持外键

  3. 文件

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

    参数：innodb_file_per_table

  4. InnoDB 结构

逻辑存储结构

MyISAM

MyISAM

Mamory

image-20230208203140263

三者对比

标黄的是 InnoDB 和 MyISAM 的区别：==InnoDB有事务而MyISAM没有，InnoDB支持行锁MyISAM支持表锁，InnoDB支持外键MyISAM不支持==

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yhvYPkK2-1683276521381)(https%3A%2F%2Fcdn.jsdelivr.net%2Fgh%2FHGD-jpg%2FPicGo-img%2Fimg%2F202305041919374.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7t7HGTh2-1683276524949)(null)]

存储引擎选择

选择存储引擎的时候，需要根据应用特点去进行存储引擎的选择。对于复杂的应用，可以根据实际情况针对多种引擎进行组合。

  • InnoDB：MySQL的默认引擎、支持事务和外键。适用于对应用一致性要求较高场景
  • MyISAM：适用于读取增加操作为主，少修改删除的情况下，对一致性要求不高的场景
    • Mamory：数据保存于内存，访问速度快，常用于缓存。

索引（最重要捏）

索引是帮助MySQL高效获取数据的数据结构。通过搭建索引的数据结构以某种方式引用数据，这样子就可以实现高级查找算法。

索引优缺点：

  • 优势
    • 提高检索效率，降低IO成本
    • 根据索引对数据进行排序，降低排序成本，降低CPU消耗
  • 劣势
    • 索引占据空间
    • 降低了更新表的效率

索引结构

MySQL的索引是在存储引擎层实现的（InnoDB、MyISAM、Memory）。不同的存储引擎有着不同的索引结构，如下

不同的存储引擎支持的索引的情况

左旋：成为右子节点的左子节点，原右子节点的左节点成为你的右节点。

右旋：成为左子节点的右子节点，原左子节点的右节点成为你的右节点。

B树和B+树

www.cs.usfca.edu/~galles/vis… 访问网站自行解决

www.bilibili.com/video/BV1Kr… b树

www.bilibili.com/video/BV1Kr… b+树

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存在在hash表中。如果出现哈希碰撞，可以通过链表来解决。

Hash索引

哈希索引的特点：

  • Hash索引只作用于对等比较，不支持范围索引和排序
  • 查询效率高。

存储引擎支持

  • 在MySQL当中，支持hash索引的是Memory引擎，而InnoDB中具有自适应的hash功能。

思考

为什么InnoDB使用B+树索引结构

  • 同样数据量情况下，B+树的搜素搜索层级要少于二叉树。
  • 和B树相比，B+树仅在叶子节点存储数据，非叶子节点存储索引和下标，这样子就能减少高度和提升性能
  • 和Hash索引相比，B+树支持排序，范围，搜索，而Hash索引仅支持搜索。

索引分类

image-20230209111413285

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

image-20230209111755958

聚集索引

  1. 选取规则

    • 如果存在主键，主机索引就是聚集索引
    • 不存在主键，第一个唯一（UNIQUE）索引作为聚集索引
    • 没有主键和唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引

  2. 什么是回表查询

    image-20230209112915562

    先去二级索引进行查询，获取对应数据的聚集索引id，再去聚集索引中进行查询

  3. 特点

    1. 聚集索引一表一个
    2. 聚集索引的叶子节点下指向一行的数据，二级索引的叶子节点下指向聚集索引id（主键、UNIQUE、rowid）

思考

如何计算InnoDB的B+树高度

  • 已知B+树的单个节点用页作为单位，即16k。而InnoDB的指针大小固定6b
  • 根据主键类型得到主键大小，我们默认为bigint，即8b
  • 设主键数量为x，得到公式：主键大小 * x +( x + 1 )* 6 = 16 * 1024，将主键大小带入即可得到主键数量。

索引使用

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的==最左列开始==，并且不跳过索引中的列。如果==跳跃到某一列，索引将部分失效（后面的字段索引失效）==

联合索引：一个关联了多列字段的索引。一个表有 id、name、age、status、score 四个属性。联合索引就是 name、age、status 三个字段融合一起作为索引。

索引失效情况：

  • 搜索语句中没有最左前缀存在：select * from table_name where age='18' and status='0'。因为 name、age、status 是联合索引且 name 是索引最左字段，name 不存在所以联合索引失效
  • 搜索语句中跳跃：select * from table_name where name='张三' and status='0'。其中 age 不存在，这就是所谓的索引跳跃。出现这种情况下只会走 name索引，即搜索语句中左边符合要求部分的索引。

索引成功情况：

  • 搜索语句中顺序打乱：select * from table_name where age='18' and name='张三' and status='0'。这种情况下依旧会走索引，只需要查询中的索引存在即可。

范围查询

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

索引失效情况：

  • 搜索语句中出现范围索引：select * from table_name where name='张三' and age>'18' and status='0'。因为 age 使用了范围查询，所以右边的索引就会失效，status 不走索引。

索引成功情况：

  • 当范围查询的时候使用（<=,>=）：select * from table_name where name='张三' and age>='18' and status='0'。

索引失效情况

一个表有 id、name、age、status、score 四个属性。联合索引就是 name、age、status 三个字段融合一起作为索引。score 是单列索引

  • 函数运算

    • 对索引使用函数运算就会失效 ：select * from table_name where subString(score,1,2)=0

  • 字符串不加引号

    • 字符串类型使用时，不加引号，索引就会失效：select * from table_name where score=1。单列索引和联合索引都会生效。

  • 头部模糊匹配

    • 头部模糊匹配就会索引失效：select * from table_name where score like '%1'

  • or 连接没有索引

    • 使用 or 连接，一部分存在索引一部分没有索引，也会索引失效：select * from table_name where id=1 and age=18。id作为主键存在聚集索引，虽然 age 是联合索引但是不符合最左前缀法则所以没有索引。即使 id 马上搜索完成但是依旧需要等待 age 扫描全表才可以得到结果。

  • 数据分布影响

    • 如果搜索结果的数量占据了整表数据的一大部分，就会直接全表扫描而不走索引。

覆盖索引

一个表有 id、name、age、status、score 四个属性。联合索引就是 name、age、status 三个字段融合一起作为索引。score 是单列索引

覆盖索引就是，我们需要的结果可以直接在索引中找到，而不需要在二级索引中获取id然后回表去聚集索引中查找。

select id , name , age , status from table_name where name = '老王' and age = 18 and status = '1'：这个搜索条件下，我们需要获取 id、name、age 、status 的结果都可以在组合索引 name、age、status 中找到，这样子就不需要回表查询了。 这就是覆盖索引。

select * 通常会出现回表查询，除非出现了包含所有字段的组合索引，因此不建议使用 * 。

前缀索引

当面对过长的字符串建立索引的时候，如果全部都建立的话会让索引体积过大。查询时会浪费大量的磁盘IO，影响查询效率。我们可以只截取字符串的一部分前缀建立索引，这样子就可以大大节约索引空间，从而提高索引效率。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jj9wk4oJ-1683276525172)(null)]

首先先对长字符串email建立前缀索引，通过截取前5位。

当查询中出现索引时，先截取查询条件的字符串在辅助索引（二级索引）中进行查找，获取到对应的id。然后回表在聚集索引中进行查询，然后比对是否符合要求。如果不符合则继续遍历下一个符合要求的辅助索引，获取id，回表查询。

索引设计原则

image-20230210002934558

SQL优化

插入数据

  • Insert 优化

    • 批量插入

      建议一次批量插入数据不易过多，5000~10000最为合适

      insert into table_name values(1,18,'hyz'),(2,19,'lk'),(3,20,'lov')

    • 手动事务提交

      多条语句同时提交

      start transaction;
insert into table_name values(1,18,'hyz'),(2,19,'lk'),(3,20,'lov') 
insert into table_name values(4,18,'hyz'),(5,19,'lk'),(6,20,'lov') 
insert into table_name values(7,18,'hyz'),(8,19,'lk'),(9,20,'lov') 
commit;

    • 主键顺序插入

      顺序插入的效率是高于乱序插入的

      主键乱序插入：1,5,3,2,6,4,7,9,8
主键顺序插入：1,2,3,4,5,6,7,8,9

  • 大批量插入数据

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

    image-20230210004049580

主键优化

聚集索引的叶子节点中的数据是根据主键顺序存放的。因为用了B+树它是排序树的一种。

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

我们知道数据库的逻辑存储结构是 image-20230210163304410

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含2~n行数据（如果一行数据过大，会出现行溢出），根据主键排序。

  • 主键顺序插入的时候[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dEDcrCfU-1683276522044)(null)]

  • 主键乱序插入时

    这时候主键50 的数据要插入

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hOSpEe9q-1683276525213)(null)]

    先定位到需要插入的页。定位到该页的50%位置，获取到位于该页后50%的数据（取整），然后开辟一个新页将刚获取的数据转移至新页当中

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qFqzzFMZ-1683276525029)(null)]

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sdXuaKUA-1683276522094)(null)]

    设置页面指针，根据id指向正确的页

    image-20230210164204536

页合并

在删除一行数据时，仅仅只是逻辑删除，对删除的行数据标记。标记代表它的空间可以被其他声明使用。

当页中删除的记录达到了阈值 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前一页、后一页）看看两个页能否和自己合并以优化空间使用

image-20230210164525700

第二页的行数据删除到阈值时候，开始前后查询看能够合并。发现第三页数据不足50%可以合并。现在就合并以优化空间

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kYmS98un-1683276524990)(null)]

主键设计原则

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

    因为二级索引的叶子节点是存储的主键。如果主键过长且二级索引较多，就会占用大量磁盘空间，搜索时耗费磁盘IO

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

    这样可以有效降低页分裂的现象

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

    因为它们是无序的，作为主键插入时，会出现页分裂的现象。而且长度较长，耗费磁盘IO

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

    修改主键会修改索引结构，代价较大。

order by 优化

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

在==覆盖索引的情况下==，如果order by字段==全部使用升序排序或者降序排序，则都会走索引==，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort，如果要优化掉Using filesort，则需要另外再创建一个索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

省流：覆盖索引情况下，全升全降排序会走索引，否则不走。可以为特定升降序创建索引，这样子也会走索引了。

总结：

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

group by 优化

  • 在分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

limit 优化

常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。 优化方案：通过子查询获取到对应的id，然后根据id获取数据。用了子查询+聚集索引

select * from table_name where id = ( select id from table_name order by limit 2000000, 10 )

limit的内部是先根据id进行排序，然后由头开始查找直到你需要的第一条数据，然后获取后面的数据。我们这里使用了id的聚集索引，不用回表直接获取到了对应的id，然后根据id直接返回数据。

count 优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）； InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。 优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis

count的几种用法：

  • 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
  • 用法：count(*)、count(主键)、count(字段)、count(1)
  • count(主键)跟count()一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count()一样；count(null)返回0

各种用法的性能：

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

按效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)\

update优化

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

更新数据的条件是索引上行锁，非索引数据上表锁

如以下两条语句： update student set no = '123' where id = 1;，这句由于id有主键索引，所以只会锁这一行； update student set no = '123' where name = 'test';，这句由于name没有索引，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引

视图 / 存储过程 / 触发器

视图

介绍

视图就是保存了sql语句。每次我们搜索视图的时候它就会自动执行sql语句并返回结果。这样子就不需要重复多次写sql语句了。视图只保存了查询的sql逻辑，不保存查询结果。

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

image-20230210220853161

视图的检查选项

我们可以对视图中的数据进行添加。如果添加的数据和当初创建视图时的sql逻辑冲突的时候，可以通过检查选项对数据插入进行检查，有冲突的数据则不允许插入。mysql提供了两种视图的检查选项。在常见视图的时候添加检查选项就可以对插入数据进行检查了。

它们总体的格式是with [local/cascaded] chekc option。当使用with check option 子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更行、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保证一致性。为了确定检查的范围，MySQL提供了两个选项local和cascaded，默认是cascaded

基于视图创建视图的例子：

create view 视图1 as select age from 表1 where age > 1;
create view 视图2 as select age from 视图1 where age < 10;
create view 视图3 as select age from 视图2 where age < 5;

在这种情况下，我们向三个视图更改每行数据是没有任何约束的。

  • with local chekc option

    例子：

    create view 视图1 as select age from 表1 where age > 1 with local chekc option;
create view 视图2 as select age from 视图1 where age < 10;
create view 视图3 as select age from 视图2 where age < 5 with local chekc option;

    我们向视图3添加数据insert into 视图3 values (0)。

    1. 先去视图3创建语句判断是否存在 with local check option 的语句。存在则判断是否符合 where 语句
    2. 视图3基于视图2创建，所以去判断视图2是否存在 with local check option 的语句。不存在则跳过。
    3. 视图2基于视图1创建，所以去判断视图2是否存在 with local check option 的语句。存在则判断是否符合视图1的 where 语句。
    4. 经过上诉条件，发现不符合视图1的要求，插入失败。

    总体流程：

    1. 判断是否存在 with local check option 语句。
    2. 存在则判断where，不存在则直接检查依赖视图。
    3. 然后检查依赖视图。
    4. 短路与操作。有任意依赖视图报错则失效。

  • with cascaded chekc option

    create view 视图1 as select age from 表1 where age > 1;
create view 视图2 as select age from 视图1 where age < 10;
create view 视图3 as select age from 视图2 where age < 5 with cascaded chekc option;

    依旧向视图3插入数据insert into 视图3 values (0)。

    1. 先去视图3创建语句判断是否存在 with cascaded chekc option 的语句。存在则判断是否符合 where 语句
    2. 视图3基于视图2创建，所以去判断是否符合视图2的 where 语句
    3. 视图2基于视图1创建，所以去判断是否符合视图1的 where 语句
    4. 经过上诉条件，发现不符合视图1的要求，插入失败。

    总体流程：

    1. 判断是否存在 with cascaded chekc option 语句。
    2. 存在则对每个视图以及其依赖视图的sql逻辑进行判断
    3. 短路与操作。有任意依赖视图报错则失效。

  • 两者区别

    with local chekc option：检查依赖视图中有相同视图检查选项的sql逻辑

    with cascaded chekc option：会检查所有依赖视图的sql逻辑

视图的更新

要使视图可更新，视图中的数据和基础表中的数据必须是一对一的关系（视图中的属性必须是表中自带的属性）。如果视图包含以下任意一项，视图都不可更新

  • 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()）
  • DISTINECT
  • GROUP BY
  • HAVING
  • UNION 或则 UNION ALL

视图的作用

  • 简单

    视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次都重新指定所有条件。

  • 安全

    数据可可以授权，但不能授权到数据库特定的行的特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

存储过程

存储函数

触发器

锁

概述

  • 介绍

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

  • 分类

    MySQL中的锁，按照锁的粒度分以下三类

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

全局锁

  • 介绍

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

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

  • 为什么全库备份需要加全局锁？

    如果诸逐个表去备份。有的表之间相互逻辑关联，逐个备份最终可能出现逻辑错误。

    例如：一个库存表和订单表。我们先备份库存表。在备份过程中有新的订单存入订单表中。最终导致库存表还是原来的库存，但是订单表又更新了，出现已下单但是没扣库存的逻辑错误，导致数据不一致。

    如果逐个备份，会数据不一致。

  • 全局锁操作

    • 加锁

      flush tables with read lock;

    • 数据库备份

      mysqldump -uroot -proot tableName>tableName.sql

    • 解锁

      unlock tables;

  • 特点

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

    1. 如果在主库上备份，在备份期间都不能执行更行操作，业务基本上停摆
    2. 如果在从库上备份，那么备份期间从库不能同步主库日志，导致主从延迟

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

    mysqldump --sigle-transaction -uroot -proot tableName>tableName.sql

表级锁

  • 介绍

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

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

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

表锁

  • 表锁

    对于表锁，分两类

    1. 表共享读锁（read lock）
    2. 表独占写锁（write lock）

    语法：

    1. 加锁

      lock tables 表名... read/write

    2. 释放锁

      unlock tables / 客户端断开连接

  • 读锁特点

    添加读锁，当前客户端可以读，其他客户端也可以读。当前客户端不允许写，其他客户端阻塞等待读锁释放。

  • 写锁特点

    添加写锁，当前客户端既能读也能写。其他客户端的操作都将阻塞等待写锁释放。

元数据锁

image-20230212153402561

意向锁

  • 介绍

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

    举例：线程A修改表中某条数据，对数据默认上了行锁并且修改了意向锁。当线程B向表添加表锁的时候，先通过意向锁判断一下当前表锁和行锁是否冲突（行读和表写冲突）。

    意向锁有两种分类

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

行级锁

  • 介绍

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

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

    1. 行锁（Record Lock）：记录单个行记录的锁，防止其他事务对此进行update和delete操作。在RC（读已提交）、RR（可重复度）隔离级别下都支持。

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

    3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR（可重复读）隔离级别下支持。

    隔离级别

    image-20230212155730397

    间隙锁就像小括号，临键锁就像中括号

行锁

  • 介绍

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

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

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

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yTfpWzPF-1683276525075)(null)]

    在默认条件下，InnoDB在可重复读事务隔离级别下，使用next-key锁（临键锁）进行搜索和索引扫描，以防止幻读

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

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

    走索引是行锁，不走索引是锁升级

  • 行锁类型

    增删改加排他锁，正常查不加锁。select lock share mode加共享锁，select for update 加排他锁

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7dl4m1o2-1683276525122)(null)]

间隙锁/临键锁

  • 介绍

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

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

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

      把等值的数据上行锁，给等值前后加间隙锁，防止在搜索期间有人向这里添加数据。就像抓蟑螂，直接抓住有点难，将蟑螂连同旁边的地方都盖住就可以了。

    3. 索引上的范围查询（唯一索引）会方位到不满足条件的第一个值为止。

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

InnoDB

事务原理

  • 事务

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

  • 特性

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

事务特性通过redo log、undo log、锁、MVCC来控制image-20230212164200664

redo log

  • 介绍

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

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

  • 操作步骤

    1. 业务提交
    2. 将数据页变化写入 redo log buffer 当中。
    3. 将 redo log buffer 写入磁盘，生成 redo log file。

    使用场景

    • 内存中的buffer pool 同步到磁盘的ibd文件时，出现异常，就可以通过redo log file修改

image-20230212180603681

undo log

  • 介绍

    回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）

    undo log和redo log记录物理日志不同，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然。当执行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 就是快照读。快照读读取的是数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

    • 读已提交：每次select，都生成一个快照读
    • 可重复读：开启事务后第一个select语句才是快照读的地方

理解

undo log版本链条

  • 每个表都有两个重要的隐藏字段和一个可能存在的隐藏字段。

    • 两个重要的隐藏字段分别是：最近操作的事务id（DB_TRX_ID） 和 版本号（DB_ROLL_PTR），如果这个记录存在上一个版本则指向上一版本。
    • 可能存在的隐藏字段：rowId。如果当前表中没有主键，没有唯一索引那么表就会自动生成一个rowId；

    然后我们知道undo log 记录的是指令。当一个事务对一行数据进行操作时候，这行数据的==最近操作的事务id（DB_TRX_ID）==就会指向操作事务，如果不存在上一版本则 ==版本号（DB_ROLL_PTR）==设置为 1 且无指向，否则==版本号（DB_ROLL_PTR）==加1并且指向上一个版本。undoLog就会把这条数据记录下来。

    当一行数据操作的事务多了之后，就会有一串undo log相链，我们就叫这undo log 版本链。

    image-20230220213427691

readview

  • readview 是快照读执行 MVCC 提起数据的依据，记录并维护当前活跃的事务id。

    readview 包括了四个重要的字段：当前活跃事务id集合，最小活跃事务id，预分配事务id，创建readview的事务id

    我们通过读取当前事务id与readview进行一下四条的规则比对

    • ==当前事务id 等于 创建readview事务id==。如果符合说明数据是当前事务更改的，可以访问这个版本的数据
    • ==当前事务id 小于 最小活跃事务id==。成立说明当前事务数据已提交，可以访问这个版本的数据
    • ==当前事务id 大于 预分配事务id==。成立说明当前事务是readview生成后开启的，不允许访问。
    • ==最小事务id <= 当前事务id <= 预分配事务id 且 当前事务id 不存在于 当前活跃事务id集合当中==，说明数据已提交，可以访问这个版本的数据。

    我们事务对一行数据进行查询的时候，会生成当前数据的 readview。通过遍历 undo log版本链，判断每一个版本的==最近操作的事务id（DB_TRX_ID）==是否符合 readview 的四条规则，根据规则判断能否获取这条undolog的数据。将第一个获取的数据返回给当前事务。

avatar
文章
阅读
粉丝