Java 八股文整理：MySQL 篇

2.1 MySQL 基础

2.1.1 MySQL 执行流程是怎样的？

1. 连接器：客户端与MySQL服务建立连接，进行权限验证。
2. 查询缓存：MySQL检查是否有缓存的查询结果可以直接返回（MySQL 8.0中已不推荐使用）。
3. 解析器：对SQL语句进行语法和语义分析，构建内部的数据结构。
4. 预处理器：处理SQL语句中的一些预处理操作，如视图展开等。
5. 优化器：生成多个可能的执行计划，并选择最优的执行计划。
6. 执行器：根据优化器提供的执行计划，执行SQL语句，调用存储引擎接口获取数据。

2.1.2 第一步：连接器

• 连接器功能：负责客户端与MySQL服务之间的连接建立和权限验证。
• 连接命令：在Linux系统中，使用命令连接MySQL服务。
• 工作流程：包括TCP三次握手建立连接、用户名和密码验证、权限获取。
• 连接管理： 使用show processlist;查看当前连接数和状态，使用show variables like 'wait_timeout';管理空闲连接，使用kill connection +id;手动断开连接，使用show variables like 'max_connections';查看连接数限制。
• 长连接与短连接：短连接每次执行完SQL后断开，长连接建立后可执行多次SQL操作。

2.1.3 第二步：查询缓存

• 查询语句处理：MySQL服务接收SQL语句后，首先检查查询缓存是否有直接返回的结果。
• 局限性：对于频繁更新的表，查询缓存命中率低，更新操作会导致缓存失效，MySQL 8.0中已不推荐使用查询缓存。

2.1.4 第三步：解析 SQL

• 解析器工作流程：进行词法分析识别关键字和标识符，语法分析构建语法树。
• 作用：检查SQL语句的语法正确性，构建语法树，处理语法错误。

2.1.5 第四步：执行 SQL

• 预处理阶段：检查表和字段存在性，扩展。
• 优化阶段：生成执行计划，选择最优方案。
• 执行阶段：按照执行计划执行查询，包括数据读取、过滤、排序、聚合等。

2.1.6 MySQL 如何优化？

1. 使用索引：合理使用索引是提升查询性能的关键。可以使用 EXPLAIN 关键字分析执行计划，查看是否有效利用了索引。
2. 查询语句优化：优化查询语句，比如避免使用 SELECT *，尽量指定需要的列；减少子查询和复杂的 JOIN 操作。
3. 使用缓存技术：利用缓存技术，如 Redis 或 Memcached，来缓存热点数据，减少数据库的访问压力。
4. 避免使用函数和操作符：在 WHERE 子句中避免对列使用函数或操作符，这样可以更好地利用索引。

2.2 MySQL 索引

2.2.1 存储引擎

2.2.1.1 MySQL 存储引擎？

1. ：MySQL 的默认存储引擎。支持事务处理、行级锁定和外键约束。支持聚簇索引。
2. ：早期 MySQL 的默认存储引擎。不支持事务处理，支持表级锁定。使用非聚簇索引。
3. ：将所有数据存储在内存中，访问速度快。适合临时表和快速读取的场景。数据在数据库重启时会丢失。

2.2.1.2 InnoDB 和 MyISAM 区别？为什么选前者？

1. MySQL 常见引擎：

1. InnoDB：这是 MySQL 默认的存储引擎，支持事务处理、外键约束、行级锁定和崩溃恢复能力。
2. MyISAM：这是 MySQL 早期的默认存储引擎，它提供了高速的读取性能和全文搜索能力，但不支持事务和行级锁定。
3. Memory：这个引擎将所有数据存储在内存中，提供极快的访问速度，但数据在数据库重启后会丢失。

2. InnoDB 和 MyISAM 的区别：

1. 事务支持：InnoDB 支持事务，这意味着它支持事务的四个基本特性：原子性、一致性、隔离性、持久性（ACID）。
2. 锁定机制：InnoDB 支持行级锁定，这可以减少锁定带来的冲突，提高并发性能。
3. 外键约束：InnoDB 支持外键，有助于保持数据的完整性。

3. 为什么选择 InnoDB：

1. 事务支持：对于需要事务支持的应用，InnoDB 是更好的选择，因为它可以保证数据的一致性和完整性。
2. 锁定机制：InnoDB 的行级锁定提供了更好的并发性能，尤其是在写操作频繁的场景下。
3. 外键约束：如果应用需要维护数据的引用完整性，InnoDB 的外键支持是必需的。

2.2.1.3 InnoDB 底层结构？

• 内存结构：缓冲池（Buffer Pool）、更改缓冲（Change Buffer）、日志缓冲（Log Buffer）、自适应哈希索引。
• 磁盘结构：表空间、数据字典、双写缓冲、重做日志（Redo Log）、撤销日志（Undo Logs）。表空间（Tablespaces）：用于存储数据文件，包括系统表空间、独立表空间。段（Segments）：表空间被划分为多个段，用于存储数据和索引。区（Extent）：段由一组区组成，通常 1MB 大小，用于磁盘读写操作。页（Page）：区由页组成，通常是 16KB 大小，是存储数据的最小单位。行（Row）：数据行存储在页中，使用 B+ 树索引数据结构。

2.2.1.4 MySQL 数据在页中是如何排列的？为什么要这么排列？

• 页中如何排列： 在页内，数据按照一定的顺序排列，通常是按照主键的顺序。这样可以保证数据的连续性，减少页分裂的发生。每个页都有一个页目录，指向页内数据的位置，这样可以快速定位到页内特定的数据行，而不需要扫描整个页。
• 为什么要这么排列： 提高性能：有序排列和索引结构可以显著提高数据检索和更新的性能。减少磁盘I/O：通过减少树的高度和优化页内数据的排列，可以减少访问数据时所需的磁盘I/O操作。支持事务：对于支持事务的存储引擎，这种排列方式也有助于保持事务的ACID属性，尤其是在并发环境下。

2.2.2 索引的分类

2.2.2.1 按数据结构分类

1. 索引的存储

• InnoDB 将索引和数据一起存储在文件，使用B+树作为索引结构，每个索引页中既存储索引键也存储数据行。
• 对于二级索引（非聚簇索引），索引页中存储的是 索引键 和对应的 聚簇索引键（即主键）。

2. 索引的数据结构

• B+Tree 索引的特点：主键索引的叶子节点存储：完整的数据行。比如：(id=1, name="Alice", age=30, ...)主键索引的非叶子节点存储：主键值和指向子节点的指针。比如：(id=1) -> 下层节点二级索引的叶子节点存储：索引列值和主键值。比如：(name="Alice", id=1)二级索引的非叶子节点存储：索引列值和指向子节点的指针。比如：(name="Alice") -> 下层节点
• B+Tree vs 其他数据结构：相比 B 树：B+Tree 的优势在于叶子节点只存放数据，适合 范围查询 和 排序操作。相比 二叉树：B+Tree 在 数据量大 时查询效率更高，因为其高度控制在较小的范围内。相比 Hash：Hash 索引适合 等值查询，但不支持范围查询和排序，而 B+Tree 能够处理更多的查询场景。

2.2.2.2 按物理存储分类

1. 索引分类

• 聚簇索引：聚簇索引决定了表中数据的物理顺序。也就是说，聚簇索引实际上是按照索引的顺序来存储数据的。一个表只能有一个聚簇索引。叶子节点直接包含 数据记录。由于数据是按索引顺序存储的，因此访问速度快，范围查询效率高。由于更新记录时可能涉及数据移动，因此插入、删除和更新操作可能比较慢。
• 非聚簇索引：非聚簇索引是索引顺序与数据物理存储顺序无关的索引。非聚簇索引包含对数据行的引用。一个表可以有多个非聚簇索引。叶子节点包含 数据行的地址或指针，而不是数据本身。由于通过索引查找数据地址后再访问数据，因此访问速度可能比聚簇索引慢。由于索引和数据是分开存储的，所以更新数据时不需要移动索引。

2. 查询过程

• 覆盖索引查询：如果查询所需的数据全包含在二级索引的叶子节点中，则不需要回表操作。减少了磁盘 I/O 操作和数据传输。
• 回表查询：如果查询所需的数据不全包含在二级索引的叶子节点中，则需要回表操作。增加了磁盘 I/O 操作和处理时间。

2.2.2.3 按字段特性分类

1. 主键索引：建立在表的主键字段上。

• 每张表只能有一个主键索引，索引列的值不允许有空值。
• 主键索引在创建表的时候定义，使用 关键字。

2. 唯一索引：建立在表的 字段上。

• 每张表可以有多个唯一索引，索引列的值必须唯一，但可以有空值。
• 唯一索引可以在创建表时定义，也可以在表创建后通过 命令创建。

2. 普通索引：建立在表的普通字段上。

• 每张表可以有多个普通索引。
• 普通索引可以在创建表时定义，也可以在表创建后通过 命令创建。

4. 前缀索引：建立在表的字符类字段的前几个字符上，而不是在整个字段上建立索引。

• 目的是减少索引占用的存储空间，提升查询效率。
• 前缀索引可以在创建表时定义，也可以在表创建后通过 命令创建。

2.2.2.4 按字段个数分类

2.2.3 什么时候创建索引？

1. 索引的优缺点：

• 优点：唯一性约束，提高查询速度，优化排序和分组操作。
• 缺点：占用物理空间，有维护成本，影响更新性能。

2. 何时需要创建索引？

• 唯一性字段：如商品编码等。
• 频繁用于查询条件的字段：提高查询效率，可以考虑创建索引。
• 用于排序和分组的字段：优化排序和分组操作，减少不必要的排序过程。

3. 何时不需要创建索引？

• 重复值多的字段：如性别字段，索引不会提高查询效率。
• 频繁更新的字段：如用户余额等，索引维护成本高，影响性能。
• 数据量较小的表：小表通常全表扫描效率更高。

2.2.4 有什么优化索引的方法？

1. 使用前缀索引：
2. 使用覆盖索引：
3. 主键索引自增：存储更紧凑，主键字段更小（二级索引省空间），磁盘的随机 I/O 访问更少，避免了页分裂。
4. 索引设置非空： 会省略没有意义的值 NULL 的行，但是它又会占用物理空间。

2.2.4.1 前缀索引优化

2.2.4.2 覆盖索引优化

2.2.4.3 主键索引自增

1. 主键索引：会按照主键的顺序将数据存储在磁盘上，这样相邻的数据行物理上也是相邻的，减少了磁盘的 随机 I/O 访问。
2. 插入性能：避免了页分裂操作：当使用非自增主键时，插入导致已满页面分裂，重新排列数据，影响性能并增加空间碎片化。
3. 空间利用：数据存储 更加紧凑，避免了存储空间的浪费和额外的碎片化，有助于提高整体数据库性能和响应速度。
4. 二级索引效率：主键字段越小，二级索引的叶子节点也就越小，减少了非主键索引的存储空间和访问成本，提升了查询效率。

2.2.4.4 索引设置非空

1. 索引列存在 会导致优化器在做索引选择的时候更加难以优化，比如 count 会省略 索引值为 的行。
2. 值是一个没意义的值，但是它会 占用物理空间，所以会带来的存储空间的问题。

2.2.5 索引的失效场景？

1. 模糊匹配：
2. 类型不匹配：
3. 函数操作：
4. 未满足最左匹配原则：
5. 使用 OR：

2.3 MySQL 事务

2.3.1 事务隔离级别是怎么实现的？

2.3.1.1 事务的四个特性及原理？

1. 原子性（Atomicity）：事务是一个不可分割的操作序列，要么全部执行成功，要么全部不执行。日志记录：使用事务日志（Redo Log、Undo Log）来记录事务的操作。如果事务执行失败，可以通过回滚恢复。 两阶段提交协议：在分布式系统中，使用两阶段提交协议（2PC）确保所有参与的节点要么都提交，要么都回滚。一致性（Consistency）：事务在执行前后，数据库的状态必须保持一致，任何事务的执行都不能破坏数据库的一致性。约束和触发器：通过定义数据库约束（主键、外键、唯一性约束）和触发器，确保数据在执行前后符合业务规则。 完整性约束：在事务开始前和结束后，数据库的完整性约束必须得到满足。
2. 隔离性（Isolation）：多个事务并发执行时，每个事务的执行不应受到其他事务的影响，事务之间是相互隔离的。锁机制：通过锁机制（行级锁、表级锁）来控制并发访问，确保在执行时，其他事务不能修改正在处理的数据。多版本并发控制（MVCC）：通过维护数据的多个版本，允许读取旧版本的数据，从而避免锁竞争，提高并发性能。
3. 持久性（Durability）：一旦事务提交，其结果是永久性的，即使系统崩溃或故障，已提交的事务数据也不会丢失。日志持久化：在事务提交时，将事务的 日志记录 持久化到磁盘中，确保即使系统崩溃也能通过日志恢复数据。写入策略：使用写入后日志（Write-Ahead Logging）策略，确保在数据写入数据库之前，相关日志先写入磁盘。

2.3.1.2 并行事务会引发什么问题？

1. 脏读：一个事务读取到了另一个事务未提交的修改数据。
2. 不可重复读：在同一个事务中，多次读取同一个数据时，第二次读取发现数据发生了变化。
3. 幻读：在同一个事务中，多次读取同一个范围内的数据时，第二次读取发现数据的数量发生了变化。

2.3.1.3 事务的隔离级别及原理？

1. 读未提交：存在脏读、不可重复读、幻读问题。一个事务可以读取另一个事务未提交的数据。实现：事务读取数据时，不会等待其他事务释放锁，因此可能会脏读。
2. 读提交：避免了脏读，存在不可重复读、幻读问题。多次读取同一数据集合时，每次读取的结果可能不同。实现：事务读取数据时，必须等待其他事务提交后才能读取。方法：共享锁读取，排他锁写入。
3. 可重复读：避免了脏读、不可重复读，存在幻读问题。当基于范围条件查询数据时，可能会看到其他事务新插入的行。实现：事务在执行第一次读操作时，会创建快照，之后的查询都基于快照。方法：MVCC，间隙锁解决大部分幻读。即使其他事务提交了新的变更，也不会影响当前事务的读取。但是，新插入的行不会包含在快照中，可能出现幻读。
4. 串行化：避免了脏读、不可重复读、幻读问题。方法：排他锁彻底解决幻读，确保事务串行执行。实现：事务按顺序依次执行，通过严格的锁机制来保证事务的隔离性。读操作时加上 S 锁，写操作时加上 X 锁。

2.3.1.4 MVCC 实现原理？

1. MVCC 实现原理：

• 隐藏字段：MVCC 是 乐观锁 的一种实现方式。InnoDB存储引擎的每行记录都会包含几个隐藏的字段，如trx_id（最近一次修改该行的事务ID）和roll_pointer（回滚指针，指向该行的上一个版本所在的undo log）。
• Undo Log：用于记录行数据的变更历史，以便在事务失败或需要回滚时恢复数据。通过保存行的旧版本来允许快照读。
• Read View：事务在执行快照读时创建的当前快照，它记录并维护当前活跃事务的ID，并用于判断数据版本的可见性。

2. MVCC 工作流程：

1. 修改数据： 保存旧版本：首先，系统会把当前的数据保存起来。这个保存旧数据的地方，称之为 Undo Log。创建新版本：然后，系统会在账本的空白页上创建一个新的版本，这个新版本就是修改后的数据。
2. 读取数据：当有人想要读取数据时，系统会根据一些规则（Read View）来决定他们应该看哪个版本的数据。 如果他们不需要修改数据，通常会看到最新的数据，但不是最新的那个版本。如果有人在读取数据时，另一个人刚好在修改同一数据，系统会确保他们读到的是修改之前的版本。
3. 清理旧版本：随着时间的推移，旧版本最终会变得不再需要，因为所有事务都已经完成了，没有人再查看它们。

2.3.1.5 读提交是如何工作的？

读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。

• 例子：假设事务 A（id 为 51）和事务 B（id 为 52）并发执行。

1. 事务 B 第一次读取数据：此时 undo log 链第一行 trx_id = 50 < min_trx_id，所以直接取余额为 100 万。事务 A 修改余额为 200 万，此时 undo log 链头增加一行 trx_id = 51。
2. 事务 B 第二次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。事务 A 提交，影响了事务 B 的 Read View。
3. 事务 B 第三次读取数据：此时 undo log 链第一行 trx_id = 51 < min_trx_id，所以直接取余额为 200 万。

2.3.1.6 可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

• 例子：假设事务 A（id 为 51）和事务 B（id 为 52）并发执行。

1. 事务 B 第一次读取数据：此时 undo log 链第一行 trx_id = 50 < min_trx_id，所以直接取余额为 100 万。事务 A 修改余额为 200 万，此时 undo log 链头增加一行 trx_id = 51。
2. 事务 B 第二次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。事务 A 提交，并不影响事务 B 的 Read View。
3. 事务 B 第三次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。

2.3.2 可重复读完全解决幻读了吗？

2.3.2.1 什么是幻读？

例：假设一个事务在 T1 和 T2 分别执行了下面语句，途中没有执行其他语句：

只要 T1 和 T2 时刻执行产生的结果集是不相同的，那就发生了幻读的问题，比如：

• T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 6 条行记录，那就发生了幻读的问题。
• T1 时间执行的结果是有 5 条行记录，而 T2 时间执行的结果是有 4 条行记录，也是发生了幻读的问题。

2.3.2.2 幻读被完全解决了吗？

InnoDB 引擎的默认隔离级别虽然是可重复读，但也很大程度上避免了幻读现象，解决的方案有两种：

• 快照读：不会锁定任何行记录，因此不会阻塞其他事务的读写，从而提高了并发性能。通过 MVCC 的 Read View 解决幻读。快照读适用于 SELECT 操作，但不包括带有 FOR UPDATE 或 LOCK IN SHARE MODE 的语句。
• 当前读：会锁定数据行，因此可能会阻塞其他事务的读写操作。通过 next-key lock（记录锁 + 间隙锁）阻塞方式解决幻读。在读取之前需要获取对应记录的锁，例如 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句。

1. 场景一：空记录幻读

1. 事务 A 执行 查询，返回了空结果。
2. 事务 B 执行 插入，并提交事务。
3. 事务 A 执行 更新成功。（看不到 B 插入的记录）
4. 事务 A 执行 查询，返回了 。

2. 场景二：范围条件下的幻读

1. 事务 A 执行 查询，返回了 3 条记录。
2. 事务 B 执行 插入成功，并提交事务。（A 没加 next-key lock）
3. 事务 A 执行 查询，返回了 4 条记录。

2.4 MySQL 锁

2.4.1 MySQL 有哪些锁？

1. 全局锁：适用于需要对 整个数据库 或表进行全面操作的情况，保证数据一致性。
2. 表级锁：适用于操作 范围较大 的场景，简单但可能降低并发性能。
3. 行级锁：适用于 高并发 环境，提供更细粒度的锁控制，允许更高的并发性。

2.4.1.1 全局锁

主要用于执行 全库逻辑备份，防止数据或表结构的更新导致备份文件与预期不符。

• flush tables with read lock;：执行后进入只读状态，阻塞其他线程的增删改操作和表结构更改。
• unlock tables;：全局锁在会话断开时会自动释放。

缺点：业务不能更新数据，可能造成业务停顿；备份大型数据库时可能耗费较长时间。

替代：可以使用事务来进行备份。可重复读隔离级别保证备份期间数据的一致性，允许业务继续进行读写操作。

2.4.1.2 表级锁

1. 表锁：适用于需要对 整张表 进行操作的情况，如执行大规模的数据修改、删除或批量更新。由于其锁粒度大，它会降低并发性能，因此通常不适合高并发环境。
2. 元数据锁：适用于 DDL 操作（如 ALTER TABLE、DROP TABLE 等）时需要使用元数据锁。它确保其他事务不能修改表的结构，以防止在 DDL 操作时发生冲突。
3. 意向锁：适用于表级别的 表锁和行锁 的协调，以提高锁的效率。在使用行级锁时，意向锁确保其他事务能够知道事务的意图，以避免不必要的锁冲突和性能瓶颈。
4. 自增锁：适用于在插入新记录到 自增列 时，MySQL 需要确保生成的自增值是唯一且连续的。自增锁确保即使在高并发的环境中，也能保持自增列的值正确。

2.4.1.3 行级锁

1. 行级锁

• 普通的 SELECT 语句属于快照读，不会对记录加锁。要加行级锁可使用：SELECT ... LOCK IN SHARE MODE;：加共享锁，允许其他事务加共享锁，但不允许加排他锁。SELECT ... FOR UPDATE;：加排他锁，不允许其他事务加共享锁或排他锁。

2. 行级锁的类型

1. Record Lock（记录锁）：仅锁定一条记录。有 S 锁和 X 锁。S 锁与 S 锁兼容，与 X 锁互斥；X 锁与 S 锁和 X 锁都互斥。
2. Gap Lock（间隙锁）：用于锁定一个范围，防止 幻读 现象。有 S 锁和 X 锁。S 锁与 X 锁和 X 锁互相兼容。
3. Next-Key Lock（临键锁）：记录锁 + 间隙锁，用于锁定一个范围并包含记录本身。可以防止其他事务在被保护记录前插入新记录或修改被保护记录。
4. 插入意向锁：一种特殊的间隙锁，用于指示事务想要在某个位置插入新记录，但受其他事务间隙锁的阻塞。与普通的间隙锁相比，它锁住的是一个点而不是一个区间。

2.4.2 MySQL 是怎么加锁的

2.4.2.1 什么 SQL 语句会加行级锁？

MySQL 中的行级锁主要由 InnoDB 引擎支持，而 MyISAM 引擎不支持行级锁。

1. 锁定读

• 普通的 SELECT 查询不会对记录加锁（除了串行化隔离级别），因为它是快照读，通过 MVCC 实现。
• 如果要在查询时对记录加行级锁，可以使用以下两种方式，这些语句被称为锁定读：共享锁（S 锁）：SELECT ... LOCK IN SHARE MODE;排他锁（X 锁）：SELECT ... FOR UPDATE;
• 这两种锁定读操作必须在事务中使用，可以通过 BEGIN 或 START TRANSACTION 开启事务。

2. 锁定删/改

• 除了锁定读操作，以下数据操作语句也会对记录加行级锁，锁的类型为排他锁（X锁）： DELETE：DELETE FROM table WHERE ...;UPDATE：UPDATE table SET ... WHERE ...;

2.4.2.2 行级锁有哪些种类？

在 MySQL 的 InnoDB 引擎中，行级锁有三种主要类型，它们在不同的事务隔离级别下起着不同的作用。

1. Record Lock（记录锁）

• 特点：针对单个记录加锁，分为 S 锁（共享锁）和 X 锁（排他锁）。
• 应用：常用于 SELECT ... FOR UPDATE 语句中，确保读取的记录在事务提交前不会被修改或删除。

2. Gap Lock（间隙锁）

• 特点：锁定一个范围，但不包括记录本身。目的是防止幻读现象，在可重复读隔离级别下使用。
• 应用：当事务需要确保某个范围内不存在新插入的记录时，使用间隙锁防止其他事务在范围内插入新记录。

2. Next-Key Lock（临键锁）

• 特点：记录锁 + 间隙锁，锁定一个范围并包括记录本身。防止其他事务在范围内插入或修改已有记录。
• 应用：典型用于 和 操作中，保护读取和更新的数据行。

2.4.2.3 MySQL 是怎么加行级锁的？

加行级锁的对象是索引，加锁的基本单位 next-key lock 是 左开右闭 区间，而间隙锁是 左开右开 区间。

但如果在能使用记录锁或者间隙锁就能避免幻读的场景下， next-key lock 就会退化成记录锁或间隙锁。

<img src=https://www.nowcoder.com/discuss/"C:UsersAdministratorAppDataRoamingTypora ypora-user-imagesbaguwen8051341.jpg" alt="8051341" style="zoom: 67%;" />

1. 唯一索引等值查询

• 记录存在的查询：select * from user where id = 1 for update;id = 1 的记录加了记录锁。
• 记录不存在的查询：select * from user where id = 2 for update;id = 5 的记录加了 (1, 5) 的间隙锁。

2. 唯一索引范围查询

• 针对大于的范围查询：select * from user where id > 15 for update;id = 20 的记录加了 (15, 20] 的 next-key lock，特殊记录加了 (20, +∞] 的 next-key lock。
• 针对大于等于的范围查询：select * from user where id >= 15 for update;id = 15 的记录加了记录锁，id = 20 的记录加了 (15, 20] 的 next-key lock，特殊记录加了 (20, +∞] 的 next-key lock。
• 针对小于的范围查询：select * from user where id < 5 for update;id = 1 的记录加了 (-∞, 1] 的 next-key lock，id = 5 的记录加了 (1, 5) 的间隙锁。
• 针对小于等于的范围查询：select * from user where id <= 5 for update;id = 1 的记录加了 (-∞, 1] 的 next-key lock，id = 5 的记录加了 (1, 5] 的 next-key lock。
• 针对小于的范围查询：select * from user where id < 6 for update;id = 1 的记录加了 (-∞, 1] 的 next-key lock，id = 5 的记录加了 (1, 5] 的 next-key lock，id = 10 的记录加了 (5, 10) 的间隙锁。

2. 非唯一索引等值查询

• 记录存在的查询：select * from user where age = 22 for update;age = 22 的记录加了记录锁。
• 记录不存在的查询：select * from user where age = 25 for update;没有加任何锁。

4. 非唯一索引范围查询

针对大于的范围查询：

• age = 22 加了 (21, 22] 的 next-key lock，age = 39 加了 (22, 39] 的 next-key lock，特殊记录加了 (39, +∞] 的 next-key lock。
• id = 10 的主键索引加了记录锁，id = 20 的主键索引加了记录锁。

5. 没有加索引的查询

可能会导致全表扫描，从而对并发操作产生严重影响。

2.4.2.4 设计一个行级锁的死锁例子？

2.5 MySQL 日志

2.5.1 为什么需要 undo log？

• 事务回滚：记录事务前的的数据状态，支持事务回滚。
• MVCC：实现多版本并发控制，支持快照读。
• 结构：由事务ID和回滚指针组成版本链。

2.5.2 为什么需要 Buffer Pool？

• 数据缓存：减少磁盘I/O，提高数据访问速度。
• 页面管理：缓存数据页和索引页，使用页目录快速定位记录。

2.5.3 为什么需要 redo log ？

• 持久性：确保事务提交后的数据修改不因故障丢失。
• WAL技术：先写日志再写数据，提高写入性能。

2.5.4 为什么需要 binlog ？

• 数据备份：记录修改用于数据恢复。
• 主从复制：记录修改用于主从复制。

2.5.5 MySQL 磁盘 I/O 很高，有什么优化的方法？

• 优化策略：延迟刷盘时机，减少刷盘频率，如调整redo log和binlog的刷盘策略。
• 注意事项：延迟刷盘可能增加系统响应时间，存在数据丢失风险。

2.5.6 MySQL 三种日志？主从架构？主从复制？

2.6 MySQL 其他

2.6.1 常用的 SQL 查询语句？

2.6.2 如何优化 join 语句？left join 和 right join 的区别？

1. 如何优化 join 语句？

1. 使用合适的 类型：明确指出使用 ，这样可以加快查找速度。
2. 添加索引：确保 表的 列和 表的 列上有索引。
3. 将小表 作为驱动表（即 的左边）会更有效，因为数据库可以更快的遍历小表。

2. left join 和 right join 的区别？

• LEFT JOIN 返回左表的所有记录和匹配的右表记录，对于没有匹配到的记录右边列显示 NULL。
• RIGHT JOIN 返回右表的所有记录和匹配的左表记录，对于没有匹配到的记录左边列显示 NULL。

2.6.3 数据仓库的分层？数据库的三个范式？

1. 源系统层：提供原始数据，未经任何转换或清洗。 例子：一个零售商的交易数据库记录了顾客的每一笔购买。
2. ETL 层：确保数据的一致性和准确性，为后续分析提供干净、标准化的数据。 例子：从交易数据库提取数据，清洗无效记录，转换日期格式，并将数据加载到数据仓库。
3. 数据集成层：提供一个缓冲区，以便在数据加载到更高层次之前进行进一步的清洗和验证。 例子：清洗后的数据被暂存，在加载到数据仓库前进行最终验证和转换。
4. 数据仓库层：提供统一的数据视图，支持复杂的查询和分析。 例子：整合了来自不同源的数据，如销售、客户和库存数据，以支持企业级报告和分析。
5. 数据集市层：为特定的用户群体或应用提供优化的、易于访问的数据。

   
   

   例子：销售团队专用的数据集市，包含优化过的销售和客户数据。
6. 呈现层：为用户提供直观的数据访问和展示方式，帮助他们理解数据并做出决策。 例子：管理仪表板展示销售趋势和客户满意度，帮助管理层做出策略决策。

• 第一范式 (1NF): 确保每个字段都是原子的。反例：1101, 102MathMr. A
• 第二范式 (2NF): 消除部分依赖，所有非主键字段都依赖于主键。反例：1101MathMr. A2101MathMr. A
• 第三范式 (3NF): 消除传递依赖，非主键字段不能依赖于其他非主键字段。反例：1101MathMr. A2102EnglishMr. B

2.6.4 数据库中的主键可以换成自己表结构中的某个字段吗？

主键是用来唯一标识表中每条记录的字段或字段组合。理论上，可以选择任何字段作为主键，但必须满足以下条件：

1. 唯一性：主键字段的值必须在表中是唯一的，不能有重复的值。
2. 非空性：主键字段的值不能为 NULL。
3. 稳定性：主键字段的值在记录的生命周期内不应该改变，因为主键用于索引和关联其他表。
4. 性能：选择作为主键的字段应该是查询效率较高的字段，以避免影响数据库性能。

在实际应用中，通常会创建一个自增的整数字段作为主键，因为它可以保证唯一性和非空性，并且优化了索引性能。

2.6.5 Mybatis 如果我想查询多个ID，xml应该怎么写？

2.6.6 MySQL 分库分表？依据是什么？后续查询条件？

1. 分库：将数据分散到多个数据库中，以减轻单个数据库的负担。水平分库：按照某种规则划分，通常是将用户ID或者地区划分，可以提高并发处理能力和负载均衡。垂直分库：按照不同的表划分，通常是将相关性不高的表拆分到不同的数据库，减少数据库之间的冲突和竞争。
2. 分表：将一个大的表拆分为多个表，以提高查询性能和并发处理能力。水平分表：按照行进行分割，分散到多个表中，例如按照日期、地区等分割。可以减少单表的数据量，提高查询效率。垂直分表：按照字段进行分割，将表中部分字段拆分到不同的表中，通常是将大字段或者不经常使用的字段独立出来。
3. 在分库分表后，添加 查询条件 通常需要考虑：确定分片键：在分库分表时，需要确定一个或多个字段作为分片键（sharding key），用于决定数据存储的位置。添加查询条件时，应确保这些条件包含分片键，以便正确地定位到数据所在的库或表。使用范围查询：对于水平分表和分库，可以利用分片键的范围查询来优化性能。例如，如果按照用户ID分库，可以添加查询条件来指定用户ID的范围。

2.6.7 查询到了B+树的叶子节点，之后的查找流程是？

• InnoDB存储引擎最小存储单元是页，页可以放一行一行的数据（叶子节点），也可以放主键和指针（非叶子节点）。
• 索引本身并不能直接找到具体的一条记录，只能知道在哪个页上。 当页被加载到内存后，可以直接顺序扫描该页来找到记录。

2.6.8 如何建立索引？索引建太多的缺点？影响读还是写效率？

建立索引的基本语法：

1. 索引建太多的缺点？

• 占用存储空间：每个索引都需要占用额外的存储空间，索引太多会导致数据库占用更多的磁盘空间。
• 影响写效率：索引需要维护，数据的增删改操作都需要更新索引，这会增加额外的写操作，从而降低写入性能。
• 维护开销：索引的维护需要额外的CPU和IO资源，尤其是在数据更新频繁的场景下，索引的维护成本会更高。
• 锁竞争：大量的索引可能会增加锁竞争，尤其是在写操作频繁的场景下，可能会影响数据库的并发性能。

本文地址：http://w.yusign.com/quote/6676.html    述古往 http://w.yusign.com/static/ , 查看更多