MySQL 引擎 Innodb&MyISAM 知识点 底层原理 存储结构区别介绍

  1. MySQL总体架构

MySQL插件式的存储引擎体系

MyISAM引擎 (1) 特性

MyISAM引擎实现特点

1.索引和数据分开存放。索引中保存的是数据位置的指针。

2.有索引cache,索引块默认1KB大小,采用LRU算法替换。

3.没有数据cache,依赖操作系统的调度策略。

4.所有的操作都是同步的,没有后台线程。

MyISAM引擎 (2)
数据格式

数据格式

MyISAM数据有三种格式,可以再create table时指定,分别是Fixed-length,Dynamic,Compress模式

1.固定格式所有的row都固定长度,不足的部分补空格,方便快速访问。出错时也容易快速修复。但耗用空间较大。

2. Dynamic格式,所有的列紧凑排列,row首部采用位图标志null位好数字0位。但update之后可能导致行溢出,使用link指针维护一行数据。

3. Compress模式,只读模式,最大限度的节省空间。

innodb引擎

Innodb的目标

面向OLTP应用,高性能,可靠性高,数据安全。

实现特性

1. 完全的事务支持  2. 行级锁  3.
MVCC   4. 数据崩溃恢复  5. 高效的IO架构

依赖组件

1.insert buffer  2. double write  3 undo&redo

Innodb引擎架构

Innodb文件管理(1)

Ø数据使用表空间来组织,内部使用数字编号,系统表空间编号0.

1.系统表空间ibdata,

2.redo日志 ib_logfile,

3.每个表独立的表空间   table.ibd文件

Innodb文件管理(2)

每个表空间按照功能可区分为不同的段。每个段独立的扩张收缩需要的空间。

段增长以extend(1MB)为单位。每个extend包含64个page

每个page大小为16KB,按照不同类型格式化。

Innodb文件管理3:系统表空间

内部数据字典:按B树组织,包含了frm中定义的所有内容。

UNDO日志:回滚段,用于实现事务和MVCC

Insert
buffer:优化对second index的插入操作。B树组织。
Double Write Buffer:用于保证O正确性,位于系统表空间的第2-3个extent,总共128个page,2MB。

Innodb内存管理(1)Buffer
pool

buffer
pool是一个很大的内存池,在mysql启动时即分配buffer_pool_size的内存空间。由于使用mmap方式,在虚拟内存地址中分配。

buffer
pool分为16KB大小的block。以block为单位进行分配。所有的block维护在链表中。同时通过一个hash索引使用(space_id,page_no)做作为键值维护表空间的page到内存block的映射。

通过连个链表来管理block

UT_LIST_BASE_NODE_T(buf_page_t)
free;

UT_LIST_BASE_NODE_T(buf_page_t)
LRU;

刷脏页分两个过程   LRU_flush  和
Flush_flush

Innodb内存管理(2)LRU 算法

初始读取的page放入OLD头部。

被访问之后放入YOUNG的头部。page pin在OLD部位一定时间防止全表扫描导致的page 汰换。

LRU
flush时从LRU链表的尾部逆向查找脏页刷盘。

Innodb内存管理(3)

有关buffer
pool的状态变量

Innodb_buffer_pool_read_requests:
innodb进行逻辑读次数。

Innodb_buffer_pool_reads   物理读次数

Innodb_buffer_pool_pages_flushed  脏页写盘数,单位为page

Innodb_buffer_pool_wait_free:

Innodb_buffer_pool_pages_dirty

Innodb_buffer_pool_pages_misc

Innodb_buffer_pool_pages_total

Innodb 线程并发控制

前台线程。

innodb_thread_concurrency控制进入innodb内部线程的数量。等待线程在FIFO队列中排队。为保证公平,每个线程进入innodb内部的次数受 innodb_concurrency_tickets 限制,消耗完ticket之后即进入FIFO队列排队。

后台线程。

线程 作用
Master_threads 主线程
Mutex
monitor
锁检测
Semaphore
monitor
信号量检测
IO
read
预读线程
IO  Write 刷脏页线程
IO  log 日志线程,purge
IO  Insert Buffer 插入缓冲的合并

Innodb IO系统(1)

同步IO。

1. 用户线程缺页导致的读是同步的,在用户线程中完成,减少线程调度

2. 由于没有空闲页可分配时,发生同步的flush
操作。等待脏页刷盘。

3. 日志的刷盘都发生在用户线程。

异步IO(AIO)

insert buffer thread  负责merge 插入缓冲

log 线程 负责purge log

read
thread  负责预读(异步读)

write thread    负责脏页刷盘   (异步写)

Innodb IO系统(2)预读(写)算法

随机预读。(逻辑随机)

随机预读针对一个extend中的64个page,当一个page第一次被从文件读取时,判断该extend中已经被读取且被访问的page数目,如果超过一个阈值,则将整个extend都读到bufferpool中。

Innodb_buffer_pool_read_ahead_rnd

顺序预读。(逻辑线性)

顺序预读是在一个page第一次被访问时,如果该page是一个extent的边界,且该extend的读取顺序为线性(异常点低于一个阈值)则将顺序范围内的下一个extent(1MB)都读到内存中。预读是异步的。

Innodb_buffer_pool_read_ahead_seq

顺序写盘

当一个page需要被刷回磁盘时,扫描该page所在的extent中所有的page,如果存在脏页,则将这些脏页合并刷盘

Innodb 索引  (1)
主键索引

数据按主键索引聚簇

整个表实现为一科B树

页节点和非叶节点分开存放

一个node就是一个page

Innodb 索引  (2)
备选索引

secondary
index中包含所有索引列以及primary
key的内容。

使用primary key访问聚簇索引来获取能够得到的数据。

HINT

1.尽可能使用小的primary
key,auto_incrment是好选择。

2. 在备询索引上使用覆盖查询

3.不要依赖备询索引执行范围查询,将导致大量随机IO

Innodb 索引  (3)自适应哈希

InnoDB监控对表上索引的查找,如果发现对对buffer_pool中某些记录的访问频率较高,则会选择建立自适应哈希索引,索引的主键是该索引的key或者前缀。

自适应哈希索引可以提高读操作性能。对于join操作,变相实现了hash
join算法。

缺点:带来全局锁定,在更新并发较大时,自适应哈希的锁定成为瓶颈。

Innodb 日志 (1) redo日志

1.redo日志记录的是物理日志,对每个page的修改

2.LSN是写入日志的字节偏移。在redo日志的头部记录每次checkpoint的LSN,之前的log可以废弃。

3.崩溃恢复时,将redo日志中的LNS和数据page中的LSN比较,之后比页面LSN大的redo日志才会aplly到page中。

Innodb 日志 (2) undo日志和MVCC

1.每个事务进入innodb内部会创建一个read_view,确定其可见数据的范围,使用trx_id来标示。

2.根据设定的隔离级别判断数据的可见性,只要还有活跃事务需要,undo日志就不会被清理。

3.innodb使用undo日志来实现非锁定读(不带显示lock的select)

由于事务隔离级别的存在,innodb当中不存在精确行数

Innodb  锁管理(1)

1.使用一个hash表管理所有的行锁,行锁按page组织。相同page上的锁组织成一个链表。通过此链表检查相容性。

2.相同事务对同一个page上的所有记录可以共用一个锁结构,使用位图标志是否锁定。

Innodb  锁管理(2)

所有的锁位图相兼容才可以加锁

Innodb 特性 InsertBuffer&
DoubleWrite

Insert Buffer

对secondary Indexes的写入优化。

对主键的顺序写入带来secondary
index上的随机IO,通过将secondary index 的写入缓冲起来,异步的merge,在IO bound的系统中性能提升非常高。Insert
Buffer按B树组织,其主键是(space_id,page_no…..)的组合,可以合并写入。

前提:非聚簇,非唯一索引。

Double Write

2MB大小的缓冲区,对应系统表空间的2个extent。

先将page写入到Double Write Buffer中,分两次每次1MB刷盘,之后再执行随机IO将page写到对应位置。

缺点:导致IO写入放大,写两次


数据运维技术 » MySQL 引擎 Innodb&MyISAM 知识点 底层原理 存储结构区别介绍