MYSQL 5.7 INNODB 表空间

背景介绍

由于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和临时表空间

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)

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)

# 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/

扫描下方

二维码

了解更多内容

今天看啥 - 高品质阅读平台
本文地址：http://www.jintiankansha.me/t/iPcYw2giw4