首页  思维导图  详情

mysql

2021-01-12 14:29:31   0  举报





AI智能生成

mysql原理

作者其他创作

大纲/内容

索引结构

B-Tree

innodb的pagesize默认16KB，每个节点包含了索引以及数据（1kb），3层高度的B+Tree, 16*16*16

B+Tree

innodb的pagesize默认16KB，假设主键为bigint（8B）,区间指针（6B），3层高度的B+Tree,叶子节点数据大小（估1kb） 1170*1170*16 约等于 2kw数据

存储引擎

MyISAM存储引擎

MyISAM索引文件和数据是分离的（非聚集）

innodb存储引擎

索引文件和数据是在一起的，聚集索引支持事务

主键索引，聚簇索引，叶子节点包含表的全部数据

二级索引，叶子节点记录的是对应的主键，查到主键之后回主键索引回查

联合索引

最左前缀优化原则

hash索引

几个关键的日志文件

innodb

undo log 回滚

redo log 重做
物理格式日志，记录对每个页的修改

存储数据与bin log 类似

用来恢复数据库宕机后buffer pool中未来的及刷入磁盘的数据

先写redo log buffer -> 写入文件系统缓存 -> fsync刷盘
redo日志在事务进行中不断的被写入

innodb_flush_log_at_trx_commint

0-事务提交时不进行写入重做日志操作，这个操作只在master thread中完成，
master thread 中每1秒会进行一次重做日志文件的fsync操作

1-事务提交时必须调用一次fsync操作

2-事务提交时将重做日志写入文件系统的缓存，不进行fsync，由系统自动控制fsync

重做日志 512字节，与磁盘扇区大小一样512
写入可以保证原子性

binlog
逻辑日志，记录对应的sql语句

通用二进制日志

可用于恢复数据

主从同步

在事务提交后进行一次写入

模式

STATEMENT

MVCC多版本并发控制

undo回滚日志

记录操作版本链

roll_pointer操作日志指针指向

tx_id事务id

相关列数据

记录头信息有个del_flag标记是否删除

删除操作为特殊的update，同样在操作版本链插入一条链数据，但标记为删除

DB_ROW_ID 当由innodb自动产生聚集索引时，聚集索引包括这个DB_ROW_ID的值，
否则聚集索引中不包括这个值.

session级别一个seeion select时生成一个视图

为当前事务查询生成一致性视图 read-view

事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），
才会被分配一个单独的事务id，这个事务id是递增的。

由当前所有进行中事务id组成列表，及历史最大事务id组成，如【101,102,103】,109

最小事务id

之前为已提交事务,小于最小的事务id表明生成该版本的事务在生成ReadView前已经提交，
所以该版本可以被当前事务访问

最大事务id

之后为未开始事务，大于最大事务id表明生成该版本的事务在生成ReadView后才生成，
所以该版本不可以被当前事务访问

min_trx_id<事务id<max_trx_id

区间中认为是未提交与已提交事务，判断一下trx_id属性值是不是在m_ids列表中，
如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；
如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

实现版本

读未提交

每次读取记录的最新版本，不需要read-view

读已提交

事务内每次查询其read-view生成最新

可重复读

事务内每次查询其read-view不变

序列化

加锁的方式来访问记录，不需要read-view

规则

查询时会结合undo日志和read-view视图对比应该返回哪个结果

查询undo日志对应操作

找到事务id在区间内事务且不在一致性视图数组中或一致性视图中事务id小于最小事务的记录返回

Buffer Pool (innodb)

按页存储，这个页就是B+树的一个节点页

引擎内更新流程

1.加载缓存数据，将更改的数据所在整页加载到缓存中，即buffer pool

2.写undo日志，将旧值写入undo日志，便于回滚

3.修改buffer pool中缓存的值

4.写redo日志到缓存中，重做日志，将修改后的数据记录到redo log buffer中

批量写

5.准备提交事务（将要执行commit操作）将redo log buffer 写入磁盘

6.准备提交事务（将要执行commit操作）写入binlog

7.写入commit标记到redo log 事务提交完成，为标记保证binlog与redo log一致，
（此时我们认为事务已经提交完成，但其其实磁盘中数据还未同步）

8.随机时间以页为单位将buffer pool中的数据刷入磁盘

mysql中的增删改查都是基于buffer pool做的（效率问题），
buffer pool一般设置为机器内存大小的60%

日志文件的读写为顺序读写，性能极高类似缓存

buffer pool 页链表管理

FREE链表

存储没有被使用的buffer块，当free链表里面的数据被使用完的时候，
如果再需要申请空间，需要根据LRU淘汰已使用过的数据页

LRU链表

最近最少使用。即最频繁使用的页在LRU链表的前端，
最少使用的页在LRU链表的尾端。当缓存池不能存放取到的页时，首先将LRU链表中释放尾端的页。
最新读取的页面，并不是放入前端而是放入中间（5:3，插入到5/8），防止热块被新读取的大量数据挤出LRU链表。
当LRU里的数据块被修改时，变成脏块，会被添加到FLUSH链表中。

Flush链表

脏块被刷入FLUSH链表，然后会在一定时间落盘。
脏块在LRU链表和FLUSH里都存在。LRU链表管理数据块的可用性，
FLUSH用于数据块的写入磁盘

页的操作

页面的分配

首先从FREE链表中申请可用的空间。如果FREE链表没有可以使用的空间，
那么就要向LRU链表中申请，通过LRU算法。LRU的页面是可以被替换的，条件：

1.页不脏

2.页没有被其他线程使用

页的物理读取

当innodb存储引擎启动后，缓冲池是空的，所有页都在FREE链表中。
数据库的读写操作都需要在缓冲池中完成，缓冲池的任务就是将磁盘中的数据读取到缓冲中，
即页面的物理读取

页的预读

判断某个区域内的大多数页面是否被访问到，如果被访问到，
数据库会进行预读操作。数据库对区域内的数据顺序性读取，提高数据库读取性能

逻辑读取