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