MYSQL 5.7 INNODB 表空间

时间:2024-04-06 22:49:16

背景介绍

由于InnoDB 引擎支持ACID、良好的读写性能,还有许多其他对数据库服务具有重要意义的特性,InnoDB已经成为MySQL最受欢迎的存储引擎。


在本文中,我们将介绍InnoDB表空间和它的一些特性:

1.     InnoDB引擎的表空间

2.     表空间的数据加密

3.     表空间的相关配置

InnoDB引擎表空间

InnoDB系统表空间

系统表空间是MySQL最基础的表空间。除了存储表数据、InnoDB元数据,以及用来支持ACID特性和事务隔离所需的MVCC信息。它还包含以下几种类型的InnoDB对象信息。

包含

表数据页
表索引页
数据字典
MVCC控制数据
Undo空间

回滚段
双写缓冲区
插入缓冲

参数

InnoDB_data_file_path = /ibdata/ibdata1:10M:autoextend


通过启用InnoDB_file_per_table(默认启用)选项,我们可以在单独的表空间文件存储每个表的数据和索引。这种做法能够有效减少数据文件的磁盘碎片

InnoDB数据字典

InnoDB数据字典是一系列位于系统表空间中的内部系统表,存储着表、索引、列等InnoDB对象元数据信息。

双写缓冲

InnoDB在将Buffer Pool里的脏页刷新到磁盘(数据文件)之前,会先将这些脏数据写入到系统表空间中一段连续的区域,我们称之为双写缓冲区(Double write buffer)。如果MySQL在将脏数据刷新到磁盘的过程中发生了crash,InnoDB仍然可以从双写缓冲区中找到一份完整的数据拷贝,这样就能有效防止部分写失效问题(Partial Page Writes)


参数: inndb_doublewrite (默认开启)

Redo logs

Redo logs主要用于崩溃恢复。MySQL启动的时候,InnoDB会自动执行auto recovery,来处理未完成的事务。


在MySQL 意外崩溃之前没有提交的事务,MySQL会尝试重做。在这个过程中是没法建立到MySQL的连接的。


为什么我们在崩溃恢复时需要Redo

举个例子:用户修改了数据会在落盘之前先写入InnoDB Buffer Pool,但是如果这时候MySQL崩溃,缓冲区的数据就会丢失,

因此MySQL需要一个文件来记录所有对page的修改,并在数据库启动的时候从这个文件进行恢复操作。


Redo里,存储了诸如这样的信息: row_id, 旧的列值,新的列值,session_id和时间。


只有在数据文件落盘之后才意味着一次commit真正完成。


参数:

InnoDB_log_file_in_group= [# Redo文件组]

InnoDB_log_buffer_size= [缓冲区大小] (如果事务较大,可以适当增大这个参数的值,但是建议不要超过日志文件大小的10-20%)

InnoDB_log_file_size= [每个Redo日志的大小]  (如果存储了BLOB等类型的数据,建议适当增大这个参数的值)   

UNDO 表空间和日志

Uodo表空间包含一个或多个undo日志文件。


Undo通过维护事务修改数据之前的副本来实现一致性的读MVCC。Undo日志也称为回滚段。


默认情况下,undo 日志存储在系统表空间里。MySQL5.6开始,允许undo日志存储在单独的undo表空间。不过它需要在MySQL启动之前配置好。


当我们配置了单独的undo表空间,系统表空间里的undo 日志就不可用了


我们需要在MySQL启动之前配置它并且在运行过程中不能动态修改。


我们可以清空 undo日志,但是却不能删除它。


默认情况下,一个undo表空间文件的初始大小是10MB


参数:

InnoDB_undo_tablespace : undo表空间的数量, 默认 0 , 最大 95


InnoDB_undo_directory : undo表空间的位置,默认存在数据目录下,初始大小是 10MB


InnoDB_undo_logs : 在每个undo表空间里的undo日志的数量, 默认和最大值都是‘128’ [ 在5.7.19后弃用了 , 由InnoDB_rollback_segments 这个参数来控制]


InnoDB_undo_log_truncate: 清空undo 表空间, 默认关闭  [当开启这个参数时, undo 表空间超过InnoDB_max_undo_log_size定义的阈值后会被标记清除. ]

关键点

清空undo日志操作需要针对独立的undo日志。这意味着在系统表空间的undo是不能被清空的。


InnoDB_undo_tablespaces必须设置为等于或大于2的值。


InnoDB_rollback_segments必须设置为等于或者大于35的值。



 

优势


当我们把长期运行的事务存储到独立的单一或多个undo表空间时,可以有效降低系统表空间的大小。

临时表空间

在MySQL 5.7之前,INNODB引擎的临时表都保存在共享表空间里,MySQL 5.7对临时表相关功能进行了优化,将临时表的数据以及回滚信息(仅限于未压缩表)从共享表空间里面剥离出来,形成自己单独的表空间,来降低临时表的创建与删除对其他正常表产生非常大的性能影响(主要是Redo相关的IO操作)。


临时表的undo日志存放在临时表空间里,不能使用裸设备,暂不支持指定临时表空间路径


默认情况下,临时表空间文件ibtmp1 会在每次服务启动时重新创建。


临时表空间不会用于崩溃恢复。


参数:

InnoDB_temp_data_file_path = ibtmp1:12M:autoextend (默认)

InnoDB通用表空间

通用表空间和系统表空间类似,可以用来存放多张表的数据。 在MySQL 5.7.6中引入。用户必须使用CREATE TABLESPACE语法创建此文件通用表空间。TABLESPACE选项可以在CREATE TABLE的时候指定表存放在通用表空间、独立表空间或者系统表空间中,也可以和ALTER  TABLE命令结合,在通用表空间、独立表空间和系统表空间直接互相移动表,而在之前的版本中,在独立表空间和系统表空间之间相互移动表是不可能的。


相比InnoDB_file_per_table存储方式,内存占用会更省。

支持Antelope 和Barracuda 文件格式。

支持所有行格式和相关功能 

可以在数据目录外创建

表空间数据加密

MySQL 5.7支持通过mysql keyring(一种两层加密架构,由一个master key和多个tablespace key组成;当InnoDB表被加密的时候,其实是对tablespace key加密并存储在表空间文件头里,当访问被加密的InnoDB表的时候,InnoDB使用master key进行解密)对独立表空间中的InnoDB表的数据进行加密,来提升物理文件的安全性。


keyring_file: 所有MySQL版本都会提供的一个插件,用于将keyring数据存储在服务器主机本地的文件中,keyring_file必须在每个服务器启动时通过-early-plugin-load选项加载。

keyring_okv: 后端**仓储产品,如Oracle Key Vault。此插件在MySQL企业发行版中可用。      


参数:

early-plugin-load :确保插件在初始化InnoDB存储引擎之前可用的配置。

keyring_file_data : keyring 文件的路径.

表空间相关配置和操作

Mysql InnoDB 配置

## DATA STORAGE ##

datadir=/var/lib/mysql

## InnoDB Configuration ##

InnoDB_file_per_table=1

# InnoDB Memory

InnoDB_buffer_pool_size = 2000M

# System Tablespace configuration

InnoDB_data_file_path= ibdata1:512M;ibdata2:512M:autoextend

# Redo log and buffer configuration

InnoDB-log-files-in-group=3

InnoDB_log_file_size=100M

InnoDB_log_buffer_size=30M

#InnoDB file formate

InnoDB_file_format = Barracuda

# UNDO Tablespace Configuration

InnoDB_undo_directory =  /var/lib/mysql/

InnoDB_undo_tablespaces = 3

InnoDB_undo_logs = 128

InnoDB_undo_log_truncate = ON

InnoDB_rollback_segments = 128

# Temp Tablespace Configuration

tmpdir = /var/lib/mysql/

InnoDB_temp_data_file_path = ibtmp1:20M:autoextend

# Keyring configuration

early-plugin-load=keyring_file.so

keyring_file_data=/var/lib/mysql-keyring/keyring


mysql 服务初始化日志

[Note] InnoDB: Using Linux native AIO

[Note] InnoDB: Number of pools: 1

[Note] InnoDB: Using CPU crc32 instructions

[Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M

[Note] InnoDB: Completed initialization of buffer pool

[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

[Note] InnoDB: Opened 4 undo tablespaces

[Note] InnoDB: 4 undo tablespaces made active

[Note] InnoDB: Highest supported file format is Barracuda.

[Note] InnoDB: Creating shared tablespace for temporary tables

[Note] InnoDB: Setting file './ibtmp1' size to 20 MB. Physically writing the file full; Please wait ...

[Note] InnoDB: File './ibtmp1' size is now 20 MB.

[Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

[Note] InnoDB: 32 non-redo rollback segment(s) are active.

[Note] InnoDB: Waiting for purge to start

[Note] InnoDB: 5.7.19 started; log sequence number 2454162

[Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

[Note] Plugin 'FEDERATED' is disabled.

[Note] InnoDB: Buffer pool(s) load completed at 170828 12:03:52

UNDO和临时表空间

MYSQL 5.7 INNODB 表空间

General表空间示例

# Create General tablespace

mysql> CREATE TABLESPACE gen_tblsp ADD DATAFILE 'gen_tlbsp.ibd' ENGINE = INNODB;

Query OK, 0 rows affected (0.01 sec)

 

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G

*************************** 1. row ***************************

             FILE_ID: 27

           FILE_NAME: ./gen_tlbsp.ibd

           FILE_TYPE: TABLESPACE

     TABLESPACE_NAME: gen_tblsp

     ....

     ....

 

# Create table inside general tablespace.

mysql> CREATE TABLE gen_ts_tbl (id int(11), c_desc varchar(100), c_comments text ) TABLESPACE gen_tblsp;

Query OK, 0 rows affected (0.01 sec)

 

mysql> INSERT INTO gen_ts_tbl values (1, 'test' , 'General tablespace testing');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from gen_ts_tbl;

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

| id   | c_desc | c_comments                 |

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

|    1 | test   | General tablespace testing |

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

1 row in set (0.00 sec)

 

# Move Existing table into general tablespace.

 

mysql> create table InnoDB_table (id int (11), uname varchar(78));

Query OK, 0 rows affected (0.01 sec)

 

mysql> insert into InnoDB_table values(1,'moving to gen_tblsp');

Query OK, 1 row affected (0.01 sec)

 

mysql> ALTER TABLE InnoDB_table TABLESPACE gen_tblsp;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> select * from InnoDB_table;

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

| id   | uname               |

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

|    1 | moving to gen_tblsp |

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

1 row in set (0.00 sec)

 

# DROP General Tablespace [ We need to drop all table in general tablespace before dropping it]

 

mysql> show tables;

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

| Tables_in_test |

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

| gen_ts_tbl     |

| InnoDB_table   |

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

2 rows in set (0.00 sec)

 

mysql> drop table gen_ts_tbl;

Query OK, 0 rows affected (0.01 sec)

mysql> drop table InnoDB_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show tables;

Empty set (0.01 sec)

 

mysql> drop tablespace gen_tblsp;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from INFORMATION_SCHEMA.FILES  where TABLESPACE_NAME ='gen_tblsp'\G

Empty set (0.00 sec)


MYSQL 5.7 INNODB 表空间

InnoDB表TDE使用 keyring_file 插件

mysql>  SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS

        WHERE PLUGIN_NAME LIKE 'keyring%';

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

| PLUGIN_NAME  | PLUGIN_STATUS |

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

| keyring_file | ACTIVE        |

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

1 row in set (0.00 sec)

 


mysql> show variables like '%keyring%';

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

| Variable_name     | Value                          |

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

| keyring_file_data | /var/lib/mysql-keyring/keyring |

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

1 row in set (0.00 sec)

 


 mysql> CREATE TABLE InnoDB_tde (id int(11), c_desc varchar(100), c_comments text ) ENCRYPTION='Y';

Query OK, 0 rows affected (0.01 sec)

 


mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM

       INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';

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

| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |

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

| test         | InnoDB_tde | ENCRYPTION="Y" |

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

1 row in set (0.01 sec)

 


mysql> INSERT INTO InnoDB_tde values (1, 'test tde' , 'InnoDB tde testing');

Query OK, 1 row affected (0.00 sec)

 


mysql> select * from InnoDB_tde;

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

| id   | c_desc   | c_comments         |

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

|    1 | test tde | InnoDB tde testing |

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

1 row in set (0.01 sec)</pre>

# Disable - Enable ENCRYPTION from table

 


mysql> ALTER TABLE InnoDB_tde ENCRYPTION='N';

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

 


mysql> ALTER TABLE InnoDB_tde ENCRYPTION='Y';

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

 


mysql> select * from InnoDB_tde;

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

| id | c_desc | c_comments |

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

| 1 | test tde | InnoDB tde testing |

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

1 row in set (0.00 sec)

 

#ENCRYPTION MASTER KEY Rotation

 

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

Query OK, 0 rows affected (0.01 sec)

 

mysql> select * from InnoDB_tde;

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

| id | c_desc | c_comments |

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

| 1 | test tde | InnoDB tde testing |

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

1 row in set (0.00 sec)



以上就是对MYSQL 5.7 INNODB 表空间的介绍和简单使用示例,有没有帮助你更好的理解InnoDB表空间呢,本文来源于LALIT'S BLOG,小伙伴们快快学起来。

https://lalitvc.wordpress.com/2017/08/28/mysql-5-7-innodb-tablespace/

扫描下方二维码了解更多内容

MYSQL 5.7 INNODB 表空间