MySQL专题总结

性感的大表哥

已于 2025-10-29 10:28:27 修改

阅读量587

点赞数 28

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库面试

于 2025-10-29 10:24:20 首次发布

本文链接：https://blog.csdn.net/heihei1314A/article/details/154061834

数据库专栏收录该内容

1 篇文章

订阅专栏

前言

大类先是集中式、分布式，然后才是关系型、非关系型。

面试点：sql优化、锁、事务、索引原理、底层执行原理、分布式（CAP）环境下常见问题解决方案。

一、SQL优化

1、核心优化思路 (首先亮出这个，格局打开)

不要一上来就说“加索引”，要先表达你的优化方法论，让面试官知道你不是只会零散技巧。

优化金字塔（自上而下，收益由大到小）：
- 架构与设计优化（最高效）：包括是否引入了缓存(Redis)、是否做了读写分离、分库分表、数据库选型等。这是解决根本性问题。
- SQL语句与索引优化（性价比最高）：优化慢查询、合理使用索引。这是开发人员最常接触的。
- 存储引擎与表结构优化：选择合适的存储引擎（InnoDB vs MyISAM）、设计合理的表结构、数据类型选择。
- 数据库配置优化（my.cnf调优）：如缓冲池大小、连接数等。注意：除非是DBA角色，否则谨慎提及，因为调参需要深厚功底和线上监控数据，容易给自己挖坑。
优化流程（体现你的专业性）：
- 发现问题：使用慢查询日志 (slow_query_log)、性能监控工具（如Prometheus）、数据库自带命令（如SHOW PROCESSLIST）来定位慢SQL。
- 分析问题：使用 EXPLAIN 命令是核心中的核心，还有 Trace 分析，分析SQL的执行计划。
- 解决问题：根据EXPLAIN的结果，针对性采用下述方法。
- 验证效果：优化后再次使用EXPLAIN分析或直接测试性能。

2、具体优化方法 (干货部分，结合EXPLAIN)

1. 索引优化（最常用）

为WHERE条件、JOIN的字段、ORDER/GROUP BY的字段创建索引。
前缀索引：对于很长的字符列（如VARCHAR(200)），可以只对前N个字符创建索引，节约空间。ALTER TABLE table_name ADD INDEX idx_name (column_name(N));
覆盖索引：索引包含了查询所需的所有字段，无需回表。EXPLAIN的Extra字段会出现 Using index。这是非常好的优化。
- 示例：SELECT id, name FROM users WHERE age = 25; 如果索引是 (age, name, id) 或 (age) 包含了id和name，就是覆盖索引。
最左前缀原则：联合索引 (a, b, c) 相当于创建了 (a), (a,b), (a,b,c) 三个索引。查询条件必须从最左边开始才能使用索引。
避免索引失效：
- 不要在索引列上做计算或函数操作：WHERE YEAR(create_time) = 2023 （❌坏）-> WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' （✅好）
- 避免隐式类型转换：user_id是字符串类型，却用WHERE user_id = 123（数字），会导致索引失效。
- 避免使用 !=、NOT IN、IS NULL、IS NOT NULL（并非绝对，但MySQL优化器可能选择全表扫描）。
- LIKE 以通配符开头：LIKE '%abc' 无法使用索引，LIKE 'abc%' 可以。

2. SQL语句优化

避免使用 SELECT \*：只取需要的字段，减少网络传输和数据量，更可能用到覆盖索引。
用 JOIN 代替子查询：通常JOIN的性能优于子查询（但并非绝对，现代MySQL版本对子查询优化已很好，但JOIN写法更直观）。
批量操作：INSERT INTO table VALUES (a), (b), (c)...; 比多条INSERT语句快得多。
分解大查询：一个大查询可能会锁住很多数据，阻塞其他查询。可以将其拆分成多个小查询。

优化LIMIT分页：LIMIT 1000000, 20 会先读取1000020条记录，然后扔掉前1000000条，非常慢。

优化方案：使用延迟关联。

-- 慢
SELECT * FROM posts ORDER BY created_time DESC LIMIT 1000000, 20;

-- 快 (先通过覆盖索引查到主键ID，再回表)
SELECT * FROM posts
INNER JOIN (SELECT id FROM posts ORDER BY created_time DESC LIMIT 1000000, 20) AS t
USING (id);

3. 表结构与存储引擎优化

选择合适的数据类型：用INT而不是VARCHAR存储数字；用DATETIME存储时间；尽可能使用最小的数据类型（如TINYINT而非INT）。
整型数据没有负数：直接用 UNSIGNED 无符号类型，容量可以扩大一倍。
字符串类型选择：如果长度差不多就用char，如果长度不确定就用varchar。
尽量定义 NOT NULL：NULL值使索引和值比较都更复杂，并占用更多空间。
使用枚举(ENUM)或集合(SET)：对于有限状态的字段，如性别(gender)，使用ENUM('male', 'female')更节省空间。
避免使用显示长度：如int(10)，直接用int就行。
范式与反范式的权衡：
- 范式化（减少冗余）：写操作更快，表更小。
- 反范式化（适当冗余）：避免频繁JOIN，用空间换时间，读操作更快。根据读写比例决定。
存储引擎：默认使用 InnoDB。它支持行级锁（并发性好）、事务和外键。除非有非常特殊的只读场景，否则不再使用MyISAM。

数据库三范式：
第一范式 (1NF)：字段不可再分。
第二范式 (2NF)：不能存在部分依赖（非主键字段必须完全依赖于整个主键）。
第三范式 (3NF)：不能存在传递依赖（非主键字段必须直接依赖于主键）。

3、实战：如何用EXPLAIN分析（面试必考）

当被问到“你说你看EXPLAIN，那你看哪些字段？”时，可以这样回答：

我主要关注以下几个关键字段：

type（非常重要）：访问类型，从好到坏：
- const/system：通过主键或唯一索引一次就找到，最佳。
- eq_ref：JOIN时使用主键或唯一索引关联。
- ref：使用普通索引。
- range：索引范围扫描（BETWEEN, IN, >等）。
- index：全索引扫描（比全表扫描好，因为索引文件通常更小）。
- ALL：全表扫描，最差，需要优化。
possible_keys & key：可能用到的索引和实际用到的索引。如果key为NULL，说明没用到索引。
rows：预估需要读取的行数。值越小越好。
Extra（包含重要信息）：
- Using filesort：MySQL需要额外的一次排序操作，而不是用索引排序。通常需要优化。
- Using temporary：使用了临时表来存储中间结果，常见于GROUP BY和ORDER BY。需要优化。
- Using index：使用了覆盖索引，非常好！
- Using where：在存储引擎检索行后进行了过滤。

4、面试回答范例

“关于SQL优化，我一般会遵循一个自上而下的思路。首先会从架构层面考虑，比如是否需要引入缓存或者读写分离。如果问题出在SQL本身，我的常规流程是：先通过慢查询日志或监控定位到具体的慢SQL，然后使用EXPLAIN命令去分析它的执行计划。”

“我会重点看EXPLAIN的type字段是否出现了全表扫描(ALL)，key字段是否用到了索引，rows需要读取的行数，以及Extra字段是否有Using filesort或Using temporary这样的危险信号。”

“具体的优化手段我常用的有：

索引方面：确保WHERE、JOIN、ORDER BY字段有索引，注意最左前缀原则，避免索引失效的写法，比如不对索引列做函数操作。
SQL语句方面：坚决不用SELECT *，会考虑用JOIN改写复杂的子查询，对于深分页会使用延迟关联的技巧来优化。
表结构方面：会选择合适的数据类型，并会根据业务场景权衡范式和反范式设计。”

“最后，我印象比较深的一个优化案例是（这里准备一个你自己的真实案例），当时一条分页查询非常慢，EXPLAIN一看是ALL全表扫描还有Using filesort。我发现是因为ORDER BY的字段没有索引，加上索引后变成了index类型，并且因为用了覆盖索引，Extra里出现了Using index，查询速度提升了几十倍。”

这样回答，既体现了你的体系化思维，又展示了扎实的具体技能和实战经验，绝对是面试官想要的答案。

案例：

1、某系统接口，10分钟一次，一天144次，一次5000条左右的数据，一天大概72万，一个月大概2160万。用时间范围查一天都很慢，用延迟关联（join）优化。

这么大规模的数据，系统表只存最近一个月，其他的放历史表（算不算冷热分离）；

2、某系统有全国人口信息表数据很大，后台有很多定时任务要跟他关联，每次系统重启跑一会就挂了，通过控制返回字段数量，加索引方式解决。

二、ACID、锁、事务

先来深刻理解三者的关系：

ACID 是目标，是事务需要实现的四个理想属性（原子性、一致性、隔离性、持久性）。
事务是舞台，是实现ACID属性的基本工作单元。
锁是工具，是数据库为了保证事务ACID特性（主要是隔离性）而采用的核心并发控制机制。

锁是一种并发控制机制，用于管理多个事务如何同时访问同一资源（如一行数据、一个表）。

ACID属性	实现机制	锁在其中扮演的角色
原子性 (A)	Undo Log	锁不直接实现原子性，但回滚时需要释放锁。
一致性 ©	由应用和数据库共同保证	锁通过保证隔离性，间接帮助实现一致性。
隔离性 (I)	锁 + MVCC	锁是主力！通过S锁、X锁、间隙锁等，控制并发事务的访问行为，制造出隔离的假象。
持久性 (D)	Redo Log	锁不直接实现持久性。

锁分类

从性能上分为乐观锁(用版本对比来实现，每次操作之前先对比版本号；没有锁等待) 和悲观锁（有锁等待）。

从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)。

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

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分，分为表锁、行锁、间隙锁。

“行锁有三种模式：共享锁S和排他锁X，以及为了高效管理表锁和行锁关系而引入的意向锁IS和IX。”
“行锁的算法有记录锁、间隙锁和临键锁，其中间隙锁是解决幻读的关键。”

间隙锁是在可重复读隔离级别下才会生效。

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

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

锁机制常见问题：

“锁机制常见的问题有死锁，InnoDB有死锁检测和超时机制…”
“还有热点行更新问题，可以通过应用层排队或数据拆分来解决…”

虽然InnoDB 本身有死锁检测，但还是要开启死锁检测

-- 检查死锁监控状态
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

-- 开启死锁监控
SET GLOBAL innodb_print_all_deadlocks = 1;

-- 获取死锁详情（如何快速阅读？抓取关键信息，找到两个事务：(1) TRANSACTION 和 (2) TRANSACTION）
SHOW ENGINE INNODB STATUS\G;

锁优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

合理设计索引，尽量缩小锁的范围。

尽可能减少检索条件范围，避免间隙锁。

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

事务：

丢失或脏写：两个事务更新同一条数据的同一个值，出现了覆盖更新的情况。

脏读：事务A读到了事务B已经修改但尚未提交的数据。

不可重复读：事务A内部的相同查询语句，在不同时刻读出的结果不一致。

幻读：事务A读到了事务B提交的新增数据。

常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;

设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’;

MySQL默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用MySQL设置的隔离级别，如果Spring设置了就用已经设置的隔离级别.

总结关系图：

三、索引原理

索引是帮助MySQL高效获取的排序好的数据结构。

1、索引数据结构

二叉树（数据不连续的情况下可以使用，数据连续情况下会有单边增长的问题）

红黑树（数据量大的情况下，树的高度会很高，查询耗时）

HasH表（很多时候Hash比B+Tree更高效，但是仅能满足“=”和“in”，不支持范围查询，而且还有hash冲突问题）
在这里插入图片描述

B-Tree

总结：

a：叶节点具有相同的深度，叶节点的指针为空

b：所有索引元素不重复

c：节点中的数据索引从左到右递增排列

B+Tree(B-Tree变种)

总结：

a：非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

b：叶子节点包含所有索引字段

c：叶子节点的data存放的是索引所在行的磁盘文件地址

d：叶子节点用指针连接，提高区间访问的性能

e：叶子节点的数据是排序好得，并且用指针连接，提高区间访问得性能；虽然b-tree得叶子节点数据也是排序好的，但是没有用指针连接，所以每次都要从跟节点开始查找

扩展点：

page size 一页的大小是16384，也就是16k，16*1024=1170b；这个值是可以设置的，如果太大的话每次加载到内存的数据就太多了
数据的高度是3，如果放满的话可以存多少数据？1170*1170*16 大概是二千多万
树的高度每加一层可以多放多少数据？
叶子节点的data存放的是索引所在行，链的数据，有就是一整行数据；一个节点的大小是多少，大概是1k

2、存储引擎

Myisam存储引擎

磁盘存储结构：

frm：存储数据表结构

myd：存储数据文件

myi：存储索引

数据查找：

查找的时候先加载myi文件，然后以B+TREE的结构进行构建，然后找到索引行所在的磁盘文件地址，最后根据磁盘文件地址在myd文件中找到数据

索引文件和数据文件是分离的（非聚集索引，稀疏索引也是一种非聚集索引）

Innodb存储引擎

磁盘存储结构：

frm：存储数据表结构

idb：本身就是按照b+tree组织一个索引结构文件

索引文件和数据文件在一个文件中（聚集索引，叶子节点包含了完整的数据记录）

Using filesort文件排序原理详解

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL的表关联常见有两种算法

Nested-Loop Join 算法

Block Nested-Loop Join 算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行**)。因此整个过程扫描了 **200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

2、 基于块的嵌套循环连接 **Block Nested-Loop Join(**BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

Extra 中的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高.

3、问题整理

> 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

有主键的情况下InnoDB会根据主键构建一个B+TREE树；首先整型空间占用小，其次在查找过程中进行大小计算更合适。没有主键的情况InnoDB会找出一个所有数据都不相等的列来构建B+TREE树；如果没有找到合适的列，他会帮你建一个隐藏列来维护一个rowId来组织整张表的数据。

> 主键为什么要自增？

构建树的过程中如果某个行满的情况下，本来应该插在中间的数据现在由于树的平衡而位置发送了变化；自己找个B+TREE树试试感受一下。

> 聚集索引和非聚集索引查找数据哪个更快？

聚集索引找到对应位置就可以直接拿到数据，非聚集索引找到当前行所对应的磁盘文件地址还要跨文件去查找。

> 回表是什么意思？

采用非主键索引查询的情况下，首先是先找到主键索引，然后再根据主键索引去查找数据，这个过程是两次的所以叫回表。

> 为什么非主键索引（二级索引）结构叶子节点存储的是主键值？

一致性和节省空间。

> 联合索引的底层结构长什么样？

索引的最左前缀原理

排好序

假设联合主键索引有三个字段：name、age、position

只有在name先匹配的情况下，age才是排好序的；如果直接从age开始那是没有经过排序的，所以肯定会进行全表扫描

> b-tree和b+tree再构建索引的时候有什么区别？

“第一，数据存储位置不同。B-Tree的所有节点都既存键也存数据；而B+Tree的非叶子节点只充当路由功能，只存放键，所有数据都集中在叶子节点上。这使得B+Tree的树更矮胖，查询的磁盘I/O次数更少。”

“第二，叶子节点结构不同。B+Tree的所有叶子节点用一个双向链表串联起来，而B-Tree没有。这让B+Tree的范围查询效率极高，找到起点后顺序遍历链表即可，而不需要像B-Tree那样复杂地回溯中序遍历。”

“第三，查询性能稳定性不同。B+Tree的任何查询都必须走到叶子节点，性能稳定；而B-Tree的查询性能波动较大。”

四、MVCC机制 + 缓存池BufferPool

MySQL中的MVCC（Multi-Version Concurrency Control，多版本并发控制）是InnoDB存储引擎实现高并发性能的核心机制。它使得读操作通常不会阻塞写操作，写操作也不会阻塞读操作，极大地提升了数据库的并发处理能力。

没有MVCC之前

读-写冲突：一个写操作（UPDATE）会阻塞其他事务的读操作（SELECT）。
写-读冲突：一个读操作（SELECT ... FOR UPDATE）会阻塞其他事务的写操作。

MVCC通过数据多版本和快照读巧妙地解决了这些冲突。

一、MVCC的核心：隐藏字段与Undo Log

InnoDB为每一行记录都隐式地添加了三个重要的字段：

字段名	描述	作用
`DB_TRX_ID`	6字节	事务ID。记录最后一次插入或更新这行记录的事务ID。
`DB_ROLL_PTR`	7字节	回滚指针。指向这条记录上一个版本在Undo Log中的地址。
`DB_ROW_ID`	6字节	行ID。如果表没有定义主键，InnoDB会自动生成一个聚簇索引 based on this。

Undo Log（回滚日志）：

当一条记录被更新时，MySQL不会立即覆盖原数据，而是会先将该记录的旧版本拷贝到Undo Log中。
新的更新操作会生成一个新的记录版本，其DB_ROLL_PTR指针就指向刚才存放在Undo Log中的旧版本记录。
多次更新会形成一条版本链，链头是最新的记录，通过DB_ROLL_PTR可以不断找到更早的历史版本。

版本链的形成过程：
假设初始有一条记录，name='Alice'，由事务Trx10插入。

事务Trx20将其更新为name='Bob'：
- 将name='Alice'拷贝到Undo Log。
- 当前行数据变为：name='Bob', DB_TRX_ID=20, DB_ROLL_PTR -> 指向Undo Log中的'Alice'版本。
事务Trx30又将其更新为name='Charlie'：
- 将name='Bob'拷贝到Undo Log。
- 当前行数据变为：name='Charlie', DB_TRX_ID=30, DB_ROLL_PTR -> 指向Undo Log中的'Bob'版本（该版本又指向'Alice'版本）。

这样就形成了一个基于Undo Log的版本链。

二、一致性视图：ReadView

MVCC的“快照”并不是真的将数据拷贝一份，而是通过ReadView来实现的。

当一个事务执行快照读（普通的SELECT语句）时，InnoDB会为这个事务生成一个一致性读视图（ReadView）。这个ReadView决定了这个事务能看到哪个版本的数据。

一个ReadView主要包含以下关键信息：

m_ids：生成ReadView时，系统中活跃（尚未提交）的所有事务ID列表。
min_trx_id：m_ids中最小的活跃事务ID。
max_trx_id：生成ReadView时，系统应该分配给下一个事务的ID。
creator_trx_id：创建这个ReadView的事务自己的ID。

三、可见性算法：如何判断版本是否可见？

当一个事务要读取一行数据时，它会从最新的版本开始，沿着版本链依次判断每个版本对自己是否可见。判断规则基于当前事务的ReadView和版本上的**DB_TRX_ID**：

当前版本DB_TRX_ID == creator_trx_id?
- 是：说明这个版本是当前事务自己修改的，可见。
当前版本DB_TRX_ID < min_trx_id?
- 是：说明这个版本的事务在生成ReadView时已经提交，可见。
当前版本DB_TRX_ID >= max_trx_id?
- 是：说明这个版本的事务在生成ReadView时还未开始，绝对不可见，需要沿版本链继续查找。
当前版本DB_TRX_ID在m_ids中?
- 是：说明这个版本的事务在生成ReadView时还活跃（未提交），不可见，需要沿版本链继续查找。
- 否：说明这个版本的事务在生成ReadView时已经提交，可见。

这个算法确保了事务只能看到：

在它开始之前就已经提交的数据。
它自己所做的修改。

而看不到：

在它开始之后才提交的数据。
所有未提交的数据。

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取

同一条数据在版本链上的不同版本数据。

四、Innodb引擎SQL执行的BufferPool缓存机制

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。

MySQL这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，（内存操作 + 磁盘顺序IO）同时还能保证各种异常情况下的数据一致性。

随机IO和顺序IO

redo日志和undo日志分别采用顺序IO和随机IO进行读写。redo日志是顺序写的基本不会有删除操作，所以采用顺序IO，顺序IO直接采用偏移量offect进行顺序读取，所以效率比较高。idb文件因为可能有删除等操作，所以不适合顺序读取，所以采用随机IO。

五、高可用方案

1、MySQL主从复制 + Keepalived

这是一个自动故障转移的高可用架构方案；

MySQL主从复制 (Replication)
- 职责：解决数据同步的问题。
- 原理：主库（Master）将数据变更写入二进制日志（Binlog），从库（Slave）的I/O线程从主库拉取Binlog，并写入本地的中继日志（Relay Log），然后从库的SQL线程重放中继日志中的事件，从而实现数据同步。
- 结果：从库拥有和主库几乎一致的数据。但它不负责业务的自动切换。
Keepalived
- 职责：解决IP漂移和故障检测的问题，提供对应用的透明访问。
- 原理：基于VRRP协议，在多台服务器之间虚拟出一个VIP（Virtual IP，如 192.168.1.100）。应用程序不直接连接真实的主库IP，而是连接这个VIP。
- 结果：Keepalived会保证只有一台机器（即当前的主库）持有这个VIP并对外提供服务。当这台机器宕机时，VIP会自动"漂移"到另一台备用的机器上。

所以，完整的流程是：

数据流：通过 MySQL复制 从主库同步到从库。
访问流：应用程序通过 Keepalived提供的VIP 访问当前的主库。

六、分库分表

一、如何对数据库进行拆分？

水平拆分的意思，就是把一个表的数据给弄到多个库的多个表里去，但是每个库的表结构都一样，只不过每个库表放的数据是不同的。
垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表，或者是多个库上去。每个库表的结构都不一样，每个库表都包含部分字段。

二、用过哪些分库分表中间件，各有什么优缺点？

Sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级需要各个系统都升级后再发布。

Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

通常来说，这两个方案其实都可以选用，
1、但是我个人建议中小型公司选用 Sharding-jdbc，client 层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；

2、但是中大型公司最好还是选用 Mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 Mycat，然后大量项目直接透明使用即可。

三、如何设计才可以让系统从未分库分表动态切换到分库分表上？

1、停机迁移（说是不常用）

2、双写迁移方案：

1、在之前所有操作数据库的地方，加上对新库的操作，也就是数据会同时写两个库。
2、编写数据同步工具，从老库中读出来往新库中写，如果新库中没有就直接写入，如果新库中有就根据最后修改时间进行覆盖。
3、导完一轮之后反复检查，直到两个库的数据保持一致。
4、基于新库的代码重新部署一次。

四、如何设计可以动态扩容缩容的分库分表方案？
1、停机扩容（说是不常用）

2、优化方案：
一开始上来就是 32 个库，每个库 32 个表，那么总共是 1024 张表，然后根据某个 id 先根据 32 取模路由到库，再根据 32 取模路由到库里的表。
假设每个表放 500 万数据，在 MySQL 里可以放 50 亿条数据可以支撑好几年；规模大的几百个库，128 个库，256 个库，512 个库，这种一般有专门的DBA，我们只负责修改配置就行。

3、分库分表之后，id 主键如何处理？肯定需要一个全局唯一的 id 来支持

1、UUID

好处就是本地生成，不要基于数据库来了；

不好之处就是，UUID 太长了、占用空间大，作为主键性能太差了；

更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），还有，由于在写的时候不能产生有顺序的 append 操作，而需要进行 insert 操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。