MySQL架构原理详解

数据行

redo log

redolog buffer：默认16MB，由参数innodb_log_buffer_size设置，内部划分为N个redo log block（512字节）redo log block什么时候刷入磁盘：事务提交时，将事务修改过的redo log都刷入磁盘；当redo log buffer内存使用超过一半时刷入磁盘；后台线程每隔1秒将redo log block刷入磁盘；MySQL关闭时，redo log block都会刷入磁盘

连接

执行器

3、获取最优查询路径

Innodb存储引擎

简单LRU链表(Least Recently Use)：最近最少使用，当我们从磁盘加载数据页到缓存页时，就把这个缓存页的描述数据块的地址放在LRU链表头部，当每次操作缓存页时，就把这个缓存页挪到链表头部。这样当没有空闲缓存页时，可以将链表尾部的缓存页刷入磁盘，然后将要加载的数据页加载到空出来的缓存页。存在问题：将频繁访问的缓存页刷入磁盘。原因：MySQL预读机制+全表扫描或大的查询SQL基于冷热数据分离思想的LRU链表：分为冷数据区和热数据区，冷数据区占37%。当数据页第一次加载到缓存页时，会被放在冷数据链头部，当超过Innodb_old_blocks_time参数设置的时间访问缓存页时，将缓存页挪到热数据链表头部。LRU链表的缓存页刷入磁盘的时机：定时将LRU链表尾部的数据刷入磁盘；定时将flush链表中的数据刷入磁盘。

槽位

页目录：由数据页中数据行的主键与数据行所在槽位的映射。页分裂：当数据行不断新增，增加新的数据页时，如果新的数据行的主键值小于上一个数据页的某数据行的主键时，则将新数据行挪到上一个数据页，把上一个数据页里主键值大的挪到新的数据页，保证新数据页的主键值一定大于上一个数据页的主键值，这个过程叫做也分裂。索引分类：主键索引、二级索引、聚簇索引、覆盖索引索引设计原则：》尽量使用基数值较大的字段》根据where、order by、group by等条件筛选符合条件的字段创建索引》对于比较长的字符串类型字段可以设计前缀索引》主键一定要自增，不要用UUID，防止聚簇索引引发频繁也分裂索引使用原则：》等值匹配原则》最左侧匹配原则》范围查询》最左前缀匹配原则：适用于模糊查询时，如like 'abc%'》等值匹配 + 最左侧匹配/最左前缀匹配/范围查询》尽量不要在查询语句中使用函数或者计算

2、解析SQL

描述数据块

MySQL主从复制原理slave有一个IO线程负责跟主库master建立TCP连接，并请求master将bin log日志传输过来，master的IO dump线程将bin log日志传输给IO线程，IO线程收到bin log日志后，将数据写到本地的relay日志文件，然后由SQL线程读取relay文件中的数据，并在slave上重新执行一次，达到数据还原的过程。MySQL数据库如何保证主从数据一致性》异步复制主库把日志写入bin log文件，接着提交事务返回，不管从库是否收到数据。主库宕机会导致丢失部分数据。》半同步复制     *AFTER COMMIT：主库写入日志到bin log，然后等待bin log复制到从库了，再提交自己本地事务，等slave返回成功响应后，主库返回提交事务成功的响应给客户端。     *主库写入日志到bin log，然后把bin log复制给从库，等待从库返回成功响应后，主库提交事务，并返回成功响应给客户端（5.7默认机制）；另外，MySQL5.7支持并行复制MySQL主从复制如何避免数据延迟数据延迟主要是因为MySQL多线程写入，单线程拉取数据，造成写入数据的速度高于读取数据的速度。如果要求写入数据后立马读取，可以通过mycat/shardingSphere配置强制读写主库

数据库连接池

数据页

LRU链表

e、提交事务，写redo log日志

flush链表

主键

redo log buffer

g、写binlog文件名和数据在binlog文件中的位置及写入commit标记

页目录

4、执行计划

undo

锁独占锁共享锁

Client

缓冲页

buffer pool：由若干chunk组成，多个chunk共享一套free、flush、哈希链表、LRU链表；而每个chunk由缓存页和描述数据块组成。

bin log

chunk(128MB)

c、更新缓存数据（脏数据）

               事务事务隔离机制MVCC多版本并发控制

a、加载数据页至缓存

b、记录事务提交前的旧值，便于回滚

Buffer Pool

d、写入redo log防止MySQL突然宕机

IO线程

MySQL高可用架构实现MySQL高可用架构的管理工具是MHAMySQL高可用架构原理：MHA分为两个节点，一个是Manager节点、一个是node节点；一般manager节点单独部署在一台服务器上，而node节点部署在每台MySQL服务器所在的机器上，因为node节点通过解析MySQL日志来进行一些操作；Manager节点通过探测集群里node节点来判断各个MySQL是否运行正常，当master故障时，manager将一个slave升为master，然后让其它slave挂到这个master上。

分库分表当数据量非常大时，通过分库分表来承载更高的并发。

独占锁之间互斥，独占锁和共享锁之间互斥

多线程并发访问导致的问题：脏写、脏读、不可重复读、幻读事务的隔离级别：读未提交、读已提交、可重复读、串行化事务隔离机制、多版本并发访问机制、锁机制解决多线程并发访问的问题

SQL优化器

flush链表：和free链表类似，只不过flush链表里面存储的是更新过的缓存页的描述数据块地址

free链表

哈希链表

磁盘文件（数据页）

undo log日志结构：undo log日志开始位置、主键、表id、undolog日志编号、undolog日志类型、undo log日志结束位置undo log版本链：undo log每行数据有两个隐藏指针，trx_id、roll_pointer，当多个事务串行执行时，都会更新trx_id、roll_pointer，而之前多个数据快照对应的undo log，通过roll_pointer指针串联起来，形成一条重要的版本链ReadView结构：   m_ids：执行当前事务时，MySQL服务有哪些正在运行的事务   min_trx_id：最小的事务id   max_trx_id：下次要创建的事务id   creator_trx_id：当前事务idReadView机制原理？基于undo log版本链条实现的一套读视图机制。意思是说你事务生成一个readView，如果是你事务自己更新的数据，或者你事务生成之前的事务更新的数据可以读到；如果是你事务生成之后或事务运行期间更新的数据不可读。

SQL接口

h、缓存页异步刷入磁盘

f、提交事务，写bin log日志

buffer pool

哈希表：每读取一个数据页到缓存页时就会在哈希表中记录一个k-v键值对，key：表空间号+数据页号，value：缓存页的地址；当下次使用这个数据页时可以直接从哈希表中获取

1、获取SQL

MySQL数据库服务器

redolog机制存在的意义：在事务提交时，绝对保证把你对缓存页做的修改以日志的形式写入到redo log日志文件里，防止MySQL突然宕机，而事务修改过的缓存页还没来得及刷入磁盘，导致buffer pool中的缓存页全部丢失。但只要有redo log，MySQL就可以依据redo对buffer pool里的数据进行重做，从而保证事务提交之后，修改过的数据绝对不会丢失。redo log长什么样子：日志类型（类似MLOG_1BYTE)+表空间号+数据页号+磁盘文件偏移量+修改过几个字节的值+具体的值

free链表：是一个双向链表数据结构，每个节点就是一个空闲缓存页的描述数据块的地址。对于free链表而言，只有一个基础节点不属于Buffer Pool，它是40字节大小的一个节点，里面包含链表的头节点地址，尾节点地址，还有当前free链表里有多少节点

SQL解析器

槽

索引