MYSQL 原理详解 （详解在注释里面，图片加载需要时间）
2023-07-04 08:36:39 0 举报AI智能生成
MySQL知识要点和原理解析,详细信息和图片均在黄色图标的注释中，鼠标移动到黄色图标上即会显示，图片加载有时较慢。
作者其他创作
大纲/内容
引擎类型
InnoDb
特性
InnoDb默认一页是16K，数据存取都是按页为单位
插入的时候 用户数据区 会对主键进行排序
索引结构
B+ Tree索引
特点
非叶子节点只存储索引，不存储数据，这样单个节点可以放更多的索引。
叶子节点包含所有索引字段和数据。
叶子节点用双向指针相连，提高了范围查找的性能。
引擎文件类型
innodb_user.frm文件存储着表结构
innodb_user.ibd文件存储着表索引和表数据
B+树优缺点
优点
缺点
B Tree索引（非mysql采用）
特点
每个节点至多有m棵子树；
除根节点外，其他分支节点至少有ceil(m/2)棵子树；根节点至少有2棵子树（除非B树只包含1个节点）
有j个孩子节点的非叶子节点有j-1个关键字，关键字按非降序排列
B树是一棵平衡树，所有叶子节点具有相同的深度
优缺点
优点
缺点
Hash索引
FULLTEXT全文索引
索引类型
从应用层次划分
普通索引
单列索引
联合索引
唯一索引
主键索引
联合索引
从索引键值类型划分
主键索引
辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分
聚集索引（聚簇索引）
注意两点
优劣势
非聚集索引（非聚簇索引）
聚簇索引和非聚簇索引的区别
INNODB和MYISAM的主键索引与二级索引的对比
全文索引
覆盖索引
索引方法
二分查找法
Hash
B+Tree
查询方法
回表查询
InnoDb优缺点
优点
缺点
MyISAM
引擎表文件类型
myisam_user.frm文件存储着表结构
myisam_user.MYD文件存储着表数据
myisam_user.MYI文件存储着表索引
MyISAM优缺点
优点
缺点
MyISAM 和 InnoDB 的区别
如何选择
事务
四大特性(ACID)
原子性（Atomicity）
依靠undo log保证
一致性（Consistency）
依靠其他三大特性保证
隔离性（Isolation）
依靠MVCC保证
持久性（Durability）
由内存（buffer pool、LogBuffer）+redo log保证
事务四大问题（两脏两读）
脏写（任何事务级别都不允许出现）- 不同事务写覆盖
脏读 - 读到其他事务未提交数据
不可重复读 - 一次事务内对同一数据的多次读操作，结果不一致
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
幻读 - 一次事务内多次相同的读取操作，因为其他事务的增删导致数据不一致
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。
四种隔离级别
READ UNCOMMITTED：未提交读
不能脏写，未提交事务的修改能被读到
READ COMMITTED：已提交读
不能脏写，已提交事务的修改能被读到
REPEATABLE READ：可重复读（多次读取结果数一致）
不能脏写、事务内每次读取的内容一致，允许幻读，在本事务内其他已提交的事务不会被读到，但增删能读到
SERIALIZABLE：串行化
MVCC
什么是当前读和快照读？
当前读
快照读
当前读，快照读和MVCC的关系
MVCC 带来的好处是？
解决以下问题
在并发读写数据库时，读不阻塞写，写不阻塞读，提高并发读写性能
解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题；
实现原理
隐式字段
事务ID：DB_TRX_ID
回滚指针：DB_ROLL_PTR
隐藏主键：DB_ROW_ID
实际还有一个删除 flag 隐藏字段
undo log
主要分为两种
insert undo log
update undo log
purge
执行流程
ReadView
什么是 Read View?
可见性算法
4个内容
m_ids
min_trx_id
max_trx_id
creator_trx_id
ReadView比较方法
1、首先比较 DB_TRX_ID < min_trx_id
2、接下来判断 DB_TRX_ID >= max_trx_id
3、判断 DB_TRX_ID 是否在活跃事务之中：trx_list.contains (DB_TRX_ID)
如果在
如果不在
其中，DB_TRX_ID：要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）
数据库隔离级别
未提交读（READ UNCOMMITTED）
串行化（SERIALIZABLE）
已提交读（READ COMMITTED）/可重复读（REPEATABLE READ）
READ COMMITTED
REPEATABLE READ
数据更新机制
Buffer Pool
undo log
作用
提供回滚操作【undo log实现事务的原子性】
提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】
主要属性
RowID
事务ID
回滚指针
数据更新前的记录
存储机制
采用分段(segment)的方式进行存储
写入时机
在更新数据之前，MySQL会提前生成undo log日志（undo log先于buffer pool）
写入undo log属于磁盘操作
redo log
redo日志的格式和结构
什么是redo日志，为什么需要redo日志？
浪费
随机IO
数据刷盘到redo日志好处
1、redo日志记录的变更内容少
2、redo行记录是顺序写入磁盘的（顺序IO）
redo日志的行格式
type：redo 日志的类型。
space ID：表空间ID
page number：页号
data：这条 redo 日志的具体内容。
作用
奔溃恢复
组成
redo log buffer
page cache
redo log日志（磁盘文件）
刷盘策略（和binLog二阶段式提交）
0
设置为0的时候，每次提交事务时不刷盘
1（默认策略）
设置为1的时候，每次提交事务时刷盘
2
设置为2的时候，每次提交事务时都只把redo log buffer写入page cache。
写入流程
默认事务提交时刷盘
后台线程
文件日志组
多个logFile组成
属性
wirte pos
checkpoint
为什么说 redo log 具有崩溃恢复的能力？
binlog
作用
备份、主备、主主、主从同步
三种格式
statement
记录的内容是SQL语句原文
优点
缺点
row
仅需记录哪条数据被修改了
优点
缺点
mixed
基于 STATMENT 和 ROW 两种模式的混合复制
刷盘时机
流程：写入binlog、binlog cache、page cache、磁盘日志
write
fsync
write和fsync时机
由参数sync_binlog控制
0
1
N
redolog与binlog两阶段提交
图示
redoLog和binLog区别
buffer pool数据落盘
数据库关闭时，将所有脏页刷新到磁盘，这是默认的方式
Master Thread操作，这个主线程会每秒、每10秒从脏页列表刷新一定比例的页到磁盘，这是个异步的操作，不会阻塞查询
buffer pool中的free列表空闲页不足时，需要刷新一部分来自LRU列表的脏页
redo log文件不可用时，需要强制刷新一部分，为了保证redo log的循环利用
InnoDB
InnoDB总体结构
内存部分由多个缓冲区构成
磁盘部分包括各种表空间
行结构和页结构
ibd文件的逻辑结构
表空间- Tablespace
段 - Segment
段和索引是一一对应的关系
段不对应表空间的某一连续物理区域
问题
一个使用 InnoDB 表只有一个聚簇索引，一个索引对应两个段，每个段都是以区为单位申请空间，是否意味着一个新创建的表即使没什么数据也要占用2个空闲区（2M空间）？并且每新建一个索引就会多申请2M的空间？如果真的是这样分配空间，对于一些只有几条数据的表而言实在是太浪费空间。
某个段分配空间的策略
区 - Extent
问题
为什么提出区的概念，并用区管理页？
如果没有区
目的：区内相邻页的物理地址连续性
页 - Page
InnoDB的页结构
用户记录 和 User Records 堆
用户记录的物理结构——堆
Infimum 和 Supremum记录
用户记录的逻辑结构——单向链表
软删除
页目录 Page Directory
页目录的构建过程
页目录的每一个槽本质上就指向每个分组（的组长）的指针
页面头部 Page Header
文件头部 File Header
数据页与记录的关系
行 - Row
索引
特性
B+树的每一层都会形成一个双向链表
叶子节点是数据页，存储普通用户记录
非叶子节点也是数据页，存储目录项记录
目录项记录跟用户记录存储结构一致，只是存储的数据略有差异
目录项记录至少有3个固定的列+隐藏列
索引是有序的
叶子节点之间是有序的
叶子节点内是有序的
同层非叶子节点之间是有序的
非叶子节点内是有序的
从数据页变成索引这件事
数据页
数据页结构
空闲空间
双向链表
数据页目录
索引
页分裂
主键目录
索引页
现在整个搜索过程就十分简单了
Innodb索引结构和方案
InnoDB的索引方案
用户记录页
目录页
目录页的页内查找：二分查找
目录页也会发生页分裂
主键索引（聚簇索引）和普通索引的区别
二级索引
解释了联合索引的最左前缀原则
联合索引的页
Buffer Pool缓存页
缓冲池 Buffer Pool
Buffer Pool的控制块
Buffer Pool 的预读特性
InnoDB怎么在不查询磁盘的情况下知道 id = 6的记录也位于 1001号页呢？
Innodb的预读不仅只读取本次所需的一个页面，还可能读取和该页面相邻近的其他页面
Buffer Pool的管理
Buffer Pool的初始化
申请空间
划分空间
3种页
1、free page
2、clean page
3、dirty page
针对这3种页，InnoDB使用3种链表维护
free list
意义
误区说明
flush list
lru list
链表中的节点不是缓存页本身，而是页对应的控制块
Buffer Pool缓存淘汰
使用LRU会遇到的问题
预读
大量数据涌入
解决方法
冷热分离LRU
优化点1：midpoint
优化点2：old_blocks_time
优化点3：减少热页在链表移动
缓冲页的哈希处理
脏页刷盘
1、从LRU 链表的冷数据区刷新部分页面到磁盘
2、从 flush 链表中刷新一部分页面到磁盘
3、主动刷盘内存池中被淘汰的脏页
多个Buffer Pool
Buffer Pool分块
目的
块大小
Buffer Pool 预热
Buffer Pool配置参数
innodb_page_size
innodb_old_blocks_pct
innodb_old_blocks_time
innodb_buffer_pool_instances
innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_dump_pct
innodb_buffer_pool_load_at_startup
日志缓冲区 Log Buffer
刷盘
3种策略
1、每隔1秒从 log buffer 写入OS cache，并马上刷盘，mysql服务故障或者主机宕机则丢失1秒数据。
2、事务提交时，立刻从 log buffer 写入 os cache， 并马上刷盘，mysql服务故障或者主机宕机不会丢失数据，但会频繁发生磁盘IO。
3、事务提交时，立刻从 log buffer 写入 os cache，每隔1秒刷盘，mysql服务故障不会丢失数据，因为数据已经进入操作系统缓存，与mysql进程无关了，主机宕机则丢失1秒数据。
除此之外，当redo/undo日志缓冲区满了之后，也会触发刷盘。
缓存页大小
16K，磁盘上的数据页和缓存页一一对应
缓存页数据查询
页缓存哈希表
如何确定数据页是否被缓存
执行一条语句的几个过程
重点误区！
流程图
索引的代价
索引的功能
高效查找
排序
分组
几种无法使用索引排序的情况
1、ASC和DESC混用，即对某个字段升序排序，对另一个字段降序排序；
2、order by 包含非同一个索引的列；
3、order by 包含多个是同一联合索引的列，但列的顺序不对
4、where 条件的索引列和排序的索引列不同
5、order by 对列使用函数；
回表的代价
如何善用索引
1、只为用于查询、排序和分组的列创建索引；
2、为基数大（即重复值少）的列建立索引
3、索引列的类型尽量小
4、为列前缀建立索引
前缀索引，只能用于查找，无法用于排序
5、覆盖索引（即避免使用*，而是在sql中注明要查询的列）
6、让索引列以列名的形式在搜索条件中单独出现（别使用函数或对列计算）
7、主键的插入尽可能按顺序插入
锁类型
锁力度
表锁
行锁
页锁
锁机制
乐观锁
悲观锁
兼容性
共享锁（S）
排它锁（X）
加锁模式
记录锁
间隙锁（Gap Locks）
为了解决幻读问题时引入的锁机制
临键锁（next-key）
意向锁
意向锁又分为 意向共享锁（IS）和 意向排他锁（IX）
插入意向锁
死锁检测
死锁问题策略解决
等待，直到超时(50s)
使用死锁检测处理死锁程序
主从复制
MySQL支持的复制类型
基于语句的复制
基于行的复制
混合类型的复制
复制的工作过程
3个线程
Master的binlog dump线程
Slave的IO线程
Slave的SQL线程
主从复制同步方式
异步复制
同步复制
半同步复制
主从同步延时
原因
解决方案
调优
35 张图带你 MySQL 调优
SHOW PROCESSLIST 命令详解
一文搞定MySQL性能调优
0 条评论
下一页
