写在之前：不建议背书式的去记忆面试题，对技术的提升帮助很小，对正经面试也没什么帮助，准备面试的过程还是要把各个知识点真懂了，然后再连成线。

个人建议把面试题看作是费曼学习法中的回顾、简化的环节，准备面试的时候，跟着题目先自己讲给自己听，看看自己会满意吗，不满意就继续学习这个点，然后调整自己的话术，如此反复，对这块知识也会理解的更到位，而且面试时候也会得心应手，心仪的 offer 肯定会有的。

当然，大家有遇到过什么样『有趣』『有含量』的题目，欢迎提出来，一起学习~

1. 🏗️ 基础与架构：MySQL架构组件、SQL语法（DDL/DML/DCL）、存储原理
2. 🗄️ 存储引擎：InnoDB vs MyISAM特性对比、存储格式、缓冲池机制
3. 🔍 索引机制与优化：B+树原理、索引类型、覆盖索引、联合索引、索引失效场景
4. 🔒 事务与锁机制：ACID特性、隔离级别、死锁处理、MVCC原理、并发控制
5. 📊 数据类型与查询优化：数据类型选择、JOIN优化、子查询、窗口函数、执行计划分析
6. 📝 日志系统：redo log、undo log、binlog机制、WAL原理
7. ⚡ 性能调优：慢查询分析、参数调优、缓存策略、硬件优化
8. 🚀 分库分表与集群：主从复制、读写分离、分片策略、数据迁移、集群部署
9. 💻 SQL实战编程：复杂查询编写、存储过程、触发器、性能优化案例
10. 🖊️ 手撕SQL：经典SQL题目、复杂业务场景查询、算法实现、面试真题
11. 🔧 运维与监控：备份恢复、监控指标、故障排查、容量规划、日常维护

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

• 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

• 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

• 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

• 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

1. 客户端请求：客户端通过连接器发送查询到 MySQL 服务器（验证用户身份，给予权限）
2. 查询接收：连接器接收请求，管理连接
3. 解析器：对 SQL 进行词法分析和语法分析，转换为解析树
4. 优化器：优化器生成执行计划，选择最优索引和连接顺序
5. 查询执行器：执行器执行查询，通过存储引擎接口获取数据
6. 存储引擎：存储引擎检索数据，返回给执行器
7. 返回结果：结果通过连接器返回给客户端

MySQL（InnoDB）的存储结构是分层设计的，数据最终存放在页（Page）里，页是最小的存储和 I/O 单位（16KB）。多个页组成区（Extent，1MB），多个区组成段（Segment），段属于表空间（Tablespace）。每张表对应一个表空间，里面包含数据段和索引段。行数据存放在数据页中，大字段可能存放在溢出页。这样的设计能兼顾存储管理、性能和空间利用。

在 MySQL 中，数据的存储结构从上到下大致分为 库 → 表 → 表空间 → 段（Segment）→ 区（Extent）→ 页（Page）→ 行（Row） 这几个层次。

1. 库（Database）

• 最上层的逻辑组织单位，相当于一个文件夹。
• 在磁盘上表现为一个 目录，里面包含了表对应的文件。

2. 表（Table）

• 每张表就是一组结构化的数据。

3. 表空间（Tablespace）

• 表空间是 InnoDB 的数据存储文件，可以是共享的（ibdata）或独立的（.ibd）。
• 表空间里存放表的所有数据和索引。
• 每个表空间由若干个 段（Segment） 组成。

4. 段（Segment）

• 段是表空间中的存储单位，用于管理不同用途的空间。
• 主要有两类：
  • 数据段（Data Segment）：存储表的数据（聚簇索引）。
  • 索引段（Index Segment）：存储二级索引的数据。
• 段是由 多个区（Extent） 组成的。

5. 区（Extent）

• 区是空间分配的基本单位，每个区大小固定为 1MB。
• 在 InnoDB 默认 16KB 页大小时：
  • 每个区包含 64 个页（16KB × 64 = 1MB）。
• 为了减少空间碎片，InnoDB 会先以页为单位分配，表大了再按区分配。

6. 页（Page）

• 页是 InnoDB 磁盘和内存交互的基本单位，默认大小 16KB。
• 常见页类型：
  • 数据页（B+Tree Node Page）：存储表数据。
  • Undo 页：存储回滚日志。
  • 系统页：存储事务系统信息。
  • 索引页：存储索引数据。
• 页内的数据通过 页目录（Page Directory） 进行管理。

7. 行（Row）

• 行是最小的数据存储单元。
• InnoDB 是 行存储，每行数据会按字段存储在数据页中。
• 行存储的特点：
  • 一行数据可能存不下时，大字段（如 TEXT、BLOB）会存储在溢出页（Overflow Page），行中只保留指针。
  • 每行都有额外的隐藏字段，比如：DB_TRX_ID（事务 ID）、DB_ROLL_PTR（回滚指针）等

数据库（Database）
   └── 表（Table）
        └── 表空间（Tablespace）
             └── 段（Segment）
                  └── 区（Extent，1MB）
                       └── 页（Page，16KB）
                            └── 行（Row）

MySQL语言分为三大类，各有不同的作用和权限要求：

语言分类对比：

1. DDL（Data Definition Language）：数据定义语言
2. DML（Data Manipulation Language）：数据操作语言
3. DCL（Data Control Language）：数据控制语言

存储引擎是 MySQL 的组件，用于处理不同表类型的 SQL 操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

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"

设置存储引擎

-- 建表时指定存储引擎。默认的就是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;

常见的存储引擎就 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文件（或多个，可自己配置）

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(*) 的性能。

MySQL 从 5.5 开始默认引擎改为 InnoDB，主要是因为 InnoDB 更符合企业级应用的需求：

• 支持事务，保证 ACID；
• 宕机时能通过 redo/undo log 保证数据安全；
• 行级锁提高了并发性能，而 MyISAM 只有表级锁；
• 支持外键，保证数据一致性；
• 有 Buffer Pool 缓存机制，性能更优；
• 也是 MySQL 社区未来重点发展的方向。

因此 InnoDB 逐渐取代 MyISAM，成为默认存储引擎。

• MYSQL官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以说索引的本质是：数据结构

• 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。

• 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。

  

  左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址

  为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。

• 索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上

• 平常说的索引，没有特别指明的话，就是 B+ 树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用 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的表中创建

这就涉及到了好多知识点，我们可以列举几项关键点，说说**索引结构、聚簇索引**

首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

• B+Tree索引

  InnoDB 的主要索引结构是 B+ 树索引。B+ 树是一种平衡树，每个节点可以有多个子节点。与 B 树不同，B+ 树的所有数据都存储在叶子节点中，叶子节点之间通过指针相连，这使得范围查询和排序操作非常高效。

• 聚簇索引 和 非聚簇索引

• InnoDB 中的主键索引就是聚簇索引。聚簇索引将数据行与索引紧密结合在一起，数据行存储在叶子节点中，因此通过主键查找数据非常高效

  • 当你创建一个表并指定主键时，InnoDB 会自动使用主键创建一个聚簇索引。
  • 如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引代替。
  • 如果没有唯一非空索引，InnoDB 会自动生成一个隐藏的行 ID 作为聚簇索引

• 辅助索引（也称为二级索引或非聚簇索引）是用于加速对非主键列的查询。辅助索引的叶子节点存储索引列的值以及对应的主键值。

  使用辅助索引进行查询时，InnoDB 首先通过辅助索引找到主键值，然后通过主键值在聚簇索引中查找实际数据。这种回表（回查）过程可能增加查询时间，但仍然比全表扫描快得多。

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 的高度依然维持在 34 层左右，也就是说一次数据查询操作只需要做 34 次的磁盘 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 的数据了， 这个是非常重要的性质，就是我们说的索引的最左匹配特性。

InnoDB 的索引是基于 B+ 树实现的，存储单元是 16KB 的页。当一个页的数据写满后，如果需要在其中间插入新数据，InnoDB 会申请新的页，把部分数据迁移过去，并更新父节点指针，这个过程就是“页分裂”。页分裂会带来 性能开销（申请页、搬迁数据、更新索引）和 空间浪费（页利用率下降），因此在设计表结构时，推荐使用 自增主键，减少离散插入，避免频繁页分裂。

1. 什么是“页”？

• InnoDB 的最小存储单元是 页（Page），默认大小是 16KB。
• 一个索引（不管是聚簇索引还是二级索引），底层都是一个 B+ 树，每个节点就是一个 页。

2. 什么是“页分裂”？

• 当 在一个页中插入数据时，空间不足（16KB 填满了），就会触发 页分裂。
• 页分裂过程：
  1. 申请一个新的页；
  2. 把原来的数据 一部分迁移到新页；
  3. 在父节点更新索引指针。

👉 简单理解：一页塞不下了，就拆成两页，然后更新 B+树结构。

3. 页分裂的触发场景

• 自增主键插入（推荐方式）： 插入的数据总是追加到页的末尾，几乎不会引发页分裂（因为新纪录总是在最后一页）。
• 非自增/离散主键插入： 新数据可能插到页的中间，导致页中间腾挪空间，如果塞不下就分裂。
• 二级索引插入： 由于二级索引按字段值排序，也可能导致中间插入，从而引发分裂。

4. 页分裂的代价

• 性能损耗：
  • 页分裂需要申请新页、数据搬迁、更新父节点，属于一次 重操作。
  • 插入效率会下降。
• 空间浪费：
  • 分裂后可能出现 页利用率下降（例如原来满页 16KB，分裂后两个页各只有 8KB）。
  • 长期频繁分裂 → 索引树更“高”，查询和维护成本上升。

5. 页合并

• 与分裂相反，当删除大量数据后，页空间利用率太低时（小于 50%），InnoDB 会触发 页合并，把数据重新压缩到一起，减少浪费。
• 页合并同样需要数据搬迁，也有开销。

6. 如何减少页分裂？

1. 使用自增主键：避免在索引中间频繁插入数据，最大限度减少分裂。
2. 控制索引数量：每个二级索引在写入时都可能分裂，减少不必要的索引。
3. 合理设计字段类型：让索引页能存放更多记录，降低分裂概率。
4. 批量插入：避免随机分散插入，尽量顺序写。

MySQL 索引底层实现，叶子结点存放的是数据还是指向数据的内存地址?

聚集索引（Clustered Index）和非聚集索引（Non-clustered Index）是数据库管理系统中常见的两种索引类型，是一种数据存储方式的区分，特别是在 MySQL 中。

• 聚簇索引，也叫“聚集索引”，表示索引结构和数据一起存放的索引。

• 非聚集索引是索引结构和数据分开存放的索引。

因为 InnoDB 默认存储引擎的原因，我们说这个一般指的是 InnoDB 中的聚集索引和非聚集索引

InnoDB 引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB 的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

• InnoDB 聚集索引：InnoDB 存储引擎使用聚集索引来存储主键列，并且所有非主键列都包含在聚集索引中，这意味着聚集索引实际上包含了整行数据。一个表只能有一个聚簇索引，通常是主键。
• InnoDB 非聚集索引：InnoDB 的非聚集索引（也称为辅助索引）首先存储非主键索引列的值，然后通过主键列的值来查找对应的行。这种方式称为“索引的索引”，因为非聚集索引首先查找主键。

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子：假设我们在员工表的年龄上建立了索引，那么当进行的查询时,在索引的叶子节点上,已经包含了 age 信息，不会再次进行回表查询。

select age from employee where age < 20

InnoDB 索引策略主要包括以下几个方面:

• 聚簇索引
• 辅助索引，也就是非聚簇索引
• 覆盖索引：查询可以直接通过索引获取所需数据，而无需回表查询
• 前缀索引：用于对较长的字符串列进行索引，只索引字符串的前 N 个字符
• 全文索引：用于对大文本字段进行全文检索

每种索引类型都有其独特的用途和优势，通过合理使用这些索引，可以显著提高数据库的查询性能。

其实这个问题也对应的是哪些情况需要建立索引，哪些不需要

使用索引需要注意的几个地方？

使用索引查询并不一定总能提高查询性能

为什么使用索引通常能提高查询性能：

1. 减少需要扫描的数据量来加快数据检索速度

   • 频繁作为查询条件的字段
   • 查询中与其他表关联的字段，外键关系建立索引
   • 在查询涉及的所有列都在索引中时，可以避免回表查询，提高查询效率

2. 索引可以显著加快 ORDER BY 和 GROUP BY 操作

为什么在某些情况下索引查询反而可能降低性能：

1. 小表或低选择性列

   • 小表：对于行数很少的表，索引带来的性能提升有限，因为全表扫描的开销也很小。索引反而增加了额外的维护开销。

     低选择性列：如果索引列的选择性很低（例如，性别列只有两个值 "M" 和 "F"），使用索引可能会导致大量的行扫描，无法显著减少数据量，索引的效果不明显。

2. 经常增删改的表

   • 频繁的写操作：索引不仅在读取数据时加速查询，还在插入、更新和删除操作时带来额外的开销。每次写操作都需要更新索引，索引越多，写操作的开销就越大。

3. 在高并发环境下，索引也可能导致锁竞争，影响查询性能

是否使用索引以及如何设计索引需要根据具体的查询模式、数据量、更新频率、硬件资源等多种因素综合考虑

1. 聚簇索引优化：自增 ID 的顺序插入让数据在磁盘连续存储，避免页拆分和碎片，提升 IO 效率；

2. B + 树特性匹配：插入时仅扩展树的最右节点，减少索引分裂开销，范围查询可利用顺序扫描；

3. 缓存与锁优化：顺序插入的新记录使得最近插入的数据很可能在相邻的存储位置，这提高了缓存的命中率。

   • 缓存友好：数据库缓存更容易缓存相邻的存储块，从而提高查询的性能，特别是在高并发的读写环境下。

建表时通常会使用自增 ID 作为主键，因为它在写入性能、存储空间和索引效率上有显著优势。

针对 ID 耗尽问题：首先评估数据规模，小表可重置自增值，核心业务表则升级为 BIGINT（理论可支撑 1.8e19 条数据）；分布式场景用雪花算法，高频删改表可设计 ID 回收机制。

MySQL高效索引

要有效地使用复合索引（也称为多列索引或组合索引），编写 SQL 查询时需要考虑以下几点：

1. 覆盖索引（Covering Index），或者叫索引覆盖， 也就是平时所说的不需要回表操作

   • 如果查询的列完全包含在复合索引中，那么可以使用覆盖索引，这样可以避免回表查询，提高性能。

2. 最左前缀法则：

   • 复合索引的效率取决于查询条件是否遵循最左前缀法则，即从索引的最左边列开始匹配。

   • 例如，如果你有一个 (c1, c2, c3) 的复合索引，那么以下查询可以高效地使用索引：

     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. 多列索引的限制

   • 索引列顺序：多列索引只有在按索引列顺序查询时才能被高效使用，如果查询条件中不包含索引的前导列，索引将无法使用。

   • 查询不完全匹配：对于复合索引，如果查询条件不完全匹配索引定义，索引的使用效果可能不佳。

索引失效常见的情况包括：在索引列上使用函数/运算、隐式类型转换、OR 连接不同字段、模糊查询前缀 %、不满足最左前缀原则、使用 !=/NOT IN/IS NOT NULL 等、范围查询导致后续列失效、ORDER BY / GROUP BY 字段不符合索引顺序、以及数据量太少导致优化器选择全表扫描。

实际项目中，可以通过 EXPLAIN 分析执行计划，避免这些写法，并合理设计联合索引。

给定查询语句 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 的条件。

主键索引是特殊的唯一索引，唯一索引查询会涉及到“回表”操作

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);
INSERT INTO example (email) VALUES (NULL), (NULL), (NULL);

索引下推（ICP）是 MySQL 5.6 引入的一项优化，允许在存储引擎层就利用索引字段做部分 WHERE 条件过滤，从而减少回表次数，降低 I/O。 例如 name LIKE 'J%' AND age=20，如果有 (name, age) 联合索引，MySQL 会在存储引擎层就过滤 age=20，避免回表后再判断。 ICP 只对二级索引有效，可以通过 EXPLAIN 的 Using index condition 来确认是否生效。

索引下推（Index Condition Pushdown，ICP） 是 MySQL 5.6 中引入的一种优化技术，用于提升范围查询或排序查询的性能。通过索引下推，MySQL 可以减少不必要的表数据行访问，加快查询速度。

1. 索引下推的由来

在 没有 ICP 之前：

• MySQL 在通过索引扫描时，先拿到索引里的主键 ID；
• 然后回表到数据页取出完整的行；
• 最后在 Server 层做条件过滤。

这样即使很多行最终不满足条件，也必须 回表取出整行数据，造成 I/O 浪费。

2. 什么是索引下推？

• 索引下推就是把部分 WHERE 条件判断下推到 存储引擎层，利用索引本身就能获取到的字段提前过滤数据。
• 这样可以在回表之前就过滤掉不必要的行，减少回表次数，提高查询效率。

3. 举个例子

假设有表：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    KEY idx_name_age (name, age)
);

查询：

SELECT * FROM user WHERE name LIKE 'J%' AND age = 20;

没有 ICP 的情况：

• name LIKE 'J%' 用到索引（范围扫描）。
• 但 age = 20 只能在回表后做判断。
• 即使扫描出来 10 万行 name LIKE 'J%'，也得回表逐条验证 age。

有 ICP 的情况：

• MySQL 把 age = 20 条件下推到存储引擎层。
• 存储引擎在扫描 idx_name_age 索引时，就能直接判断 age。
• 这样可能只剩下几百行需要回表，大大减少 I/O。

4. 哪些场景能触发 ICP？

• 只对 二级索引 有效（主键索引存的就是全字段，不需要回表）。
• 查询条件中，部分字段能利用索引顺序，部分不能利用。
• 不支持的情况：某些复杂表达式、函数计算等。

5. 如何确认是否使用了 ICP？

执行 EXPLAIN，如果 Extra 列里有：

Using index condition

说明 MySQL 启用了索引下推。

索引下推的优势

• 减少回表次数：通过提前过滤不符合条件的记录，减少回表操作。
• 提升查询效率：尤其是当索引列上的范围条件命中大量记录，而回表的记录较少时，索引下推可以显著减少不必要的 IO 操作。

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 支持四种事务隔离级别，由低到高分别为：

• READ UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

简单点的理解

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。

读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。

串行：我的事务尚未提交，别人就别想改数据。

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation'

• “更新丢失” 通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

• “脏读” 、 “不可重复读”和“幻读” ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：

  • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。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 是 InnoDB 实现读写并发控制的核心机制，通过 隐藏列、Undo Log、Read View 来维护多版本数据。 在快照读时，事务会根据自己的 Read View，决定看到哪个版本。

• 优点：实现了 非阻塞读，大幅提升并发性能。
• 适用：主要用于 RC 和 RR 两个隔离级别。 本质上，MVCC 是“读旧版本数据来避免读写冲突”。

1. 定义与作用 MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL InnoDB 引擎用来实现 高并发下的读写一致性 的机制。

• 作用：保证 读写并发 时，读不会被写阻塞（非阻塞读），提升性能。
• 核心思想：同一行数据在不同时间点可能有多个版本，读操作可以“读历史版本”，而不是等写操作完成。

2. 底层原理（InnoDB 实现）

MVCC 主要是借助于版本链来实现的。InnoDB 引擎通过回滚指针，将数据的不同版本串联在一起，也就是版本链。这些串联起来的历史版本，被放到了 undolog 里面。当某一个事务发起查询的时候，MVCC 会根据事务的隔离级别来生成不同的 Read View，从而控制事务查询最终得到的结果。

MVCC 依赖三个关键机制：

① 隐藏列（存储事务信息），InnoDB 在每行记录后面有两个隐藏列：

• trx_id：最后一次修改该行的事务 ID。
• roll_pointer：指向 Undo Log 的指针，用于找到修改前的旧版本。

InnoDB下的Compact行结构，有三个隐藏的列

列名	是否必须	描述
row_id	否	行ID，唯一标识一条记录（如果定义主键，它就没有啦）
transaction_id	是	事务ID
roll_pointer	是	DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

② Undo Log（回滚日志）

• 当事务修改数据时，InnoDB 会先把“修改前的旧值”写入 Undo Log。
• 这样即使数据被更新，仍能通过 Undo Log 找到历史版本。

③ Read View（读视图）

• 在 快照读（普通 SELECT） 时生成，保存当前活跃事务的 ID 列表。
• 通过对比 trx_id 与 Read View，决定当前事务能看到哪一个版本的数据。

👉 简单来说：

• 读操作 → 根据 Read View 规则，选择可见版本（可能是 Undo Log 里的旧版本）。
• 写操作 → 新增一个版本（更新 trx_id、写 Undo Log），而不是覆盖旧版本。

3. 可见性规则（简化版） 判断某条记录的版本是否对当前事务可见：

1. 如果 trx_id < min_trx_id（最小活跃事务 ID），说明版本在当前事务前提交 → 可见。
2. 如果 trx_id > max_trx_id（最大已分配事务 ID），说明版本在当前事务开始后生成 → 不可见。
3. 如果 trx_id 在活跃事务列表里 → 不可见。
4. 其他情况 → 可见。

4. MVCC 适用的隔离级别

• 读已提交（RC）：每次查询生成新的 Read View，可能看到最新提交的数据。
• 可重复读（RR）：一个事务里只生成一次 Read View，多次查询看到的数据一致。
• 未提交读（RU） 和 串行化 不走 MVCC（RU 直接读最新，串行化加锁）。

核心概念

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 解决了什么问题

• 读写不冲突，极大的增加了系统的并发性能

• 解决脏读，幻读，不可重复读 等问题（注:其实多版本只是解决不可重复读问题，而加上临界（next-key）锁(也就是它这里所谓的并发控制)才解决了幻读问题。）

  Next-Key Locking 是行锁和间隙锁的组合，它锁定的是索引记录以及其附近的间隙，防止其他事务在查询范围内插入或删除记录，确保当前事务在后续的查询中不会看到“幻影”。

  举例：如果事务A查询了范围 WHERE age BETWEEN 18 AND 25，Next-Key Locking 会锁定已经存在的记录以及这个范围的间隙，防止其他事务在这个范围内插入新数据

在可重复读（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的可重复读隔离级别提供了强有力的幻读保护，但在某些特殊情况下，幻读仍然可能发生。开发者需要了解这些情况，并在必要时采取额外的措施来确保数据的一致性和隔离性。

在 InnoDB 中，普通的 SELECT 是快照读，它基于 MVCC 从 undo log 里读取历史版本，不加锁，性能高；而带锁的查询（如 SELECT ... FOR UPDATE）以及 UPDATE/DELETE/INSERT 属于当前读，它会加锁并返回记录的最新版本，用来保证数据一致性。

1. 快照读（Snapshot Read）

• 定义：读取的是数据的 快照版本（历史版本），不加锁。

• 实现机制：依赖 InnoDB 的 MVCC（多版本并发控制），通过 undo log 保存旧版本数据，根据事务的隔离级别和 Read View 来决定能看到哪一条版本。

• 特点：

  • 不加锁，读写不冲突 → 性能好。
  • 可能读到旧数据（取决于隔离级别：RC、RR）。

• 常见 SQL 场景：

  SELECT * FROM user WHERE id = 1;
  

  （普通的 SELECT，没有加 for update / lock in share mode）

2. 当前读（Current Read）

• 定义：读取的是 记录的最新版本，并且会加锁（保证数据一致性）。

• 实现机制：通过加锁（共享锁 / 排他锁）来保证读取的是最新值，阻塞其他事务的修改，避免并发问题。

• 特点：

  • 读取最新数据，带有锁。
  • 和写操作冲突时会等待或死锁。

• 常见 SQL 场景：

  SELECT * FROM user WHERE id = 1 FOR UPDATE;   -- 排他锁
  SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁
  UPDATE user SET name = 'Tom' WHERE id = 1;   -- 写操作，本质也是当前读
  DELETE FROM user WHERE id = 1;
  INSERT INTO user VALUES (...);
  

3. 核心区别总结

我现在有三个事务，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 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现 ACID 特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。

还看到一种说法：

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。我感觉这种更好些

MySQL（主要指 InnoDB 引擎）通过 存储引擎的机制来保证事务的 ACID：

1. A（原子性，Atomicity）
   • 原子性保证事务中的操作要么全部成功，要么全部失败。
   • 实现原理：
     • InnoDB 使用 undo log（回滚日志） 来保证原子性。
     • 如果事务中途失败，可以用 undo log 回滚到事务开始前的状态。
2. C（一致性，Consistency）
   • 一致性保证数据从一个一致状态转换到另一个一致状态（符合约束、触发器等）。
   • 实现原理：
     • 一致性依赖于其他三个特性（A、I、D）的保证；
     • 同时依靠 外键约束、唯一性约束、触发器 等机制，确保数据完整性。
3. I（隔离性，Isolation）
   • 隔离性保证并发事务之间互不干扰。
   • 实现原理：
     • InnoDB 采用 MVCC（多版本并发控制）+ 锁机制 实现：
       • MVCC：通过 undo log + ReadView，不同事务在相同时间点读到不同版本的数据，避免读写冲突；
       • 锁机制：行锁、间隙锁、Next-Key 锁，防止写写冲突、幻读。
4. D（持久性，Durability）
   • 持久性保证事务一旦提交，数据永久保存，即使宕机也不会丢失。
   • 实现原理：
     • InnoDB 使用 redo log（重做日志）+ WAL 机制 实现：
       • 提交时先写 redo log（顺序写，落盘快）；
       • 后续再将数据异步刷入数据文件；
       • 宕机恢复时，依赖 redo log 重放，保证数据持久化。

分布式事务相关问题，可能还会问到 2PC、3PC，，，

分布式事务的实现方式有很多，既可以采用 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模式适用于事务较为复杂的场景，尤其是在资金交易类系统中。”

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

在 MySQL 的 InnoDB 引擎里面，锁是借助索引来实现的。或者说，加锁锁住的其实是索引项，更加具体地来说，就是锁住了叶子节点

1. 锁的物理载体：B+树索引节点（非数据页）
2. 锁升级条件：无索引或索引失效时退化为表锁
3. 锁兼容性：共享锁（S锁）允许并行读，排他锁（X锁）独占写

从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响

• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MyISAM 表锁

MyISAM 的表锁有两种模式：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB 行锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁又称为读锁，简称 S 锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排他锁又称为写锁，简称 X 锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据行读取和修改。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 varchar 查询不写单引号的情况。

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

InnoDB 的行锁是其支持高并发的核心机制，本质是基于索引记录加锁，通过内存中的锁结构与事务系统实现并发控制。

一、行锁的本质：基于索引记录的 “标记”

• InnoDB 不会直接锁定物理数据行，而是通过 索引 B+ 树上的索引项 来加锁。
• 聚簇索引：锁住叶子节点（数据行存储在聚簇索引里）。
• 二级索引：先锁住二级索引项，再回表锁住聚簇索引项。
• 无索引或索引失效时 → 行锁退化为表锁。

例：对表 user(id, name)（id 为主键，name 为二级索引）执行 UPDATE user SET age=30 WHERE name='张三' 时：

1. 先锁定 name='张三' 对应的二级索引记录；
2. 通过二级索引回表，锁定对应 id 的聚簇索引记录。

二、行锁的类型与数据结构

InnoDB 实现了两种基础行锁类型，通过 锁结构（Lock Struct） 存储在内存中：

1. 共享锁（S 锁，读锁）
   • 允许事务读取一行数据，多个事务可同时持有同一行的 S 锁（读不互斥）；
   • 加锁方式：SELECT ... LOCK IN SHARE MODE。
2. 排他锁（X 锁，写锁）
   • 允许事务修改或删除一行数据，与其他任何锁（S 或 X）互斥（写独占）；
   • 加锁方式：UPDATE/DELETE 自动加 X 锁，或 SELECT ... FOR UPDATE 显式加 X 锁。

锁结构的核心信息：

• 锁定的索引记录（如 id=100 的聚簇索引记录）；
• 锁类型（S 或 X）；
• 持有锁的事务 ID；
• 等待该锁的事务链表（当锁冲突时，等待的事务会被挂入此链表）。

三、行锁的实现依赖：事务与 undo 日志

1. 事务 ID 与锁的关联每个事务开始时会分配唯一的 transaction_id，锁结构中会记录持有锁的 transaction_id，用于标识锁的归属。当事务提交或回滚时，InnoDB 会释放该事务持有的所有行锁，并唤醒等待链表中的事务。
2. undo 日志与行锁释放行锁的释放时机与事务状态强相关：
   • 对于 UPDATE/DELETE 操作，InnoDB 会先写 undo 日志（用于事务回滚），然后加 X 锁修改数据；
   • 事务提交后，undo 日志标记为可清理，行锁释放；
   • 事务回滚时，通过 undo 日志恢复数据，同时释放行锁。

四、特殊锁机制：间隙锁与临键锁（解决幻读）

在默认的 Repeatable Read（可重复读） 隔离级别下，InnoDB 为了防止 “幻读”（同一事务中，多次查询同一范围返回不同行数），引入了两种特殊锁，本质是对 “行锁粒度的扩展”：

1. 记录锁（Record Lock）
   • 锁定单条索引记录 SELECT * FROM user WHERE id=1 FOR UPDATE;
2. 间隙锁（Gap Lock）
   • 锁定索引记录之间的 “间隙”（不包含记录本身），防止其他事务在间隙中插入新行；
   • 例：表中存在 id=10, 20，执行 UPDATE ... WHERE id BETWEEN 10 AND 20 时，会锁定间隙 (10,20)，阻止插入 id=15 的行。
3. 临键锁（Next-Key Lock）
   • 记录锁 + 前一个间隙，锁定范围为“左开右闭”。
   • InnoDB 默认使用 Next-Key Lock，保证 RR 隔离级别下无幻读。
   • 如果是唯一索引精确匹配，会优化为 Record Lock。

注意：若将隔离级别降为 Read Committed，间隙锁和临键锁会关闭，仅保留行锁（可能出现幻读，但并发性能提升）。

五、行锁的触发与释放流程（以 UPDATE 为例）

1. 事务开始，获取 transaction_id；
2. 解析 SQL，定位需要修改的索引记录（通过 B+ 树查找）；
3. 对目标索引记录尝试加 X 锁：
   • 若锁未被占用，加锁成功，继续执行修改；
   • 若锁已被其他事务占用，当前事务进入等待状态，挂入该锁的等待链表；
4. 修改数据（写 redo 日志确保持久化，写 undo 日志用于回滚）；
5. 事务提交 / 回滚：释放所有行锁，唤醒等待链表中的事务重新竞争锁。

六、核心结论

InnoDB 行锁的实现可总结为：

1. 索引是基础：无索引或索引失效时，行锁会退化为表锁；
2. 锁结构是载体：通过内存中的锁结构记录锁类型、持有事务和等待队列；
3. 事务是上下文：锁的生命周期与事务绑定，提交 / 回滚时释放；
4. 特殊锁是补充：间隙锁和临键锁解决幻读，代价是降低部分并发性能。

最早只有 Record Lock，只能锁住已有的记录，能保证“当前读”的一致性；

但是只用 Record Lock 会有 幻读问题（别的事务在两次查询之间插入新行）；

为了解决幻读，引入 Gap Lock，把“记录之间的空隙”也锁起来，阻止插入；

但单独使用 Record Lock + Gap Lock 管理复杂，所以 InnoDB 提出了 Next-Key Lock（记录锁 + 前一个间隙），作为默认锁算法，保证 REPEATABLE READ 下无幻读。

MySQL 的行锁粒度主要体现在不同的存储引擎和索引使用情况。InnoDB 是支持行级锁的，它的粒度会根据索引条件来变化：

• 如果查询语句命中了索引，通常是行级锁，锁定更精细；
• 如果没有命中索引或者索引不够精准，可能会退化为表级锁；
• 在事务隔离级别下，不同的加锁策略（比如 next-key lock）也会让粒度变粗，涉及到间隙锁、范围锁等。 所以行锁的颗粒度不是固定的，会随着 索引情况、SQL 语句、事务隔离级别而变化。

在 MySQL 中，隔离级别和锁的关系紧密相关，不同的隔离级别通过使用不同的锁机制来控制事务间的并发行为，确保数据的一致性和隔离性。以下是 MySQL 四种隔离级别与锁的关系：

1. 读未提交（Read Uncommitted）

   • 锁机制：最低级别，不使用锁，允许读取未提交的数据。

   • 并发性：高。

   • 数据一致性：低，可能导致脏读。

2. 读已提交（Read Committed）

   • 锁机制：读取时使用共享锁（S Lock），更新时使用排他锁（X Lock）。

   • 并发性：中等。

   • 数据一致性：避免脏读，但可能发生不可重复读。

3. 可重复读（Repeatable Read）

   • 锁机制：读取时使用共享锁（S Lock），更新时使用排他锁（X Lock），并使用间隙锁（Gap Lock）防止幻读。

   • 并发性：较低。

   • 数据一致性：避免脏读和不可重复读，但在默认设置下防止幻读。

4. 串行化（Serializable）

   • 锁机制：事务间完全隔离，所有读取和写入都使用表级锁或行级锁，确保完全隔离。

   • 并发性：最低。

   • 数据一致性：最高，避免所有并发问题。

选择适当的隔离级别和锁机制，可以在数据一致性和系统并发性之间找到最佳平衡。

在数据库中，如果两个事务同时尝试更新同一条数据，会发生以下几种情况之一，具体取决于数据库的隔离级别和锁机制：

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。更新操作时，检查版本号是否匹配，匹配则更新，不匹配则说明有冲突，需要重新读取数据再进行操作。
• 时间戳：用时间戳字段记录最后修改时间，更新操作时检查时间戳是否匹配。

可以用来解决电商中的“超卖”问题。

for update 仅适用于 InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL 会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！

假设有个表单 products ，里面有 id 跟 name 二个栏位，id 是主键。

• 明确指定主键，并且有此笔资料，row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

• 明确指定主键，若查无此笔资料，无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

• 无主键，table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

• 主键不明确，table lock

SELECT * FROM products WHERE id <>'3' FOR UPDATE;

• 主键不明确，table lock

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE 仅适用于 InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。 注2: 要测试锁定的状况，可以利用 MySQL 的 Command Mode ，开二个视窗来做测试。

死锁是指两个或多个事务在等待彼此持有的资源，从而导致所有事务都无法继续执行的情况。在 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 的锁

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 遇到过死锁问题吗，你是如何解决的？

死锁产生：

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

死锁产生的四个必要条件

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] 临键锁。

核心理念：合理的数据类型选择和高效的查询编写是数据库性能的基础，掌握查询优化技巧能显著提升系统响应速度。

主要包括以下五大类：

• 整数类型：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(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

  • 存储时，char 不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而 varchar 会根据实际存储的数据分配最终的存储空间，加上 1 或 2 个额外字节用于存储字符串的长度信息

• 性能不同：

  • CHAR 因为其固定长度的特性，在某些情况下可能会提供更好的性能，尤其是当处理大量具有相同长度的数据时。
  • VARCHAR 由于需要存储长度信息，并且可能需要额外的空间来存储不同长度的字符串，因此在性能上可能略逊于 CHAR

• 使用场景：char 适用于固定长度字符串：如存储状态码、国家代码、MD5 哈希值等。varchar 适用于可变长度字符串：如用户名、电子邮件地址、描述等。

阿里规范：【强制】任何货币金额，均以最小货币单位且整型类型来进行存储。

在 MySQL 中记录货币数据时，通常推荐使用 DECIMAL 类型。DECIMAL 类型提供高精度的存储和计算，非常适合用于存储货币值。以下是使用 DECIMAL 类型的原因以及其他可能选择的字段类型和其适用场景：

使用 DECIMAL 类型的原因

1. 高精度：
   • DECIMAL 类型可以精确存储货币值，没有浮点运算误差。例如，定义为 DECIMAL(10, 2) 表示最多 10 位数字，其中 2 位小数，适合存储最多到亿位的金额，精确到小数点后两位。
2. 存储效率：
   • 由于货币值通常需要精确到小数点后两位，DECIMAL 能够确保存储的每一个值都是精确的，避免了浮点数可能带来的舍入误差。
3. 计算正确性：
   • 在涉及到财务计算时，使用浮点数类型（如 FLOAT 或 DOUBLE）可能会因为舍入误差导致计算不准确。DECIMAL 类型避免了这些问题，确保计算结果的准确性。

当然还有些业务，用 INT 或者 BIGINT 类型存储货币的最小单位也可以（如美分、分），适合对性能有更高要求的场景（整数运算比浮点运算更快），但需要处理转换逻辑。

MySQL 的 DATETIME 和 TIMESTAMP 都用来存储日期时间，但有几个关键区别：

1. DATETIME 与时区无关，存储的是什么取出来就是什么，范围更大（1000 ~ 9999 年），占 8 字节。
2. TIMESTAMP 与时区相关，存储时会转为 UTC，取出时按会话时区转换，范围是 1970 ~ 2038 年，占 4~7 字节。
3. TIMESTAMP 支持 CURRENT_TIMESTAMP 和自动更新，常用来存记录的创建时间、修改时间；而 DATETIME 更适合业务逻辑相关的时间点。

1. 存储方式

• DATETIME
  • 以字符串形式存储日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。
  • 与 时区无关，存进去什么值，取出来就是什么值。
• TIMESTAMP
  • 以 Unix 时间戳（从 1970-01-01 00:00:01 UTC 到某一时刻的秒数）存储。
  • 与 时区相关，存储时会转成 UTC，取出时会根据会话时区转换。

2. 取值范围

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• TIMESTAMP：1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC（32 位整型限制，存在 2038 年问题）

3. 存储大小

• DATETIME：8 字节
• TIMESTAMP：4 字节（MySQL 5.6 之后扩展为 4 ~ 7 字节，取决于小数秒精度）

4. 时区影响

• DATETIME：不受时区影响，适合存储固定的“绝对时间”。
• TIMESTAMP：受时区影响，存储时转 UTC，取出时根据当前时区转换，适合存储事件发生的“真实时间点”。

5. 默认值与自动更新

• DATETIME：默认值需要显式指定，一般不会自动更新。
• TIMESTAMP：可以默认 CURRENT_TIMESTAMP，并支持 ON UPDATE CURRENT_TIMESTAMP，自动存储当前时间。

6. 应用场景

• DATETIME：适合存储“业务层面的日期时间”，如生日、会议时间、账单周期等，保证跨时区不变。
• TIMESTAMP：适合存储“记录生成或修改的时间”，如 create_time、update_time，自动随时区变化而调整。

这个问题其实很不重要，因为大部分公司不让让使用这两种类型。

禁止使用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。

• COUNT(*)：统计表中所有行的数量，包括 NULL 值，性能最佳，适用于需要统计总行数的情况
• COUNT(1)：与 COUNT(*) 类似，统计表中所有行的数量，包括 NULL 值，性能与 COUNT(*) 基本相同
• COUNT(列名)：统计指定列中非 NULL 值的数量，适用于需要统计特定列中实际值数量的情况

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；

• UNION 在进行表连接后会筛选掉重复的数据记录（效率较低），而 UNION ALL 则不会去掉重复的数据记录；

• UNION 会按照字段的顺序进行排序，而 UNION ALL 只是简单的将两个结果合并就返回；

• 手写

  SELECT DISTINCT <select_list>
  FROM  <left_table> <join_type>
  JOIN  <right_table> ON <join_condition>
  WHERE  <where_condition>
  GROUP BY  <group_by_list>
  HAVING <having_condition>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>

• 机读

  FROM  <left_table>
  ON <join_condition>
  <join_type> JOIN  <right_table> 
  WHERE  <where_condition>
  GROUP BY  <group_by_list>
  HAVING <having_condition>
  SELECT
  DISTINCT <select_list>
  ORDER BY <order_by_condition>
  LIMIT <limit_number>

mysql 的内连接、左连接、右连接有什么区别？

什么是内连接、外连接、交叉连接、笛卡尔积呢？

Join图

MySQL 日志其实是各种其他知识模块的基础

• 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

• 通用查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

• 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

• 二进制日志：记录对数据库执行更改的所有操作。

• 中继日志：中继日志也是二进制日志，用来给slave 库恢复

• 事务日志：重做日志 redo 和回滚日志 undo

时序上先 undo log，redo log 先 prepare， 再写 binlog，最后再把 redo log commit

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）和数据一致，保证事务在任何情况下都能正确提交或回滚。

在 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 文件通常不循环使用，它们会随着时间持续增长，直到通过配置的策略进行清理。

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的影响

  • 不适合放进MySQL的数据
    • 二进制多媒体数据
    • 流水队列数据
    • 超大文本数据
  • 需要放进缓存的数据
    • 系统各种配置及规则数据
    • 活跃用户的基本信息数据
    • 活跃用户的个性化定制信息数据
    • 准实时的统计信息数据
    • 其他一些访问频繁但变更较少的数据

• Schema设计对系统的性能影响

  • 尽量减少对数据库访问的请求
  • 尽量减少无用数据的查询请求

• 硬件环境对系统性能的影响

优化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 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> 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 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 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 相同不同，同时存在，相同的属于一组，从上往下执行

• 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 认为不使用索引更有效。

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）的列，如果列值较长且前缀具有较高区分度，可以使用前缀索引来节省空间和提高查询效率。
• 复合索引：在多个列上建立复合索引（即组合索引），以优化涉及多列的查询。但要注意列的顺序，通常将选择性更高的列放在最前面。
• 考虑查询频率：根据查询的频率和响应时间要求，对高频查询的列进行索引优化。在写操作频繁的表上，要平衡索引的数量，以避免过多索引导致的插入和更新性能下降。
• 避免过多索引：虽然索引可以加快查询速度，但过多的索引会增加插入、更新、删除操作的成本。因此，需要在查询性能和写性能之间取得平衡。

• 查询条件中使用了函数或表达式：当在查询条件中对索引列使用了函数或表达式（如UPPER(column_name)），索引可能失效。MySQL需要扫描所有行来计算函数结果，导致全表扫描。
• 隐式类型转换：如果查询条件中的列和参数类型不匹配（如将字符串列与数字比较），MySQL会进行隐式类型转换，导致索引失效。
• 模糊查询以通配符开头：使用LIKE '%value'形式的模糊查询时，由于通配符位于开头，索引无法使用，MySQL需要进行全表扫描。
• 索引列不在最左侧：对于复合索引，如果查询条件中未使用复合索引的最左侧列，索引将无法使用（“最左前缀”原则）。
• OR条件未全部使用索引：如果查询条件中有OR，且每个条件都未使用索引，则MySQL无法利用索引，需要进行全表扫描。
• 查询条件中有NULL值：在某些情况下，索引列的查询条件中如果包含IS NULL或IS NOT NULL，可能会导致索引失效。
• 查询条件中使用不等号：使用<>或!=查询条件时，MySQL可能会选择不使用索引，因为这种条件通常需要扫描大量行。

• 重构查询：避免在索引列上使用函数、表达式、隐式类型转换等操作。尽可能让查询条件直接作用于索引列，确保索引生效。
• 使用合适的类型：确保查询条件中的类型与列的类型匹配，避免隐式类型转换。
• 合理使用通配符：对于模糊查询，尽量避免通配符开头。如果业务允许，可以考虑在应用层进行拆分查询或引入全文索引（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 就无法使用前缀索引，无法把前缀索引用作覆盖索引。

1. 查看sql是否涉及多表的联表或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成临时表（业务和算法的优化）
2. 涉及连表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合
3. 如果以上两种都不能操作，非要连表查询，那么考虑对相对应的查询条件做索引。加快查询速度
4. 针对数量大的表进行历史表分离（如交易流水表）
5. 数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql有自带的binlog实现 主从同步
6. explain分析sql语句，查看执行计划，分析索引是否用上，分析扫描行数等等
7. 查看mysql执行日志，看看是否有其他方面的问题

大表优化我一般从几个层面考虑：

1. 存储层面：分库分表、冷热数据分离；
2. 索引层面：建合适的联合索引、覆盖索引，必要时用分区表；
3. SQL 层面：避免全表扫描、避免函数导致索引失效、批量处理代替逐行；
4. 维护层面：归档历史数据、定期优化表结构；
5. 架构层面：读写分离、引入缓存、甚至用 Elasticsearch/ClickHouse 处理分析类查询。 通过这些手段，把大表的单次查询和维护压力控制在可接受范围内。

数据库调优我一般从五个层次来考虑：

1. 架构层：比如读写分离、分库分表、加缓存，把整体压力降下来。
2. 索引优化：结合最左前缀原则，避免索引失效，尽量用覆盖索引减少回表。
3. SQL 优化：用 explain 看执行计划，避免 select *、大事务，分页优化。
4. 参数调优：调整 buffer_pool、连接池、慢查询日志。
5. 硬件和运维：SSD、加内存、监控慢 SQL。

在项目里，我做过分页优化，把 limit offset 深分页改成基于主键范围查询，单次查询延迟从几秒降到几十毫秒；也做过索引重构，把一个多表 join 改成冗余字段 + 单表查询，性能提升明显。

这个问题很常见，面试官想听你是否系统性理解过数据库调优（不仅仅是写 explain 看执行计划，而是从 架构、SQL、索引、参数、硬件 全链路考虑）

数据库调优思路（五个层次）

1. 架构层面

• 读写分离：主库写，从库读，缓解单库压力。
• 分库分表：数据量过大时按业务或范围拆分（如订单库按用户 ID hash 分表）。
• 引入缓存：热点数据放在 Redis，减少 DB 压力。
• 异步化：非核心操作走消息队列（MQ），削峰填谷。

2. 索引优化

• 合理建立 联合索引，遵循最左前缀原则。
• 避免索引失效（如函数操作、隐式类型转换、!=、like '%xx'）。
• 使用 覆盖索引，减少回表操作。
• 定期分析表和索引碎片，必要时 analyze table 或重建索引。

3. SQL 优化

• 使用 EXPLAIN 查看执行计划，关注 type、rows、Extra。
• 避免 select *，只查需要的列。
• 控制分页深度：大 offset 的分页用 覆盖索引 + id 范围 或 search_after。
• 大事务拆小事务，避免长时间锁表。
• 批量写入用 batch insert，减少单条 SQL。

4. 参数调优

• 连接池：合理配置 max_connections，避免过大导致上下文切换。
• 缓冲池：InnoDB 的 innodb_buffer_pool_size 一般配置为物理内存的 60%~70%。
• 日志参数：调优 innodb_log_file_size，提升写性能。
• 慢查询日志：开启 slow_query_log，对慢 SQL 定位和优化。

5. 硬件与运维

• SSD 替代机械硬盘，IOPS 提升明显。
• 提升内存，增大缓存命中率。
• 分区表、冷热数据分离。
• 监控（Prometheus + Grafana），实时发现慢 SQL。

MySQL 8.0 相比 5.7 有很多重要升级，最典型的有：

1. 数据字典统一化：以前的元数据分散在 .frm、.par 文件里，现在集中在系统表里，更一致也更安全。
2. 8.0 支持 函数索引（如 INDEX idx_name (UPPER(name))），5.7 中对索引列使用函数（如 UPPER(name)）会导致索引失效，8.0 可直接命中函数索引，查询效率提升显著。
3. 默认字符集改为 utf8mb4，彻底支持 Emoji 等四字节字符。
4. 事务性 DDL：大多数 DDL（比如 ALTER TABLE）可以回滚，不像以前执行失败会半途残留。
5. 窗口函数和公用表表达式（CTE），SQL 能力更强。
   • 新增 ROW_NUMBER()、RANK()、LEAD()、LAG() 等函数，支持复杂排名和分组统计。
   • 以前需要子查询 + 变量才能实现，现在 SQL 更简洁。
6. 更强的 JSON 支持，比如 JSON 表函数、索引优化。
7. 性能优化：更高效的并行查询、持久化执行计划、历史表统计信息。
8. 安全性增强：默认使用 caching_sha2_password 认证插件，加密更强。

一般情况下我们创建的表对应一组存储文件

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分区的一些关键点，适合在面试中讨论：

能干嘛

• 逻辑数据分割
• 提高单一的写和读应用速度
• 提高分区范围读查询的速度
• 分割数据能够有多个不同的物理文件路径
• 高效的保存历史数据

分区类型及操作

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
• 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
• 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

针对“如何确定分库还是分表？”的问题，你要结合具体的场景。

何时分表

当数据量过大造成事务执行缓慢时，就要考虑分表，因为减少每次查询数据总量是解决数据查询缓慢的主要原因。你可能会问：“查询可以通过主从分离或缓存来解决，为什么还要分表？”但这里的查询是指事务中的查询和更新操作。

何时分库

为了应对高并发，一个数据库实例撑不住，即单库的性能无法满足高并发的要求，就把并发请求分散到多个实例中去

总的来说，分库分表使用的场景不一样：

• 分表是因为数据量比较大，导致事务执行缓慢；

• 分库是因为单库的性能无法满足要求。

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对 master 性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

分库的优点：

• 减少增量数据写入时的锁对查询的影响

• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

• 垂直拆分

  垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

• 水平拆分(数据分片)

  单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

  水平分割的几种方法：

  • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
  • 还可根据时间放入不同的表，比如：article_201601，article_201602。
  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
  • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

在电商和金融类项目中，我负责过订单、交易记录等核心表的分库分表设计，主要基于 Sharding-JDBC 实现。分库分表本质是 “突破单库单表的性能和容量瓶颈”，但也会引入新的复杂度，具体可以从 “为什么做→怎么做→问题与解决” 展开说明：

一、为什么要分库分表？（核心痛点）

单库单表在数据量增长到一定规模后，会遇到 性能瓶颈 和 容量瓶颈，具体表现为：

1. 查询性能急剧下降：单表数据量超过 1000 万行后，即使加了索引，SQL 执行也会变慢（B + 树索引层级增加，磁盘 IO 次数增多）。比如电商订单表，3 年数据可能达 5000 万行，查询 “近 3 个月订单” 需要扫描大量数据，响应时间从 100ms 飙升到 1s+。
2. 写入性能受限：单库的并发写入能力有限（MySQL 单库 TPS 一般在 1-2 万），若秒杀场景下每秒产生 5 万订单，单库会出现 “锁等待”“连接耗尽”，导致写入超时。
3. 运维风险高：单表数据量过大，备份 / 恢复时间极长（比如 100GB 的表，备份需几小时），一旦数据库故障，恢复周期长，影响业务可用性。

分库分表通过 “将大表拆成小表、大库拆成小库”，把压力分散到多个数据库节点，解决上述问题。

二、多少数据适合分库分表？（无绝对标准，看业务）

没有固定阈值，核心看 “当前数据量是否影响业务性能”，结合数据库类型和硬件配置，行业有通用参考：

• MySQL：单表数据量建议控制在 500 万 - 1000 万行，单库表数量控制在 200-300 张以内（超过后，元数据管理、锁竞争会变慢）。
• 业务驱动优先：即使数据量没到阈值，但未来 6-12 个月会快速增长（如预期从 300 万涨到 1500 万），建议提前分库分表（避免后期数据迁移的复杂度）。
• 反例：若表是 “配置表”“字典表”，数据量长期稳定在 10 万以内，无需分库分表（过度设计反而增加复杂度）。

三、分库分表会有什么问题？（核心挑战）

分库分表打破了 “单库单表的完整性”，会引入新的技术难题：

1. 跨库跨表查询 / 聚合难：比如 “查询用户近 1 年的所有订单 + 关联商品信息”，若订单表按用户 ID 分库，商品表按商品 ID 分库，跨库关联查询无法直接用 SQL 实现，需业务层处理。
2. 分布式事务一致性：比如 “创建订单” 需要同时写订单表（分库 A）和库存表（分库 B），若其中一个库写入失败，如何保证 “要么全成功，要么全失败”？单库事务无法覆盖。
3. 全局 ID 生成难：单库可用自增 ID，但分库分表后，多个表不能用自增（会重复），需生成全局唯一的 ID（如雪花算法、UUID）。
4. 数据迁移与扩容复杂：后期需要增加分库分表节点（如从 8 分表扩到 16 分表），需迁移历史数据，且迁移过程中要保证业务不中断。
5. 运维成本高：多个数据库节点需要监控、备份、故障转移，运维复杂度比单库高一个量级。

四、跨库 / 聚合操作怎么做？（解决方案）

针对分库分表后的跨库、聚合问题，行业有成熟的解决思路，核心是 “业务层适配 + 工具支持”：

1. 跨库查询 / 关联：优先 “避免跨库”，其次 “业务层拆分”

• 设计阶段规避：尽量让关联表按同一字段分片（如订单表和订单明细表都按 “订单 ID” 分库，用户表和用户地址表都按 “用户 ID” 分库），确保关联查询在同一库内。

• 业务层拆分查询：若必须跨库，分两步处理：

  例：查询 “用户 ID=123 的订单及关联商品”（订单表按用户 ID 分库，商品表按商品 ID 分库）：

  ① 先查订单表（找到用户 123 的所有订单，获取商品 ID 列表）；

  ② 再查商品表（根据商品 ID 列表，批量获取商品信息）；

  ③ 业务层将订单和商品数据拼接，返回结果。

• 工具支持：用 Sharding-JDBC 等中间件的 “跨库关联” 能力（本质是中间件帮你做了拆分查询 + 结果合并），但性能比单库关联差，需谨慎使用。

2. 聚合操作（如 count、sum、group by）：分 “预计算” 和 “实时计算”

• 实时聚合（小数据量）：中间件（Sharding-JDBC、MyCat）会先在每个分表上执行聚合 SQL（如 count(*)），再将结果汇总（如 8 个分表各返回 1000，总 count=8000）。

  适合数据量小的场景（如 “查询用户近 7 天的订单数”），若数据量大（如 “查询全量订单的 sum (金额)”），实时聚合会很慢。

• 预计算（大数据量）：用 “离线计算 + 实时同步” 的方式，提前计算聚合结果：

  ① 用 Flink/Spark 等计算引擎，每天离线计算 “全量订单的 sum/avg/count”，结果存到 “聚合结果表”（单库）；

  ② 实时新增的数据，通过 Binlog 同步到计算引擎，更新聚合结果；

  ③ 业务查询时，直接查 “聚合结果表”，无需跨库计算（如报表统计、大屏展示常用此方案）。

3. 分布式事务：用 “柔性事务” 平衡一致性和性能

• 非核心业务：用 “最终一致性” 方案（如可靠消息队列）：

  例：订单创建→扣减库存：

  ① 订单表写入成功后，发送 “扣库存” 消息到 MQ；

  ② 库存服务消费消息，扣减库存；

  ③ 若库存扣减失败，MQ 重试，直到成功（需保证库存扣减接口幂等）。

• 核心业务：用 “TCC 事务” 或 “Seata 等中间件”：

  TCC 分三步：Try（预留资源，如冻结库存）→ Confirm（确认执行，如扣减冻结的库存）→ Cancel（回滚，如释放冻结的库存），通过业务层代码保证跨库一致性；

  Seata 等中间件封装了 TCC、SAGA 等模式，降低开发成本（如电商订单支付场景常用 Seata）。

五、总结

分库分表是 “业务发展到一定阶段的必然选择”，但不是银弹 ——能不分就不分，要分就早分。设计时需优先规避跨库、分布式事务等复杂问题，选择成熟的中间件（如 Sharding-JDBC）降低开发和运维成本，同时做好数据扩容、监控的预案。我们项目中，订单表按 “用户 ID 哈希” 分 8 库 16 表，核心解决了 “千万级订单的查询和写入性能问题”，跨库操作通过 “业务层拆分 + 预计算” 处理，整体性能和稳定性满足了业务需求。

分库分表之后，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 或其他字段的哈希值来决定数据的存储位置。这种方法看似能解决大规模数据的存储和查询性能问题，但在实际操作中会带来一些挑战和问题。

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 去搞的，但还是要知道

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. 主库写入 binlog（Binary Log）

  • 主库上的所有增删改操作（DDL/DML），都会被记录到 binlog 中，形成一条条事件（event）。
  • binlog 是逻辑日志，记录了 SQL 或行级别的更改。

  2. 从库读取 relay log（中继日志）

  • 从库会启动一个 I/O 线程，连接主库，请求新的 binlog。
  • 主库的 binlog dump 线程 会把 binlog 内容发送给从库。
  • 从库的 I/O 线程把接收到的内容写入 relay log（中继日志）。

  3. 从库重放 relay log

  • 从库启动 SQL 线程，从 relay log 里取出日志，执行里面的 SQL/行事件，最终更新从库数据。

一旦你提及“一主多从”，面试官很容易设陷阱问你：那大促流量大时，是不是只要多增加几台从库，就可以抗住大促的并发读请求了？

当然不是。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

其实，你从 MySQL 主从复制过程也能发现，MySQL 默认是异步模式：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。

而这时，面试官一般会追问你“**MySQL 主从复制还有哪些模型？”**主要有三种。

• 同步复制：事务线程要等待所有从库的复制成功响应。

• 异步复制：事务线程完全不等待从库的复制成功响应。

• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。

这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

MySQL 主从复制是基于 binlog 的异步过程，主库写入 binlog，从库通过 relay log 重放。复制延迟的原因主要有网络问题、主库压力大、从库执行慢、单线程复制瓶颈、大事务等。 复制延迟会导致读写分离场景下的数据不一致。 优化方法有：使用 MySQL 5.7+ 的多线程复制，避免大事务，优化从库 SQL 和索引，提升硬件和网络。 一致性上，可以通过 强制读主库、半同步复制、组复制 或 Proxy 层路由策略 来解决。

1. 主从复制原理（简要）

MySQL 主从复制通常基于 binlog：

1. 主库（Master）：把事务操作写入 binlog。
2. 从库 I/O 线程：从主库拉取 binlog，写入 relay log。
3. 从库 SQL 线程：读取 relay log，并在从库重放执行。

因此，从库的数据落后于主库，会存在一定延迟。

2. 延迟的原因

1. 网络延迟：主从之间的网络传输慢。
2. 主库压力大：binlog 写入速度快，从库拉取不过来。
3. 从库 SQL 执行性能差：从库执行 binlog SQL 比主库慢（比如没有合适的索引、单机性能差）。
4. 单线程复制瓶颈（传统复制）：MySQL 5.6 之前，SQL 线程是单线程，执行大事务时延迟严重。
5. 大事务 / 批量更新：一次性更新、删除上百万行，导致 binlog 很大，从库应用时间很长。

3. 一致性问题

• 读写分离下的数据不一致：如果应用在从库读数据，可能读到的是旧数据，因为主库刚写入，从库还没同步。
• 事务一致性问题：主库写入成功，但从库延迟，导致业务逻辑判断错误（比如库存、余额）。

4. 解决思路

（1）减少延迟

• 多线程复制：MySQL 5.7 开始支持 并行复制（基于库级并行、基于组提交并行），大幅降低延迟。
• 优化 SQL
  • 避免大事务，改成小批量操作。
  • 保证主从索引一致，防止从库回放慢。
• 硬件优化：提升从库磁盘/CPU 性能，减少执行耗时。
• 网络优化：使用更低延迟的链路。

（2）一致性保证

• 强制读主库：对于关键数据（如支付、库存），读写都走主库。
• semi-sync 半同步复制
  • 主库写事务时，至少要等一个从库确认收到 binlog 才返回客户端，保证数据至少写到一个从库。
  • 缺点：降低写性能。
• 组复制 / MGR (MySQL Group Replication)
  • 多主复制，保证强一致性，但性能有损耗。
• Proxy 层策略
  • 在中间件（如 MyCat、ShardingSphere、ProxySQL）里，对延迟敏感的查询强制走主库，其它查询走从库。
• 延迟监控 + 降级
  • 通过 SHOW SLAVE STATUS 监控 Seconds_Behind_Master，大于阈值时从库不提供读服务。

• 延时

在设计MySQL数据库时，有许多方面需要注意，以确保数据库的性能、可扩展性、安全性和可维护性

• 表结构设计：规范化、合适的数据类型
• 索引：覆盖索引
• 查询优化：合理使用JOIN
• 分区与分库分表

迁移数据时最核心的挑战：

1. 源库与目标库要保持一致（全量 + 增量）。
2. 迁移过程不中断业务（不停机，业务可读写）。
3. 最终一致性（迁移完成后保证数据不丢、不重、不错）。

1. 常见方案步骤

步骤 1：全量迁移

• 使用工具（如 mysqldump、mydumper、pt-archiver，或大数据同步工具如 Canal、Debezium、DataX、DTS）将源库的历史数据 批量导入到目标库。
• 这一步通常需要 只读快照 或 事务隔离 保证导出一致性。

步骤 2：增量同步

• 全量迁移的过程中，源库业务仍在写入，这部分数据必须捕捉并同步。
• 常见做法是基于 binlog（MySQL）或者 WAL（Postgres），利用 CDC（Change Data Capture）机制 实时同步增量数据到目标库。
• 代表工具：
  • MySQL → Canal、Debezium、Maxwell
  • Kafka + sink → 目标库

步骤 3：双写验证（可选）

• 在迁移过程中，业务层可以同时写 源库和目标库，然后通过 校验服务 或 对账机制（hash 校验、抽样比对）确认一致性。

步骤 4：流量切换（灰度迁移）

• 在验证数据无误后，将业务读写请求逐步切换到新库。
• 可以采用 读流量先切，再切写流量，避免一次性切换导致大故障。
• 这里推荐 蓝绿发布 或 双活切换。

步骤 5：确认一致性并下线旧库

• 切换后短时间保留双写或增量同步，待验证无误后下线旧库。

2. 保证一致性的关键点

• 全量 + 增量结合：先拷贝静态数据，再捕获实时变化。
• 校验机制：对关键表做 row count + checksum/hash 校验，确保无丢失。
• 幂等性：迁移程序必须保证重复消费 binlog 时不会产生脏数据。
• 最终一致性：接受迁移过程中存在短暂延迟，但最终必须对齐。
• 流量切换要平滑：避免一次性大规模切换引发不可控风险。

3. 常见工具链

• MySQL：pt-online-schema-change、gh-ost（用于表结构变更不停机）
• 数据迁移/同步：Canal、Debezium、Maxwell、DataX、DTS（阿里云）
• 消息队列中转：Kafka（承接 binlog 流，再同步到目标库）

数据库设计中的三个范式（3NF）是用于规范数据库的结构，以减少数据冗余和提高数据的一致性。

• 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
• 第二范式（2NF）：第二范式在第一范式的基础上进一步规范化，要求表中的每一列都与主键直接相关，而不是间接相关
• 第三范式（3NF）：第三范式要求列之间没有传递依赖，即非主键列之间不能相互依赖。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

在 mysql 中 limit 可以实现快速分页，但是如果数据到了几百万时我们的 limit 必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器

当一个表数据有几百万的数据的时候成了问题！

日常分页SQL语句

select id,name,content from users order by id asc limit 100000,20

扫描100020行

如果记录了上次的最大ID

 select id,name,content from users where id>100073 order by id asc limit 20

扫描 20 行。

总数据有500万左右，以下例子

select * from wl_tagindex where byname='f' order by id limit 300000,10 

执行时间是 3.21s

优化后：

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的计算开销。但是，综合来说，这是一个比较好的解决方案。

如果字段里面有大字段(text,blob)类型的，而且这些字段的访问并不多，这 时候放在一起就变成缺点了。 MYSQL 数据库的记录存储是按行存储的，数据 块大小又是固定的(16K)，每条记录越小，相同的块存储的记录就越多。此 时应该把大字段拆走，这样应付大部分小字段的查询时，就能提高效率。当需 要查询大字段时，此时的关联查询是不可避免的，但也是值得的。拆分开后， 对字段的 UPDAE 就要 UPDATE 多个表了

MySQL在处理大型数据集时，性能瓶颈的出现并非仅取决于数据量的大小，还与硬件配置、表结构设计、索引情况、查询复杂度和并发访问量等多种因素密切相关。因此，很难给出一个精确的数据量阈值来确定何时会出现瓶颈。

一般情况下，以下情况可能会导致MySQL产生性能瓶颈：

1. 单表数据量过大：
   • 数据量级别：当单表记录数达到数百万到数千万时，查询性能可能会明显下降。
   • 影响因素：如果缺乏合理的索引和优化，查询速度会受到显著影响。
2. 索引设计不合理：
   • 缺少必要索引：没有为常用查询添加索引，导致全表扫描。
   • 过多索引：索引过多会增加写入和更新的开销。
   • 索引碎片：频繁的插入和删除操作会导致索引碎片化。
3. 硬件资源限制：
   • 内存不足：无法将常用数据缓存到内存中，导致频繁的磁盘I/O。
   • 磁盘性能：传统HDD的读写速度较慢，可能成为瓶颈。
   • CPU性能：复杂查询和高并发需要更高的CPU处理能力。
4. 高并发访问：
   • 连接数过多：大量的并发连接会消耗系统资源，导致性能下降。
   • 锁竞争：高并发写操作会导致锁竞争，影响事务的执行效率。
5. 查询复杂度高：
   • 复杂的JOIN操作：多表关联查询会增加数据库的计算负担。
   • 未优化的SQL语句：如使用SELECT *或缺少条件过滤。
6. 配置参数不当：
   • 默认配置不适合大数据量：需要根据业务场景调整MySQL的配置参数，如innodb_buffer_pool_size。
   • 连接池设置不合理：可能导致资源浪费或不足。
7. 事务和锁机制的影响：
   • 长事务：长时间占用锁资源，阻塞其他事务。
   • 死锁问题：不合理的事务管理可能导致死锁。

SQL注入是一种常见且危险的安全漏洞，但有几种有效的方法可以防止它。以下是解决SQL注入问题的主要方法：

1. 使用参数化查询（预处理语句）：

   这是防止SQL注入最有效和推荐的方法。参数化查询将SQL语句和数据分开处理，从而防止恶意输入被解释为SQL命令。

   不安全的 SQL 查询：

   String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

   攻击者可以通过输入 " OR "1" = "1 这样类似的内容绕过验证，导致 SQL 注入。

   使用预编译语句的安全查询：

   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. 当然，还有些 统一编码、定期安全审计、保持软件更新 等措施

要以每 10 岁为一组统计用户数量，可以使用 FLOOR 函数（向下取整）对用户的年龄进行分组，然后使用 GROUP BY 和 COUNT(*) 来统计每个年龄段的用户数量

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);

这种方法比较多，我用最简单的， 使用 LEFT JOIN 和 IS NULL

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

   SELECT DISTINCT column_name FROM table_name
   ORDER BY column_name DESC
   LIMIT 1 OFFSET 1

   这个查询先按降序排列,然后跳过第一个结果(OFFSET 1),取下一个结果(LIMIT 1)

2. 使用子查询

   SELECT MAX(column_name)
   FROM table_name
   WHERE column_name < (SELECT MAX(column_name) FROM table_name)

3. 使用 DENSE_RANK() 窗口函数【MySQL 8.0+ 支持】

   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;

排名问题是SQL面试的经典题型，考察对窗口函数和聚合查询的掌握：

💻 排名问题SQL实现：

-- ===== 基础排名问题 =====

-- 题目1：查询各科目成绩前3名的学生
CREATE TABLE student_scores (
    student_id INT,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score INT,
    exam_date DATE
);

-- 插入测试数据
INSERT INTO student_scores VALUES
(1, '张三', '数学', 95, '2024-01-15'),
(2, '李四', '数学', 88, '2024-01-15'),
(3, '王五', '数学', 92, '2024-01-15'),
(4, '赵六', '数学', 85, '2024-01-15'),
(5, '张三', '英语', 90, '2024-01-15'),
(6, '李四', '英语', 95, '2024-01-15'),
(7, '王五', '英语', 88, '2024-01-15');

-- 解法1：使用窗口函数
WITH ranked_scores AS (
    SELECT 
        student_id,
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) as rn,
        RANK() OVER (PARTITION BY subject ORDER BY score DESC) as rnk,
        DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) as dense_rnk
    FROM student_scores
)
SELECT 
    student_id,
    student_name,
    subject,
    score,
    rn as row_number_rank,
    rnk as rank_with_gaps,
    dense_rnk as dense_rank
FROM ranked_scores
WHERE rn <= 3
ORDER BY subject, score DESC;

-- 题目2：查询每个部门薪资前20%的员工
WITH salary_percentiles AS (
    SELECT 
        employee_id,
        employee_name,
        department,
        salary,
        PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as percentile_rank
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROUND(percentile_rank * 100, 2) as top_percentage
FROM salary_percentiles
WHERE percentile_rank <= 0.2  -- 前20%
ORDER BY department, salary DESC;

连续问题是考察逻辑思维的重要题型：

💻 连续问题SQL实现：

-- ===== 连续登录问题 =====

-- 题目：找出连续登录3天以上的用户
CREATE TABLE user_login_log (
    user_id INT,
    login_date DATE,
    login_time TIMESTAMP
);

-- 解法：使用ROW_NUMBER()找连续区间
WITH date_with_row AS (
    SELECT DISTINCT
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn
    FROM user_login_log
),
consecutive_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) as group_date
    FROM date_with_row
),
consecutive_counts AS (
    SELECT 
        user_id,
        group_date,
        COUNT(*) as consecutive_days,
        MIN(login_date) as start_date,
        MAX(login_date) as end_date
    FROM consecutive_groups
    GROUP BY user_id, group_date
)
SELECT 
    user_id,
    consecutive_days,
    start_date,
    end_date
FROM consecutive_counts
WHERE consecutive_days >= 3
ORDER BY user_id, start_date;

-- 题目：股票连续上涨问题
WITH price_changes AS (
    SELECT 
        stock_code,
        trade_date,
        closing_price,
        LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date) as prev_price,
        CASE 
            WHEN closing_price > LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date)
            THEN 1 ELSE 0
        END as is_up
    FROM stock_prices
),
consecutive_ups AS (
    SELECT 
        stock_code,
        COUNT(*) as up_days,
        MIN(trade_date) as start_date,
        MAX(trade_date) as end_date
    FROM (
        SELECT 
            stock_code,
            trade_date,
            SUM(CASE WHEN is_up = 0 THEN 1 ELSE 0 END) 
                OVER (PARTITION BY stock_code ORDER BY trade_date) as group_id
        FROM price_changes
        WHERE is_up = 1
    ) grouped
    GROUP BY stock_code, group_id
)
SELECT * FROM consecutive_ups
WHERE up_days >= 3
ORDER BY stock_code, start_date;

实际业务中的复杂查询场景：

💻 业务场景SQL实现：

-- ===== 电商用户行为分析 =====

-- 题目：找出"流失预警"用户（最后购买>30天，但历史活跃）
WITH user_purchase_stats AS (
    SELECT 
        user_id,
        COUNT(*) as total_orders,
        SUM(order_amount) as total_spent,
        AVG(order_amount) as avg_order_value,
        MAX(order_date) as last_purchase_date,
        DATEDIFF(NOW(), MAX(order_date)) as days_since_last_purchase
    FROM orders
    WHERE order_status = 'completed'
    GROUP BY user_id
),
user_segments AS (
    SELECT 
        *,
        CASE 
            WHEN total_orders >= 10 AND avg_order_value >= 200 THEN 'VIP客户'
            WHEN total_orders >= 5 AND total_spent >= 500 THEN '优质客户'
            WHEN total_orders >= 2 THEN '普通客户'
            ELSE '新客户'
        END as customer_segment,
        CASE 
            WHEN days_since_last_purchase > 60 THEN '高风险流失'
            WHEN days_since_last_purchase > 30 AND total_orders >= 5 THEN '流失预警'
            WHEN days_since_last_purchase <= 7 THEN '活跃用户'
            ELSE '一般用户'
        END as churn_risk_level
    FROM user_purchase_stats
)
SELECT 
    customer_segment,
    churn_risk_level,
    COUNT(*) as user_count,
    ROUND(AVG(total_orders), 2) as avg_orders,
    ROUND(AVG(total_spent), 2) as avg_total_spent
FROM user_segments
GROUP BY customer_segment, churn_risk_level
ORDER BY 
    FIELD(customer_segment, 'VIP客户', '优质客户', '普通客户', '新客户'),
    FIELD(churn_risk_level, '高风险流失', '流失预警', '一般用户', '活跃用户');

-- ===== 树形递归查询 =====

-- 题目：查询员工的所有下属
WITH RECURSIVE subordinates AS (
    -- 锚点：直接下属
    SELECT 
        emp_id,
        emp_name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id = 1001  -- 查询员工1001的下属
    
    UNION ALL
    
    -- 递归：下属的下属
    SELECT 
        e.emp_id,
        e.emp_name,
        e.manager_id,
        s.level + 1
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.emp_id
    WHERE s.level < 5  -- 限制层级
)
SELECT 
    emp_id,
    emp_name,
    level,
    CONCAT(REPEAT('  ', level-1), emp_name) as hierarchy_display
FROM subordinates
ORDER BY level, emp_id;

核心理念：数据库运维与监控是保障系统稳定运行的关键，及时发现和解决问题能够避免业务中断和数据丢失。

MySQL备份恢复是数据安全的最后一道防线，需要制定完善的备份策略：

💻 备份恢复实战：

-- ===== 逻辑备份（mysqldump）=====

/*
# 完整数据库备份
mysqldump -u root -p --single-transaction --routines --triggers \
         --all-databases > full_backup_$(date +%Y%m%d_%H%M%S).sql

# 单个数据库备份
mysqldump -u root -p --single-transaction --routines --triggers \
         ecommerce > ecommerce_backup_$(date +%Y%m%d_%H%M%S).sql

# 只备份结构不备份数据
mysqldump -u root -p --no-data ecommerce > ecommerce_schema.sql

# 只备份数据不备份结构
mysqldump -u root -p --no-create-info ecommerce > ecommerce_data.sql

# 备份特定表
mysqldump -u root -p ecommerce users orders > tables_backup.sql

# 压缩备份
mysqldump -u root -p --single-transaction ecommerce | gzip > backup.sql.gz
*/

-- ===== 物理备份（MySQL Enterprise Backup / Percona XtraBackup）=====

/*
# 使用Percona XtraBackup进行热备份
# 完整备份
innobackupex --user=backup_user --password=backup_pass /backup/full/

# 增量备份（基于完整备份）
innobackupex --user=backup_user --password=backup_pass \
            --incremental /backup/inc1/ \
            --incremental-basedir=/backup/full/

# 准备备份（应用redo log）
innobackupex --apply-log /backup/full/

# 恢复数据
innobackupex --copy-back /backup/full/
*/

-- ===== 基于binlog的时点恢复 =====

-- 查看binlog位置信息
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- 查看特定时间点的binlog位置
SHOW BINLOG EVENTS IN 'mysql-bin.000001' 
WHERE Start_time >= '2024-01-01 10:00:00'
LIMIT 1;

/*
# 时点恢复步骤
# 1. 恢复最新全备份
mysql -u root -p ecommerce < ecommerce_backup_20240101_020000.sql

# 2. 应用binlog到指定时间点
mysqlbinlog --start-datetime="2024-01-01 02:00:00" \
           --stop-datetime="2024-01-01 09:30:00" \
           mysql-bin.000001 mysql-bin.000002 | mysql -u root -p ecommerce

# 3. 跳过错误事务，继续恢复
mysqlbinlog --start-position=1000 --stop-position=2000 \
           mysql-bin.000001 | mysql -u root -p ecommerce
*/

-- ===== 自动化备份脚本示例 =====

/*
#!/bin/bash
# MySQL自动备份脚本

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
LOG_FILE="$BACKUP_DIR/backup.log"
RETENTION_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
echo "$(date): Starting backup..." >> $LOG_FILE
mysqldump -u backup_user -p$BACKUP_PASSWORD \
         --single-transaction --routines --triggers \
         --all-databases | gzip > $BACKUP_DIR/full_backup_$DATE.sql.gz

if [ $? -eq 0 ]; then
    echo "$(date): Backup completed successfully" >> $LOG_FILE
    
    # 清理过期备份
    find $BACKUP_DIR -name "full_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "$(date): Old backups cleaned" >> $LOG_FILE
else
    echo "$(date): Backup failed!" >> $LOG_FILE
    # 发送告警邮件
    echo "MySQL backup failed at $(date)" | mail -s "Backup Alert" admin@company.com
fi
*/

-- ===== 备份验证 =====

-- 验证备份完整性的存储过程
DELIMITER //
CREATE PROCEDURE ValidateBackup(IN backup_file VARCHAR(255))
BEGIN
    DECLARE backup_size BIGINT;
    DECLARE table_count INT;
    DECLARE record_count BIGINT;
    
    -- 这里应该包含备份文件大小检查
    -- 恢复到临时数据库进行验证
    -- 检查表数量和记录数量
    
    SELECT 
        COUNT(*) as tables,
        SUM(TABLE_ROWS) as total_rows
    INTO table_count, record_count
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'backup_validation_db';
    
    SELECT 
        backup_file,
        table_count,
        record_count,
        CASE 
            WHEN table_count > 0 AND record_count > 0 THEN 'VALID'
            ELSE 'INVALID'
        END as validation_result;
END //
DELIMITER ;

MySQL监控需要关注多个维度的指标，及时发现性能瓶颈和潜在问题：

💻 监控和故障排查：

-- ===== 关键性能指标监控 =====

-- 连接状态监控
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE VARIABLE_NAME
        WHEN 'Max_used_connections' THEN 
            CONCAT(ROUND(VARIABLE_VALUE / @@max_connections * 100, 2), '%')
        ELSE ''
    END as usage_percentage
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Connections', 'Max_used_connections', 'Threads_connected', 'Threads_running'
);

-- 查询性能指标
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE VARIABLE_NAME
        WHEN 'Questions' THEN CONCAT(ROUND(VARIABLE_VALUE / @@uptime, 2), ' QPS')
        WHEN 'Slow_queries' THEN 
            CONCAT(ROUND(VARIABLE_VALUE / 
                   (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Questions') * 100, 4), '%')
        ELSE ''
    END as rate_info
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Questions', 'Queries', 'Slow_queries', 'Select_scan', 'Sort_merge_passes'
);

-- InnoDB缓冲池监控
SELECT 
    'Buffer Pool Hit Rate' as metric,
    ROUND((
        1 - (reads.VARIABLE_VALUE / requests.VARIABLE_VALUE)
    ) * 100, 2) as hit_rate_percent,
    CASE 
        WHEN (1 - (reads.VARIABLE_VALUE / requests.VARIABLE_VALUE)) * 100 >= 99 THEN '优秀'
        WHEN (1 - (reads.VARIABLE_VALUE / requests.VARIABLE_VALUE)) * 100 >= 95 THEN '良好'
        ELSE '需要优化'
    END as status
FROM 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') reads,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') requests;

-- ===== 实时性能分析 =====

-- 当前运行的查询
SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) as QUERY_SAMPLE,
    CASE 
        WHEN TIME > 60 THEN '长时间运行'
        WHEN TIME > 10 THEN '需要关注'
        ELSE '正常'
    END as status
FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 锁等待分析
SELECT 
    r.trx_id AS waiting_trx,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) as wait_time_seconds
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- ===== 故障排查查询 =====

-- 查找占用空间最大的表
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS size_mb,
    TABLE_ROWS,
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) AS index_size_mb,
    ROUND((DATA_LENGTH / 1024 / 1024), 2) AS data_size_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC
LIMIT 20;

-- 分析表碎片化情况
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    ROUND((DATA_LENGTH / 1024 / 1024), 2) as data_mb,
    ROUND((DATA_FREE / 1024 / 1024), 2) as free_mb,
    ROUND((DATA_FREE / (DATA_LENGTH + DATA_FREE)) * 100, 2) as fragmentation_percent
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND DATA_FREE > 0
ORDER BY fragmentation_percent DESC;

• 🏗️ 基础与架构：MySQL架构组件、DDL/DML/DCL区别、SQL语法和最佳实践
• 🗄️ 存储引擎：InnoDB vs MyISAM特性、内存结构、磁盘结构、缓冲池机制
• 🔍 索引机制：B+树原理、索引类型、联合索引最左前缀、覆盖索引、索引失效场景
• 🔒 事务与锁：ACID特性实现、隔离级别对比、锁机制、MVCC原理、死锁处理
• 📊 查询优化：JOIN类型优化、窗口函数、执行计划分析、数据类型选择
• 📝 日志系统：redo log、undo log、binlog机制、WAL原理、恢复策略
• ⚡ 性能调优：慢查询分析、参数调优、缓存策略、硬件优化
• 🚀 分库分表：主从复制、读写分离、分片策略、数据迁移、集群部署
• 💻 SQL实战：复杂查询编写、存储过程、函数、CTE递归查询
• 🔧 运维监控：备份恢复、监控指标、故障排查、容量规划、日常维护

1. 基础概念题：先说定义和原理，再举具体应用例子，最后分析优缺点和适用场景
2. 性能优化题：分析性能瓶颈，提出具体优化方案，说明效果评估方法
3. 架构设计题：从业务需求出发，考虑数据量和并发量，选择合适的架构方案
4. 故障排查题：描述排查思路和工具，定位根本原因，提供解决方案和预防措施

1. 性能优先：合理设计索引，优化查询语句，配置合适的参数，选择适合的存储引擎
2. 数据安全：事务保证一致性，完善的备份恢复策略，严格的权限控制机制
3. 高可用设计：消除单点故障，实现故障自动切换，多层次的监控告警体系
4. 可扩展性：分库分表应对数据增长，读写分离提升并发能力，集群化部署
5. 运维规范：标准化的部署流程，完善的监控体系，及时的故障响应机制

• MySQL官方文档：深入学习存储引擎原理、性能调优最佳实践
• 经典书籍：《高性能MySQL》、《MySQL技术内幕》、《数据库系统概念》
• 开源工具：Percona Toolkit、pt-query-digest、MySQL Workbench使用
• 监控方案：Prometheus+Grafana、Zabbix、云监控平台实践
• 新版本特性：MySQL 8.0新特性、JSON支持、窗口函数、CTE等

记住：数据库是应用系统的核心，扎实的MySQL基础和丰富的优化经验是后端工程师的核心竞争力！ 🚀