存储引擎

一 MySQL组织架构

1、连接层

1.验证用户的身份，用户名密码是否匹配
2.提供两种连接方式（TCP/IP连接、socket连接）
3.连接层提供了一个与sql层交互的线程

				

					
				
				

					
				
				

					
				
				

					
				
			

2、SQL层

1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL,DML,DQL,DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
    6.1 与存储引擎层建立交互的线程
    6.2 将要执行的sql发给存储引擎层
7.如果有缓存，则走缓存
8.记录日志（如binlog）

				

					
				
				

					
				
				

					
				
				

					
				
			

3、存储引擎层

1.接收SQL层传来的语句
2.与磁盘交互，获取数据，返回给sql层
3.建立与sql层交互的线程

				

					
				
				

					
				
				

					
				
				

					
				
			

什么是存储引擎？？？

mysql中建立的库===>文件夹
 
库中建立的表===>文件
 
现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等
 
数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎，mysql根据不同的表类型会有不同的处理机制
 
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和
操作此表的类型）
 
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据
自己的需要编写自己的存储引擎
 
SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每 个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设 计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这 么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据 的查询。 

				

					
				
				

					
				
				

					
				
				

					
				
			

4、mysql查询过程

二 查看存储引擎信息

查看所有支持的存储引擎

MariaDB [(none)]> show engines\G

				

					
				
				

					
				
				

					
				
				

					
				
			

innodb存储引擎支持的核心特性

事务              
行级锁：innodb支持行级锁，myisam是表级锁，锁的粒度越小并发能力越强
外键
MVCC        多版本并发控制
备份和恢复   innodb支持支持热备，myisam不支持
自动故障恢复 （CSR） Crash Safe Recovery

				

					
				
				

					
				
				

					
				
				

					
				
			

了解

01）InnoDB
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其
特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 
 
从 MySQL 5.5.8 版本开始是默认的存储引擎。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。
 
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。
 
02）MyISAM
只是读取和插入，不做修改和删除使用这个，MyISAM不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。
 
03）MEMORY       支持hash索引，使用redis替换
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。
 
04）BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。
 
05）NDB 存储引擎
2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
 
06）Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
 
07）NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。
 
08）ARCHIVE
09）FEDERATED
10）EXAMPLE
11）MERGE
12）NDBCLUSTER
13）CSV
 
#还可以使用第三方存储引擎:
01）MySQL当中插件式的存储引擎类型
02）MySQL的两个分支
03）perconaDB
04）mariaDB

				

					
				
				

					
				
				

					
				
				

					
				
			

查看正在使用的存储引擎

show variables like 'storage_engine%';
或者
SELECT @@default_storage_engine;

				

					
				
				

					
				
				

					
				
				

					
				
			

查看innodb的表有哪些，通过查表information_schema.tables来获取

# table_schema字段的值即表所在的库
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

				

					
				
				

					
				
				

					
				
				

					
				
			

查看myisam的表有哪些，通过查表information_schema.tables来获取

select table_schema,table_name,engine from information_schema.tables where engine='myisam';

				

					
				
				

					
				
				

					
				
				

					
				
			

查看表的存储引擎

SHOW CREATE TABLE db1.t1\G
或者
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'db1'\G

				

					
				
				

					
				
				

					
				
				

					
				
			

三 修改存储引擎

3.1 配置文件修改存储引擎

vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1

				

					
				
				

					
				
				

					
				
				

					
				
			

3.2 临时修改存储引擎

#在MySQL命令行中临时设置
SET @@storage_engine=myisam
 
#查看
SELECT @@default_storage_engine;

				

					
				
				

					
				
				

					
				
				

					
				
			

3.3 建表时修改存储引擎

CREATE TABLE egon(id INT) ENGINE=myisam;

				

					
				
				

					
				
				

					
				
				

					
				
			

四 存储引擎实验

创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试

MariaDB [db1]> create table t1(id int)engine=innodb;
MariaDB [db1]> create table t2(id int)engine=myisam;
MariaDB [db1]> create table t3(id int)engine=memory;
MariaDB [db1]> create table t4(id int)engine=blackhole;
MariaDB [db1]> quit
[root@egon db1]# ls /var/lib/mysql/db1/ #发现后两种存储引擎只有表结构，无数据
db.opt  t1.frm  t1.ibd  t2.MYD  t2.MYI  t2.frm  t3.frm  t4.frm
 
#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录

				

					
				
				

					
				
				

					
				
				

					
				
			

五 企业真实案例

1.项目背景：

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

				

					
				
				

					
				
				

					
				
				

					
				
			

2.小问题不断：

1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

				

					
				
				

					
				
				

					
				
				

					
				
			

3.如何解决：

1、提建议将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38
    1）如果使用MYISAM会产生”小问题”，性能安全不能得到保证，使用innodb可以解决这个问题。
    2）5.1.77版本对于innodb引擎支持不够完善，5.6.38版本对innodb支持非常完善了。
2、提出升级的方案
升级的方法
升级的时间
升级终会出现的问题
升级后出现的问题

				

					
				
				

					
				
				

					
				
				

					
				
			

1）准备一台新机器，安装mysql-5.6版本

源码包安装，二进制安装
 
# 配置yum源
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=1
gpgcheck=0
 
# 获取mysql5.7初始密码
grep "temporary password" /var/log/mysqld.log
 
# 设置密码，密码已经不能再用弱密码了，弱密码会报错
set password=password("Egon@123");

				

					
				
				

					
				
				

					
				
				

					
				
			

2）在旧机器上备份系统库以外的生产库数据

# –triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
# -R:–routines,导出存储过程以及自定义函数
 
mysqldump -uroot -p123 -B db1 --triggers -R  > /tmp/db1.sql

				

					
				
				

					
				
				

					
				
				

					
				
			

3）对备份数据进行处理（将engine字段替换）

[root@db01 ~]# sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#gi' /tmp/db1.sql

				

					
				
				

					
				
				

					
				
				

					
				
			

4）将备份的数据传到新的数据库服务器上

scp  rsync 硬件设备  NFS

				

					
				
				

					
				
				

					
				
				

					
				
			

5）将修改后的备份恢复到新库

mysql -uroot -p123 < /tmp/db1.sql

				

					
				
				

					
				
				

					
				
				

					
				
			

6）应用测试环境连接新库，测试所有功能

7）停应用，将备份之后的生产库发生的新变化，补偿到新库

8）应用割接到新数据库

六 lnnoDB逻辑架构

如下图所示，InnoDB的逻辑架构主要分为三个大的组成部分：
1、在内存中的架构（In-Memory Structures）；

2、操作系统缓存（Operating System Cache）。

3、在硬盘上的架构（On-Disk Structures）；

6.1 内存上的架构

InnoDB的内存架构分为4个部分：

• 1、缓冲池（Buffer Pool）；

  缓冲池是一块用于缓存被访问过的表和索引数据的内存区域，缓冲池允许在内存中处理一些被用户频繁访问的数据，在某一些专用的服务器上，甚至有可能使用80%的物理内存作为缓冲池。
   
  缓冲池的存在主要是为了通过降低磁盘IO的次数来提升数据的访问性能。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 2、写缓冲（Change Buffer）；

  写缓冲是为了缓存缓冲池（Buffer Pool）中不存在的二级索引(Secondary Index)页的变更操作的一种特殊的内存数据结构。
   
  这些变更通常是一些Insert、Update、Delete等DML操作引发的，如果有一些其它的读操作将这些被变更的二级索引页加进了缓冲池（Buffer Pool)，则这些变更会被马上合并至缓冲池中以保证用户可以读取到一致的数据。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 3、日志缓冲（Log Buffer）；

  InnoDB将数据的每次写优化为了批量写，这便以降低磁盘IO的次数，为了防止一些数据尚未写入硬盘就断电了，需要记录日志。
  而日志缓冲就是用来缓存一些即将要被写入磁盘日志文件（log files)中的数据。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 4、自适应哈希索引（Adaptive Hash Index)；

  在InnoDB中，用户是不可以直接去创建哈希索引的，这个自适应哈希索引是InnoDB为了加速查询性能，会根据实际需要来决定是否对于一些频繁需要被访问的索引页构建哈希索引，它会利用key的前缀来构建哈希索引。这样做可以提高查询性能，因为索引采用类似B+树的结构进行存储，B+树的单key查询时间复杂度为O(log2n),但是优化为哈希索引后，单key的查询时间复杂度就为O(1)了。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

6.2 操作系统缓存

储备知识：

fsync和write操作是系统调用函数，在很多持久化场景都有使用到，比如 Redis 的AOF持久化中也使用到两个函数。
fsync操作 将数据提交到硬盘中，强制硬盘同步，将一直阻塞到写入硬盘完成后返回，大量进行fsync操作就有性能瓶颈.
而write操作将数据写到系统的页面缓存后立即返回，后面依靠系统的调度机制将缓存数据刷到磁盘中去,其顺序是user buffer——> page cache——>disk。

				

					
				
				

					
				
				

					
				
				

					
				
			

操作系统缓存：

操作系统为了提升性能而降低磁盘IO的次数，在InnoDB的缓存体系与磁盘文件之间，加了一层操作系统的缓存/页面缓存。用户态innodb存储引擎的进程向操作系统发起write系统调用时，在内核态完成页面缓存写入后即返回，如果想立即将页面缓存的内容立即刷入磁盘，innodb存储引擎需要发起fsync系统调用才可以

				

					
				
				

					
				
				

					
				
				

					
				
			

O_DIRECT

选项是在Linux系统中的选项，使用该选项后，对文件进行直接IO操作，不经过文件系统缓存，直接写入磁盘

				

					
				
				

					
				
				

					
				
				

					
				
			

6.3 在硬盘上的架构

InnoDB在硬盘上总共分为六个部分，也就是：

• 1、表（Tables）；

  1、如果已经指定了数据的默认存储引擎，那么创建表的时候，无需指定再指定存储引擎。
   
  2、默认情况下，创建InnoDB表的时候innodb_file_per_table参数是开启的，它表明用户创建的表和索引，会被以单表单文件的形式放入到file-per-table表空间中。
   
  3、如果禁用了该参数innodb_file_per_table，那么表及索引会被放入系统表空间(System Tablespaces)中。
   
  4、如果创建表的时候，想要把表创建在通用表空间（General Tablespaces)中，那么需要用户使用CREATE TABLE … TABLESPACE语法来创建表结构。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 2、表空间（Tablespaces）；

  在InnoDB中，表空间总共分为：
  1、系统表空间（System Tablespaces）
  系统表空间主要用于存储双写缓冲、写缓存以及用户创建的表和索引（当innodb_file_per_table被禁用的情况下）
   
  2、file-per-table表空间（file-per-tableTablespaces）
  存储用户创建的表和索引数据，默认情况下（innodb_file_per_table参数是启用的）
   
  3、通用表空间（General Tablespaces）
  通用表空间允许用户存储一些自己想要放进通常表空间的表或数据，需要用户创建表的时候，自己指定采用通用表空间，上面讲表的时候已经介绍过。
   
  4、回滚表空间（Undo Tablespaces）
  回滚表空间是为了存储回滚日志，通常回滚日志在表空间会以回滚段(Undo Segments)的形式存在。
   
  5、临时表空间（Temporary Tablespaces）
  临时表空间用于存储用户创建的临时表，或者优化器内部自己创建的临时表。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 3、索引（Indexes）；

  按键的类别划分：主键索引和二级索引/辅助索引；
  按索引的类型分：BTree索引和自适应哈希索引；
  按存储结构划分：聚集索引和非聚集索引。
   
  索引存在的目的主要是为了加速数据的读取速度，InnoDB采用BTree（实际为优化改进后的B+树索引）。
   
  主键索引也是聚集索引，二级索引都是非聚集索引。
   
  自适应哈希索引是InnoDB为了加速查询性能，它自己按需在内存中对加载进内存的BTree索引优化为哈希索引的一种手段。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 4、双写缓冲（Doublewrite Buffer）；

  双写缓冲是一个在系统表空间System Tablespaces中存储区，在这个存储区中，在InnoDB将页面写入InnoDB数据文件中的适当位置之前，会先从缓冲池中刷新页面 。如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从双写缓冲中找到页面的原来的数据。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• 5、Redo日志：记录的是尚未完成的操作，断电则用其重做

redo即redo日志，是用于记录数据库中数据变化的日志，只要你修改了数据块那么就会记录redo信息，当然nologging除外了。
 
你的每次操作都会先记录到redo日志中，当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件

				

					
				
				

					
				
				

					
				
				

					
				
			

• 6、Undo段：记录的改动之前的旧数据，一旦改错，可以回滚

undo即undo段，是指数据库为了保持读一致性，存储历史数据在一个位置。
 
用于记录更改前的一份copy，用于回滚、撤销还原

				

					
				
				

					
				
				

					
				
				

					
				
			

​ 下面是redo log + undo log的简化过程，便于理解两种日志的过程：

假设有A、B两个数据，值分别为1,2.
1. 事务开始
2. 记录A=1到undo log
3. 修改A=3
4. 记录A=3到 redo log
5. 记录B=2到 undo log
6. 修改B=4
7. 记录B=4到redo log
8. 将redo log写入磁盘
9. 事务提交

				

					
				
				

					
				
				

					
				
				

					
				
			

6.4 innodb存储引擎执行流程

执行一条更新sql语句，存储引擎执行流程可以分为三大阶段，8个小步骤

三大阶段

• 1、执行阶段
  • 数据加载到内存，写undo log，更新内存中数据，写redo log buffer
• 2、事务提交阶段
  • redo log和binlog刷盘，commit标记写入redo log中
• 3、最后
  • 后台io线程随机把内存中脏数据刷到磁盘上

8个小步骤

1. 把该行数据从磁盘加载到buffer pool中，并对该行数据进行加锁
2. 把旧数据写入undo log，以便修改出错情况下的回滚
3. 在buffer pool中的数据更新，得到脏数据
4. 把修改后的数据写入到redo log buffer当中
5. 准备提交事务redo log刷入磁盘
6. 把修改的操作记录准备写入binlog日志
7. 把binlog的文件名和位置写入commit标记，commit标记写入redolog中（redo log中存放的修改后的数据与binlog中的修改操作对应上，双管齐下），事务才算提交成功；否则不会成功
8. IO线程Buffer Pool中的脏数据刷入磁盘文件，完成最终修改

各部分作用简介

# 1、缓冲池 buffer pool
1）会把一些磁盘上的数据加载到该内存当中
2）查询数据的时候不从磁盘查，从该内存里查
 
# 2、undo log
1）逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录
2）用于数据回滚
3）实现mvcc
 
# 3、redo log
1）存储引擎层日志
2）物理日志（类似于“对哪个数据页中的什么记录，做了个什么修改”）
3）记录对数据做了什么修改，防止已提交事务的数据丢失。因为数据不是实时刷盘的，数据是在buffer pool当中，如果数据库宕机了并且buffer pool中的数据还没有刷盘，修改过的数据就丢失了，redo log解决这一问题
4）redo log buffer是redo log的缓冲区，数据做了什么修改，首先会写入到redo log buffer中，再刷盘写入redo log中
 
# 4、binlog
归档日志，属于mysql server层，不属于存储引擎层
逻辑性日志（类似于“对users表中的id=10的一行数据做了更新操作，更新以后的值是什么”）

				

					
				
				

					
				
				

					
				
				

					
				
			

问题1：事务还没有提交，mysql宕机了怎么办？

答案：
事务没有提交，mysql宕机，buffer pool和redo log buffer中的数据都会丢失，数据库返回异常，提示事务失败
磁盘上的数据没有任何变化，不影响

				

					
				
				

					
				
				

					
				
				

					
				
			

问题2：事务提交了，mysql突然宕机了怎么办？

答案：
事务如果提交了，但是提交失败，那么对磁盘数据没有任何影响
事务如果提交了，但是提交成功了，如果mysql突然挂掉，buffer pool和redo log buffer中的数据都会丢失，但事务提交成功意味着已经写入了redo log file，此时即便buffer中的数据丢失，依然可以凭借redo log file恢复数据

				

					
				
				

					
				
				

					
				
				

					
				
			

6.5 redo log刷盘策略

• 当提交事务的时候，redo log buffer里的数据会根据一定规则刷到磁盘上
• 通过innodb_flush_log_at_trx_commit参数来配置
  • 0 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失
  • 1 （默认值，建议）提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了
  • 2 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。此时mysql宕机，数据不会丢失；如果机器宕机，数据会丢失

6.6 binlog刷盘策略

• 当提交事务的时候，binlog也会刷到磁盘上去
• 通过sync_binlog参数来配置
  • 0 默认值。事务提交后，将二进制日志写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志
  • 1 （推荐）事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存

6.7 commit标记的意义

commit写入redo log，才能判定事务成功；因为此时，redo log中有这次更新记录，binlog也有这次更新记录，redo log和binlog保持了一致，否则

• 1、redo log刷盘成功，binlog还没刷盘

  数据库宕机，没有commit标记写到redo log中，事务判定为失败。

  因为redolog中有这次更新日志，binlog中没有这次更新日志，会出现数据不一致问题

• 2、redo log刷盘成功，binlog刷盘成功

  commit标记还没来得及写入redo log中，数据库宕机，同样判定事务提交失败

6.8 内存(buffer pool)中更新过脏数据什么时候刷盘

• 后台io线程有时间会把内存buffer pool中更新过的脏数据(因为更新过，和磁盘上的数据不一样，所以叫脏数据)刷回到磁盘上，哪怕这时候mysql宕机，也没有关系，可通过redo log和binlog恢复数据到内存中，io线程有时间再把数据刷盘，那何时刷呢？

  - 1、redo log满的情况下才会主动刷入磁盘
  - 2、系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
  - 3、MySQL 认为空闲的时间，这种情况没有性能问题；
  - 4、MySQL 正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

6.9 LRU(Least Recently Used) 淘汰策略

以上我们了解了 InnoDB 的更新和插入操作的具体实现原理，接下来我们再来了解下它的实现和优化方式。

InnoDB 存储引擎是基于集合索引实现的数据存储，也就是除了索引列以及主键是存储在 B + 树之外，其它列数据也存储在 B + 树的叶子节点中。而这里的索引页和数据页都会缓存在缓冲池中，在查询数据时，只要在缓冲池中存在该数据，InnoDB 就不用每次都去磁盘中读取页，从而提高数据库的查询性能。

虽然缓冲池是一个很大的内存区域，但由于存放了各种类型的数据，加上存储数据量之大，缓冲池无法将所有的数据都存储在其中。因此，缓冲池需要通过 LRU 算法将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 列表的首部，而 InnoDB 则是将数据放在一个 midpoint 位置，通常这个 midpoint 为列表长度的 5/8。

这种策略主要是为了避免一些不常查询的操作突然将热点数据淘汰出去，而热点数据被再次查询时，需要再次从磁盘中获取，从而影响数据库的查询性能。

如果我们的热点数据比较多，我们可以通过调整 midpoint 值来增加热点数据的存储量，从而降低热点数据的淘汰率。

七 innodb存储引擎表空间详解

7.1 段、区、页、行

之前我们提过msyql中的库、表、记录行与我们自己操作的文件夹、文件、文件行的对应关系

库 -------------> 文件夹
表 -------------> 文件
row（记录行） ---> 文件中的一行内容

				

					
				
				

					
				
				

					
				
				

					
				
			

当时我们为了方便理解，对于数据库中的一张表

# 库：db1
use db1;
 
# 表：t1
create table t1(id int,name varchar(16),age int);
 
# 记录行
insert t1 values
(1,"egon",18),
(2,"tom",19),
(3,"jack",20);
 
mysql> select * from t1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | egon |   18 |
|    2 | tom  |   19 |
|    3 | jack |   20 |
+------+------+------+
3 rows in set (0.00 sec)

				

					
				
				

					
				
				

					
				
				

					
				
			

我们可以理解成，在db1文件夹下有一个文本文件，文件中有三行内容

事实上，mysql的存储引擎中关于表中数据的存储结构要复杂的多

InnoDB存储引擎的逻辑存储结构和 Oracle大致相同 ，所有数据都被逻辑地存放在一个空间中 ，我们称之为表空间 （ tablespace ） ，表空间又由：段 （ segment ） 、区 （ extent ） 、页 ( page ） 组成 。页在一些文档中有时也称为块（block)或磁盘块，一次io操作的是一个磁盘的数据，即一页数据。

InnoDB存储引擎的逻辑存储结构大致如下图所示

详解如下

• Row行

  一个Row存放的是一行内容，有trx id，回滚指针，该行包含的n列内容
  InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放是按行进行存放的。
  这里提到面向行（row-oriented）的数据库，那么也就是说，还存在有面向列（column-orientied）的数据库。MySQL infobright储存引擎就是按列来存放数据的，这对于数据仓库下的分析类SQL语句的执行以及数据压缩很有好处。类似的数据库还有Sybase IQ、Google Big Table。面向列的数据库是当前数据库发展的一个方向。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• Page页：最多包含7992行记录

  多个Row组织到一个Page页中，一个Page页即一个磁盘块大小，是io操作的最小物理存储单元，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
  每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2～200行的记录，即7992行记录。
  InnoDB存储引擎page页的大小为16KB，且不可以更改（也许通过更改源码可以）。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• Extent区：由64个连续的页组成的

  区是由64个连续的页组成的，每个页大小为16KB，即每个区的大小为1MB。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• Segment 段 ：最多由4个区组成

  对于大的数据段，InnoDB存储引擎最多每次可以申请4个区，以此来保证数据的顺序性能。
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

• Tablespace 表空间

  表空间由三种段构成
  1、叶子节点数据段：即数据段
  2、非叶子节点数据段：即索引段
  3、回滚段
  
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  				
  
  					
  				
  			

总结：

7992行—>一页（16kB）

64个页—>一个区（1MB）

4个区—> 一个数据段（4M）

叶子节点数据段+非叶子节点数据段+回滚数据段-》一个表空间

7.2 表空间tablespace

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。表空间的管理模式的出现是为了数据库的存储更容易扩展，关于表空间我们还需要详细说一下

• mysql 5.5版本以后出现共享表空间概念
• mysql5.6版本中默认的是独立表空间
• mysql5.7版本新特性共享临时表空间

7.2.1 共享表空间

1）概念

类似于LVM逻辑卷，是动态扩展的
默认只有12M，会根据数据的量慢慢变越来越大
 
优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。
 
缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

				

					
				
				

					
				
				

					
				
				

					
				
			

2）查看共享表空间

mysql> show variables like '%path%';
+----------------------------------+------------------------+
| Variable_name                    | Value                  |
+----------------------------------+------------------------+
| innodb_data_file_path            | ibdata1:12M:autoextend |
| sha256_password_private_key_path | private_key.pem        |
| sha256_password_public_key_path  | public_key.pem         |
| ssl_capath                       |                        |
| ssl_crlpath                      |                        |
+----------------------------------+------------------------+
5 rows in set (0.01 sec)

				

					
				
				

					
				
				

					
				
				

					
				
			

3）修改共享表空间