2 - MySQL 篇
2.1 MySQL 基础
2.1.1 MySQL 执行流程是怎样的？
- 连接器：客户端与MySQL服务建立连接，进行权限验证。
- 查询缓存：MySQL检查是否有缓存的查询结果可以直接返回（MySQL 8.0中已不推荐使用）。
- 解析器：对SQL语句进行语法和语义分析，构建内部的数据结构。
- 预处理器：处理SQL语句中的一些预处理操作，如视图展开等。
- 优化器：生成多个可能的执行计划，并选择最优的执行计划。
- 执行器：根据优化器提供的执行计划，执行SQL语句，调用存储引擎接口获取数据。
2.1.2 第一步：连接器
- 连接器功能：负责客户端与MySQL服务之间的连接建立和权限验证。
- 连接命令：在Linux系统中，使用
mysql -h$ip -u$user -p命令连接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
- 预处理阶段：检查表和字段存在性，扩展
SELECT *。
- 优化阶段：生成执行计划，选择最优方案。
- 执行阶段：按照执行计划执行查询，包括数据读取、过滤、排序、聚合等。
2.1.6 MySQL 如何优化？
- 使用索引：合理使用索引是提升查询性能的关键。可以使用
EXPLAIN关键字分析执行计划，查看是否有效利用了索引。CREATE INDEX idx_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = 'example@163.com';</pre>
- 查询语句优化：优化查询语句，比如避免使用
SELECT *，尽量指定需要的列；减少子查询和复杂的JOIN操作。SELECT * FROM employees WHERE department_id = 5; -- 不推荐
SELECT id, name FROM employees WHERE department_id = 5; -- 推荐</pre>
- 使用缓存技术：利用缓存技术，如 Redis 或 Memcached，来缓存热点数据，减少数据库的访问压力。
SET hot_product_1 "details of product 1";
SET hot_product_2 "details of product 2";</pre>
- 避免使用函数和操作符：在
WHERE子句中避免对列使用函数或操作符，这样可以更好地利用索引。SELECT * FROM users WHERE LENGTH(name) = 5; -- 不推荐
SELECT * FROM users WHERE name = 'John'; -- 推荐</pre>
2.2 MySQL 索引
2.2.1 存储引擎
2.2.1.1 MySQL 存储引擎？
InnoDB：MySQL 的默认存储引擎。支持事务处理、行级锁定和外键约束。支持聚簇索引。
MyISAM：早期 MySQL 的默认存储引擎。不支持事务处理，支持表级锁定。使用非聚簇索引。
MEMORY：将所有数据存储在内存中，访问速度快。适合临时表和快速读取的场景。数据在数据库重启时会丢失。
2.2.1.2 InnoDB 和 MyISAM 区别？为什么选前者？
1. MySQL 常见引擎：
- InnoDB：这是 MySQL 默认的存储引擎，支持事务处理、外键约束、行级锁定和崩溃恢复能力。
- MyISAM：这是 MySQL 早期的默认存储引擎，它提供了高速的读取性能和全文搜索能力，但不支持事务和行级锁定。
- Memory：这个引擎将所有数据存储在内存中，提供极快的访问速度，但数据在数据库重启后会丢失。
2. InnoDB 和 MyISAM 的区别：
- 事务支持：InnoDB 支持事务，这意味着它支持事务的四个基本特性：原子性、一致性、隔离性、持久性（ACID）。
- 锁定机制：InnoDB 支持行级锁定，这可以减少锁定带来的冲突，提高并发性能。
- 外键约束：InnoDB 支持外键，有助于保持数据的完整性。
3. 为什么选择 InnoDB：
- 事务支持：对于需要事务支持的应用，InnoDB 是更好的选择，因为它可以保证数据的一致性和完整性。
- 锁定机制：InnoDB 的行级锁定提供了更好的并发性能，尤其是在写操作频繁的场景下。
- 外键约束：如果应用需要维护数据的引用完整性，InnoDB 的外键支持是必需的。
2.2.1.3 InnoDB 底层结构？
- 内存结构：缓冲池（
Buffer Pool）、更改缓冲（Change Buffer）、日志缓冲（Log Buffer）、自适应哈希索引。
- 磁盘结构：表空间、数据字典、双写缓冲、重做日志（
Redo Log）、撤销日志（Undo Logs）。1. 表空间（`Tablespaces`）：用于存储数据文件，包括系统表空间、独立表空间。 2. 段（`Segments`）：表空间被划分为多个段，用于存储数据和索引。 3. 区（`Extent`）：段由一组区组成，通常 1MB 大小，用于磁盘读写操作。 4. 页（`Page`）：区由页组成，通常是 16KB 大小，是存储数据的最小单位。 5. 行（`Row`）：数据行存储在页中，使用 B+ 树索引数据结构。
2.2.1.4 MySQL 数据在页中是如何排列的？为什么要这么排列？
- 页中如何排列：
* 在页内，数据按照一定的顺序排列，通常是按照**主键的顺序**。这样可以保证数据的连续性，减少页分裂的发生。 * 每个页都有一个**页目录**，指向页内数据的位置，这样可以快速定位到页内特定的数据行，而不需要扫描整个页。
- 为什么要这么排列：
* 提高性能：有序排列和索引结构可以显著提高数据检索和更新的性能。 * 减少磁盘I/O：通过减少树的高度和优化页内数据的排列，可以减少访问数据时所需的磁盘I/O操作。 * 支持事务：对于支持事务的存储引擎，这种排列方式也有助于保持事务的ACID属性，尤其是在并发环境下。
2.2.2 索引的分类
2.2.2.1 按数据结构分类
1. 索引的存储
- InnoDB 将索引和数据一起存储在
.ibd文件，使用B+树作为索引结构，每个索引页中既存储索引键也存储数据行。
- 对于二级索引（非聚簇索引），索引页中存储的是 索引键 和对应的 聚簇索引键（即主键）。
2. 索引的数据结构
- B+Tree 索引的特点：
1. 主键索引的叶子节点存储：**完整的数据行**。比如：`(id=1, name="Alice", age=30, ...)` 主键索引的非叶子节点存储：主键值和指向子节点的指针。比如：`(id=1) -> 下层节点` 2. 二级索引的叶子节点存储：**索引列值和主键值**。比如：`(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. 主键索引：建立在表的主键字段上。
- 每张表只能有一个主键索引，索引列的值不允许有空值。
- 主键索引在创建表的时候定义，使用
PRIMARY KEY关键字。2. 唯一索引：建立在表的
UNIQUE字段上。
- 每张表可以有多个唯一索引，索引列的值必须唯一，但可以有空值。
- 唯一索引可以在创建表时定义，也可以在表创建后通过
CREATE UNIQUE INDEX命令创建。2. 普通索引：建立在表的普通字段上。
- 每张表可以有多个普通索引。
- 普通索引可以在创建表时定义，也可以在表创建后通过
CREATE INDEX命令创建。4. 前缀索引：建立在表的字符类字段的前几个字符上，而不是在整个字段上建立索引。
- 目的是减少索引占用的存储空间，提升查询效率。
- 前缀索引可以在创建表时定义，也可以在表创建后通过
CREATE INDEX命令创建。
2.2.2.4 按字段个数分类
1. 联合索引
- 联合索引的非叶子节点使用多个字段的值作为 B+Tree 的 key 值，按照创建索引时定义的字段顺序排序数据。
- 最左匹配原则：联合索引查询遵循最左匹配原则，即必须按照索引顺序的最左边的字段开始匹配。
- 查询条件的影响：利用联合索引：查询条件包括最左边的字段，如
where a = 1或where a = 1 and b = 2。
- 联合索引失效：查询条件不从最左边的字段开始，如
where b = 2。2. 联合索引范围查询
范围查询中的字段可以使用联合索引，但只能到达第一个不是范围查询的字段，后面的字段将无法利用联合索引。
例 1: select * from t_table where a > 1 and b = 2;
- 分析：由于 a > 1 是范围查询，联合索引只能使用到 a 字段。因为 b 字段的值在 a > 1 的范围内是无序的，无法利用索引。
例 2: select * from t_table where a >= 1 and b = 2;
- 分析：由于 a >= 1 不是范围查询，联合索引可以使用到 b 字段。因为 b 字段的值在 a = 1 的条件下是有序的，可以利用索引。
2. 索引下推优化（Using index condition）
- 索引下推优化可以在联合索引遍历中，先判断索引中包含的字段，过滤掉不满足条件的记录，从而减少回表次数。
- 索引区分度与优化：建立联合索引时，应将 区分度高 的字段排在前面，这样能更好地利用索引。
- 联合索引排序优化：如
WHERE status = 1 ORDER BY create_time ASC;建立联合索引（status, create_time）。
2.2.3 什么时候创建索引？
1. 索引的优缺点：
- 优点：唯一性约束，提高查询速度，优化排序和分组操作。
- 缺点：占用物理空间，有维护成本，影响更新性能。
2. 何时需要创建索引？
- 唯一性字段：如商品编码等。
- 频繁用于查询条件的字段：提高查询效率，可以考虑创建索引。
- 用于排序和分组的字段：优化排序和分组操作，减少不必要的排序过程。
3. 何时不需要创建索引？
- 重复值多的字段：如性别字段，索引不会提高查询效率。
- 频繁更新的字段：如用户余额等，索引维护成本高，影响性能。
- 数据量较小的表：小表通常全表扫描效率更高。
2.2.4 有什么优化索引的方法？
- 使用前缀索引：
CREATE INDEX idx_username_prefix ON users(username(10));
- 使用覆盖索引：
CREATE INDEX idx_cover ON users(username, age);
- 主键索引自增：存储更紧凑，主键字段更小（二级索引省空间），磁盘的随机 I/O 访问更少，避免了页分裂。
- 索引设置非空：
COUNT会省略没有意义的值 NULL 的行，但是它又会占用物理空间。
2.2.4.1 前缀索引优化
1. 优点：
- 减小索引字段大小：使用字段的前几个字符而不是整个字段作为索引，节省存储空间。
- 提高查询速度：减少了索引的大小，加快了索引的扫描速度，特别是在大字符串字段上。
2. 缺点：
- 无法用于排序：前缀索引只能加速前缀匹配的查询，对于需要全字段排序的查询不适用。
- 无法用作覆盖索引：前缀索引只包含部分数据，无法覆盖完整的查询需求。
CREATE INDEX idx_email_prefix ON users(email(10));
SELECT * FROM users WHERE email LIKE 'example%@%';
2.2.4.2 覆盖索引优化
1. 覆盖索引
- 在查询过程中，所有字段都可以从索引的 B+Tree 结构中的叶子节点获取，而不需要额外的回表操作。
- 优化原理：
* **减少 I/O 操作**：避免了访问主表数据行，仅通过索引即可满足查询需求。 * **提高查询速度**：因为不再需要额外的查找和读取操作，查询响应时间更短。
- 如何实现覆盖索引优化：
* 建立合适的联合索引：例如，对于查询商品名称和价格的需求，可以创建联合索引（商品 ID、名称、价格）。 * 利用联合索引的顺序：索引的字段顺序应与查询的字段顺序一致，利用索引的排序特性加速查询。2. 适用场景：
- 当需要查询的字段可以完全通过索引覆盖时，使用覆盖索引可以最大程度地提升查询效率。
- 特别适合那些只需返回 部分字段信息 的查询，如仅查询商品的名称和价格而不需要其他信息时。
CREATE INDEX idx_order_date_id ON orders(order_date, order_id);
SELECT order_id, order_date FROM orders WHERE order_date = '2024-08-12';
2.2.4.3 主键索引自增
- 主键索引：会按照主键的顺序将数据存储在磁盘上，这样相邻的数据行物理上也是相邻的，减少了磁盘的 随机 I/O 访问。
- 插入性能：避免了页分裂操作：当使用非自增主键时，插入导致已满页面分裂，重新排列数据，影响性能并增加空间碎片化。
- 空间利用：数据存储 更加紧凑，避免了存储空间的浪费和额外的碎片化，有助于提高整体数据库性能和响应速度。
- 二级索引效率：主键字段越小，二级索引的叶子节点也就越小，减少了非主键索引的存储空间和访问成本，提升了查询效率。
2.2.4.4 索引设置非空
- 索引列存在
NULL会导致优化器在做索引选择的时候更加难以优化，比如 count 会省略 索引值为NULL的行。
NULL值是一个没意义的值，但是它会 占用物理空间，所以会带来的存储空间的问题。
2.2.5 索引的失效场景？
- 模糊匹配：
SELECT * FROM users WHERE username LIKE '%john%';
- 类型不匹配：
SELECT * FROM users WHERE username = 123;
- 函数操作：
SELECT * FROM products WHERE DATE(created_at) = '2024-08-01';
- 未满足最左匹配原则：
SELECT * FROM users WHERE last_name = 'B' AND first_name = 'A';
- 使用 OR：
SELECT * FROM products WHERE price = 100 OR name = 'unique_name';
2.3 MySQL 事务
2.3.1 事务隔离级别是怎么实现的？
2.3.1.1 事务的四个特性及原理？
- 原子性（Atomicity）：事务是一个不可分割的操作序列，要么全部执行成功，要么全部不执行。
* 日志记录：使用事务日志（`Redo Log、Undo Log`）来记录事务的操作。如果事务执行失败，可以通过回滚恢复。 * 两阶段提交协议：在分布式系统中，使用两阶段提交协议（2PC）确保所有参与的节点要么都提交，要么都回滚。
- 一致性（Consistency）：事务在执行前后，数据库的状态必须保持一致，任何事务的执行都不能破坏数据库的一致性。
- 约束和触发器：通过定义数据库约束（
主键、外键、唯一性约束）和触发器，确保数据在执行前后符合业务规则。* 完整性约束：在事务开始前和结束后，数据库的完整性约束必须得到满足。
- 隔离性（Isolation）：多个事务并发执行时，每个事务的执行不应受到其他事务的影响，事务之间是相互隔离的。
* 锁机制：通过锁机制（`行级锁、表级锁`）来控制并发访问，确保在执行时，其他事务不能修改正在处理的数据。 * 多版本并发控制（`MVCC`）：通过维护数据的多个版本，允许读取旧版本的数据，从而避免锁竞争，提高并发性能。
- 持久性（Durability）：一旦事务提交，其结果是永久性的，即使系统崩溃或故障，已提交的事务数据也不会丢失。
* 日志持久化：在事务提交时，将事务的 `日志记录` 持久化到磁盘中，确保即使系统崩溃也能通过日志恢复数据。 * 写入策略：使用写入后日志（`Write-Ahead Logging`）策略，确保在数据写入数据库之前，相关日志先写入磁盘。
2.3.1.2 并行事务会引发什么问题？
- 脏读：一个事务读取到了另一个事务未提交的修改数据。
- 不可重复读：在同一个事务中，多次读取同一个数据时，第二次读取发现数据发生了变化。
- 幻读：在同一个事务中，多次读取同一个范围内的数据时，第二次读取发现数据的数量发生了变化。
2.3.1.3 事务的隔离级别及原理？
- 读未提交：存在脏读、不可重复读、幻读问题。
一个事务可以读取另一个事务未提交的数据。* 实现：事务读取数据时，不会等待其他事务释放锁，因此可能会脏读。
- 读提交：避免了脏读，存在不可重复读、幻读问题。
多次读取同一数据集合时，每次读取的结果可能不同。* 实现：事务读取数据时，必须等待其他事务提交后才能读取。**方法：共享锁读取，排他锁写入。**
- 可重复读：避免了脏读、不可重复读，存在幻读问题。
当基于范围条件查询数据时，可能会看到其他事务新插入的行。* 实现：事务在执行第一次读操作时，会创建快照，之后的查询都基于快照。**方法：MVCC，间隙锁解决大部分幻读。** 即使其他事务提交了新的变更，也不会影响当前事务的读取。但是，新插入的行不会包含在快照中，可能出现幻读。
- 串行化：避免了脏读、不可重复读、幻读问题。方法：排他锁彻底解决幻读，确保事务串行执行。
* 实现：事务按顺序依次执行，通过严格的锁机制来保证事务的隔离性。读操作时加上 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 工作流程：
- 修改数据：
* 保存旧版本：首先，系统会把当前的数据保存起来。这个保存旧数据的地方，称之为 Undo Log。 * 创建新版本：然后，系统会在账本的空白页上创建一个新的版本，这个新版本就是修改后的数据。
- 读取数据：当有人想要读取数据时，系统会根据一些规则（Read View）来决定他们应该看哪个版本的数据。
* 如果他们不需要修改数据，通常会看到最新的数据，但不是最新的那个版本。 * 如果有人在读取数据时，另一个人刚好在修改同一数据，系统会确保他们读到的是修改之前的版本。
- 清理旧版本：随着时间的推移，旧版本最终会变得不再需要，因为所有事务都已经完成了，没有人再查看它们。
2.3.1.5 读提交是如何工作的？
读提交隔离级别是在每次读取数据时，都会生成一个新的 Read View。
- 例子：假设事务 A（id 为 51）和事务 B（id 为 52）并发执行。
- 事务 B 第一次读取数据：此时 undo log 链第一行 trx_id = 50 < min_trx_id，所以直接取余额为 100 万。
事务 A 修改余额为 200 万，此时 undo log 链头增加一行 trx_id = 51。
- 事务 B 第二次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。
**事务 A 提交，影响了事务 B 的 Read View。**
- 事务 B 第三次读取数据：此时 undo log 链第一行 trx_id = 51 < min_trx_id，所以直接取余额为 200 万。
2.3.1.6 可重复读是如何工作的？
可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。
- 例子：假设事务 A（id 为 51）和事务 B（id 为 52）并发执行。
- 事务 B 第一次读取数据：此时 undo log 链第一行 trx_id = 50 < min_trx_id，所以直接取余额为 100 万。
事务 A 修改余额为 200 万，此时 undo log 链头增加一行 trx_id = 51。
- 事务 B 第二次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。
**事务 A 提交，并不影响事务 B 的 Read View。**
- 事务 B 第三次读取数据：此时 undo log 链第一行 trx_id = 51 在 m_ids 内，所以沿着链向下找到余额 100 万。
2.3.2 可重复读完全解决幻读了吗？
2.3.2.1 什么是幻读？
例：假设一个事务在 T1 和 T2 分别执行了下面语句，途中没有执行其他语句：
SELECT * FROM t_test WHERE id > 100;只要 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. 场景一：空记录幻读
- 事务 A 执行
select * from t_stu where id = 5;查询，返回了空结果。
- 事务 B 执行
insert into t_stu values(5, '小美', 18);插入，并提交事务。
- 事务 A 执行
update t_stu set name = '小林coding' where id = 5;更新成功。（看不到 B 插入的记录）
- 事务 A 执行
select * from t_stu where id = 5;查询，返回了(5, '小林coding', 18)。2. 场景二：范围条件下的幻读
- 事务 A 执行
select * from t_stu where id > 5;查询，返回了 3 条记录。
- 事务 B 执行
insert into t_stu values(10, '小美', 18);插入成功，并提交事务。（A 没加 next-key lock）
- 事务 A 执行
select * from t_stu where id > 100 for update;查询，返回了 4 条记录。
2.4 MySQL 锁
2.4.1 MySQL 有哪些锁？
- 全局锁：适用于需要对 整个数据库 或表进行全面操作的情况，保证数据一致性。
- 表级锁：适用于操作 范围较大 的场景，简单但可能降低并发性能。
- 行级锁：适用于 高并发 环境，提供更细粒度的锁控制，允许更高的并发性。
2.4.1.1 全局锁
主要用于执行 全库逻辑备份，防止数据或表结构的更新导致备份文件与预期不符。
flush tables with read lock;：执行后进入只读状态，阻塞其他线程的增删改操作和表结构更改。
unlock tables;：全局锁在会话断开时会自动释放。缺点：业务不能更新数据，可能造成业务停顿；备份大型数据库时可能耗费较长时间。
替代：可以使用事务来进行备份。可重复读隔离级别保证备份期间数据的一致性，允许业务继续进行读写操作。
2.4.1.2 表级锁
- 表锁：适用于需要对 整张表 进行操作的情况，如执行大规模的数据修改、删除或批量更新。
由于其锁粒度大，它会降低并发性能，因此通常不适合高并发环境。
- 元数据锁：适用于 DDL 操作（如 ALTER TABLE、DROP TABLE 等）时需要使用元数据锁。
它确保其他事务不能修改表的结构，以防止在 DDL 操作时发生冲突。
- 意向锁：适用于表级别的 表锁和行锁 的协调，以提高锁的效率。
在使用行级锁时，意向锁确保其他事务能够知道事务的意图，以避免不必要的锁冲突和性能瓶颈。
- 自增锁：适用于在插入新记录到 自增列 时，MySQL 需要确保生成的自增值是唯一且连续的。
自增锁确保即使在高并发的环境中，也能保持自增列的值正确。
2.4.1.3 行级锁
1. 行级锁
- 普通的 SELECT 语句属于快照读，不会对记录加锁。要加行级锁可使用：
* `SELECT ... LOCK IN SHARE MODE;`：**加共享锁**，允许其他事务加共享锁，但不允许加排他锁。 * `SELECT ... FOR UPDATE;`：**加排他锁**，不允许其他事务加共享锁或排他锁。2. 行级锁的类型
- Record Lock（记录锁）：仅锁定一条记录。
* 有 S 锁和 X 锁。S 锁与 S 锁兼容，与 X 锁互斥；X 锁与 S 锁和 X 锁都互斥。
- Gap Lock（间隙锁）：用于锁定一个范围，防止 幻读 现象。
* 有 S 锁和 X 锁。S 锁与 X 锁和 X 锁互相兼容。
- Next-Key Lock（临键锁）：记录锁 + 间隙锁，用于锁定一个范围并包含记录本身。
* 可以防止其他事务在被保护记录前插入新记录或修改被保护记录。
- 插入意向锁：一种特殊的间隙锁，用于指示事务想要在某个位置插入新记录，但受其他事务间隙锁的阻塞。
* 与普通的间隙锁相比，它锁住的是一个点而不是一个区间。
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（临键锁）
- 特点：记录锁 + 间隙锁，锁定一个范围并包括记录本身。防止其他事务在范围内插入或修改已有记录。
- 应用：典型用于
SELECT ... FOR UPDATE和UPDATE操作中，保护读取和更新的数据行。
2.4.2.3 MySQL 是怎么加行级锁的？
加行级锁的对象是索引，加锁的基本单位 next-key lock 是 左开右闭 区间，而间隙锁是 左开右开 区间。
但如果在能使用记录锁或者间隙锁就能避免幻读的场景下， next-key lock 就会退化成记录锁或间隙锁。
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. 非唯一索引范围查询
针对大于的范围查询：
SELECT * FROM user WHERE age >= 22 FOR UPDATE;
- 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 设计一个行级锁的死锁例子？
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 事务A尝试锁定ID=1的行（成功）
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 事务A尝试锁定ID=2的行（等待...）
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 事务B尝试锁定ID=2的行（成功）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 事务B尝试锁定ID=1的行（等待...）
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 事务A尝试锁定ID=2的行（等待...）
SELECT * FROM accounts WHERE id = 2 FOR UPDATE; -- 事务B尝试锁定ID=2的行（成功）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 事务B尝试锁定ID=1的行（等待...）
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 三种日志？主从架构？主从复制？
1. MySQL 三种日志？
更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog （归档日志）这三种日志：
- undo log：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于 事务回滚 和 MVCC。
- redo log：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等 故障恢复；
- binlog：是 Server 层生成的日志，主要用于 数据备份 和 主从复制；
2. MySQL 主从架构？主从复制？
主从复制是一种数据库复制技术，用于同步主数据库（Master）的数据到一个或多个从数据库（Slave）。主要用于：
1. 数据备份：从服务器作为数据备份。主服务器的变更通过二进制日志（binlog）记录，并同步到从服务器。
2. 读写分离：主服务器处理写操作，而从服务器处理读操作，从而提高性能和可扩展性。
3. 负载均衡：减轻主服务器的负载。Redis的复制是异步的，可能会导致主从节点之间的数据不一致。
4. 故障转移：如果主节点发生故障，可以手动或自动将一个从节点提升为新的主节点。</pre>
2.6 MySQL 其他
2.6.1 常用的 SQL 查询语句？
-- 1. 基本查询
SELECT 列名称 FROM 表名称;
SELECT DISTINCT 列名称 FROM 表名称;-- 2. 条件查询
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;
SELECT 列名称 FROM 表名称 WHERE 列 LIKE 值;
SELECT 列名称 FROM 表名称 WHERE 列 IN (值1, 值2, ...);
SELECT 列名称 FROM 表名称 WHERE 列 BETWEEN 值1 AND 值2;-- 3. 连接查询
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列名 = 表2.列名;
SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.列名 = 表2.列名;
SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.列名 = 表2.列名;-- 4. 分页和排序
SELECT 列名称 FROM 表名称 LIMIT 值;
SELECT 列名称 FROM 表名称 ORDER BY 列 DESC;-- 5. 聚合和分组
SELECT COUNT(列名称) FROM 表名称;
SELECT 列名称 FROM 表名称 GROUP BY 列名称 HAVING 列 运算符 值;-- 6. 子查询和合并
SELECT 列名称 FROM 表1 WHERE 列1 IN (SELECT 列1 FROM 表2);
SELECT 列名称 FROM 表1 UNION SELECT 列名称 FROM 表2;-- 7. 逻辑判断和窗口函数
SELECT 列名称, CASE WHEN condition1 THEN 值1 WHEN condition2 THEN 值2 END FROM 表;
SELECT 列1, SUM(列2) OVER (PARTITION BY 列1 ORDER BY 列2) FROM 表名称;</pre>
- 查手机号 136 开头的数据：
SELECT * FROM table_name WHERE phone_number LIKE '136%';
2.6.2 如何优化 join 语句？left join 和 right join 的区别？
1. 如何优化 join 语句？
- 使用合适的
JOIN类型：明确指出使用INNER JOIN，这样可以加快查找速度。
- 添加索引：确保
employees表的department_id列和departments表的department_id列上有索引。
- 将小表
departments作为驱动表（即INNER JOIN的左边）会更有效，因为数据库可以更快的遍历小表。2. left join 和 right join 的区别？
- LEFT JOIN 返回左表的所有记录和匹配的右表记录，对于没有匹配到的记录右边列显示 NULL。
- RIGHT JOIN 返回右表的所有记录和匹配的左表记录，对于没有匹配到的记录左边列显示 NULL。
2.6.3 数据仓库的分层？数据库的三个范式？
- 源系统层：提供原始数据，未经任何转换或清洗。
* 例子：一个零售商的交易数据库记录了顾客的每一笔购买。
- ETL 层：确保数据的一致性和准确性，为后续分析提供干净、标准化的数据。
* 例子：从交易数据库提取数据，清洗无效记录，转换日期格式，并将数据加载到数据仓库。
- 数据集成层：提供一个缓冲区，以便在数据加载到更高层次之前进行进一步的清洗和验证。
* 例子：清洗后的数据被暂存，在加载到数据仓库前进行最终验证和转换。
- 数据仓库层：提供统一的数据视图，支持复杂的查询和分析。
* 例子：整合了来自不同源的数据，如销售、客户和库存数据，以支持企业级报告和分析。
- 数据集市层：为特定的用户群体或应用提供优化的、易于访问的数据。
* 例子：销售团队专用的数据集市，包含优化过的销售和客户数据。
- 呈现层：为用户提供直观的数据访问和展示方式，帮助他们理解数据并做出决策。
* 例子：管理仪表板展示销售趋势和客户满意度，帮助管理层做出策略决策。
- 第一范式 (
1NF): 确保每个字段都是原子的。反例：| StudentID | CourseID | CourseName | Instructor | | --- | --- | --- | --- | | 1 | 101, 102 | Math | Mr. A |
- 第二范式 (
2NF): 消除部分依赖，所有非主键字段都依赖于主键。反例：| StudentID | CourseID | CourseName | Instructor | | --- | --- | --- | --- | | 1 | 101 | Math | Mr. A | | 2 | 101 | Math | Mr. A |
- 第三范式 (
3NF): 消除传递依赖，非主键字段不能依赖于其他非主键字段。反例：| StudentID | CourseID | CourseName | Instructor | | --- | --- | --- | --- | | 1 | 101 | Math | Mr. A | | 2 | 102 | English | Mr. B |
2.6.4 数据库中的主键可以换成自己表结构中的某个字段吗？
主键是用来唯一标识表中每条记录的字段或字段组合。理论上，可以选择任何字段作为主键，但必须满足以下条件：
- 唯一性：主键字段的值必须在表中是唯一的，不能有重复的值。
- 非空性：主键字段的值不能为 NULL。
- 稳定性：主键字段的值在记录的生命周期内不应该改变，因为主键用于索引和关联其他表。
- 性能：选择作为主键的字段应该是查询效率较高的字段，以避免影响数据库性能。
在实际应用中，通常会创建一个自增的整数字段作为主键，因为它可以保证唯一性和非空性，并且优化了索引性能。
2.6.5 Mybatis 如果我想查询多个ID，xml应该怎么写？
<select id="selectByIds" resultType="YourResultVO">
SELECT * FROM your_table WHERE id IN
<foreach item="id" index="index" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>
2.6.6 MySQL 分库分表？依据是什么？后续查询条件？
- 分库：将数据分散到多个数据库中，以减轻单个数据库的负担。
* 水平分库：按照某种规则划分，通常是将用户ID或者地区划分，可以提高并发处理能力和负载均衡。 * 垂直分库：按照不同的表划分，通常是将相关性不高的表拆分到不同的数据库，减少数据库之间的冲突和竞争。
- 分表：将一个大的表拆分为多个表，以提高查询性能和并发处理能力。
* 水平分表：按照行进行分割，分散到多个表中，例如按照日期、地区等分割。可以减少单表的数据量，提高查询效率。 * 垂直分表：按照字段进行分割，将表中部分字段拆分到不同的表中，通常是将大字段或者不经常使用的字段独立出来。
- 在分库分表后，添加 查询条件 通常需要考虑：
* 确定分片键：在分库分表时，需要确定一个或多个字段作为分片键（sharding key），用于决定数据存储的位置。 添加查询条件时，应确保这些条件包含分片键，以便正确地定位到数据所在的库或表。 * 使用范围查询：对于水平分表和分库，可以利用分片键的范围查询来优化性能。 例如，如果按照用户ID分库，可以添加查询条件来指定用户ID的范围。
2.6.7 查询到了B+树的叶子节点，之后的查找流程是？
- InnoDB存储引擎最小存储单元是页，页可以放一行一行的数据（叶子节点），也可以放主键和指针（非叶子节点）。
- 索引本身并不能直接找到具体的一条记录，只能知道在哪个页上。 当页被加载到内存后，可以直接顺序扫描该页来找到记录。
2.6.8 如何建立索引？索引建太多的缺点？影响读还是写效率？
建立索引的基本语法：
CREATE INDEX index_name ON table_name (column_name);1. 索引建太多的缺点？
- 占用存储空间：每个索引都需要占用额外的存储空间，索引太多会导致数据库占用更多的磁盘空间。
- 影响写效率：索引需要维护，数据的增删改操作都需要更新索引，这会增加额外的写操作，从而降低写入性能。
- 维护开销：索引的维护需要额外的CPU和IO资源，尤其是在数据更新频繁的场景下，索引的维护成本会更高。
- 锁竞争：大量的索引可能会增加锁竞争，尤其是在写操作频繁的场景下，可能会影响数据库的并发性能。
2. 影响读还是写效率？
- 读效率：合理的索引可以显著提高查询效率，减少全表扫描，加快数据检索速度。
- 写效率：索引的维护会影响写效率，尤其是在更新频繁的场景下，每次增删改都需要更新索引。
2.6.9 MySQL 的并发控制手段？
- 事务：事务是一组原子性的SQL操作，它们要么全部成功，要么全部失败。
* **事务**：通过ACID属性（原子性、一致性、隔离性、持久性）来保证并发操作的安全性。 * **隔离级别**：读未提交、读提交、可重复读、串行化。定义事务受其他并发事务的影响程度，及对其他事务的可见性。 * **多版本并发控制（MVCC）**：通过在事务中读取数据时提供旧版本来允许非锁定读取，减少锁争用并提高并发性能。
- 锁：包括全局锁、表锁和行锁，以控制对数据库资源的并发访问。
* **全局锁**：用于控制对整个数据库实例的访问，如在备份操作时使用。 * **表锁**：在对整个表进行操作时使用，如`LOCK TABLES`和`UNLOCK TABLES`。 * **行锁**：在InnoDB存储引擎中使用，允许在行级别上进行锁定，以提高并发性。
- 乐观锁和悲观锁：
* **乐观锁**：假设冲突很少发生，通常通过版本号或时间戳来实现，如果更新时发现版本号已变化，则放弃操作。 * **悲观锁**：假设冲突会发生，通常在事务开始时就加锁，直到事务结束。
2.6.10 MySQL 慢查询的排除与优化？
- 开启 慢查询日志，准确定位到哪个 sql 语句出现了问题。
- 分析语句，看看是否 加载了额外的数据，可能是查询了多余的行，可能是加载了多余的列，对语句进行分析以及重写。
- 分析语句的执行计划，然后获得其 使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
- 如果对语句的优化已经无法进行，可以考虑表中的 数据量是否太大，如果是的话可以进行横向或者纵向的分表。
