MySql

Sql基础

SQL数据库和NoSql的区别

SQL数据库 是关系型数据库，存储结构化数据于二维表 中，数据间通过预定义关系连接。NoSQL是 非关系型数据库，存储非结构化或半结构化数据，模型灵活多样（如文档、键值对），数据间关系弱 。

它们的主要区别在于事务一致性和扩展性。 SQL遵循ACID原则，提供强一致性，适用于金融等对数据准确性要求极高的事务场景 。 NoSQL通常采用BASE模型，提供最终一致性，更易于水平扩展以应对海量数据和高并发，适合对一致性要求相对宽松、需要高可用和快速响应的场景 。

‍

什么是数据库三大范式

数据库范式是设计关系型数据库时遵循的一系列原则，旨在减少数据冗余、提高数据完整性。最常见的是前三大范式：

1. 第一范式 (1NF): 要求数据库表的每一列都是不可分割的原子数据项。这意味着列中不能包含重复的组或多个值 ，确保数据的基本结构是扁平的。
2. 第二范式 (2NF): 在满足1NF的基础上，要求非主属性必须完全依赖于候选码。对于使用联合主键的表，这意味着任何非主键列不能只依赖于主键的一部分，而必须依赖于整个主键 。
3. 第三范式 (3NF): 在满足2NF的基础上，要求任何非主属性不依赖于其它非主属性。也就是说，非主键列之间不能存在传递依赖，它们都应该直接依赖于主键 。

‍

MySQL联表查询

在MySQL中，我们通过联表查询（JOIN）来根据两个或多个表之间的关联关系，将它们的数据行组合起来。主要的联表类型包括：

1. 内连接 (INNER JOIN): 只返回两个表中 连接条件匹配 的行。
2. 左外连接 (LEFT JOIN): 返回 左表的所有行，以及右表中匹配的行 ；如果右表没有匹配，则右表列显示NULL。
3. 右外连接 (RIGHT JOIN): 返回 右表的所有行，以及左表中匹配的行 ；如果左表没有匹配，则左表列显示NULL。
4. 全外连接 (FULL JOIN): 返回两个表 的所有行，包括非匹配行 。需要注意的是，MySQL没有直接的FULL JOIN语法，通常通过将LEFT JOIN和RIGHT JOIN的结果集使用UNION操作符合并来实现。

基本的联表语法结构是 SELECT ... FROM table1 JOIN_TYPE table2 ON condition，其中 JOIN_TYPE 是上述连接类型之一，condition 指定了两个表之间关联的列。

‍

MySQL中，如何避免重复插入数据

在MySQL中，避免重复插入数据有几种常见的方法，主要取决于你希望如何处理重复情况：

1. 使用 ​UNIQUE​ 约束： 这是最基础且推荐的方式。在需要保证唯一性的列（或列组合）上添加 ​UNIQUE​​ 约束。 当尝试插入重复值时，MySQL会在数据库层面阻止该操作并返回错误，从根本上保证了数据的唯一性。
2. 使用 ​INSERT ... ON DUPLICATE KEY UPDATE​​ ： 这种语句尝试插入数据。如果遇到因 UNIQUE 约束或主键冲突而导致的重复，它不会报错，而是执行指定的 UPDATE 操作来更新已存在的记录。 这适用于需要“插入或更新”（upsert）的场景。
3. 使用 ​INSERT IGNORE​​ ： 这种语法尝试插入数据。如果遇到重复键冲突，它会静默地忽略该条插入操作，不报错也不更新。 这适用于批量导入数据时，希望跳过重复项而不中断流程的场景。

‍

CHAR 和 VARCHAR有什么区别？

CHAR和VARCHAR是MySQL中常用的字符串数据类型，它们的主要区别在于存储方式和长度处理：

1. CHAR： 是 固定长度的字符串类型 。定义时 需要指定一个固定的长度 （例如 CHAR(10)）。存储时，无论 实际字符串多长，都会占用指定的固定字节空间 ，不足的部分会在末尾用 空格 填充。读取时，末尾的填充空格会被去除。CHAR适合存储长度固定或长度差异不大的数据，如邮政编码、状态码等，处理速度相对较快。
2. VARCHAR： 是 可变长度的字符串类型 。定义时需要 指定一个最大长度 （例如 VARCHAR(255)）。存储时，它 只占用实际字符串长度所需的空间 ，外加1或2个字节用于记录字符串的实际长度。VARCHAR适合存储 长度变化较大的文本数据 ，如姓名、地址、描述等，能有效节省存储空间。

总的来说，CHAR以空间换时间（固定空间，处理快），VARCHAR以时间换空间（可变空间，处理稍慢但节省空间）。选择哪种类型取决于存储数据的特性和对空间效率的需求。

‍

varchar后面代表字节还是会字符？

在MySQL中， ​VARCHAR​ ​ 后面括号里的数字代表的是 ​** 字符数 **​ ，而不是字节数 。

例如，VARCHAR(10)表示该字段最多可以存储10个字符。实际占用的字节数则取决于你使用的字符集。

如果使用像ASCII这样每个字符只占1个字节的字符集，那么VARCHAR(10)最多占用10个字节（加上记录长度的开销）。但如果使用UTF-8这样变长编码的字符集，一个字符可能占用1到4个字节，VARCHAR(10)仍然最多存储10个字符，但占用的字节数可能会超过10个，最多可达40个字节（加上长度开销）。

所以， 关键点是数字限制的是字符数量，而实际存储所需的字节数由字符集决定 。

‍

int(1) int(10) 在mysql有什么不同？

在MySQL中，INT(1)和INT(10)之间的区别不在于存储空间或数值范围，而主要在于显示宽度（Display Width） 。

​INT数据类型本身在MySQL中 总是占用固定的4个字节存储空间 ，能够存储相同范围的整数值，无论你写成INT(1)、INT(10)还是INT()。

括号里的数字（如1或10）是显示宽度， 它 ​** 只有在结合 ​ ​ZEROFILL​ ​ 属性使用时才有实际效果 **。如果一个INT列设置了ZEROFILL，MySQL会在显示数值时用前导零填充到指定的宽度。例如，一个​​INT(4) ZEROFILL​​字段存储值​​5​​时，会显示为​​0005​​；存储值​​12345​​时，由于超过了显示宽度，仍会显示完整的​​12345​​。

因此，除非你使用了ZEROFILL并需要控制前导零的显示格式，否则INT(1)和INT(10)在功能上是等价的。这个显示宽度的概念在现代应用开发中已较少使用，通常由应用程序层面控制格式化。

‍

IPv4地址如何在数据库里存储？

在数据库中存储IP地址，主要有两种常见方式：

1. 使用字符串类型： 直接将IP地址作为字符串存储，例如 使用 ​ ​VARCHAR(15)​ ​ 来存储IPv4地址 。这种方式的优点是直观易懂，插入和查询都比较直接。缺点是 占用空间相对较大 ，且进 行范围查询或排序时效率较低，字符串比较不如数字比较快 。
2. 使用整数类型： 将IP地址转换为一个 32位无符号整数进行存储 ，通常使 用 ​ ​INT UNSIGNED​ ​ 类型。MySQL提供了 ​ ​INET_ATON()​ ​ 函数将IP字符串转为整数，以及 ​ ​INET_NTOA()​ ​ 函数将整数转回IP字符串。这 种方式的优点是占用空间小（IPv4固定4字节），并且整数的比较和范围查询效率非常高。缺点是存储的数据不够直观，需要通过函数进行转换才能方便查看或显示。

总的来说，如果对存储空间和查询性能有较高要求，特别是需要进行IP范围查询，推荐使用整数类型存储。如果数据量不大，且更看重直观性和开发便捷性，字符串类型也是可行的选择。

‍

什么是外键约束

外键约束是关系型数据库中用于维护表与表之间关联关系、确保数据完整性和一致性的一种重要机制。

它通过在一个表的列（即外键）中存储另一个表的主键值来实现连接。外键约束的核心作用是强制引用完整性（Referential Integrity），这意味着外键列中的值必须在被引用的表的主键列中存在，或者为NULL。

这样做的好处是多方面的：它能有效防止创建“孤儿”记录或引用不存在的数据，例如确保一个订单引用的客户ID在客户表中是真实存在的。同时，外键约束还可以定义关联数据在主表记录被删除或更新时的行为（如级联删除、限制删除等），进一步维护数据间的同步和一致性。

MySQL的关键字in和exist的区别

在MySQL中，IN和EXISTS都用于子查询，但工作方式不同：

1. ​​IN​​ : 检查一个值是否在子查询返回的结果集中。它先执行子查询，得到一个集合，再与外部查询的值进行匹配。
2. ​​EXISTS​​ : 检查关联子查询是否返回任何行。它不关心具体值，只判断是否存在满足条件的关联记录。

核心区别： IN是值匹配，EXISTS是存在性判断。通常，当子查询返回大量数据时，EXISTS的性能更优，因为它一旦找到匹配就停止。选择哪个取决于子查询的大小和是否需要关联判断。

MySQL常用函数

MySQL提供了丰富的内置函数，用于处理和操作数据。我可以列举一些常用的基本函数，它们大致可以分为几类：

1. 字符串函数： 用于处理文本数据。例如：

   • ​CONCAT()：连接多个字符串。
   • ​LENGTH()：获取字符串的长度。
   • ​SUBSTRING()：截取子字符串。
   • ​REPLACE()：替换字符串中的特定部分。

2. 数值函数： 用于进行数学计算。例如：

   • ​ABS()：获取绝对值。
   • ​ROUND()：四舍五入。
   • ​CEIL() / FLOOR()：向上/向下取整。

3. 日期和时间函数： 用于处理日期和时间数据。例如：

   • ​NOW() / CURDATE() / CURTIME()：获取当前日期和时间、日期、时间。
   • ​DATE_FORMAT()：格式化日期和时间显示。
   • ​DATEDIFF()：计算两个日期之间的天数差。

4. 聚合函数： 用于对一组行的值进行计算，通常与GROUP BY子句一起使用。例如：

   • ​COUNT()：计算行数或非NULL值的数量。
   • ​SUM()：计算总和。
   • ​AVG()：计算平均值。
   • ​MAX() / MIN()：获取最大/最小值。

这些函数是进行数据查询、分析和转换的基础工具，能够极大地提高SQL操作的灵活性和效率。

‍

SQL执行顺序

理解SQL查询语句的执行顺序非常重要，它决定了数据如何被处理和过滤。虽然我们写SQL时通常按照SELECT, FROM, WHERE这样的顺序，但数据库实际执行时遵循的是一个不同的逻辑顺序。

这个逻辑执行顺序大致如下：

1. ​​FROM​: 首先确定数据来源，即从哪个表或哪些表获取数据。
2. ​​JOIN​ / ​ON​: 如果涉及多个表，接着根据JOIN类型和ON条件将这些表的数据进行关联和组合，生成一个临时的中间结果集。
3. ​​WHERE​: 对上一步生成的中间结果集进行行过滤。只保留满足WHERE条件的行。
4. ​​GROUP BY​: 如果使用了GROUP BY，则将经过WHERE过滤后的数据按照指定的列进行分组。
5. 聚合函数 (如 ​COUNT​​ , ​SUM​​ , ​AVG​​ , ​MAX​​ , ​MIN​​ ) : 在分组后（或没有分组时对所有行），计算每个组（或整个结果集）的聚合值。
6. ​​HAVING​: 对GROUP BY产生的分组进行过滤。这与WHERE不同，HAVING用于过滤组，通常包含聚合函数的条件。
7. ​​SELECT​: 选择需要输出的列或表达式。此时可以引用原始列、计算出的表达式或聚合函数的结果。
8. ​​DISTINCT​: 如果使用了DISTINCT，则在SELECT选出的结果集中移除重复的行。
9. ​​ORDER BY​: 对最终的结果集进行排序。
10. ​​LIMIT​ / ​OFFSET​: 最后，根据LIMIT或OFFSET限制返回的行数。

‍

存储引擎

执行一条SQL请求的过程是什么

‍

当MySQL接收到一条SQL查询请求时，整个处理流程可以分为几个关键步骤：

1. 连接器：管理客户端连接并进行身份验证。
2. 解析器：分析SQL语句的语法和结构，生成内部语法树。
3. 预处理器：检查表和列的有效性及权限。
4. 优化器：生成多种执行计划，并选择成本最低的一个。
5. 执行器：根据选定计划，调用存储引擎执行数据操作，处理结果后返回给客户端。

简而言之，MySQL处理SQL查询的过程是：连接认证 -> 解析SQL -> 验证逻辑 -> 优化执行计划 -> 执行并返回结果。

MySQL多种存储引擎

• InnoDB： InnoDB是MySQL的默认存储引擎，具有ACID事务支持、行级锁、外键约束等特性。它适用于高并发的读写操作，支持较好的数据完整性和并发控制 。
• MyISAM： MyISAM是MySQL的另一种常见的存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作的场景。 然而， MyISAM不支持事务、行级锁和外键约束， 因此在并发写入和数据完整性方面有一定的限制。
• Memory： Memory引擎将数据存储在 ​** 内存 **​ 中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束

为什么MySQL选择InnoDB作为默认引擎

首先， 也是非常重要的一点，InnoDB ** 支持ACID事务 **​ 。 这意味着它能保证数据操作的原子性、一致性、隔离性和持久性，这对于需要保证数据可靠性和完整性的业务场景是必不可少的， 而MyISAM等引擎则不具备事务能力。

其次， InnoDB提供了 ** 行级锁定 **。相比于MyISAM的表级锁，行级锁大大减少了在高并发读写环境下的锁冲突，显著提高了数据库的 并发处理能力。

此外，In noDB具备强大的 ** 崩溃恢复 ** 能力。它通过redo log等机制 ，能够在数据库意外停止后恢复到一致状态，有效防止数据丢失，保证了数据的 持久性和可靠性。

‍

InnoDB和MyISAM存储引擎区别

首先，也是最核心的区别在于 事务支持。InnoDB 完全支持 ACID 事务，而 MyISAM 则不支持事务。

其次是 锁粒度。InnoDB 实现了 行级锁定，这意味着在并发写入时，只有被修改的行会被锁定，MyISAM 只支持表级锁定，一个写操作会锁住整个表，严重限制了并发写入的能力。

再者是 索引结构。InnoDB 使用 聚簇索引，数据行直接存储在主键索引的叶子节点上，通过主键查询效率很高，但辅助索引需要额外查找主键。MyISAM 使用 非聚簇索引，索引和数据文件是分离的，索引存储的是数据行的物理地址。

此外，在 ​COUNT(*)​ 效率 方面， MyISAM 由于在元数据中保存了表的总行数，执行 ​COUNT(*)​ 通常非常快。而 InnoDB 需要扫描或估算行数，对于精确的 ​COUNT(*)​ 可能较慢 。

‍

数据管理里，数据文件大体分成哪几种数据文件

最基本的文件类型是存储表结构（元数据） 的文件。对于每张表，MySQL都会生成一个 .frm 文件，这个文件就记录了这张表的列定义、数据类型、索引定义等结构信息。

其次是存储表数据和索引的文件。这部分根据存储引擎的不同而有所差异。对于默认的 InnoDB 引擎，数据和索引可以存储在两种地方：
一种是独占表空间，即每张表的数据和索引都存储在一个独立的 .ibd 文件中。这是MySQL 5.6.6版本后的默认设置，也是推荐的方式。
另一种是共享表空间，所有InnoDB表的数据和索引都存储在一个或多个共享的 ibdata 文件中。

此外，还有一些其他类型的文件，比如存储数据库默认字符集和校验规则的 db.opt 文件，以及各种日志文件（如binlog、redo log、undo log等），这些日志文件对于数据恢复、复制和事务处理至关重要，但它们主要服务于数据库的运行和管理，与具体的表数据存储文件有所区别。

所以， 核心的数据文件类型主要就是存储 ​** 表结构 ​ 的 ​.frm​ 文件，以及存储 ​ 表数据和索引 **​ 的 ​.ibd​ 文件（或共享的 ​ibdata​ 文件） 。

‍

‍

‍

‍

索引

什么是索引

索引是一种有序的数据结构，它就像书的目录一样，可以帮助 MySQL 更快地找到你想要的数据，而不需要去扫描整张表。

它的主要作用是：

1. 提高查询速度： 通过索引，MySQL 可以跳跃式地定位到数据，把原来可能 O(N) 的全表扫描变成像 B+ 树这样的结构的查找（通常是 O(logN) 的效率），大大减少了需要扫描的数据量和磁盘 I/O 次数。
2. 降低排序成本： 索引本身就是有序的，如果查询需要排序的列正好在索引里，MySQL 可以直接利用索引的顺序，减少额外的排序操作（Using filesort），降低 CPU 消耗。

好的，MySQL 的 InnoDB 存储引擎选择 B+ 树作为索引结构，主要是因为 B+ 树非常适合磁盘存储的数据库。

‍

索引的分类

首先，从 数据结构 来看，MySQL主要支持 ** B+Tree 索引 ​ 、 ​ Hash 索引 ** 和 ** Full-text 索引 **。其中，B+Tree 索引 是InnoDB和MyISAM等大多数存储引擎默认和最常用的索引类型，它非常适合范围查询和排序。Hash索引适用于等值查询，而Full-text索引用于全文搜索。

其次，从 物理存储 的角度，索引分为 ** 聚簇索引 (Clustered Index) ** 和 ** 二级索引 (Secondary Index) ** 。在InnoDB中，聚簇索引 通常就是主键索引，它的叶子节点直接存储了整行的数据记录。而 二级索引 的叶子节点存储的是主键值，通过二级索引查找数据需要先找到主键，再通过主键去聚簇索引查找完整数据，这个过程称为 回表。

再者，根据 **== **** 字段特性 **​ ，索引可以分为 ** **** 主键索引 ​ （基于主键，唯一且非空）、 ​ 唯一索引 ​ （值必须唯一，允许NULL）、 ​ 普通索引 **​ （最基本的索引，无特殊限制）以及 ** **** 前缀索引 **​ （对字符串字段的前N个字符建立索引）。==

最后，按 **== **** 字段个数 **** ** 分类，有 ** **** 单列索引 ​ （只在一个字段上）和 ** **** 联合索引 (Composite Index)== （在多个字段上组合创建）。联合索引在使用时遵循 最左匹配原则，即查询条件必须从索引的最左边字段开始连续匹配才能有效利用索引。

‍

‍

MySQL为什么用B+树结构？和其他结构比的优点？

B+Tree 的核心特性使其非常适合数据库索引：

1. 所有数据都存储在叶子节点： 非叶子节点只存储索引键值，用于导航搜索路径。这使得** 非叶子节点可以存储更多的索引项 **，从而降低树的高度，减少磁盘I/O次数。
2. 叶子节点通过链表连接： 所有叶子节点构成一个有序链表（通常是双向链表） 。这使得范围查询和顺序扫描非常高效，可以直接沿着链表遍历，而无需回到父节点。
3. 查找性能稳定： 无论查找任何数据，都需要从根节点走到叶子节点，路径长度一致，因此查询性能稳定。

和其它结构比，B+ 树的优点在于：

1. 相比 B 树：

   • 【核心区别 - 数据存储】 ：B+ 树 仅在 ** 叶子节点 ** == 存储 完整数据记录或指向数据的指针。而 非叶子节点 (内节点) 仅存储 **** 索引键和指向子节点的指针 。相比之下， B 树的非叶子节点也存储数据==。
   • 【优势 - 结构与性能】 ：由于非叶子节点不存储数据，B+ 树 的非叶子节点可以存放** 更多的索引键 **，这意味着其 扇出度 (Fan-out) 更高。更高的扇出度导致 ** 树的高度更矮 **。在数据库系统中，每次访问一个节点通常需要一次 磁盘 I/O 操作，因此 树的高度直接影响查询所需的磁盘 I/O 次数。更矮的 B+ 树意味着更少的磁盘 I/O，从而 提高了查询效率。
   • 【优势 - 范围查询】 ：** B+ 树的所有叶子节点通过链表连接 **。这使得 ** 范围查询和顺序扫描极为高效 **，可以直接在叶子链表上快速遍历所需范围的数据，这是 B 树不具备的关键特性。

2. 相比二叉树 (如 AVL, 红黑树)：

   • 【核心区别 - 树高与磁盘 I/O】 ：二叉树是高瘦结构，即使平衡，其高度相对较高。B+ 树 作为多叉树，其 == **** 高度远低于二叉树==。由于数据库索引主要存储在 磁盘 上，每次节点访问都需要昂贵的 磁盘 I/O 操作。B+ 树的低树高 特性 显著减少了磁盘 I/O 次数，这是其作为 磁盘数据库索引的核心优势。

3. 相比哈希表：

   • 【核心区别 - 有序性与查询类型】 ：哈希表 在 等值查询 (如 WHERE id = 1) 时性能极高，理论上可达 O(1) 的平均时间复杂度。但哈希表是 无序存储 的。
   • 【优势 - 查询能力广度】 ：因此，** 哈希表无法高效支持范围查询 ** (如 WHERE score > 100)、** 模糊查询 ** 或 数据排序。B+ 树 既能高效支持 等值查询 (时间复杂度为 ** O(logN) ** )，又能凭借其 叶子节点的有序性和链表结构 极高效地支持 ** 范围查询、排序和前缀模糊匹配 **。这使得 B+ 树的 适用场景更广泛。

4. 相比跳表 (Skip List)：

   • 【核心区别 - 磁盘适应性】 ：跳表在 ** 内存数据结构 ** 中表现优秀。但在 磁盘存储 场景下，如果直接将其结构映射到磁盘块，由于其层结构可能导致 访问路径上的节点较多 (即 ** 层高相对较高 **)，所需的 磁盘 I/O 次数 通常会多于 B+ 树。因此，B+ 树 因其固有的 低树高和高扇出度 特性，更适合作为 磁盘数据库的索引结构。

什么是联合索引，创建联合索引时应该注意什么

将将多个字段组合成一个索引， 该索引就被称为联合索引。创建联合索引时，最关键的是要注意索引列的顺序。这是因为MySQL遵循最左匹配原则（Leftmost Prefix Matching） ：只有查询条件从联合索引的最左边字段开始连续匹配时，索引才能被有效利用。因此，在确定字段顺序时， 应将 ​** 区分度（Selectivity）高 ​ 的字段放在联合索引的 ​ 前面 **。区分度高的字段能更快地过滤掉大量数据，从而提高索引效率。如果最左边的字段区分度很低，索引效果会大打折扣。

简而言之：顺序很重要，高区分度的字段放前面。

‍

什么是聚簇索引什么是非聚簇索引？

MySQL 里关于索引的存储方式，主要分两种：

1. 聚簇索引 (Clustered Index)： 简单说，就是** 数据行和索引是存放在一起的 ​ 。它的 B+ 树的 ​ 叶子节点 ​ 直接 ​ 存着整行的数据记录 ​ 。一个表 ​ 只能有一个 ​ 聚簇索引 ，在 InnoDB 存储引擎里，通常就是表的 主键 ​索引**。数据行的物理存储顺序基本上就是按照聚簇索引的顺序排列的。
2. 非聚簇索引 (Non-clustered Index)： 也叫二级索引。这种索引是独立于数据存储的。它 的 B+ 树的 ​** 叶子节点 ​ 存的 ​ 不是整行数据 ​ ，而是 ​ 索引的 key 值 ​ 以及 ​ 对应行的主键值 ​ （或者指向聚簇索引的指针） 。一个表可以有多个**非聚簇索引，就是我们平时除了主键外自己创建的其他索引。

‍

如果聚簇索引的数据更新，它的存储要不要变化？

• 如果更新的数据是非索引数据，也就是普通的用户记录，那么存储结构是不会发生变化
• 如果更新的数据是索引数据，那么存储结构是有变化的，因为要维护 b+树的有序性

‍

MySQL的 InnoDB 存储引擎中，主键 就是 聚簇索引 (Clustered Index)吗

是的，在MySQL的 InnoDB 存储引擎中，主键 就是 聚簇索引 (Clustered Index) 。

1. 数据存储方式： 表中的 数据行 在物理上是直接存储在主键索引的 B+Tree 的叶子节点 中的，并且数据行是按照主键的顺序进行物理排序的。

2. 唯一性： 一张InnoDB表只能有一个聚簇索引。

3. 主键的选择：

   • 如果你显式定义了主键，InnoDB就会使用它作为聚簇索引。
   • 如果你没有定义主键，InnoDB会尝试查找第一个非空的唯一索引作为聚簇索引。
   • 如果两者都没有，InnoDB会自动生成一个隐藏的、自增的6字节ID作为聚簇索引。

在InnoDB表中选择自增ID还是UUID作为主键

在InnoDB存储引擎中，通常优先推荐使用自增ID作为主键，而非UUID。

核心原因在于InnoDB使用** 聚簇索引 ，数据行物理存储在B+Tree的叶子节点，并严格按 主键顺序 **排列。

• 自增ID：新插入值递增，总是追加到索引末尾。实现顺序写入。

  • 优点：最大化页面填充，避免页分裂，减少随机I/O，提升写入性能。

• UUID：随机生成，新值无序。导致数据插入到索引的随机位置。

  • 缺点： 频繁 ​** 随机I/O ​ ，大量 ​ 页分裂 ​ ，产生 ​ 数据碎片 **，影响性能。

此外，UUID作为 长字符串 ，占用更多存储空间。更大的主键会减少每页存储的行数，可能导致索引树更高，增加查询的磁盘I/O。字符串比较效率也低于整数。

‍

什么是回表查询嘛？

然后，回表查询呢，其实就和非聚簇索引有关系。当你通过非聚簇索引去查数据时，比如 SELECT * FROM table WHERE index_col = 'value'。

• 首先会通过非聚簇索引找到 index_col = 'value' 对应的** 主键值 **。
• 但是非聚簇索引的叶子节点没有整行数据（因为只存了主键）。
• 如果你需要获取整行数据的其他列（比如 SELECT *），就得拿着这个主键值，再去聚簇索引里找一遍，才能找到完整的记录。

这个** 拿着主键值，再回到聚簇索引去查找整行数据 ​ 的过程，就叫做 ​ 回表查询 **。它是用非聚簇索引查询时可能产生的一个额外步骤。

‍

什么是覆盖索引

• 定义： 指一个索引包含了** 查询所需的所有列 **（例如 SELECT, WHERE, ORDER BY 中使用的列）。
• 核心机制： 使得数据库可以直接从索引结构中获取所有需要的数据，** 无需访问表中的实际数据行 **​ （即 ** 无需回表查询 **​ ）。
• 优势： 显著减少了** 磁盘 I/O 操作 ，从而大幅提升查询性能**。

【补充说明】 在某些存储引擎（如 InnoDB）中，辅助索引的叶子节点会自动包含主键。因此，如果查询所需的列是索引列以及主键列，这也构成覆盖索引。

【示例】

表结构：user (id INT PRIMARY KEY, name VARCHAR, age INT)
索引：CREATE INDEX idx_name_age ON user (name, age);
查询：SELECT age FROM user WHERE name = 'TestUser';​

这个查询需要 name (WHERE条件) 和 age (SELECT列)。由于 idx_name_age 包含了 name 和 age 两列，数据库可以直接通过这个索引找到符合条件的行，并直接从索引中获取 ​age​ 值，无需回表访问完整的用户数据行。

‍

MYSQL超大分页怎么处理？

处理 MySQL 超大分页（LIMIT offset, count 中的 offset 非常大），直接查非常慢，因为它要读取并跳过前面大量数据。

我们的方案是利用** 覆盖索引 + 子查询 **：

1. 先用一个子查询，只查出当前页需要数据的主键 ID（或者只需要排序/过滤需要的列+主键），并且用 LIMIT 限制范围。这个子查询可以利用索引，避免读取不必要的数据。
2. 外面再用主查询，拿着子查询找到的这几个主键 ID，去主表快速精确地获取完整的行数据。

这样就避免了 MySQL 扫描和跳过大量前面的数据，而是直接定位到目标页的主键，再高效取数。

‍

索引创建原则有哪些

1. ** 给重要的表和字段建： ** 主要给 ​** 数据量比较大 ​ ，而且 ​ 查询非常频繁 **​ 的那些表建索引 。重点是那些经常出现在 ** WHERE 条件 **​ 、需要 ** ORDER BY 排序 **​ 或者需要 ** GROUP BY 分组 **的字段。
2. 字段要区分度高： 选择那些字段值 ​** 重复度比较低 **​ 的列建索引，比如用户ID、订单号等 。区分度越高，索引过滤效果越好。
3. 考虑联合索引： 如果你的查询经常会同时用到多个字段作为条件，可以 考虑创建 ​** 联合索引 **​ ，这样能覆盖更多的查询场景 。
4. 控制索引数量： ** 不是索引越多越好 ​ 。索引会占用磁盘空间 ，更重要的是， 每次修改数据 ​ （增、删、改）时，索引也需要跟着更新，这会 ​ 降低写入性能 **​ 。 所以要控制索引数量，只建那些真正需要的。
5. 对于比较长的字符串字段，可以考虑 ​** 使用前缀索引 **，不用把整个长字符串都放到索引里，节省空间。

总之，创建索引的原则就是建在对查询性能提升最明显的地方，同时要控制数量，平衡读写性能。

‍

索引失效的情况

索引虽然能提高查询效率，但在一些情况下它会失效，或者说 MySQL 不会选择使用它，导致查询变慢。常见的情况有：

1. ** 违反最左前缀原则： ** 如果你用的是 ​** 联合索引 ​ （比如 ​(a, b, c)​ ​ ），但你的查询条件 ​ 没有从索引的最左边列开始 ​ 匹配，或者 ​ 跳过了中间的列 **，那么索引就只能用到部分或者完全用不上。比如查 WHERE b = 1 或 WHERE a = 1 AND c = 3。
2. ** 范围查询右边的列失效： ** 在联合索引中，如果你的查询条件对某列使用了 ​** 范围查询 **​ （比如 ​>​ ​ **== ** ​<​ **​ ** ** ** ​BETWEEN​ ​ ** 或 ** ** ​LIKE 'abc%'​ ​ ），那么 ​ 这个条件右边的列==**就无法再使用索引了。比如索引是 (a, b, c)，你查 WHERE a = 1 AND b > 10 AND c = 5，那么 c 列的索引会失效。
3. ** 在索引列上进行运算或使用函数： ** 如果你在查询条件中，对索引列进行了 ​** 数学运算、函数操作 ​ 或者 ​ 类型转换 **，MySQL 往往不会使用索引。比如 WHERE indexed_col * 2 = 10 或 WHERE DATE(indexed_time) = '...'。
4. ** 字符串类型的索引列不加引号： ** 如果你的索引列是字符串类型，但在查询条件中 ​** 没有给值加单引号 ​ ，MySQL 可能会进行 ​ 隐式的类型转换 **，这就像在索引列上使用了函数，导致索引失效。
5. ​** ​LIKE​ **​ == **** 查询以 **** ** ** **** ​%​ **** ​ ** ** **** 开头： **** ** 如果 ** ** ​LIKE​ ​ ** 查询的 ​ 通配符 **** ** ** **** ​%​ **** ​ ** ** **** 放在了最前面==（比如 WHERE indexed_col LIKE '%abc'），由于索引是按从左到右的顺序排序的，MySQL 无法利用索引进行快速查找，通常会导致索引失效进行全表扫描。

联合索引ABC，现在有个执行语句是A = XXX and C < XXX，索引怎么走

根据最左匹配原则，A可以走联合索引，C不会走联合索引，但是C可以走索引下推

联合索引(a,b,c) ，查询条件 where b > xxx and a = x 会生效吗

查询优化器会重排条件，使得最左边的等值条件 ​a = x​ 先被处理，从而激活联合索引，后续的 ​b > xxx​ 条件也能在 ​a​ 确定的范围内继续利用索引进行过滤。因此，索引是生效的，并且 ​a​ 和 ​b​ 字段都利用到了联合索引。

联合索引 (a, b，c)，where条件是 a=2 and c = 1，能用到联合索引吗？

会用到联合索引，但是只有 a 才能走索引，c 无法走索引，因为不符合最左匹配原则。虽然 c 无法走索引， 但是 c 字段在 5.6 版本之后，会有索引下推的优化，能减少回表查询的次数。

‍

什么是索引下推

索引下推（ICP）是针对联合索引的一个优化。它的核心目的非常直接：减少不必要的回表次数。

在没有这个优化时，如果 WHERE 条件不完全符合最左匹配（比如跳过中间字段），MySQL 在利用索引定位后，可能需要回表去取很多完整的数据行，再在这些数据行上进行过滤。有了 ICP，MySQL 会把 WHERE 条件中只涉及索引列的那部分过滤逻辑，“下推”到存储引擎层，在回表之前就先在索引条目上应用这些过滤。只有通过过滤的索引条目，才会去回表读取完整的数据行。

举个例子： 联合索引 (a, b, c)，查询 WHERE a=2 AND c=1。没有索引下推，MySQL 找到所有 a=2 的索引项就去回表了。有了索引下推，它找到 a=2 的索引项后，会先看看这个项里的 c 是不是 1。只有当 a=2 并且 c=1 都符合时，它才真的去回表。

这样，通过在索引层提前过滤，大大减少了回表到数据行的开销，从而提升了查询性能。

总结一下：ICP 就是在联合索引查询中，将部分 WHERE 条件的判断工作从服务器层推到存储引擎层，在回表前先利用索引项进行过滤，从而减少回表次数，提升效率。

‍

‍

‍

Skip Scan Range Access Method

确实，MySQL 8.0.13引入了Skip Scan Range Access Method（跳过扫描范围访问方法），为联合索引的使用提供了更多的灵活性。这一特性特别有助于那些联合索引中最左字段没有直接等值查询条件，但后续字段有条件的情况。

简单来说：

• 传统情况：如果联合索引 (a, b, c) 中最左字段 a 没有出现在查询条件中，那么这个联合索引通常不会被使用，可能导致全表扫描。
• 有了 Skip Scan 后：即使 a 字段没有具体的等值条件，只要后续字段（如 b 或 c）有条件，MySQL 可以对 a 字段的所有可能值进行范围扫描，而不需要对整个表进行扫描。这意味着，对于每一个不同的 a 值，** MySQL 将单独考虑 ** ** ​b​ ** ** 和 ** ** ​c​ ** ** 的条件，从而有可能利用到联合索引中的 ** ** ​b​ ** ** 和 ** ** ​c​ ** ** 部分，减少不必要的全表扫描。 **

‍

如果一个列即使单列索引，又是联合索引，单独查它的话先走哪个？

mysql 优化器会分析每个索引的查询成本，然后选择成本最低的方案来执行 sql。

‍

前缀索引

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

‍

场景

如何定位慢查询

好的，定位 MySQL 慢查询，主要有两种方式。

• 可以使用了运维监控系统 Skywalking。通过它的调用链追踪，能非常快地找到是哪个接口响应慢，并进一步下钻看是这个接口里具体哪条 SQL 的执行耗时高，这样就能精准定位问题 SQL 了。
• 另外一种标准的方式是利用 ** MySQL 自己的慢查询日志功能 **​ 。可以在 MySQL 的配置文件里开启，设置一个时间阈值（比如我们之前项目设的 2 秒），所有执行时间超过这个阈值的 SQL 语句都会被记录到慢查询日志文件里。然后我们就可以分析这个日志文件来找到慢查询进行优化。

‍

mysql的explain有什么作用？

explain 是查看 sql 的执行计划，主要用来分析 sql 语句的执行过程，比如有没有走索引，有没有外部排序，有没有索引覆盖等等。

如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

‍

• possible_keys 字段表示** 可能用到的索引 **；
• key 字段表示 实际用的索引 ，如果这一项为 NULL，说明没有使用索引；
• key_len 表示 索引的长度 ；
• rows 表示 扫描的数据行数 。
• type 表示数据 扫描类型 ，我们需要重点看这个。

explain里extra 显示的结果，这里说几个重要的参考指标：

• Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
• Using temporary： 使了用临时表保存中间结果 ，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by 。效率低，要避免这种问题的出现。
• Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引， 避免了回表操作 ，效率不错
• Using index condition ：使用了索引但是需要回表查询

type 字段就是描述了找到所需数据时使用的扫描方式是什么，从效率低到高通常是 ALL (全表扫描) -> index (全索引扫描) -> range (索引范围扫描) -> ref/eq_ref (索引查找) -> const (常量查找)。我们一般希望能达到 range 或更高的级别

1. ​​const​ / ​eq_ref​​ ： 这是非常高效的方式。

   • ​const: 通过** 主键或唯一索引 ​ 用 ​ 常数值 进行查找，直接就能定位到 唯一的一条 **匹配的行，速度极快。比如 WHERE id = 1 且 id 是主键。
   • ​eq_ref: 通常用在** 多表联查 时，根据前面表的结果，通过 主键或唯一索引 去另一张表找 唯一匹配 **的行。效率也很高。

2. ​​ref​​ ： 通过** 非唯一索引 进行查找，或者使用唯一索引的部分前缀**。虽然使用了索引，但一个值可能对应** 多条记录 **，所以需要查找多行。不过因为它走了索引，比全表扫描快得多。比如 WHERE city = '北京' 且 city 是非唯一索引。

3. ​​range​​ ： 表示使用了** 索引进行范围扫描 **。比如 WHERE score BETWEEN 60 AND 100 或者 WHERE date > '2023-01-01'。它只扫描索引中指定范围的数据，效率比全索引扫描和全表扫描高很多。我们通常希望查询至少能达到 ​range​ 这个级别。

4. ​​index​​ ： 全索引扫描。它虽然扫描的是索引而不是整个表，但需要扫描** 索引中的所有条目 **。如果索引很小或者查询只需要索引中的列（覆盖索引），效率还行。但如果索引很大，或者不是覆盖索引，效率也不高。

5. ​​ALL​​ ： 全表扫描。这是最差的情况，意味着 MySQL 必须读取表中的** 所有行 **来找到匹配的数据。在大型表上基本是不可接受的，需要重点优化避免。

所以，看 type 字段，我们目标是尽量让它在 range 或更高效的级别，避免出现 index 和 ALL。

‍

当一条 SQL 语句执行很慢的时候，如何分析

当一条 SQL 语句执行很慢的时候，我会用 MySQL 自带的 EXPLAIN 工具来分析。EXPLAIN 会显示这条 SQL 的执行计划，通过看它的输出，可以分析慢的原因：

1. ** 看 ** **== **** ​key​ ****​ ** ** **** 和 **** ** ** **** ​key_len​ **​ ** ** **** 字段==​ ： 先确认有没有命中索引，或者索引长度是否合适。如果 key 是 NULL，说明没用索引，这通常是大问题。
2. ** 看 ** **== **** ​type​ **​ ** ** **** 字段： **** ** 这是最重要的。它显示了 MySQL 是 ​ 怎么扫描数据 **​ 的==，比如是不是出现了效率很低的扫描方式，像 ALL (全表扫描) 或 index (全索引扫描)。我们希望至少能看到 range 或更高效的类型。
3. 看 ​Extra​ 字段： 里面有一些额外的信息。比如出现 **== ** ​Using filesort​ ​ ** 或 ** ** ​Using temporary​ ​ ** 就表示需要 ​ 额外的排序或使用了临时表 **，这些都很耗时，需要避免。 如果出现 ** ** ​Using index​ ​ ** 则很好，说明使用了 ​ 覆盖索引==，避免了回表操作。

通过分析 EXPLAIN 输出的这些关键信息，就能判断 SQL 是不是因为索引没用对、扫描方式低效或者有额外操作导致变慢，然后根据情况去优化索引或改写 SQL。

谈一谈你对sql的优化的经验

1. 分析查询语句 (首要步骤):

   • 使用 EXPLAIN 命令分析慢查询的 SQL 执行计划。

   • 重点关注执行计划中的关键信息，如：

     • ​type 列：是否出现了低效的访问方式，如 全表扫描 (ALL) 。
     • ​key 列：是否使用了预期的 索引。
     • ​rows 列：预估需要扫描的行数。
     • ​Extra 列：是否存在文件排序 (Using filesort) 或临时表 (Using temporary) 等开销较大的操作。

   • 通过分析找出慢查询的根本原因（例如索引缺失、索引失效、连接方式不当等）。

2. 优化索引策略:

   • 创建或优化索引： 根据 EXPLAIN 分析结果和查询模式，为经常出现在 ​WHERE​ 子句、​JOIN​ 连接条件、​ORDER BY​ 排序字段、​GROUP BY​ 分组字段 的列创建合适的索引。
   • 联合索引： 对于涉及多个字段的查询，考虑创建联合索引。理解并遵循最左匹配原则，确保索引能够被有效利用。
   • 覆盖索引 (Covering Index)： 创建一个索引，使其包含查询所需的所有列 (SELECT, WHERE, ORDER BY 等)。这样数据库可以直接从索引中获取数据，避免回表查询，显著减少 I/O。

3. 避免索引失效的常见陷阱：

   • 避免在索引列上使用函数或表达式计算 (WHERE function(column) = ... 或 WHERE column + 1 = ...)。
   • 避免在 LIKE 子句中使用前导通配符 (%keyword)，这会导致索引失效。
   • 避免对索引列进行隐式类型转换。
   • 避免使用 != 或 NOT IN（在某些情况下可能导致全表扫描）。
   • 避免对允许 NULL 的列进行判断时使用 IS NOT NULL（取决于索引类型和版本）。

4. 优化查询语句本身：

   • 避免 ​SELECT *​ ​ ： 只查询真正需要的列，减少数据传输和处理量，并有助于实现覆盖索引。

   • 优化联表查询 (JOIN)：

     • 尽量让小结果集驱动大表（虽然优化器会尝试选择最佳连接顺序，但理解数据量有助于写出更优的 SQL）。
     • 确保被驱动表上用于连接的字段有索引。

   • 考虑反范式设计： 在某些读性能要求极高的场景，可以考虑通过冗余字段来避免复杂的联表查询，但需权衡数据一致性维护成本。

5. 优化深分页查询：

   • 对于 LIMIT offset, limit 这种深分页（offset 很大）的查询，直接跳过大量记录开销很大。
   • 优化方法：将分页转换为基于索引列范围的查询。例如，如果按主键 id 排序，可以使用 SELECT * FROM table WHERE id > (SELECT id FROM table ORDER BY id LIMIT offset, 1) LIMIT limit; 或更简单的 SELECT * FROM table WHERE id > last_id LIMIT limit; (适用于自增主键且按主键排序)。

6. 优化数据库表结构：

   • 水平分表 (Sharding)： 当单表数据量达到千万或亿级别时，考虑将大表拆分为多个小表，分散数据和查询压力。
   • 垂直分表 (Vertical Partitioning)： 将字段较多的表按访问频率或业务逻辑分解为多个表，将不常用的字段分到单独的表中，减少主表的宽度，提高常用字段的查询效率。

7. 引入缓存层：

   • 对于热点数据或频繁查询的结果，引入缓存系统（如 Redis, Memcached）来存储，直接从缓存中获取数据，避免访问数据库。
   • 需要重点考虑缓存一致性问题。常见的缓存策略如旁路缓存 (Cache-Aside) ，写入时通常采用先更新数据库，再删除缓存的策略。

‍

分库分表

简单来说，当单台数据库的数据量太大或者并发压力太高时，我们就需要把数据分散到多个数据库或多张表上。

分库分表主要有四种基本形式：

1. 垂直分表： 把一个表里字段比较多的，按冷热程度或字段关联性拆分成多个小表（字段变少了），比如一个商品表拆成基本信息表和详情表。
2. 垂直分库： 按业务把不同的表放到不同的数据库里。比如用户相关的表放一个库，订单相关的表放另一个库，这在微服务架构下很常见。
3. 水平分库： 把同一个表里的大量数据行，按某种规则（比如用户ID取模）分散到多个数据库中。每个库都有这张表的一部分数据，结构一样。
4. 水平分表： 在同一个数据库里，把同一个表里的大量数据行，按某种规则（比如订单号范围）分散到多个表中。每张表有这张表的一部分数据，结构一样。

在我们项目里，主要就是为了解决订单数据量巨大的问题，我们采用了水平分库和水平分表相结合的方式：

• 分库方面，我们把订单数据水平分散到了3个数据库实例上。分库的规则是根据用户 ID 进行取模，保证同一个用户的订单都落到同一个库里，方便查询。

• 同时在每个库内部，又做了水平分表，把订单表按订单号的范围来分，每 1500 万一个表。

• 我们使用了 ShardingSphere 这个中间件来管理这些分库分表规则，对我们应用代码来说，就像操作一张表一样，感觉不到底层数据的分散，是比较透明的。

‍

事务

MySql事务的特性

好的，ACID 是数据库事务的四个非常重要的特性：

1. 原子性 (Atomicity)： 简单来说， 一个事务是一个 ​** 不可分割的整体 ​ ，里面的所有操作 ​ 要么全部成功，要么全部失败 ​ 。 中间出了任何问题，已经执行的操作也会被全部撤销**，就像从未发生过一样。
2. 一致性 (Consistency)： 它保证 事务在执行 ​** 之前和之后 ​ ，数据库都处于一个 ​ 有效的、合法的状态 **​ 。 也就是说，事务不会破坏数据的完整性或业务规则。
3. 隔离性 (Isolation)： 当 ​** 多个事务同时并发执行 ​ 时，隔离性保证每个事务感觉就像在 ​ 独立运行 ​ 一样，它们之间 ​ 互不干扰 **，不会看到彼此未提交的中间数据。
4. 持久性 (Durability)： 一旦事务 ​** 提交成功 ​ ，它对数据的 ​ 修改就是永久的 ​ ，即使系统突然发生 ​ 故障、断电或崩溃 ​ ，这些改变也 ​ 不会丢失 **​ 。

‍

并发事务问题

好的，MySQL 因为是多客户端连接、支持并发处理事务，所以当多个事务同时操作数据时，就可能出现一些和并发相关的问题：

1. 脏读 (Dirty Read)： 就是一个事务读到了另一个事务 ​** 还没提交 **​ 的数据。如果那个事务后来回滚了，那你读到的就是个不真实的数据。

   假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后再执行更新操作，如果此时事务 A 还没有提交事务，而此时正好事务 B 也从数据库中读取小林的余额数据，那么事务 B 读取到的余额数据是刚才事务 A 更新后的数据，即使没有提交事务。

   

   因为事务 A 是还没提交事务的，也就是它随时可能发生回滚操作，如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

2. 不可重复读 (Non-repeatable Read)： 同一个事务 ​** 多次读取同一条记录 ​ ，结果发现 ​ 数据变了 ​ 。这是因为在两次读取之间，有其他事务 ​ 修改或删除了这条记录并提交了 **​ 。

   假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后继续执行代码逻辑处理，在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读

   

   幻读

3. 幻读 (Phantom Read)： 同一个事务 ​** 按照某个条件去查询数据 ​ ， ​ 两次 ​ 查询结果 ​ 返回的行数变了 ​ 。这是因为在两次查询之间，有其他事务 ​ 插入或删除了符合条件的新的行 **​ 。

   举个栗子。

   假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库查询账户余额大于 100 万的记录，发现共有 5 条，然后事务 B 也按相同的搜索条件也是查询出了 5 条记录。

   

   接下来，事务 A 插入了一条余额超过 100 万的账号，并提交了事务，此时数据库超过 100 万余额的账号个数就变为 6。

   然后事务 B 再次查询账户余额大于 100 万的记录，此时查询到的记录数量有 6 条，发现和前一次读到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就被称为幻读。

这些问题是并发事务可能带来的数据不一致现象，不同的事务隔离级别就是用来控制会发生哪些问题的。

‍

MySQL 的事务隔离级别

它分了四个级别，从低到高分别是：：

1. 读未提交 (READ UNCOMMITTED)： 这是最低级别，它 ​** 能看到别的事务还没提交的数据 ​ ，所以 ​ 脏读、不可重复读、幻读都会发生 ​ 。实现上就是 ​ 直接读取数据的最新版本 **，没有任何限制。
2. 读已提交 (READ COMMITTED)： 这个级别 ​** 只能看到别的事务已经提交的数据 ​ ，能 ​ 防止脏读 ​ 。但 ​ 不可重复读和幻读还是会发生 ​ 。 实现上， 它通过 ** MVCC ** (多版本并发控制)，在 ​ 事务中每次执行 ** **== **** ​SELECT​ ****​ ** ** **** 语句前 ​ ，都 ​ 重新生成一个 Read View **​ ，==这样就能看到语句执行前已提交的最新数据。
3. 可重复读 (REPEATABLE READ)： 这是 MySQL InnoDB 的 ​** 默认级别 。 它保证事务 ​ 多次读取同一条数据 ​ 时结果 ​ 是一样的 ​ ，能 ​ 防止脏读和不可重复读 ​ 。 ​幻读问题在这个级别理论上可能发生**，但 InnoDB 通过特殊机制大部分情况也能防止。实现上， 它也是通过 ** MVCC ​ ，但 ​ 只在事务启动时生成一个 Read View ​ ，并且整个事务 ​ 都使用这个固定的 Read View **​ ，从而看到了事务启动那一刻的数据快照 。
4. 串行化 (SERIALIZABLE)： 这是最高级别。它会 ​** 对读写加锁 ​ ，强制事务 ​ 一个接一个地执行 ​ ，彻底 ​ 防止所有并发问题 ​ 。但代价是 ​ 并发性能最差 **​ 。

所以，不同的级别通过直接读最新数据、基于 MVCC 的 Read View（生成时机不同） 或严格加锁来实现对并发问题的不同程度的控制。

MySQL 的 InnoDB 存储引擎 ​** 默认 ​ 的隔离级别是 ​ 可重复读 (REPEATABLE READ) **​ 。

‍

‍

一条update是不是原子性的？为什么？

是原子性，主要通过锁+undolog 日志保证原子性的

• 执行 update 的时候，会加行级别锁，保证了一个事务更新一条记录的时候，不会被其他事务干扰。
• 事务执行过程中，会生成 undolog，如果事务执行失败，就可以通过 undolog 日志进行回滚。

‍

单纯依赖MVCC（多版本并发控制）并不能完全避免幻读

好的，您提供的这个例子非常典型，它确实展示了在MySQL InnoDB的可重复读（Repeatable Read）隔离级别下，单纯依赖MVCC（多版本并发控制）并不能完全避免幻读的特定场景。

核心原因在于理解 MVCC的Read View 和 更新操作（UPDATE） 的行为差异。

1. 第一次 SELECT (快照读) 与 Read View： 当事务 A 第一次执行 SELECT * from t_stu where id = 5; 时，这是一个普通的 SELECT 语句，属于 快照读（Consistent Read） 。在可重复读级别下，InnoDB会为事务 A 生成一个 Read View。这个 Read View 就像一个时间戳，决定了事务 A 能看到哪些已提交的数据版本。由于在生成 Read View 的那一刻，id=5 的记录还不存在（或者即使存在，如果是由一个在 Read View 生成后才提交的事务插入的，也会被过滤），所以事务 A 看不到这条记录。
2. 事务 B 插入并提交： 事务 B 插入 id=5 的记录并提交。这条新记录的内部隐藏字段 trx_id 被标记为事务 B 的事务 ID。根据事务 A 的 Read View，这条记录对事务 A 是不可见的。
3. 事务 A 执行 UPDATE (当前读)： 事务 A 执行 UPDATE t_stu set name = '小林coding' where id = 5;。这是一个 当前读（Current Read） 操作。与快照读不同，当前读会忽略事务的 Read View，而是去读取数据的最新已提交版本，并且通常会加上锁。此时，它找到了事务 B 刚刚提交的 id=5 的记录。当事务 A 成功更新这条记录后，这条记录的内部隐藏字段 trx_id 会被修改为事务 A 的事务 ID。
4. 第二次 SELECT (快照读) 看到记录： 事务 A 再次执行 SELECT * from t_stu where id = 5;。这仍然是一个快照读，它会使用事务 A 之前生成的那个 Read View。然而，MVCC的一个规则是：一个事务总是可以看到自己修改过的记录。由于 id=5 这条记录的 trx_id 现在已经变成了事务 A 的 ID（因为它被事务 A 更新了），尽管这条记录最初对事务 A 的 Read View 是不可见的“幻影”，但现在它符合“自己修改的记录可见”的规则，因此对事务 A 变得可见了。

总结来说： 幻读在这个场景下发生，是因为事务 A 的快照读（依赖Read View）和当前读（UPDATE，读取最新版本并修改行记录的trx_id）之间发生了交互。一个原本在快照中不存在的记录，通过当前读被当前事务修改后，就“神奇地”出现在了后续的快照读结果中，打破了可重复读的感知一致性，形成了幻读现象。

‍

什么是MVCC/事务中的隔离性是如何保证的呢？

事务的隔离性主要通过锁和 MVCC（多版本并发控制） 来保证。

这里重点讲 MVCC：

• 核心思想： 为数据行维护** 多个历史版本 **。
• 目的： 让并发的** 读操作（快照读） ** 可以读取数据的旧版本，从而减少读写之间的阻塞，提高并发性能。

在数据库事务中，读操作分为两种：

• 快照读 (Snapshot Read)：

  • 读取数据在某个** 时间点 ​ 的 ​ 快照版本 **。
  • 不加锁，因此不会被写操作阻塞，也不会阻塞写操作。
  • 普通的 SELECT 语句默认是快照读（在读已提交和可重复读隔离级别下）。
  • 主要依赖 MVCC 实现。

• 当前读 (Current Read)：

  • 读取数据的** 最新版本 **（包括其他事务已提交的修改）。
  • 为了保证读取到最新并防止其他事务立即修改，** 需要加锁 **。
  • 可能会被写操作阻塞，也会阻塞写操作。
  • 包括 SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, 以及 INSERT, UPDATE, DELETE 操作。
  • 主要依赖锁实现。

MVCC 主要依赖以下三个组件来支持快照读：

1. 隐藏字段： 每行数据包含 trx_id（创建/修改该版本的事务ID）和 roll_pointer（指向上一个版本在 Undo Log 中的地址）。
2. Undo Log： 存储旧版本的数据。通过 roll_pointer 将同一行数据的不同版本串联起来，形成一个版本链。
3. Read View： 一个读视图或数据快照。它定义了当前事务在访问数据时，能看到版本链中的哪个版本。

【隔离级别与 Read View】

读已提交 (Read Committed) 和 可重复读 (Repeatable Read) 这两个隔离级别的主要区别在于 Read View 的生成时机：

• 读已提交： 每次执行 ​SELECT​ 语句前都会重新生成一个 Read View。因此能看到该语句执行前已提交的最新数据。
• 可重复读： 在事务第一次读取数据时（或事务启动时）只生成一个 Read View，并整个事务期间固定使用。保证事务看到一致的数据快照，避免不可重复读。

【可见性判断】

数据库通过可见行算法，结合事务当前的 Read View 和数据行的 trx_id，判断版本链中的哪个版本对当前事务是可见的。

【总结】

总结来说，MVCC 是 InnoDB 实现事务隔离性，特别是提高并发读写能力的核心机制之一。它通过为数据行创建并维护** 多个历史版本 （存储在 Undo Log 中，形成版本链**），并结合每个事务独立的Read View，使得快照读可以读取到事务启动时或语句执行时的数据快照，而不会被正在进行的写操作阻塞。这种设计极大地减少了读写冲突，提高了系统的并发处理能力。不同的隔离级别（如读已提交和可重复读）通过控制 Read View 的生成时机来决定快照读能看到哪个版本的数据，从而实现不同的隔离效果。需要注意的是，MVCC 主要服务于快照读。对于写操作（INSERT, UPDATE, DELETE）以及需要读取最新数据的当前读（如 SELECT ... FOR UPDATE），数据库仍然需要依赖传统的锁机制来保证数据的一致性和隔离性。

日志

‍

日志文件是分成了哪几种？

• redo log 重做日志，是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• undo log 回滚日志，是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• bin log 二进制日志，是 Server 层生成的日志，主要用于数据备份和主从复制；
• relay log 中继日志，用于主从复制场景下，slave通过io线程拷贝master的bin log后本地生成的日志
• 慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

什么是redolog

好的，Redo log 主要就是为了保证 MySQL 事务的** 持久性（Durability **​ ） 。

• 性质： 是 InnoDB 存储引擎的日志，记录的是对数据页的物理修改。
• 记录内容： Redo Log 记录的是在某个物理位置（如表空间、数据页、偏移量）上执行了什么物理操作，并且会记录修改的具体值。

它用到一个技术叫 ** WAL (Write-Ahead Logging) ​ ，核心思想是： ​ 先写日志，再写数据到磁盘 **​ 。

具体来说，当要更新数据时，MySQL 会** 先在内存里的 Buffer Pool 把数据改了 ​ （标记为脏页），同时 ​ 立即把这个修改操作记录到内存中的 ** == **** ​redo log buffer​ ****​ ** ** **** 里 。最关键的是， 事务提交的时候 **​ ， ​ ​redo log buffer​ ​ ** 里的 ​ 相关记录会被强制刷到磁盘上的 **** ** ** **** ​redo log file​ ****​ ** ** **** 里==。

这样一来，即使之后系统崩溃了，内存里的脏页还没来得及写到磁盘，因为** 磁盘上的 ** ** ​redo log file​ ** ** 已经包含了已提交事务的记录 ​ ，MySQL ** 重启时就能读取 ** ** ​redo log file​ ​ ，并 ​ 根据里面的记录重 ​做那些已提交但没落盘的修改，确保数据不会丢失。这就是它的 crash-safe 能力，从而保证了持久性。

‍

什么是undo log

好的，Undo log 主要就是用来保证 MySQL 事务的原子性（Atomicity） 和一致性（Consistency） 。

它的作用简单来说就是：记录事务执行前的数据状态。

当你在一个事务里对数据进行修改（插入、删除、更新）时，InnoDB 引擎会先把修改前的数据或信息记录到 undo log 里。

这样，如果这个事务在执行过程中失败了，或者你手动执行了回滚（ROLLBACK） 操作，MySQL 就可以读取 undo log 里的记录，根据这些记录把数据恢复到事务开始之前的样子，撤销掉已经做过的修改。

比如：

• 如果你插入了一条数据，undo log 会记下它的主键，回滚时就删掉它。
• 如果你删除了一条数据，undo log 会记下这行完整的数据，回滚时就再插入回去。
• 如果你更新了一行数据，undo log 会记下更新前那些列的旧值，回滚时就再更新回旧值。

所以，undo log 的核心作用就是提供回滚（Undo） 能力，确保事务要么全部完成，要么全部撤销，维持原子性和一致性。

‍

undolog和redolog的区别

1. Redo log： 它记录的是** 数据页的物理修改 ​ 。主要用于 ​ 崩溃恢复 ​ 时， ​ 重做（re-do） ​ 那些已经提交但还没来得及写入磁盘的数据修改 ， 以此来保证事务的 ​ 持久性 **。
2. Undo log： 它记录的是** 逻辑上的操作 ​ （比如插入对应删除，更新对应反向更新）。主要用于事务 ​ 回滚 ​ 时， ​ 撤销（un-do） ​ 已经执行过的修改操作，把数据恢复到事务 ​ 开始前 ​ 的状态，以此来保证事务的 ​ 原子性 ​ 和 ​ 一致性 **。

简单记就是：Redo log 是向前重做（恢复） ，保证持久；Undo log 是向后撤销（回滚） ，保证原子和一致。

能不能只用binlog不用redo log

• Binlog：记录数据库更改，用于复制和恢复，但不跟踪内存中已修改未写盘的数据（脏页），针对所有数据。
• Redo Log：** InnoDB特有 **，记录哪些脏页未刷盘，确保崩溃后能恢复这些数据。

如果只使用Binlog而没有Redo Log，在崩溃恢复的情况下，将 无法恢复那些已经在内存中更新但还没有被刷写到磁盘上的数据页，这可能会导致数据丢失或数据不一致的问题。 为了保证数据的安全性与一致性，这两个日志系统在MySQL中各自扮演着不可或缺的角色。不行。Binlog和Redo Log在MySQL中作用不同，不能互相替代。

‍

binlog 两阶段提交过程是怎么样的？

MySQL的Binlog两阶段提交是为了保证 Redo Log（InnoDB层）和 Binlog（Server层）这两个关键日志的一致性。

它将事务提交过程分为两个阶段：

1. 准备阶段 (Prepare): InnoDB将事务的修改写入 ** Redo Log ​ ，标记事务状态为“prepare” ，并将Redo Log刷写到磁盘**。同时在Redo Log中记录一个事务ID (XID)。
2. 提交阶段 (Commit): Server层将包含相同 XID 的事务信息写入 Binlog，并将Binlog刷写到磁盘。一旦Binlog刷盘成功，事务就被认为是已提交的。最后，InnoDB在内存中标记事务为“commit”。

如果在崩溃恢复时，Redo Log中发现处于“prepare”状态的事务，MySQL会检查Binlog。如果在Binlog中找到了对应的XID，就认为事务已提交并重做；如果没找到，就回滚 。

这样就确保了Redo Log和Binlog的状态一致，要么都认为事务提交了，要么都认为没提交。

update语句的具体执行过程是怎样的？

假设执行一个 UPDATE 语句并提交事务：

1. SQL 执行与内存记录阶段 (Server 层 & InnoDB 层协作):

   • 客户端发送 UPDATE 语句到 MySQL Server。

   • Server 层: 接收、解析、优化，生成执行计划。

   • Server 层调用 InnoDB 接口: 执行器调用 InnoDB 存储引擎接口执行修改操作。

   • InnoDB 内部操作 (针对每个修改):

     • 记录 Undo Log: 在修改数据页之前，将旧版本数据写入 Undo Log Buffer (内存)。用于回滚。
     • 记录 Redo Log: 在修改数据页之后或同时，将物理修改记录写入 Redo Log Buffer (内存)。用于重做。
     • 修改数据页: 将修改应用到 Buffer Pool 中的数据页（内存）。

   • Server 层 (在 InnoDB 修改成功后):

     • 生成该修改操作对应的 Binlog Event（根据 Binlog 格式）。
     • 将 Binlog Event 写入 Binlog Buffer (内存)。用于复制和恢复。

   • 这个过程会重复，直到事务中的所有修改语句执行完毕。此时，Undo/Redo/Binlog 的记录都在各自的内存 Buffer 中。

2. 事务提交阶段 (Commit - 关键的“两阶段提交”): 这是将事务的变更持久化到磁盘的关键过程，确保 Redo Log 和 Binlog 的一致性。

   • 阶段 1: Prepare (Redo Log 持久化):

     • InnoDB: 将 Redo Log Buffer 中该事务对应的记录刷写 (fsync) 到 Redo Log 文件磁盘。
     • InnoDB: 在 Redo Log 中写入一个特殊的 “准备好” (prepare) 标记。
     • 目的： 确保即使在 Binlog 刷盘前崩溃，InnoDB 也能通过 Redo Log 知道这个事务已经“准备好”提交了。

   • 阶段 2: Commit (Binlog 持久化 & InnoDB 最终提交):

     • Server 层: 将 Binlog Buffer 中该事务的记录刷写 (fsync) 到 Binlog 文件磁盘。
     • InnoDB: 在 Redo Log 中写入一个提交记录，标记该事务正式提交完成。
     • 目的： 确保 Binlog 完整记录了所有已提交的事务，与 Redo Log 状态一致，支持准确的复制和时间点恢复。

3. 数据页刷盘 (异步):

   • Buffer Pool 中的脏页（被修改的数据页）会在后台由 Checkpoint 机制或其他时机异步刷写到数据文件磁盘。这个过程通常发生在事务提交之后，其安全性由 Redo Log 的持久性保障。

【总结关键时机与日志作用】

• Undo/Redo Log 记录 (Buffer): 发生在事务执行中，由 InnoDB 完成，用于回滚和重做。
• Binlog 记录 (Buffer): 发生在事务执行中，在 InnoDB 修改 Buffer Pool 后，由 Server 层 完成，用于复制和时间点恢复。
• Redo Log 刷盘 (Prepare): 发生在提交第一阶段，由 InnoDB 完成，保证已提交事务的持久性。
• Binlog 刷盘: 发生在提交第二阶段，由 Server 层 完成，保证复制和恢复的完整性。
• Redo Log 提交标记: 发生在提交第二阶段，在 Binlog 刷盘后，由 InnoDB 完成，标记事务最终提交。
• 数据页刷盘: 通常在提交后异步进行，依赖 Redo Log 保障安全。

‍

正常执行: ** SQL -> Undo/Redo Log Buffer -> Buffer Pool 修改 -> (Commit: Redo Log 刷盘 -> Binlog 刷盘 -> Redo Log 提交记录) -> (异步) 数据页刷盘。 **

‍

崩溃恢复过程的时间顺序与日志作用

当 MySQL 遭遇崩溃并重启时，会进入自动崩溃恢复流程：

1. 检查 Redo Log: MySQL (InnoDB) 首先扫描 Redo Log 文件。

   • 重放 (Redo): 查找在崩溃前已经写入 Redo Log 并标记为** 已提交 的事务记录。即使这些事务修改的数据页在崩溃前没有来得及刷入磁盘**，InnoDB 也会** 重放 **这些 Redo Log 记录，将对应的修改应用到数据文件中，确保已提交事务的持久性。

2. 检查 Undo Log: 在 Redo Log 应用完成后，数据库状态可能包含了 未提交事务的部分修改 。

   • 回滚 (Undo): 扫描 Undo Log。查找在崩溃前** 尚未提交 **的事务记录。利用 Undo Log 中的信息，回滚这些未提交事务已经完成的修改，将数据恢复到这些事务开始前的状态，保证原子性和一致性。

总结自动崩溃恢复顺序： 扫描 Redo Log -> 重放已提交事务 -> 扫描 Undo Log -> 回滚未提交事务

Binlog 在崩溃恢复中的作用：

• Binlog 不参与上述 MySQL 启动时的自动崩溃恢复过程。

• Binlog 的作用体现在基于备份的时间点恢复。如果你有一个旧的数据库备份，你想恢复到崩溃前的某个特定时间点：

  1. 先恢复到备份时刻的数据状态。
  2. 然后按时间顺序重放（应用）备份时刻之后到目标时间点之间的所有 Binlog 文件中记录的事件。这个过程是手动或通过工具完成的，不是数据库启动时自动进行的。

最终时间线总结：

1. 自动崩溃恢复 (启动时): 扫描 Redo Log -> **** ** 重放已提交 ** -> 扫描 Undo Log -> ** 回滚未提交 **。
2. 基于备份的时间点恢复 (手动): 恢复备份 -> 按时间顺序重放 Binlog。

通过这种机制，MySQL 确保了即使在崩溃发生时，已提交的事务不会丢失，未提交的事务会被正确回滚，从而保证了数据的一致性。Binlog 则提供了更灵活的基于时间点的恢复能力和主从同步的基础。

为什么要写RedoLog，而不是直接写到B+树里面？

MySQL不直接写B+Tree数据页，而是先写Redo Log，这是基于 WAL (Write-Ahead Logging) 机制。

主要原因是为了 提高性能和保证持久性：

1. 性能： Redo Log是 ​** 顺序写入 ​ 磁盘，速度远快于直接对分散的B+Tree数据页进行 ​ 随机写入 ​ 。 先写Redo Log能显著加速事务提交**。
2. 持久性与恢复： Redo Log记录了数据页的 ​** 物理修改 ​ 。它保证事务提交后，即使数据页还没来得及刷盘就崩溃，也能通过Redo Log进行 ​ 崩溃恢复 **​ ，重做修改，确保已提交事务的数据不丢失。

简单说，就是用快速的顺序写日志来保证提交速度和数据安全，而将慢速的随机写数据页操作推迟到后台进行。

mysql 两次写（double write buffer）了解吗？

MySQL的Doublewrite Buffer是InnoDB存储引擎用来解决部分页写入（Partial Page Write） 问题的机制。

问题在于，MySQL的数据页（16KB）比操作系统页（通常4KB）大。当MySQL将一个数据页刷到磁盘时，操作系统会分多次写入。如果在写入过程中崩溃，可能导致磁盘上的页数据不完整或损坏。这种损坏是Redo Log无法修复的，因为Redo Log假设页本身是完整的。

Doublewrite Buffer的工作方式是：在将数据页写入最终的数据文件位置之前，先将页的完整副本顺序地写入磁盘上的一个Doublewrite Buffer区域。只有这个写入成功后，才会将页写入数据文件的目标位置。

如果在写入数据文件时崩溃导致页损坏，恢复时InnoDB可以从Doublewrite Buffer区域找到该页的完好副本，然后结合Redo Log进行恢复。

简单来说，Doublewrite Buffer提供了一个页的安全备份，确保即使最终写入失败，也有一个完整的页版本用于恢复，保证了数据页写入的原子性和可靠性。

‍

为什么redolog无法代替double write buffer？

Redo Log 和 Doublewrite Buffer 作用不同，不能互相替代。

• Redo Log 记录的是修改操作（“在页X做了什么”），用于在崩溃后重做这些操作，前提是数据页本身是完整的。
• Doublewrite Buffer 提供的是页的完整副本。它解决的是部分页写入导致的页损坏问题。

如果数据页损坏了，Redo Log无法在损坏的页上正确重做修改。Doublewrite Buffer提供了损坏页的完好备份，让Redo Log有完整的基础来应用修改。

简单说，Redo Log保证操作，Doublewrite Buffer保证操作应用的对象（页）是完整的。

‍

‍

什么是binlog

MySQL Binlog 是 ** Server 层 ** 实现的二进制日志，记录了所有** 数据库结构变更 (DDL) ** 和 ​** 数据修改 (DML) ** == 操作，** 不记录查询操作==** (如 SELECT, SHOW)。

• 生成与写入： 在完成更新操作后生成，并在** 事务提交时 **，将该事务产生的 binlog 统一写入 binlog 文件。
• 存储方式： 追加写入，写满当前文件则创建新文件，** 不会覆盖 **​ 历史日志 。
• 主要用途： 用于** 数据备份与恢复 ，以及 主从复制 **。

Binlog 有三种格式类型：

1. STATEMENT：

   • 记录** 逻辑 SQL 语句 **。
   • 优点：日志文件相对较小。
   • 缺点：可能存在** 动态函数导致主从不一致 **的问题 (如 UUID(), NOW())。

2. ROW：

   • 记录** 行数据的最终变更 **。
   • 优点：避免动态函数问题，保证主从数据一致性。
   • 缺点：对于批量操作，日志文件可能** 非常大 **。

3. MIXED：

   • 混合模式，根据情况自动选择 STATEMENT 或 ROW 格式。
   • 旨在结合两者的优点，规避缺点。

主从同步原理

好的，MySQL 主从同步（Master-Slave Replication） 的原理，简单来说就是通过记录主库的数据变更日志，然后从库去回放这些日志来实现数据同步。

核心依赖是主库的二进制日志（Binlog） 。Binlog 记录了主库执行的所有会修改数据的操作（比如增删改）。

整个过程主要分三步：

1. 主库记录 Binlog： 当主库执行一个事务并提交时，它会把这个事务对数据的所有修改操作记录到自己的 Binlog 文件里。
2. 从库拉取 Binlog 并写入 Relay Log： 从库会启动一个进程，连接到主库，不断拉取主库最新的 Binlog 内容，然后把这些拉取到的日志写到自己本地的一个文件里，这个文件叫做中继日志（Relay Log） 。
3. 从库应用 Relay Log： 从库的另一个进程会不断读取中继日志（Relay Log）里的内容，然后在自己身上重新执行一遍这些操作，就像在主库上执行过一样。这样，从库的数据就变得和主库一致了。

通过这三个步骤，从库就能基本实时地复制主库的数据变化，实现读写分离、故障切换等功能。

‍

锁

MySQL主要有几类锁

1. 全局锁 (Global Lock):

   • 通过 FLUSH TABLES WITH READ LOCK 语句实现。
   • 它会使整个数据库实例处于只读状态，阻止所有对数据的修改操作（DML）以及对表结构的修改操作（DDL）。
   • 主要用于进行全库逻辑备份，确保备份期间数据不会发生变化，从而获得一致性的备份文件。

2. 表级锁 (Table-Level Locks):

   • 表锁 (Table Lock): 通过 LOCK TABLES 语句显式加锁。它可以锁定一张表用于读或写，会阻塞其他线程对该表的相应操作，甚至会限制本线程对其他表的访问，使用较少。
   • 元数据锁 (Metadata Lock, MDL): 这是MySQL自动管理的锁。当对表进行CRUD操作时，会加MDL读锁；进行DDL操作（如修改表结构）时，会加MDL写锁。MDL的主要作用是保证在对表进行DML操作时，表的结构不被其他线程修改，反之亦然，确保操作的稳定性。
   • 意向锁 (Intention Locks): 这是InnoDB存储引擎自动管理的锁，属于表级锁。它表示一个事务有意向在表中的某些行上加行级锁（意向共享锁 IS 表示将要加 S 锁，意向排他锁 IX 表示将要加 X 锁）。意向锁的存在是为了快速判断在对表加表级锁时，是否有行已经被加了行级锁，而无需扫描每一行。

3. 行级锁 (Row-Level Locks):

   • 这是InnoDB存储引擎特有的锁，粒度最小，能最大程度支持并发。
   • 记录锁 (Record Lock): 锁定单个行记录。分为共享锁（S锁，允许多个事务读）和排他锁（X锁，只允许一个事务写），遵循读写互斥、写写互斥原则。
   • 间隙锁 (Gap Lock): 锁定的是一个范围，而不是具体的记录。它锁定的是索引记录之间的“间隙”，或者第一个记录之前的范围，或最后一个记录之后的范围。间隙锁的主要作用是防止其他事务在锁定的范围内插入新的记录，从而用于解决可重复读隔离级别下的幻读问题。
   • 临键锁 (Next-Key Lock): 这是InnoDB在可重复读隔离级别下默认使用的锁，它是记录锁和间隙锁的组合。它锁定的是一个包含记录本身的范围，例如锁定一个左开右闭的区间 (start, end]。临键锁既锁定了记录，也锁定了记录前的间隙，是防止幻读的更强力手段。

这些不同粒度和类型的锁协同工作，共同维护着MySQL数据库在并发环境下的数据一致性和隔离性。

‍

MySQL数据库的表锁和行锁的作用

表锁 (Table Lock) 的作用是 锁定 ​** 整个表 ​ 。 当一个事务获得表锁后， 其他事务对该表的读写操作都会受到限制，甚至完全阻塞 。它的主要优点是管理起来相对简单，开销较小，适用于需要对整个表进行大批量操作**（如导入导出、表结构变更）的场景。然而，表锁的粒度大，会显著降低并发性能，因为即使是操作不同的行，也会因为锁定了整个表而相互等待。

行锁 (Row Lock) 的作用是 锁定表中的 ​** 单个行记录 ​ 。 这是InnoDB等存储引擎支持的更细粒度的锁。它的主要优点是能够实现高并发**，因为不同事务可以同时操作同一张表中不同的行，相互之间不会产生锁冲突。行锁适用于对表中频繁进行单行或小批量行操作的场景（如订单更新、用户数据修改）。缺点是管理和维护行锁的开销相对表锁要大。

总的来说， 表锁提供的是粗粒度的控制，简单但并发性差；行锁提供的是细粒度的控制，复杂但并发性高。选择哪种锁或引擎支持哪种锁，取决于应用对并发性能和管理开销的需求权衡。

‍

MySQL两个线程的update语句同时处理一条数据，会不会有阻塞？

如果是两个事务同时更新了 id = 1，比如 update … where id = 1，那么是会阻塞的。** 因为 InnoDB 存储引擎实现了行级锁。 **

当A事务对 id =1 这行记录进行更新时，会对主键 id 为 1 的记录加X类型的记录锁，这样第二事务对 id = 1 进行更新时，发现已经有记录锁了，就会陷入阻塞状态。

两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，会不会遇到阻塞？底层是为什么的？

不会，因为锁住的范围不一样，不会形成冲突。

• 第一条 update sql 的话（ id<10），锁住的范围是（-♾️，10）
• 第二条 update sql 的话（id >15），锁住的范围是（15，+♾️）

‍

两条update语句处理一张表的不同的不是主键或索引范围的记录，一个<10，一个>15，会不会遇到阻塞？

如果2个范围查询的字段不是索引的话，那就代表 update 没有用到索引，这时候触发了全表扫描，全部索引都会加行级锁，这时候第二条 update 执行的时候，就会阻塞了。

因为如果 update 没有用到索引，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。

‍

‍

SQL题 #todo#​

sql题：给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩

可以使用SQL的子查询和LEFT JOIN或者EXISTS关键字来实现，这里我将展示两种不同的方法来完成这个查询。

假设我们有以下两张表：

1. ​Student 表，其中包含学生的sid（学生编号）和其他相关信息。
2. ​Score 表，其中包含sid（学生编号），cid（课程编号）和score（分数）。

方法1：使用LEFT JOIN 和 IS NULL

SELECT s.sid, s.sname, sc2.cid, sc2.score
FROM Student s
LEFT JOIN Score AS sc1 ON s.sid = sc1.sid AND sc1.cid = '01'
LEFT JOIN Score AS sc2 ON s.sid = sc2.sid AND sc2.cid = '02'
WHERE sc1.cid IS NULL AND sc2.cid IS NOT NULL;

方法2：使用NOT EXISTS

SELECT s.sid, s.sname, sc.cid, sc.score
FROM Student s
JOIN Score sc ON s.sid = sc.sid AND sc.cid = '02'
WHERE NOT EXISTS (
    SELECT 1 FROM Score sc1 WHERE sc1.sid = s.sid AND sc1.cid = '01'
);

#给定一个学生表 student_score（stu_id，subject_id，score），查询总分排名在5-10名的学生id及对应的总分

可以使用以下 SQL 查询来检索总分排名在 5 到 10 名的学生 ID 及对应的总分。其中我们先计算每个学生的总分，然后为其分配一个排名，最后检索排名在 5 到 10 之间的记录。

WITH StudentTotalScores AS (
    SELECT 
        stu_id,
        SUM(score) AS total_score
    FROM 
        student_score
    GROUP BY 
        stu_id
),
RankedStudents AS (
    SELECT
        stu_id,
        total_score,
        RANK() OVER (ORDER BY total_score DESC) AS ranking
    FROM
        StudentTotalScores
)
SELECT
    stu_id,
    total_score
FROM
    RankedStudents
WHERE
    ranking BETWEEN 5 AND 10;

解释：

1. 子查询 StudentTotalScores 中，我们通过对 student_score 表中的 stu_id 分组来计算每个学生的总分。
2. 子查询 RankedStudents 中，我们使用 RANK() 函数为每个学生分配一个排名，按总分从高到低排序。
3. 最后，我们在主查询中选择排名在 5 到 10 之间的学生。

#SQL题：查某个班级下所有学生的选课情况

有三张表：学生信息表、学生选课表、学生班级表

学生信息表（students）结构如下：

CREATE TABLE students (
  student_id INT PRIMARY KEY, //学生的唯一标识，主键。
  student_name VARCHAR(50), //学生姓名。
  class_id INT //学生所属班级的标识，用于关联班级表。
);

学生选课表（course_selections）结构如下：

CREATE TABLE course_selections (
    selection_id INT PRIMARY KEY, //选课记录的唯一标识，主键。
    student_id INT, //选课学生的标识，用于关联学生信息表。
    course_name VARCHAR(50), //所选课程的名称。
);

学生班级表（classes）结构如下：

CREATE TABLE classes (
    class_id INT PRIMARY KEY, //班级的唯一标识，主键。
    class_name VARCHAR(50) //班级名称。
);

要查询某个班级（例如班级名称为 ‘Class A’）下所有学生的选课情况，可以使用以下 SQL 查询语句：

SELECT 
    s.student_id,
    s.student_name,
    cs.course_name
FROM 
    students s
JOIN 
    course_selections cs ON s.student_id = cs.student_id
JOIN 
    classes c ON s.class_id = c.class_id
WHERE 
    c.class_name = 'Class A';

如何用 MySQL 实现一个可重入的锁？

创建一个保存锁记录的表：

CREATE TABLE `lock_table` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    //该字段用于存储锁的名称，作为锁的唯一标识符。
    `lock_name` VARCHAR(255) NOT NULL, 
    // holder_thread该字段存储当前持有锁的线程的名称，用于标识哪个线程持有该锁。
    `holder_thread` VARCHAR(255),   
    // reentry_count 该字段存储锁的重入次数，用于实现锁的可重入性
    `reentry_count` INT DEFAULT 0
);

加锁的实现逻辑

1. 开启事务

2. 执行 SQL SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name =? FOR UPDATE，查询是否存在该记录：

   • 如果记录不存在，则直接加锁，执行 INSERT INTO lock_table (lock_name, holder_thread, reentry_count) VALUES (?,?, 1)
   • 如果记录存在，且持有者是同一个线程，则可冲入，增加重入次数，执行 UPDATE lock_table SET reentry_count = reentry_count + 1 WHERE lock_name =?

3. 提交事务

解锁的逻辑：

1. 开启事务

2. 执行 SQL SELECT holder_thread, reentry_count FROM lock_table WHERE lock_name =? FOR UPDATE，查询是否存在该记录：

   • 如果记录存在，且持有者是同一个线程，且可重入数大于 1 ，则减少重入次数 UPDATE lock_table SET reentry_count = reentry_count - 1 WHERE lock_name =?
   • 如果记录存在，且持有者是同一个线程，且可重入数小于等于 0 ，则完全释放锁，DELETE FROM lock_table WHERE lock_name =?

3. 提交事务

‍

‍

‍

‍