MySQL存储引擎

常用存储引擎

mysql的存储引擎有：
InnoDB[MySQL 5.5或更高版本的默认存储引擎]、MyISAM[管理非事务性表，提供高速存储和检索，支持全文搜索]、MEMORY[提供内存中的表，用于快速查找引用和其他相同的数据]、MERGE[将多个类似的MyISAM表分组为一个表，可以处理非事务性表，默认情况下包括这些表]、EXAMPLE[可以使用此引擎创建表，但不能存储或获取数据]、ARCHIVE[用于存储大量数据，不支持索引]、CSV[在文本文件中以逗号分隔值格式存储数据]、BLACKHOLE[受要存储的数据，但始终返回空]、FEDERATED[将数据存储在远程数据库中].
 

常用存储引擎有：
（1）MyISAM存储引擎
MyISAM存储引擎是MySQL最常用的引擎。
它管理的表具有以下特征：

使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间
优势：占用空间小，处理速度快。 缺点：不支持事务的完整性和并发性。

（2）InnoDB存储引擎
InnoDB存储引擎是MySQL的缺省引擎。
它管理的表具有下列主要特征：

每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
提供全ACID兼容
在MySQL服务器崩溃后提供自动恢复
多版本（MVCC）和行级锁定
支持外键及引用的完整性，包括级联删除和更新
优势：提供了良好的事务处理、崩溃修复能力和并发控制。 缺点：读写效率较差，占用的数据空间相对较大。

（3）MEMORY存储引擎
使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
MEMORY存储引擎管理的表具有下列特征：

在数据库目录内，每个表均以.frm格式的文件表示。
表数据及索引被存储在内存中。
表级锁机制。
不能包含TEXT或BLOB字段。
MEMORY存储引擎以前被称为HEAP引擎。

选择合适的存储引擎

MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

查看系统支持的存储引擎

mysql> show engines;

 如何查看表所使用的存储引擎

1.

show table status from 库名where name=‘表名’\G

2.

use 库名;
show create table 表名;

如何修改存储引擎

1.

alter table 表名 engine=存储引擎 ;

2.

vim /etc/ my.cnf                 #修改letc/my.cnf 配置文件指定默认存储引擎
.....
[mysqld]......
default-storage-engine=INNODB
:wq

systemctl restart mysql.service  #重启服务

3.

通过create table创建表时指定存储引擎
use 库名;
create table表名(字段1数据类型，… .) engine=MyISAM;

InnoDB概述

InnoDB引擎是由InnobaseOy公司开发，是现在MySQL数据库默认引擎，具体发展历程不在本文讨论范围内。其实MySQL还有其他引擎如NDB、Memory、Archive、MyISAM等它们被设计应用在不同业务场景，InnoDB存储引擎的设计目标是面向在线事务处理（OLTP)的应用。以下是我从官网摘下来的InnoDB存储引擎功能清单。

nnodb是第一个完整支持ACID事务的mysql存储引擎,特点是行锁设计,支持mvcc,支持外检,提供一致性非锁定读,同事被设计用来最有效地利用及上使用内存和cpu

InnoDB体系

innodb有多个内存块,可以认为这些内存块组成了一个大的内存池,负责
维护所有进程/线程需要访问的多个内部数据结构

后台线程

innodb是多线程模型
Master Thread:核心的后台线程,负责将缓冲区数据异步刷新到磁盘,保证数据的一致性,包括脏页的刷新,合并插入缓冲,undo页的回收
IO Thread:使用了大量AIO处理写io请求,而Io thread主要负责这些io请求的回调处理,目前5.7版本linux环境 有10个线程 4个read 4个write 1个insert buffer 1个log
Purge Thread:事务被提交后,其使用的undo log可能不需要了,英雌purgeThread需要回收已经使用并分配的undo页,从innodb1.1之后开始从master thread交给了purge thread 为了减轻master thread 压力
Page Cleaner Thread:在innodb1.2.x版本引入为了处理咱也刷新操作,同时减轻master thread 压力
 

内存

缓冲池

innodb基于磁盘存储,并将记录按照页的方式进行管理,因此可将其认为基于磁盘的数据库系统;在数据库系统中,由于cpu和磁盘速度相差巨大,所以基于磁盘的系统通常使用缓冲池来提高数据库整体性能;
缓冲池简单来说是一块内存区域,通过内存速度弥补磁盘对性能影响.在数据库进行读取页操作时,首先从磁盘读到的页放在缓冲池,这个过程叫将页"FIX"在缓冲池中.下次再读相同的页时,先判断缓冲池中是否存在
对于修改,先修改缓冲池的页,然后再以一定频率刷新到磁盘上,需要注意的是,页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发,而是通过一种checkpoint的机制刷新回磁盘
缓冲池可通过 innodb_buffer_pool_size可设置,缓冲池中缓存的数据页类型有:索引页,数据页,undo也,插入缓冲insert buffer,自适应哈希索引,innodb存储的锁信息,数据字典信息
 

LRU list 和 Flush list 和Free list

通常来说,数据库中的缓冲池是通过LRU(Latest Recent Used),最少使用算法进行管理的.使用最频繁的页在LRU列表最前端,最少使用的页在LRU列表尾端,当缓冲池不能存放新读取的数据时,先释放LRU列表中尾端的页
在innodb中,缓冲池默认的页大小是16kb,同样使用LRU算法对缓冲池进行管理.但是innodb做了一些改动,增加了midpoint位置,新都区的页不直接放入到LRU列表的首部,而是放入lru列表的midpoint位置,该位置默认在LRU列表的5/8处. midpoint位置可以由innodb_old_blocks_pct控制
 

重做日志缓冲

内存区除了缓冲池外,还有重做日志缓冲,innodb首先将重做日志信息先放入到这个缓冲区,然后按着一定的频率将其刷新到重做日志文件,
重做日志缓冲一般不大,默认8mb,因为每秒都会对重做日志刷新到磁盘上,可通过innodb_log_buffer_size控制
有以下三种情况会将重做日志缓冲刷新到磁盘上
1.master thread 每秒执行
2.每个事务提交后
3.当重做日志缓冲小于1/2时
 

额外的内存池

Checkpoint技术

当执行了一条dml语句,就会造成缓冲区与磁盘数据不一致,产生脏页,就要刷入磁盘,这样开销会很大;同时为了避免数据丢失采用write ahead log策略 ,事务提交时先写入重做日志然后在修改页;
一但出现宕机可以通过重做日志回复数据, 但会有以下两个问题
1.重做日志持续增大
2.缓冲区能否承担所有数据
显然以上两个问题需要解决, 这时推出了 checkpoint检查点技术 可以解决以下几个问题
1.缩短数据库的恢复时间
2.缓冲池不够用时,将脏页刷新到磁盘
3.重做日志不可用时,刷新脏页
当数据库发生宕机时,不需要所有重做日志,因为checkpoint之前的页都已经刷新回磁盘了
 

Master Thread工作方式

master thread具有最高的线程优先级别.内部由多个循环loop组成,主循环,后台循环,刷新循环,暂停循环,Master Thread 会根据数据库运行的状态在循环之间切换

innodb关键特性

插入缓冲 insert buffer
两次写 double write
自适应哈希索引 adaptive hash index
异步io async io
刷新邻接页flush neighbo page
这些特性为innodb带来了更好的性能和更高的可靠性

插入缓冲 insert buffer

innodb中聚簇索引即主键id,插入顺序和主键递增的顺序基本保持一致;但是对于非聚簇索引且索引值不唯一,在进行插入操作时,数据页的存放还是按着主键a进行顺序存储的,但是对于非聚簇索引叶子节点的插入不在是顺序的;
为了解决非聚簇索引这种情况,开创性地设计了insert buffer,对于非聚簇索引的操作,不是每次插入索引页中,而是先判断是否在缓冲池中,若在直接插入;若不在,先放入到insert buffer对象中,然后以一定频率和情况进行insert buffer 和 辅助索引页 子节点的merge操作,这时通常能将多个插入合并到一个操作中,大大提高了非聚簇索引插入性能
但是使用insert buffer要满足以下两个条件
1.索引是辅助索引或者说二级索引sencondary index
2.索引不是惟一的, 目的是 不需要再去判断记录的唯一性
但如果某个时刻突然宕机了 ,insert buffer的数据还未合并到实际的非聚簇索引中去, 恢复起来就麻烦了
 

change buffer

insert buffer升级版,在1.0.x之后加入,innodb引擎可以对dml操作, insert delete update 进行缓冲对应的是insert buffer delete buffer purge buffer
其中对一条记录进行update 需要两步
1.将记录标记为已删除 ->delete
2.真正删除 ->purge
 

insert buffer内部实现

insert buffer的数据结构是一颗B+树,在4.1之后版本,全局只有一个B+树负责所有表二级索引的insert buffer处理;
insert buffer是一颗B+树,由叶子节点和非叶子节点组成,非叶子节点存放的是查询的search key键值

自适应哈希索引

innodb监控表中各索引查询情况,如果发现建立哈希索引能带来大的优化,就建立哈希索引 adaptive hash index
建立AHI一个要求是,对这个页的访问模式必须一样 ,即查询条件一样

异步io

为了提高磁盘操作性能,数据采用了异步ioaio处理磁盘
AIO好处1是不用等待耗时操作 2是可以进行io merge,将多个io合并为1个io

刷新邻接页

工作原理是:当刷新一个脏页时,innodb会检测该页所在区的所有页,如果是脏页,那么就一起刷新
需要注意:随着ssd速度升高 这个功能主要针对磁盘

启动,关闭与恢复

在关闭时,参数innodb_fast_shutdown影响着表的存储引擎的行为该值可谓0,1,2默认为1
0:关闭数据库时,innodb需要完成所有与full purge 和 merge insert buffer 所有脏页写回磁盘
1:默认设置,不需要完成full purge 和 merge insert buffer ,但是缓冲区的脏页还是会刷新到磁盘
2.不进行任何操作,只是将日志写到日志文件中,然后下次启动进行服务操作