本文共 10775 字，大约阅读时间需要 35 分钟。

MySQLInnoDB表空间管理

概述

MySQL 在整体架构上分为 Server 层和存储引擎层。

其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

单点（Single），适合小规模应用，复制（Replication），适合中小规模应用，集群（Cluster），适合大规模应用。

一InnoDB表空间

从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间。而表空间由段（sengment）、区（extent）、页（page）组成。

mysql :表空间>段>区>页>行

SPACE file > segment > extent > page > row

oracle :表空间>段>区>块>行

1. 1. 表空间类型

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都是存放在表空间中。默认情况下InnoDB存储引擎有一个共享表空间ibdata1，即所有数据都放在这个表空间内。如果我们启用了参数innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

mysql 5.7有6种表空间:

1 system tablespace

2 file-per-table tablespace

3 temporary tablespace

4 undo tablespace

5 general tablespace

6 transportable tablespace  ##传输表空间

 

show variables like 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

1.2 数据表空间

innodb包含两种表空间文件模式: 共享表空间和独立表空间，5.6.6以后默认是独立表空间。

1.2.1默认的系统表空间

1默认的系统表空间，Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

官方默认的;数据量小不影响;数据量大了后文件大小不好控制，性能也不好

show variables like 'innodb_data%';

innodb_data_file_path ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G

表空间有四个文件组成：ibdata1、ibdata2、ibdata3、每个文件的大小为200M，当每个文件都满了的时候，自动扩展，最大为5G。

 

show variables like '%_autoextend%';

| innodb_autoextend_increment | 64    |

当系统表空间满的时候，会预先申请64M，

 

1.2.2独立表空间

2每个表分离的独立表空间 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，主要是表结构的定义信息.还有一个.ibd文件，InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table = 1)产生的存放该表的数据和索引的文件。

my.cnf   innodb_file_per_ table=1

alter table table_ .name engine= =innodb把共享表迁移成独立表空间。1000- - 个表1000个文件存储:分散I0，性能有提升，方便维护和迁移。文件太多，也不方便管理和维护。

 

innodb _file _per_table= =1;

alter table table_ name engine=innodb: 表少的情况，把现有的共享表迁移成独立表空间;如果表多，导出再导入即可。|

1.2.3 File-Per-Table的优缺点：

1File-Per-Table的优点：

1.当表删除或者truncate，空间可以被OS回收。

2.Truncate table在单个.ibd文件上执行更快。 3.可以为每个表指定一个特定存储。优化IO，空间管理。CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory 。 4.运行OPTIMEIZE TABLE，压缩或者重建创建表空间。运行OPTIMIZE TABLE InnoDB会创建一个新的ibd文件。当完成时，老的表空间会被新的代替。 5.可以移动单个表，不需要移动整个数据库 6.可以把表复制到另外一个实例 7.innodb_file_per_table启动后才能使用Barracuda文件格式。 8.可以更有效的存储带BLOB，TEXT使用动态行模式的表。 9.使用innodb_file_per_table可以提高recovery的成功率，减少损坏错误发生恢复的时间。 10.可以快速的备份，恢复单个表。 11.innodb_file_per_table 可以从备份中去除一个表 12.innodb_file_per_table在备份和复制表时，容易观察每个表的状态。 13.可以通过文件系统直接观察表的大小。 14.当innodb_flish_method设置为O_DIRECT，通常linux文件系统不允许并发的写入同一个文件。使用innodb_file_per_table就会有性能提升。 15.不启用innodb_file_per_table，数据都会放在系统表空间中，最大64TB，如果使用innodb_file_per_table每个表可以64TB。  

2File-Per-Table的缺点：

1. 表空间中的空间只能被这个表使用
   2.fsync操作必须在每个表上都运行一遍
   3.mysqld必须保持一个打开的文件句柄，表太多会影响性能。
   4.会消耗很多文件描述
   5.innodb_file_per_tablezhiyou 5.6.6或更高版本才能用，有向下兼容问题。
   6.如果很多表都增长，会出现文件碎片问题。导致drop表和表扫描性能下降。
   7.当drop表的时候会扫描buffer pool，如果太大会比较耗时。
   8.innodb_autoextend_increment指定当文件满了之后增长的空间。

二 innodb变更缓存区与双写缓存区

共享表空间除了存储数据，还存储了以下数据。

1)数据字典

2)变更缓冲区

3)双写缓存区

2.1 变更缓冲区

变更缓冲区是一种特殊的数据结构，当这些页面不在缓冲池中时，该高速缓存会将更改缓存到辅助索引页面。缓冲的更改（可能由INSERT，UPDATE或DELETE操作（DML）引起）将在以后通过其他读取操作将页面加载到缓冲池中时合并。

提高辅助索引的性能,默认开启，能提升30%左右的性能。

mysql 5.5 < insert buffer

mysql 5.5 > change buffer ( insert , delete-marking, purge)

show variables like '%change_buffer%';

| Variable_name                 | Value |

| innodb_change_buffer_max_size | 25    |

| innodb_change_buffering       | all

 

show engine innodb status\G;

 

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

merged operations:

 insert 0, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

Hash table size 553193, node heap has 0 buffer(s)

1. hash searches/s, 0 non-hash searches/s

2.2 双写缓存区

show variables like '%doublew%';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| innodb_doublewrite | ON    |

类似于raid 1模式，double write作用是为了保证数据的写入可靠性，避免部分写(partial write) 的情况。

部分写(partial write) :

一个页16K，只写了基中一部分(10K/8K) ，不完整的页是不能通过redo log恢复.

double write是全局的，以段方式存在，每个段2个区，每个区1M组成。

总结:

在脏页刷新到磁盘之前，先有一个地方记录这个脏页的副本:

1)将脏页copy到double write buffer对象中，默认2*1M大小;

2)将double write buffer中 的对象先写入到共享表空间ibdata1中的double write;

3)再根据共享表空间ibdata1中记录的表空间和页号(space id, page number) 再写到用户表空间的bd文件中。

 

恢复机制:

1)如果写到ibdata1中的double write时，机器挂了;原来用户表空间的ibd文件是可用的，下次启动可以通过redo自动恢复。

2)如果写到用户表空间的ibd文件时，机器挂了;再通过原来的共享表空间ibdata1中副本进行恢复。

 2.2.1 partial page write

为啥会有两次写？必要了解partial page write 问题 : 

       InnoDB 的Page Size一般是16KB，其数据校验也是针对这16KB来计算的，将数据写入到磁盘是以Page为单位进行操作的。而计算机硬件和操作系统，写文件是以4KB作为单位的,那么每写一个innodb的page到磁盘上，在os级别上需要写4个块.通过以下命令可以查看文件系统的块大小.

 dumpe2fs /dev/vda1 |grep "Block size"

dumpe2fs 1.41.12 (17-May-2010)

Block size:               4096

     在极端情况下（比如断电）往往并不能保证这一操作的原子性，16K的数据，写入4K 时，发生了系统断电/os crash ，只有一部分写是成功的，这种情况下就是 partial page write 问题。有人会想到系统恢复后MySQL可以根据redolog 进行恢复，而mysql在恢复的过程中是检查page的checksum，checksum就是pgae的最后事务号，发生partial page write 问题时，page已经损坏，找不到该page中的事务号，就无法恢复。

2.2.2 MySQL 8.与5.7位置变化

在MySQL 8.0.20之前，doublewrite缓冲区存储区位于InnoDB系统表空间中。从MySQL 8.0.20开始，doublewrite缓冲区存储区位于doublewrite文件中。

2.2.3性能影响:

2M数据是顺序刷入磁盘，默认性能降低5%~25%。

特殊情况关闭:

不要数据安全，只要速度。

innodb_ doublewrite=0

三系统 ibdata表空间文件扩容与缩小

3.1 自动扩展

自动扩展就是最后一个文件，autoextend:只能应用于最后一个文件。还按照1M的倍数增加。

show variables like 'innodb_data_file_path%';

+-----------------------+----------------------------------------------------------+

| Variable_name         | Value                                                    |

+-----------------------+----------------------------------------------------------+

| innodb_data_file_path | ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:500G |

3.2 增加文件

innodb_data_file_path | ibdata1:200M;ibdata2:200M;ibdata3:200M;ibdata4:200M:autoextend:max:500G

service mysql restart

Shutting down MySQL..                                      [确定]

Starting MySQL..                                           [确定]

 

-rw-r----- 1 mysql mysql 200M 10月 29 19:38 ibdata1

-rw-r----- 1 mysql mysql 200M 9月   2 22:08 ibdata2

-rw-r----- 1 mysql mysql 200M 9月   2 22:08 ibdata3

-rw-r----- 1 mysql mysql 200M 10月 29 19:38 ibdata4

 

select 209715200/1024/1024;

+---------------------+

| 209715200/1024/1024 |

+---------------------+

|        200.00000000 |

+---------------------+

 

3.3 关于文件目录

1 可以通过copy文件到指定的位置，在修改配置文件即可。

2系统默认配置修改

show variables like '%innodb%home%';

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| innodb_data_home_dir      |       |

如果没有指定，就会在my.cnf配置文件中

datadir=/mysql/data/3306/data

3.4 缩小innodb system tablespace(需要做实验)

场景1:原来是共享表空间，转换独立表空间后，数据和索引迁移出bdata,原来的又有500G文件不会自动缩小。

 

 

1)导出所有的数据:

mysqldump --all-databases --flush-privileges --quick --routines --triggers -uroot -p > all-db. Sql

 

2)查询所有的innodb表-mysql

select table_name from information_schema.tables where table_ schema= 'mysql' and engine = 'innodb' ;

 

 

3)停止mysql: service mysql stop

4)拷贝现在所有的目录(空间不足，必须拷mysql数据文件目录)

5)删除所有的表空间文件(* . ibd) ,包括ibdata, ib_ log* , undo* ,mysql目录下面的* . ibd文件，以及mysql 中的innodb表的. frm文件。

find  –name  *.ibd" -exec rm -rf {} \;

6)开启mysql服务

service mysql start

7)数据数据库

mysql -uroot -p < all-db. sql

8)重启测试

 

四 临时表空间

   临时表空间:主要用于临时排序/非压缩的临时表，默认12M的文件

 

mysql 5.7 <临时表都放在ibdata里面

mysql5.7>临时表放在临时表空间

 

show variables like 'innodb_temp_data%';

+----------------------------+--------------------------------+

| Variable_name              | Value                          |

+----------------------------+--------------------------------+

| innodb_temp_data_file_path | ibtmp1:200M:autoextend:max:20G |

 

4.1 临时表

 

临时表类型:

全局临时表(memory引 擎)

会话级临时表( create temporary table)

事务级临时表(mysql目前没有这种临时表)

检索级临时表

 

mysql创建临时表的情况:

1) uni on查询

2) order by

3) group by

4)from子查询，链接，大的结果集

 

 

 

 

Mysql 5.7.5>默认定义磁盘临时表为innodb :

show variables like 'internal_tmp_d%';

+----------------------------------+--------+

| Variable_name                    | Value  |

+----------------------------------+--------+

| internal_tmp_disk_storage_engine | InnoDB |

+----------------------------------+--------+

 

mysql 5.6.3 < create temporary table临 时表为memory引擎:

show variables like 'default_ tmp_storage_engine%';

五 一般表空间general tablespace

mysql 5.7.6 >开始支持general tablespace,类型oracle的表空间管理，创建表空间可以指定数据文件名，但只能是一个数据文件，. ibd为后缀。                                              

General tablespace 是一种共享的 innodb 表空间，有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表，即使这些表来自不同的 schame 。

5.1 General tablespace 的优点

类似系统表空间，可以存储多个 schema 下的多张表。

    与 file_per_table tablespace 相比， General tablespace 有潜在的内存的优点。这种方式存储表空间的元数据到内存中，可以减小内存消耗。

General tablespace 可以将数据文件存在其它盘上。这样做的好处是，比如说我需要将几张热点表放到更快的存储盘上，就可以在更快的存储盘上创建 general tablespace ，将热点表放过去。

 General 支持所有的行格式，以及相关特性。

数据表可以在 general tablespace 和 per_file_table  tablespace 之间来回移动。类似 Oracle 数据库的 move  table 。

  

创建表空间语法

CREATE TABLESPACE tablespace_name

      ADD DATAFILE 'file_name'

      [FILE_BLOCK_SIZE = value]

        [ENGINE [=] engine_name]

 

标准表空间

create tablespace itpuxts1 add datafile '/mysql/data/3306/data/itpuxts101.ibd' engine=innodb;

 

小一点的表空间

create tablespace itpuxts2 add datafile '/mysql/data/3306/data/itpuxts201.ibd' file_block_size=8k;

Query OK, 0 rows affected (0.00 sec)

 

创建一个压缩表

create table itpuxz01(c1 int)  tablespace itpuxts1 row_format=compressed;

ERROR 1478 (HY000): InnoDB: Tablespace `itpuxts1` cannot contain a COMPRESSED table

 

 

create table itpuxz01(c1 int)  tablespace itpuxts2 row_format=compressed;

Query OK, 0 rows affected (0.00 sec)

5.2 表在表空间之间的迁移

create tablespace itpuxts1 add datafile '/mysql/data/3306/data/itpuxts101.ibd' engine=innodb;

 

create table inpuxt01(a int);

Query OK, 0 rows affected (0.01 sec)

 

 

alter table inpuxt01 tablespace=itpuxts1;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

alter table inpuxt01 tablespace=innodb_system;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

 

alter table inpuxt01 tablespace=innodb_file_per_table;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

六undo tablespace

edo log是为了持久化数据，在数据还没从内存刷新到磁盘时，如果发生故障，可读取该日志持久化到磁盘。

binlog 是为了复制和恢复数据的，即Mysql从服务器可以读取主服务器的binlog复制数据，数据库数据丢失，也可以读取binlog恢复

undo log是为了保证原子性的。

 

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证：“一个事务包含多个操作，这些操作要么全部执行，要么全都不执行”

6.1 查询状态

mysql5.7>从系统表空间独立出来的undo回滚表空间

show variables like '%undo%';

 

Connection id:    4

Current database: mysql

+--------------------------+------------+

| Variable_name            | Value      |

+--------------------------+------------+

| innodb_max_undo_log_size | 2147483648 |    ##最大20G

| innodb_undo_directory    | ./         |

| innodb_undo_log_truncate | ON         |    ##开启自动清理

| innodb_undo_logs         | 128        |   ##128回滚段

| innodb_undo_tablespaces  | 3          |    ##三个表空间

 

 

 

show variables like '%truncate%';

| innodb_purge_rseg_truncate_frequency | 128   |   ##

| innodb_undo_log_truncate             | ON    |  ##

 

 

转载地址：http://gxbai.baihongyu.com/