13@mysql数据库存储引擎详述

mysql存储引擎

一 MySQL逻辑架构

1、Connectors(连接层)

Connectors(连接层): MySQL服务器时一个进程，会等待客户端的连接(大概在150-250个线程), 每当一个连接过来的时候，都会启用线程进行处理. 然后进行：
1.身份认证(authentication)
2.权限认证(authorization).

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

2、SQL层（服务层）

MySQL Server服务层是核心层，提供了MySQL Server数据库系统的所有逻辑功能，该层可以分为如下不同的组件:
1.MySQL Management Server & utilities(系统管理)
2.SQL Interface(SQL 接口)
3.SQL Parser(SQL 解析器)
4.Optimizer (查询优化器)
5.Caches & buffers(缓存)

1）#MySQL Management Server & utilities(系统管理) 
数据库管理功能具体如下:
数据库备份和恢复
数据库安全管理，如用户及权限管理
数据库复制管理
数据库集群管理
数据库分区，分库，分表管理
数据库元数据管理

2）# SQL Interface(SQL 接口)
SQL接口是接收用户的SQL命令并进行处理，得到用户所需要的结果，具体处理功能如下:
Data Manipulation Language (DML).
Data Definition Language (DDL).
存储过程
视图
触发器


3）#SQL Parser(SQL 解析器) 
解析器的作用:
主要是解析查询语句，最终生成语法树。首先解析器会对查询语句进行语法分析，如果语句语法有错误，则返回相应的错误信息。语法检查通过后，解析器会查询缓存，如果缓存中有对应的语句，就直接返回结果不进行接下来的优化执行操作
     #注：读者会疑问，从缓存中查出来的数据会不会被修改，与真实的数据不一致，这里读者大可放心，因为缓存中数据被修改，会被清出缓存


4）#Optimizer(查询优化器) 
优化器的作用:
主要是对查询语句进行优化，包括选择合适的索引，数据的读取方式

5）#Caches & buffers(缓存) 
缓存的作用:
包括全局和引擎特定的缓存，提高查询的效率,如果查询缓存中有命中的查询结果，则查询语句就可以从缓存中取数据，无须再通过解析和执行。这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等

【架构概述】

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

3、存储引擎层

存储引擎是MySQL中具体与文件打交道的子系统，也是MySQL最有特色的地方,数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作,MySQL区别于其他数据库的最重要特点是其插件式的表存储引擎。他根据MySQL AB公司提供的文件访问层抽象接口来定制一种文件访问的机制（该机制叫存储引擎)

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

【什么是存储引擎】

【存储引擎概述详解】

#抽象对比：
mysql中建立的库 ===>  文件夹
库中建立的表    ===>  文件

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

 

二 、存储引擎信息

1、查看存储引擎

#查看数据库所有支持的存储引擎：
MariaDB [(none)]> show engines\G     #显示详细信息
MariaDB [(none)]> show engines;      #列表显示信息
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

    #注:  Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎

【查看正在使用的存储引擎】

#方式一:
mysql> show variables like 'storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)


#方式二：
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

【查看存储引擎innodb的表有哪些】（通过查表information_schema.tables来获取）

#table_schema字段的值即表所在的库
#table_name表述库所在的表
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
+--------------+----------------------+--------+
| table_schema | table_name           | engine |
+--------------+----------------------+--------+
| db1          | author               | InnoDB |
| db1          | author2book          | InnoDB |
| db1          | book                 | InnoDB |
| db1          | employee             | InnoDB |
| hzl          | class                | InnoDB |
| hzl          | course               | InnoDB |
| hzl          | department           | InnoDB |
| hzl          | employee             | InnoDB |
| hzl          | score                | InnoDB |
| hzl          | student              | InnoDB |
| hzl          | teacher              | InnoDB |
| mysql        | innodb_index_stats   | InnoDB |
| mysql        | innodb_table_stats   | InnoDB |
| mysql        | slave_master_info    | InnoDB |
| mysql        | slave_relay_log_info | InnoDB |
| mysql        | slave_worker_info    | InnoDB |
+--------------+----------------------+--------+
16 rows in set (0.00 sec)

【查看存储引擎myisam的表有哪些】（通过查表information_schema.tables来获取）

#table_schema字段的值即表所在的库
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------------+---------------------------+--------+
| table_schema       | table_name                | engine |
+--------------------+---------------------------+--------+
| information_schema | COLUMNS                   | MyISAM |
| information_schema | EVENTS                    | MyISAM |
| information_schema | OPTIMIZER_TRACE           | MyISAM |
| information_schema | PARAMETERS                | MyISAM |
| information_schema | PARTITIONS                | MyISAM |
| information_schema | PLUGINS                   | MyISAM |
| information_schema | PROCESSLIST               | MyISAM |
| information_schema | ROUTINES                  | MyISAM |
| information_schema | TRIGGERS                  | MyISAM |
| information_schema | VIEWS                     | MyISAM |
| mysql              | columns_priv              | MyISAM |
| mysql              | db                        | MyISAM |
| mysql              | event                     | MyISAM |
| mysql              | func                      | MyISAM |
| mysql              | help_category             | MyISAM |
| mysql              | help_keyword              | MyISAM |
| mysql              | help_relation             | MyISAM |
| mysql              | help_topic                | MyISAM |
| mysql              | ndb_binlog_index          | MyISAM |
| mysql              | plugin                    | MyISAM |
| mysql              | proc                      | MyISAM |
| mysql              | procs_priv                | MyISAM |
| mysql              | proxies_priv              | MyISAM |
| mysql              | servers                   | MyISAM |
| mysql              | tables_priv               | MyISAM |
| mysql              | time_zone                 | MyISAM |
| mysql              | time_zone_leap_second     | MyISAM |
| mysql              | time_zone_name            | MyISAM |
| mysql              | time_zone_transition      | MyISAM |
| mysql              | time_zone_transition_type | MyISAM |
| mysql              | user                      | MyISAM |
+--------------------+---------------------------+--------+
31 rows in set (0.01 sec)

【查看表的存储引擎】

#只查看表的存储引擎
#方式一：
mysql> show create table db1.book\G

*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)




#方式二：
mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'book' AND TABLE_SCHEMA = 'db1'\G
*************************** 1. row ***************************
TABLE_NAME: book
    ENGINE: InnoDB
1 row in set (0.00 sec)


mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'book' AND TABLE_SCHEMA = 'db1';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| book       | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

2、MySQL 默认存储引擎

InnoDB 是系统的默认引擎，支持可靠的事务处理

【默认存储引擎】

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

1）事务（Transaction）   #一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成	，一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
2）行级锁               # innodb支持行级锁，myisam是表级锁，锁的粒度越小并发能力越强
3）外键                #即外键约束，保持数据一致性，完整性，主要目的是控制存储在外键表中的数据， 使两张表形成关联，外键只能引用外表中列的值！
4）MVCC               #多版本并发控制
5）备份和恢复          #innodb支持支持热备，myisam不支持
6）自动故障恢复 （CSR） Crash Safe Recovery

【修改默认存储引擎】

#存储引擎格式:
set default_storage_engine=< 存储引擎名 >


#临时修改默认存储引擎：(重新登录时失效）
mysql> set default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

         #注： Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎

【MySQL 存储引擎的选择 】

#根据以下的原则来选择适用MySQL 存储引擎：

1）如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制 #（InnoDB 是一个很好的选择）

2）如果数据表主要用来插入和查询记录    #（ MyISAM 引擎提供较高的处理效率）

3）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，选择将数据保存在内存的 #（MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果）

4）如果只有 INSERT 和 SELECT 操作   #（可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎）

      #提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求，使用合适的存储引擎将会提高整个数据库的性能。

3、mysql存储引擎的详述（了解）

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
顾名思义：归档(是适用于存放大量数据的存储引擎)； 支持select、insert等操作; 不支持delete 、update、索引等操作； 使用zlib无损数据压缩算法，节省空间； 适用于大量历史数据(可查询但不能删除)的保存


09）#FEDERATED
使用FEDERATED存储引擎，您可以从远程MySQL数据库访问数据，而无需使用复制或群集技术。查询本地FEDERATED表会自动从远程（联合）表中提取数据，没有数据存储在本地表上



10）#EXAMPLE
11）#MERGE
12）#NDBCLUSTER
13）#CSV





#还可以使用第三方存储引擎:（了解即可）
01）MySQL当中插件式的存储引擎类型
02）MySQL的两个分支
03）perconaDB
04）mariaDB

三、修改存储引擎

1、修改存储引擎（配置文件指定）

#配置文件指定存储引擎
vim /etc/my.cnf

[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1

2、修改存储引擎（命令行临时设定）

#在MySQL命令行中临时设置
mysql> set @@storage_engine=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)



#查看存储引擎
#方式一：
mysql> select  @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)


#方式二：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

3、 修改存储引擎（创建表时指定）

#创建并指定存储引擎
mysql> CREATE TABLE hzl01(id INT) ENGINE=myisam;
Query OK, 0 rows affected (0.00 sec)



#查看表的存储引擎
mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'hzl01' AND TABLE_SCHEMA = 'hzl';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| hzl01      | MyISAM |
+------------+--------+
1 row in set (0.00 sec)

四、存储引擎实验

1）创建一个库（测试使用）
2）创建四个表（innodb、myisam、memory、blackhole）
3）分别使用innodb，myisam，memory，blackhole存储引擎
4）分别插入数据测试存储引擎的特性

#创建一个测试库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1;
Database changed




#创建四个表（使用不同的存储引擎）
mysql> create table t1(id int)engine=innodb;    #使用innodb存储引擎
Query OK, 0 rows affected (0.01 sec) 

mysql> create table t2(id int)engine=myisam;    #使用myisam存储引擎
Query OK, 0 rows affected (0.00 sec)

mysql> create table t3(id int)engine=memory;    #使用memory存储引擎
Query OK, 0 rows affected (0.00 sec)

mysql> create table t4(id int)engine=blackhole; #使用blackhole存储引擎
Query OK, 0 rows affected (0.00 sec)

mysql> \q                                       #创建完成后退出数据库
Bye




#查看表数据存储结构
[root@hzl ~]# cd /var/lib/mysql/data/db1/
[root@hzl db1]# ls           #发现后两种存储引擎只有表结构，无数据
db.opt  t1.frm  t1.ibd  t2.frm  t2.MYD  t2.MYI  t3.frm  t4.frm
[root@hzl db1]# ll
总用量 152
-rw-rw---- 1 mysql mysql    67 7月   3 05:12 db.opt
-rw-rw---- 1 mysql mysql  8556 7月  11 08:48 t1.frm   #表结构文件
-rw-rw---- 1 mysql mysql 98304 7月  11 08:48 t1.ibd   #独占表空间文件
-rw-rw---- 1 mysql mysql  8556 7月  11 08:48 t2.frm
-rw-rw---- 1 mysql mysql     0 7月  11 08:48 t2.MYD   #.myd 即my data，表数据文件
-rw-rw---- 1 mysql mysql  1024 7月  11 08:48 t2.MYI   #.myi 即my index，表索引文件
-rw-rw---- 1 mysql mysql  8556 7月  11 08:48 t3.frm  
-rw-rw---- 1 mysql mysql  8556 7月  11 08:49 t4.frm


ps：#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

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

使用源码包安装，二进制安装

2）数据库备份

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

#数据库备份
mysqldump -uroot -p123 -B db1 --triggers -R --master-data=2 >/tmp/db1.sql   

注：
  # –triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
  # -R:–routines,导出存储过程以及自定义函数

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的组成】

#InnoDB的逻辑架构主要分为三个大的组成部分：

1)在内存中的架构（In-Memory Structures）

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

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

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)了。

2、 操作系统缓存

储备知识：

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

【操作系统缓存概述】

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

【O_DIRECT】（直接写入磁盘）

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

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）

【mysql索引详述】

索引存在的目的主要是为了加速数据的读取速度，InnoDB采用BTree（实际为优化改进后的B+树索引）

#按键的类别划分：
主键索引和二级索引/辅助索引；


#按索引的类型分：
BTree索引和自适应哈希索引；


#按存储结构划分：
聚集索引和非聚集索引。



ps： 主键索引也是聚集索引，二级索引都是非聚集索引
     自适应哈希索引是InnoDB为了加速查询性能，它自己按需在内存中对加载进内存的BTree索引优化为哈希索引的一种手段

4）双写缓冲（Doublewrite Buffer）；

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

5）Redo日志：

记录的是尚未完成的操作，断电则用其重做

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

7）Undo段：

记录的改动之前的旧数据，一旦改错，可以回滚

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

【redo log+undo log】

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. 事务提交

4、innodb存储引擎（执行流程）

【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中，事务才算提交成功；否则不会成功
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恢复数据

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、 刷盘策略

【binlog刷盘策略】

当提交事务的时候，binlog也会刷到磁盘上去
通过sync_binlog参数来配置

  - 0 默认值。事务提交后，将二进制日志写入了操作系统缓冲，若操作系统宕机则会丢失部分二进制日志
 
  - 1 （推荐）事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存

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中，数据库宕机，同样判定事务提交失败

8、 buffer pool (内存池脏数据刷盘)

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

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

- 1、redo log满的情况下才会主动刷入磁盘

- 2、系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；

- 3、MySQL 认为空闲的时间，这种情况没有性能问题；

- 4、MySQL 正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题。

9、LRU（淘汰策略）

LRU(Least Recently Used) 最近最少使用

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

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

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

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

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

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

七 、innodb存储引擎（表空间）

1、 段、区、页、行

msyql中的库、表、记录行与我们自己操作的文件夹、文件、文件行是相对应的关系：
库 -------------> 文件夹
表 -------------> 文件
row（记录行） —> 文件中的一行内容

#数据库中的一张表
1）#库：db1
use db1;

2）#表：t1
create table t1(id int,name varchar(16),age int);

3）#记录行row
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文件夹下有一个文本文件t1，文件中有三行内容

#mysql的存储引擎中关于表中数据的存储结构

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

【InnoDB存储引擎的逻辑存储结构】

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

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




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



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



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



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

#InnoDB存储引擎 存储总结：

7992行--->一页（16kB）

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

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

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

2、tablespace（表空间）

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

- mysql 5.5版本以后出现   （共享表空间）

- mysql 5.6版本中默认的是 （独立表空间）

- mysql 5.7版本新特性共享 （临时表空间）

【共享表空间】

#共享表空间概述
类似于LVM逻辑卷，是动态扩展的
默认只有12M，会根据数据的量慢慢变越来越大


#优点：
  可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。

#缺点：
  所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

1）查看（共享表空间）

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)

2）修改（共享表空间）

1>#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# innodb_data_home_dir = /var/lib/mysql     
#开启独享表空间，并指定ibdata1大小为1G，ibdata2大小200M，自动扩张
innodb_data_file_path = ibdata1:1G;ibdata2:200M:autoextend

#配置一个就够用了，为什么要配置两个呢？
  第一个共享表空间数据达到50M以后，他会往第二个表空间里面写数据，当第二个共享表空间数据也达到50M以后会动态扩容，这个文件会越来越大，就像日志一样；
  这样就会导致一个问题，当越来越多的数据增加的时候，ibdata也会持续膨胀，有的达到几十G，上百G
  那么，当前存储数据的磁盘分区满的时候，要怎么样去扩展数据空间呢？
	



2>#.修改完配置文件重启
[root@db01 ~]# systemctl start mysql                #启动会报错或者启动不了
#查看日志
[root@db01 ~]# less /usr/local/mysql/data/db03.err   #分屏显示日志内容
2021-07-21 22:26:00 50917 [ERROR] InnoDB: Data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in t
he .cnf file 3200 pages!






3>#报错说明共享表空间大小与当前已经存在的表空间不一致，我们要把共享表空间修改为当前共享表空间的大小才行
[root@db01 ~]# ll -h /var/lib/mysql/ibdata1 
-rw-rw---- 1 mysql mysql 76M Jul  8 16:57 /var/lib/mysql/ibdata1
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:76M;ibdata2:200M:autoextend

【独立表空间】

对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理

1）概述（独立表空间）

#优点：
- 1.每个表都有自已独立的表空间，易于区分与管理
- 2.每个表的数据和索引都会存在自已的表空间中。
- 3.可以实现单表在不同的数据库中移动。
- 4.空间可以回收（除drop table操作外，表空不能自已回收）
  - 1> Drop table操作自动回收表空间
  - 2> 如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
  - 3> 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
  - 4> 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。



#缺点：
- 1、单表增加过大，如超过100个G。

2）查看（独立表空间）

#物理查看
[root@db01 ~]# ll /application/mysql/data/world/
-rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd






#命令行查看
mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

八、回滚日志的物理空间

innodb存储引擎支持事务
一个事务在执行时会产生大量回滚日志，即 undo log

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



    ps：#也就是说回滚日志也是要存放于表空间中的，大家都是混在一起的，这会造成什么问题呢

1、undo log（回滚表空间）

1）undo log （mysql 5.5共享表空间）

MySQL 5.5时代的undo log(共享的undo表空间)

#共享表空间
 在MySQL5.5以及之前，随着数据库上线时间越来越长，ibdata1文件（即InnoDB的共享表空间，或者系统表空间）会越来越大，这会造成2个比较明显的问题： 
   （1）磁盘剩余空间越来越小，到后期往往要加磁盘； 
   （2）物理备份时间越来越长，备份文件也越来越大。




#问题所在： 
   原因除了数据量自然增长之外
   在MySQL5.5以及之前，InnoDB的撤销记录undo log也是存放在ibdata1里面的，一旦出现大事务，这个大事务所使用的undo log占用的空间就会一直在ibdata1里面存在，即使这个事务已经关闭

   那么问题来了，有办法把上面说的空闲的undo log占用的空间从ibdata1里面清理掉吗？
   答案是没有直接的办法，只能全库导出sql文件，然后重新初始化mysql实例，再全库导入

2）undo log （mysql 5.6独立表空间）

MySQL 5.6时代的undo log(独立的undo表空间)

#独立表空间：
  MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下；采用独立undo表空间，再也不用担心undo会把 ibdata1 文件搞大；也给我们部署不同IO类型的文件位置带来便利，对于并发写入型负载，我们可以把undo文件部署到单独的高速存储设备上





#如何在独立出undo log的表空间呢？
  MySQL 5.6在数据库初始化的时候使用如下三个参数就可以把undo log从ibdata1移出来单独存放
   （1） innodb_undo_directory：
        指定单独存放undo表空间的目录，默认为.（即datadir），可以设置相对路径或者绝对路径。如果需要将undo log放到更快的设备上时，可以设置innodb_undo_directory参数，但是一般我们不这么做，因为现在SSD非常普及，该参数实例初始化之后虽然不可直接改动，但是可以通过先停库，修改配置文件，然后移动undo表空间文件的方式去修改该参数；
   （2） innodb_undo_tablespaces：
        指定单独存放的undo表空间个数，例如如果设置为3，即可将undo log设置到单独的undo表空间中，undo表空间为undo001、undo002、undo003，每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3，原因下文将解释。该参数实例初始化之后不可改动；
   （3） innodb_undo_logs：
        指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认128个，使用默认值即可。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。




#mysql5.6中undo log单独拆出来后就能缩小了吗？:答案是不能？
    mysql5.6中确实可以把undo log回滚日志分离到一个单独的表空间里，这只解决了不把ibdata1搞大的问题，至于撤销记录依然存在，空间是不能被回收（收缩）的,直到MySQL5.7 ，才支持在线收缩。

3）undo log （mysql 5.7共享临时表空间）

MySQL 5.7时代的undo log(共享临时表空间)

#共享临时表空间
 MySQL 5.7引入了新的参数，innodb_undo_log_truncate，开启后可在线收缩拆分出来的undo表空间,在满足以下2个条件下，undo表空间文件可在线收缩：
  （1） innodb_undo_tablespaces >= 2
        因为truncate undo表空间时，该文件处于inactive状态，如果只有1个undo表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响，建议将该参数最少设置为3；

  （2） innodb_undo_logs >= 35（默认128）
        因为在MySQL 5.7中，第一个undo log永远在系统表空间中，另外32个undo log分配给了临时表空间，即ibtmp1，至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log；
        





#共享临时表空间的设定
 满足以上2个条件后，把 innodb_undo_log_truncate设置为ON即可开启undo表空间的自动truncate，这还跟如下2个参数有关：
  （1） innodb_max_undo_log_size
        undo表空间文件超过此值即标记为可收缩，默认1G，可在线修改；

  （2） innodb_purge_rseg_truncate_frequency
        指定purge操作被唤起多少次之后才释放rollback segments，当undo表空间里面的rollback segments被释放时，undo表空间才会被truncate，由此可见，该参数越小，undo表空间被尝试truncate的频率越高

2、 表空间管理（mysql 5.6）

1） 分离undo log表空间

可以把Undo Log从共享表空间里ibdata1拆分出去
注意：需要在安装mysql时，在my.cnf里指定，否则等创建数据库以后再指定，就会报错

#查看undo log
mysql> show variables like '%undo%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_directory   | .     |
| innodb_undo_logs        | 128   |
| innodb_undo_tablespaces | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)





#对于已安装数据库不能修改 innodb_undo_tablespaces 
mysql> set global innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable
mysql> 

我们创建新的空数据目录，重启mysql，重新初始化库，就可以把undo log从共享表空间分离出去了

# 1、创建新的数据目录并设置权限
mkdir /var/lib/mysql1
chown -R mysql.mysql /var/lib/mysql1




# 2、修改配置文件，指向新的数目录/var/lib/mysql1
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql1
socket=/var/lib/mysql1/mysql.sock
# 共享表空间
innodb_data_file_path=ibdata1:76M;ibdata2:12M:autoextend
# 分离
innodb_undo_logs = 128
innodb_undo_tablespaces = 4

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




# 3、重启mysql
systemctl restart mysql

便可以看到
[root@localhost ~]# ll /var/lib/mysql1/
-rw-rw---- 1 mysql mysql 10485760 Jul  8 18:16 undo001
-rw-rw---- 1 mysql mysql 10485760 Jul  8 18:16 undo002
-rw-rw---- 1 mysql mysql 10485760 Jul  8 18:16 undo003
-rw-rw---- 1 mysql mysql 10485760 Jul  8 18:16 undo004


     #注： undo log创建好后，就不能再次修改，或者增加。

2）表空间的迁移拷贝（.ibd）

在线不停机把.ibd数据拷贝到另外一台机器上

#表空间的迁移
  从mysql5.6版本开始，引入了表空间传输的功能
  可以把一张表从一个数据库移动到另一个数据库中或者另一台机器上
  使用该功能必须满足如下条件：
    1》 Mysql版本必须是5.6及以上
    2》使用独立表空间方式，现在版本默认开启innodb_file_per_table
    3》源库和目标库的page size必须一致，表结构必须一致
    4》如果要做表的导出操作，该表只能进行只读操作

#假设
源主机:    主机A
目标主机:  主机B



# 步骤1：在主机A操作
mysql> select version();  #查看版本
+-----------+
| version() |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)

create database hzl;    -- #创建新库与新表，并插入数据
use hzl;
create table t1(id int);
insert t1 values(1),(2),(3);
  首先为t1表加读锁（只能读，不能写，目的是保证数据一致性）
  然后把数据从内存导出到磁盘上。
FLUSH TABLES t1 FOR EXPORT;    #对t1进行锁表操作，让t1表只能读且无法写入数据




# 步骤2：在主机B操作
mysql> select version();  #查看版本
+-----------+
| version() |
+-----------+
| 5.7.34    |
+-----------+
1 row in set (0.00 sec)

create database hzl;    -- #创建新库与新表，并插入数据
use hzl;
create table t2(id int)row_format=Compact;   #创建时此参数指定相同结构
主机B上，创建与原表一样的表结构
create table t2(字段1 类型,字段2 类型,...);

在主机B上关闭t2表的数据空间，删除.ibd文件
alter table t2 discard tablespace;   -- #使用此命令删除t2的.ibd文件




# 步骤3：在主机A上
将主机A上原表t1.ibd拷贝到主机B的数据目录下： scp 
注意拷贝的ibd文件的属主属组与权限问题。
拷贝完后主机A执行  UNLOCK TABLES;


# 步骤4：在主机B上
chown mysql.mysql t2.ibd    #权限更改
执行alter table t2 import tablespace;;  就会进行恢复操作。
然后check table t2;
select * from t2;你会发现数据恢复了。

行格式：

【示例】

基于上述原理完成物理备份与恢复

# 1、安装mysql5.6+版
[root@localhost ~]# cat /etc/yum.repos.d/mysql.repo 
[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
[root@localhost ~]# yum install mariadb-server* -y
[root@localhost ~]# yum install mariadb-* -y
[root@localhost ~]# systemctl start mysql
[root@localhost ~]# mysql -uroot
mysql> select version();       #查看版本
+-----------+
| version() |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)




# 2、设置独立表空间
vim /etc/my.cnf      #配置文件设置独立表空间
[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1



# 3、准备测试数据
create database hzl;
use hzl;
create table t1(id int);
insert t1 values(1),(2),(3);





# 4、将/var/lib/mysql/目录下的hzl库打包，并删除目录hzl
cd /var/lib/mysql/
tar -czvf hzl.tar.gz hzl/
rm -rf hzl/      #打包压缩完成后，对源文件进行删除





# 5、本机测试也行，重启mysql测试，或者把数据拷贝到另外一台数据库服务器进行解压测试
[root@db01 ~]# systemctl restart mysql
[root@db01 ~]# mysql -uroot -p
mysql> create database db1;            #创建新库
mysql> create table test(id int);   -- #表结构与源应一致
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test discard tablespace;   -- #删除test.ibd文件独立表空间文件
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

[root@db01 ~]# tar xvf hzl.tar.gz 
hzl/
hzl/db.opt
hzl/t1.frm
hzl/t1.ibd
[root@dn01 ~]# cp -a hzl/t1.ibd /var/lib/mysql/db1/test.ibd
[root@db01 ~]# 

[root@localhost ~]# mysql -uroot -p
mysql> use db1;
mysql> alter table test import tablespace;
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> select * from test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

3）更改独立表空间

把.ibd文件创建到其他文件夹下

在之前的版本，采用独立表空间(.ibd)存放数据时，是不能更改路径的，比如磁盘满了，恰巧没做LVM卷组，那么通过下述指令

#命令更改 就把创建t1表的.ibd放到了/data2/目录下
CREATE TABLE t1(id int primary key)engine=innodb DATA DIRECTORY="/hzl_data/"，

3、 表空间管理（ MySQL5.7）

1）回滚表空间log（MySQL 5.7）

MySQL 5.7的undo表空间的truncate

#（1）安装mysql5.7版
[root@db02 ~]# cat /etc/yum.repos.d/mysql.repo 
[mysql56-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
[root@localhost ~]# yum install mysql-* mysql-server* -y
[root@localhost ~]# rm -rf  /var/lib/mysql/*    #删除mysql所有的数据文件




#（2） 首先确保如下参数被正确设置：
# 为了实验方便，我们减小该值 
innodb_max_undo_log_size = 11M 
innodb_undo_log_truncate = ON 
innodb_undo_logs = 128 
innodb_undo_tablespaces = 3 
#为了实验方便，我们增大该值 （设置此参数表示回收undo表空间）
innodb_purge_rseg_truncate_frequency = 1000     #设置回收频率



#（3）启动mysqld，并设置密码
[root@db02 ~]# systemctl start mysqld
[root@db02 ~]# grep "temporary password" /var/log/mysqld.log # 过滤出随机密码
[root@db02 ~]# mysql -uroot -p'随机密码'
mysql> set password=password("Hzl@123");   -- 设定弱密码会报错




#（4） 创建表： 
[root@db02 ~]# mysql -uroot -p'Hzl@123'
mysql> create database db1;
mysql> use db1;
mysql> create table t1(id int primary key auto_increment, name varchar(200)); 
Query OK, 0 rows affected (0.13 sec)






#（4）插入测试数据 
mysql>  insert into t1(name) values(repeat('e',200)); 
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1(name) select name from t1; 
mysql> insert into t1(name) select name from t1; 
mysql> insert into t1(name) select name from t1; 
mysql> insert into t1(name) select name from t1; 

#一直插入数据，直到undo log超过11M
-- 执行n次insert into t1(name) select name from t1; 直到undo日志增大超过11M





#（5）查看undo表空间文件大小如下，可以看到有一个undo文件已经超过了11M：

-rw-r----- 1 mysql mysql  36M Jul  8 20:11 undo001
-rw-r----- 1 mysql mysql  10M Jul  8 20:11 undo002
-rw-r----- 1 mysql mysql  11M Jul  8 20:11 undo003


#（6）此时，为了，让purge线程运行，可以运行几个delete语句：
mysql> delete from t1 limit 1; 
mysql> delete from t1 limit 1; 
mysql> delete from t1 limit 1; 
mysql> delete from t1 limit 1;




#（7）过一会之后，再查看undo文件大小，可以看到，超过11M的undo文件已经收缩到10M了
-rw-r----- 1 mysql mysql  10M Jul  8 20:12 undo001
-rw-r----- 1 mysql mysql  10M Jul  8 20:12 undo002
-rw-r----- 1 mysql mysql  11M Jul  8 20:12 undo003

2）测试小练习

对上述表进行一次全表更新，期间观察undo表空间一度增长到800多M，更新结束后，表看空间压缩到10M

mysql> update t1 set name="hzl"; 
Query OK, 4194299 rows affected (2 min 51.00 sec)
Rows matched: 4194299  Changed: 4194299  Warnings: 0