谈谈那些欠下的技术债——MySQLMySQL的底层原理 MySQL 架构连接层：主要用于管理连接，权限验证。 Conn

MySQL的底层原理

MySQL 架构

连接层：主要用于管理连接，权限验证。
- Connection Pool：用于减少频繁创建和销毁数据库连接的开销，提升应用程序的性能。
服务层
- SQL接口组件：负责接收SQL语言并分发给其他组件并接收执行结果返回给客户端。
- 解析器：验证SQL语句是否正确，以及将SQL语句解析成MySQL能看懂的机器码指令。
  - 词法分析、语义分析、语法树生成
- 优化器：生成执行计划。
  - 选择最合适的索引，选择最合适的join方式
- 缓存&缓冲：读取缓存与写入缓冲（后续高版本的MySQL移除了查询缓存区）。
存储引擎层：MySQL中所有与磁盘打交道的工作，最终都会交给存储引擎来完成。
- InnoDB
- MyISAM
文件系统层
- 配置文件
  - my.cnf：Linux下的配置文件。
  - my.ini：Windows下的配置文件。
- 数据文件
  - .frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。
  - InnoDB存储引擎
    - .ibd文件：用于存储表数据和索引信息的文件。
    - .ibdata文件：用于存储共享表空间的数据和索引的文件。
  - MyISAM存储引擎
    - .MYD文件：用于存储表中所有数据的文件。
    - .MYI文件：用于存储表中索引信息的文件。
- 日志文件
  - binlog二进制日志，主要记录MySQL数据库的所有写操作（增删改）。
  - redo-log重做/重写日志，MySQL崩溃时，对于未落盘的操作会记录在这里面，用于重启时重新落盘（InnoDB专有的）。
  - undo-logs撤销/回滚日志：记录事务开始前[修改数据]的备份，用于回滚事务。
  - error-log：错误日志：记录MySQL启动、运行、停止时的错误信息。
  - general-log常规日志，主要记录MySQL收到的每一个查询或SQL命令。
  - slow-log：慢查询日志，主要记录执行时间较长的SQL。
  - relay-log：中继日志，主要用于主从复制做数据拷贝。

(一)全解MySQL之架构篇：自顶向下深入剖析MySQL整体架构！无论你是大前端，亦或是Java、Go、Python、C - 掘金

MySQL都有哪些文件你都了解这些文件吗？-腾讯云开发者社区-腾讯云

执行一条 SQL 查询语句期间发生了什么

连接器：建立连接，管理连接、校验用户身份；
查询缓存（MySQL 8.0 已删除该模块）：查询语句如果命中查询缓存则直接返回，否则继续往下执行。
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
1. 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
2. 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
3. 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

SQL执行流程图文分析：从连接到执行的全貌_一条 sql 执行的全流程?-CSDN博客

MySQL8.0之后为什么删除了查询缓存

一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了SQL的查询时间。

存储引擎

InnoDB

特点

支持 ACID 事务，实现 四种事务隔离级别
支持 MVCC（多版本并发控制），减少读写冲突
支持外键（FOREIGN KEY） ，保证数据一致性
支持行级锁（Row-Level Locking） ，高并发下性能更好
使用 Clustered Index（聚簇索引） ，主键索引与数据存储在一起
自动崩溃恢复机制（Redo Log + Undo Log）
存储结构：数据存储在 页（16KB） 单位，采用 B+ 树索引

MySQL ：： MySQL 8.4 参考手册：： 17.1 InnoDB 简介

适用场景

高并发读写场景

存储结构

从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间(tablespace)，而表空间由段（sengment）、区（extent）、页（page）组成。在一些文档中extend又称块（block）。

表空间（table space）

表空间可以看做InnoDB逻辑结构的最高层，所有的数据都放在表空间中。

共享表空间（System Tablespace） ：默认存储在 ibdata1 文件中（存储事务日志、Undo 日志、系统元数据等）。
独立表空间（File-Per-Table Tablespace） ：每张表的 .ibd 文件独立存储数据和索引（推荐）。
临时表空间（Temporary Tablespace） ：存储临时表和临时数据。
Undo Tablespace：存储 Undo Log（回滚日志）。

段（segment）

段是表空间的主要组织结构，用来管理物理文件。创建一个索引（B+树）时，默认同时创建两个段，分别是内节点段（存储主键）和叶子节点段（存储数据）。

数据段（Data Segment） ：存储 表的实际数据。
索引段（Index Segment） ：存储 B+ 树索引。
回滚段（Rollback Segment） ：存储 Undo Log，用于事务回滚。

区（extent）

区是由物理上连续分配的一段空间（每个区的大小都为1MB），默认innodb页（16K）的情况下，一个区由64个连续页组成。【16K * 64 = 1M】

页（Page）

页是文件管理的最小单位，默认每个页的大小为16KB。innodb_page_size参数可以配置页面大小（2KB，4KB， 8KB，16KB等）

常见的页类型：

数据页（B-tree Node）
undo页（undo Log Page）
系统页（System Page）
事务数据页（Transaction system Page）
插入缓冲位图页（Insert Buffer Bitmap）
插入缓冲空闲列表页（Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page）
压缩的二进制大对象页（compressed BLOB Page）

页分裂与页合并

页分裂 (Page Split) ：发生在一个页（数据页）满了，无法再插入新的数据时。为了保持索引的平衡，数据库会将该页的数据分成两部分，并将中间的值提升到父节点，形成新的页。
页合并 (Page Merge) ：当一个页的空间变得过于空闲时，数据库会将其与相邻的页合并，以释放空间并减少碎片。这个操作通常发生在删除大量数据后，或者当数据被压缩时。
危害：
- 页分裂和合并涉及大量的数据移动和重组操作。频繁进行这些操作会增加数据库的 I/O 负担和 CPU 消耗，从而影响数据库的整体性能。
- 分裂和合并可能会导致 B+树索引结构频繁地进行调整，这会影响插入和删除操作的性能。
- 频繁的页分裂和合并还可能导致磁盘上存在较多的空间碎片。新分出的数据页通常会有大量的空闲空间，这会导致数据库表占用更多的磁盘空间，造成资源浪费。
如何避免：
- 选择使用自增的字段作为索引，特别是作为主键索引。
- 插入大量数据，推荐使用批量插入的方式，而不是逐条插入。
- 建议使用逻辑删除而不是物理删除。
- 调整 InnoDB 的配置参数。

深入理解InnoDB中的页分裂与页合并-51CTO.COM

行（row）

InnoDB存储引擎是面向行的(row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。

一文理解 MySQL 中的 page 页-腾讯云开发者社区-腾讯云

MySQL高级进阶：关于InnoDB存储结构，一文深入分析讲解-mysql innodb存储结构

MySQL-逻辑结构 - KuBee - 博客园

MyISAM（已淘汰，不推荐）

特点

不支持事务
不支持外键
表级锁（Table-Level Locking） ，并发性能较差
查询速度快（无事务管理的开销）
容易损坏（断电可能导致数据丢失）
使用 B+ 树索引，但数据与索引分开存储
支持全文索引（FULLTEXT）

适用场景

只读数据库（如日志、文章存储）
低并发的查询场景
MySQL 5.7 及之后版本不推荐 MyISAM

MySQL 8.0以后为什么要移除MyISAM？

锁范围大、容易发生锁冲突（表中的每条数据都有可能会发生冲突），并发度低。

MySQL存储引擎为何弃用MyISAM而投向InnoDB的怀抱（MyISAM和InnoDB的区别）_为什么没人再用myisam存储引擎了-CSDN博客

索引

在关系数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构。相当于图书的目录，可以根据目录中的页码快速找到需要的内容。

索引是什么？索引的作用？ - 简书

索引的原理

数据库索引是什么，它的作用是什么？_数据库索引的作用-CSDN博客

数据库为什么要加索引？

如果没有索引，数据库在查询时必须全表扫描，性能会非常低。索引的作用：

优化查询、更新、删除速度
使用索引直接排序，避免额外排序（ORDER BY、GROUP BY）
加速关联查询的速度（JOIN）
优化去重速度（减少数据化扫描量）（DISTINCT）
减少数据被锁的范围，降低死锁风险

索引类型

按功能分类

主键索引 (Primary Key Index)、复合主键
唯一索引 (Unique Index)、联合唯一索引
普通索引 (Normal Index)
全文索引 (Full-text Index)
复合索引 (Composite Index)
空间索引 (Spatial Index)

mysql系列：全网最全索引类型汇总「建议收藏」-腾讯云开发者社区-腾讯云

聚簇索引与非聚簇索引

聚簇索引(Clustered Index) ：聚簇索引是指数据表中的数据存储顺序与索引的顺序一致，只能有一个。主键通常是聚簇索引。适用于主键查询和范围查询。
非聚簇索引(Non-clustered Index) ：非聚簇索引则是指数据表的索引结构和数据存储结构是分开的。适用于多条件查询和涉及多个列的查询。

密集索引与稀疏索引

密集索引（Dense Index） ：密集索引为每一行数据都建立索引项。通常用于主键和唯一索引。
稀疏索引（Sparse Index） ：稀疏索引并不会为每一行都建立索引项，而是选择性地为某些记录建立索引。通常情况下，只有满足特定条件（例如某些列不为空或者符合某些查询条件）的记录才会被索引。

主键索引和辅助索引（二级索引）

覆盖索引与回表

覆盖索引 (Covering Index) ：只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

只有B-Tree 索引能够支持覆盖索引（Covering Index），而其他类型的索引（如哈希索引、空间索引和全文索引）并不存储索引列的值，因此无法使用它们来实现覆盖索引。
回表 (Table Lookup) ：当索引中没有包含查询所需的所有列时，数据库必须先通过索引获取匹配的行（通常是通过索引的主键或指针），然后再访问数据表获取实际的列数据。

五分钟告诉你什么是MySQL的覆盖索引_mysql什么是覆盖索引-CSDN博客

如何避免回表

使用覆盖索引：如果查询的字段全部都在索引里，就不需要回表。
使用主键查询
避免使用 SELECT *

索引下推

索引下推(Index Condition Pushdown，简称ICP) 能减少回表查询次数，提高查询效率。其中的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

使用条件

ICP目标是减少全行记录读取，从而减少IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。
只能用于range、 ref、 eq_ref、ref_or_null访问方法；
where 条件中是用 and 而非 or 的时候。
ICP适用于分区表。
ICP不支持基于虚拟列上建立的索引，比如说函数索引
ICP不支持引用子查询作为条件。
ICP不支持存储函数作为条件，因为存储引擎无法调用存储函数。

五分钟搞懂MySQL索引下推-腾讯云开发者社区-腾讯云什么是索引下推？（深入精讲）-CSDN博客

联合索引

联合索引（Composite Index）：指由多个列组合在一起的索引。

最左匹配原则

最左匹配原则：在使用联合索引时，按照索引字段的最左前缀进行匹配的规则。只有按索引字段顺序使用，索引才能生效，否则索引可能失效！
若符合最左覆盖原则，则走ref这种索引；若不符合最左匹配原则，但是符合覆盖索引（index），就可以扫描整个索引树，从而找到覆盖索引对应的列，避免回表；若不符合最左匹配原则，也不符合覆盖索引（如本例的select *），则需要扫描整个索引树，并且回表查询行记录，此时，查询优化器认为这样两次查找索引树，还不如全表扫描来得快（因为联合索引此时不符合最左匹配原则，要不普通索引查询慢得多），因此，此时会走全表扫描。

MySQL索引最左匹配原则是什么 • Worktile社区

为什么要使用联合索引

减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。
减少回表：如果覆盖索引的话，直接通过索引就可以取得数据，无需回表。
效率高：索引列越多，通过索引筛选出的数据越少。

【mysql】普通索引和组合索引(复合索引、联合索引) & 最左原则_联合索引和组合索引-CSDN博客

联合索引失效

查询条件跳过字段
范围查询 (>, <，BETWEEN 等) 后续字段的索引会失效。
LIKE '%xxx'（前面有 %）会导致索引失效。
OR 可能导致索引失效，最好用 UNION 代替。
ORDER BY、GROUP BY 必须符合索引顺序，否则索引无法优化排序。

索引失效

联合索引不满足最左匹配原则
使用了select *
索引列参与运算
索引列使用函数
使用 LIKE 时模糊匹配的占位符位于条件的首部
类型隐式转换
使用OR操作
两列做比较
使用不等于
is not null
not in和not exists

15个必知的 MySQL 索引失效场景，别再踩坑了！-mysql索引失效场景

EXPLAIN执行计划

B+ 树索引（默认，InnoDB、MyISAM）
哈希索引（Memory 存储引擎）
全文索引（MyISAM、InnoDB）
R-Tree（空间索引）
自适应哈希索引（InnoDB 自适应优化）

连接池

什么是连接池？

数据库连接池是为了管理和复用数据库连接，在高并发环境下避免频繁创建和销毁数据库连接，从而提高性能和资源利用率。

在应用启动时创建一定数量的数据库连接（连接池）。
这些连接不直接由应用程序创建，而是由连接池统一管理。
当应用需要数据库连接时，从池中获取空闲连接，使用完后，将连接归还池中，而不是关闭连接。

如何优化数据库链接数

合理配置数据库连接池
- 最大连接数（Max Open Connections）
- 最大空闲连接数（Max Idle Connections）
- 连接超时（Connection Timeout）
- 连接最大生命周期（Max Lifetime）
使用数据库连接池
避免长时间持有连接
通过优化数据库查询的性能，减少数据库操作的次数，从而减少连接的使用。

数据库连接泄露

如果在某次使用或者某段程序中没有正确地关闭Connection、Statement和ResultSet资源，那么每次执行都会留下一些没有关闭的连接，这些连接失去了引用而不能得到重新使用，因此就造成了数据库连接的泄漏。

什么是数据库连接泄露？ - justloving - 博客园

事务

事务 ACID

原子性(Atomicity) ：单个事务，为一个不可分割的最小工作单元，整个事务中的所有操作要么全部commit成功，要么全部失败rollback。
一致性(Consistency) ：数据库总是从一个一致性的状态转换到另外一个一致性的状态。事务最终没有提交，所以事务中所做的修改也不会保存到数据库中，保证数据一致性。
隔离性(Isolation) ：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性(Durability) ：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

数据库ACID四大特性到底为了啥，一文带你看通透-腾讯云开发者社区-腾讯云

ACID的具体实现

原子性

事务通常是以 BEGIN TRANSACTION 开始，以 COMMIT 或 ROLLBACK 结束。

MySQL 使用 回滚日志（undolog） 实现原子性：

每条数据变更（INSERT/UPDATE/DELETE/REPLACE）等操作都会生成一条undolog记录，在SQL执行前先于数据持久化到磁盘。
当事务需要回滚时，MySQL会根据回滚日志对事务中已执行的SQL做逆向操作，比如 DELETE 掉一行数据的逆向操作就是再把这行数据 INSERT回去，其他操作同理。

持久性

缓冲池(Buffer Pool)

为了提升性能 InnoDB 提供了缓冲池(Buffer Pool)，Buffer Pool 中包含了磁盘数据页的映射，可以当做缓存来使用：

读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；
- 执行持久化时机
  - MySQL线程低于高水位
  - 当有其他查询、更新语句操作该数据页时

刷脏

脏页：修改数据的时候，也是先写入到 buffer pool，而不是直接写到磁盘。当数据在缓存中，也就是内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。
刷脏：InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
刷脏的时机
- MySQL定时刷
- 缓冲池内存不足时
- MySQL正常关闭的时候
- redo Log满了的时候

MySQL的刷脏机制-腾讯云开发者社区-腾讯云

WAL机制

InnoDB 刷脏时采用的是WAL(Write Ahead Log)机制，即先写日志，再写入磁盘。InnoDB 通过 redo log 日志实现了 WAL，让 MySQL 拥有了崩溃恢复能力。

两阶段提交

在最后提交事务的时候，有 3 个步骤：

写入 redo log，处于 prepare 状态。
写 binlog。
修改 redo log 状态变为 commit。

由于 redo log 的提交分为 prepare 和 commit 两个阶段，所以称之为两阶段提交。

MySQL 为什么需要两阶段提交？@[toc] 为什么要两阶段提交？一阶段提交不行吗？小伙伴们知道，MySQL 中的事 - 掘金

两阶段提交解决的故障

写入 redo log，处于 prepare 状态，binlog 未写入，此时崩溃直接回滚即可。
binlog 已写入， redo log 状态为 prepare
- binlog 不完整，回滚事务
- binlog 完整，提交事务

隔离性

隔离级别

READ UNCOMMITTED：读未提交，也叫未提交读，该隔离级别的事务可以看到其他事务中未提交的数据。该隔离级别因为可以读取到其他事务中未提交的数据，而未提交的数据可能会发生回滚，因此我们把该级别读取到的数据称之为脏数据，把这个问题称之为脏读。
READ COMMITTED（RC） ：读已提交，也叫提交读，该隔离级别的事务能读取到已经提交事务的数据，因此它不会有脏读问题。但由于在事务的执行中可以读取到其他事务提交的结果，所以在不同时间的相同 SQL 查询中，可能会得到不同的结果，这种现象叫做不可重复读。
REPEATABLE READ（RR） ：可重复读，MySQL 默认的事务隔离级别。可重复读可以解决“不可重复读”的问题，但还存在幻读的问题。所谓的幻读指的是，在同一事务的不同时间使用相同 SQL 查询时，会产生不同的结果。例如，一个 SELECT 被执行了两次，但是第二次返回了第一次没有返回的一行，那么这一行就是一个“幻像”行。

注意：幻读和不可重复读的侧重点是不同的，不可重复读侧重于数据修改，两次读取到的同一行数据不一样；而幻读侧重于添加或删除，两次查询返回的数据行数不同。

SERIALIZABLE：序列化，事务最高隔离级别，它会强制事务排序，使之不会发生冲突，从而解决了脏读、不可重复读和幻读问题，但因为执行效率低，所以真正使用的场景并不多。

事务隔离级别脏读不可重复读幻读
读未提交 √ √ √
读已提交 √ √
可重复读 √
串行化

事务隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交		√	√
可重复读			√
串行化

面试突击61：说一下MySQL事务隔离级别？ - 磊哥|www.javacn.site - 博客园

脏读、幻读、不可重复读

脏读：某个事务对一份数据执行了更新操作，另一个事务在此时读取了同一份数据，由于某些原因，前一个事务又执行了RollBack回滚操作，则后一个事务所读取的数据就会是不正确的。我们称此时发生了脏读。也就是读取到了未提交事务的数据，发生在读取阶段。
不可重复读：在同一个事务的先后两次查询的结果数据不一致。可能是在两次查询之间另一个事务执行了更新的操作并已提交。当然大部分情况下这种情况是允许的，毕竟我们要以最新的数据为标准。
幻读：在同一个事务当中先后两次查询结果的总数不一致，例如前一个事务查询了几列（Row）数据，而另一个事务却在此时插入了新的几列数据，前一个事务此时再执行一次查询操作，就会出现有几列数据是未查询出来的，但是如果此时前一个事务想要插入后一个事务插入的数据，就会报错（在前一个事务看来，我明明没有这些数据，怎么还插入不进去？？？就跟发生了幻觉一样）。发生在插入阶段。

MySQL默认隔离级别是RR，但是为什么一些大厂会改成RC？-腾讯云开发者社区-腾讯云

MySQL默认隔离级别是RR，但是为什么一些大厂会改成RC？

提升并发：因为RC在加锁过程中，是不需要添加Gap Lock和Next-Key Lock的，只需要对要修改的记录添加行级锁就行了。另外，RC还支持半一致读，可以大大的减少了更新语句时行锁的冲突。对于不满足更新条件的记录，可以提前释放锁，提升并发度。
减少死锁：因为RR会增加Gap Lock和Next-Key Lock，这就使得锁的粒度变大了，那么就容易导致死锁的概率增大。

MySQL默认隔离级别是RR，但是为什么一些大厂会改成RC？-腾讯云开发者社区-腾讯云

一致性

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。
从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

MySQL 是如何保证一致性、原子性和持久性的！-腾讯云开发者社区-腾讯云

MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种数据库并发控制机制，用于解决读写冲突，提高并发性能。每个事务在读取数据时，看到的是某个时间点的快照，而不是实时数据。这样，即使有其他事务在修改数据，也不会影响当前事务的读取，避免了加锁带来的性能损耗。

基本原理

隐藏字段
1. trx_id（创建版本）：表示插入这条记录的事务 ID。
2. roll_pointer（回滚指针）：指向旧版本的数据，用于构造快照。
Undo Log（回滚日志） ：存储老版本数据，形成版本链，保证事务的可见性。
ReadView（读取视图） ：判断数据可见性，决定当前事务能看到哪些数据版本。

在不同隔离级别下的表现

MVCC 主要用于 REPEATABLE READ（可重复读） 和 READ COMMITTED（读已提交） 隔离级别：

隔离级别	事务能看到的数据
`READ COMMITTED`（读已提交）	读取最新的已提交数据（每次查询都会创建新的快照）
`REPEATABLE READ`（可重复读）	事务开始时的快照（事务内多次查询看到的数据一致）

MVCC 无法解决 SERIALIZABLE（可串行化）和 READ UNCOMMITTED（读未提交） ：

READ UNCOMMITTED：直接读取最新数据，不需要 MVCC。
SERIALIZABLE：需要锁定整个表，避免并发。

锁

共享锁和排他锁

共享锁（S，Share Lock） ：允许其他事务同时读取，但不能修改。
排他锁（X，Exclusive Lock） ：其他事务既不能读，也不能写。

全局锁、表级锁、行级锁

全局锁：对整个数据库实例加锁，所有表都变为只读状态，一般全库备份时使用。
表级锁
- 表锁：直接锁住整张表，阻止其他线程的读写。
- 元数据锁：用于保护表结构，不需要手动加锁。防止 DDL（如 ALTER、DROP）和 DML（如 INSERT、UPDATE）冲突。
行级锁：针对索引生效的，而不是针对物理行。如果查询未命中索引，InnoDB 会退化为表锁，从而影响并发能力。

记录锁、间隙锁、临键锁

记录锁（Record Lock） ：针对 索引上的某一行数据 进行的锁定，仅锁定索引记录本身，不锁定前后的索引间隙。
间隙锁（Gap Lock） ：锁定索引间隙，防止其他事务在此范围内插入新数据，但不会锁定已有的记录。
临键锁（Next-Key Lock） ：记录锁 + 间隙锁 的组合锁，不仅锁定索引记录，还锁定其前后的间隙，防止幻读。

加锁方式

行锁主要通过索引来实现：

精确匹配索引 ➝ 记录锁（Record Lock）
范围查询索引 ➝ 间隙锁（Gap Lock）
范围查询（RR 隔离级别） ➝ Next-Key Lock（记录锁 + 间隙锁）

乐观锁和悲观锁

乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。
悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。

面试必备之乐观锁与悲观锁 - 知乎

意向锁

意向共享锁（IS锁）：用于表示事务计划在未来对某些行加共享锁。它的存在表明事务对表中的行有意向加共享锁，从而确保其他事务不会在表级别上加排他锁。
意向排它锁（IX锁）：用于表示事务计划在未来对某些行加排他锁。它的存在表明事务对表中的行有意向加排他锁，从而阻止其他事务对这些行加共享锁或排他锁。

意向锁是一种表级锁，在触发意向锁的事务提交或回滚后会被释放。

意向锁并非直接用于锁定资源，而是用于通知其他事务，以避免它们在资源上设置不兼容的锁。帮助数据库系统高效地管理并发事务，减少锁冲突，提高性能。

意向锁并非由用户直接请求，而是由MySQL管理的。

✅什么是排他锁、共享锁、意向锁-腾讯云开发者社区-腾讯云

意向共享锁与意向排它锁：详解与应用 - 若-飞 - 博客园

实现机制

事务要对表中的行加 S 锁，先在表级别加 IS 锁。
事务要对表中的行加 X 锁，先在表级别加 IX 锁。
如果一个事务要对整个表加 X 锁，只需检查表级是否有 IS/IX 锁，无需遍历所有行。

意向锁为什么是表级锁？

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）：

如果意向锁是行锁，则需要遍历每一行数据去确认；
如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

mysql的共享锁(S)、排他锁(X)、意向共享锁(IS)、意向排他锁(IX)的关系_is锁-CSDN博客

意向锁的兼容矩阵

	IS	IX	S	X
IS	√	√	√	×
IX	√	√	×	×
S	√	×	√	×
X	×	×	×	×

插入意向锁

Insert Intention Lock（插入意向锁）是一种特殊的 Gap Lock（间隙锁） ，它由 INSERT 操作在实际插入前设置，目的是在并发插入时，避免多个事务相互阻塞（避免不必要的等待和幻读）。

本质上是 Gap Lock，但不会阻塞其他不同位置的插入
多个事务可以在同一个间隙内插入不同的值，不会相互阻塞
只有当插入的位置与现有事务锁冲突时，才会等待
仅适用于 REPEATABLE READ 和 SERIALIZABLE 事务隔离级别

MySQL :: MySQL 8.4 Reference Manual :: 17.7.1 InnoDB Locking

自增锁

MySQL 的自增锁是指在使用自增主键（Auto Increment）时，为了保证唯一性和正确性，系统会对自增字段进行加锁。

全面深入理解MySQL自增锁_mysql 自增锁-CSDN博客

表的插入方式

简单插入模式（Simple inserts）：可以提前确定要插入的行数
批量插入模式（Bulk inserts）：事先不知道要插入的行数，以及所需的自动增量值的数量
混合模式（Mixed-mode）：有些行指定了自增id，有些行未指定自增id

自增锁模式

传统模式：执行语句时加 AUTO-INC 表级锁，statement 语句执行完毕后释放
连续模式：针对批量插入 时会采用 AUTO-INC 锁，针对简单插入时，采用轻量级的互斥锁
混合模式：不使用 AUTO-INC 表级锁，采用轻量级的互斥锁

自增 ID 的连续性

三种模式都无法保障自增id的连续性，除非设置隔离界别为串行化（Serialiable）隔离级别

插入发生唯一索引冲突校验
事务回滚会发生ID 发号段丢弃

MySQL 之 innodb 自增锁原理实现💡 引言：平常在开发过程中，或多或少碰到过数据去重的场景，数据少没问题，数 - 掘金

死锁（Deadlock）

死锁是指多个事务因相互等待资源释放，导致所有事务都无法继续执行的情况。

死锁的原因

锁顺序不一致：事务以不同的顺序锁定行或表，可能导致死锁。
范围锁：当事务锁定索引记录的范围或间隙时，如果某些锁未能及时获取，可能会引发死锁。
并发写操作：在写操作频繁的环境中，多个事务同时尝试更新相同的数据，容易引发死锁。

经典案例

-- 事务 A
START TRANSACTION;
UPDATE orders SET order_name = 'Updated A' WHERE id = 1;

-- 事务 B
START TRANSACTION;
UPDATE orders SET order_name = 'Updated B' WHERE id = 2;

-- 事务 A 继续
UPDATE orders SET order_name = 'Updated B' WHERE id = 2; -- 等待事务 B 释放锁

-- 事务 B 继续
UPDATE orders SET order_name = 'Updated A' WHERE id = 1; -- 等待事务 A 释放锁

事务 A 持有 id=1 的锁，需要 id=2 的锁
事务 B 持有 id=2 的锁，需要 id=1 的锁
两个事务互相等待，导致死锁

如何避免死锁

统一事务的操作顺序
短事务，减少锁持有时间
创建索引，避免锁定过多行
降低隔离级别（降低到 READ COMMITTED 可能减少死锁）
重试机制，处理因死锁而回滚的事务

死锁检测

InnoDB默认开启死锁检测机制，当检测到死锁时，会自动选择一个事务（“受害者”）进行回滚，以打破死锁循环。
如果禁用死锁检测，InnoDB 会依赖 innodb_lock_wait_timeout 设置，在事务等待超时后回滚。

MySQL :: MySQL 8.4 Reference Manual :: 17.7.5 Deadlocks in InnoDB

数据表设计

数据库范式

第一范式（1NF）：确保原子性，所有字段的值必须是不可分割的原子值（即单一值），消除重复的列，避免存储多个值在一个字段中
第二范式（2NF）：消除部分依赖，每个非主键字段都必须完全依赖于主键，而不是部分依赖
第三范式（3NF）：消除传递依赖，非主键字段不能依赖于其他非主键字段，而必须直接依赖主键
适度反规范化（提高查询效率）：针对读多写少的场景，以空间换时间

主键选择

在 MySQL InnoDB 中，尽量选择单调递增的数值类型作为主键，主要是为了提高 B+树索引 的效率，减少碎片，提高插入性能。

字段设计

数值优先选择整型（INT/BIGINT）
- 比 VARCHAR 占用更少空间，索引查询更快。
字符串存储优先考虑 VARCHAR
- VARCHAR 比 CHAR 更节省存储空间（CHAR 适用于定长字段）。
时间字段
- DATETIME（推荐）：精确到秒，占 8 字节，适合大多数时间记录。
- TIMESTAMP（占 4 字节）：会受时区影响，适用于表示事件时间（如创建时间）。
JSON vs. 关系表
- JSON 适用于灵活的结构，但查询性能较差，不适合高频查询场景。
- 规范化存储更利于索引优化。

索引设计

主键索引
二级索引
- 常用查询字段创建索引（INDEX）
- 避免过多索引，每个索引都会影响写入性能。
复合索引
- WHERE + ORDER BY 涉及多个字段时，考虑使用联合索引
- 注意 索引最左匹配原则（Leftmost Prefix Rule）

分库分表

垂直拆分
水平拆分
- 分区表：将一张大表拆分成多个物理存储的分区，但在逻辑上仍然是一张表。

UNION ALL 与 UNION 的区别

UNION 需要额外的 DISTINCT 计算，内部执行 排序（ORDER BY）+ 去重，数据量大时性能开销较大。
UNION ALL 直接合并数据，不进行额外计算，性能更优。

如果数据本身已唯一，或者不关心重复数据，建议使用 UNION ALL 以获得更高的查询性能。

高效分页

基于游标分页

主键

-- 第一页
SELECT * FROM table_name ORDER BY id LIMIT 10;

-- 后续页（假设上一页的最后一个 id 是 100）
SELECT * FROM table_name WHERE id > 100 ORDER BY id LIMIT 10;

其他唯一字段

-- 第一页
SELECT * FROM table_name ORDER BY created_at DESC, id DESC LIMIT 10;

-- 后续页
SELECT * FROM table_name 
WHERE created_at < '2023-10-01 12:00:00'
ORDER BY created_at DESC, id DESC 
LIMIT 10;

覆盖索引优化

如果查询的字段都包含在索引中，可以使用覆盖索引来避免回表操作，从而提升性能。

-- 假设 (created_at, id) 是一个联合索引
SELECT id, created_at FROM table_name 
ORDER BY created_at DESC, id DESC 
LIMIT 100000, 10;

延迟关联

先通过索引获取主键，再通过主键关联查询完整数据。

-- 先获取主键
SELECT id FROM table_name 
ORDER BY created_at DESC 
LIMIT 100000, 10;

-- 再通过主键查询完整数据
SELECT * FROM table_name 
WHERE id IN (/* 上一步查询的主键列表 */);

分区表

对表进行分区，按时间或范围分区后，分页查询只需要扫描部分分区。

-- 查询特定分区的数据
SELECT * FROM table_name PARTITION (p1) 
ORDER BY created_at DESC 
LIMIT 100000, 10;

避免深分页

限制用户只能访问前 N 页。
提供基于条件（如时间范围）的筛选功能，减少数据量。

备份

mysqldump 是 MySQL 自带的逻辑备份工具，用于将数据库或表导出为 SQL 文件。

mysqldump -u [用户名] -p[密码] [选项] [数据库名] [表名] > 备份文件.sql

删除数据

DELETE

逐行删除，可以带 WHERE 过滤
可回滚（需要在 TRANSACTION 内执行）
会触发 DELETE 触发器
不会重置 AUTO_INCREMENT
行级锁（有 WHERE 时）或 表级锁（无 WHERE 时）
删除后需要 OPTIMIZE TABLE 才能回收空间

TRUNCATE

删除整张表的数据
不可回滚、立即生效
不会触发 DELETE 触发器
会重置 AUTO_INCREMENT
表级锁
直接回收存储空间

触发器

触发器（Trigger） ：一种特殊的存储程序，当某些事件（如 INSERT、UPDATE、DELETE）发生时，会自动执行定义好的 SQL 语句。

触发器基于表中的 INSERT、UPDATE 和 DELETE 事件，并且可以定义在操作发生的 之前（BEFORE）或之后（AFTER） 。

一个表最多只能有一个同类型的触发器（如 BEFORE INSERT 只能有一个）。
不能在触发器内部修改被触发表的数据（避免递归调用）。
不能使用事务语句（如 COMMIT、ROLLBACK）。
不能触发 TRUNCATE TABLE（因为 TRUNCATE 不触发 DELETE 触发器）。
触发器对性能有影响，尤其是 AFTER 触发器，需要慎重使用。

存储过程

存储过程Stored Procedure） ：是一组预定义的 SQL 语句，它们被存储在数据库中并通过一个名称来调用。

可以提高代码的可重用性和维护性。
支持输入、输出和输入输出参数，使得数据交换更加灵活。
可以进行事务控制，保证数据一致性。

合理使用存储过程可以提高性能，但过度使用可能导致性能瓶颈。

视图

视图是在数据库中定义的虚拟表。它是一个基于一个或多个实际表的查询结果集，可以像实际表一样被查询和操作，视图本身并不存储数据，它只是通过定义一个查询。视图可以看作是一个动态生成的数据表，其内容是从其他表中选择、过滤和计算得到的。

【MySQL】视图，15道常见面试题---含考核思路详细讲解_数据库视图面试题-CSDN博客

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，不允许添加和删除数据。
视图的操作包括创建视图，查看视图，删除视图和修改视图。

用途

视图根本用途：简化sql查询，提高开发效率。
兼容老的表结构

可更新视图和不可更新视图

可更新视图：通常是基于单个表的简单视图，没有复杂的计算或聚合，且涵盖所有必要的字段。
不可更新视图：涉及多个表的视图，或者使用了 DISTINCT、GROUP BY、JOIN 等会改变数据结构的操作。

高可用

高可用方案

MGR（MySQL Group Replication）
MySQL InnoDB Cluster
MySQL InnoDB ReplicaSet
MySQL InnoDB ClusterSet
MMM（Multi-Master Replication Manager）
MHA（MySQL High Availability）
MySQL NDB Cluster
Galera Cluster
PXC（Percona XtraDB Cluster）

MySQL高可用九种方案-腾讯云开发者社区-腾讯云

主从复制（Master-Slave Replication）

工作原理

主服务器 将数据修改（如 INSERT、UPDATE、DELETE 等）记录到二进制日志（binlog）。
从服务器 通过 I/O 线程连接到主服务器，读取主服务器的 binlog，并将其写入到从服务器的中继日志（relay log）。
从服务器 的 SQL 线程会从中继日志读取事件，并在从服务器上执行这些事件，从而保持与主服务器数据的同步。

同步模式

异步复制：MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理。这样就会有一个问题，一旦主库宕机，此时主库上已经提交的事务可能因为网络原因并没有传到从库上，如果此时执行故障转移，强行将从提升为主，可能导致新主上的数据不完整。
全同步复制：指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
半同步复制：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认。

主从延迟的解决方案

主库刚插入数据，去从库查不到数据，如何解决？

读写使用同一事务
强制从主库读取（强制读写一致性）

主从复制延迟，如果主库刚插入数据，去从库查不到数据，如何解决_mysql集群写入主节点马上去查询可能会查不到吗-CSDN博客