---
title: MySQL 四万字精华总结 + 面试 100 问，和面试官扯皮绰绰有余
date: 2024-05-31
tags:
- MySQL
- Interview
categories: Interview
---
> 写在之前：不建议背书式的去记忆面试题，对技术的提升帮助很小，对正经面试也没什么帮助，准备面试的过程还是要把各个知识点真懂了，然后再连成线。
>
> 个人建议把面试题看作是费曼学习法中的回顾、简化的环节，准备面试的时候，跟着题目先自己讲给自己听，看看自己会满意吗，不满意就继续学习这个点，然后调整自己的话术，如此反复，对这块知识也会理解的更到位，而且面试时候也会得心应手，心仪的 offer 肯定会有的。
>
> 当然，大家有遇到过什么样『有趣』『有含量』的题目，欢迎提出来，一起学习~
## 一、MySQL 架构
> 和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，**插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离**。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
>
> 
>
> - **连接层**：最上层是一些客户端和连接服务。**主要完成一些类似于连接处理、授权认证、及相关的安全方案**。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
>
> - **服务层**：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
>
> - **引擎层**：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
>
> - **存储层**：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互
>
### MySQL 的查询流程具体是怎么样的？
> [!TIP]
>
> 一条 SQL 语句在 MySQL 中如何执行的？
>
> 画出 MySQL 架构图？ 「这种变态问题都能问的出来~」
1. **客户端请求**：客户端通过连接器发送查询到 MySQL 服务器（验证用户身份，给予权限）
2. **查询接收**：连接器接收请求，管理连接
3. **解析器**：对 SQL 进行词法分析和语法分析，转换为解析树
4. **优化器**：优化器生成执行计划，选择最优索引和连接顺序
5. **查询执行器**：执行器执行查询，通过存储引擎接口获取数据
6. **存储引擎**：存储引擎检索数据，返回给执行器
7. **返回结果**：结果通过连接器返回给客户端
------
## 二、存储引擎
> 存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
>
> 使用哪一种引擎可以灵活选择，**一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求**，使用合适的存储引擎，将会提高整个数据库的性能 。
>
> MySQL 服务器使用**可插拔**的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。
>
> **查看存储引擎**
>
> ```mysql
> -- 查看支持的存储引擎
> SHOW ENGINES
>
> -- 查看默认存储引擎
> SHOW VARIABLES LIKE 'storage_engine'
>
> --查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
> show create table tablename
>
> --准确查看某个数据库中的某一表所使用的存储引擎
> show table status like 'tablename'
> show table status from database where name="tablename"
> ```
>
> **设置存储引擎**
>
> ```mysql
> -- 建表时指定存储引擎。默认的就是INNODB，不需要设置
> CREATE TABLE t1 (i INT) ENGINE = INNODB;
> CREATE TABLE t2 (i INT) ENGINE = CSV;
> CREATE TABLE t3 (i INT) ENGINE = MEMORY;
>
> -- 修改存储引擎
> ALTER TABLE t ENGINE = InnoDB;
>
> -- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
> SET default_storage_engine=NDBCLUSTER;
> ```
>
### 说说 MySQL 都有哪些存储引擎？都有哪些区别？
常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。
InnoDB 现在是 MySQL5.5 版本后默认的存储引擎，支持**事务、行级锁定和外键**。我们一般和 MyISAM 进行对比即可
1. **事务支持**：InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
2. **外键约束**：InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
3. **存储结构**：InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
4. **表空间管理**：InnoDB 使用表空间（tablespace）来管理数据存储，支持自动扩展，支持表和索引分开存储，提高存储效率。MyISAM 每个表有三个文件：`.frm`（表定义文件）、`.MYD`（数据文件）和 `.MYI`（索引文件）
5. **锁定机制**：InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
6. **崩溃恢复**：InnoDB 具有崩溃恢复的能力，使用重做日志（redo log）和回滚日志（undo log）来恢复数据；MyISAM 没有崩溃恢复机制，可能需要手动恢复
7. **性能**： InnoDB 在写密集型操作中表现更好，特别是在需要事务和外键约束的场景下。MyISAM 在读密集型操作中表现更好，尤其是在没有写操作的情况下。
> #### 文件存储结构对比
>
> 在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 `.frm` 文件，`.frm` 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有`.frm`文件，命名方式为 数据表名.frm，如 user.frm。
>
> 查看 MySQL 数据保存在哪里：`show variables like 'data%'`
>
> MyISAM 物理文件结构为：
>
> - `.frm`文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
> - `.MYD` (`MYData`) 文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的数据
> - `.MYI` (`MYIndex`)文件：MyISAM 存储引擎专用，用于存储 MyISAM 表的索引相关信息
>
> InnoDB 物理文件结构为：
>
> - `.frm` 文件：与表相关的元数据信息都存放在 frm 文件，包括表结构的定义信息等
>
> - `.ibd` 文件或 `.ibdata` 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用**共享表空间**存放存储数据，还是用**独享表空间**存放存储数据。
>
> 独享表空间存储方式使用`.ibd`文件，并且每个表一个`.ibd`文件
> 共享表空间存储方式使用`.ibdata`文件，所有表共同使用一个`.ibdata`文件（或多个，可自己配置）
>
### 哪个存储引擎执行 select count(*) 更快，为什么?
MyISAM 更快，因为 MyISAM 内部维护了一个计数器，可以直接调取。
- MyISAM 存储每个表的行数在表的元数据中，因此执行 `SELECT COUNT(*)` 时，它可以直接读取这个值，而不需要扫描整个表
- nnoDB 不存储行数信息在表的元数据中。每次执行 `SELECT COUNT(*)` 查询时，InnoDB 都需要扫描整个表来计算行数。这对于大表来说可能会非常慢。
InnoDB 不将表的行数存储在元数据中，主要原因是其设计目标与 MyISAM 不同。InnoDB 设计为支持高并发的事务处理和数据一致性，因此其存储和计数机制需要权衡性能和一致性。以下是一些具体原因：
**1. 行级锁定和并发控制**
InnoDB 支持行级锁定，这意味着在高并发环境中，不同事务可以同时对不同的行进行操作，而不会相互阻塞。为了确保这种并发控制和数据一致性，InnoDB 需要动态计算行数，以反映当前事务视图下的数据状态。
- **事务隔离级别**：InnoDB 支持多种事务隔离级别（如 READ COMMITTED、REPEATABLE READ、SERIALIZABLE），这些隔离级别决定了事务如何看到数据。预先存储的行数无法满足这些隔离级别的要求，因为行数在不同事务下可能有所不同。
- **锁机制**：由于行级锁定，InnoDB 在处理大量并发事务时，需要动态调整行数信息，而不是依赖预先存储的静态行数。
**2. 一致性和持久性**
InnoDB 设计为支持 ACID 属性（原子性、一致性、隔离性、持久性），这要求所有的数据操作都必须保证一致性和可靠性。
- **崩溃恢复**：InnoDB 使用重做日志（redo log）和回滚日志（undo log）来实现崩溃恢复。如果行数保存在元数据中，崩溃恢复后行数可能与实际数据不一致，从而破坏数据的一致性。
- **并发更新**：在高并发环境中，多个事务可能同时修改表中的数据。如果行数保存在元数据中，每次更新都需要锁定并更新元数据，这将导致严重的性能瓶颈。
**3. 性能优化**
动态计算行数虽然在某些查询中（如 `SELECT COUNT(*)`）较慢，但它避免了在高并发写操作下频繁更新元数据的性能开销。
- **写操作性能**：为了保证高效的写操作，InnoDB 设计避免了每次写操作都需要更新元数据的设计，这样可以更好地处理高并发写入。
- **实际应用**：在实际应用中，行数的精确统计并不是经常需要的操作。大多数情况下，应用程序可以通过索引和其他机制来实现高效的数据访问，而不依赖于 `SELECT COUNT(*)` 的性能。
------
## 三、数据类型
> 主要包括以下五大类：
>
> - 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
> - 浮点数类型：FLOAT、DOUBLE、DECIMAL
> - 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
> - 日期类型：Date、DateTime、TimeStamp、Time、Year
> - 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
### CHAR 和 VARCHAR 的区别？
`CHAR` 和 `VARCHAR` 都是用于存储字符串的字段类型，但它们在存储方式、性能和使用场景上存在一些关键区别
- **存储方式**：char 是固定长度，varchar 长度可变：
- char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
- 存储时，char 不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而 varchar 会根据实际存储的数据分配最终的存储空间，加上 1 或 2 个额外字节用于存储字符串的长度信息
- **性能不同**：
- `CHAR` 因为其固定长度的特性，在某些情况下可能会提供更好的性能，尤其是当处理大量具有相同长度的数据时。
- `VARCHAR` 由于需要存储长度信息，并且可能需要额外的空间来存储不同长度的字符串，因此在性能上可能略逊于 `CHAR`
- 使用场景：char 适用于固定长度字符串：如存储状态码、国家代码、MD5 哈希值等。varchar 适用于可变长度字符串：如用户名、电子邮件地址、描述等。
### MySQL 里记录货币用什么字段类型比较好？
> 阿里规范：【强制】任何货币金额，均以最小货币单位且整型类型来进行存储。
在 MySQL 中记录货币数据时，通常推荐使用 `DECIMAL` 类型。`DECIMAL` 类型提供高精度的存储和计算，非常适合用于存储货币值。以下是使用 `DECIMAL` 类型的原因以及其他可能选择的字段类型和其适用场景：
**使用 `DECIMAL` 类型的原因**
1. **高精度**：
- `DECIMAL` 类型可以精确存储货币值，没有浮点运算误差。例如，定义为 `DECIMAL(10, 2)` 表示最多 10 位数字，其中 2 位小数，适合存储最多到亿位的金额，精确到小数点后两位。
2. **存储效率**：
- 由于货币值通常需要精确到小数点后两位，`DECIMAL` 能够确保存储的每一个值都是精确的，避免了浮点数可能带来的舍入误差。
3. **计算正确性**：
- 在涉及到财务计算时，使用浮点数类型（如 `FLOAT` 或 `DOUBLE`）可能会因为舍入误差导致计算不准确。`DECIMAL` 类型避免了这些问题，确保计算结果的准确性。
当然还**有些业务**，用 `INT` 或者 `BIGINT` 类型存储货币的最小单位也可以（如美分、分），适合对性能有更高要求的场景（整数运算比浮点运算更快），但需要处理转换逻辑。
### BLOB 和 TEXT 有什么区别？
> 这个问题其实很不重要，因为大部分公司不让让使用这两种类型。
>
> **禁止使用TEXT、BLOB类型**：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
`BLOB`（Binary Large Object）和 `TEXT` 是 MySQL 中用于存储大型二进制数据和大型文本数据的两种不同的数据类型。它们之间的主要区别包括：
1. **存储内容**：`BLOB` 用于存储二进制数据，如图片、音频、视频等。`TEXT` 用于存储大型文本数据，如文章、评论等。
2. **最大长度**：
- `BLOB` 和 `TEXT` 类型的最大长度可以达到 65,535 字节，即 64KB（在 MySQL 5.0.3 之前的版本中，最大长度为 255 字节）。
- 从 MySQL 5.0.3 版本开始，`BLOB` 和 `TEXT` 类型的列可以存储更大的数据，最大可达到 4GB（使用 `LONGBLOB` 和 `LONGTEXT`）。
3. **字符编码**：`BLOB` 存储的是二进制数据，与字符编码无关。`TEXT` 存储的是字符数据，受字符编码的影响，如 `utf8`、`latin1` 等。
4. **存储效率**：`BLOB` 由于存储的是二进制数据，不涉及字符编码转换，通常存储效率更高。`TEXT` 类型在存储时会进行字符编码转换，可能会占用更多的存储空间。
5. **排序和比较**：`BLOB` 类型的列不能进行排序和比较，因为它们是二进制数据。`TEXT` 类型的列可以进行排序和比较，因为它们是字符数据。
在选择 `BLOB` 还是 `TEXT` 时，需要根据数据的特性和应用场景来决定。如果需要存储非文本的二进制数据，应选择 `BLOB`；如果需要存储大量的文本数据，则应选择 `TEXT`。
------
## 四、索引
> - MYSQL官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以说**索引的本质是：数据结构**
>
> - 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
>
> - 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，**数据库还维护者一个满足特定查找算法的数据结构**，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。
>
> 
>
> 左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址
>
> 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。
>
> - 索引本身也很大，不可能全部存储在内存中，**一般以索引文件的形式存储在磁盘上**
>
> - 平常说的索引，没有特别指明的话，就是 B+ 树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用 B+ 树索引，统称索引。此外还有哈希索引等。
>
### 说说你对 MySQL 索引的理解？
> 这种就属于比较宽泛的问题，可以有结构条例的多说一些。差不多的问法：
>
> - 索引是越多越好吗？为什么？
> - 索引有哪些优缺点？
> [!TIP]
>
> **话术点**：B+ 树、优缺点、索引分类、最左匹配原则
索引是数据库优化的重要工具，从数据结构上来说，在 MySQL 里面索引主要是 B+ 树索引。它的查询性能更好，适合范围查询，也适合放在内存里。 MySQL 的索引又可以从不同的角度进一步划分。比如说根据叶子节点是否包含 数据分成聚簇索引和非聚簇索引，还有包含某个查询的所有列的覆盖索引等 等。数据库使用索引遵循最左匹配原则。但是最终数据库会不会用索引，也是一个比较难说的事情，跟查询有关，也跟数据量有关。在实践中，是否使用索引以及使用什么索引，都要以 EXPLAIN 为准。
> **优势**
>
> - 提高数据检索效率，降低数据库IO成本
>
> - 降低数据排序的成本，降低CPU的消耗
>
>
> **劣势**
>
> - 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
> - 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。
> 因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
> 都会调整因为更新所带来的键值变化后的索引信息
> ##### MySQL索引分类
>
> ###### 数据结构角度
>
> - B+树索引
> - Hash索引
> - R-Tree索引
>
> ###### 从物理存储角度
>
> - 聚集索引（clustered index）
>
> - 非聚集索引（non-clustered index），也叫辅助索引（secondary index）
>
> 聚集索引和非聚集索引都是B+树结构
>
> ###### 从逻辑角度
>
> - 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
> - 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引
> - 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
> - 唯一索引或者非唯一索引
> - 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
> MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建
>
### 说一下 MySQL InnoDB 的索引原理是什么?
> 这就涉及到了好多知识点，我们可以列举几项关键点，说说**索引结构**、**聚簇索引**
**首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面**。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。
- **B+Tree索引**
InnoDB 的主要索引结构是 B+ 树索引。B+ 树是一种平衡树，每个节点可以有多个子节点。与 B 树不同，B+ 树的所有数据都存储在叶子节点中，叶子节点之间通过指针相连，这使得范围查询和排序操作非常高效。
- **聚簇索引 和 非聚簇索引**
- InnoDB 中的主键索引就是聚簇索引。聚簇索引将数据行与索引紧密结合在一起，数据行存储在叶子节点中，因此通过主键查找数据非常高效
- 当你创建一个表并指定主键时，InnoDB 会自动使用主键创建一个聚簇索引。
- 如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引代替。
- 如果没有唯一非空索引，InnoDB 会自动生成一个隐藏的行 ID 作为聚簇索引
- 辅助索引（也称为二级索引或非聚簇索引）是用于加速对非主键列的查询。辅助索引的叶子节点存储索引列的值以及对应的主键值。
使用辅助索引进行查询时，InnoDB 首先通过辅助索引找到主键值，然后通过主键值在聚簇索引中查找实际数据。这种回表（回查）过程可能增加查询时间，但仍然比全表扫描快得多。
### 为什么要用 B+树？
> B+ 树 索引相比于其他索引类型的优势？
>
> 为什么MySQL 索引中用 B+tree，不用 B-tree 或者其他树，为什么不用 Hash 索引
>
> B-Tree 对比 B+Tree索引
- **B+Tree 相对于 B 树 索引结构的优势：**
- B+ 树空间利用率更高：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
- B 树只适合随机检索，B+Tree 叶子节点采用的是双链表连接，同时支持**随机检索和顺序检索**
- **B+Tree 相对于二叉树索引结构的优势：**
- 与 B+ 树相比，平衡二叉树、红黑树在同等数据量下，高度更高，性能更差，而且它们会频 繁执行再平衡过程，来保证树形结构平衡
- 对于有 N 个叶子节点的 B+Tree，其搜索复杂度为$O(logdN)$，其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据（这里的查询参考上面 B+Tree 的聚簇索引的查询过程）。
而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 $O(logN)$，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。
- **B+Tree 相对于 Hash 表存储结构的优势**：
- 我们知道范围查询是 MySQL 中常见的场景，但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。
- **B+Tree 相对于 跳表存储结构的优势**：
- 与B+ 树相比，跳表在极端情况下会退化为链表，平衡性差，而数据库查询需要一个可预期 的查询时间，并且跳表需要更多的内存。
> MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。
>
> B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。
>
> 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
>
> InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB。
>
> 而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
>
> B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。从 B-Tree 结构图中可以看到每个节点中不仅包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，**所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上**，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。
>
> - **节点存储内容**：
> - **B-Tree**：每个节点可以存储多个键和多个子节点的指针。键被存储在节点内部，并且可以被多次访问。
> - **B+Tree**：非叶子节点仅存储键作为索引，不存储实际的数据记录，所以**适合放入内存中**。所有的数据记录都存储在叶子节点中，并且叶子节点被额外的指针连接在一起，形成一个有序链表。
>
> - **查询性能**：
> - **B-Tree**：叶子节点可能包含数据记录，也可能不包含，取决于具体的实现。在查找特定值时，可以在找到对应节点时立即返回结果。
> - **B+Tree**：所有的数据记录都存储在叶子节点中。在查找特定值时，需要访问叶子节点，但因为**叶子节点形成了有序链表**，所以**范围查询和顺序访问的性能更好**。
> - **空间效率**：
> - **B-Tree**：因为每个节点存储更多的键和指针，所以每个节点可以有更少的子节点，树的高度可能会更小。
> - **B+Tree**：每个内部节点可以有更多子节点，因为它们只存储键的索引，这使得树更宽、更浅，减少了树的高度，**提高了 I/O 效率**。
>
> #### B+Tree 性质
>
> 在数据库中，B+ 树索引结构的高度 *h* 直接影响到进行一次索引查找所需的 I/O 次数。这是因为每次 I/O 操作通常只能读取一个磁盘块（或页）的数据。
>
> - **B+ 树高度 h**：
>
> - B+树的高度是由树中节点的最大数量决定的，可以通过以下公式近似计算： $h≈logm(N)$
>
> - 其中 *N* 是树中存储的总记录数，*m* 是每个磁盘块（页）可以存储的数据项数量。
>
> 当数据量 N 一定的情况下，m 越大，h 越小；而 m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的（默认 16 KB），如果数据项占的空间越小，数据项的数量越多，树的高度越低。
>
> 这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 4 字节，要比 bigint 8 字节少一半。这也是为什么 B+ 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
>
> > [!NOTE]
> >
> > 在实际应用中，B+树的高度和 I/O 次数会受到许多因素的影响，包括页的大小、数据的分布、索引的选择性等
>
> 当 B+ 树的数据项是复合的数据结构，比如(name,age,sex) 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F) 这样的数据来检索的时候，B+树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当 (20,F) 这样的没有 name 的数据来的时候，B+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候name 就是第一个**比较因子**，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当 (张三,F) 这样的数据来检索时，B+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，就是我们说的**索引的最左匹配特性**。
### 说下页分裂？
MySQL 中的页分裂（Page Split）是指在 InnoDB 存储引擎中，当一个页（Page）无法容纳更多的数据时，需要将数据分散到新的页中，以保持 B+ 树的平衡。页分裂通常发生在以下几种情况：
1. **插入操作**：当向一个已满的页中插入新行时，如果该页没有足够的空间来容纳新数据，就会发生页分裂。InnoDB 会将该页中的一部分数据移动到新的页中，以容纳新插入的行。
> - 当向一个已满的数据页中插入新的记录时，必须进行页分裂。
> - 特别是在使用自增主键的情况下，新的记录总是插入到最后，页分裂较少发生。
> - 如果使用随机键（如 UUID）作为主键，新记录会被插入到表的任意位置，更容易导致页分裂。
2. **行更新操作**：如果对已有行的数据进行更新，导致其大小发生变化，可能会引起页分裂。例如，如果更新后的行大小超过了页的剩余空间，InnoDB 可能会将部分数据移动到新的页中。
3. **行删除操作**：删除操作通常不会引起页分裂，但如果没有足够的空间来重新组织页中的数据，或者为了优化页的使用，InnoDB 可能会进行页合并（与页分裂相反的操作）。
页分裂会导致 B+ 树的深度增加，这可能会影响查询性能，因为查询可能需要访问更多的页。此外，页分裂也会导致磁盘 I/O 的增加，因为需要在不同的页之间移动数据。
为了减少页分裂的发生，可以采取以下措施：
1. **优化数据行的大小**：确保数据行的大小适中，避免过大的行，因为大行更容易引起页分裂。
2. **使用合适的存储引擎配置**：InnoDB的页大小默认为16KB，可以通过调整页大小来减少页分裂的发生。
3. **定期优化和维护数据库**：定期对数据库进行优化，如使用`OPTIMIZE TABLE`命令来减少页分裂（重组表和索引，消除碎片。注意：该操作会加锁，影响性能，建议在业务低峰期执行）
4. **使用合适的索引策略**：合理的索引设计可以减少页分裂的发生，因为索引会占用页的空间。
### 聚集索引与非聚集索引的区别？
> MySQL 索引底层实现，叶子结点存放的是数据还是指向数据的内存地址?
聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是数据库管理系统中常见的两种索引类型，是一种**数据存储方式**的区分，特别是在 MySQL 中。
- 聚簇索引，也叫“**聚集索引**”，表示索引结构和数据一起存放的索引。
- 非聚集索引是**索引结构和数据分开存放的索引**。
因为 InnoDB 默认存储引擎的原因，我们说这个一般指的是 InnoDB 中的聚集索引和非聚集索引
**InnoDB 引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录**（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，**InnoDB 的数据文件本身就是主键索引文件**，这样的索引被称为"“**聚簇索引**”，一个表只能有一个聚簇索引。
- **InnoDB 聚集索引**：InnoDB 存储引擎使用聚集索引来存储主键列，并且所有非主键列都包含在聚集索引中，这意味着聚集索引实际上包含了整行数据。一个表只能有一个聚簇索引
- **InnoDB 非聚集索引**：InnoDB 的非聚集索引（也称为辅助索引）首先存储非主键索引列的值，然后通过主键列的值来查找对应的行。这种方式称为“索引的索引”，因为非聚集索引首先查找主键。
### 非聚簇索引一定会回表查询吗?
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。
举个简单的例子：假设我们在员工表的年龄上建立了索引，那么当进行的查询时,在索引的叶子节点上,已经包含了 age 信息，不会再次进行回表查询。
```sql
select age from employee where age < 20
```
### InnoDB 引擎中的索引策略，了解过吗？
InnoDB 索引策略主要包括以下几个方面:
- 聚簇索引
- 辅助索引，也就是非聚簇索引
- 覆盖索引：查询可以直接通过索引获取所需数据，而无需回表查询
- 前缀索引：用于对较长的字符串列进行索引，只索引字符串的前 N 个字符
- 全文索引：用于对大文本字段进行全文检索
每种索引类型都有其独特的用途和优势，通过合理使用这些索引，可以显著提高数据库的查询性能。
### 使用索引查询一定能提高查询的性能吗？为什么?
> 其实这个问题也对应的是哪些情况需要建立索引，哪些不需要
>
> 使用索引需要注意的几个地方？
使用索引查询并不一定总能提高查询性能
**为什么使用索引通常能提高查询性能：**
1. 减少需要扫描的数据量来加快数据检索速度
- 频繁作为查询条件的字段
- 查询中与其他表关联的字段，外键关系建立索引
- 在查询涉及的所有列都在索引中时，可以避免回表查询，提高查询效率
2. 索引可以显著加快 `ORDER BY` 和 `GROUP BY` 操作
**为什么在某些情况下索引查询反而可能降低性能**：
1. 小表或低选择性列
- **小表**：对于行数很少的表，索引带来的性能提升有限，因为全表扫描的开销也很小。索引反而增加了额外的维护开销。
**低选择性列**：如果索引列的选择性很低（例如，性别列只有两个值 "M" 和 "F"），使用索引可能会导致大量的行扫描，无法显著减少数据量，索引的效果不明显。
2. 经常增删改的表
- **频繁的写操作**：索引不仅在读取数据时加速查询，还在插入、更新和删除操作时带来额外的开销。每次写操作都需要更新索引，索引越多，写操作的开销就越大。
3. 在高并发环境下，索引也可能导致锁竞争，影响查询性能
是否使用索引以及如何设计索引需要根据具体的查询模式、数据量、更新频率、硬件资源等多种因素综合考虑
### InnoDB 表为什么要建议用自增列做主键
1. **聚簇索引的效率**
InnoDB 表使用聚集索引来存储数据，聚簇索引的叶子节点存储数据行的物理位置，这意味着数据行在磁盘上的物理顺序与索引的顺序一致
- **顺序插入**：自增列使得每次插入新记录时，新的记录都被追加到表的末尾，插入操作非常高效，不需要在表的中间位置插入数据，从而避免了频繁的页拆分和数据移动。
- **减少碎片**：顺序插入数据可以减少表碎片，维持数据的物理连续性，进而提高查询性能。
2. **唯一性和简单性**
- **唯一性**：自增列保证每个记录都有一个唯一的标识符，避免了重复记录。
- **简单性**：自增列无需复杂的生成逻辑，MySQL 会自动处理自增值，简化了应用程序的开发和维护。
3. **更好的缓存命中率**
顺序插入的新记录使得最近插入的数据很可能在相邻的存储位置，这提高了缓存的命中率。
- **缓存友好**：数据库缓存更容易缓存相邻的存储块，从而提高查询的性能，特别是在高并发的读写环境下。
### 如何写 SQL 能够有效的使用到复合索引
> MySQL高效索引
要有效地使用复合索引（也称为多列索引或组合索引），编写 SQL 查询时需要考虑以下几点：
1. **覆盖索引**（Covering Index），或者叫索引覆盖， 也就是平时所说的不需要回表操作
- 如果查询的列完全包含在复合索引中，那么可以使用覆盖索引，这样可以避免回表查询，提高性能。
2. **最左前缀法则**：
- 复合索引的效率取决于查询条件是否遵循最左前缀法则，即从索引的最左边列开始匹配。
- 例如，如果你有一个 (`c1`, `c2`, `c3`) 的复合索引，那么以下查询可以高效地使用索引：
```sql
SELECT * FROM table WHERE c1 = 'value1';
SELECT * FROM table WHERE c1 = 'value1' AND c2 = 'value2';
```
- 如果查询条件不包含 `c1`，则该复合索引不会被使用。
3. **索引列的顺序**：在复合索引中，列的顺序很重要。应该将选择性最高的列（即不同值占总行数比例最高的列）放在前面。
4. **使用索引列作为条件**：确保 WHERE 子句中的条件列与复合索引中的列相匹配，并且顺序正确。
5. **避免使用函数和表达式**：
- 如果在 WHERE 子句中对索引列应用了函数或计算，可能会使索引失效。
- 例如，如果 `c1` 是索引的一部分，应避免 `WHERE UPPER(col1) = 'VALUE'`，而应使用 `WHERE c1 = 'value'`。
6. **范围查询和排序**：
- 复合索引可以用于涉及范围查询的 ORDER BY 和 GROUP BY 子句。
- 例如，如果有一个 (`c1`, `c2`) 的索引，`ORDER BY c1, c2` 可以有效地使用索引。
7. **限制索引的使用**：
- 使用 `LIKE` 操作符进行模糊匹配时，如果模式以通配符（`%`）开头，索引将不会被使用。
- 例如，使用 `WHERE c1 LIKE '%value'` 将无法利用索引。
考虑查询的实际条件，如数据量大小、表的更新频率等，以确定是否真正需要复合索引。在实际的数据库环境中测试查询性能，并根据查询执行计划（`EXPLAIN`）来优化索引的使用
### 数据库不使用索引的几种可能？
> 上一个问题的反向问法
1. **小型表或数据量少**
- 全表扫描速度快：对于小型表或数据量较少的表，全表扫描的速度可能与使用索引扫描的速度相当甚至更快，因为读取整个表所需的时间较短。
- 索引开销大：索引的创建和维护需要额外的存储空间和资源，对小型表来说，这些开销可能超过其带来的性能提升。
2. **数据分布不均**
- 低选择性：如果某个列的值的重复率很高，例如性别列只有“男”和“女”两种值，使用索引的选择性很低，索引扫描可能比全表扫描更慢。
- 数据倾斜：数据在某些特定值上高度集中，这种情况下，使用索引可能不会带来显著的性能提升。
3. **查询模式不适合**
- **范围查询**：对于范围查询，例如“BETWEEN”、“>”和“<”，使用索引的效率可能不如期望中的高，因为索引可能需要扫描较多的记录。
> 范围查询，不是一定不会使用索引，成本决定执行计划，优化器会首先针对可能使用到的二级索引划分几个扫描区间，然后分别调查这些区间内有多少条记录，在这些扫描区间内的二级索引记录的总和占总共的记录数量的比例达到某个值时，优化器将放弃使用二级索引执行查询，转而采用全表扫描
- **LIKE 操作**：对于使用通配符“%”在前的LIKE查询（如“%value”），索引无法高效使用，因为数据库需要扫描整个表来找到匹配的值。
4. **索引未命中**
- **函数操作**：在查询条件中使用函数操作（如UPPER(column_name) = 'VALUE'）会导致索引无法被使用，因为索引存储的是原始数据。
- **隐式类型转换**：如果列的数据类型与查询条件中的数据类型不一致（如列为整数，但条件中使用字符串），数据库可能进行隐式类型转换，导致索引失效。
5. **数据库优化器选择**
- **优化器策略**：数据库优化器根据统计信息和查询成本选择执行计划。在某些情况下，优化器可能判断全表扫描比索引扫描更高效。
- **统计信息不准确**：如果统计信息不准确或过期，优化器可能做出不合适的决策，选择不使用索引。
6. **索引维护成本**
- **高频更新**：对于高频插入、更新和删除操作的表，索引的维护成本可能较高，影响整体性能。在这种情况下，可能会选择不使用索引或减少索引的数量。
7. **多列索引的限制**
- **索引列顺序**：多列索引只有在按索引列顺序查询时才能被高效使用，如果查询条件中不包含索引的前导列，索引将无法使用。
- **查询不完全匹配**：对于复合索引，如果查询条件不完全匹配索引定义，索引的使用效果可能不佳。
### 联合索引ABC，现在有个执行语句是 A = XXX and C < XXX，索引怎么走?
给定查询语句 `A = XXX AND C < XXX` 和联合索引 `(A, B, C)`，我们来分析索引的使用情况：
1. **精确匹配 `A`**：
- 由于条件 `A = XXX` 是精确匹配，第一个索引列 `A` 将被使用。
2. **跳过 `B`**：
- 由于查询条件中没有涉及列 `B`，联合索引的第二列 `B` 将被跳过。
3. **范围查询 `C`**：
- 条件 `C < XXX` 是范围查询。根据最左前缀法则和范围查询终止索引使用的规则，虽然条件 `C < XXX` 出现在查询中，但因为 `B` 没有出现在条件中，所以索引在 `C` 列上不能继续有效使用。
在这个查询 `A = XXX AND C < XXX` 中，联合索引 `(A, B, C)` 只能部分使用，即只会使用索引的第一列 `A`，后续的 `B` 和 `C` 列将不会被索引利用。具体来说，执行计划会使用索引 `(A, B, C)` 中的 `(A)` 进行查找，然后对找到的记录进行筛选以满足 `C < XXX` 的条件。
### 主键索引和唯一索引的区别？
主键索引是特殊的唯一索引，唯一索引查询会涉及到“回表”操作
| 特性 | 主键索引（Primary Key Index） | 唯一索引（Unique Index） |
| ------------- | ----------------------------- | ------------------------------------------------------------ |
| 唯一性 | 必须唯一 | 必须唯一（允许 NULL 值） |
| 是否允许 NULL | 不允许 | 允许多个 NULL 值「这里 NULL 的定义 ，是指 未知值。 所以多个 NULL ，都是未知的」 |
| 聚簇索引 | 是（在 InnoDB 中） | 否（除非是主键） |
| 每个表的数量 | 只能有一个 | 可以有多个 |
| 主要用途 | 唯一标识每一行 | 强制唯一性约束，非主键用途 |
| 创建语法 | `PRIMARY KEY` | `UNIQUE` |
```sql
CREATE TABLE example (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
INSERT INTO example (email) VALUES (NULL), (NULL), (NULL);
```
### 索引下推？
**索引下推（Index Condition Pushdown，ICP）** 是 MySQL 5.6 中引入的一种优化技术，用于提升范围查询或排序查询的性能。通过索引下推，MySQL 可以减少不必要的表数据行访问，加快查询速度。
**索引下推的工作机制**
在没有索引下推时，当执行范围查询时，MySQL 首先根据索引查找符合范围条件的索引记录，然后从存储引擎中读取表的完整数据行，再在服务器层检查 `WHERE` 条件是否成立。如果查询条件只使用部分字段，MySQL 仍然需要读取完整的数据行。
而使用索引下推优化后，MySQL 会将查询中的一部分条件（`WHERE` 子句中的条件）下推到存储引擎，尽可能利用索引直接过滤掉不符合条件的索引记录，而无需回表（即从表中获取完整数据行）。只有那些通过了索引条件过滤的记录才会回表，从而减少数据访问和提升查询效率。
> 索引下推（Index Condition Pushdown，ICP）的实现主要依赖于 MySQL 存储引擎层和 SQL 解析器层之间的协作。索引下推的关键在于**将部分过滤条件推送到存储引擎层**，而不是像传统方式那样仅在 SQL 层面做条件过滤。
**索引下推的示例**
考虑如下的表结构和查询语句：
```sql
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
address VARCHAR(255),
INDEX idx_age_name (age, name)
);
```
我们有一个联合索引 `idx_age_name`，索引包含了 `age` 和 `name` 两个字段。
执行查询：
```sql
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND name LIKE 'A%';
```
**无索引下推时的执行过程**
1. MySQL 通过索引 `idx_age_name` 先找到 `age` 在 20 到 30 范围内的所有记录。
2. MySQL 读取每个符合 `age` 范围条件的索引记录，并回表读取完整的数据行。
3. 在服务器层对 `name LIKE 'A%'` 这个条件进行过滤。
这种方式的一个问题在于，如果索引条件返回大量记录，即使最终符合 `WHERE` 过滤条件的数据较少，MySQL 仍然需要回表大量次进行数据行的读取，这会导致性能瓶颈，尤其是在数据量非常大的情况下。
**使用索引下推时的执行过程**
1. MySQL 同样通过 `idx_age_name` 先找到 `age` 在 20 到 30 范围内的记录。
2. 但是此时，MySQL 在存储引擎层就可以利用 `name LIKE 'A%'` 的条件对索引记录进行进一步过滤，不需要回表。
3. 只有那些通过索引条件过滤的记录，才会读取完整的表数据行。
通过索引下推，MySQL 在存储引擎层就减少了不必要的数据行访问，从而显著提升了查询的效率，尤其是当索引范围查询返回大量结果而大多数最终不满足查询条件时。
索引下推最有效的场景是**复合索引**。MySQL 通过索引的前缀规则匹配多个列，可以将尽可能多的条件推送到存储引擎进行处理。
> 例如，复合索引 `(age, name)` 可以处理如下两种查询：
>
> - `SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND name LIKE 'A%';`
> - `SELECT * FROM users WHERE age BETWEEN 20 AND 30;`
>
> 但对于查询 `WHERE name LIKE 'A%'` 而没有 `age` 条件的查询，索引 `(age, name)` 则不会使用索引下推，因为该查询不能有效利用索引的前缀
**使用索引下推的条件**
1. **索引的多列查询**：索引下推通常在多列索引（复合索引）时最为有效。
2. **`WHERE` 子句包含索引列**：索引下推依赖于查询条件与索引字段匹配，因此 `WHERE` 子句中的条件应该涉及索引中的列。
3. **合适的存储引擎**：索引下推主要用于 InnoDB 存储引擎。
**索引下推的优势**
- **减少回表次数**：通过提前过滤不符合条件的记录，减少回表操作。
- **提升查询效率**：尤其是当索引列上的范围条件命中大量记录，而回表的记录较少时，索引下推可以显著减少不必要的 IO 操作。
------
## 五、日志
MySQL 日志其实是各种其他知识模块的基础
### MySQL 都有哪些日志，分别介绍下作用，执行顺序是怎么样的？
> - **错误日志**：记录出错信息，也记录一些警告信息或者正确的信息。
>
> - **通用查询日志**：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
>
> - **慢查询日志**：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
>
> - **二进制日志**：记录对数据库执行更改的所有操作。
>
> - **中继日志**：中继日志也是二进制日志，用来给slave 库恢复
>
> - **事务日志**：重做日志 redo 和回滚日志 undo
时序上先 undo log，redo log 先 prepare， 再写 binlog，最后再把 redo log commit
### 说下 一条 MySQL 更新语句的执行流程是怎样的吧？
1. **解析和优化**
- **语法解析**：MySQL解析器会对`UPDATE`语句进行语法解析，生成语法树，并检查语句的合法性。
- **查询优化器**：优化器会根据表的索引、统计信息等来选择最优的执行计划，以最快的方式查找需要更新的记录。
2. **定位记录**（这个过程也被称作**加锁读**）
- **通过索引定位**：如果更新语句涉及索引，MySQL会利用索引快速定位需要更新的记录。如果没有索引，则会进行全表扫描。
- **在Buffer Pool中查找页**：MySQL首先会在Buffer Pool中查找包含目标记录的页（数据页或索引页），如果该页已经存在于Buffer Pool中，则直接使用；否则会将其从磁盘加载到 Buffer Pool 中。
3. **生成Undo日志**
- **生成Undo日志**：为了支持事务的回滚，MySQL 会在更新操作前生成一条 Undo 日志，该日志记录了被更新行的**旧值**。Undo日志存储在回滚段（Rollback Segment）中。
- **保存Undo日志**：在发生事务回滚时，MySQL会使用Undo日志将数据恢复到更新前的状态。
4. **更新数据**
- **更新Buffer Pool中的数据页**：MySQL在Buffer Pool中对定位到的记录进行更新操作，修改记录的值。这是一个内存中的操作，数据页此时还没有写回到磁盘。
- **标记页为脏页**：被修改的页会被标记为“脏页”，表示其内容与磁盘上的数据不同，需要在适当的时候写回磁盘。
5. **生成Redo日志**
- **生成Redo日志**：Redo日志记录了数据页的物理变化，用于在数据库崩溃时恢复数据一致性。Redo日志包括这次更新操作的具体细节，如页号、偏移量和新值。
- **写入Redo日志缓存**：生成的Redo日志首先被写入Redo日志缓存（Log Buffer），而不是直接写入磁盘。
> 此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务
6. **写入Binlog**
- **生成Binlog**：MySQL会生成一条对应的Binlog（针对事务性表的DML语句）。Binlog记录的是这次更新的逻辑变化（类似于SQL语句或行级变化），主要用于主从复制和数据恢复。
- **刷写Binlog**：Binlog首先被写入到Binlog缓存，当事务提交时会将缓存中的Binlog刷写到磁盘上的Binlog文件。
7. **事务提交**
- **刷写Redo日志**：在事务提交前，MySQL会将Redo日志从日志缓存刷写到磁盘的Redo日志文件（ib_logfile0, ib_logfile1）。此操作通常是顺序写入，因此效率较高。
- **刷写Binlog**：在事务提交时，Binlog也会被刷写到磁盘。根据MySQL的设置，可能会先刷写Binlog再刷写Redo日志，或以不同顺序执行。
- **两阶段提交**：为了确保Binlog和Redo日志的原子性，MySQL使用两阶段提交的机制。具体步骤是：首先写入Redo日志（处于Prepare状态），然后写入Binlog，最后更新Redo日志为Commit状态。
8. **数据页刷盘**
- **异步刷盘**：脏页的刷盘（即将修改后的数据页从Buffer Pool写回到磁盘）通常是异步进行的。InnoDB存储引擎会根据脏页数量、系统空闲时间等条件定期执行刷盘操作。
- **检查点**：MySQL通过检查点机制来控制Redo日志的循环使用和脏页的刷盘。当达到检查点时，会将所有脏页刷写到磁盘，同时更新Redo日志的检查点信息。
9. **完成更新操作**
- **事务结束**：当Redo日志和Binlog都刷写到磁盘后，事务就完成了。对于非事务性表，操作可能不会涉及Undo日志和Redo日志。
- **释放锁**：在事务完成后，MySQL会释放占用的锁资源。
### 两阶段提交？
在 MySQL 中，`UPDATE` 操作涉及对数据的修改，为了保证数据的一致性和可靠性，特别是在使用 **InnoDB** 存储引擎时，`UPDATE` 操作需要使用**两阶段提交（Two-Phase Commit）**。两阶段提交的设计主要是为了确保数据的**原子性**和**一致性**，特别是在支持事务和涉及到数据库的**Binlog**（二进制日志）记录的情况下。
1. **两阶段提交的原因**
MySQL 的两阶段提交机制主要用于协调 **InnoDB 引擎的事务日志（Redo Log）** 和 **MySQL 的 Binlog**。当数据库发生崩溃时，保证事务的持久性和一致性是非常重要的。事务的执行不能一部分成功，另一部分失败，这会导致数据不一致。
**关键问题：**
- **Redo Log** 是 InnoDB 的内部事务日志，确保事务的**持久性**（即便崩溃后数据也能恢复）。
- **Binlog** 是 MySQL 的二进制日志，主要用于数据的**复制**和**备份**。
如果没有两阶段提交，在执行 `UPDATE` 操作时可能会出现以下问题：
- 写入 Binlog 成功，但事务日志（Redo Log）失败**：**事务被记录在 Binlog 中，但实际修改的数据没有持久化，导致数据不一致。
- 写入 Redo Log 成功，但 Binlog 失败：数据修改在本地持久化了，但 Binlog 失败，导致主从复制出现问题，主库和从库数据不一致。
因此，MySQL 使用两阶段提交来确保在事务修改数据的过程中，**Binlog 和 Redo Log 同时成功**，从而保证事务的一致性。
2. **两阶段提交的流程**
MySQL 在执行 `UPDATE` 操作时，InnoDB 和 Binlog 通过两阶段提交进行协调。具体步骤如下：
a. 第一阶段：预提交阶段（Prepare Phase）
- **开始事务**：InnoDB 首先会执行 `UPDATE` 操作，并将修改记录到内存中的 Undo Log，以便在事务失败时可以回滚。
- **记录 Redo Log（prepare）**：修改数据的操作被记录在**Redo Log**中，并标记为 "Prepare" 状态，此时数据尚未真正提交，只是标记为“准备提交”。
- **等待事务进入准备状态**：InnoDB 引擎等着上层的 MySQL Server 层确认 Binlog 日志写入成功。
此时，事务还没有被真正提交，但 Redo Log 已经有了准备提交的记录。
b. 第二阶段：提交阶段（Commit Phase）
- **写入 Binlog**：MySQL Server 层会将这次 `UPDATE` 操作记录到 Binlog 中，确保日志记录成功。
- **提交事务**：当 Binlog 成功写入后，MySQL Server 会通知 InnoDB 提交事务，InnoDB 会将 Redo Log 的状态从 "Prepare" 变为 "Commit"。
- **持久化 Redo Log**：最后，InnoDB 将 Redo Log 的状态变为已提交，保证数据的修改真正生效，并确保即使系统崩溃，事务也可以在恢复过程中继续完成。
通过这样的两阶段提交，MySQL 确保了 Binlog 和 Redo Log 都正确记录，避免了系统崩溃时数据不一致的问题。
3. 为什么需要两阶段提交？
两阶段提交的必要性源自于以下几点：
- **数据的一致性**：事务的修改操作必须同时写入 InnoDB 的事务日志和 MySQL 的 Binlog，确保数据和日志保持一致，尤其是在崩溃恢复或主从复制的场景下。
- **事务的原子性**：保证要么事务的所有操作都成功，要么所有操作都回滚，不会出现事务部分提交的情况。
- **崩溃恢复**：在崩溃恢复时，InnoDB 可以依靠已提交的 Redo Log 来恢复数据，而 Binlog 用于恢复操作步骤，并保持主从数据库同步。
4. 解决的问题
- **防止数据不一致**：没有两阶段提交机制，可能会导致数据和日志之间的不一致，尤其在分布式环境下，数据库的主从复制和日志备份可能会出现问题。
- **确保事务完整性**：确保所有日志（Binlog 和 Redo Log）和数据一致，保证事务在任何情况下都能正确提交或回滚。
### 说说 redo log 、undo log 和 bin log ？
在 MySQL 中，特别是在使用 InnoDB 存储引擎时，`redo log`（重做日志）、`undo log`（回滚日志）和 `binlog`（二进制日志）各自承担着不同的角色：
**1. Redo Log（重做日志）：**
- **目的**：确保事务的持久性。在系统崩溃后，`redo log` 允许恢复未提交的事务更改，保证数据的完整性和一致性。
- **内容**：记录了事务对数据页所做的物理更改【**物理日志**】，以便在崩溃恢复时重新应用这些更改。
- **写入时机**：在事务提交时，将更改刷新到磁盘上的 `redo log` 文件中。
- **大小和循环**：`redo log` 通常配置为固定大小的日志文件，并且可以循环使用。
- **用途**：**崩溃恢复**，在数据库崩溃后，通过 redo log 恢复到崩溃前的状态，保证数据一致性。
> redo log 不需要写磁盘吗？如果 redo log 也要写磁盘，干嘛不直接修改数据呢？redo log 是需要写磁盘的，但是 redo log 是**顺序写**的，所以也是 WAL（writeahead-log） 的一种。
>
> redo log 本身也是先写进 redo log buffer，后面再刷新到操作系统的 page cache，或者一步到位刷新到磁盘
>
> InnoDB 引擎本身提供了参数 `innodb_flush_log_at_trx_commit` 来控制写到磁盘的时机，里面有三个不同值。
>
> - 0：每秒刷新到磁盘，是从 redo log buffer 到磁盘。
>- 1：每次提交的时候刷新到磁盘上，也就是最安全的选项，InnoDB 的**默认值**。
> - 2：每次提交的时候刷新到 page cache 里，依赖于操作系统后续刷新到磁盘。
**2. Undo Log（回滚日志）：**
- **目的**：提供事务的原子性和一致性。它允许撤销事务的更改，以保持数据的一致状态。
- **内容**：记录了事务对数据页所做的更改的逆操作【**逻辑日志**】，使得在事务失败或需要回滚时可以恢复原始数据。
- **写入时机**：当事务进行修改操作时，`undo log` 会记录这些更改的逆操作，通常在事务提交前就已经写入。
- **用途**：主要用于 MVCC（多版本并发控制）和事务回滚。
**3. Binlog（二进制日志）：**
- **目的**：记录数据库的所有修改操作，用于数据恢复、主从复制和数据审计。
- **内容**：记录了所有修改数据的 SQL 语句，如 `INSERT`、`UPDATE` 和 `DELETE`，但不记录 `SELECT` 和 `SHOW` 这类的语句。
- **写入时机**：在 SQL 语句执行后，根据配置，`binlog` 可以立即或事务提交时写入磁盘。
- **大小和存储**：`binlog` 文件通常不循环使用，它们会随着时间持续增长，直到通过配置的策略进行清理。
| 特性 | Redo Log | Undo Log | Bin Log |
| -------- | ---------------------- | -------------------------------- | ---------------------- |
| 主要用途 | 崩溃恢复 | 事务回滚、多版本并发控制（MVCC） | 主从复制和数据恢复 |
| 日志类型 | 物理日志 | 逻辑日志 | 逻辑日志 |
| 存储内容 | 页级物理更改 | 数据快照 | SQL 语句或行级数据变化 |
| 写入方式 | 循环写入 | 按需写入 | 追加写入 |
| 写入时机 | 事务提交时 | 事务操作时 | 事务提交时 |
| 大小 | 固定大小 | 可变大小 | 可变大小 |
| 作用 | 保证数据一致性和持久性 | 提供事务回滚和一致性读支持 | 实现数据复制和备份 |
### MySQL 的 binlog 有几种录入格式？分别有什么区别？
`binlog`日志有三种格式，分别为`STATMENT`、`ROW`和`MIXED`。
> 在 `MySQL 5.7.7`之前，默认的格式是`STATEMENT`，`MySQL 5.7.7`之后，默认值是 `ROW`。日志格式通过 `binlog-format` 指定。
- `STATMENT` ：基于 SQL 语句的复制(`statement-based replication, SBR`)，每一条会修改数据的 sql 语句会记录到 binlog 中**。**
- 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了 IO，从而提高了性能；
- 缺点：在某些情况下会导致主从数据不一致，比如执行`sysdate()`、`slepp()`等。
- `ROW` ：基于行的复制(`row-based replication, RBR`)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了**。 **
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题**；**
- 缺点：会产生大量的日志，尤其是 `alter table` 的时候会让日志暴涨
- `MIXED` ：基于 STATMENT 和 ROW 两种模式的混合复制(`mixed-based replication, MBR`)，mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式
------
## 六、MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
### 什么是事务？事务有哪些特性？
事务是由一组 SQL 语句组成的逻辑处理单元，具有 4 个属性，通常简称为事务的 ACID 属性。
- **A (Atomicity) 原子性**：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
- **C (Consistency) 一致性**：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
- **I (Isolation)隔离性**：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
- **D (Durability) 持久性**：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
### 什么是脏读、不可重复读和幻读？
**并发事务处理带来的问题**
- 更新丢失（Lost Update)： 事务 A 和事务 B 选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
- 脏读(Dirty Reads)：事务 A 读取了事务 B 未提交的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据
- 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
- 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务 A 读取了几行数据，接着另一个并发事务 B 插入了一些数据时。在随后的查询中，事务 A 就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
> **幻读和不可重复读的区别：**
>
> - **不可重复读的重点是修改**：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）
> - **幻读的重点在于新增或者删除**：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）
### MySQL 支持哪些事务隔离级别？各有什么区别？
MySQL 支持四种事务隔离级别，由低到高分别为：
- **READ UNCOMMITTED(读未提交)：** 最低的隔离级别，允许读取尚未提交的数据变更，**可能会导致脏读、幻读或不可重复读**。
- **READ COMMITTED(读已提交)：** 允许读取并发事务已经提交的数据，**可以阻止脏读，但是幻读或不可重复读仍有可能发生**。
- **REPEATABLE READ(可重复读)：** 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，**可以阻止脏读和不可重复读，但幻读仍有可能发生**。
- **SERIALIZABLE(可串行化)：** 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，**该级别可以防止脏读、不可重复读以及幻读**。
> **简单点的理解**
>
> 读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
>
> 读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
>
> 可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
>
> 串行：我的事务尚未提交，别人就别想改数据。
查看当前数据库的事务隔离级别：
```mysql
show variables like 'tx_isolation'
```
### MySQL 如何实现事务隔离 | 并发事务处理带来的问题的解决办法
- “更新丢失” 通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
- “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：
- 一种是加**锁**：在读取数据前，对其加锁，阻止其他事务对数据进行修改。MySQL 通过行级锁和表级锁来管理并发访问。行级锁包括共享锁（读锁）和排他锁（写锁），表级锁包括意向锁和元数据锁。
- 另一种是数据**多版本并发控制**（MultiVersion Concurrency Control，简称 **MVCC**），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 **REPEATABLE-READ（可重读）**。我们可以通过`SELECT @@tx_isolation;`命令来查看，MySQL 8.0 该命令改为`SELECT @@transaction_isolation;`
这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 **REPEATABLE-READ（可重读）**事务隔离级别下**使用的是 Next-Key Lock 算法，因此可以避免幻读的产生**，这与其他数据库系统(如 SQL Server)是不同的。所以说 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 **SERIALIZABLE(可串行化)**隔离级别，而且保留了比较好的并发性能。
> **Next-Key Locks**：MySQL通过在索引上的间隙加锁（Gap Lock），结合行锁，形成所谓的Next-Key锁，锁定一个范围。这样即使在事务运行期间，其他事务也无法在该范围内插入新的行，从而避免了幻读
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是**READ-COMMITTED(读已提交):**，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。
> 加锁的基本原则（RR隔离级别下）
>
> - 原则1：加锁的对象是 next-key lock。（是一个前开后闭的区间）
> - 原则2：查找过程中访问到的对象才加锁
>
> 优化1：唯一索引加锁时，next-key lock 退化为行锁。
> 索引上的等值查询，向右遍历时最后一个不满足等值条件的时候，next-key lock 退化为间隙锁
> 唯一索引和普通索引在范围查询的时候 都会访问到不满足条件的第一个值为止
### MVCC 熟悉吗，它的底层原理？
MVCC（Multi-Version Concurrency Control）中文叫做多版本并发控制协议，是 MySQL InnoDB 引擎用于控制数据并发访问的协议。
MVCC 主要是借助于版本链来实现的。
在 InnoDB 引擎里面，每一行都有两个额外的列，一个是 trx_id，代表的是修改这一行数据的事务 ID。另外一个是 roll_ptr，代表的是回滚指针。
InnoDB 引擎通过回滚指针，将数据的不同版本串联在一起，也就是版本链。这些串联起来的历史版本，被放到了 undolog 里面。当某一个事务发起查询的时候，MVCC 会根据事务的隔离级别来生成不同的 Read View，从而控制事务查询最终得到的结果。
> MySQL 的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。
>
> 多版本并发控制（MVCC, Multi-Version Concurrency Control）是一种用于处理数据库并发操作的机制，它允许多个事务同时读取和写入数据，而**不会互相阻塞**。MVCC 通过为每个事务提供数据的不同版本来实现，并确保每个事务都能看到一致的数据快照
>
> 可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。
>
> **核心概念**
>
> 1. **快照读（Snapshot Read）**：
> - 每个事务在开始时，会获取一个数据快照，事务在读取数据时，总是读取该快照中的数据。
> - 这意味着即使在事务进行期间，其他事务对数据的更新也不会影响当前事务的读取。
> 2. **版本链（Version Chain）**：
> - 每个数据行都有多个版本，每个版本包含数据和元数据（如创建时间、删除时间等）。
> - 新版本的数据行会被链接到旧版本的数据行，形成一个版本链。
> 3. **隐式锁（Implicit Locking）**：
> - MVCC 通过版本管理避免了显式锁定，减少了锁争用问题。
> - 对于读取操作，事务读取其开始时的快照数据，不会被写操作阻塞。
>
> MySQL InnoDB 存储引擎使用 MVCC 来实现可重复读（REPEATABLE READ）隔离级别，避免脏读、不可重复读和幻读问题。具体机制如下：
>
> 1. **隐藏列**：
> - InnoDB 在每行记录中存储两个隐藏列：`trx_id`（事务ID，也叫做事务版本号）和`roll_pointer`（回滚指针）。
> - `trx_id` 记录最后一次修改该行的事务ID，`roll_pointer` 指向该行的上一版本，把每一行的历史版本串联在一起。
> 2. **Undo日志**：
> - 每次数据更新时，InnoDB 会在 Undo 日志中记录旧版本数据。
> - 如果需要读取旧版本数据，InnoDB 会通过 `roll_pointer` 找到 Undo 日志中的旧版本。
> 3. **一致性视图（Consistent Read View）**：
> - InnoDB 为每个事务创建一致性视图，记录当前活动的所有事务ID。
> - 读取数据时，会根据一致性视图决定哪些版本的数据对当前事务可见。
**MVCC 只在 COMMITTED READ（读提交）和 REPEATABLE READ（可重复读）两种隔离级别下工作**。
> #### MVCC 解决了什么问题
>
> - 读写不冲突，极大的增加了系统的并发性能
>
> - 解决脏读，幻读，不可重复读 等问题（注:其实多版本只是解决不可重复读问题，而加上临界（**next-key**）锁(也就是它这里所谓的并发控制)才解决了幻读问题。）
>
> **Next-Key Locking** 是行锁和间隙锁的组合，它锁定的是索引记录以及其附近的间隙，防止其他事务在查询范围内插入或删除记录，确保当前事务在后续的查询中不会看到“幻影”。
>
> 举例：如果事务A查询了范围 `WHERE age BETWEEN 18 AND 25`，Next-Key Locking 会锁定已经存在的记录以及这个范围的间隙，防止其他事务在这个范围内插入新数据
> InnoDB下的Compact行结构，有三个隐藏的列
>
> | 列名 | 是否必须 | 描述 |
> | -------------- | -------- | ------------------------------------------------------------ |
> | row_id | 否 | 行ID，唯一标识一条记录（如果定义主键，它就没有啦） |
> | transaction_id | 是 | 事务ID |
> | roll_pointer | 是 | DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本 |
>在可重复读（Repeatable Read）隔离级别下，MySQL的InnoDB存储引擎通过多版本并发控制（MVCC）和next-key锁机制在很大程度上减少了幻读的发生，但并没有完全消除幻读的可能性。
>
>1. **快照读（Snapshot Read）**：在可重复读隔离级别下，InnoDB使用MVCC来处理普通的SELECT查询。MVCC通过为每个事务创建一个Read View，使得事务在其执行期间看到的是一致的快照数据，即使其他事务在这段时间内插入了新记录，当前事务的查询也不会看到这些新记录。这通常可以避免幻读。
>2. **当前读（Current Read）**：对于需要锁定结果集的查询，如SELECT ... FOR UPDATE或SELECT ... IN SHARE MODE，InnoDB使用next-key锁，这是记录锁和间隙锁的组合。这种锁机制可以防止其他事务在锁定的范围内插入新记录，从而避免幻读。
>
>然而，根据搜索结果中的讨论，即使在可重复读隔离级别下，仍然存在一些特殊情况可能导致幻读：
>
>- 如果事务在执行快照读之后，立即执行更新操作，可能会看到其他事务插入的新记录，因为更新操作会检查最新版本的数据。
>- 如果事务在执行当前读之前，其他事务已经提交了插入操作，那么当前读可能会观察到这些新记录。
>
>因此，虽然InnoDB的可重复读隔离级别提供了强有力的幻读保护，但在某些特殊情况下，幻读仍然可能发生。开发者需要了解这些情况，并在必要时采取额外的措施来确保数据的一致性和隔离性。
### 版本链问题？
我现在有三个事务，ID 分别是 101、102、103。如果事务 101 已经提交了，但是 102、103 还没提交。这个时候，我开启了一个事务，准备读取数据，那么我读到的是哪个事务的数据？如果这时候事务 103 提交了，但是 102 还没提交，那么会读到谁的呢？
这种题，就和隔离级别有关系了。
在MVCC机制中，事务读取数据的可见性取决于事务的隔离级别和事务的开始时间。以下是根据你描述的情况，按照可重复读（Repeatable Read）隔离级别来分析：
1. **事务101已提交，事务102和103未提交时开启新事务读取数据**：
- 当你开启一个新的事务时，这个事务会创建一个Read View。Read View会根据事务的隔离级别来确定哪些数据版本是可见的。在可重复读隔离级别下，新事务的Read View将只能看到在该事务开始之前已经提交的事务所做的修改。因此，如果你的新事务是在事务102和103提交之前开启的，你将能够读取到事务101提交的数据，但是看不到事务102和103的修改。
2. **事务103提交后，但事务102还未提交时读取数据**：
- 如果在新事务的Read View创建之后，事务103提交了，那么在可重复读隔离级别下，新事务的Read View已经创建完成，它不会包含事务103提交后的数据。因此，即使你的新事务在事务103提交后读取数据，你仍然不会看到事务103的修改，除非你的新事务重新启动并创建一个新的Read View。
在可重复读隔离级别下，新事务读取的数据版本是基于事务开始时数据库的快照，这个快照包括了所有在此之前已经提交的事务的修改。一旦Read View创建，即使其他事务提交了，新事务也不会看到这些新提交的修改，除非新事务重新启动并创建一个新的Read View。
需要注意的是，如果隔离级别是读已提交（Read Committed），情况会有所不同。在这种情况下，每次读取操作都会看到最新的提交事务的结果，所以如果事务103提交了，即使新事务已经开启，它在读取数据时也会看到事务103的修改。
### 简单说下事务日志？
InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。
事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。
InnoDB 假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB 用日志把随机 IO 变成顺序 IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。
InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。
事务日志可以帮助提高事务效率：
- 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
- 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
- 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
- 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。
目前来说，大多数存储引擎都是这样实现的，我们通常称之为**预写式日志**（Write-Ahead Logging），修改数据需要写两次磁盘。
### MySQL 事务的 ACID 实现原理？ | 事务的实现？
事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。
事务的实现就是如何实现 ACID 特性。
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。
>**还看到一种说法**：
>
>从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
- 原子性保证：`undo log` 名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。
- 持久性保证：持久性主要通过 redo log 和数据文件的刷新（flush）来实现。InnoDB 存储引擎在事务提交时会将 redo log 写入到磁盘，确保更改可以被恢复
- 隔离性保证：MySQL 支持不同的事务隔离级别，隔离级别的实现依赖于锁机制和多版本并发控制（MVCC）。
- 一致性保证：MySQL 通过约束（如主键、外键、检查约束等）、触发器和存储过程等来维护数据的完整性和一致性。
> 分布式事务相关问题，可能还会问到 2PC、3PC，，，
### MySQL 对分布式事务的支持
分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下 InnoDB 对分布式事务的支持。
MySQL 从 5.0.3 InnoDB 存储引擎开始支持 XA 协议的分布式事务。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。
以下是 MySQL 对分布式事务支持的几个关键点：
1. **XA 事务**：MySQL 的 InnoDB 存储引擎支持 XA 事务，这是分布式事务处理的基础。XA 事务允许跨多个数据库实例的事务操作，要么全部提交，要么全部回滚，以保持数据的一致性。
2. **两阶段提交**：XA 事务使用两阶段提交协议来确保事务的原子性和一致性。在第一阶段，所有的参与资源（如不同的数据库实例）准备提交事务；在第二阶段，根据第一阶段的准备结果，决定是提交还是回滚事务。
3. **隔离级别**：在使用 XA 事务时，InnoDB 存储引擎的事务隔离级别必须设置为 `SERIALIZABLE`，这是最高的隔离级别，可以避免脏读、不可重复读和幻读。
4. **XA 语法**：MySQL 支持 XA 事务的特定语法，包括 `XA START`、`XA END`、`XA PREPARE`、`XA COMMIT` 和 `XA ROLLBACK` 等命令，用于控制分布式事务的流程。
5. **事务管理器和资源管理器**：在 XA 事务中，事务管理器（TM）负责协调全局事务，而资源管理器（RM）负责管理具体的事务资源，如数据库连接。
6. **分布式事务的问题**：XA 事务可能面临一些问题，如超时、死锁和资源管理器故障等。此外，MySQL 中的 XA 事务在早期版本中存在一些限制，比如在服务器故障重启后，已准备的事务的二进制日志可能会丢失，导致主从复制的数据不一致问题。
7. **MySQL 5.7 改进**：在 MySQL 5.7 版本中，对分布式事务的支持有所改进，解决了一些长期存在的问题，比如在客户端退出或服务宕机时，已准备的事务会被正确回滚，以及在服务器故障重启后，相应的 Binlog 被正确处理。
8. **应用场景**：分布式事务在分布式架构或需要跨多个数据库系统进行数据同步的场景中比较常见，例如金融行业的转账操作。
总的来说，MySQL 通过 XA 事务为分布式系统提供了强大的事务支持，确保了跨多个数据库实例的操作能够满足 ACID 原则。随着 MySQL 版本的更新，分布式事务的支持也在不断改进和增强。
> 关于分布式事务，首先你需要弄清楚一个东西，就是分布式事务既可以是纯粹多个数据库实例之间的分布式事务，也可以是跨越不同中间件的业务层面上的分布式事务。前者一般是分库分表中间件提供支持，后者一般是独立的第三方中间件提供支持，比如 Seata。
### 在单体应用拆分成微服务架构之后，你怎么解决分布式事务？
**常见的解决方案**
- **最终一致性：** "最理想的解决方案是通过最终一致性来避免分布式事务。可以通过事件驱动架构、异步消息和服务之间的解耦来处理。每个服务都处理自己的事务，当一个服务完成业务操作后，它会发布一个事件，其他服务订阅并处理相应的事件，逐步确保数据一致性。最终一致性容忍系统中的暂时不一致，但它能有效避免长时间的锁定和事务阻塞问题。"
- **Saga模式：** "Saga模式是解决分布式事务的一种常见方法，它将长事务拆分成多个局部事务，每个局部事务都会发布事件。如果某个事务失败，系统会触发补偿事务来回滚之前的操作。Saga模式有两种实现方式：一种是基于长事务的补偿机制，另一种是基于事件驱动的方式，适用于更高并发的环境。通过Saga模式，我们可以避免分布式事务中常见的性能瓶颈。"
- **两阶段提交（2PC）：** "两阶段提交（2PC）是一种经典的分布式事务解决方案，它通过协调者保证所有参与者在提交事务前的一致性。然而，2PC有一个显著的问题，就是在网络分区或者某些参与者失败时，容易导致系统的阻塞。这使得它不适合高并发和高可用的微服务环境。"
- **三阶段提交（3PC）：** "三阶段提交是2PC的改进，旨在解决2PC中的阻塞问题。它引入了准备阶段、提交阶段和确认阶段来确保系统的一致性。尽管比2PC更安全，但在高并发场景下仍然会对性能造成影响。"
- **TCC模式：** "TCC（Try-Confirm/Cancel）模式将每个操作分为三个阶段：尝试阶段（Try）、确认阶段（Confirm）和取消阶段（Cancel）。如果所有操作都成功，执行确认，失败则执行取消操作回滚之前的所有操作。这种模式适用于复杂的分布式事务场景，确保每个操作的最终一致性。”
**强调最佳实践与策略：** "在实际应用中，我会根据具体的业务场景选择合适的解决方案。如果业务逻辑允许，我倾向于通过**最终一致性**和**事件驱动架构**来避免复杂的分布式事务管理。如果需要严格的一致性保障，可以选择**Saga模式**，它能较好地平衡可用性与一致性。**TCC模式**适用于事务较为复杂的场景，尤其是在资金交易类系统中。"
------
## 七、MySQL 查询
### count(*) 和 count(1)和count(列名)区别
- **COUNT(\*)**：统计表中所有行的数量，包括 `NULL` 值，性能最佳，适用于需要统计总行数的情况
- **COUNT(1)**：与 `COUNT(*)` 类似，统计表中所有行的数量，包括 `NULL` 值，性能与 `COUNT(*)` 基本相同
- **COUNT(列名)**：统计指定列中非 `NULL` 值的数量，适用于需要统计特定列中实际值数量的情况
### UNION和UNION ALL的区别?
UNION和UNION ALL都是将两个结果集合并为一个，**两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；**
- UNION 在进行表连接后会筛选掉重复的数据记录（效率较低），而 UNION ALL 则不会去掉重复的数据记录；
- UNION 会按照字段的顺序进行排序，而 UNION ALL 只是简单的将两个结果合并就返回；
### SQL执行顺序
- 手写
```mysql
SELECT DISTINCT
FROM
JOIN ON
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
```
- 机读
```mysql
FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT
```
> mysql 的内连接、左连接、右连接有什么区别？
>
> 什么是内连接、外连接、交叉连接、笛卡尔积呢？
### Join图
------
## 八、MySQL 锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。
打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。
在 MySQL 的 InnoDB 引擎里面，锁是借助索引来实现的。或者说，加锁锁住的其实是索引项，更加具体地来说，就是锁住了**叶子节点**
1. **锁的物理载体**：B+树索引节点（非数据页）
2. **锁升级条件**：无索引或索引失效时退化为表锁
3. **锁兼容性**：共享锁（S锁）允许并行读，排他锁（X锁）独占写
### MySQL 中有哪几种锁，列举一下？
**从对数据操作的类型分类**：
- **读锁**（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
- **写锁**（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
**从对数据操作的粒度分类**：
为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。
- **表级锁**：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
- **行级锁**：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
- **页面锁**：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
| | 行锁 | 表锁 | 页锁 |
| ------ | ---- | ---- | ---- |
| MyISAM | | √ | |
| BDB | | √ | √ |
| InnoDB | √ | √ | |
| Memory | | √ | |
> ##### MyISAM 表锁
>
> MyISAM 的表锁有两种模式：
>
> - 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
> - 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
>
> MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。
>
> 默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。
>
> ##### InnoDB 行锁
>
> InnoDB 实现了以下两种类型的**行锁**：
>
> - 共享锁又称为读锁，简称 S 锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
>
> - 排他锁又称为写锁，简称 X 锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。
>
> 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是**表锁**：
>
> - 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
> - 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
>
> **索引失效会导致行锁变表锁**。比如 varchar 查询不写单引号的情况。
>
> **InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁**。
### MySQL 中 InnoDB 引擎的行锁是怎么实现的？
> 锁模式(InnoDB有三种行锁的算法)
- **记录锁(Record Locks)**： 单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
```mysql
SELECT * FROM table WHERE id = 1 FOR UPDATE;
```
它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行
在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：
```mysql
-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
```
但是如果查询条件没有命中任何记录，那么就不会使用记录锁，而是使用间隙锁。
- **间隙锁（Gap Locks）**： 当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。
InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的`Next-Key Locking` 算法，请务必牢记：**使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据**。
```mysql
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
```
即所有在`（1，10）`区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。
> 假设表中存在以下id值的记录：
>
> ```
> 0, 5, 11, 15
> ```
>
> 对于给定的查询，InnoDB会产生以下间隙锁：
>
> 1. (0, 5)：0和5之间的间隙：防止在1到4范围内插入新记录
> 2. (5, 11)：5和11之间的间隙：防止在6到10范围内插入新记录
>
> 此外，还会有以下锁：
>
> - 在id=5的记录上的记录锁（Record Lock）：防止其他事务修改或删除id=5的记录
> - (负无穷, 0]和[11, 正无穷)的 Next-Key Lock，可以插入 -1 或 12 。。。
GAP 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
- **临键锁(Next-key Locks)**： **临键锁**，是**记录锁与间隙锁的组合**，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免**幻读**(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)
```mysql
SELECT * FROM table WHERE id BETWEEN 50 AND 100 FOR UPDATE
```
Next-Key 可以理解为一种特殊的**间隙锁**，也可以理解为一种特殊的**算法**。通过**临建锁**可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，`InnoDB` 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。。当查询的索引含有唯一属性（如唯一索引或主键索引）时，InnoDB 存储引擎会对 next-key lock 进行优化，将其降为 record lock，即仅锁住索引本身，而不是范围。
对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
间隙锁和临键锁是在可重复读的隔离级别下才有效果的。
### MySQL 隔离级别与锁的关系？
在 MySQL 中，隔离级别和锁的关系紧密相关，不同的隔离级别通过使用不同的锁机制来控制事务间的并发行为，确保数据的一致性和隔离性。以下是 MySQL 四种隔离级别与锁的关系：
1. **读未提交（Read Uncommitted）**
- 锁机制：最低级别，不使用锁，允许读取未提交的数据。
- 并发性：高。
- 数据一致性：低，可能导致脏读。
2. **读已提交（Read Committed）**
- 锁机制：读取时使用共享锁（S Lock），更新时使用排他锁（X Lock）。
- 并发性：中等。
- 数据一致性：避免脏读，但可能发生不可重复读。
3. **可重复读（Repeatable Read）**
- 锁机制：读取时使用共享锁（S Lock），更新时使用排他锁（X Lock），并使用间隙锁（Gap Lock）防止幻读。
- 并发性：较低。
- 数据一致性：避免脏读和不可重复读，但在默认设置下防止幻读。
4. **串行化（Serializable）**
- 锁机制：事务间完全隔离，所有读取和写入都使用表级锁或行级锁，确保完全隔离。
- 并发性：最低。
- 数据一致性：最高，避免所有并发问题。
选择适当的隔离级别和锁机制，可以在数据一致性和系统并发性之间找到最佳平衡。
### 两个事务 update 同一条数据会发生什么？
在数据库中，如果两个事务同时尝试更新同一条数据，会发生以下几种情况之一，具体取决于数据库的隔离级别和锁机制：
1. **脏读（Dirty Read）**：在读未提交（Read Uncommitted）隔离级别下，一个事务可能会读取到另一个事务未提交的更新数据。如果第二个事务回滚了更改，那么第一个事务就会读到一些最终不会被提交的“脏数据”。
2. **不可重复读（Non-Repeatable Read）**：在读已提交（Read Committed）隔离级别下，一个事务在读取某条数据后，另一个事务可能会修改或更新这条数据。这导致第一个事务无法再次读取到之前的数据状态，即不可重复读。
3. **幻读（Phantom Read）**：在可重复读（Repeatable Read）隔离级别下，一个事务在读取某个范围内的数据后，另一个事务可能会插入新数据，导致第一个事务再次读取该范围时，会发现一些“幻读”的数据行。
4. **更新丢失（Lost Update）**：当两个事务都读取了同一数据项，并且基于读取的值进行更新时，如果它们几乎同时提交，那么第二个提交的事务的更新可能会覆盖第一个事务的更新，导致第一个事务的更新丢失。
5. **第一提交者胜出**：在大多数情况下，数据库会使用悲观锁或乐观锁来管理并发更新。如果使用悲观锁，那么通常只有一个事务能够锁定数据并进行更新，其他事务必须等待。如果使用乐观锁，事务会检查在读取数据后是否有其他事务对其进行了修改，如果没有修改，才会提交更新。
6. **写入冲突**：在某些数据库系统中，如果两个事务尝试同时更新同一数据，可能会引发写入冲突。数据库会根据其冲突解决策略来决定如何处理这种情况，可能会回滚其中一个事务，或者提供一种机制让应用层解决冲突。
为了避免这些问题，数据库通常提供不同的隔离级别，允许开发者根据业务需求选择合适的隔离级别。此外，通过使用事务锁和一致性检查，可以在应用层提供额外的控制来处理并发更新。
在设计系统时，应该仔细考虑并发控制策略，以确保数据的完整性和一致性。在实现时，可以使用数据库提供的锁机制，如行级锁、表级锁等，或者在应用层实现乐观锁或悲观锁逻辑。
### 在高并发情况下，如何做到安全的修改同一行数据？
在高并发情况下安全地修改同一行数据需要确保数据的一致性和完整性，避免并发导致的问题，如脏读、不可重复读和幻读。以下是一些常见的策略：
1. **乐观锁**：乐观锁通过在数据表中添加一个版本号或时间戳字段来实现。每次更新数据时，检查版本号或时间戳是否一致，如果不一致，说明数据在读取后被其他事务修改过，当前事务应该放弃更新。
2. **悲观锁**：悲观锁在事务开始时就对数据行进行锁定，直到事务结束才释放锁。这可以防止其他事务同时修改同一数据行。
3. **原子操作**：使用数据库提供的原子操作，如 `UPDATE ... WHERE` 语句，确保修改操作的原子性。
4. **事务隔离级别**：适当设置事务的隔离级别，如可重复读（REPEATABLE READ）或串行化（SERIALIZABLE），以减少并发事务间的干扰。
5. **数据库的锁机制**：利用数据库的锁机制，如行锁、表锁等，来控制对数据的并发访问。
6. **应用层控制**：在应用层实现重试逻辑，当检测到更新冲突时，可以重试事务。
7. **分布式锁**：使用分布式锁来确保在分布式系统中，同一时间只有一个操作能够修改数据。
8. **消息队列**：将更新操作封装在消息中，通过消息队列顺序处理更新，以避免并发冲突。
9. **唯一索引**：在可能发生冲突的列上设置唯一索引，确保数据库层面上避免重复数据的插入。
10. **条件更新**：使用条件更新，如 `UPDATE ... IF EXISTS`，来避免在数据已被其他事务修改的情况下进行更新。
### 数据库的乐观锁和悲观锁？
**乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题**
乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，**悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。**
### 怎么在数据库里面使用乐观锁？
乐观锁（Optimistic Locking）是一种用于解决并发控制问题的技术，特别适用于读多写少的场景。它假设冲突很少发生，因此不在操作开始时加锁，而是在提交更新时检查是否有冲突。通常通过以下几种方式实现：
- 版本号：在表中添加一个版本号字段，每次更新记录时，版本号加1。更新操作时，检查版本号是否匹配，匹配则更新，不匹配则说明有冲突，需要重新读取数据再进行操作。
- 时间戳：用时间戳字段记录最后修改时间，更新操作时检查时间戳是否匹配。
> 可以用来解决电商中的“超卖”问题。
### select for update有什么含义，会锁表还是锁行还是其他？
for update 仅适用于 InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
假设有个表单 products ，里面有 id 跟 name 二个栏位，id 是主键。
- 明确指定主键，并且有此笔资料，row lock
```mysql
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
```
- 明确指定主键，若查无此笔资料，无lock
```mysql
SELECT * FROM products WHERE id='-1' FOR UPDATE;
```
- 无主键，table lock
```mysql
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
```
- 主键不明确，table lock
```mysql
SELECT * FROM products WHERE id <>'3' FOR UPDATE;
```
- 主键不明确，table lock
```mysql
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
```
**注1**: FOR UPDATE 仅适用于 InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
**注2**: 要测试锁定的状况，可以利用 MySQL 的 Command Mode ，开二个视窗来做测试。
### 死锁有没有了解，写一段会造成死锁的 sql 语句?
死锁是指两个或多个事务在等待彼此持有的资源，从而导致所有事务都无法继续执行的情况。在 MySQL 中，死锁通常发生在并发更新相同的资源时。
```mysql
START TRANSACTION;
UPDATE table_name SET column_a = 1 WHERE id = 1;
-- 这里事务 1 锁定了 id = 1 的记录
UPDATE table_name SET column_b = 2 WHERE id = 2;
-- 等待事务 2 释放 id = 2 的锁
```
```mysql
START TRANSACTION;
UPDATE table_name SET column_b = 2 WHERE id = 2;
-- 这里事务 2 锁定了 id = 2 的记录
UPDATE table_name SET column_a = 1 WHERE id = 1;
-- 等待事务 1 释放 id = 1 的锁
```
### 死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁？
> MySQL 遇到过死锁问题吗，你是如何解决的？
**死锁产生**：
- 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
- 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
- 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
> 死锁产生的四个必要条件
>
> 1. 互斥： 多个线程不能同时使用一个资源。比如线程 A 已经持有的资源，不能再同时被线程 B 持有。
> 2. 持有并等待： 当线程 A 已经持有了资源 1，又提出申请资源 2，但是资源 2 已经被线程 C 占用，所以线程 A 就会处于等待状态，但它在等待资源 2 的同时并不会释放自己已经获取的资源 1。
> 3. 不可剥夺： 线程 A 获取到资源 1 之后，在自己使用完之前不能被其他线程（比如线程 B）抢占使用。如果线程 B 也想使用资源 1，只能在线程 A 使用完后，主动释放后再获取
> 4. 循环等待： 发生死锁时，必然会存在一个线程，也就是资源的环形链。比如线程 A 已经获取了资源 1，但同时又请求获取资源 2。线程 B 已经获取了资源 2，但同时又请求获取资源 1，这就会形成一个线程和资源请求等待的环形图。
**检测死锁**：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB 存储引擎能检测到死锁的循环依赖并立即返回一个错误。
**死锁恢复**：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，**InnoDB 目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚**。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
**外部锁的死锁检测**：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
**死锁影响性能**：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖`innodb_lock_wait_timeout`设置进行事务回滚。
### 如何尽可能避免死锁？
1. 合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引`定位更少的行，减少锁竞争`。
2. 调整业务逻辑 SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
3. 避免`大事务`，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
4. 以`固定的顺序`访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
5. 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里`（运行了 start transaction 或设置了autocommit 等于0）`,那么就会锁定所查找到的记录。
6. 尽量按`主键/索引`去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
7. 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，`减少连接的表`，将复杂 SQL `分解`为多个简单的 SQL。
### 说个自己遇到的死锁案例？
早期我优化过一个死锁问题，是**临键锁引起**的。业务逻辑很简单，先用 SELECT FOR UPDATE 查询数据。如果查询到了数据，那么就执行一段业务逻辑，然后更新结果；如果没有查询到，那么就执行另外一段业务逻辑，然后插入计算结果。
那么如果 SELECT FOR UPDATE 查找的数据不存在，那么数据库会使用一个临键锁。此时，如果有两个线程加了临键锁，然后又希望插入计算结果，那么就会造成死锁。
我这个优化也很简单，就是上来先不管三七二十一，直接插入数据。如果插入成功，那么就执行没有数据的逻辑，此时不会再持有临键锁，而是持有了行锁。如果插入不成功，那么就执行有数据的业务逻辑。
此外，还有两个思路。一个是修改数据库的隔离级别为 RC，那么自然不存在临键锁了，但是这个修改影响太大，被 DBA 否决了。
另外一个思路就是使用乐观锁，不过代码改起来要更加复杂，所以就没有使用。
> 假设表 t 中最大 id 是 10，那么如果两个业务进来，同时执行这个逻辑。一个准备插入 id=11 的数据，一个准备插入 id = 12 的数据。如果它们的执行时序如下图，那么你就会得到一个死锁错误
>
> 造成死锁的原因也很简单。在线程 A 执行 SELECT FOR UPDATE 的时候，因为 id=11 的数据不存在，所以实际上数据库会产生一个(11，supremum] 的临键锁。类似地，线程 B 也会产生一个(10，supremum] 临键锁。
------
## 九、MySQL 调优
### 影响 MySQL 的性能因素有哪些?
- 业务需求对MySQL的影响(合适合度)
- 存储定位对MySQL的影响
- 不适合放进MySQL的数据
- 二进制多媒体数据
- 流水队列数据
- 超大文本数据
- 需要放进缓存的数据
- 系统各种配置及规则数据
- 活跃用户的基本信息数据
- 活跃用户的个性化定制信息数据
- 准实时的统计信息数据
- 其他一些访问频繁但变更较少的数据
- Schema设计对系统的性能影响
- 尽量减少对数据库访问的请求
- 尽量减少无用数据的查询请求
- 硬件环境对系统性能的影响
### 日常工作中你是怎么优化SQL的？
优化SQL的日常工作可以从以下几个方面进行：
1. **索引优化**
- **创建索引**：为频繁查询的列创建合适的索引，特别是主键和外键列。
- **使用覆盖索引**：通过索引来满足查询需求，避免回表查询。
- **删除冗余索引**：清理不常用或重复的索引，以减少维护开销。
2. **查询优化**
- **避免选择所有列**：只选择需要的列，避免使用 `SELECT *`。
- **使用合适的SQL语法**：如使用 `JOIN` 替代子查询，避免N+1查询问题。
- **优化WHERE子句**：使用索引列进行过滤，避免在过滤条件中进行函数运算或转换。
3. **数据库结构优化**
- **规范化与反规范化**：根据实际需求选择合适的数据库设计，平衡数据冗余和查询性能。
- **分区表**：对于大表，使用分区技术来提升查询和维护性能。
4. **缓存机制**
- **应用层缓存**：如Memcached或Redis，缓存频繁访问的数据，减少数据库负载。
- **数据库缓存**：合理设置数据库缓存参数，优化数据库内存使用。
5. **SQL分析与监控**
- **执行计划分析**：使用 `EXPLAIN` 分析SQL的执行计划，了解查询的执行步骤和时间。
- **慢查询日志**：启用慢查询日志，找出并优化执行时间较长的SQL语句。
- **性能监控工具**：如使用New Relic、APM等工具，持续监控数据库性能。
6. **事务控制**
- **减少事务范围**：尽量缩小事务的范围和持续时间，避免长时间锁定资源。
- **合理设置隔离级别**：根据业务需求选择合适的事务隔离级别，平衡并发性和数据一致性。
7. **数据库参数调整**
- **调整连接池**：合理设置数据库连接池大小，避免过多连接导致资源争用。
- **优化数据库配置**：根据硬件资源和业务需求，调整数据库内存、缓存、IO等参数。
通过以上方法，可以有效地优化SQL性能，提高数据库的响应速度和稳定性。持续关注数据库的运行情况，并根据实际需求进行调整，是保持数据库高效运行的关键。
### 什么是最左前缀原则？什么是最左匹配原则？
**最左前缀原则** 和 **最左匹配原则** 是在使用索引时的两个相关概念，它们通常与复合索引（即在多个列上创建的索引）的使用相关：
1. **最左前缀原则**：
- 当使用复合索引时，最左前缀原则指的是查询优化器只会使用复合索引中最左边的列（或前几列）来查找数据。
- 这意味着，如果要利用复合索引，查询条件中必须包含最左边的列。如果查询条件不包含最左边的列，那么索引将不会被使用，或者不会完全被使用。
2. **最左匹配原则**：
- 最左匹配原则是指在复合索引中，只有当前面的列匹配后，才能继续向后匹配。换句话说，只有当前一个列的值已经确定，才能利用下一个列的索引。
- 例如，如果你有一个 (`col1`, `col2`, `col3`) 的复合索引，那么只有当 `col1` 的值确定后，`col2` 的索引才会被使用；同样，只有当 `col1` 和 `col2` 的值都确定后，`col3` 的索引才会被使用。
> 在 MySQL 中，**联合索引 (a, b, c)** 的最左匹配原则使用情况如下：
>
> | **查询条件** | **是否使用索引** | **使用索引的字段** | **底层原因** |
> | --------------------------- | -------------------- | ------------------ | -------------------------------------------- |
> | `WHERE a=1 AND b=2 AND c=3` | ✅ 完全使用 | `a, b, c` | 严格遵循最左顺序，B+ 树逐层定位 |
> | `WHERE c=3 AND b=2 AND a=1` | ✅ 完全使用 | `a, b, c` | 优化器调整顺序后等价于 `a=1 AND b=2 AND c=3` |
> | `WHERE a=1 AND c=3` | ⚠️ 部分使用（仅 `a`） | `a` | 跳过 `b`，`c` 无法直接通过索引定位 |
> | `WHERE b=2 AND c=3` | ❌ 索引失效 | 无 | 未包含最左列 `a`，无法触发索引路径 |
### MySQL常见性能分析手段？
**MySQL Query Optimizer**
1. MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）
2. 当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimizer 时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
**MySQL常见瓶颈**
- CPU：CPU 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat 来查看系统的性能状态
**性能下降SQL慢 执行时间长 等待时间长 原因分析**
- 查询语句写的烂
- 索引失效（单值、复合）
- 关联查询太多 join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置（缓冲、线程数等）
在优化 MySQL 时，通常需要对数据库进行分析，常见的分析手段有**慢查询日志**，**EXPLAIN 分析查询**，**profiling分析**以及**show命令查询系统状态及系统变量**，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
### 性能瓶颈定位
我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：
```mysql
Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
```
### 怎么看执行计划（explain），如何理解其中各个字段的含义？
使用 **Explain** 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
> ```sql
> create table t1
> (
> id int auto_increment primary key,
> col1 varchar(100) null,
> col2 int null,
> col3 varchar(100) null,
> part1 varchar(100) null,
> part2 varchar(100) null,
> part3 varchar(100) null,
> common_field varchar(100) null,
> constraint idx_key2 unique (col2)
> )charset = utf8mb3;
>
> create index idx_key1
> on t1 (col1);
>
> create index idx_key3
> on t1 (col3);
>
> create index idx_key_part
> on t1 (part1, part2, part3);
> ```
各字段解释
- **id**（select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序）
- id 相同，执行顺序从上往下
- id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
- id 相同不同，同时存在，相同的属于一组，从上往下执行
- **select_type**（查询的类型，用于区别普通查询、联合查询、子查询等复杂查询）
- **SIMPLE** ：简单的 select 查询，查询中不包含子查询或 UNION
- **PRIMARY**：查询中若包含任何复杂的子部分，最外层查询被标记为 PRIMARY
- **SUBQUERY**：在 select 或 where 列表中包含了子查询
- **DERIVED**：在 from 列表中包含的子查询被标记为 DERIVED，mysql 会递归执行这些子查询，把结果放在临时表里
- **UNION**：若第二个 select 出现在 UNION 之后，则被标记为 UNION，若 UNION 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED
- **UNION RESULT**：从 UNION 表获取结果的 select
- **table**（显示这一行的数据是关于哪张表的）
- **partitions**（匹配的分区信息，高版本才有的）
- **type**（显示查询使用了那种类型，从最好到最差依次排列 **system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL** ）
- system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
- const：通过主键或唯一索引查找时，表中最多返回一条数据
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
- ref：使用非唯一性索引查询，返回匹配某个索引值的所有行
- ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法就可能是ref_or_null
- index_merge: 在某些场景下可以使用`Intersection`、`Union`、`Sort-Union`这三种索引合并的方式来执行查询
- range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的 where 语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
- index：全表扫描,但仅扫描索引树（**也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的**）
- all：全表扫描，将遍历全表找到匹配的行
> 一般来说，得保证查询至少达到 range 级别，最好到达 ref
- **possible_keys**（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）
- **key**
- 实际使用的索引，如果为NULL，则没有使用索引
- **查询中若指定了使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在 key 列表中**
- **key_len**
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
- key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的
- **ref** （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）
- **rows** （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）
- **filtered**（某个表经过搜索条件过滤后剩余记录条数的百分比）
- **Extra**（包含不适合在其他列中显示但十分重要的额外信息）
额外信息有好几十个，我们看几个常见的
1. `using filesort`：说明 MySQL 会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”
2. `Using temporary`：使用了临时表保存中间结果，比如去重、排序之类的，比如我们在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成查询，`MySQL`很有可能寻求通过建立内部的临时表来执行查询。
3. `using index`：表示相应的 select 操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现 `using where`，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
4. `using where`：当某个搜索条件需要在`server层`进行判断时
5. `using join buffer`：使用了连接缓存
6. `impossible where`：where 子句的值总是 false，不能用来获取任何元祖
7. `Using index condition` : 查询使用了索引，但是查询条件不能完全由索引本身来满足
`Using index condition `通常出现在以下几种情况：
- **索引条件下推（Index Condition Pushdown, ICP）**：这是 MySQL 的一个优化策略，它将查询条件的过滤逻辑“下推”到存储引擎层，而不是在服务器层处理。这样可以减少从存储引擎检索的数据量，从而提高查询效率。
- **部分索引**：当查询条件只涉及索引的一部分列时，MySQL 可以使用索引来快速定位到满足条件的行，但是可能需要回表（即访问表的实际数据行）来检查剩余的条件。
- **复合索引**：在使用复合索引（即索引包含多个列）的情况下，如果查询条件只匹配索引的前几列，那么剩余的列可能需要通过 `Using index condition` 来进一步过滤。
8. `select tables optimized away`：在没有 group by 子句的情况下，基于索引优化操作或对于 MyISAM 存储引擎优化COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
9. `distinct`：优化 distinct 操作，在找到第一匹配的元祖后即停止找同样值的动作
> 在 MySQL 的 EXPLAIN 输出中，`possible_keys` 和 `key` 这两个字段看似相似，但实际上提供了不同的信息：
>
> - `possible_keys`：显示 MySQL 可能使用哪些索引来查找表中的行。
> - `key`：显示 MySQL 实际决定使用的索引。
>
> 为什么两者都需要？
>
> 1. **优化潜力**： `possible_keys` 显示了所有可能的选项，而 `key` 显示了优化器最终的选择。这种对比可以帮助数据库管理员（DBA）了解是否有更好的索引选择。
> 2. **查询分析**： 通过比较 `possible_keys` 和 `key`，我们可以了解优化器的决策过程，这对于复杂查询的优化非常有用。
> 3. **索引使用情况**： `possible_keys` 可能列出多个索引，而 `key` 只会显示实际使用的一个。这有助于识别冗余索引或缺失的索引。
> 4. **优化器行为**： 有时，`possible_keys` 可能列出多个索引，但 `key` 可能是 NULL，这表明 MySQL 认为不使用索引更有效。
### 一般你们怎么建 MySQL 索引，基于什么原则，遇到过索引失效的情况么，怎么优化的?
#### 1. MySQL索引建立的原则
> 1. 最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 `a = 1 and b = 2 and c > 3 and d = 4` 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
> 2. =和in可以乱序，比如 `a = 1 and b = 2 and c = 3` 建立(a,b,c)索引可以任意顺序，mysql 的查询优化器会帮你优化成索引可以识别的形式。
> 3. 尽量选择**区分度高**的列作为索引，区分度的公式是 `区分度 = distinct(col)/count(*)`，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
> 4. 索引列不能参与计算，保持列“干净”，比如 `from_unixtime(create_time) = ’2014-05-29’` 就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 `create_time = unix_timestamp(’2014-05-29’)`。
> 5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
- **选择合适的列**：优先考虑在查询条件、排序（`ORDER BY`）、分组（`GROUP BY`）、连接（`JOIN`）中频繁使用的列上建立索引。这些列通常对查询性能有显著影响。
- **唯一性**：在唯一性要求较高的列上建立唯一索引（`UNIQUE`），如ID号、邮箱等。唯一索引不仅能加快查询速度，还能保证数据的唯一性。
- **覆盖索引**：尽量选择创建覆盖索引（即索引包含了查询所需的所有列），避免回表操作。这样可以显著提升查询性能，尤其是涉及多列的查询。
- **前缀索引**：对于文本类型（如`VARCHAR`、`TEXT`）的列，如果列值较长且前缀具有较高区分度，可以使用前缀索引来节省空间和提高查询效率。
- **复合索引**：在多个列上建立复合索引（即组合索引），以优化涉及多列的查询。但要注意列的顺序，通常将选择性更高的列放在最前面。
- **考虑查询频率**：根据查询的频率和响应时间要求，对高频查询的列进行索引优化。在写操作频繁的表上，要平衡索引的数量，以避免过多索引导致的插入和更新性能下降。
- **避免过多索引**：虽然索引可以加快查询速度，但过多的索引会增加插入、更新、删除操作的成本。因此，需要在查询性能和写性能之间取得平衡。
#### 2. 常见的索引失效情况
- **查询条件中使用了函数或表达式**：当在查询条件中对索引列使用了函数或表达式（如`UPPER(column_name)`），索引可能失效。MySQL需要扫描所有行来计算函数结果，导致全表扫描。
- **隐式类型转换**：如果查询条件中的列和参数类型不匹配（如将字符串列与数字比较），MySQL会进行隐式类型转换，导致索引失效。
- **模糊查询以通配符开头**：使用`LIKE '%value'`形式的模糊查询时，由于通配符位于开头，索引无法使用，MySQL需要进行全表扫描。
- **索引列不在最左侧**：对于复合索引，如果查询条件中未使用复合索引的最左侧列，索引将无法使用（“最左前缀”原则）。
- **`OR`条件未全部使用索引**：如果查询条件中有`OR`，且每个条件都未使用索引，则MySQL无法利用索引，需要进行全表扫描。
- **查询条件中有NULL值**：在某些情况下，索引列的查询条件中如果包含`IS NULL`或`IS NOT NULL`，可能会导致索引失效。
- **查询条件中使用不等号**：使用`<>`或`!=`查询条件时，MySQL可能会选择不使用索引，因为这种条件通常需要扫描大量行。
#### 3. 索引优化方法
- **重构查询**：避免在索引列上使用函数、表达式、隐式类型转换等操作。尽可能让查询条件直接作用于索引列，确保索引生效。
- **使用合适的类型**：确保查询条件中的类型与列的类型匹配，避免隐式类型转换。
- **合理使用通配符**：对于模糊查询，尽量避免通配符开头。如果业务允许，可以考虑在应用层进行拆分查询或引入全文索引（`FULLTEXT`）来处理文本搜索。
- **优化复合索引的顺序**：根据查询条件的使用情况，调整复合索引的列顺序，确保最左前缀列经常在查询条件中使用。
- **拆分复杂查询**：对于使用`OR`的复杂查询，可以尝试将查询拆分为多个子查询，并使用`UNION`合并结果，确保每个子查询都能利用索引。
- **使用覆盖索引**：如果可能，创建覆盖索引，使查询能够直接从索引中获取所需数据，避免回表，提高查询效率。
- **分析查询性能**：使用`EXPLAIN`命令分析查询的执行计划，检查索引是否被使用。根据执行计划的结果，调整索引设计和查询语句。
- **定期维护索引**：对于频繁更新的表，定期进行索引重建或优化，以保持索引结构的高效性。
通过合理的索引设计和优化，可以显著提高MySQL的查询性能。但在实际应用中，需要根据具体的业务需求、数据量和查询频率等因素来灵活调整索引策略，避免索引失效带来的性能问题。
> ##### 如何写sql能够有效的使用到复合索引？
>
> 1. 全值匹配我最爱
>
> 2. **最佳左前缀法则**，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
>
> 3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
>
> 4. 存储引擎不能使用索引中范围条件右边的列
>
> 5. 尽量使用**覆盖索引**(只访问索引的查询(索引列和查询列一致))，减少select
>
> 6. is null ,is not null 也无法使用索引
>
> 7. like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，
>
> 8. 字符串不加单引号索引失效
>
> 9. 少用or，用它来连接时会索引失效
>
> 10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描
>
> 11. 前缀索引：前缀索引就是用某个字段中，字符串的前几个字符建立索引，比如我们可以在订单表上对商品名称字段的前 5 个字符建立索引。使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
>
> 但是，前缀索引有一定的局限性，例如 order by 就无法使用前缀索引，无法把前缀索引用作覆盖索引。
### 一条sql执行过长的时间，你如何优化，从哪些方面？
1. 查看sql是否涉及多表的联表或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成临时表（业务和算法的优化）
2. 涉及连表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合
3. 如果以上两种都不能操作，非要连表查询，那么考虑对相对应的查询条件做索引。加快查询速度
4. 针对数量大的表进行历史表分离（如交易流水表）
5. 数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql有自带的binlog实现 主从同步
6. explain分析sql语句，查看执行计划，分析索引是否用上，分析扫描行数等等
7. 查看mysql执行日志，看看是否有其他方面的问题
------
## 十、分区、分表、分库
### MySQL分区？
一般情况下我们创建的表对应一组存储文件
1. **未分区的表文件结构**
- **MyISAM引擎**： `.frm`（表结构） + `.MYD`（数据文件） + `.MYI`（索引文件） *例如：`user.frm`、`user.MYD`、`user.MYI`*
- **InnoDB引擎**： `.frm`（表结构） + `.ibd`（数据+索引文件） *例如：`user.frm`、`user.ibd`*
当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率
2. **分区后的表文件结构** 每个分区对应独立的物理文件，文件命名规则为： `表名#分区名.ibd` *例如：`user#p0.ibd`、`user#p1.ibd`*
MySQL分区是一种数据库优化技术，通过将表的数据划分为更小、更易管理的部分，来提高查询性能和管理效率。下面是关于MySQL分区的一些关键点，适合在面试中讨论：
**能干嘛**
- 逻辑数据分割
- 提高单一的写和读应用速度
- 提高分区范围读查询的速度
- 分割数据能够有多个不同的物理文件路径
- 高效的保存历史数据
**分区类型及操作**
| 类型 | 说明 | 适用场景 | 性能风险点 | 示例场景 |
| --------- | ------------------------------------------------------------ | ------------------------ | -------------------------------- | ---------------------- |
| **RANGE** | 基于属于一个给定连续区间的列值，把多行分配给分区 | 时间序列、连续数值 | 数据倾斜导致热点分区（如最新月） | 订单表按创建年份分区 |
| **LIST** | 按列表划分，类似于RANGE分区，但使用的是明确的值列表 | 离散枚举值（地区、状态） | 分区键值变更需重构分区 | 用户表按国家代码分区 |
| **HASH** | 按哈希算法划分，将数据根据某个列的哈希值均匀分布到不同的分区中 | 均匀分布请求压力 | 扩容需重新计算哈希分布 | 评论表按用户ID哈希分区 |
| **KEY** | 类似于HASH分区，但使用MySQL内部的哈希函数 | 非整型字段的均匀分布 | 依赖MySQL内置哈希算法 | 日志表按UUID前缀分区 |
**看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？**
- 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
- 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
- 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控
### 如何确定分库还是分表？
> 针对“如何确定分库还是分表？”的问题，你要结合具体的场景。
**何时分表**
当数据量过大造成事务执行缓慢时，就要考虑分表，因为减少每次查询数据总量是解决数据查询缓慢的主要原因。你可能会问：“查询可以通过主从分离或缓存来解决，为什么还要分表？”但这里的查询是指事务中的查询和更新操作。
**何时分库**
为了应对高并发，一个数据库实例撑不住，即单库的性能无法满足高并发的要求，就把并发请求分散到多个实例中去
总的来说，分库分表使用的场景不一样：
- **分表是因为数据量比较大，导致事务执行缓慢；**
- **分库是因为单库的性能无法满足要求。**
### MySQL分库?
**为什么要分库?**
数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对 master 性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。
**分库是什么？**
一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。
**分库的优点：**
- 减少增量数据写入时的锁对查询的影响
- 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短
但是它无法解决单表数据量太大的问题
### MySQL分表?
分表有两种分割方式，一种垂直拆分，另一种水平拆分。
- **垂直拆分**
垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。
- **水平拆分(数据分片)**
单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。
水平分割的几种方法：
- 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
- 还可根据时间放入不同的表，比如：article_201601，article_201602。
- 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
- 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。
### 分布式ID生成方案？
> 分库分表之后，id主键如何处理？
>
> 推荐：https://zhuanlan.zhihu.com/p/107939861
- UUID：`UUID`的生成简单到只有一行代码，输出结果 `c2b8c2b9e46c47e3b30dca3b0d447718`，但UUID却并不适用于实际的业务需求。像用作订单号`UUID`这样的字符串没有丝毫的意义，看不出和订单相关的有用信息；而对于数据库来说用作业务`主键ID`，它不仅是太长还是字符串，而且不是自增的，存储性能差查询也很耗时，所以不推荐用作`分布式ID`。
> UUID 最大的缺陷是它产生的 ID 不是递增的。一般来说，我们倾向于在数据库中使用自增主键，因为这样可以迫使数据库的树朝着一个方向增长，而不会造成中间叶节点分裂，这样插入性能最好。而整体上 UUID 生成的 ID 可以看作是随机，那么就会导致数据往页中间插入，引起更加频繁地页分裂，在糟糕的情况下，这种分裂可能引起连锁反应，整棵树的树形结构都会受到影响。所以我们普遍倾向于采用递增的主键。
- 数据库自增ID：需要一个单独的MySQL实例用来生成ID（DB单点存在宕机风险，无法扛住高并发场景）
- 数据库多主模式
- 号段模式
- Redis：利用`redis`的 `incr`命令实现ID的原子性自增。
- 雪花算法（SnowFlake）：`Snowflake`生成的是Long类型的ID，一个Long类型占8个字节，每个字节占8比特，也就是说一个Long类型占64个比特
Snowflake ID 组成结构：`正数位`（占1比特）+ `时间戳`（占41比特）+ `机器ID`（占5比特）+ `数据中心`（占5比特）+ `自增值`（占12比特），总共64比特组成的一个Long类型。
- **缺点**：时钟回拨需特殊处理
- 改进：
- 百度UidGenerator：自定义时间位、引入RingBuffer
- 美团Leaf：混用号段模式应对时钟问题
- 滴滴出品（TinyID）：基于ZooKeeper的号段服务化
- 百度 （Uidgenerator）
- 美团（Leaf）：号段模式 + 双Buffer预加载
### 分库分表容量是怎么确定的？
**容量确定的原则**
1. **单表数据量控制：**
- **建议单表数据量控制在**500万到2000万 条记录以内。
> **单表数据量超过阈值**
>
> - 行数阈值：单表行数超过500万（一般建议）或2000万（B+树存储结构限制，3层树高对应约2000万行数据，查询效率最优）。
> - **表容量阈值**：单表存储超过2GB，尤其是包含大字段（如BLOB、TEXT）时，读写性能显著下降
- **原因**：单表数据量过大，可能导致查询和索引效率下降，备份和恢复时间也会变长。
2. **单库数据量控制：**
- **建议单库的数据量不超过**100GB。
- **原因**：单库数据量过大，可能导致磁盘I/O成为瓶颈，影响数据库性能。
3. **分片数量规划：**
- **预留空间**：根据未来的数据增长预期，预留足够的分片数量，避免频繁扩容。
- **均衡分布**：确保数据在各个分片之间均匀分布，避免出现热点分片。
### id哈希映射分库的话会产生什么问题？如何解决？
使用 **ID 哈希映射**进行分库时，会将数据分散到不同的数据库实例或节点上，基于用户 ID 或其他字段的哈希值来决定数据的存储位置。这种方法看似能解决大规模数据的存储和查询性能问题，但在实际操作中会带来一些挑战和问题。
1. **数据倾斜 (Data Skew)**
问题**： 哈希映射的目的是均匀分散数据到多个数据库中，但如果哈希函数或分库策略不合理，可能导致某些数据库或分片存储的数据量远大于其他数据库，产生**数据倾斜**，也就是数据分布不均匀。这样，某些数据库会成为性能瓶颈。**
解决方案：
- **改进哈希函数**：选择更加均匀的哈希算法，避免出现某些特定范围的值过于集中。
- **范围分片**：有时可以结合哈希分片与范围分片（例如，按地域、注册时间等划分）来确保更均匀的数据分布。
- **分片重新分配**：在检测到数据倾斜后，可以定期或动态地调整数据的分布，通过数据迁移来平衡负载。
2. **跨库查询复杂度增加 (Cross-shard Querying)**
问题： 使用哈希分库后，查询可能会涉及多个数据库，尤其是当查询需要合并不同分片的数据时。传统的 JOIN 或聚合查询跨越多个分库时会变得非常复杂和低效，尤其是如果分库策略没有设计好，查询性能会显著下降。
解决方案：
- **避免跨库 JOIN**：尽量避免需要跨多个分片或数据库的复杂 JOIN 操作。通过数据 denormalization 或者将数据聚合到单个数据库中来减少跨库查询的复杂度。
- **分库查询优化**：当跨库查询不可避免时，可以使用 **分布式查询引擎** 或中间件（例如 **Hadoop**, **Presto**, **Apache Drill**）来优化跨库查询。
- **聚合和计算预处理**：如果查询频繁，考虑将结果预计算并存储（例如，通过缓存或者周期性计算）。减少实时计算的需求，提升查询性能。
3. **数据迁移与扩展困难 (Data Migration and Scaling)**
问题： 当数据量增加时，可能需要重新划分分片或迁移数据。在哈希映射的分库策略下，若重新划分分库或添加新的分片，现有的数据必须重新哈希并迁移到新的数据库实例，这个过程会非常复杂，且可能需要停机或者长时间的迁移操作。
解决方案：
- **分片重新平衡**：设计支持**动态扩展**和分片重新平衡的机制。使用可以实时调整的 **虚拟节点** 或 **哈希槽** 来减少数据迁移的复杂性。
- **使用分布式数据库系统**：一些分布式数据库系统（如 **Cassandra**, **CockroachDB**）提供自动扩展和数据迁移的能力，可以在不中断服务的情况下平衡分片和迁移数据。
- **预留扩展性**：在设计分库方案时，可以预留扩展的空间，并考虑将来可能需要添加更多分库或分片的情况。
4. **跨库事务管理 (Distributed Transactions)**
问题： 哈希分库导致数据分布在多个数据库中，而在一些操作中可能需要跨多个数据库的事务操作。例如，用户在多个数据库中有数据需要更新或修改时，如何保证事务的一致性（即 ACID 特性）就变得非常复杂。
解决方案：
- **使用 Saga 模式**：Saga 是一种长事务模式，将一个大的分布式事务分解成多个小的子事务，并在子事务失败时通过补偿操作进行回滚。适用于大多数分布式事务场景，尤其是对于微服务架构中的分布式数据更新。
- **采用最终一致性**：在一些业务场景中，避免使用强一致性，而是采用**最终一致性**来允许系统在短时间内不一致，但最终会恢复一致性。可以使用消息队列或事件驱动的方式来保证数据的最终一致性。
- **分布式事务管理器**：使用分布式事务管理器（如 **Atomikos** 或 **Narayana**）来处理跨多个数据库的事务。
5. **查找和聚合性能差 (Lookup and Aggregation Performance)**
问题： 哈希映射分库后，对于某些查询（如获取某个用户的所有数据或跨多个分片进行聚合查询），如果不采取合适的优化策略，查找和聚合性能会大大下降。
**解决方案**：
- **数据冗余**：可以使用 **数据冗余** 或 **复制** 来减少跨库查询。例如，某些字段的冗余存储可以提高查询效率，避免每次都跨多个分片查询。
- **聚合操作分片**：对于聚合类操作，采用 **分布式计算框架**（如 **Apache Spark**、**Flink**）来进行分片内聚合，然后再合并结果。
6. **数据一致性和延迟问题 (Consistency and Latency)**
问题： 在分库哈希策略中，可能会因为多个数据库或分片的网络延迟而引入一定的延迟问题，尤其是在高并发环境下，多个数据库的访问可能会导致较高的延迟，影响用户体验。
解决方案：
- **本地缓存与副本**：通过使用本地缓存（如 **Redis**）来减少对远程数据库的访问，提升响应速度。同时，可以在多个分片之间保持副本，提高数据访问速度。
- **数据同步机制**：采用实时或批量数据同步机制，将热点数据或常用数据同步到访问频繁的分片。
------
## 十一、集群
> 配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道
### 说下 MySQL 主从？
1. **什么是 MySQL 主从复制？**
- 主从复制用于建立一个或多个与主库相同的数据库，称为从库，实现读写分离，提高并发处理能力。
2. **主从复制的作用是什么？**
- 主从复制用于数据热备份、架构扩展、提高并发处理能力，实现读写分离。
3. **MySQL 主从复制是如何实现的？**
- 主从复制通过 I/O 线程和 SQL 线程实现，I/O 线程负责从主库请求 binlog，SQL 线程负责将 binlog 应用到从库。
4. **主从复制可能会遇到哪些问题？**
- 可能遇到的问题包括复制延迟、数据不一致、锁表导致的阻塞、宕机导致的数据丢失。
5. **如何解决主从复制的问题？**
- 可以通过半同步复制策略减少数据丢失风险，采用并行复制减少复制延迟。
6. **什么是 MySQL 集群？**
- MySQL 集群是一组 MySQL 服务器的集合，它们协同工作以提供高可用性、负载均衡和读写分离。
7. **MySQL 集群有哪些类型？**
- 包括主从集群、互主集群、Galera 集群等，每种集群有其特定的应用场景和特点。
8. **如何搭建 MySQL 集群？**
- 搭建 MySQL 集群通常涉及配置多个 MySQL 服务实例，实现主从复制，配置读写分离，以及设置故障转移机制。
9. **什么是 GTID 同步集群？**
- GTID（全局事务 ID）同步集群是一种基于全局唯一 ID 标识事务的复制方式，引入于 MySQL 5.6 版本，用于确保事务在复制集群中的一致性。
10. **如何实现 MySQL 的读写分离？**
- 读写分离通常由业务层实现，可以通过智能路由、负载均衡器或中间件如 ShardingSphere、MyCat 来实现。
11. **什么是半同步复制？**
- 半同步复制是一种提高数据安全性的机制，主库在事务提交后等待至少一个从库接收并写入 relay log 后才返回客户端响应。
12. **MySQL 集群扩容和数据迁移怎么做？**
- 扩容集群可能需要数据迁移，可以通过 mysqldump 工具备份数据，然后在新节点上恢复数据，再配置数据同步。
13. **如何解决 MySQL 主从复制的延迟问题？**
- 可以通过优化网络条件、升级硬件、使用并行复制等方法减少延迟。
14. **MySQL 集群的高可用性是如何实现的？**
- 高可用性可以通过故障检测、自动故障转移、多副本等机制实现。
15. **MySQL 集群中的分库分表是如何考虑的？**
- 分库分表需要考虑数据量、查询模式、业务逻辑等因素，以优化性能和扩展性
### 复制的基本原理？
主从复制用于建立一个或多个与主库相同的数据库，称为从库，实现读写分离，提高并发处理能力
- slave 会从 master 读取 binlog 来进行数据同步
- 三个步骤
1. 主服务器：主服务器上的所有数据修改操作（如 INSERT、UPDATE、DELETE）都会记录到二进制日志（binlog）中。
1. 从服务器：从服务器通过 I/O 线程连接到主服务器，读取主服务器的二进制日志，并将日志内容保存在本地的中继日志（relay log）中。
1. SQL 线程：从服务器的 SQL 线程读取中继日志，并将日志中的数据更新应用到自己的数据库中，以实现主从数据同步。
### MySQL 一主多从？
一旦你提及“一主多从”，面试官很容易设陷阱问你：那大促流量大时，是不是只要多增加几台从库，就可以抗住大促的并发读请求了？
当然不是。
因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。
其实，你从 MySQL 主从复制过程也能发现，MySQL 默认是异步模式：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
而这时，面试官一般会追问你“**MySQL 主从复制还有哪些模型？”**主要有三种。
- 同步复制：事务线程要等待所有从库的复制成功响应。
- 异步复制：事务线程完全不等待从库的复制成功响应。
- 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。
这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。
### 复制的基本原则？
- 每个 slave 只有一个 master
- 每个 salve 只能有一个唯一的服务器 ID
- 每个 master 可以有多个 salve
### 复制的最大问题
- 延时
------
## 十二、其他问题
### MySQL 设计需要注意什么?
在设计MySQL数据库时，有许多方面需要注意，以确保数据库的性能、可扩展性、安全性和可维护性
- 表结构设计：**规范化**、合适的数据类型
- 索引：覆盖索引
- 查询优化：合理使用JOIN
- 分区与分库分表
### 如何在不停机的情况下保证迁移数据的一致性？
1. 创建目标表。
2. 用源表的数据初始化目标表 （mysqldump、XtraBackup 等工具）
3. 执行一次校验，并且修复数据，此时用源表数据修复目标表数据。
4. 业务代码开启双写，此时读源表，并且先写源表，数据以源表为准
> 支持双写大体上有两个方向：侵入式和非侵入式两种。**侵入式方案就是直接修改业务代码**。
>
> **非侵入式一般和你使用的数据库中间件有关**，比如说 ORM 框架。这一类框架一般会提供两种方式来帮你解决类似的问题。
>
> - AOP（Aspect Oriented Program 面向切面编程）方案
> - 数据库操作抽象：可能叫做 Session、Connection、Connection Pool、Executor 等，就是将对源表的操作修改为双写模式
5. 开启增量校验和数据修复，保持一段时间。
6. 切换双写顺序，此时读目标表，并且先写目标表，数据以目标表为准。
> **数据一致性问题**：可以暂时不做处理，后边统一修复，自增主键，可以依业务区决定，是否统一
7. 继续保持增量校验和数据修复。
> 基于 update_time 或者 binlog 都可以
8. 切换为目标表单写，读写都只操作目标表。
**初始化目标表数据**
我选择了从源表导出数据，使用的是 mysqldump 工具。mysqldump 是一个开源的逻辑备份工具，优点是使用简单，能够直接导出整个数据库。缺点则是导出和导入的速度都比较慢，尤其是在数据量非常大的情况下。所以我针对mysqldump 做了一些优化，来提高导出和导入的性能。加快导出速度能做的事情并不多，主要就是开启 extended-insert 选项，将多行合并为一个INSERT 语句。
加快导入速度就可以做比较多的事情。
1. 关闭唯一性检查和外键检查，源表已经保证了这两项，所以目标表并不需要检查。
2. 关闭 binlog，毕竟导入数据用不着 binlog。
3. 调整 redo log 的刷盘时机，把 innodb_flush_log_at_trx_commit 设置为0。
### 说一说三个范式？
数据库设计中的三个范式（3NF）是用于规范数据库的结构，以减少数据冗余和提高数据的一致性。
- 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
- 第二范式（2NF）：第二范式在第一范式的基础上进一步规范化，要求表中的每一列都与主键直接相关，而不是间接相关
- 第三范式（3NF）：第三范式要求列之间没有传递依赖，即非主键列之间不能相互依赖。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y
### limit 100000 加载很慢的话，你是怎么解决的呢？
在 mysql 中 limit 可以实现快速分页，但是如果数据到了几百万时我们的 limit 必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器
**当一个表数据有几百万的数据的时候成了问题！**
日常分页SQL语句
```mysql
select id,name,content from users order by id asc limit 100000,20
```
扫描100020行
如果记录了上次的最大ID
```mysql
select id,name,content from users where id>100073 order by id asc limit 20
```
扫描 20 行。
总数据有500万左右，以下例子
```mysql
select * from wl_tagindex where byname='f' order by id limit 300000,10
```
执行时间是 3.21s
优化后：
```mysql
select * from (
select id from wl_tagindex
where byname='f' order by id limit 300000,10
) a
left join wl_tagindex b on a.id=b.id
```
执行时间为 0.11s 速度明显提升
- 原查询需要扫描并丢弃前 300,000 行数据。优化后的子查询只选择 id 列，大大减少了需要处理的数据量
这里需要说明的是 我这里用到的字段是 byname ,id 需要把这两个字段做复合索引，否则的话效果提升不明显
### 在高并发情况下，如何做到安全的修改同一行数据？
**1、使用悲观锁**
悲观锁本质是当前只有一个线程执行操作，排斥外部请求的修改。遇到加锁的状态，就必须等待。结束了唤醒其他线程进行处理。虽然此方案的确解决了数据安全的问题，但是，我们的场景是“高并发”。也就是说，会很多这样的修改请求，每个请求都需要等待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种请求就会死在那里。同时，这种请求会很多，瞬间增大系统的平均响应时间，结果是可用连接数被耗尽，系统陷入异常。
**2、FIFO（First Input First Output，先进先出）缓存队列思路**
直接将请求放入队列中，就不会导致某些请求永远获取不到锁。看到这里，是不是有点强行将多线程变成单线程的感觉哈。
然后，我们现在解决了锁的问题，全部请求采用“先进先出”的队列方式来处理。那么新的问题来了，高并发的场景下，因为请求很多，很可能一瞬间将队列内存“撑爆”，然后系统又陷入到了异常状态。或者设计一个极大的内存队列，也是一种方案，但是，系统处理完一个队列内请求的速度根本无法和疯狂涌入队列中的数目相比。也就是说，队列内的请求会越积累越多，最终Web系统平均响应时间还是会大幅下降，系统还是陷入异常。
**3、使用乐观锁**
这个时候，我们就可以讨论一下“乐观锁”的思路了。乐观锁，是相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带版本号（Version）更新。实现就是，这个数据所有请求都有资格去修改，但会获得一个该数据的版本号，只有版本号符合的才能更新成功，其他的返回抢购失败。这样的话，我们就不需要考虑队列的问题，不过，它会增大CPU的计算开销。但是，综合来说，这是一个比较好的解决方案。
### 表中有大字段 **X**(例如:**text** 类型)，且字段 **X** 不会经常更新，以读为 为主，将该字段拆成子表好处是什么?
如果字段里面有大字段(text,blob)类型的，而且这些字段的访问并不多，这 时候放在一起就变成缺点了。 MYSQL 数据库的记录存储是按行存储的，数据 块大小又是固定的(16K)，每条记录越小，相同的块存储的记录就越多。此 时应该把大字段拆走，这样应付大部分小字段的查询时，就能提高效率。当需 要查询大字段时，此时的关联查询是不可避免的，但也是值得的。拆分开后， 对字段的 UPDAE 就要 UPDATE 多个表了
### MySQL 数据达到多少会产生瓶颈？
MySQL在处理大型数据集时，性能瓶颈的出现并非仅取决于数据量的大小，还与硬件配置、表结构设计、索引情况、查询复杂度和并发访问量等多种因素密切相关。因此，很难给出一个精确的数据量阈值来确定何时会出现瓶颈。
**一般情况下，以下情况可能会导致MySQL产生性能瓶颈：**
1. **单表数据量过大：**
- **数据量级别**：当单表记录数达到**数百万到数千万**时，查询性能可能会明显下降。
- **影响因素**：如果缺乏合理的索引和优化，查询速度会受到显著影响。
2. **索引设计不合理：**
- **缺少必要索引**：没有为常用查询添加索引，导致全表扫描。
- **过多索引**：索引过多会增加写入和更新的开销。
- **索引碎片**：频繁的插入和删除操作会导致索引碎片化。
3. **硬件资源限制：**
- **内存不足**：无法将常用数据缓存到内存中，导致频繁的磁盘I/O。
- **磁盘性能**：传统HDD的读写速度较慢，可能成为瓶颈。
- **CPU性能**：复杂查询和高并发需要更高的CPU处理能力。
4. **高并发访问：**
- **连接数过多**：大量的并发连接会消耗系统资源，导致性能下降。
- **锁竞争**：高并发写操作会导致锁竞争，影响事务的执行效率。
5. **查询复杂度高：**
- **复杂的JOIN操作**：多表关联查询会增加数据库的计算负担。
- **未优化的SQL语句**：如使用`SELECT *`或缺少条件过滤。
6. **配置参数不当：**
- **默认配置不适合大数据量**：需要根据业务场景调整MySQL的配置参数，如`innodb_buffer_pool_size`。
- **连接池设置不合理**：可能导致资源浪费或不足。
7. **事务和锁机制的影响：**
- **长事务**：长时间占用锁资源，阻塞其他事务。
- **死锁问题**：不合理的事务管理可能导致死锁。
### SQL 注入？
SQL注入是一种常见且危险的安全漏洞，但有几种有效的方法可以防止它。以下是解决SQL注入问题的主要方法：
1. 使用参数化查询（预处理语句）：
这是防止SQL注入最有效和推荐的方法。参数化查询将SQL语句和数据分开处理，从而防止恶意输入被解释为SQL命令。
**不安全的 SQL 查询：**
```sql
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
```
攻击者可以通过输入 `" OR "1" = "1` 这样类似的内容绕过验证，导致 SQL 注入。
**使用预编译语句的安全查询：**
```java
String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, username);
stmt.setString(2, password);
ResultSet rs = stmt.executeQuery();
```
2. 使用ORM（对象关系映射）：ORM工具通常会自动使用参数化查询，提供额外的安全层。
3. 输入验证和清洗：
- 使用白名单策略（允许合法字符），拒绝非预期的输入
- 限制输入的长度，避免输入过多字符
- 对输入的数据类型进行严格验证，如预期是数字类型的字段必须验证输入为数字
- 对特殊字符进行转义或过滤（如 `'`, `"`, `;` 等）
4. **最小权限原则**：确保应用程序连接数据库的账号仅具备完成任务所需的最小权限，避免攻击者一旦突破防线就能全面操控数据库。
5. 当然，还有些 统一编码、定期安全审计、保持软件更新 等措施
## 十三、 手撕 SQL
### 十岁为一组，统计每个年龄段的用户数量
要以每 10 岁为一组统计用户数量，可以使用 `FLOOR` 函数（向下取整）对用户的年龄进行分组，然后使用 `GROUP BY` 和 `COUNT(*)` 来统计每个年龄段的用户数量
```sql
SELECT
CASE
WHEN age BETWEEN 0 AND 9 THEN '0-9'
WHEN age BETWEEN 10 AND 19 THEN '10-19'
WHEN age BETWEEN 20 AND 29 THEN '20-29'
WHEN age BETWEEN 30 AND 39 THEN '30-39'
WHEN age BETWEEN 40 AND 49 THEN '40-49'
WHEN age BETWEEN 50 AND 59 THEN '50-59'
WHEN age BETWEEN 60 AND 69 THEN '60-69'
WHEN age BETWEEN 70 AND 79 THEN '70-79'
WHEN age BETWEEN 80 AND 89 THEN '80-89'
WHEN age >= 90 THEN '90+'
ELSE 'Unknown'
END AS age_group,
COUNT(*) AS user_count
FROM users
GROUP BY age_group
ORDER BY age_group;
---------
SELECT
CONCAT(FLOOR(age / 10) * 10, '-', FLOOR(age / 10) * 10 + 9) AS age_range,
COUNT(*) AS user_count
FROM users
GROUP BY FLOOR(age / 10)
ORDER BY FLOOR(age / 10);
```
### 给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩
这种方法比较多，我用最简单的， 使用 `LEFT JOIN` 和 `IS NULL`
```sql
SELECT
cg.student_id,
s.name,
cg.course_id,
cg.grade
FROM
course_grades cg
JOIN students s ON cg.student_id = s.student_id
LEFT JOIN course_grades cg2 ON cg.student_id = cg2.student_id AND cg2.course_id = '01'
WHERE
cg.course_id = '02'
AND cg2.student_id IS NULL;
```
还可以使用 `NOT IN` 子查询
### 查询第二大的数值
1. 使用 ORDER BY 和 LIMIT
```sql
SELECT DISTINCT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1 OFFSET 1
```
这个查询先按降序排列,然后跳过第一个结果(OFFSET 1),取下一个结果(LIMIT 1)
2. 使用子查询
```sql
SELECT MAX(column_name)
FROM table_name
WHERE column_name < (SELECT MAX(column_name) FROM table_name)
```
3. 使用 `DENSE_RANK()` 窗口函数【MySQL 8.0+ 支持】
```sql
SELECT column_name
FROM (
SELECT column_name, DENSE_RANK() OVER (ORDER BY column_name DESC) AS rank
FROM table_name
) AS ranked
WHERE rank = 2;
```
## 参考与感谢：
- https://zhuanlan.zhihu.com/p/29150809
- https://juejin.im/post/5e3eb616f265da570d734dcb#heading-105
- https://blog.csdn.net/yin767833376/article/details/81511377