文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL 存储引擎(2)

2016-06-21 13:38

关注

MySQL 存储引擎(2)

首先我们带着下边三个问题来认识存储引擎

存储引擎在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调用存储引擎并返回结果。

mysql 体系结构图

模块 说明
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

下次读取相同的页,先判断是不是在缓冲池中,如果是,就直接读取,不用再次访问磁盘。

修改数据的时候,先修改缓冲池中的页。内存数据页和磁盘数据不一致的时候我们把他叫做脏页。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 特点

  1. redolog是InnoDB存储引擎实现的,并不是所有存储引擎都有。
  2. 不是记录数据页更新之后的状态,而是记录这个页做了什么改动,属于物理日志。
  3. redolog的大小是固定的,前面的内容会被覆盖。

以上是MySQL的4种内存结构,磁盘结构里主要是各种各样的表空间,叫做Table space。

磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。InnoDB的表空间分为5大类。

系统表空间(system tablespace)

在默认情况下InnoDB存储引擎有一个共享表空间(对应文件/var/lib/mysql/ibdata1),也叫系统表空间。

InnoDB系统表空间包含InnoDB数据字典双写缓冲区Change BufferUndo Logs),如果没有指定file-per-table,也包含用户创建的表和索引数据。

  1. undo在后面介绍,因为有独立的表空间
  2. 数据字典:由内部系统表组成,存储表和索引的元数据(定义信息)
  3. 双写缓冲(InnoDB的一大特性

InnoDB的页和操作系统的页大小不一致,InnoDB页大小一般为16K,操作系统页大小为4K,InnoDB的页写入到磁盘时,一个页需要分4次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,比如只写了4K,就宕机了,这种情况叫做部分写失效(partialpagewrite),可能会导致数据丢失。

# innoDB双写开关
show variables like "innodb_doublewrite";

我们不是有redolog吗?但是有个问题,如果这个页本身已经损坏了,用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前,需要一个页的副本。如果出现了写入失效,就用页的副本来还原这个页,然后再应用redo log。这个页的副本就是double write,InnoDB的双写技术。通过它实现了数据页的可靠性。

跟redolog一样,double write由两部分组成,一部分是内存的double write,一个部分是磁盘上的double write。因为doublewrite是顺序写入的,不会带来很大的开销。

在默认情况下,所有的表共享一个系统表空间,这个文件会越来越大,而且它的空间不会收缩。

独占表空间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,然后执行一遍。

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯