首先我们带着下边三个问题来认识存储引擎
- 存储引擎在MySQL中的作用是什么?
- MySQL都有哪些存储引擎
- SQL又与存储引擎有什么关系?
存储引擎在MySQL中的作用是什么?
顾名思义,存储引擎就是用于存储我们的数据的。在关系型数据库中我们一般将数据库存放在表中(Table)。
我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。
在MySQL中,支持多种存储引擎,他们是可以替换的,所以叫插件式的存储引擎。为什么要弄这么多存储引擎呢?一种还不够用吗?
MySQL都有哪些存储引擎?
可以通过下边命令,查询已存在表的存储引擎
show table status from `data_test`;
在MySQL里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且,创建表之后还可以修改存储引擎。
下面我们简单创建三种类型的存储引擎做测试
CREATE TABLE `user_innoDB` (
`id` int(5) NOT NULL,
`name` varchar(255) NULL,
`age` int(4) NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `user_MEMORY` (
`id` int(5) NOT NULL,
`name` varchar(255) NULL,
`age` int(4) NULL,
PRIMARY KEY (`id`)
) ENGINE = MEMORY;
CREATE TABLE `user_MyISAM` (
`id` int(5) NOT NULL,
`name` varchar(255) NULL,
`age` int(4) NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM;
存储引擎在服务器上它们是怎么存储的呢?我们先要找到数据库存放数据的路径:
show variables like 'datadir';
进入我们创建的DataBase下就可以看到,上边我们刚创建的3种引擎类型的表。
任何一个存储引擎都有一个frm文件,这个是表结构定义文件。
不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb是1个,memory没有,myisam是两个。
各引擎特点说明
类型
说明
特点
适合场景
InnoDB
默认存储引擎
支持事务、行级锁和外键、支持读写并发,写不阻塞读(MVCC)、特殊的索引存放方式,可以减少IO,提升查询效率
经常更新的表,存在并发读写或者有事务处理的业务系统
MEMORY
将表中的数据存储到内存中
把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失。只适合做临时表
不建议使用
MyISAM
不支持事务,也不支持外键,尤其是访问速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎
支持表级别的锁(插入和更新会锁表)。不支持事务。拥有较高的插入(insert)和查询(select)速度。存储了表的行数(count速度更快)
只读之类的数据分析的项目
CSV
带有逗号分隔值的文本文件
不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之间导入导出
ARCHIVE
这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息
不支持索引,不支持update 、delete
PERFORMANCE_SCHEMA
FEDERATED
BLACKHOLE
MRG_MYISAM
TIPS:怎么快速向数据库插入100万条数据?先用MyISAM插入数据,然后修改存储引擎为InnoDB。
在实际应用中可以根据不同的业务场景来选择不同的存储引擎,如果以上存储引擎都不能满足你的需求,并且你的技术能力足够,可以根据官网内部手册用C语言自己开发一个存储引擎:
https://dev.mysql.com/doc/internals/en/custom-engine.html
SQL又与存储引擎有什么关系?
SQL无法独立执行,他是通过执行引擎执行的,执行引擎是通过公共的API调用存储引擎并返回结果。
模块
说明
Connector
用来支持各种语言和SQL的交互,比如PHP,Python,Java的JDBC
Management Serveices & Utilities
系统管理和控制工具,包括备份恢复、MySQL复制、集群等
Connection Pool
连接池,管理需要缓冲的资源,包括用户密码权限线程等
SQL Interface
用来接收用户的SQL命令,返回用户需要的查询结果
Parser
用来解析SQL语句
Optimizer
查询优化器
Cache and Buffer
查询缓存,除了行记录的缓存之外,还有表缓存,Key缓存,权限缓存等
Pluggable Storage Engines
插件式存储引擎,它提供API给服务层使用,跟具体的文件打交道
insert & update & delete 是如何执行的?
缓冲池(Buffer Pool)
InnoDB的数据都是放在磁盘上的,InnoDB操作数据有一个最小的逻辑单位,叫做页(索引页和数据页)。我们对数据的操作,不是每次都直接操作磁盘,因为磁盘的速度太慢。InnoDB使用了一种缓冲池的技术,就是把磁盘读到的页放到一块内存区域里。这个内存区域就叫Buffer Pool。
下次读取相同的页,先判断是不是在缓冲池中,如果是,就直接读取,不用再次访问磁盘。
修改数据的时候,先修改缓冲池中的页。内存数据页和磁盘数据不一致的时候我们把他叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘,每隔一段时间就一次性把多个修改写入磁盘,这个动作就叫做刷脏。
Buffer Pool是InnoDB里面非常重要的一个结构,它的内部又分成几块区域。
InnoDB内存结构和磁盘结构
BufferPool主要分为3个部分:Buffer Pool、Change Buffer、Adaptive Hash Index,另外还有一个(redo)log buffer。
Buffer Pool
BufferPool缓存的是页面信息,包括数据页、索引页。
# 查看服务器状态
# 详细说明https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
show status like '%innodb_buffer_pool%';
BufferPool默认大小是128M(134217728字节),可以调整。
# 查看系统变量
# 详细说明https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
show variables like '%innodb_buffer_pool%';
内存的缓冲池写满了怎么办?(Redis设置的内存满了怎么办?)InnoDB用LRU算法来管理缓冲池(链表实现,不是传统的LRU,分成了young和old),经过淘汰的数据就是热点数据。
Change Buffer 写缓冲
如果这个数据页不是唯一索引,不存在数据重复的情况,也就不需要从磁盘加载索引页判断数据是不是重复(唯一性检查)。这种情况下可以先把修改记录在内存的缓冲池中,从而提升更新语句(Insert、Delete、Update)的执行速度。
最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge?
有几种情况:在访问这个数据页的时候,或者通过后台线程、或者数据库shut down、redo log写满时触发。
如果数据库大部分索引都是非唯一索引,并且业务是写多读少,不会在写数据后立刻读取,就可以使用Change Buffer(写缓冲)。
# 如果数据库大部分索引都是非唯一索引,并且业务是写多读少,不会在写数据后立刻读取,可以调整Change Buffer
# 写多读少的业务,可以调大这个值
# Change Buffer占Buffer Pool的比例,默认25%
show variables like 'innodb_change_buffer_max_size';
Adaptive Hash Index
索引应该是放在磁盘的,为什么要专门把一种哈希的索引放到内存?
下章详细讲解索引时说明。
(redo)Log Buffer
思考一个问题:如果Buffer Pool里面的脏页还没有刷入磁盘时,数据库宕机或者重启,这些数据丢失。如果写操作写到一半,甚至可能会破坏数据文件导致数据库不可用。
为了避免这个问题,InnoDB把所有对页面的修改操作专门写入一个日志文件,并且在数据库启动时从这个文件进行恢复操作(实现crash-safe)——用它来实现事务的持久性。
这个文件就是磁盘的redolog(叫做重做日志),对应于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1,每个48M。
这种日志和磁盘配合的整个过程,其实就是MySQL里的WAL技术(Write-Ahead Logging),它的关键点就是先写日志,再写磁盘。
# 查看Log日志相关配置
show variables like 'innodb_log%';
同样是写磁盘,为什么不直接写到dbfile里面去?为什么先写日志再写磁盘?
我们先来了解一下随机I/O和顺序I/O的概念。
磁盘的最小组成单元是扇区,通常是512个字节。
操作系统和内存打交道,最小的单位是页Page。
操作系统和磁盘打交道,读写磁盘,最小的单位是块Block。
如果我们所需要的数据是随机分散在不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到我们所需要的一块数据,一次进行此过程直到找完所有数据,这个就是随机IO,读取数据速度较慢。
假设我们已经找到了第一块数据,并且其他所需的数据就在这一块数据后边,那么就不需要重新寻址,可以依次拿到我们所需的数据,这个就叫顺序IO。
刷盘是随机I/O,而记录日志是顺序I/O,顺序I/O效率更高。因此先把修改写入日志,可以延迟刷盘时机,进而提升系统吞吐。
当然redo log也不是每一次都直接写入磁盘,在Buffer Pool里面有一块内存区域(Log Buffer)专门用来保存即将要写入日志文件的数据,默认16M,它一样可以节省磁盘IO。
注意:redo log的内容主要是用于崩溃恢复。磁盘的数据文件,数据来自buffer pool。redo log写入磁盘,不是写入数据文件。
Log Buffer什么时候写入log file?
在我们写入数据到磁盘的时候,操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。
# logbuffer写入磁盘的时机,由一个参数控制,默认是1
show variables like 'innodb_flush_log_at_trx_commit';
值
含义
0(延迟写)
logbuffer将每秒一次地写入logfile中,并且logfile的flush操作同时进行。该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
1(默认,实时写,实时刷)
每次事务提交时MySQL都会把logbuffer的数据写入logfile,并且刷到磁盘中去。
2(实时写,延迟刷)
每次事务提交时MySQL都会把logbuffer的数据写入logfile。但是flush操作并不会同时进行。该模式下,MySQL会每秒执行一次flush操作。
redo log 特点
- redolog是InnoDB存储引擎实现的,并不是所有存储引擎都有。
- 不是记录数据页更新之后的状态,而是记录这个页做了什么改动,属于物理日志。
- redolog的大小是固定的,前面的内容会被覆盖。
以上是MySQL的4种内存结构,磁盘结构里主要是各种各样的表空间,叫做Table space。
磁盘结构
表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。InnoDB的表空间分为5大类。
系统表空间(system tablespace)
在默认情况下InnoDB存储引擎有一个共享表空间(对应文件/var/lib/mysql/ibdata1),也叫系统表空间。
InnoDB系统表空间包含InnoDB数据字典和双写缓冲区,Change Buffer和Undo Logs),如果没有指定file-per-table,也包含用户创建的表和索引数据。
- undo在后面介绍,因为有独立的表空间
- 数据字典:由内部系统表组成,存储表和索引的元数据(定义信息)
- 双写缓冲(InnoDB的一大特性)
InnoDB的页和操作系统的页大小不一致,InnoDB页大小一般为16K,操作系统页大小为4K,InnoDB的页写入到磁盘时,一个页需要分4次写。
如果存储引擎正在写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,比如只写了4K,就宕机了,这种情况叫做部分写失效(partialpagewrite),可能会导致数据丢失。
# innoDB双写开关
show variables like 'innodb_doublewrite';
我们不是有redo log吗?但是有个问题,如果这个页本身已经损坏了,用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前,需要一个页的副本。如果出现了写入失效,就用页的副本来还原这个页,然后再应用redo log。这个页的副本就是double write,InnoDB的双写技术。通过它实现了数据页的可靠性。
跟redo log一样,double write由两部分组成,一部分是内存的double write,一个部分是磁盘上的double write。因为double write是顺序写入的,不会带来很大的开销。
在默认情况下,所有的表共享一个系统表空间,这个文件会越来越大,而且它的空间不会收缩。
独占表空间file-per-table tablespaces
# 我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置。
show variables like 'innodb_file_per_table';
开启后,则每张表会开辟一个表空间,这个文件就是数据目录下的ibd文件(例如/var/lib/mysql/data_test/user_innodb.ibd),存放表的索引和数据。但是其他类的数据,如回滚(undo)信息,插入缓冲索引页、系统事务信息,二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。
通用表空间general tablespaces
通用表空间也是一种共享的表空间,跟ibdata1类似。可以创建一个通用的表空间,用来存储不同数据库的表,数据路径和文件可以自定义。
# 创建一个通用的表空间
create tablespace tabtest123 add datafile '/var/lib/mysql/tabtest123.ibd' file_block_size=16K engine=innodb;
# 创建表时指定表空间
create table user (id integer) tablespace tabtest123;
临时表空间 temporary tablespaces
存储临时表的数据,包括用户创建的临时表,和磁盘的内部临时表。对应数据目录下的ibtmp1文件。当数据服务器正常关闭时,该表空间被删除,下次重新产生。
Redo log
磁盘结构里面的redo log,在前面已经介绍过了。
undo log tablespace
undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态(不包括select)。
如果修改数据时出现异常,可以用undo log来实现回滚操作(保持原子性)。
在执行undo的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从物理页面上操作实现的,属于逻辑格式的日志。
redo Log和undo Log与事务密切相关,统称为事务日志。
undo Log的数据默认在系统表空间ibdata1文件中,因为共享表空间不会自动收缩,也可以单独创建一个undo表空间。
# 查看撤销日志相关参数
show global variables like '%undo%';
mysql后台线程
后台线程
说明
master thread
负责刷新缓存数据到磁盘并协调调度其它后台进程
IO thread
分为insert buffer、log、read、write进程。分别用来处理insert buffer、重做日志、读写请求的IO回调
purge thread
用来回收undo页
page cleaner thread
用来刷新脏页
除了InnoDB架构中的日志文件,MySQL的Server层也有一个日志文件,叫做binlog,它可以被所有的存储引擎使用。
Binlog
binlog以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是数据值,属于逻辑日志),可以用来做主从复制和数据恢复。
跟redo log不一样,它的文件内容是可以追加的,没有固定大小限制。在开启了binlog功能的情况下,我们可以把binlog导出成SQL语句,把所有的操作重放一遍,来实现数据的恢复。
binlog的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的binlog,然后执行一遍。