Mysql学习梳理
2026-01-16 22:32:48 0 举报AI智能生成
学习梳理
作者其他创作
大纲/内容
Mysql索引底层数据结构
索引常见的数据结构
二叉查找树 (BST - Binary Search Tree)
左子节点比父节点小，右子节点比父节点大。
红黑树 (Red-Black Tree)
红黑树是一种自平衡的二叉查找树，保证树的最长路径不超过最短路径的两倍，避免了退化成链表
哈希索引 (Hash Index)
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”，“IN”，不支持范围查询hash冲突问题
AVL 树 (Balanced Binary Search Tree)
AVL 树比红黑树更加“严格平衡”，任何节点的两个子树的高度差最大为 1。
B-树 (B-Tree)
- 叶节点具有相同的深度
- 所有节点 根节点、内部节点、叶子节点）都存索引和数据
- 索引从左到右递增排列
B+ 树 (B+ Tree)
叶子节点存数据，非叶子节点存索引，所有叶子节点通过指针相连。
面试问题总结
为什么mysql(B+ Tree)当作索引结构?
优势总结：B+Tree适合数据库的查询（等值、范围、排序）场景
- B-Tree：所有节点存储数据，范围查询需中序遍历，效率较低。
- B+Tree：叶子节点存数据，非叶子节点存索引，叶子节点双向链表连接
- 能存更多索引，而且范围查询更高效。
- 哈希索引：仅支持等值查询（如 WHERE id=5），不支持范围查询 。
优势总结：B+Tree适合数据库的查询（等值、范围、排序）场景
mysql中 InnoDB vs MyISAM 结构特点
InnoDB
.ibd 文件（表空间）
聚簇索引（主键=物理存储顺序）
数据与索引一体
优势：主键查询快，不需要回表
MyISAM
.MYD（数据） + .MYI（索引）
非聚簇索引
数据与索引分离
劣势: 二级索引需要回表
为什么innodb必须建主键,
B+树（聚簇索引）必须有个建进行排序,
如果没有定义主键，InnoDB 会选择第一个唯一且非空的索引作为聚簇索引。
都没有的话，InnoDB 会自动创建一个隐藏的6字节的行 ID (ROWID) 用作聚簇索引，
rowid是全局表共享数据达到最大值，有回绕风险。
如果没有定义主键，InnoDB 会选择第一个唯一且非空的索引作为聚簇索引。
都没有的话，InnoDB 会自动创建一个隐藏的6字节的行 ID (ROWID) 用作聚簇索引，
rowid是全局表共享数据达到最大值，有回绕风险。
为什么推荐自增主键？
顺序插入，总在最右边追加，避免页分裂，写入快
只占 8 字节，所有二级索引都会存主键，索引小，缓存多
数据有序，范围查询是顺序 I/O，速度快
只占 8 字节，所有二级索引都会存主键，索引小，缓存多
数据有序，范围查询是顺序 I/O，速度快
联合索引底层结构以及相关特性
- 底层结构：
- 非叶子节点（只存键值）
- 叶子节点（存完整索引项+主键）
四大核心特性
最左前缀匹配原则：查询必须从最左列开始
索引覆盖：若SELECT字段全在索引中，不回表
索引下推：即使中间断了，也能在索引层先过滤掉不符合条件的行，减少回表次数。
范围查询导致后续列失效：一旦遇到范围查询（>, <, BETWEEN, LIKE 'xxx%'），索引匹配就会停止。
Mysql优化
Explain工具
Explain关键字用于模拟优化器执行SQL语句，分析查询性能瓶颈
Explain中的列
id
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
select type
表示对应行是简单还是复杂的查询
table
这一列表示 explain 的一行正在访问哪个表
type（从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL）
possible_keys
这一列显示查询可能使用哪些索引来查找。
partitions
如果查询是基于分区表的话，partitions 字段会显示查询将访问的分区。
key
实际使用的索引
key_len
使用的索引长度
ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows
预计扫描行数
Extra 额外信息
Using index：使用覆盖索引
Using where：使用where语句来处理结果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary（必须优化）：mysql需要创建一张临时表来处理查询
Using filesort（必须优化）：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序
Select tables optimized away：使用某些聚合函数（比如max、min）来访问存在索引的某个字段
索引最佳实践
优先为 WHERE/JOIN/ORDER BY/GROUP BY 列建索引
联合索引遵循最左前缀原则
区分度高的列放前面
等值查询列放前面，范围查询放最后
考虑覆盖索引（包含查询需要的列）
主键用自增 BIGINT
控制索引数量
单表索引建议控制在 5 个以内。
理由：
写放大：索引是“读得快，写得慢”。每次 INSERT/UPDATE/DELETE，都要去维护那一堆 B+ 树，IO 成本很高。
优化器负担：索引太多，MySQL 优化器在计算 Cost（成本）选索引时会变慢，甚至更容易选错索引。
理由：
写放大：索引是“读得快，写得慢”。每次 INSERT/UPDATE/DELETE，都要去维护那一堆 B+ 树，IO 成本很高。
优化器负担：索引太多，MySQL 优化器在计算 Cost（成本）选索引时会变慢，甚至更容易选错索引。
给长字符串加“前缀索引”
不要索引整个长文本（如 VARCHAR(255)），只索引前 N 个字符。
理由:体积小 = 内存能装更多索引页 = 更少的磁盘 IO = 更快。
理由:体积小 = 内存能装更多索引页 = 更少的磁盘 IO = 更快。
即使列可空，也尽量定义为 NOT NULL
- NULL 在 B+ 树里处理极其麻烦，占用额外的存储空间。
- COUNT(column) 统计时会自动忽略 NULL，可能导致业务逻辑 BUG。
- 复合索引中如果有 NULL，会影响优化器的判断。
索引失效场景
结构被破坏
索引列使用函数或表达式
隐式类型转换(字符串不加单引号索引失效)
匹配规则不满足
前导模糊查询 LIKE '%xxx'
最左前缀法则被打破
范围与逻辑限制
范围查询打断后续索引
OR 连接条件
数据分布问题
mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
索引优化
trace工具：查看mysql如何选择索引
Order by与Group by优化
mysql 排序
index
使用索引来完成排序。
filesort
使用文件进行排序（但实际上不一定真用磁盘文件，内存够就在内存排）。
单路排序
是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
多路排序
是首先根据相应的条件取出相应的排序字段和可以直接定位行
数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段
数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段
调整策略按照max_length_for_sort_data 情况做调整
单路排序<max_length_for_sort_data<多路排序
order by满足两种情况会使用Using index。
ORDER BY 语句完全契合索引的最左前缀
WHERE + ORDER BY 联合契合索引的最左前缀
排序注意事项
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则
分页查询优化
为什么要优化？
优化方案
根据自增且连续的主键排序的分页查询
可以用 BETWEEN 算 ID 范围（但要小心删除导致的空洞）
根据非主键字段排序的分页查询
用子查询 + 覆盖索引 (SELECT id ... LIMIT ...) 先把那一页的 ID 捞出来，再去主表拿详情。
游标分页
join关联查询优化
嵌套循环连接Nested-Loop Join算法
小表驱动大表
在 Left Join 中，左边的表最好是数据量小的表；在 Inner Join 中，MySQL 优化器会自动选小表做驱动表。
关联字段加索引
一旦加了索引，算法直接升级为 Index NLJ，性能提升几百倍。
基于块的嵌套循环连接Block-Nested-Loop Join算法
count()查询优化
索引设计原则
索引优化技巧
利用索引排序 Extra: Using index condition ✅ 没有 filesort
覆盖索引 + 排序 Extra: Using where; Using index ✅ 最优
限制结果集（即使 filesort，影响也小） LIMIT 小时，即使 filesort 影响也小
索引设计实战
Mysql事务
ACID特性
原子性（Atomicity）定义： 事务中的所有操作要么全部成功，要么全部失败，不存在部分成功。
实现原理： 通过 undo log（回滚日志）记录修改前的数据回滚时根据 undo log 恢复原数据
一致性（Consistency）定义： 事务执行前后，数据库从一个一致性状态转换到另一个一致性状态。
隔离性（Isolation）定义： 多个事务并发执行时，一个事务的执行不应该被其他事务干扰。
实现原理： 通过 锁机制 和 MVCC（多版本并发控制）
持久性（Durability）定义： 一旦事务提交，对数据库的改变是永久性的，即使系统崩溃也不会丢失。
实现原理： 通过 redo log（重做日志）先写日志，再写磁盘（WAL - Write-Ahead Logging）崩溃恢复时根据 redo log 重做操作
并发事务处理带来的问题
脏读（Dirty Read）
读到其他事务未提交的数据
脏写(Lost Update) 当两个或多个事务选择同一行数据修改，有可能发生更新丢失问题，即最后的更新
不可重复读（Non-Repeatable Read）
同一事务内，同一条记录两次查询结果不同
幻读（Phantom Read）
同一事务内，范围查询两次结果条数不同
事务隔离级别
读未提交（Read Uncommitted)
可以读取其他事务未提交的数据
问题：脏读
读已提交(Read Committed, RC)
只能读取已提交的数据
问题：不可重复读
可重复读 (Repeatable Read, RR，MySQL 默认)
同一事务内，多次读取结果一致
问题:幻读
可串行化(Serializable)
全串行执行，不会有任何并发问题
问题：性能极差
Mysql锁机制
性能
宏观策略
悲观锁 (Pessimistic Locking)
- 心态：“总有刁民想害朕”。每次拿数据都觉得别人会改，所以先上锁，锁住再操作。
- 实现：MySQL 的 SELECT ... FOR UPDATE，LOCK TABLES。
- 场景：写多读少，强一致性要求高（如银行转账）。
乐观锁(Optimistic Locking)
- 心态：“世界很美好”。认为冲突很少，先不加锁，等提交修改的时候再检查一下：“我看这行的时候它是版本 v1，现在它还是 v1 吗？”
- 实现：MySQL 本身不原生提供乐观锁，通常由业务层代码实现（比如加个 version 字段）。
- 场景：读多写少，为了高吞吐量（如抢票、点赞）。
锁分类
锁的粒度：范围越小，并发越高
表锁 (Table Lock)
- 开销小，加锁快，但并发度最低。锁一次，全表都瘫痪。
- MyISAM 的标配。InnoDB 在特定 DDL 或手动 LOCK TABLES 时也会用。
页锁 (Page Lock)
介于表锁和行锁之间。BDB 引擎才用，MySQL 中很少提。
行锁 (Row Lock)
- 每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
- InnoDB 的行锁是加在索引上的！不是加在数据行上的。（这点面试必考：如果 update 没走索引，行锁会退化成表锁！）
间隙锁 (Gap Lock)
- 锁住两个记录之间的**“空隙”**。
- 为了解决 RR 级别下的幻读问题。防止你在两个记录之间插入新数据。
临键锁(Next-key Locks)
- Next-Key Locks是行锁与间隙锁的组合。
- InnoDB 在 RR 级别下的默认加锁单位。既锁住数据本身，又锁住前面的空隙，形成一个“左开右闭”的区间。
- 注意：唯一索引 + 等值查询 + 记录存在 Next-Key Lock会退化为 Record Lock (只锁行)
总结
数据库操作类型
意向锁（Intention Lock/I锁）
- 就是一个“不用扫描就能知道表里有没有锁”的快捷标志。
- 它的唯一作用：阻挡表锁。 当有人想锁整张表（LOCK TABLES）时，只要看一眼这个灯亮着，就立刻知道不能锁，直接去排队，省去了扫描全表一行行检查的麻烦
读锁 / 共享锁 (Shared Lock / S锁)
针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁 / 排他锁 (Exclusive Lock / X锁)
当前写操作没有完成前，它会阻断其他写锁和读锁
锁等待分析
想要锁，被别人占了，只能等。如果等太久（默认 50s），报错超时。
死锁问题分析
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，
这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。
这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。
MVCC（多版本并发控制）
undo日志版本链
指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日
志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链
志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链
ReadView
查询时拍一张快照（ReadView），记录哪些事务已提交、哪些还在跑，用它来决定当前能看到哪个版本。
版本链比对规则
MVCC可见性
读类型
快照读（Snapshot Read）
- SQL： 普通 SELECT
- 特点： 不加锁、用 ReadView 判断可见性
- 作用： 保证可重复读（RR）/ 每次读取已提交（RC）
当前读（Locking Read）
- SQL：
- SELECT ... FOR UPDATE
- SELECT ... FOR SHARE / LOCK IN SHARE MODE
- UPDATE
- DELETE
- INSERT（写入时也属于当前读路径）
- 特点： 读取最新已提交版本，并加锁
- 作用： 保证修改基于最新数据
注意事项
1.读和写是两套机制
当前读 ≠ 快照读，不会影响快照
2.当前读不生成版本链
只有写操作（INSERT/UPDATE/DELETE）才生成版本链
事务ID分配
3.BEGIN不会立刻分配事务ID
第一次写操作或锁定读才分配
4.可见性优先级
自己修改的记录无条件可见
当前读 ≠ 快照读，不会影响快照
2.当前读不生成版本链
只有写操作（INSERT/UPDATE/DELETE）才生成版本链
事务ID分配
3.BEGIN不会立刻分配事务ID
第一次写操作或锁定读才分配
4.可见性优先级
自己修改的记录无条件可见
总结
MVCC 通过 undo log 保留数据的多个历史版本，事务查询时生成 ReadView 记录当前事务状态，根据可见性算法（绿色区已提交可见、红色区未来不可见、黄色区看m_ids）从版本链中找到可见的版本，从而实现读写不互斥的高并发。RR 级别 ReadView 生成一次，RC 级别每次查询都重新生成
MySQL架构
MySQL内部组件结构
Server层
连接器
1.这些客户端要向mysql发起通信都必须先跟Server端建立通信连
接，而建立连接的工作就是有连接器完成的。
接，而建立连接的工作就是有连接器完成的。
查询缓存
2.MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。（8.0已经移除了查询缓存功能）
分析器
3.如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解
析。如下图
析。如下图
优化器
经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各
个表的连接顺序；以及一些mysql自己内部的优化机制。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各
个表的连接顺序；以及一些mysql自己内部的优化机制。
执行器
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如
下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)。
1 mysql> select * from test where id=10;
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)。
1 mysql> select * from test where id=10;
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
存储引擎层
InnoDB
聚簇索引（主键即数据），支持事务、行级锁、外键，崩溃恢复强，适合高并发写入场景。
MyISAM
非聚簇索引（索引与数据分离）不支持事务和外键，表级锁，读性能好但写入并发差。
Memory
默认 Hash 索引，数据存内存，速度快但重启丢失，适合临时表/缓存。
MySQL日志机制
执行流程
redo log 写入策略
先写入缓存，提交时sync_binlog 控制何时 fsync 落盘。（innodb_flush_log_at_trx_commit 控制）。
=1 （默认，最安全）：每次事务提交，都把 redo log buffer 调用 write 写入 page cache，
并立刻 fsync 刷到磁盘。（崩溃几乎不丢数据，但性能最低）
并立刻 fsync 刷到磁盘。（崩溃几乎不丢数据，但性能最低）
=2 （折中）：每次提交只 write 到 page cache，不 fsync；由后台 每隔 1 秒 fsync 到磁盘。
(崩溃可能丢 1 秒内的数据。)
(崩溃可能丢 1 秒内的数据。)
=0（性能优先）：后台 每隔 1 秒 才把 redo log buffer 的日志 write 到 page cache，并 fsync 到磁盘。
(崩溃可能丢 1 秒内甚至更多的数据)
(崩溃可能丢 1 秒内甚至更多的数据)
redo log重做日志关键参数
innodb_log_buffer_size
设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
innodb_log_group_home_dir
设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其
中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
innodb_log_files_in_group
设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2
个，最大100个。
个，最大100个。
innodb_log_file_size
设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo
log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。
log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。
binlog 日志
二进制归档日志
为什么会有redo log 和 binlog 两个日志？
redo log 是引擎级的物理日志，用于崩溃恢复，能保证事务提交的一致性；binlog 是 Server 级逻辑日志，主要用于复制和归档，崩溃时不能单靠它判断事务是否已落盘，所以需要 redo 来兜底。两阶段提交保证 redo 和 binlog 的一致性，避免主从和崩溃恢复不一致
日志格式
写入磁盘机制
操作日志
删除 binlog 日志文件
mysqlbinlog 自动解析二进制文件查看binlog日志内容
查看 binlog 日志文件
binlog日志文件恢复数据
按 Position 恢复（推荐）
按 Datetime 恢复
生产环境备份恢复最佳实践
仅依赖 Binlog 恢复存在风险（日志滚动删除、体积过大），黄金策略：每日全量备份 + Binlog 增量恢复
undo log回滚日志
- 是什么：undo log 是 InnoDB 的回滚日志，记录事务修改前的旧版本。
- 作用：用于事务回滚和 MVCC （一个事务在读时会拿一个“快照”，如果某行被别的事务改过，就用 undo log 去找符合快照版本的旧记录，这样读到的是当时一致的视图）生成一致性读。
undo log日志什么时候删除？
undo 主要服务回滚，提交后可尽快清；更新/删除的 undo 会被 MVCC 使用，必须等不再被任何 Read View 引用后由 purge 线程 异步清理。
undo log 和 redo log 区别
redo 是“写入后的变化”用于崩溃恢复；undo 是“写入前的版本”用于回滚与 MVCC。
为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
直接改磁盘太慢、也不安全。MySQL 这套“复杂机制”（buffer pool + redo/undo + binlog + 两阶段提交）是为了同时做到 高性能 + 事务一致性 + 崩溃可恢复 + 高并发
错误日志
通用查询日志
MySQL高可用集群架构
传统主从复制(MySQL Replication)
基于binlog file+位点
从库记录主库 binlog 的 文件名 + 位置点 来继续复制。
优点：简单直观；缺点：切换主库/故障恢复时对位点依赖强，容易出错。
优点：简单直观；缺点：切换主库/故障恢复时对位点依赖强，容易出错。
基于GTID
每个事务有全局唯一 ID（server_uuid:transaction_id）。
从库只需告诉主库“我缺哪些 GTID”，无需手工找位点。
优点：主从切换更简单、更安全；缺点：配置稍复杂，要求 GTID 开启。
一句话对比：
位点复制靠“文件+偏移”，GTID 复制靠“事务ID集合”。
从库只需告诉主库“我缺哪些 GTID”，无需手工找位点。
优点：主从切换更简单、更安全；缺点：配置稍复杂，要求 GTID 开启。
一句话对比：
位点复制靠“文件+偏移”，GTID 复制靠“事务ID集合”。
复制的数据同步类型
异步复制（默认）
主库提交不等从库，默认方式，性能最好但可能丢数据。
半异步复制
主库至少等一个从库确认收到 binlog 才提交，可靠性更高但有延迟。
组复制(MySQL Group Replication)
解决了传统主从复制不能故障转移的问题
InnoDB Cluster
MySQL Shell+MySQL Router+组复制
InnoDB ReplicaSet
MySQL Shell + MySQL Router + MySQL Replication
0 条评论
下一页
