MySQL 存储引擎(2)

Wesley13
• 阅读 591

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

  • 存储引擎在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种引擎类型的表。 MySQL 存储引擎(2)

任何一个存储引擎都有一个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 存储引擎(2)

模块

说明

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。

MySQL 存储引擎(2)

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

修改数据的时候,先修改缓冲池中的页。内存数据页和磁盘数据不一致的时候我们把他叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘,每隔一段时间就一次性把多个修改写入磁盘,这个动作就叫做刷脏

Buffer Pool是InnoDB里面非常重要的一个结构,它的内部又分成几块区域。

InnoDB内存结构和磁盘结构

MySQL 存储引擎(2)

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%';

MySQL 存储引擎(2)

BufferPool默认大小是128M(134217728字节),可以调整。

# 查看系统变量
# 详细说明https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
show variables like '%innodb_buffer_pool%';

MySQL 存储引擎(2)

内存的缓冲池写满了怎么办?(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)——用它来实现事务的持久性

MySQL 存储引擎(2)

这个文件就是磁盘的redolog(叫做重做日志),对应于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1,每个48M。

这种日志和磁盘配合的整个过程,其实就是MySQL里的WAL技术(Write-Ahead Logging),它的关键点就是先写日志,再写磁盘。

# 查看Log日志相关配置
show variables like 'innodb_log%';

MySQL 存储引擎(2)

同样是写磁盘,为什么不直接写到dbfile里面去?为什么先写日志再写磁盘?

我们先来了解一下随机I/O和顺序I/O的概念。

磁盘的最小组成单元是扇区,通常是512个字节。

操作系统和内存打交道,最小的单位是页Page。

操作系统和磁盘打交道,读写磁盘,最小的单位是块Block。

MySQL 存储引擎(2)

如果我们所需要的数据是随机分散在不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到我们所需要的一块数据,一次进行此过程直到找完所有数据,这个就是随机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';

MySQL 存储引擎(2)

含义

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次写。

MySQL 存储引擎(2)

如果存储引擎正在写入页的数据到磁盘时发生了宕机,可能出现页只写了一部分的情况,比如只写了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 存储引擎(2)

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,然后执行一遍。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这