侧边栏壁纸
博主头像
ldwcool's Blog博主等级

行动起来，活在当下

  • 累计撰写 24 篇文章
  • 累计创建 10 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录
数据库 后端

Java 面试之数据库

ldwcool
ldwcool
2024-03-27 / 0 评论 / 1 点赞 / 34 阅读 / 52168 字

Preface

关系型数据库主要考点：

  • 架构
  • 索引
  • 锁
  • 语法
  • 理论范式

数据库架构

如何设计一个关系型数据库？

image-20240324153757356

要设计一个关系型数据库首先需要划分为两大部分：存储部分和程序实例模块。

存储部分类似文件系，将数据持久化到设备中。

程序实例模块作用是对存储进行逻辑上的管理，它主要包含：

  1. 将数据的逻辑关系转换为物理存储关系的存储管理模块；
  2. 优化执行效率的缓存模块；
  3. 将 SQL 语句进行解析的 SQL 解析模块；
  4. 记录操作的日志管理模块；
  5. 多用户管理的权限划分模块；
  6. 灾难恢复模块；
  7. 优化数据查询效率的索引模块；
  8. 使得数据库支持并发操作的锁模块。

索引模块

首先抛出几个常见问题，带着这些问题学习下面的章节效果会更好。

这里先给出一些简单的解释：

  • 为什么要使用索引？

    快速查询数据。

  • 什么样的信息能成为索引？

    主键、唯一键以及普通键等。

  • 索引的数据结构？

    • 生成索引，建立二叉查找树进行二分查找；
    • 生成索引，建立B-Tree结构进行查找；
    • 生成索引，建立B+-Tree结构进行查找；
    • 生成索引，建立Hash结构进行查找。

  • 密集索引和稀疏索引的区别？

优化索引，二叉查找树

二叉查找树（Binary Search Tree）是一种常见的数据结构，用于组织数据元素。它具有以下特性：

  • 每个节点最多有两个子节点，分别称为左子节点和右子节点。
  • 对于每个节点，左子节点的值小于等于该节点的值，右子节点的值大于等于该节点的值。
  • 中序遍历二叉查找树会得到一个有序的序列。

这种数据结构的主要优势在于它支持快速的搜索、插入和删除操作，时间复杂度为 O(log n)，其中 n 是树中节点的数量。

image-20240325213905972

二叉查找树在最坏情况下可能会出现退化成链表的情况，这种情况下搜索、插入和删除操作的时间复杂度会达到 O(n)，其中 n 是树中节点的数量。这通常发生在树的结构不平衡时，比如插入顺序不当或者数据分布不均匀导致树高度不平衡。在最坏情况下，二叉查找树的性能会下降到线性级别。

image-20240325215246387

此时可能有人会说可以利用树的旋转特性保证其一直为平衡二叉树，这样其时间复杂度就一直为 O(log n) 了。

平衡二叉树（Balanced Binary Tree），也称为 AVL 树（Adelson-Velsky and Landis tree），是一种特殊的二叉查找树，它在插入和删除操作后会通过旋转操作来保持树的平衡，从而避免了二叉查找树在最坏情况下退化成链表的问题。

平衡二叉树具有以下特性：

  • 每个节点的左子树和右子树的高度差不超过 1。
  • 每个子树都是平衡二叉树。

通过这些特性，平衡二叉树能够确保在最坏情况下，搜索、插入和删除操作的时间复杂度保持在 O(log n) 级别，其中 n 是树中节点的数量。

这样确实避免了二叉查找树最坏情况下时间复杂度会达到 O(n) 的问题，但是另一个问题就出现了：影响程序运行速度的瓶颈是磁盘 IO。

假设这些数据都存放在磁盘中，本次要寻找的数据是 6，那么发生第一次 IO 时读入根 5 数据，接着再发生一次 IO 读入右子树根 7 数据，最后才读入到数据 6，一共产生了 3 次 IO，即检索深度每多 1 层就会增加一次 IO。

所以不管是平衡二叉树还是红黑树，由于它们每个节点都只能拥有两个子节点，当数据块非常多的时候树的深度也会非常深，IO 的次数也会变得很多，这时树的检索性能甚至比全表扫描都要慢很多，根本无法满足优化查询的需求。

想要既降低查询的时间复杂度，又降低 IO 的次数，唯有减少树的深度，并增加每个节点存储的数据量。

优化索引，B-Tree

B树是一种自平衡的树数据结构，通常用于数据库和文件系统中。它被设计用来高效地存储、检索和排序大量数据，特别是在需要频繁的插入、删除和查找操作时。B-Tree的特点包括：

  • 平衡性： B-Tree保持自身平衡，确保所有叶节点到根节点的路径长度相等或相差不大。
  • 多路搜索： 每个节点可以拥有多个子节点，这使得B-Tree能够存储更多的数据，提高了查找效率。
  • 高度平衡： B-Tree的高度相对较低，这意味着查找、插入和删除操作的时间复杂度是对数级别的。
  • 节点分裂和合并： 当节点中的数据达到一定数量时，会进行分裂操作，以保持平衡；反之，当节点中的数据过少时，会进行合并操作，以减少空间使用。

总的来说，B-Tree是一种非常有效的数据结构，特别适用于需要高效地处理大量数据的场景。

定义：

  • 根节点至少包括两个孩子；

  • 树中每个节点最多含有 m 个孩子 (m>=2)；

  • 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子；

  • 所有叶子节点都位于同一层；

  • 假设每个非终端结点中包含有 n 个关键字信息，其中：

    1. Ki (i=1...n) 为关键字，且关键字按顺序升序排序 K(i-1) < Ki；

      图中数据都是从小到大排序，如：17, 35 和 8,12 等。

    2. 关键字的个数 n 必须满足：[ceil(m/2)-1] <= n <= m-1；

      图中每个节点有 3 个孩子，则关键字数量只能为 2。

    3. 非叶子结点的指针：P[1]，P[2]，...，P[M]；其中 P[1] 指向关键字小于 K[1] 的子树，P[M] 指向关键字大于 K[M-1] 的子树，其它 P[i] 指向关键字在 (K[i-1], K[i]) 左开右开区间的子树。

      如图中根结点的左子树 8,12 均小于 17，右子树 65,87 均大于 35，而中间的子树 26,30 处于 17~35 之。

image-20240326070107170

B-Tree 的时间复杂度为 O(log n)。

当数据发生变化时，B-Tree 通过合并、分裂、上移、下移节点保证其特征，所以它的深度远比二叉查找树矮的多，并且不会出现最坏情况下退化成链表的情况。

优化索引，B+Tree

B+树是主流的索引，MySQL数据库使用的正是此索引。

B+ 树是 B树的变体，其定义基本与 B树相同，除了：

  • 非叶子节点的子树指针与关键字个数相同；

  • 非叶子节点的子树指针 P[i]，指向关键字值在 [K[i], K[i+1]) 左闭右开区间的子树；

  • 非叶子节点仅用来索引，数据都保存在叶子节点中；

    所以 B+Tree 必须要检索到叶子节点，叶子节点存放的可能是指向数据的指，组件的值或者干脆是目标数据本身；又由于非叶子节点仅用来存储索引不存储数据，从而能够存储更多的索引，因此 B+Tree 相比 B树更矮。

  • 所有叶子节点均有一个链指针指向下一个叶子结点。

    B+Tree 的叶子节点是按照大小顺序排列的，可以方便我们直接在叶子节点做范围统计，比如搜索大于等于 10 的数据。

PixPin_2024-03-26_07-21-14

B+Tree 更适合用来做存储索引：

  • B+树的磁盘读写代价更低；

    B+树非叶子节点不存放真实数据，能够存放更多的索引，若将非叶子节点的关键字存放在同一盘块中，那么这个盘块所能容纳的关键字数量相比来说就会更多，一 IO 读取到内存中需要查找的关键字也就更多，整体上降低了需要 IO 的次数。

  • B+树的查询效率更加稳定；

    因为任何关键字的查找必须从根结点开始，到叶子节点为止，也就是说所有关键字的查找长度都相同，因此每个数据的查询效率几乎相同，时间复杂度为 O(log n)。

  • B+树更有利于对数据库的扫描；

    只需要遍历叶子节点就能扫描全部数据，在范围查询中有着更高的性能。

优化索引，Hash

根据 hash 函数的运算仅需一次定位就能找到查询数据所在的头，所以理论上 Hash 索引的查询效率高于B+树索引。

如下图所示，仅需一次 hash 运算就能定位到 Sandra Dee 数据所在的 152 号bucket，然后将bucket中的数据全部加载到内存中，由于其为链表结构，顺着 John Smith 所在的指针最终就能定位到 Sandra Dee 所在的节点。

PixPin_2024-03-27_21-49-15

虽然 Hash 索引查询效率高，但是由于其本身特性也带来了很多缺点。

缺点：

  • 仅仅能满足=，N，不能使用范围查询；

    因为经过 hash 运算之后的 hash 值之间的大小关系不能保证和运算前的完全一致。

    如上图中的 John Smith 和 Sandra Dee 就有相同的 hash 值。

  • 无法被用来避免数据的排序操作；

    理由和上一条一样。

  • 不能利用部分索引键查询；

    对于组合索引，hash 索引是将其合并之后统一计算 hash 值，不是单独计算组合索引中每个索引的 hash 值，所以无法利用组合索引中的部分几个索引进行查询；而 B+树是支持利用组合索引中的部分索引进行查询的。

  • 不能避免表扫描；

    因为 hash 值相同的数据都存放在一个bucket中，所以即便定位到了 hash 值所在的位置还是要将bucket中的数据全部加入到内存，通过遍历链表查询到数据。

  • 遇到大量 Hash值相等的情况后性能并不一定就会比 B+Tree 索引高。

    极端情况下大多数的键计算出来的 hash 值都相同，这就会造成某个bucket中存放大量的数据。

综合以上缺点，hash 索引不够稳定，因此它不是主流的索引。

优化索引，BitMap

BitMap：位图索引。很少有数据库支持位图索引，知名的有 Oracle 数据库。

当表中的某个字段只有几种值的时候，如性别字段，若为了在此字段上实现高效的统计，使用位图索引是最好的选择。

image-20240327221852818

位图索引的结构类似 B+树。

由于数据的值种类固定，它会先按照值的种类分开，如上图中的 Blue、Green、Red、Yellow，每种值的数据空间会存储实际数据行是否是这个值，如 Blue 的位图 1000100100010010100 表示第一行是 Blue，第二行不是 Blue，第五行是 Blue 以此类推。

由于只需存放是和否，通常只用一个 bit 位存放数据，所以理论上一个叶子块可以存放非常多的 bit 位用来表示不同行的数据，用它来做统计效率非常高。

但是它也有一个很大的缺陷：锁的粒度非常大。当尝试新增或修改数据时，通常与这个数据在同一个位图的其它数据都会被锁住，因为某行所在的位图数据可能因为数据的添加或修改而发生改变。

所以位图索引不适合高并发的联机事务处理系统，它适合并发较少且统计运算较多的系统。

密集索引和稀疏索引

密集索引和稀疏索引的区别：

  • 密集索引文件中的每个搜索码值都对应一个索引值；

    可理解为叶子节点保存的不仅仅是键值，还保存了同一行中的其它列的信息。

    密集索引决定了表的排列顺序，而一张表只能有一个物理排列顺序，所以一张表最多只能创建一个密集索引。

  • 稀疏索引文件只为索引码的某些值建立索引项；

    可理解为叶子节点仅保存键位信息和该行的行数据地址。

    有的稀疏索引仅保存键位及其主键信息。

在查找时定位到叶子节点后还需通过地址或主键信息进一步定位到数据所在的位置。

image-20240330180242480

我们具体分析下 MySQL 数据的情况。

MySQL 数据库主要有两种存储引擎，分别时MyISAM和InnoDB。

  • MySAM的主键索引，唯一键索引、普通索引，均属于稀疏索引；
  • InnonDB有且仅有一个密集索引。

InnoDB索引选取规则：

  • 若一个主键被定义，该主键则作为密集索引；

  • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引；

  • 若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）；

  • 非主键索引存储相关键位和其对应的主键值，包含两次查找。

    非主键索引（稀疏索引）不存储行数的物理地址，而是存储该行的主键值，所以非主键索引包含两次查找，一次是查找次级索引本身，另一次是根据次级索引查找主键。

InnoDB检索过程：

  • 密集索引

    将所有主键构建成一棵 B+树，并且行数据直接存放在叶子节点上。

    实际存储中InnoDB的密集索引和行数据保存在同一个文件中，在加载密集索引的同时也会将对应的行数据加载到内存中。

    假设使用where id=14主键查询条件，那么直接使用 B+树的查找算法定位到对应的叶子节点后获取其中的行数据即可。

  • 稀疏索引（辅助索引）

    使用稀疏索引进行条件筛选，需要两个步骤，第一步在稀疏索引的 B+树中检索目标键，如 Ellison，得到主键值为 14，接着第二步使用得到的主键 14 再从密集索引中执行 B+树的检索操作，最终定位到叶子节点得到行数据。

MyISAM检索过程：

MyISAM 中的主键索引和辅助键索引都是稀疏索引，只是存储的内容有所不同，因此它们的检索过程是相同的。

主键索引 B+树中存储的是主键，辅助见索引 B+树中存储的是辅助键，索引数据和表数据分别使用单独的文件存储，因此这两棵 B+树的叶子节点存放的都是表数据存放位置的地址。

对于表数据来说这两个键没有任何差别，通过辅助键索引查找无需访问主键的索引树。

PixPin_2024-03-30_18-12-48

以MySQL 8.0.36 为例，在数据库中创建以下两张表：

表shop_info_small使用MyISAM存储引擎：

CREATE TABLE `shop_info_small`
(
    `shop_id`      int(2) NOT NULL AUTO_INCREMENT,
    `shop_name`    varchar(20) DEFAULT NULL,
    `person_id`    int(2)      DEFAULT NULL,
    `shop_profile` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`shop_id`),
    UNIQUE KEY `shop_name` (`shop_name`)
) ENGINE = MyISAM
  DEFAULT CHARSET = utf8;

表person_info_large使用InnoDB存储引擎：

CREATE TABLE `person_info_large`
(
    `id`      int(7) NOT NULL AUTO_INCREMENT,
    `account` varchar(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `area`    varchar(20) DEFAULT NULL,
    `title`   varchar(20) DEFAULT NULL,
    `motto`   varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `account` (`account`),
    KEY `index_area_title` (`area`, `title`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;

创建完毕后在两张表中随机添加一些数据。

进入该数据库文件存放的目录中:

  • person_info_large.ibd文件中存放的就是表person_info_large的索引和行数据信息。
  • shop_info_small.MYD文件中存放是表shop_info_small的行数据信息，shop_info_small.MYI存放的是索引信息。

image-20240331144541342

总结

回顾之前的常见问题：

  • 为什么要使用索引？

    索引可以避免全表扫描方式查询数据，提升检索效率。

  • 什么样的信息能成为索引？

    主键、唯一键等能够让数据具备一定区分性的字段都能够成为索引。

  • 索引的数据结构？

    主流是 B+树，另外还有 hash 和 BitMap 索引，其中 MySQL 数据库不支持 BitMap 索引，且InnoDB和MyISAM两种存储引擎不支持 hash 索引。

  • 密集索引和稀疏索引的区别？

    见上一小节开始部分。

索引之如何调优 SQL

由索引衍生出来的问题，以 MySQL 8.0.36 为例：

  • 如何定位并优化慢查询 Sql？
  • 联合索引的最左匹配原则的成因。
  • 索引是建立得越多越好吗？

如何定位并优化慢查询 SQL

具体场景具体分析，只提出大致思路：

  1. 根据慢日志定位慢查询 sql；
  2. 使用explain等工具分析 sql；
  3. 修改 sql 或者尽量让 sql 走索引；

下面就来看一个简单实用的调优例子。

向person_info_large表中填充 550W 行的数据，用来支持生成慢查询 sql。

接着需要做一些慢查询日志相关设置。

show variables like '%query%';

image-20240331162407046

  • slow_query_log：慢查询日志开关。
  • slow_query_log_file：慢查询日志文件位置，此文件中就存放了慢查询的 sql 语句。
  • long_query_time：sql 语句执行时间超过此值（单位：秒）时即认为时慢查询 sql 并记录到慢查询日志文件中去；

设置慢查询日志开关为打开状态。

set global slow_query_log = on;

设置慢查询时间阈值为 1秒。此设置完毕之后需要重新连接数据库才能生效。

set global long_query_time = 1;

使用set global更改 MySQL 参数只会在本次运行中生效，一旦 MySQL 重启就会重置这些已设置的参数，通过修改my.cnf配置文件可以实现持久化设置。

查询本次会话慢查询 sql 的条数：

# 注意：只显示本次会话慢查询 sql 的个数，新的会话会重置为 0。
show status like '%slow_queries%';

再来回忆下person_info_large表的结构：

CREATE TABLE `person_info_large`
(
    `id`      int(7) NOT NULL AUTO_INCREMENT,
    `account` varchar(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `area`    varchar(20) DEFAULT NULL,
    `title`   varchar(20) DEFAULT NULL,
    `motto`   varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `account` (`account`),
    KEY `index_area_title` (`area`, `title`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;

制造一条慢查询 sql 语句：

select name from person_info_large order by name desc;

显示的执行时间超过 1秒，并且show status like '%slow_queries%';显示的数量也为 1。

image-20240331223821721

image-20240331164443392

来看下慢查询日志文件中的内容：

image-20240331224121210

其中就记录了慢查询sql语句及真正的查询耗时。

利用explain 分析慢查询 sql，此关键字一般放在select查询语句的前面，用于描述 MySQL 如何执行查询操作以及 MySQL 成功返回查询的结果集需要扫描的行数；使用它可以得出查询效率低下的原因，从而改进我们的 sql 语句使查询优化器能够更好的工作。

explain并不会真正执行后面的 sql 语句。

explain select name from person_info_large order by name desc;

image-20240331224508476

在explain执行结果中有两列数据对 sql 调优非常的关键，分别是：

  • type：表示找到需要数据行的方式。

    性能从最优到最差的顺序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

    其中index和all表示本次查询是全表扫描，因此当我们执行的语句时慢查询 sql 语句且出现了index或all时表示执行的语句需要优化。

  • extra

    image-20240331174116671

根据以上慢查询 sql的分析结果，其中的type字段值为ALL，extra为Using filesort，因此需要对上面执行的这条 sql 调优。

给name字段加上索引：

alter table person_info_large add index idx_name(name);

加上索引之后，再次分析上面的慢查询 sql 语句，type字段值就变为了index，extra值边变为Backward index scan; Using index。

image-20240331230230686

再来执行一遍上面的慢查询 sql 语句。

image-20240331231804337

好家伙！直接从 3.6 秒优化到了 0.074 秒！

以上就是一个 sql 调优的例子，并不绝对，具体如何调优需要根据实际情况决定。

再来补充一个知识点，count()函数会使用哪一个索引，会选择密集索引还是稀疏索引呢？

explain select count(*) from person_info_large;

image-20240331234000398

分析结果出乎意料，竟然使用的是唯一键索引account字段，而不是使用主键索引id字段，出现此种情况的原因是 MySQL 查询优化器的目标是尽可能的使用索引，并且使用最严格的索引排除尽可能多的数据行，优化器试图排除数据行的原因在于它排除数据行的速度越快，找到与条件匹配的数据行也就越快，因此查询优化会根据其分析和标准决定最终使用哪个索引。

上面这条查询语句没有使用主键索引的原因大概是主键索引（密集索引）的叶子节点中另外存放了其它列的数据，导致节点占用的存储空间变大，又由于稀疏索引只存放关键字和主键的值，因此一次 IO 就能在内存中加载更多的关键字和主键的值用来执行count()，提高了执行效率，因此查询优化器选择了稀疏索引。

sql 语句执行结果，耗时 607 毫秒。

image-20240331235639635

当然我们还可以强制其使用主键索引（密集索引）来执行count()，看看它执行的时间是否大于稀疏索引的执行时间。

explain select count(*) from person_info_large force index (`PRIMARY`);

image-20240331235308072

sql 语句执行结果，耗时 664 毫秒，因此得出使用主键索引未必比使用其它索引要快。

image-20240331235914223

MySQL 查询优化器固然很厉害，但有时它按照其标准算出来的索引并不一定是最优的，还是需要具体情况具体分析，这时就可以使用force index测试各种索引的执行效率挑选出最优的索引。

联合索引的最左匹配原则的成因

什么是联合索引？答：由多列组成的索引即为联合索引。例如表person_info_large中的index_area_title就是联合索引。

CREATE TABLE `person_info_large`
(
    `id`      int(7) NOT NULL AUTO_INCREMENT,
    `account` varchar(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `area`    varchar(20) DEFAULT NULL,
    `title`   varchar(20) DEFAULT NULL,
    `motto`   varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `account` (`account`),
    KEY `index_area_title` (`area`, `title`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;

那是最左匹配原则又是什么？

例如表中有两列 A 列和 B 列，以 AB 顺序将其设置为联合索引，当使用where A='xxx' and B='yyy' 或 where A='xxx'查询条件时就会使用联合索引，与之不同的时当使用where B='yyy'查询条件时就不会使用到联合索引。

具体情况请看下面这个例子：

由上面的表person_info_large定义语句可知，其中存在一个area列和title列的联合索引index_area_title，我们就用此作为例子。注意：联合索引的顺序时area在前titile在后，这很重要。

执行以下两条查询语句将会使用到联合索引：

explain select * from person_info_large where area = 'xxx' and title ='yyy';

image-20240401224644742

explain select * from person_info_large where area = 'xxx';

image-20240401224551145

但是如果去掉area只保留title查询条件的话，此条查询语句使用的就是全表扫描，是最差的性能。

explain select * from person_info_large where title ='yyy';

image-20240401225302079

最左匹配原则定义：

  1. 最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询>、<、between、like才停止匹配，比如a = 3 and b = 4 and c > 5 and d = 6如果建立a,b,c,d顺序的索引，由于c定死在索引的第 3 位，在遇到c > 5的范围查询条件时就停止匹配，所以d是用不到索的；但如果建立a,b,d,c的索引则都可以用到，且a,b,d的顺序可以任意调整。

  2. =和in可以乱序，比如a = 1 and b = 2 and c = 3建立a,b,c索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

    例如上面的例子中，将area和title查询顺序调换位置也会使用联合索引

    explain select * from person_info_large where title ='yyy' and area = 'xxx';

    image-20240401230227837

    在查询过程中 MySQL 会将title ='yyy'和area = 'xxx'调换位置使其满足最左匹配原则。

联合索引的最左匹配原则的成因：

  • MySQL 创建联合索引的规则是首先使用联合索引的最左边字段进行排序，排序完成之后再从当前排序的基础上对第二个索引字段排序，以此类推，类似于order by 字段1,字段2,字段3...排序语句，因此最左边的第一个索引字段是绝对有序的，而后面的索引字段则是无序的了，所以一般情况下直接使用第 2 个或之后的索引字段作为查询条件是用不到索引的。

例如下图中联合索引顺序是col3, col2，就会用col3列的值建立一个 B+树，搜索 Alice 时定位到叶子节点后，又会以col2列的值的顺序排序，如图中的 34, 77，因此从图中就能够直观的看出如果仅使用col2作为查询条件是无法使用到联合索引col3, col2优化查询效率的，必须要依靠col3。

当然 MySQL 中联合索引的结构不一定和下图一致，但总体上是这么个意思。

image-20240401231840839

索引是建立的越多越好吗？

答案是否定的，所谓物极必反：

  • 数据量小的表不需要建立索引，建立会增加额外的索引开销；

    例如看个两页的宣传手册哪里需要目录。

  • 数据变更需要维护索引，因此更多的索引意味着更多的维护成本；

  • 更多的索引意味着也需要更多的空间。

    例如 100 页的书却有 50 页的目录。

锁模块

锁模块常见问题：

  • MyISAM与InnoDB关于锁方面的区别是什么？
  • 数据库事务的四大特性。
  • 事务隔离级别以及各级别下的并发访问问题？
  • InnoDB可重复读隔离级别下如何避免幻读？
  • RC、RR级别下的InnoDB的非阻塞读如何实现？

MyISAM 与 InnoDB 关于锁方面的区别是什么？

答：

  • MyISAM默认用的是表级锁，不支持行级锁；
  • InnoDB默认用的是行级锁，也支持表级锁。

为了本节的演示，需要新建两张表，并分别初始化 220万行数据：

CREATE TABLE `person_info_large`
(
    `id`      int(7) NOT NULL AUTO_INCREMENT,
    `account` varchar(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `area`    varchar(20) DEFAULT NULL,
    `title`   varchar(20) DEFAULT NULL,
    `motto`   varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `account` (`account`),
    KEY `name` (`name`),
    KEY `index_area_title` (`area`, `title`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8;

CREATE TABLE `person_info_large`
(
    `id`      int(7) NOT NULL AUTO_INCREMENT,
    `account` varchar(10) DEFAULT NULL,
    `name`    varchar(20) DEFAULT NULL,
    `area`    varchar(20) DEFAULT NULL,
    `title`   varchar(20) DEFAULT NULL,
    `motto`   varchar(50) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `account` (`account`),
    KEY `name` (`name`),
    KEY `index_area_title` (`area`, `title`)
)
    ENGINE = MyISAM
    DEFAULT CHARSET = utf8;

先看MyISAM的实际情况。

在单独执行下面两条 sql 语句时耗时情况分别如下：

第一条查询 sql 语句耗时达 33.6 秒。

select * from person_info_myisam where id between 1 and 2000000;

image-20240404101401464

第二条更新 sql 语句耗时为 0.001 秒。注意：更新的id数为 2000001 在查询语句的范围之外。

update person_info_myisam set account = account where id = 2000001;

image-20240404102256484

那么此时如果在第一条查询 sql 语句的运行期间在 MySQL 连接客户端中新建一个查询窗口session执行第二条更新 sql 语句，第二条 sql 的查询耗时将会变为惊人的 28.08 秒。

出现这种情况的原因是：当执行查询语句时MyISAM会自动的为表加上表锁，在这期间将会阻塞住其它的session对此表的数据更新（增、删、改）。

因此第二条更新语句需要等待第一条查询语句运行完毕后才能够执行，所以耗时才高达 28.08 秒。

image-20240404102200823

具体来说，MyISAM会为查询语句自动加上表级的读锁，为增、删、改语句加上表级的写锁；当读锁未被释放时，其它的session想要为同一张表加上写锁就会被阻塞住，直到所有的写锁被释放。

可以使用下面这些语句为表显式的操作表锁。

# 为表 person_info_myisam 加上读锁
lock tables person_info_myisam read;

# 为表 person_info_myisam 加上写锁
lock tables person_info_myisam write;

# 释放所有的锁
unlock tables;

读锁也叫共享锁，因为它不会阻塞住其它session的查询操作。

再来看先上写锁再上读锁的情况。单独执行更新和查询语句的耗时如下：

更新语句耗时 24.843 秒。

update person_info_myisam set account = account where id between 1 and 2000000;

image-20240404110018496

查询语句耗时 0.003 秒。

select * from person_info_myisam where id in (2000001);

image-20240404105725407

那么在更新语句的运行之前使用另外一个session执行查询语句时，查询语句的耗时情况为 21.639 秒。因此也就说明了当表上了写锁后无法再上读锁，将会被阻塞住，直到写锁被释放为止。

image-20240404110244419

写锁也叫排它锁，因为它不仅会阻塞住其它session的查询操作，也会阻塞住增、删、改操作。

select语句默认上的是读锁，如果想要使其上的是写锁（排它锁）也是有办法的，比如：

select * from person_info_myisam where id between 1 and 2000000 for update;

MyISAM以上操作总结：

  • MyISAM引擎默认支持表级锁，不支持行级锁；
  • 表级锁会锁住整张表；
  • 锁分为共享锁和排它锁，上了共享锁后支持继续上共享锁，但不支持上排它锁；上了排它锁后不支持再上共享锁和排它锁。

看完先看MyISAM的实际情况后，再来看支持行级锁的InnoDB引擎的实际情况。

InnoDB引擎支持事务，可以通过session获取锁且暂时不提交事务的方式模拟并发访问的过程；不用像上面那样执行查询或更新很多条数据的 sql 语句，通过较大的耗时来模拟并发操作（因为MyISAM引擎不支持事务）。

MySQL 默认配置是自动提交事务。

show variables like 'autocommit';

image-20240404114914336

InnoDB引擎用的是二段锁，即加锁和解锁分为两个步骤，首先对同一个事务中的一批操作分别加锁，在commit时再统一的解锁。

使用set autocommit = 0;语句可以关闭当前session的自动提交事务，关闭之后需要手动执行commit语句才会提交事务。

对本次测试所需的新创建的两个session查询窗口都执行关闭自动提交事务的语句。

首先在一个session中执行一条查询语句，不提交事务：

select * from person_info_large where id = 3;

在另外一个session中对同样的id执行更新操作：

update person_info_large set title = 'test3' where id = 3;

image-20240404121424103

从执行结果看更新操作竟然没有被阻塞住直接就执行成功了！这是因为InnoDB引擎对select语句进行了改进，在执行select语句时不会对目标行上锁（即非阻塞select），因此不会影响到更新操作。

想要显示的为select上共享锁也是有办法的。

我们先执行两次commit将上面测试的两条语句的事务提交。

接着执行：

# 为 select 语句加上读锁
select * from person_info_large where id = 3 lock in share mode;

再在另一个session中执行：

update person_info_large set title = 'test3' where id = 3;

此时更新语句将会被阻塞住，直到在select语句的session中执行commit语句手动提交事务后才会执行此条更新语句。

但是如果更新的是另外一个id的数据，那么此更新操作不会被阻塞住，说明InnoDB引擎默认使用的是行级锁。如：

update person_info_large set title = 'test4' where id = 4;

如果在两个session查询窗口中使用select查询同样的数据，即便没有手动提交事务也不会阻塞住另外一个的session的select操作，如：

select * from person_info_large where id = 3 lock in share mode;

这种表现与MyISAM引擎表现一致，即上了共享锁（读锁）后支持继续上共享锁（读锁）；同样的上了排它锁（写锁）后不支持再上共享锁（读锁）和排它锁（写锁）。

由于MyISAM引擎只支持表级锁，所以它的共享锁和排它锁都与索引无关，但支持行级锁的InnoDB引擎不同，如果在执行的 sql 中使用到主键索引之外的其它索引时，涉及到的行都会被加上共享锁或排它锁。

例如执行下面这条语句，所有name列的值为Leung Kwok Kuen的行都会被加上行级别的读锁：

select * from person_info_large where name = 'Leung Kwok Kuen' lock in share mode;

此时如果有其它session查询窗口中执行的 sql 语句中只包含name列的筛选条件就会被阻塞住，如：

update person_info_large set title = title where name = 'Leung Kwok Kuen';

但是若where条件中不知一个name = 'Leung Kwok Kuen' 筛选条件则此条更新语句不会被阻塞住。

上面的这种使用非主键索引的情况对排它锁也同样适用。

那么如果where语句中的筛选条件不是索引列而是普通的列情况又是怎样呢？

答案就是如果使用普通列作为筛选条件，就会为整张表加上一个表级锁。

例如执行下面这个这条 sql 的执行期间就会为表加上一个表级的读锁，因为motto只是一个普通的列：

select * from person_info_large where motto = 'test' lock in share mode;

执行这些语句都会被阻塞住，直到执行查询的session中手动提交事务为止：

update person_info_large set title = title where motto = 'testmotto';

update person_info_large set title = title where title = 'testtitle';

update person_info_large set title = title where id = 1;

总结：InnoDB引擎在没有用到索引时使用的是表级锁，用到索引时使用的是行级锁或GAP锁。

InnoDB引擎还支持表级的意向锁，意向锁分为共享读锁（IS），排他写锁（IX），与MyISAM的表锁差不多，主要作用是涉及到表级别的操作时不用轮询每一行看看是否上了行锁。

共享锁和排它锁的兼容性（S 表示共享锁，X 表示排它锁）：

image-20240406001106897

行级锁未必比表级锁更好，锁的粒度越细，使用的代价越高。

MyISAM适合的场景：

  • 频繁执行全表count语句；

    MyISAM引擎会使用一个变量保存整张表的行。

  • 对数据进行增删改的频率不高，查询非常频繁；

    InnoDB引擎不仅支持行级锁，还支持事务的，因此它的开销会更大，同时InnoDB引擎有且必须有一个密集索引（即主键索引），实际数据和索引数据使用同一个文件存储，虽然使用主键索引的效率很高，但是使用其辅助索引首先需要根据辅助索引定位到主键索引的值，然后再根据主键索引定位到实际的数据；而MyISAM引擎使用的是稀疏索引（非聚集索引），实际数据和索引数据是分开保存的，索引保存的是实际数据的指针且辅助索引和主键索引是独立的，因此MyISAM引擎在纯检索的系统（增删改的频率不高）中其性能会好于InnoDB引擎。

  • 没有事务。

InnoDB适合的场景：

  • 数据增删改查都相当频繁；

    增删改只会锁住对应的行，不像MyISAM会锁住整张表。

  • 可靠性要求比较高，要求支持事务；

锁的分类

  • 按锁的粒度划分，可分为表级锁、行级锁、页级锁；

    在MySQL中，数据存储在页面（或称为数据页）中，每个页面通常包含多行数据。页级锁是一种粒度较大的锁定方式，它可以锁定整个数据页，而不是单独的行。

  • 按锁级别划分，可分为共享锁，排它锁；

  • 按加锁方式划分，可分为自动锁、显式锁；

    MyISAM中的表锁以及执行insert、delete、update语句时加上的锁就是自动锁。

    使用select ... for update和lock in share mode这种显式加上的锁就是显式锁。

  • 按操作划分，可分为 DML锁、DDL锁；

    对表数据进行操作的就是 DML锁。

    对表结构进行操作的就是 DDL锁；

  • 按使用方式划分，可分为乐观锁、悲观锁；

    悲观锁指的是数据被外界（本系统中的其它事务或来自外部系统的事务处理）修改持保守悲观态度，因此在数据过程中将会锁定目标数据，它会使用到数据库提供的锁机制，例如全程使用排它锁就是一种悲观锁的实现；悲观并发控制实际上是先取锁再访问的保守策略，为数据的处理的安全提供了保证，但是处理加锁的机制会让数据库产生额外的开销且有可能会发生死锁情况；而且在只读型事务中由于不会产生冲突也没必要加上悲观锁。

    乐观锁相对悲观锁而言，认为一般情况下数据不会发生冲突，所以在数据提交更新时才会检测是否有冲突，如果发生冲突则返回用户错误信息，由用户决定下一步的操作。乐观锁不使用数据库提供的锁机制，常用的实现方式是记录数据的版本，即为数据增加一个版本的标识，一般是通过为数据库表增加一个数字类型的version字段来实现，version字段存储版本号或者时间戳。

    例如使用版本号方式实现数据版本时的更新操作类似下面这种情况：

    # 1.先读取 testinnodb 的数据得到 version 的值为 versionValue
select version from test_innodb where id = 2;  # 假设获取到的 versionValue 是 0 

# 2.每次更新 test_innodb 表中的 money 字段时候，为了防止发生冲突，先去检查 version 再做更新，更新成功的话 version+1
update test _innodb set money = 123, version = 0 + 1 where version = 0 and id = 2;

# 那么此时另外一个程序之前获取到的 versionValue 也为 0，但是它的更新操作晚了一步此时 version 的值已被先于它的更新操作变为了 1，所以此时它再执行更新操作时由于使用的条件还是 version = 0，更新的行数为 0 行发生了冲突，会返回用户错误信息交由用户处理

事务的四大特性

ACID

  • 原子性（Atomic）

    事务包含的全部操作要么全部执行，要么全部失败回滚。要么全做，要么全不做。

  • 一致性( Consistency）

    事务要确保数据库状态从一个一致状态到另一个一致状态；一致状态指的是数据库中的数据要满足完整性约束。

    拿转账操作来说，假设用户A 和用户B 的钱加起来是2000，不管用户 A 和 B 之间如何转账，转几次帐，事务结束后两个用户的钱相加起来还应该是2000.

  • 隔离性（Isolation）

    多个事务执行时，一个事务的执行不能影响别的事务执行。

  • 持久性（Durability）

    事务一旦提交，它对数据库的修改要永久的保存在数据库中；当系统或者介质发生故障时已提交事务的更新不能丢失。

    例如InnoDB会将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件中进行恢复操作，这个文件就是redo log。

事务并发访问产生的问题及事务隔离机制

事务并发访问引起的问题以及如何避免：

  • 更新丢失 一一 MySQL 所有事务隔离级别在数据库层面上均可避免；
  • 脏读 一一 READ-COMMITTED及以上的事务隔离级别可避免；
  • 不可重复读 一一 REPEATABLE-READ及以上的事务隔离级别避免；
  • 幻读 一一 SERIALIZABLE事务隔离级别可避免。

为了复现这些问题出现的情况，这里新建一张表account_innodb，它的表结构及表数据如下：

CREATE TABLE `account_innodb`
(
    `id`      int(2) NOT NULL AUTO_INCREMENT,
    `name`    varchar(10) DEFAULT NULL comment '顾客姓名',
    `balance` int(3)      DEFAULT '0' comment '顾客余额',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

image-20240406213155724

更新丢失

描述：一个事务的更新覆盖了另一个事务的更新。

现在主流的数据库都会自动加锁，因此在数据库层面上所有事务隔离级别均可避免此问题。

它出问题的过程大致如下：

image-20240406205837319

脏读

描述：一个事务读到另一个事务未提交的更新数据。

复现脏读发生的场景首先需要将 MySQL 事务的隔离级别调低变为READ-UNCOMMITTED（读未提交），默认是REPEATABLE-READ（重复读）。

# 设置当前查询会话的事务隔离级别为 READ-UNCOMMITTED，session1 和 session 2 都需要设置
set session transaction isolation level read uncommitted;

# 展示当前查询会员的事务隔离级别
show variables like 'transaction_isolation';

模拟脏读的 sql 执行顺序如下图：

image-20240407212126445

session 1成功回滚，session 2成功提交了事务，最后的结果也是它预期的 1100，从它们各自的角度来看都没什么问题，但对于拥有上帝视角的我们来说是有大问题的！取款失败了，存款成功了，最终正确的余额应该是 1200 才对，结果现在莫名的少了 100，这就是脏读带来的恶果。

解决办法是：使用 READ-COMMITTED及以上的事务隔离级别可避免此问题。

设置 READ-COMMITTED（重复读）事务隔离级别之后，session 2中将读取不到session 1中未提交的数据，因此它会在正确余额的基础上加上 200，即 1000 + 200 = 1200。

不可重复读

描述：事务A 多次读取同一数据，事务B 在事务A 读取期间对数据做了更新并提交，导致事务A 多次读取同一数据的结果不一致。

新建两个session，使用READ-COMMITTED或READ-UNCOMMITTED事务隔离级别。

# 设置当前查询会话的事务隔离级别为 READ-COMMITTED，session1 和 session 2 都需要设置
set session transaction isolation level read committed;

# 设置当前查询会话的事务隔离级别为 READ-UNCOMMITTED
set session transaction isolation level read uncommitted;

image-20240407231956079

session 1对balance字段没有做修改却在第三次读取的时候，读取的结果为 1300 与前两次的不一样，原因就是读取到了session 2中提交的数据，即不可重复度。

拥有上帝视角的我们看来可能并不觉得有什么问题，但是设身处地的从session 1的角度来看，它只是刚好多查询了一次才查询到了最新的数据，如果没有这一次查询它后续的更新操作可能会在原有 1000 余额的基础上更新就会造成数据紊乱的恶果，那现在读到的数据就一定是正确的吗，难免不会有别的事务正在对同一行数据操作，所以取出数据的值并不可靠，这就是不可重复读最大的问题。

解决办法是：使用REPEATABLE-READ（重复读，InnoDB 默认级别）及以上的事务隔离级别可避免此问题。

REPEATABLE-READ支持多次重复读，读到的结果都会是一致的。

# 补充一个知识点

# 如果此时 session2 中已经将余额增加了 300 变为 1300；由于 REPEATABLE-READ 事务隔离级别，此时 session1 中查询的余额值还为 1000
select * from account_innodb where id = 1;

# 但是如果在 session1 中执行更新操作，将余额增加 400，它并不是在查询到的 1000 结果上增加 400，而是会在最新的数据上增加，最终会得到 1700 正确的结果，这就避免了可重复读带来的问题
update account_innodb set balance = balance + 400;

幻读

描述：事务A 读取与搜索条件相匹配的若干行，事务B 以插入或删除行的方式修改事务A 的结果集，导致事务A 看起来像出现幻觉一样。

新建两个session，使用READ-COMMITTED或READ-UNCOMMITTED事务隔离级别。理论上说REPEATABLE-READ也无法避免幻读的现象，但是 MySQL 中的REPEATABLE-READ却避免掉了幻读的现象，至于为什么以后的小节再谈，本次就以READ-COMMITTED为例。

image-20240408001716240

解决办法是：设置最高的SERIALIZABLE事务隔离级别可避免此问题。

设置完毕后，session2再想插入数据会被阻塞，直到session1提交事务或者回滚。

总结

PixPin_2024-04-08_00-21-50

可重复读理论上避免不了幻读，但是在 MySQL 中它通过一种巧妙的方式避免可幻读，这个会在稍后的内容中介绍。

事务隔离级别也不是设的越高越好，因为级别越高虽说安全性也越高，但是耗费的性能也会越大。ORACLE默认为READ-COMMITTED，MySQL默认为REPEATABLE-READ。

当前读和快照读

  • 当前读：select ... lock in share mode、select ... for update、update、delete、insert；
  • 快照读：不加锁的非阻塞读（前提是不为SERIALIZABLE隔离级别），select。

当前读就是加了锁的增删改查语句，它读取的是数据的最新版本，并且读取时还会加锁保证其它并发事务不会修改当前数据。

快照读可以提升并发性能，基本多版本并发控制 MVCC 实现，MVCC 是行级锁的一个变种，它在很多情况下避免了加锁操作，开销更低，但是快照读是多版本的，所以读取到的数据可能并不是最新版本的数据，而是之前的历史数据。

那为什么update、delete、insert也是当前读呢？

以update语句为例，当update语句发给 MySQL 之后，MySQL Server（MySQL 程序实例）会根据where条件读取第一条满足条件的记录，InnoDB返回第一条满足条件的记录并加锁，MySQL Server 接收到之后会发起一个update操作更新这条记录，更新完毕后再继续更新下一条记录直至没有满足条件的记录为止。update操作内部就包含了一条当前读，用来获取数据的最新版本。

image-20240408005558943

来看一个快照读和当前读的实际例子。

从图中可得出当前读和快照读在READ-COMMITTED和REPEATABLE-READ隔离级别下不同的表现形式。image-20240411220510663

那么REPEATABLE-READ隔离级别下的快照读就无法读取到最新的数据了吗？

其实也不一定，REPEATABLE-READ隔离级别快照读读取的时机决定了数据的版本，如果在上图中先执行session2中的更新语句并提交后再执行session1中的快照读语句，则此时查询到的余额也为 1000，并且之后当前事务的快照读语句查询的余额结果都会是 1000。

insert和delete语句的情况和上图中的update情况都是一样的，就不做过多的演示了。

READ-COMMITTED和REPEATABLE-READ隔离级别下InnoDB快照读（非阻塞读）实现方式：

  • 数据行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段；

    MySQL 数据库表中除了真实的数据列之外还有一些额外的字段，其中最关键的就是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID这三个字段。

    • DB_TRX_ID用来标识最近一次对本行记录修改的事务id；删除操作在InnoDB看来也是一次更新操作，更新deleted隐藏列的数据，不会真的就删除了当前行；
    • DB_ROLL_PTR回滚指针表示写入回滚段rollback segment的undo log日志记录；如果一行记录被更新，undolog会记录重建改行记录所必需的信息；
    • DB_ROW_ID行号，包含一个随着新行插入而单调递增的行 id；当由InnoDB自动产生聚集索引时（表既没有主键也没有唯一键InnoDB会自动创建一个隐藏的自增主键字段，即DB_ROW_ID），聚集索引会包括此行id 的值否则行id 不会出现在任何索引中。

  • undo日志；

    存储的是老版数据，当旧的事务需要读取数据时，为了能读到老版本的数据，就需要顺序undo链找到满足其可见性的记录。

    主要分为两种：insert undo log和update undo log。

    insert undo log表示事务新增记录的undo log，只在事务回滚时需要，在事务提交后就可以立即丢弃。

    update undo log表示事务对记录update或delete操作的记录，在事务回滚时和快照读时都需要，不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚数据才会被删除。

    undo log简版演示如下：

    某个事务要更新Field2字段的值为 32，首先使用排他锁锁定该行，然后将改行修改前的数据拷贝一份到undo log中，接着修改Field2字段的值为 32，填写事务idDB_TRX_ID，使用回滚指针DB_ROLL_PTR指向undo log中修改前的行数据。

    image-20240411232244910

    假设数据库还有别的事务使用快照读在读取该日志记录，对应的undo log还未被清除，此时又有一个事务对同一行数据做了修改，将Field3改为 45，它的流程如下：

    PixPin_2024-04-11_23-27-00

    数据的多个版本就是这样实现的，按照时间顺序通过DB_ROLL_PTR由近到远连接起来。

  • read view

    用来做可见性判断。

    例如当执行快照读select语句时会针对查询的数据创建出一个read view来决定当前事务能看到的是哪个版本的数据。

    read view可见性算法是：将要修改记录的DB_TRX_ID取出来与系统其它活跃事务id 作比较，如果大于等于这些活跃的事务id，就通过DB_ROLL_PTR指针取出undo log上一层的DB_TRX_ID直到小于活跃事务id 为止，保证了获取到是当前可见的且是最稳定的数据版本。

    READ-COMMITTED和REPEATABLE-READ隔离级别不同可见性正是由于read view生成时机不同所造成的：

    • REPEATABLE-READ级别下，session在start transaction;之后的第一条快照读就会创建一个read view，将当前系统中活跃的其它事务记录起来，此后再调用快照读的时候都会用同一个read view。
    • READ-COMMITTED级别下，session在start transaction;之后的每条select语句调用快照读的时候都会创建一个新的快照read view。

    这就是READ-COMMITTED级别下可以看到别的事务对表数据的增删改，而REPEATABLE-READ级别下不可以的原因。

读数据时的非阻塞就是 MVCC，全称 Multi-Version Concurrency Control，即多版本并发控制。InnoDB实现了仿造版的 MVCC，因为它没有实现核心的多版本共存，undo log中的内容只是串行化的结果，记录了多个事务的结果，不属于多版本共存；读不加锁、读写不冲突极大的提升了系统的并发性能。

InnoDB 可重复读级别下如何避免幻读

  • 表象：快照读（非阻塞读）—— 伪 MVCC；
  • 内在：next-key 锁（行锁 + gap 锁）。

next-key 锁（行锁 + gap 锁）

  • 行锁；

  • Gap 锁。

    Gap 就是索引树中插入新纪录的间隙。

    Gap 锁锁定一个范围但不包括记录本身，为的是防止同一事务的两次当前读出现幻读的情况。

    Gap 锁在READ-COMMITTED及更低事务隔离级别下是不存在的，这也是它们无法避免幻读的原因。在REPEATABLE-READ和SERIALIZABLE级别下默认都支持 Gap 锁。

这里主要探讨REPEATABLE-READ级别下 Gap 锁出现的场景。

删改查是当前读使用到主键索引或唯一索引时会用 Gap 锁吗？

答案并不唯一，需要分情况讨论。

  • 情况一：如果where条件全部命中，则不会用 Gap锁，只会加记录锁；

全部命中表示精确查询时，要查询的所有记录都有，如select * from tb where id in (1,3,5)，id 为 1,3,5 的数据在表中全部存在即为全部命中。

再比如执行delete from tb where id = 9，只需要锁住id = 9的行即可，即便其它事务执行了新增数据并提交，新增的数据也必要在id = 9之外，不会影响到本次的事务，再做当前读的时候还是只会获取到原先的数据集，不会出现幻读现象，所以此时加行锁就足够了。

如果加锁时用的是主键之外的索引，需要对当前索引以及主键索引对应的记录都上锁。

如下图name列为主键，id列是唯一键，执行delete from tb where id = 9时由于id列是唯一索引，因此它会使用id列的索引作为where条件的过滤，首先会将id = 9的索引加上行锁，然后根据获取的数据将name = 'd'的主键索引（密集索引）也加上一个排他锁。

image-20240412010017294

为什么密集索引上的记录也要加排他锁呢？

如果并发的一条 sql 是根据主键索引来更新的，如update tb set id = 90 where name = 'd';，此时若没有将name = 'd'的主键加锁，那么并发的update语句就感知不到delete操作的存在，就违背了同一记录的更新 / 删除操作需要串行执行的约束。

来看where条件全部命中时的情况：

首先创建上图中的tb表，并初始化相应数据。

CREATE TABLE `tb`
(
    name varchar(10) NOT NULL,
    `id` int(100)    NOT NULL DEFAULT '0',
    PRIMARY KEY (`name`),
    UNIQUE KEY `unique_id` (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into tb (name, id) value ('f', 1);
insert into tb (name, id) value ('h', 2);
insert into tb (name, id) value ('b', 3);
insert into tb (name, id) value ('a', 5);
insert into tb (name, id) value ('c', 6);
insert into tb (name, id) value ('d', 9);

image-20240412012839627

通过explain分析session1中的delete操作使用的确实是唯一键索引。

image-20240412013459876

  • 情况二：如果where条件部分命中或者全不命中，则会加 Gap锁。

首先来看全不命中的情况：

image-20240412014521083

再来看部分命中的情况：

image-20240412015226448

Gap锁会用在非唯一索引或者不走索引的当前读中。

  • 当前读走非唯一索引的情况；

如下图InnoDB表tb1有主键name列和非唯一键id列。

其中有两行数据均为 9，假设事务A 第一次用当前读锁住id = 9的行，如果只锁住选出的两行数据，另外一个事务B 插入了同样id = 9的数据并提交，事务A 再次用当前读选出id = 9的数据时，会取出三条数据，出现了幻读，因此需要引入 Gap锁避免幻读。

Gap 的分布与非唯一索引id的值有很大关系，基本都是左开右闭的区间：(-∞, 2]，(2,6]，(6,9]，(9,11]，(11,15]，(15,+∞)；具体可看官方文档：MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking，在这些区间内一旦上了 Gap锁，那么该区间内就没办法插入数据了，没错 Gap锁就是用来防止插入的。

对于普通非唯一索引来说，只会对要修改行的周边上 Gap锁，如delete from tb1 where id = 9;只会对(6,9]，(9,11]这两个区间上锁，预防幻读的发生。

image-20240412020652672

创建tb1表并初始化数据：

CREATE TABLE `tb1`
(
    name varchar(10) NOT NULL,
    `id` int(100)    NOT NULL DEFAULT '0',
    PRIMARY KEY (`name`),
    KEY `non_unique_id` (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into tb1 (name, id) value ('h', 2);
insert into tb1 (name, id) value ('cc', 6);
insert into tb1 (name, id) value ('b', 9);
insert into tb1 (name, id) value ('d', 9);
insert into tb1 (name, id) value ('f', 11);
insert into tb1 (name, id) value ('a', 15);

image-20240412031501898

  • 当前读不走索引的情况。

当前读不走索引时会对所有的 Gap 都上锁，类似于锁表，同样可以防止幻读。

如下图InnoDB表tb2，有主键索引name列，普通id列，当执行delete from tb2 where id = 9;时该表的所有 Gap 均会被锁住。

image-20240412025302136

创建tb2表并初始化数据：

CREATE TABLE `tb2`
(
    name varchar(10) NOT NULL,
    `id` int(100)    NOT NULL DEFAULT '0',
    PRIMARY KEY (`name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

insert into tb2 (name, id) value ('a', 5);
insert into tb2 (name, id) value ('b', 3);
insert into tb2 (name, id) value ('d', 9);
insert into tb2 (name, id) value ('f', 2);
insert into tb2 (name, id) value ('g', 9);
insert into tb2 (name, id) value ('h', 11);

image-20240412031704224

但是相比表锁，这种 Gap 锁代价更大，通常是需要避免的，会降低数据库的效率。

关键语法讲解

关键语法：

  • GROUP BY

    如果用到了GROUP BY，那么SELECT语句中选出的列要么是GROUP BY中的列要么是使用COUNT、SUM、MAX、MIN、AVG函数的列，此条件只针对同一张表成立。

    列函数对于GROUP BY子句定义的每个组各返回一个结果。

  • HAVING

    通常与GROUP BY子句一起使用，指定GROUP BY之后过滤的条件，如果省略GROUP BY则与WHERE功能相同，支持所有WHERE操作符；

    WHERE过滤行，HAVING过滤组；

    出现在同一 sqI 的顺序：WHERE > GROUP BY > HAVING。

  • 统计相关：COUNT、SUM、MAX、MIN、AVG

为了演示用法，分别创建成绩表score、学生表student、课程表course表并初始化数据，它们之间的关系如下图：

image-20240413131016756

CREATE TABLE `student`
(
    `student_id` int(11) NOT NULL AUTO_INCREMENT,
    `name`       varchar(32) DEFAULT NULL,
    `age`        int(11)     DEFAULT NULL,
    `sex`        varchar(8)  DEFAULT NULL,
    PRIMARY KEY (`student_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into student value (1, 'lilei', 19, 'female');
insert into student value (2, 'hanmeimei', 18, 'male');
insert into student value (3, 'polly', 17, 'female');
insert into student value (4, 'tom', 18, 'male');
insert into student value (5, 'david', 17, 'male');
insert into student value (6, 'luck', 19, 'female');
insert into student value (7, 'jacky', 25, 'male');

CREATE TABLE `course`
(
    `course_id` int(11) NOT NULL AUTO_INCREMENT,
    `name`      varchar(20) DEFAULT NULL,
    PRIMARY KEY (`course_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into course value (1, 'chinese');
insert into course value (2, 'math');
insert into course value (3, 'english');
insert into course value (4, 'physics');

CREATE TABLE `score`
(
    `student_id` int(11) DEFAULT NULL,
    `course_id`  int(11) DEFAULT NULL,
    `score`      int(11) DEFAULT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into score value (1, 2, 78);
insert into score value (1, 3, 67);
insert into score value (1, 4, 67);
insert into score value (2, 1, 52);
insert into score value (2, 2, 81);
insert into score value (2, 3, 92);
insert into score value (2, 4, 67);
insert into score value (3, 1, 52);
insert into score value (3, 2, 47);
insert into score value (3, 3, 88);
insert into score value (3, 4, 67);
insert into score value (4, 2, 88);
insert into score value (4, 3, 90);
insert into score value (4, 4, 67);
insert into score value (5, 1, 52);
insert into score value (5, 3, 78);
insert into score value (5, 4, 67);
insert into score value (6, 1, 52);
insert into score value (6, 2, 68);
insert into score value (6, 4, 67);
insert into score value (1, 1, 52);
insert into score value (5, 2, 72);
insert into score value (7, 2, 72);

写 sql 语句的一个思路：根据题目的意思列出子 sql，然后再将子 sql 拼接起来，即先将关键语法列出来，然后再拼接。

先来看GROUP BY语法的相关用法：

# 查询所有同学的学号、选课数、总成绩
# 分析关键语法：
#   查询所有同学                    -> group by student_id
#   查询学号、选课数、总成绩          -> select student_id, count(course_id), sum(score)
#   学号、课程、分数在 score 表中都有 -> from score

select student_id, count(course_id), sum(score)
from score
group by student_id;

查询结果：

image-20240414231142492

使用explain分析一下刚才之执行的 sql 语句：

explain select student_id, count(course_id), sum(score)
from score
group by student_id;

image-20240414231446710

Using temporary表示group by语句会将按学号划分出来的结果集缓存到一张临时表中，再通过统计函数对这些结果集作处理，最后展现出来。

继续看下面这条 sql 语句。

select语句中查询的 stu.name并未出现在group by s.student_id，但是执行不会报错，原因如下：

如果用到了GROUP BY，那么SELECT语句中选出的列要么是GROUP BY中的列要么是使用COUNT、SUM、MAX、MIN、AVG函数的列，此条件只针对同一张表成立。

# 查询所有同学的学号、姓名、选课数、总成绩
select s.student_id, stu.name, count(s.course_id), sum(s.score)
from score s,
     student stu
where s.student_id = stu.student_id
group by s.student_id;

查询结果：

image-20240414232007917

再来看HAVING语法的相关用法：

# 查询平均成绩大于 60分的同学的学号和平均成绩
# 分析关键语法：
#     平均成绩大于60分  -> having avg(score) > 60
#     查询学号和平均成绩 -> select student_id, avg(`score`)
#     查询...同学      -> group by `student_id`

select student_id, avg(`score`)
from score
group by student_id
having avg(score) > 60;

查询结果：

image-20240414233256385

继续没有GROUP BY时HAVING的作用：

# 取出 student_id 为 1 的学生的成绩情况。

# 如果省略 GROUP BY 则 HAVING 的作用与 WHERE 一样。
select * from score where student_id = 1;
select * from score having student_id = 1;

再来看最后一个例子：

# 查询没有学全所有课的同学的学号、姓名
# 分析关键语法：
#    查询学号、姓名    -> select student_id, name
#    学生表和成绩表    -> from student stu, score s where stu.student_id = s.student_id
#    查询..所有..同学 -> group by student_id
#    没有学全所有课   -> having count(*) < (select count(*) from course)

select stu.student_id, stu.name
from student stu,
     score s
where stu.student_id = s.student_id
group by s.student_id
having count(*) < (select count(*) from course);

查询结果：

image-20240414234623548

参考资料

1
面试 MySQL
版权归属： ldwcool
本文链接： https://blog.ldw.cool/archives/java-mian-shi-zhi-shu-ju-ku
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区