Mysql

百里自来卷

843人浏览 · 2025-04-04 19:24:25

百里自来卷 · 2025-04-04 19:24:25 发布

索引

B+ 树索引（B+ Tree Index）

最常见的索引类型，主要用于 InnoDB 和 MyISAM 存储引擎。
采用的是 B+ 树，它是 B 树 的一种变种。所有数据存储在叶子节点，并且叶子节点之间通过指针相连，形成一个链表。

哈希索引（Hash Index）

哈希索引通常用于 Memory 存储引擎，也可以在某些情况下用于 InnoDB（例如，在特定的存储引擎配置中）或其他存储引擎中。基于 哈希表，使用哈希函数将键值映射到表的特定位置。
只适用于 等值查询，例如 = 和 IN 查询。
不支持范围查询。

全文索引（Fulltext Index）

全文索引用于文本数据的搜索，尤其适合处理 MyISAM 或 InnoDB 中的文本字段（如 TEXT 或 VARCHAR）。
可以通过 MATCH 和 AGAINST 操作符执行全文搜索。
支持对单词进行索引和查询，适用于自然语言的匹配。

聚簇索引（Clustered Index）

InnoDB 存储引擎中主键索引的一个特性，数据表的记录本身就按主键的顺序存储。
聚簇索引本质上是 主键索引，表中的数据按主键顺序存储，因此聚簇索引的数据文件就是索引文件。
一个表只能有一个聚簇索引，因为数据本身只能按一种顺序存储。

非聚簇索引（Non-Clustered Index）

是表的一种独立的索引结构，数据表的记录和索引的顺序不一致。InnoDB 和 MyISAM 都可以使用非聚簇索引。
非聚簇索引是独立于数据存储的，它存储的是数据行的指针（而不是数据本身）。
一个表可以有多个非聚簇索引。

索引失效情况
在查询中使用 OR 条件时，如果其中某个条件不能使用索引，整个索引可能会失效。MySQL 可能会选择不使用索引，转而使用全表扫描。

SELECT * FROM employees WHERE name = 'Alice' OR age = 30;

如果 name 列上有索引，但 age 列没有索引，MySQL 可能会放弃使用索引而进行全表扫描。优化方法是确保每个条件都有索引，或者将查询重写为 UNION 语句。

对于 NOT、IS NULL 和 IS NOT NULL 的查询条件，索引可能失效。

SELECT * FROM employees WHERE name IS NOT NULL;

或者：

SELECT * FROM employees WHERE age NOT IN (30, 40, 50);

在这些情况下，索引可能无法有效使用，尤其是当查询条件使用了反向操作或 null 检查时。

当在查询条件中对索引列使用函数（如 LOWER(), UPPER(), DATE(), CONCAT() 等）时，索引将失效，因为这些函数的应用会导致无法直接匹配原始索引值。

SELECT * FROM employees WHERE LOWER(name) = 'alice';

在这个例子中，LOWER(name) 会导致索引失效。为了避免索引失效，可以避免使用函数，或者将数据存储为统一格式（例如，将所有名称存储为小写字母）。

在使用范围查询（如 BETWEEN, >, <, >=, <=, LIKE 等）时，如果索引列后有其他列没有使用，索引可能会失效。索引会按照列的顺序使用，一旦遇到范围查询，后续的列通常不会参与索引。

SELECT * FROM employees WHERE age > 30 AND name = 'Alice';

如果 age 列在索引的前面，而 name 列在后面，age > 30 会使用索引，但 name = 'Alice' 可能不会。解决方法是确保在适当的顺序使用索引，或者调整查询顺序。

当 LIKE 查询中使用了以 % 开头的通配符时，索引将失效，因为这种查询无法通过索引加速。

SELECT * FROM employees WHERE name LIKE '%lice';

由于 % 通配符位于查询的开头，MySQL 无法使用索引来优化查询，必须进行全表扫描。优化的方法是避免使用前缀 %，或者使用 全文索引。

如果查询包含了 ORDER BY 子句，并且排序的列与索引不一致（例如，索引列的顺序和 ORDER BY 的顺序不匹配），索引可能不会被使用，或者使用索引的效率较低。

SELECT * FROM employees ORDER BY age DESC, name ASC;

如果索引是按 age ASC 或 name ASC 排序的，那么它就不适用于此查询。如果查询的排序顺序和索引的顺序不一致，可能需要进行全表扫描。

当查询使用 DISTINCT 或 GROUP BY 时，可能会导致索引失效，特别是在没有合适的索引支持时。MySQL 可能会选择进行全表扫描以满足查询需求。

SELECT DISTINCT name FROM employees WHERE age > 30;

如果 name 和 age 没有合适的复合索引，MySQL 可能无法有效使用索引，而进行全表扫描。

当使用 IN 子句时，索引可能会失效，尤其是在 IN 子句中列出了很多值。如果查询涉及多个值，MySQL 会计算是否能够通过索引使用最优的执行计划。

SELECT * FROM employees WHERE age IN (30, 40, 50, 60, 70, 80, 90, 100);

在某些情况下，如果 IN 子句中包含大量值，MySQL 可能会放弃使用索引，进行全表扫描。

在多表连接查询中，如果连接条件没有使用索引，或者连接字段上没有合适的索引，MySQL 可能会选择全表扫描。

SELECT * FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'HR';

如果 employees.department_id 或 departments.id 没有索引，MySQL 可能会进行全表扫描。

使用 <> 或 != 时，索引通常无法有效使用，因为 MySQL 无法快速定位到与某个值不相等的所有记录。

SELECT * FROM employees WHERE age <> 30;

在这种情况下，MySQL 可能会选择全表扫描，而不是使用索引。

对于非常小的数据集，MySQL 可能会判断全表扫描比使用索引更高效。即使有索引，MySQL 可能选择全表扫描，因为全表扫描的开销可能更低。

在 MySQL 中创建索引时，正确的设计和使用索引非常关键，因为不当的索引设计可能会影响查询性能，甚至导致性能下降。创建索引时需要注意以下几点：

选择合适的列作为索引

通常索引应当应用在查询中频繁使用的列，特别是作为查询条件（WHERE 子句）的一部分的列。
在多表连接查询（JOIN）中，连接条件中经常涉及的列也是创建索引的好候选。
如果查询中常常使用 ORDER BY 或 GROUP BY 对某个列排序或分组，该列也应该加上索引。
避免对低基数列（如布尔类型列、性别等）创建索引，因为这些列的索引并不会提高查询性能，反而可能会增加存储和维护成本。

单列索引与复合索引

对于经常单独用于查询的列（例如，某一列用于独立的 WHERE 子句），可以使用单列索引。
对于涉及多个列的查询，可以考虑创建复合索引（即多列索引），尤其是当查询经常同时涉及多个列时。复合索引的顺序非常重要，应该根据查询条件中列的顺序来确定复合索引的列顺序（遵循最左前缀原则）。

避免过多的索引，虽然索引能提高查询性能，但过多的索引会带来负面影响：

插入、更新和删除操作的性能下降：每次插入、更新或删除数据时，索引都需要同步更新，因此过多的索引会导致这些操作的性能显著下降。
增加存储空间：每个索引都需要占用额外的存储空间。
只为查询频繁的列创建索引，避免为不常用的列或每个查询条件都创建单独的索引。

选择合适的数据类型

使用合适的数据类型（例如，使用整数而不是字符类型），能让索引更加高效。如果索引列的数据类型较大（例如 BIGINT 或 TEXT 类型），索引的存储和查询性能都会受到影响。因此，选择合适的数据类型会提高查询性能。

保持索引的最新状态

MySQL 支持对索引进行优化（例如，使用 OPTIMIZE TABLE）。随着数据的插入、更新和删除，索引可能会变得不够高效，定期维护索引可以保证查询性能。
使用工具如 EXPLAIN 来查看查询计划，确认索引是否被正确使用，避免冗余索引。

最左匹配原则
在 MySQL 中，最左匹配原则（Leftmost Prefix Rule）是指在复合索引（由多个列组成的索引）中，索引会尽可能从左到右使用列，并且只要查询条件匹配索引的前缀部分，MySQL 就会利用这个索引。如果查询中没有使用索引的最左边列，那么索引就无法被利用。例如：

CREATE INDEX idx_name_age_gender ON employees (name, age, gender);

这个索引是由 name, age, 和 gender 组成的复合索引。根据最左匹配原则，MySQL 会根据查询中的条件，尽量从左到右使用索引列。

如果查询中涉及 name 列，MySQL 可以使用该索引。
如果查询中涉及 name 和 age 两列，MySQL 可以使用该索引。
如果查询中涉及 name、age 和 gender 三列，MySQL 仍然可以使用该索引。
如果查询中只涉及 age 和 gender，但是没有 name 列，则无法使用该索引（因为 name 是最左边的列，age 和 gender 不是索引的最左前缀）。
如果查询只涉及 gender 列，MySQL 也无法使用该索引。

在 MySQL 中，虽然最左匹配原则（Leftmost Prefix Rule）对于复合索引的高效利用至关重要，但在一些特定情况下，这个原则会被阻断，导致索引无法发挥预期作用。

最左匹配原则的核心是 查询条件必须包括复合索引的最左边列，否则索引无法被有效利用。如果查询条件中没有包含复合索引的最左边列，则该复合索引无法使用。

在复合索引中，列的顺序非常重要。最左匹配原则要求查询条件中的列顺序和复合索引中的列顺序保持一致。如果查询条件中的列顺序与复合索引中列的顺序不一致，MySQL 就无法利用该索引。

当查询中对索引列使用了函数、表达式 或 类型转换 时，MySQL 将无法利用索引进行优化，因为 MySQL 无法直接从索引中提取函数或表达式的值来进行高效匹配。

CREATE INDEX idx_name_age_gender ON employees (name, age, gender);

查询1：没有使用函数，符合最左匹配原则：

SELECT * FROM employees WHERE name = 'Alice' AND age = 30;

此查询可以使用索引。

查询2：对 name 列使用了函数（例如 LOWER()）：

SELECT * FROM employees WHERE LOWER(name) = 'alice' AND age = 30;

此查询不能使用索引，因为 LOWER(name) 对 name 列使用了函数，MySQL 无法直接在索引中查找经过函数处理的值。

查询3：对 age 列进行范围查询（例如 BETWEEN 或 <）：

SELECT * FROM employees WHERE name = 'Alice' AND age > 30;

此查询依然可以使用索引，因为 name 列是最左边的列，并且对 age 的范围查询不会阻止最左匹配原则的使用。

使用 OR 操作符时，特别是当 OR 连接的不同条件涉及的列与复合索引的最左前缀不一致时，索引的使用可能会受到阻碍。MySQL 可能无法有效使用复合索引，尤其是在 OR 子句中存在不一致的列顺序时。

OR 连接条件但列顺序一致：

SELECT * FROM employees WHERE name = 'Alice' OR age = 30;

此查询可能无法完全利用索引，因为 OR 子句可能使 MySQL 使用两个独立的索引，而不是一个复合索引。

当查询条件中使用 NOT 操作符时，通常会导致索引失效。NOT 操作符会使得 MySQL 无法通过索引进行高效的查询，因为索引通常只能有效地执行等值或 范围查询。

查询1：使用 NOT 操作符：

SELECT * FROM employees WHERE NOT name = 'Alice';

此查询不能使用索引，因为 NOT 操作符导致 MySQL 无法使用索引进行快速查找。

如果查询中使用 LIKE 并且模式以 % 开头（例如 %pattern），索引将无法用于优化查询，因为 MySQL 无法使用索引来直接匹配以 % 开头的模式。

查询1：LIKE 查询，模式不以 % 开头：

SELECT * FROM employees WHERE name LIKE 'Alice%';

此查询可以使用索引，因为 LIKE 查询是前缀匹配，可以利用索引。

查询2：LIKE 查询，模式以 % 开头：

SELECT * FROM employees WHERE name LIKE '%Alice';

此查询无法使用索引，因为 % 开头的模式会导致全表扫描，而索引无法优化这种查询。

单库单表向分库分表平滑过渡

实现 MySQL 从单库单表向分库分表的平滑过渡，目标是：在不中断服务、不影响业务逻辑的前提下，实现底层数据架构的演进。

✅ 核心设计目标

数据迁移无感知：用户请求不受影响。
读写逻辑可切换：新老逻辑可并存，便于灰度。
系统具备回滚机制：出问题能迅速退回旧逻辑。

🧩 技术步骤
引入 中间层/路由层（Sharding Middleware）

在应用层加入一层 数据库访问中间件或封装 DAO 层，统一路由逻辑，常见中间件有：ShardingSphere、MyCAT
此步骤是整个演进的 起点和关键，后续可以只改配置或路由策略而非业务逻辑。

添加扩展字段 + 抽象主键

增加逻辑分片字段（如 user_id、tenant_id、create_time）；
主键设计为全局唯一 ID（推荐使用雪花算法、UUID、数据库自增 ID 加上库表标识）；

设计 双写架构（可选但强烈推荐）
在数据库迁移期间，可以选择“双写”策略：

写入时同时写入旧表和新表；
读取优先从旧表读，或支持灰度方式从新表读；
通过比对机制（如 checksum 或 binlog 比对）保证数据一致性；

实现 灰度迁移策略
迁移过程推荐使用如下灰度方式进行：

阶段	写入	读取	说明
阶段 1	旧库	旧库	正常运行
阶段 2	旧 + 新（双写）	旧库	验证写入
阶段 3	旧 + 新	灰度用户走新库	验证读取准确性
阶段 4	新库	新库	切换完成，旧库保留
阶段 5	清理旧库	-	彻底完成迁移

这样既保证数据安全，也能灵活应对风险。

增量 + 全量数据迁移方案

全量数据迁移：使用脚本或工具将旧表数据分批导入新库（如：MyDumper + Loader、Canal、自研工具）；
增量数据迁移：利用 binlog（如阿里 Canal）实时同步；或应用层记录“数据迁移标记字段”进行补偿迁移。

确保在切流量前，数据同步已完成。

为了兼容新旧逻辑，业务代码层面要做到：

引入 统一的数据访问层（DAO/Repository）；
所有操作通过中间层决定路由（不直接写死表名或数据库名）；
查询要支持跨表或跨库聚合场景（如分页、统计）时，采用中间件合并逻辑或异步处理。

避免的坑（经验总结）

问题	建议
写入漏写（新库未写）	加强双写日志或链路追踪
数据不一致	使用校验工具对账 + 定期 checksum
查询分页异常	分表分页需重新设计，如用时间戳游标等
跨库事务困难	避免强一致事务，改为最终一致性方案
回滚困难	保留旧表一段时间，便于快速回滚

🧭 推荐演进路径示意图

阶段 0：单库单表
        |
阶段 1：封装访问层（路由逻辑）
        |
阶段 2：新表上线 + 双写 + 数据迁移
        |
阶段 3：灰度切读写
        |
阶段 4：切流量 -> 只写新表
        |
阶段 5：清理旧表 + 完成演进

分库分表平滑过渡的关键在于：

抽象数据访问逻辑；
引入路由中间层；
配合双写+灰度+校验机制；
保障可回滚能力。

分库分表条件

在MySQL中，是否需要进行分库分表取决于多种因素，不仅仅是单表数据量或查询耗时。以下是一些常见的参考指标和判断标准，可帮助你评估是否该进行 分库分表：

🧱 单表数据量的参考阈值：

数据量级	建议	说明
小于 100 万行	不需要分表	普通索引优化足矣
100 万 ~ 1000 万行	优化索引+读写分离	可考虑垂直拆分或读写分离
超过 1000 万行	可能考虑分表	需要根据具体业务查询频率和写入量判断
超过 5000 万 ~ 1 亿行	优先考虑分库分表	性能问题开始明显，维护成本增高

📈 每日数据增长量

< 10 万条/天：短期内不必分表，优化索引和查询即可。
10 万 ~ 100 万条/天：中期内可能需要考虑分区表或分表。
> 100 万条/天：通常推荐使用分库分表，或考虑使用大数据平台（如ClickHouse、TiDB等）。

⏱️ 查询耗时的考量

查询时间 > 200ms 且优化空间有限（如已建好索引）；
慢查询频繁出现（> 1s）；
JOIN 操作变慢 或排序、分页卡顿；
大表上频繁的写入/更新操作对性能造成明显影响；

此时，可以考虑进行 水平分表（按范围、哈希、时间等拆分）或 垂直分表（按字段或功能模块划分）。

🛠️ 实战评估方式

判断维度	是否建议分库分表
表的数据行数 > 1000 万？	✅ 倾向考虑分表
查询耗时超过 500ms？	✅ 优化后仍慢则考虑分表
每天新增数据是否快速增长？	✅ 未来可能成为大表需提前设计
业务系统是否需要高并发支撑？	✅ 读写压力大，建议分库分表或读写分离
数据访问是否集中在某类数据（如某天、某用户）？	✅ 可做分区或分表

💡 替代方案（分库分表前的优化）

优化 SQL 语句，避免 SELECT *。
合理建索引，覆盖索引优先。
使用分区表（MySQL 5.7 以后支持）。
读写分离（主从复制 + 中间件）。
缓存热点数据（Redis 缓存热点查询）。

事务隔离级别实现原理

事务隔离级别通过 锁机制 和 数据快照 来实现。MySQL 的 InnoDB 存储引擎主要使用 行级锁 和 多版本并发控制（MVCC, Multi-Version Concurrency Control）来保证不同事务之间的数据隔离性。不同的事务隔离级别通过不同的方式控制事务的 可见性，即一个事务能看到其他事务未提交或已提交的数据变化。以下是每个事务隔离级别背后的实现原理及它们之间的区别：

Read Uncommitted（读未提交），在这个隔离级别下，一个事务可以读取其他事务未提交的数据，可能会出现 脏读（Dirty Read），即读取到其他事务正在修改的数据。MySQL 在这个隔离级别下，不会对数据加锁或做任何限制，直接读取数据。

Read Committed（读已提交），在这个隔离级别下，一个事务只能读取其他事务已提交的数据，因此避免了脏读。但是，仍然可能出现 不可重复读（Non-repeatable Read），即事务在执行过程中读取相同数据时，数据可能发生变化。虽然避免了脏读，但由于数据的提交是即时可见的，可能会读取到事务提交后的修改，导致相同的查询在同一事务中返回不同的结果。

当事务读取数据时，MySQL 会使用共享锁（S锁），确保数据在读取时不被其他事务修改。也就是说，其他事务不能对该数据行进行写操作（更新、删除），但可以读取数据（即允许其他事务对该数据行加共享锁）。InnoDB 使用 多版本并发控制（MVCC）来实现这一行为。每个事务都有自己的视图，它看到的是其他事务已提交的数据（通过版本号区分不同事务的数据快照）。

Repeatable Read（可重复读），在这个隔离级别下，事务在执行过程中对已经读取过的数据加锁，确保后续读取时数据不会发生变化。这样可以避免 不可重复读。但仍然可能出现 幻读（Phantom Read），即事务读取时，其他事务可以插入新的数据行，导致查询结果数量变化。这个隔离级别避免了不可重复读，但仍然可能遇到幻读问题。

为了避免不可重复读，InnoDB 会为读取的数据加 共享锁（S锁），并且在事务提交之前，不允许其他事务修改这些数据。通过 MVCC，InnoDB 会保持多个版本的数据，每个事务会读取在其事务开始时的一致视图。即便其他事务对数据进行了修改，它们的修改对当前事务是不可见的。为了避免幻读，InnoDB 使用了 间隙锁（Gap Lock） 来锁定索引范围，防止其他事务在该范围内插入新数据。

Serializable（可串行化），这是最高级别的隔离级别，它通过强制事务串行化来避免脏读、不可重复读和幻读。在此级别下，事务会像按顺序一个接一个地执行一样，完全隔离。每个事务在执行时都会加 排他锁（X锁），使得其他事务无法同时修改相关数据。虽然提供了最强的数据一致性保障，但也会大幅度影响系统性能，降低并发性，因为事务之间的冲突会导致大量的等待和锁竞争。

为了实现完全的隔离，InnoDB 会为涉及的数据加 排他锁（X锁），这会阻止其他事务对该数据进行任何修改或读取。在此级别下，所有事务必须按顺序执行，不允许事务并行进行，导致极大的性能瓶颈。

MySQL 通过 行级锁 和 多版本并发控制（MVCC） 来实现隔离级别的控制。

ACID 实现原理

Atomicity，原子性保证了一个事务中的操作要么全部成功，要么全部失败。事务中的操作不会被部分执行，也就是说如果事务中的某个操作失败，之前的所有操作都将被撤销，系统回到事务开始之前的状态。原理：InnoDB 使用 Undo Log 来支持原子性。每次事务执行时，InnoDB 会记录修改前的数据副本（即 Undo Log）。如果事务失败或显式调用回滚（ROLLBACK），InnoDB 会根据 Undo Log 将数据恢复到事务开始前的状态。InnoDB 通过日志记录每个操作。无论是成功还是失败，系统都会根据这些日志恢复数据。

Consistency，一致性保证事务开始前和结束后，数据库的状态都必须是一致的。每个事务都必须使数据库从一个一致性状态转换到另一个一致性状态。事务的执行不会破坏数据完整性约束（如外键约束、唯一性约束等）。原理：数据库的完整性约束（如主键、外键、唯一性等）保证在事务执行过程中，数据始终符合预定义的规则，在事务提交之前，所有操作都必须满足数据库的完整性约束。如果事务的任何操作破坏了约束（如插入一个违反外键约束的记录），事务将被回滚，从而保证数据库的一致性。触发器（Triggers）和存储过程可以确保复杂的约束和逻辑的执行。它们会在事务提交时验证数据库的一致性。MySQL 使用 Redo Log 和 Undo Log 来保证一致性，即使在系统崩溃的情况下，日志也可以帮助恢复数据到一致性状态。

Isolation，隔离性保证了事务在执行时是独立的，不会受到其他事务的干扰。不同事务之间的操作必须是隔离的，事务的中间状态对其他事务不可见。原理：InnoDB 使用 行级锁、表级锁 和 意向锁 等机制来实现事务之间的隔离，保证事务不会发生冲突，能够按顺序执行。

Durability，持久性确保一旦事务提交，它对数据库的所有更改都是永久性的，即使系统发生崩溃或断电，这些更改也不会丢失。事务一旦提交，数据就会被持久化到磁盘中。原理：Redo Log 是 MySQL 中实现持久性的关键。每个事务的提交操作会记录到 Redo Log 中，Redo Log 是一个顺序写入的日志，当事务提交时，Redo Log 会被写入磁盘，并保证不会丢失。即使数据库崩溃，系统在恢复时会使用 Redo Log 来重做（redo）已提交事务的操作，从而保证数据的持久性。InnoDB 使用 Doublewrite Buffer 来进一步提高数据安全性。每次将数据页写入磁盘之前，它会先写入 Doublewrite Buffer，这样可以避免磁盘出现部分写入的情况，保证数据的一致性。InnoDB 提供了多个 innodb_flush_log_at_trx_commit 配置选项，控制日志写入磁盘的方式。默认设置为 1，即每次事务提交都会立即将 Redo Log 刷新到磁盘，保证持久性。

binlog、redolog、undolog

Binlog（Binary Log） 是 MySQL 用来记录数据库所有数据更改操作的日志文件。它是 MySQL 数据库的核心组件之一，广泛应用于 数据复制、数据恢复 和 故障恢复 等操作中。

Binlog 是 MySQL 主从复制（Master-Slave Replication）架构的核心。在主服务器上启用 Binlog 后，所有对数据库进行更改的 SQL 语句（例如 INSERT、UPDATE、DELETE 等）都会被记录在 Binlog 文件中。从服务器会读取主服务器的 Binlog，并执行相应的操作，保证从服务器的数据与主服务器一致。

Binlog 允许数据库进行 点-in-time 恢复，即将数据库恢复到某个时间点的状态。在主数据库发生灾难或崩溃后，可以通过备份（如数据文件的备份）和 Binlog（记录从备份时间以来的所有数据修改）来恢复数据库的状态。如果从服务器的数据丢失或损坏，可以利用主服务器的 Binlog 来重新同步数据，从而恢复复制链路。

Binlog 并不是一个单一的文件，它由一系列的文件组成。通常，文件名为 mysql-bin.000001、mysql-bin.000002 等。当文件达到一定大小时，MySQL 会自动轮换生成新的文件。并且 Binlog 是异步地记录数据的，这意味着当执行某个 SQL 语句时，MySQL 并不会立刻写入 Binlog，而是异步地将数据记录到 Binlog 文件中。

MySQL 支持三种 Binlog 格式，每种格式在记录数据时有不同的粒度：

Statement-based Logging (SBL，基于语句的日志)，记录实际执行的 SQL 语句。这是最常见的格式。这种方式存储的日志文件较小，便于管理和传输。
Row-based Logging (RBL，基于行的日志)，记录每一行数据的修改操作。这意味着每条 INSERT、UPDATE、DELETE 操作都会记录具体的行变化。这种方式在复制过程中不容易出现数据不一致的问题。但是日志文件可能非常庞大，尤其是当数据表更新非常频繁时。
Mixed Logging (混合日志)，结合了 Statement-based 和 Row-based 两种格式。MySQL 会根据具体的 SQL 语句决定采用哪种日志格式。在大多数情况下，它可以兼顾性能和一致性，避免 Statement-based 和 Row-based 的缺点。但是相较于纯 Statement-based 或 Row-based，配置和管理稍显复杂。

在 MySQL 中，Redo Log 是 InnoDB 存储引擎用于实现 事务持久性 和 崩溃恢复 的关键机制之一。Redo Log 记录了对数据库所做的 已提交事务的所有修改操作，并且能够在系统崩溃后恢复数据到崩溃之前的状态。

在事务提交时，Redo Log 确保事务的修改操作被永久记录下来，即使数据库崩溃，已提交的事务依然可以恢复。这是保证 ACID（原子性、一致性、隔离性、持久性） 中 持久性 的一个重要环节。在 MySQL 或操作系统崩溃时，Redo Log 使得 InnoDB 存储引擎能够恢复到崩溃时的最后一致性状态。所有已提交事务的操作都会在恢复时重新执行，以保证数据不丢失。

Redo Log 使用 Write-Ahead Logging (WAL) 策略，即在对数据进行修改之前，必须先将修改记录写入 Redo Log。

当一个事务执行时，InnoDB 会将事务对数据的修改（如 INSERT、UPDATE、DELETE）记录到 Redo Log 中。这些日志通常包含了对数据库页（即数据块）的修改信息，而不是每个 SQL 语句的内容。当事务提交时，InnoDB 会首先将所有已修改的数据写入 Redo Log。提交的事务会被标记为已提交，系统会保证在崩溃恢复时，这些已提交的事务能够被重做。当 MySQL 重新启动时，InnoDB 会从 Redo Log 中读取所有在崩溃之前已提交的事务，并将其“重做”（redo）到数据库中，从而保证数据的持久性。当 Redo Log 文件满了时，InnoDB 会将日志中的数据刷新到磁盘中的数据库页上，并在日志中写入新的内容。这是 Redo Log 的写入操作。

Redo Log 文件通常由多个文件组成，这些文件通常是 ib_logfile0、ib_logfile1 等。每个文件是固定大小的，系统会按顺序写入这些日志文件，直到一个文件写满后，开始写下一个文件。InnoDB 使用 Log Buffer 来缓存 Redo Log 的内容，在事务提交时会将 Log Buffer 中的数据刷新到磁盘上的 Redo Log 文件。这个操作称为 log flush。

Redo Log 由两种类型的日志组成：

事务日志：记录所有事务对数据页的修改。每次事务提交时，都会在 Redo Log 中写入一条对应的日志记录。
日志文件：Redo Log 数据存储在磁盘上的日志文件中。每个日志文件大小是固定的（由 innodb_log_file_size 配置项设置）。日志文件写满后，InnoDB 会开始写入下一个日志文件。

Redo Log 记录已提交事务的修改操作，用于 数据恢复，即保证事务的 持久性。Undo Log 记录未提交事务的操作，用于 事务回滚，即保证事务的 原子性。Redo Log 保证了事务提交后的修改持久化，即便系统崩溃也不会丢失。Undo Log 则用于回滚未提交的事务，确保事务的原子性。

Redo Log 是异步写入的，它能够减少磁盘 I/O 操作，提升系统的性能。

Undo Log 是实现 事务的原子性 和 一致性 的关键日志。它记录了每个事务所做的 数据撤销操作，以确保在事务出现错误或被回滚时，能够撤销事务所做的任何修改。Undo Log 是 事务日志 的一部分，在事务的生命周期内起着至关重要的作用。

Undo Log 的主要功能是为 事务回滚（rollback） 提供支持。当一个事务执行某些操作时，如果发生错误或者事务被显式地回滚，那么这些操作就需要被撤销。Undo Log 记录了事务所做的修改的 前镜像数据（Before Image），即修改之前的数据副本，以便在事务回滚时可以恢复到原始状态。即：

事务的回滚：当事务未提交就发生错误，或者调用了 ROLLBACK 命令，Undo Log 用于撤销事务对数据的所有修改，恢复数据的初始状态。
保证事务的原子性（Atomicity）：原子性要求事务要么完全执行，要么完全撤销。Undo Log 通过记录原始数据的副本来确保在事务失败时能够回滚，从而保证原子性。
多版本并发控制（MVCC）：Undo Log 也在 多版本并发控制（MVCC） 中起到作用，它使得事务可以读取未提交的数据修改，从而在并发环境下提高事务的隔离性。

在事务执行过程中，InnoDB 会记录所有数据的变化。在数据更新时，Undo Log 会保存这些变化前的数据副本。即：

当一个事务对某个数据进行修改时，InnoDB 会首先将该数据的 修改前的值（Before Image） 记录到 Undo Log 中。这个副本是为了保证在事务回滚时可以恢复数据到修改前的状态。
修改数据，事务会修改数据页中的值，通常这些修改会先在内存中进行，而后再异步地刷新到磁盘。
事务提交或回滚，如果事务提交，Undo Log 中的记录将被丢弃，因为修改已经成功写入数据库。如果事务回滚，InnoDB 会使用 Undo Log 中的记录来撤销数据的修改，把数据恢复到修改之前的状态。

Undo Log 在实现 多版本并发控制（MVCC） 中起到重要作用。MVCC 是通过为每一行数据维护多个版本来避免读锁，允许多个事务并发执行，且不会相互阻塞。在读取数据时，InnoDB 会检查当前事务的 提交时间戳，并通过 Undo Log 确保读取到的数据是事务开始时可见的，避免读取到未提交事务的修改。在更新数据时，InnoDB 会在 Undo Log 中保存旧的值，使得其他事务在读取时可以看到旧版本的数据，避免读取到不一致的结果。

Undo Log 和 Redo Log 都是事务日志的一部分，但它们在数据库事务的处理过程中有不同的作用：

Undo Log：用于回滚事务，记录事务修改前的数据副本。它主要用于保证事务的 原子性 和 一致性。当事务回滚时，Undo Log 被用来恢复数据。
Redo Log：用于恢复已提交的事务，记录已提交事务的操作。当系统崩溃时，Redo Log 用来重做已提交事务的操作，确保事务的 持久性。

Undo Log 并不是存储在一个单独的文件中，它存储在 InnoDB 的 Undo 表空间（Undo Tablespace） 中。每个事务有一个独立的 Undo Log。当事务提交时，Undo Log 中的记录会被丢弃。Undo Log 主要用于保证事务的原子性，特别是在 崩溃恢复 或 回滚操作 时。

日志种类

错误日志（Error Log）
错误日志用于记录 MySQL 服务器的启动、运行和停止过程中发生的错误、警告以及重要信息。它非常重要，尤其在数据库出现问题时，管理员可以通过错误日志查看发生的异常。通常是 hostname.err，可以在 MySQL 配置文件中指定 log_error 选项来改变路径。

/var/log/mysql/error.log

二进制日志（Binary Log）
二进制日志记录了所有修改数据库状态的操作，如数据的插入、更新和删除等。它非常重要，主要用于 数据复制 和 数据恢复。
在 MySQL 配置文件中通过设置 log_bin 来启用二进制日志：

log_bin = /var/log/mysql/mysql-bin

查询日志（General Query Log）
查询日志记录了 MySQL 服务器执行的所有 SQL 查询，无论这些查询是否成功执行。这个日志对于 性能调试 和 问题排查 很有帮助。日志文件可以是 mysql-query.log，可以在 MySQL 配置文件中设置 general_log_file 来指定路径。在 MySQL 配置文件中通过设置 general_log 来启用查询日志：

general_log = 1
general_log_file = /var/log/mysql/mysql-query.log

慢查询日志（Slow Query Log）
慢查询日志用于记录执行时间超过一定阈值的 SQL 查询。这对于查找数据库中 性能瓶颈 或 优化慢查询 非常有用。文件格式：慢查询日志的文件名可以是 mysql-slow.log，具体路径可以在 MySQL 配置文件中指定。在 MySQL 配置文件中设置 slow_query_log 以启用慢查询日志，并指定 long_query_time 来设定慢查询的阈值：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

中继日志（Relay Log）
中继日志是 MySQL 主从复制架构中，从服务器用于记录从主服务器接收到的二进制日志的副本。中继日志存储了主服务器的变更日志，供从服务器执行。中继日志的文件名通常是 relay-log.000001，relay-log.000002 等。中继日志在主从复制配置中自动启用，不需要手动开启。

事务日志（InnoDB Log）
InnoDB 存储引擎使用 事务日志 来保证数据的持久性。它记录了 InnoDB 存储引擎的事务操作，用于崩溃恢复。保证事务的 原子性 和 持久性。InnoDB 事务日志通常由 ib_logfile0、ib_logfile1 等文件组成。事务日志是 InnoDB 存储引擎的一部分，通常是自动启用的。可以通过 innodb_log_file_size 等配置项来调整事务日志的大小和其他参数。

二进制日志索引（Binary Log Index）
二进制日志索引记录了所有二进制日志文件的文件名及其顺序。这对于管理多个二进制日志文件很有帮助。通常是 mysql-bin.index 文件。

审计日志（Audit Log），MySQL Enterprise 版本支持审计日志记录，它能够记录所有的数据库操作，特别是对 安全性 和 权限管理 相关的操作。用于 安全审计 和 合规性检查。审计日志通常需要额外的插件（如 MySQL Enterprise Audit Plugin）来启用。

B+树和 B 树数据增删改查后的结构调整

在 MySQL 中 B+ 树是一种自平衡的树数据结构，其中所有的值都存储在叶子节点中，内部节点则只存储索引信息。

查，B+ 树查询是从根节点开始，逐层向下寻找，直到叶子节点。每个节点（非叶子节点）都包含多个键值，查询时通过与键值的比较，决定进入哪个子树，直到进入叶子节点。叶子节点存储了实际的数据记录，查询到叶子节点后，可以直接找到对应的记录。

增，在 B+树中插入数据时，过程相对复杂，可能会触发节点的分裂，甚至导致树的高度增加。插入过程的基本步骤如下：

首先，按照与查询操作相同的方式，找到插入数据的叶子节点。
如果该叶子节点还有空间容纳新插入的数据，就直接插入。叶子节点通常按顺序存储数据，所以新数据会插入到正确的位置。
如果叶子节点满了（超过了预设的容量），就需要进行分裂。分裂过程如下：
- 将叶子节点的中间数据值上升到父节点。
- 将叶子节点一分为二，左侧包含较小的一半数据，右侧包含较大的一半数据。
- 如果父节点也满了，会继续分裂父节点。
- 如果父节点已经满了，需要递归地分裂父节点，直到找到不满的父节点或者分裂到根节点。若根节点分裂，则会增加树的高度。

删，删除数据时，B+ 树也需要处理节点的合并和重新平衡。删除的过程如下：

首先根据查询操作，找到需要删除的数据所在的叶子节点。
如果要删除的数据存在于叶子节点且该叶子节点的剩余空间大于最小要求，则直接删除该数据。
如果删除数据后，叶子节点的键值数量少于最小要求（通常是 ⌈m/2⌉，其中 m 为节点的最大容量），则会尝试与相邻的兄弟节点合并。
如果兄弟节点也无法合并，就会将父节点中相应的索引键值下移，进行“借位”操作，从而确保叶子节点保持平衡。
合并或借位的操作会沿着树的路径向上进行，直到合适的位置或者根节点。此时如果根节点被合并，树的高度会减少。

改，更新操作包括删除和插入两个步骤。对于 B+ 树中的更新操作，首先查找要更新的记录，然后删除旧的数据（这会触发删除过程），最后插入更新后的数据（这会触发插入过程）。由于 B+树的插入和删除本身就会触发节点的分裂和合并，因此更新操作的过程也是通过插入和删除来调整树的结构。

在 B树中，增、删、改、查数据时的调整过程与 B+树 有相似之处，但也有一些关键的区别。B树和B+树的主要区别在于 B树的内部节点也存储数据，而 B+树的内部节点只存储索引。

查，查询操作在 B树中的基本过程如下：

与 B+ 树类似，B树在进行查询时，从根节点开始，逐层比较，判断应该进入哪个子节点，直到最终找到目标数据或者确定数据不存在。
B树的每个节点都存储键值，并且每个节点内可能有多个子节点。查询时会与节点内的键值进行比较，并选择合适的子节点继续查找。每个节点的键值都是有序的，查找效率很高。根据查询的键值，比较该键值与当前节点内的键值，并决定向左、右子树或者中间子树递归查找。直到找到叶子节点，叶子节点包含实际数据（数据存储在叶子节点和内部节点中）。

增，在 B树中插入数据时，主要有以下几个步骤：

插入操作首先会根据查询过程查找插入数据的正确位置。找到该位置之后，数据将被插入到目标节点的适当位置。由于 B 树的节点是有序的，插入的数据会保持该节点内的键值有序。
如果目标节点内有足够的空间容纳新数据（即节点的键数未达到最大值），则直接将数据插入该节点。
如果插入数据后，节点的键值数量超出了该节点的最大容量（一般是 m 个键），则会触发 节点分裂。将节点内的键值分成两部分，左侧的部分保留在原节点，右侧的部分会移动到新节点。然后，中间的键值会被提升到父节点中。如果父节点也因提升的键值而超出容量，会继续进行 递归分裂，直到找到一个父节点不满的位置。如果分裂一直向上传播到根节点，根节点也会分裂，从而 增加树的高度。
删，在 B树中删除数据时，也需要保证树的平衡性。删除的过程如下：
首先，根据查询操作找到要删除的目标数据的位置。
如果目标数据在叶子节点，并且删除后节点的键数依然满足最小要求（例如，不少于 ⌈m/2⌉ 个键），则直接删除该数据。
如果删除数据后，节点内的键数少于最小要求，则需要合并或借位来恢复平衡。
- 借位：如果相邻的兄弟节点有足够的键值，可以借一个键值过来填补当前节点。
- 合并：如果相邻的兄弟节点无法借位，当前节点与相邻的兄弟节点会合并。合并时，父节点中与当前节点相关的键值也会被删除，可能会导致父节点的键数不足。
如果父节点因为删除键值而导致其键数也不足，需要对父节点进行借位或合并操作。这个调整过程会递归向上进行，直到根节点。
如果根节点的子节点减少到 1（即合并后只剩一个子节点），则根节点被删除，树的高度会减少 1。

改，B树中的更新操作本质上是 删除旧数据并插入新数据 的组合过程。步骤如下：

首先，使用查询操作找到要更新的数据的当前位置。
然后，按照删除操作删除旧的数据。如果删除后节点不足，需要进行节点合并或借位的调整。
最后，插入新的数据。插入操作会引发节点分裂（如果节点满了），并可能导致树的高度增加。

行级锁升级为表级锁

在 MySQL 中，行级锁通常由 InnoDB 存储引擎使用，因为它支持高并发和细粒度的锁定。通常情况下，InnoDB 在执行诸如 UPDATE、DELETE 或 SELECT FOR UPDATE 等操作时，会为被修改的数据行加锁（行级锁）。但是，在某些情况下，InnoDB 会将行级锁升级为 表级锁，从而影响并发性能。

锁粒度和事务状态不一致，如果 InnoDB 在执行一个事务时，无法确定是否应该继续使用行级锁，它可能会将行级锁升级为表级锁。通常这种情况发生在 非一致性事务（non-consistent reads） 中，尤其是在 查询过程中可能会修改数据的场景。如果查询会影响到多个行或范围，InnoDB 有时会选择使用表级锁以确保数据的安全性和一致性。

锁竞争导致的表级锁升级，当多个事务争用相同的行时，如果这些事务无法成功地获取锁（因为其他事务已经加了行级锁），InnoDB 可能会将这些行级锁升级为表级锁。这样做是为了防止死锁的发生，因为表级锁避免了对单个行的过度竞争。当事务在处理时涉及大量行，InnoDB 会决定表级锁比行级锁更合适，以减少锁的开销。

索引失效导致无法使用行级锁，当查询中的条件列没有使用索引时，InnoDB 可能无法在特定的行上加锁。由于没有有效的索引，InnoDB 会对整个表加锁（表级锁）来确保数据一致性和完整性。如果查询范围较大，无法高效地在行级别进行锁定，InnoDB 可能会退化为表级锁。

在 LOCK TABLES 语句中手动锁定表，虽然行级锁通常在正常的事务中自动生效，但如果你在事务中显式使用了 LOCK TABLES 语句进行表级锁定，那么 InnoDB 会升级所有对该表的行级锁为表级锁。LOCK TABLES 会直接在表级上加锁，从而使行级锁无法继续应用。

死锁检测和回滚，在 死锁检测 过程中，InnoDB 会对某些事务进行回滚，可能会导致行级锁被升级为表级锁。如果多个事务在争用行级锁时发生死锁，InnoDB 会回滚其中一个事务，并释放相关的锁。在某些情况下，InnoDB 为了避免进一步的死锁，可能会选择释放整个表的锁（表级锁）。

外键约束导致的表级锁，当涉及外键约束时，InnoDB 在执行某些操作（如 DELETE 或 UPDATE）时，可能需要对整个表加锁。这是因为 InnoDB 必须确保外键关系的完整性。虽然行级锁是首选，但如果外键约束的操作涉及到多个表的更新，InnoDB 可能会升级为表级锁来确保事务的一致性。

临时表的使用，当查询需要使用临时表时，InnoDB 会在临时表的存储上加锁。如果临时表涉及大量数据，InnoDB 可能会选择表级锁，而不是行级锁，因为临时表的内容是有限的。

MyISAM vs InnoDB

分类	InnoDB	MyISAM
✅ 默认引擎	✅ 是（MySQL 5.5 之后默认）	❌ 否（MySQL 5.5 之前默认）
📦 存储结构	表结构 `.frm` + 数据+索引 `.ibd` 或共享表空间	`.frm` + `.MYD`（数据）+ `.MYI`（索引）
📌 索引类型	聚簇索引（主键索引的叶子节点存整行数据）	非聚簇索引（叶子节点存物理地址）
🔐 锁机制	行级锁 + 表级锁 + 间隙锁（支持事务隔离）	表级锁（写锁会阻塞所有读）
🔄 事务支持	✅ 支持事务、回滚、崩溃恢复	❌ 不支持事务
💾 崩溃恢复	✅ 支持 crash-safe（redo log + undo log）	❌ 崩溃容易导致数据损坏
🚦 并发性能	高（行级锁 + MVCC）	低（写时锁整个表）
🧠 MVCC 支持	✅ 支持	❌ 不支持
🔍 全文索引	✅ 从 MySQL 5.6 开始支持	✅ 早期支持（MySQL 5.5 及以前）
🔑 外键	✅ 支持	❌ 不支持
📊 适合场景	写多读多、有事务要求、强一致性	读多写少、对事务不敏感、只读数据
⚠️ 幻读控制	✅ 有隔离级别控制（如 RR）	❌ 不支持，不能避免幻读
📈 查询速度	二级索引需要回表查询	读取索引后直接定位数据文件，纯读速度快
🛠️ 工具兼容	✅ mydumper、xtrabackup 等全面支持	❌ 一些工具不兼容，如 xtrabackup 不支持备份

“MyISAM 索引和数据分离，而 InnoDB 索引和数据在一起”

MyISAM 的索引是典型的 B+ 树结构
叶子节点存储的是数据的物理地址（指针），不是数据本身
查数据过程是“两步走”：
1. 先查索引定位数据文件的位置
2. 再去 .MYD 文件里根据地址把数据读出来。

两阶段提交

在 MySQL 中，事务提交时需要把事务操作写入 InnoDB 的 redo log，把操作写入 binlog（用于主从复制、数据恢复），要保证这两个步骤一致，MySQL 使用了 两阶段提交协议。

✅ 第一阶段：准备阶段（Prepare Phase）
InnoDB 引擎将事务的修改写入 redo log，并写入 prepare 状态（此时 redo log 并未提交）。如果写入 redo log 成功，则继续。

✅ 第二阶段：提交阶段（Commit Phase）
MySQL Server 层将事务写入 binlog。如果写入成功，通知 InnoDB 提交 redo log（即将 redo log 状态从 prepare 改为 commit）。如果写入 binlog 失败，则回滚事务，并让 InnoDB 回滚 redo log。

之所以需要两阶段提交，是因为：InnoDB 的 redo log 和 binlog 是 两个独立的系统。必须保证 两者一致，否则在 crash 恢复或者主从复制时可能出现数据不一致。

例如：redo log 已经提交（物理上持久化），但 binlog 写入失败（主从复制数据就丢了），这就会导致数据不一致。即：

InnoDB 的事务操作已经写入了 redo log 并提交（事务在本地已经真正生效了）。
但是 binlog（用于主从复制和 crash 恢复）没有成功写入磁盘或写失败。

这样会：

主库上事务已经生效（数据已经更新）
binlog 没有记录这个操作
从库在复制时不会收到这个事务的数据
➜ 导致主库和从库的数据出现不一致❗️

数据主键类型不一样导致数据插入速度快慢问题

在 MySQL 数据库中，主键是自增的 BIGINT 类型 和 主键是 UUID 字符串类型 的情况下，插入数据的速度通常是不一样的，并且 BIGINT 类型通常比 UUID 更快。原因如下：

索引结构的影响，MySQL 的 InnoDB 存储引擎使用 B+ 树索引 作为主键索引。

自增 BIGINT（顺序插入），自增主键是单调递增的，新插入的记录会顺序追加到 B+ 树的叶子节点末尾，索引结构相对稳定。由于数据总是往后追加，几乎不会导致页分裂（page split），插入性能更高。
UUID 是随机分布的，新插入的记录可能落在索引树的不同位置，导致频繁的 页分裂 和 页面重组。频繁的索引调整会导致更多的磁盘 I/O 操作，从而降低插入速度。

数据存储的影响

BIGINT 占用 8 字节，存储紧凑，索引占用的空间较小，数据查询效率更高。
UUID（通常是 VARCHAR(36) 或 BINARY(16)），如果是 VARCHAR(36)，占用 36 字节，会使索引更大，影响查询和插入效率。如果使用 BINARY(16) 存储，会稍微优化存储效率，但仍然比 BIGINT 大。

BIGINT 更小，索引页能容纳更多的索引项，减少磁盘 I/O，提高查询和插入速度。UUID 更大，占用更多的索引页，导致数据库缓存命中率下降，从而影响整体性能。

并发场景

在高并发场景下，使用自增 BIGINT 可能会带来主键争用（INSERT 争用），但 MySQL 5.7+ 的 innodb_autoinc_lock_mode=2 可以缓解这个问题。
UUID 没有自增 ID 的锁争用问题，但由于随机性高，插入速度仍然较慢。

innodb_autoinc_lock_mode=2 是 MySQL InnoDB 存储引擎中的一个自增锁模式，它可以优化并发插入时的性能。innodb_autoinc_lock_mode 控制 InnoDB 在插入数据时如何处理自增（AUTO_INCREMENT）锁，主要影响并发插入的性能和一致性。它有 0、1、2 三种模式。

取值	模式名称	作用
`0`	传统模式（“traditional”）	使用表级 AUTO-INC 锁，严格保证自增 ID 连续，但并发性能较差。
`1`	交错模式（“consecutive”） (默认模式)	使用轻量级的 AUTO-INC 锁，支持批量插入并提高并发性能。
`2`	轻量级模式（“interleaved”）	不使用 AUTO-INC 锁，使用事务内部的自增分配机制，极大提高并发性能，但 ID 可能不连续。

MVCC 版本控制机制原理

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种数据库事务并发控制机制，主要用于提高数据库的读写性能。它通过维护数据的多个版本，使得读操作无需加锁，同时保证一致性，减少了事务之间的阻塞。

在 MySQL 的 InnoDB 存储引擎中，MVCC 主要用于**可重复读（REPEATABLE READ）和读已提交（READ COMMITTED）**这两种事务隔离级别。

MVCC 通过隐藏删除和修改的行，加上一些额外的信息来实现多版本控制。主要依赖于 UNDO日志 和 事务 ID（Transaction ID）。

InnoDB 的数据行结构中包含两个隐藏的字段：

trx_id（事务 ID）：表示最近对该行进行修改的事务 ID。
roll_pointer（回滚指针）：指向该行的 旧版本（即 undo log 记录），从而支持回滚和版本链。

当数据被修改时：

更新（UPDATE）：不会直接修改数据，而是将旧版本保存到 undo log，然后生成新的数据版本，并更新 trx_id。
删除（DELETE）：不会立即删除，而是生成一个新版本，标记该行已删除，并记录 undo log。
插入（INSERT）：只插入最新版本的数据，不会产生历史版本（因此插入数据不受 MVCC 影响）。但插入的数据版本会被 MVCC 管理，从而影响其他事务的可见性。

Undo Log 主要用于回滚事务，形成一个版本链，事务可以基于 trx_id 获取合适的旧版本数据，而不会影响其他事务。

当一个事务读取数据时，它需要判断哪些数据版本对自己可见。InnoDB 通过 Read View（读取视图）来管理可见性。

数据版本的 (trx_id) < Read View 的最小活跃事务 ID (min_trx_id)：该数据版本已经提交，对当前事务可见。
数据版本的 (trx_id) >= Read View 的最大活跃事务 ID (max_trx_id)：该数据版本是在当前事务之后创建的，不可见。
数据版本的 (trx_id) 介于 min_trx_id 和 max_trx_id 之间：
- 若 trx_id 属于活跃事务列表，则表示该事务还未提交，不可见。
- 若 trx_id 不在活跃事务列表中，则可见。

MVCC 在不同事务隔离级别下的表现

隔离级别	MVCC 读取的版本
读已提交（Read Committed）	每次 `SELECT` 都创建新的 Read View，读取最近提交的数据版本
可重复读（Repeatable Read）	事务开始时创建 Read View，整个事务期间保持一致
串行化（Serializable）	不使用 MVCC，需要加锁
未提交读（Read Uncommitted）	不使用 MVCC，而是直接读取最新的数据版本，因此可能会读取到未提交的数据（脏读）

MVCC 适用于读多写少的场景，对于高并发写入，可能需要结合锁机制或优化索引来提升性能。

Read View
当事务在 READ COMMITTED 或 REPEATABLE READ 隔离级别下执行 SELECT 语句时，InnoDB 不会直接读取最新的数据版本，而是通过 Read View 来决定 哪个数据版本对当前事务可见。它主要包含：

trx_id：每个事务都有一个唯一递增的事务 ID，越新的事务 ID 值越大。
m_ids（活跃事务列表）：当 Read View 生成时，当前正在执行但未提交的事务 ID 列表。
min_trx_id（最小活跃事务 ID）：m_ids 中最小的事务 ID。
max_trx_id（下一个将要分配的事务 ID）：比当前所有活跃事务 ID 都大的值，代表未来新事务的起始 ID。

REPEATABLE READ 级别的 Read View 确保了事务期间看到的行数据不会随其他事务的提交而变化，但对 INSERT 仍然可能出现幻读（需借助 Next-Key Lock 解决）。

死锁产生条件

多个事务同时锁定相同的资源（循环等待）

当两个或多个事务以不同的顺序锁定相同的资源，并相互等待对方释放锁时，就会发生死锁。

-- 事务 A
START TRANSACTION;
UPDATE table1 SET column1 = 'value1' WHERE id = 1;  -- 锁住 id = 1
UPDATE table1 SET column1 = 'value2' WHERE id = 2;  -- 等待事务 B 释放 id = 2

-- 事务 B
START TRANSACTION;
UPDATE table1 SET column1 = 'value2' WHERE id = 2;  -- 锁住 id = 2
UPDATE table1 SET column1 = 'value1' WHERE id = 1;  -- 等待事务 A 释放 id = 1

由于事务 A 和事务 B 互相等待对方释放锁，造成死锁。MySQL 会自动检测死锁，并回滚其中一个事务，释放其占有的锁，以使另一个事务得以继续执行。

索引不合理导致的意外行锁升级

在 InnoDB 存储引擎下，行锁是基于索引 的，而不是基于物理行。如果 SQL 语句未使用索引，会导致表锁，进而增加死锁的可能性。
```
-- 假设 `column1` 没有索引
SELECT * FROM table1 WHERE column1 = 'some_value' FOR UPDATE;
```
由于 column1 没有索引，InnoDB 会对整张表加锁，可能导致多个事务争抢表锁，增加死锁风险。

外键约束导致的隐式锁

由于 外键约束 可能导致级联更新/删除操作，在并发事务中可能出现死锁。

-- 假设 child_table 具有外键约束
DELETE FROM parent_table WHERE id = 1;
DELETE FROM parent_table WHERE id = 2;

由于外键约束，删除 parent_table 的数据会影响 child_table，多个事务并发执行时，可能导致死锁。

间隙锁（Next-Key Lock）

在 REPEATABLE READ 隔离级别下，InnoDB 可能会对索引范围执行 间隙锁（Gap Lock），导致多个事务在并发插入或更新时出现死锁。
```
SELECT * FROM users WHERE age BETWEEN 18 AND 25 FOR UPDATE;
```
如果多个事务同时查询 18~25 之间的数据并尝试更新，就可能导致死锁。

解决 MySQL 死锁

使用索引，确保 WHERE 条件涉及索引，避免表锁导致的死锁。
控制事务顺序，保证多个事务按相同的顺序访问数据，减少死锁发生的概率。
减少事务持有锁的时间，使用 短事务，减少锁持有时间。
监控死锁并进行重试，通过 SHOW ENGINE INNODB STATUS\G 查看死锁信息。

事务隔离级别

读未提交（READ UNCOMMITTED）
- 最低的隔离级别，允许事务读取其他事务未提交的数据（脏读）。
- 存在脏读、不可重复读 和幻读问题。
读已提交（READ COMMITTED）
- 事务只能读取其他事务已经提交的数据，避免了脏读，但仍然可能会出现 不可重复读 问题。
- 在同一个事务中，读取同一数据可能会得到不同的值。
可重复读（REPEATABLE READ）
- 事务在其生命周期内多次读取相同数据时，结果保持一致，避免了 不可重复读 问题。
- 但仍然可能会出现幻读问题。
- MySQL 默认的隔离级别。
序列化（SERIALIZABLE）
- 最高的隔离级别，强制所有事务串行执行，避免了脏读、不可重复读 和幻读。
- 为了确保事务串行执行，会通过加锁机制（如行级锁、表级锁等）避免并发。
- 性能较差，适用于对数据一致性要求极高的场景。

脏读（Dirty Read）

一个事务读取到另一个事务未提交的数据。
当事务 A 读取了事务 B 正在修改但尚未提交的数据，并且事务 B 之后发生回滚时，事务 A 读取到的数据是无效的。
脏读会导致事务 A 基于无效数据进行计算、决策，可能造成数据不一致。

不可重复读（Non-repeatable Read）

在同一事务中，读取同一数据的两次查询结果不同。
事务 A 第一次读取数据后，事务 B 对同一数据进行了修改并提交。事务 A 再次读取数据时，得到的值与第一次读取时不同。
不可重复读会导致同一事务内的查询结果不一致，影响事务的逻辑判断。

幻读（Phantom Read）

一个事务读取到的记录集合，在其生命周期内发生了变化。
事务 A 在执行查询时，读取到一组符合条件的数据。随后，事务 B 插入、删除或修改了符合条件的数据。当事务 A 重新执行查询时，返回的数据集发生了变化（例如，原本没有某条数据，现在有了，或者原本有的数据，现在没有了）。
幻读会导致事务在读取数据时，查询结果不稳定，可能影响计算结果或业务逻辑。

MySQL 的 InnoDB 存储引擎在 REPEATABLE READ 级别下，默认使用 MVCC（多版本并发控制） 来实现这一特性，使得即使 T2 提交了事务，T1 仍然可以读到事务开始时的快照数据，而不是最新提交的数据。

如果数据库的事务隔离级别设置为 序列化（SERIALIZABLE），那么 T1 和 T2 不能同时访问相同的数据行，因为 SERIALIZABLE 通过加锁 使事务 串行执行，从而避免所有并发事务导致的数据不一致问题。

T1 开始事务，并读取数据，获取值 A。
T2 尝试修改数据，想将 A 修改为 B：
- 由于 SERIALIZABLE 级别会对读取的数据加“共享锁”（S锁），直到 T1 提交事务后才释放锁。
- T2 需要修改这条数据，因此会尝试获取“排他锁”（X锁），但由于 T1 还未提交，锁没有释放，T2 必须等待。
T1 再次读取数据，仍然是 A。
T1 提交事务后，T2 才能获取锁，修改数据为 B，并提交。

在 MySQL 的 InnoDB 存储引擎下，SERIALIZABLE 通过行级锁或间隙锁（Next-Key Lock）来实现，从而避免并发事务的冲突。
由于 SERIALIZABLE 级别会降低并发性能，通常只在银行、财务等高一致性要求的场景下使用，日常业务中更常用 REPEATABLE READ 或 READ COMMITTED。

delete、truncate、drop

Delete：属于DML、可回滚、表结构还在，删除表的全部或者一部分数据行、删除速度慢，需要逐行删除。
Truncate：属于DDL、不可回滚、表结构还在，删除表中的所有数据、删除速度快。
Drop：属于DDL、不可回滚、从数据库中删除表，所有的数据行，索引和权限也会被删除、删除速度最快。

ROW_ID vs 主键

在 MySQL 中，ROW_ID 是 InnoDB 存储引擎用来唯一标识每一行记录的内部标识符。每个表都有自己独立的 ROW_ID。这意味着每个没有定义主键的表都会有自己的 ROW_ID，并不与其他表共享。虽然 MySQL 用户通常不会直接操作 ROW_ID，但它在行存储和索引中起着重要作用。

唯一性：ROW_ID 在 InnoDB 中是唯一的，确保每一行可以被唯一识别。
隐式使用：对于没有主键的表，InnoDB 会自动创建一个隐式的 ROW_ID，用于行的存储和检索。
性能：通过使用 ROW_ID，InnoDB 可以高效地管理行的插入、更新和删除操作。

在 MySQL 中，ROW_ID 一旦达到其数据类型的最大值，不会自动从 0 开始重新计数。相反，插入新行时会出现错误，表明无法插入数据。此时应采取措施，例如添加主键、归档旧数据或重建表，以避免达到极限。因此，确保设计良好的表结构和定期监控数据使用情况是必要的，以防止 ROW_ID 达到最大值。

自增属性：自增列会根据当前最大值生成下一个值。
重启后状态：如果重启前自增列的最大值为 100，重启后下一个插入的值仍然是 101。
数据持久性：自增计数器的信息存储在数据库内部，因此重启不会影响其状态。
手动更改：如果手动修改自增列的值（例如，通过 ALTER TABLE），可能会影响后续插入的自增值。
删除记录：如果删除了行，插入新行时不会使用被删除行的自增值，而是继续使用下一个自增值。

锁

在 MySQL 中，锁是用来管理并发操作的机制，以确保数据库的一致性、隔离性和并发性。MySQL 支持多种类型的锁，主要取决于所使用的存储引擎（如 InnoDB 和 MyISAM）以及锁的粒度和作用对象。主要锁类型有：

共享锁（S锁，Shared Lock）

共享锁允许一个事务读取数据，但不允许其他事务修改该数据。多个事务可以同时持有共享锁，只要它们只是读取数据。
当一个事务需要读取某行数据，并且希望阻止其他事务修改该行数据时，使用共享锁。
例如：在 SELECT 查询时，如果使用了 LOCK IN SHARE MODE，就会对查询的记录加上共享锁。
```
SELECT * FROM employees WHERE id = 1 LOCK IN SHARE MODE;
```

排他锁（X锁，Exclusive Lock）

排他锁会阻止其他事务对该数据行进行任何形式的读取或修改操作。一个事务持有排他锁时，其他事务不能获得该行的共享锁或排他锁。排他锁不仅会锁定数据的读取，还会锁定数据的修改。
当一个事务需要修改某行数据时，需要使用排他锁，确保该数据在其他事务中不会被访问或修改。
例如：在 UPDATE、DELETE 操作时，InnoDB 会自动为被修改的数据加上排他锁。
```
UPDATE employees SET name = 'Alice' WHERE id = 1;
```

自增锁（AUTO-INC Lock）

自增锁是一种特殊的锁类型，用于处理 AUTO_INCREMENT 列的并发操作。自增锁确保多个事务对自动递增字段的写操作是串行的，防止数据冲突。
当多个事务同时插入数据时，涉及 AUTO_INCREMENT 列的操作会加上自增锁，确保每个插入操作都能正确生成一个唯一的自增值。
例如：在插入新记录时，涉及自增列时会自动加上自增锁。
```
INSERT INTO employees (name, age) VALUES ('Alice', 30);
```

表锁（Table Lock）

表锁是对整个表加锁，意味着在某个事务执行时，整个表都被锁定，其他事务无法访问该表。表锁是比较粗粒度的锁，通常会导致较低的并发性能。
适用于表级操作，如在 MyISAM 存储引擎中进行查询时，通常使用表锁。
例如：LOCK TABLES 可以显式地加表锁。
```
LOCK TABLE employees WRITE;
```

行级锁（Row Lock）

行级锁是一种较细粒度的锁，它只对表中的某一行数据加锁。与表锁相比，行级锁允许更多的并发操作，因为不同的事务可以对表中的不同数据行加锁，避免了表级锁的冲突。
行级锁是 InnoDB 存储引擎使用的默认锁类型，适用于需要频繁读取和修改不同数据行的高并发环境。
例如：UPDATE 或 DELETE 查询会在修改的行上加上行级锁。
```
UPDATE employees SET age = 35 WHERE id = 1;
```

意向锁（Intention Lock）

意向锁是为了保证行级锁和表级锁的兼容性而设计的一种锁。意向锁并不直接加锁数据，而是表明事务对某些行或者整个表有意图加锁。
意向共享锁（IS，Intention Shared）：表示事务希望对某些行加共享锁。
意向排他锁（IX，Intention Exclusive）：表示事务希望对某些行加排他锁。
意向锁用于优化对表的加锁策略。它主要用于 InnoDB 存储引擎，在执行表级锁操作时，帮助 MySQL 判断是否能加行级锁。
例如：当你查询某些行时，MySQL 会在表级别添加意向锁，以确保其他事务能理解事务对行的锁定意图。

乐观锁与悲观锁

悲观锁是假设并发冲突很严重，因此在访问数据时采取严格的加锁策略，以保证数据的一致性。例如，使用 SELECT FOR UPDATE 来加排他锁。
乐观锁是假设并发冲突较少，通常通过在数据上设置版本号或时间戳来实现数据的并发访问，避免使用锁。常见的乐观锁实现方式是基于 version 字段 或 timestamp 字段。

调优

MySQL 调优是提高数据库性能的一个关键过程，主要目的是通过配置、优化查询、调整硬件资源等方面来提高 MySQL 数据库的响应速度和并发处理能力。MySQL 调优可以分为多个方面，主要包括硬件调优、配置调优、查询调优和索引调优等。

硬件调优
硬件资源对 MySQL 的性能有直接影响，尤其是内存、存储设备（如磁盘和 SSD）以及 CPU。在硬件方面进行适当的调优，可以显著提高 MySQL 的性能。

确保系统有足够的内存供 MySQL 使用。MySQL 性能的一个重要方面是 InnoDB Buffer Pool，它存储了大部分数据。
使用 SSD 替代传统机械硬盘可以显著提高磁盘 I/O 性能，特别是在事务日志、数据库文件和临时表的读取/写入操作中。
多核 CPU 会对高并发查询有帮助，但 MySQL 的单线程性能也很重要，尤其是对于某些查询操作。确保 CPU 资源能够满足高并发请求。

配置调优
MySQL 提供了大量的配置参数，可以调整数据库的性能。

innodb_buffer_pool_size：设置 InnoDB 存储引擎的缓存大小，通常应设置为物理内存的 60-80%。这可以大大提高查询性能。
innodb_log_buffer_size：设置事务日志缓冲区大小。如果这个缓冲区过小，MySQL 在写入日志时会频繁地与磁盘交互，导致性能下降。
innodb_flush_log_at_trx_commit：控制事务日志刷新策略，1 表示每次事务提交时都会将日志写入磁盘，这对数据安全有保障，但会增加 I/O。设置为 2 会减少 I/O，但牺牲数据安全性。
innodb_file_per_table：启用每表单独存储文件，而不是所有数据都存储在一个共享表空间中。这样可以避免表空间的碎片化。
query_cache_size：查询缓存用于缓存 SELECT 查询的结果集，以便相同查询可以直接从缓存中获取。这适用于读多写少的场景。但在高并发的写操作下，查询缓存会造成锁竞争，因此在高写负载环境下不建议启用。
query_cache_type：配置查询缓存的使用方式。推荐将其设置为 DEMAND 或 OFF，特别是在高写负载的系统中。
max_connections：设置允许的最大连接数。如果设置得过低，可能导致大量连接请求被拒绝；如果设置得过高，会导致系统资源过度消耗。
thread_cache_size：控制 MySQL 连接线程缓存的大小，可以减少线程创建和销毁的开销，提高性能。
tmp_table_size 和 max_heap_table_size：控制临时表的最大内存使用量。如果临时表的大小超过这个限制，MySQL 会将其写入磁盘，导致性能下降。
sort_buffer_size：设置排序操作所使用的内存缓冲区大小。适当增加此值可以加速排序操作，但也会消耗更多内存。

查询调优
查询优化是 MySQL 调优中的核心部分。优化查询语句可以减少数据库的负担，提高查询效率。

尽量避免使用 SELECT *，只查询实际需要的字段，这样可以减少 I/O 和内存消耗。
确保查询使用了合适的索引，以避免全表扫描。
使用 EXPLAIN 来分析查询执行计划，找出查询瓶颈，优化查询结构。
复合索引：如果查询包含多个条件，使用复合索引（覆盖多个列的索引）可以有效提高查询速度。
避免 N+1 查询问题：N+1 查询是指在循环中执行大量的查询，导致大量的数据库访问，应该尽量避免这种查询模式。
尽量避免在查询中使用复杂的子查询，特别是嵌套子查询，尝试用 JOIN 来替代。
优化 JOIN 查询的顺序，通常在 WHERE 子句中限制数据量较大的表。
对于大数据量的查询，使用 LIMIT 限制返回的行数。

索引调优
索引是提高查询性能的关键。在 MySQL 中，索引可以大幅度提高检索速度，但如果使用不当，也会造成性能问题。

为常用的查询条件字段（如 WHERE 子句中的字段、JOIN 操作中的连接条件字段）添加索引。
对于需要排序或分组的字段（ORDER BY 和 GROUP BY），可以考虑创建索引来加速这些操作。
使用 覆盖索引：如果查询只涉及索引中的列，可以完全通过索引获取数据，而不需要回表查询，从而提高查询速度。
避免过多的索引：过多的索引会影响写操作的性能，因为每次插入、更新、删除数据时，MySQL 都需要更新相关的索引。
BTREE 索引：适用于范围查询、排序、比较等操作。
HASH 索引：适用于精确查找，但不适用于范围查询和排序。
定期优化索引：随着数据量的增长，表中的数据和索引可能会发生碎片化，定期执行 OPTIMIZE TABLE 可以帮助重建索引并提高性能。

数据库表优化
优化数据库表的结构和设计可以提高查询和数据处理的效率。

避免使用过长的字段类型，例如使用 VARCHAR(255) 可能会浪费存储空间，应该根据实际需求设置适当的字段长度。
使用合适的字段类型，尽量选择 INT 而不是 BIGINT，使用 CHAR 而不是 VARCHAR 等。
对于非常大的表，考虑使用 分区表。分区可以通过按照某些规则将数据分布到多个物理存储区域中，从而提高查询性能。
水平分表（sharding）也可以用于将数据分散到多个表或数据库实例中，从而提高扩展性。
InnoDB 存储引擎支持 表压缩，可以在磁盘上节省空间并提高查询性能，特别是在存储大量历史数据时。

其他高级优化

虽然 MySQL 支持查询缓存（query_cache），但在高并发环境中，它的效率可能较低，甚至会成为瓶颈。在这种情况下，可以考虑禁用查询缓存，或使用其他缓存机制（如 Redis、Memcached）来减少数据库负载。
使用连接池可以减少频繁建立和销毁数据库连接的开销，特别是在 Web 应用中，可以有效提高并发性能。
分析 慢查询日志，找出执行时间较长的查询，并优化它们。使用 Percona Toolkit 等工具可以帮助监控和优化查询性能。

主从复制

MySQL 主从复制（Master-Slave Replication）是一种常用的数据同步机制，用于实现数据库的高可用性、负载均衡以及读写分离等目标。其原理如下：

主库（Master）开启二进制日志（Binary Log），主库会记录所有写操作（如 INSERT, UPDATE, DELETE）到 binlog 中。
从库（Slave）连接主库，拉取 binlog，从库启动一个 I/O 线程，连接主库，读取 binlog，并将其保存为中继日志（relay log）。
从库的 SQL 线程 解析中继日志并执行对应操作，达到数据同步。

📊 MySQL 主从复制的三种模式对比

复制模式	同步机制	数据一致性保障	性能表现	使用场景
异步复制	主库写完 binlog 后立即返回客户端	❌ 可能存在数据延迟和丢失	🚀 最快	写性能优先、不严格要求一致性的系统
半同步复制	主库等待至少一个从库确认接收 binlog 后返回	✅ 降低数据丢失概率	⚖️ 中等	多数业务系统，需一定一致性保障
全同步复制（组复制）	主库等待所有从库确认接收 binlog 才返回	✅✅ 一致性最强	🐢 最慢	金融、支付等极度敏感、强一致性要求场景

在生产环境中，多数系统使用 异步 + 半同步切换机制（即“增强半同步”）来折中性能与一致性。
全同步通常不是单机主从场景，而是用于多主复制或分布式集群架构。

分区

MySQL 分区（Partitioning）是一种将一个大表拆分成多个更小、更易管理部分（分区）的技术，逻辑上是一张表，物理上是多个子表，可以显著提高大表的查询、管理和维护效率。

“MySQL 分区可以跨磁盘，但不能跨数据节点。”

MySQL 的内置 分区机制是“单实例”内的逻辑分区，不是分布式数据库。
所有分区都由同一个 MySQL 实例进程 来管理和访问，所有数据都在 一个实例的物理机器 上。
无法像 分布式数据库（如 TiDB、CockroachDB、Citus） 那样自动把分区数据分散在多个节点上。

如果确实有需要将分区分布在不同机器或实例上：

✅ 可以考虑使用 分布式数据库：如 TiDB、Vitess、Citus（PostgreSQL扩展）
✅ 或使用 中间件 做水平分表（Sharding）：如 ShardingSphere、MyCat、Vitess

MySQL 分区的几种类型（重点）

类型	描述
RANGE	按范围分区，常用于时间字段，如 `date < '2024-01-01'`
LIST	按列表值分区，例如国家、地区等
HASH	按哈希值分区，适用于均匀分布的数据，如用户ID
KEY	类似 HASH，但使用 MySQL 自己的哈希函数（支持复合主键）

注意事项

注意点	说明
❌ 不支持外键	分区表不支持外键约束
⚠️ 分区键必须包含在主键中	否则会报错
🚫 不支持部分存储引擎	分区仅支持 `InnoDB` 和 `MyISAM`
🔄 分区不能动态改变	变更分区需要重建表或用 `EXCHANGE PARTITION`
🔥 不能用分区代替索引	分区是优化大表的策略，不是万能提速手段

分库分表

分库是把数据拆分到 多个数据库实例（可能在不同服务器上），解决的是：单库容量瓶颈、连接数限制、单机性能问题。

分表（Table Sharding）是把一张大表按某个规则拆成多张结构相同的小表，解决的是：单表行数过大、索引膨胀、查询慢。

常见分库分表方式

类型	描述
水平拆分	将同一张表的数据按行拆分到多个库/表中（最常见，如按用户ID取模）
垂直拆分（字段）	按表结构字段拆分，如把用户表和用户扩展表分离
垂直拆分（模块）	按业务模块拆分库，如订单库、用户库、商品库

分库分表后同库同表可用原生事务，跨库事务需用分布式事务（如 XA、TCC）或最终一致性模型。

范式化 vs 反范式化

范式化 是一种通过合理拆分表结构、消除数据冗余、保证数据一致性的设计思想。

常见的三种范式：

范式	要求	举例说明
第一范式（1NF）	每一列都是原子值，不能再拆	列中不能存数组、逗号分隔的字符串
第二范式（2NF）	满足1NF，且每个非主属性完全依赖主键	消除对主键“部分依赖”
第三范式（3NF）	满足2NF，且非主属性不传递依赖主键	消除“间接依赖”

第一范式（1NF）：要求每一列的值是原子性的，即不能再进一步拆分。每个字段只能存储一个值，不能有重复的组。例如，用户表的地址字段不能同时存储城市和邮编，应该将地址分成“城市”和“邮编”两个字段。
第二范式（2NF）：在符合第一范式的基础上，要求每个非主键字段都必须完全依赖主键，即没有部分依赖（指某些非主键字段只依赖于主键的一部分）。例如，订单表中的“用户地址”如果只依赖于“用户ID”而不是订单ID，则应该把用户地址移到用户表中。
第三范式（3NF）：在符合第二范式的基础上，要求非主键字段之间不能有传递依赖。也就是说，非主键字段不能依赖于其他非主键字段。例如，如果学生表中包含“学院名”和“学院地址”，那么学院地址依赖于学院名，应该将学院信息提取到一个独立的学院表中。

范式化避免数据冗余、保证数据一致性、数据更新容易维护；但是需要大量 JOIN 查询，性能下降、查询逻辑复杂，开发成本高。

反范式化 是一种适当增加冗余、减少表连接，提升读性能的数据库设计方法，尤其常见于大数据量、高频读场景。

常见反范式化手段：

技术手段	示例
❗合并表	把用户表+地址表合并为一个表
❗冗余字段	存订单表时冗余商品名称、价格
❗预计算字段	存储总金额、平均值等
❗物化视图 / 缓存表	提前生成报表、汇总表

反范式化提高查询性能（减少 JOIN），简化查询逻辑，有利于报表、缓存和分页等场景；但是数据冗余，可能出现数据不一致，写操作变复杂（需更新多个冗余字段），数据维护成本上升。

回表查询

在 MySQL 中，是否在 B+树的叶子节点上存储完整的行数据，取决于使用的存储引擎和索引类型：

聚簇索引 (Clustered Index)

叶子节点包含完整的行数据。
MySQL InnoDB 存储引擎的主键索引（或聚簇索引）。
聚簇索引的叶子节点直接存储了表中的完整数据行。
如果查询条件可以通过聚簇索引找到所需行，则无需回表。

辅助索引 (Secondary Index)

叶子节点不包含完整的行数据。
InnoDB 辅助索引。
辅助索引的叶子节点存储的是索引列的值和主键值（Row ID）。
如果查询需要其他字段，必须通过主键值（Row ID）回到聚簇索引中读取完整的行数据。

MyISAM 存储引擎

叶子节点不包含行数据。
MyISAM 存储引擎（或其他非聚簇存储引擎）。
MyISAM 的 B+树索引（包括主键索引和辅助索引）的叶子节点只存储索引字段值和对应数据的物理地址。
MyISAM 表的索引和数据是分开的，因此查询数据时总是需要通过索引找到物理地址，然后再读取对应的数据。

在 MySQL 中，回表查询是指查询执行过程中，通过索引找到所需数据行的指针（也称为 Row ID 或 主键值），然后再回到表中（通常是聚簇索引）获取完整的数据行。回表查询通常发生在使用 非覆盖索引 的场景中，即查询的字段不完全被索引覆盖。

使用索引条件（如 WHERE 子句）在辅助索引（Secondary Index）中查找到满足条件的记录。
辅助索引中的记录通常存储的是索引字段值和指向完整数据行的指针（如主键值）。
根据辅助索引中的指针信息，回到聚簇索引或主表中，读取所需的其他列数据。

回表特指从辅助索引查找主键值，再跳转到主键索引获取行数据的过程。
通过主键索引查询行数据不是回表，因为主键索引已经包含行数据，查询无需额外的跳转或操作。

SQL 语句的生命周期

客户端
   ↓
接收请求（连接管理器）
   ↓
查询缓存（MySQL 8.0 之后已废弃）
   ↓
SQL 解析（词法分析、语法分析）
   ↓
查询优化器（生成执行计划）
   ↓
执行器（根据执行计划调度存储引擎）
   ↓
存储引擎（比如 InnoDB，负责数据读写）
   ↓
返回结果

1️⃣ 客户端连接

客户端通过 TCP 协议连接 MySQL Server
MySQL 的连接管理模块验证权限

2️⃣ 查询缓存（已废弃）

MySQL 8.0 已经移除查询缓存，因为它在并发场景下反而拖慢性能
如果是 MySQL 5.7 及以下，有可能命中查询缓存，直接返回结果

3️⃣ 语法解析（Parser）

将 SQL 语句进行 词法分析 和 语法分析
例如 SELECT * FROM users WHERE id = 1 会被分解成语法树（AST）

4️⃣ 查询优化器（Optimizer）

优化器决定：
- 使用哪个索引？
- 执行顺序如何？
- 是走全表扫描还是范围查询？
生成一个最优的执行计划（不是最完美，但通常够用）

5️⃣ 执行器（Executor）

执行器根据优化器给出的计划逐步执行 SQL
它会调用存储引擎提供的 API 来取数据

6️⃣ 存储引擎层（InnoDB 等）

实际执行数据操作（比如读取页、修改 row）
处理事务（redo log, undo log）
管理数据页的缓存（Buffer Pool）

7️⃣ 返回结果

执行器拿到结果后，组装成返回格式
返回给客户端

和 Redis 数据一致性问题

Redis 和 MySQL 的数据很难直接实现 强一致性，但可以通过一些策略尽量接近或实现 最终一致性。因为

两者的数据更新机制不同
- Redis 的数据更新非常快，但可能异步刷盘或存在短暂的数据丢失风险。数据更新通常是异步传播，存在瞬时不一致，且默认不是事务性强一致的。
- MySQL 保证数据的事务性写入，保证数据的强一致性，但性能相对 Redis 较慢。
分布式 CAP 理论限制
- Redis 和 MySQL 在不同的系统中，相当于分布式场景。根据 CAP 理论：
- 如果优先保证高可用性和分区容错性，就难以完全保证一致性。
- Redis 通常更注重性能和高可用性。
数据的写入顺序问题
- 如果数据先写入 Redis 再写入 MySQL，或反之，可能因网络延迟、宕机等问题导致数据不一致。

🎯 几种常用策略来保证 MySQL 与 Redis 的数据一致性

先删除缓存，后更新数据库（推荐 ✅）
- 适合：最终一致性容忍的系统。
- 优点：更新成功后即使并发读也不会读到旧数据。
- 缺点：更新数据库失败，缓存已被删，可能导致缓存穿透。
先更新数据库，后延迟删除缓存（推荐 ✅）
- 避免并发写 + 并发读时缓存数据“脏读”的问题。
- 可结合消息队列异步延迟删除缓存。
- 比第一种方案稍安全，但需处理好延迟期间的并发问题。

延迟的目的是为了应对一种并发场景下的数据不一致问题，延迟时间视具体情况而定

10ms ~ 500ms 是常见的延迟区间。
如果你系统写入 QPS 不高，延迟 50ms 左右就足够了。
对于高并发系统，可以通过压力测试评估最小能保证一致性的延迟值。

把延迟时间做成配置项，可以按实际负载灵活调整。

也可以基于负载自适应（进阶）通过观察系统中的并发程度、缓存命中率，动态调整延时（如高并发时延迟增加，低并发时减少），这就需要接入监控系统。

高并发时延迟删除缓存的延迟时间要增加，因为：

高并发下，线程 A 和线程 B 的“撞车”概率更大；
请求越密集，发生“读到旧数据 + 写回缓存”的机会越多；
所以需要加长延迟时间，让“危险窗口”彻底过去，再删缓存，确保脏数据不再写回。

低并发时可以“缩短”延迟是因为：

并发少，线程 A/B 几乎不会“撞车”；
没必要延迟太久浪费资源（比如 sleep、异步延迟处理）；
小系统可以设 10ms 左右即可满足一致性保障。

相比手动 sleep 延时，更推荐用 消息队列 异步处理删除逻辑，原理：

更新数据库成功；
向 MQ 发送一条“删除缓存”的消息；
消费端延迟 N 毫秒处理该消息，删除缓存。

Redis 和 MySQL 的数据一致性不是 100% 靠某个单点保证的，而是通过一整套机制来控制不一致的时间窗口、发生概率，并做好异常补偿机制。

数据库查询避免深分页问题

使用 cursor 换了一种分页方式，从根本上绕开 offset 带来的性能问题。

传统 page 会有“深分页问题”

SELECT * FROM feed
ORDER BY created_at DESC
LIMIT 20 OFFSET 1000000;

问题在于：

数据库仍然要扫描前 100 万条
再丢弃前面的，只取 20 条

👉 本质是：offset 越大，性能越差（线性退化）

cursor 思路是：
👉 不再跳过数据，而是“从上一次的位置继续查”

比如：

SELECT * FROM feed
WHERE created_at < last_time
ORDER BY created_at DESC
LIMIT 20;

这里的 last_time 就是 cursor

如果用 ID 做游标（更常见）：

SELECT * FROM feed
WHERE id < last_id
ORDER BY id DESC
LIMIT 20;

👉 数据库可以直接走索引，不需要扫描大量数据

关键区别

方式	本质	性能
page + offset	跳过数据	❌ 越翻越慢
cursor	定位数据	✅ 始终稳定

cursor 也有它的局限性
不能随便跳页
不能：

直接跳到第 100 页

👉 只能：

一页一页往下翻

需要稳定排序字段
必须满足：

唯一
单调递增

慢查询原因

导致 MySQL 慢查询（Slow Query）的原因很多，通常可以分成：

SQL 写得不好
索引失效
数据量太大
锁竞争
硬件瓶颈
执行计划异常
参数配置问题

没走索引
这是慢查询第一大原因。
例如：

SELECT * FROM user WHERE name = 'Tom';

如果 name 没索引：

type = ALL

就会：

全表扫描
数据越大越慢

索引失效
即使建了索引，也可能失效。
对索引列做函数操作

SELECT * FROM user
WHERE DATE(create_time) = '2026-05-20';

索引失效。
应改为：

WHERE create_time >= '2026-05-20 00:00:00'
AND create_time < '2026-05-21 00:00:00'

使用 %xxx

WHERE name LIKE '%abc'

前导 % 无法使用 B+Tree 索引。
但：

LIKE 'abc%'

可以走索引。

隐式类型转换
例如：

phone VARCHAR(20)

SQL：

WHERE phone = 13800138000

MySQL 会自动转换类型，导致索引失效。

使用 OR

WHERE age = 18 OR name = 'Tom'

可能导致索引失效。

违反最左前缀原则
联合索引：

(a,b,c)

但查询：

WHERE b = 1

通常无法使用索引。

查询返回数据太多

深分页（经典慢查询）
非常常见。

LIMIT 1000000,20

MySQL 会：

先扫描前100万行
再丢弃

所以越往后越慢。

排序导致慢查询
ORDER BY 未命中索引

ORDER BY create_time

没索引会：

Using filesort

大量磁盘排序。
GROUP BY / DISTINCT
会：

排序
临时表
聚合计算

JOIN 导致慢查询
大表 JOIN 大表

A JOIN B

两边都几百万数据。
会非常慢。

JOIN 字段没索引

ON a.user_id = b.user_id

若 user_id 没索引：

Nested Loop 巨慢

子查询导致慢
例如：

WHERE id IN (
   SELECT user_id FROM order
)

有时优化器执行不好。
可改：

JOIN

锁竞争
即使 SQL 本身不慢，也可能：

等锁
行锁冲突
Gap Lock
MDL锁

导致执行时间很长。

回表太多
例如：
二级索引找到数据后：

再去聚簇索引查整行
数据量大时很慢。

临时表过大
出现：

Using temporary

说明：

内存不够
落磁盘临时表

性能会骤降。

磁盘 IO 瓶颈
数据库慢不一定是 SQL。
可能：

SSD 被打满
随机 IO 高
fsync 频繁

CPU 打满
例如：

大量排序
大量 JOIN
JSON 查询
正则匹配

内存不足
Buffer Pool 太小：

innodb_buffer_pool_size

会导致频繁磁盘读取。

网络问题
有时：

SQL 很快
返回很慢

可能是：

网络延迟
返回数据过大

生产环境最常见的慢查询TOP原因
真实线上最常见：

排名	原因
1	没索引
2	索引失效
3	深分页
4	排序
5	大事务锁等待
6	JOIN没索引
7	查询数据太多
8	热点更新
9	回表
10	Buffer Pool 太小

单表数据量不超过1000万的原因

本质原因：B+Tree 会变“大”
MySQL InnoDB 的索引底层是：

B+Tree

数据越多：

树越高
页越多
IO 越多
缓存命中率下降

最终查询性能开始明显下降。

因为到了千万级：
索引开始变得巨大
这时：

Buffer Pool 很难完全缓存
大量随机 IO 出现

Buffer Pool 放不下了
InnoDB 核心优化依赖：

innodb_buffer_pool_size

如果：

热数据
热索引

无法全部进入内存。

那么：

磁盘随机IO暴增

这是性能拐点。

B+Tree 层级增加
查询 IO 增加。

尤其：

回表
范围查询
排序

更明显。

回表代价越来越大
数据越多：

随机 IO 越严重

分页越来越慢
经典问题：

LIMIT 1000000,20

MySQL 必须：

扫描前100万行
再丢弃

千万级表：

后分页几乎不可接受

排序和临时表成本暴增
例如：

ORDER BY create_time

数据量巨大时：

filesort
temporary table
磁盘排序

都会变慢。

锁冲突会明显增加
数据量大以后：

真正的瓶颈往往不是“数据量”
而是：

问题	更关键
查询模式	YES
索引设计	YES
热点数据	YES
是否回表	YES
是否排序	YES
是否深分页	YES

有些公司几亿数据也没事
因为它们：
使用覆盖索引
避免回表。

使用冷热分离
只查最近数据。

使用分区表
按：

时间
用户
地域

分区。

查询模式简单
例如：

WHERE id = ?

主键查询其实很快。

大内存机器
例如：

Buffer Pool 128GB+

大量数据都在内存。

“单表不要超过1000万”本质上是：

为了避免：
IO失控
缓存失效
DDL灾难
运维风险
锁冲突
分页排序性能崩塌

它是：

工程经验
不是MySQL限制

真正决定性能的核心：

索引设计 + 查询模式 + 热数据大小

而不是单纯“行数”。

AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

项目介绍基于java+vue的十字路口协同通行信号优化系统设计与实现（含模型描述及部分示例代码）专栏近期有大量优惠还请多多点一下关注加油谢谢你的鼓励是我前行的动力谢谢支持加油谢谢

AtomGit开源社区

基于模型预测控制的波浪能转换器（WEC）研究（Matlab代码实现）

本文模拟从波浪能转换器（WEC）中提取的能量，当受控移动窗口阻塞 MPC 时，单设备。它还比较了使用标准MPC和GPC控制时WEC提取的能量。摘要：海浪能是可再生能源最集中的来源之一。然而，到目前为止，它还没有达到商业化所需的经济可行性。为了提高波浪能转换器的效率，已经提出了几种先进的控制策略，包括模型预测控制（MPC）。然而，每个优化问题的计算负担都是传统（全自由度）MPC的缺点，这通常会