MYSQL5.7INNODB表空间

见贤思齐

背景介绍由于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 logsRedo 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 , 最大 95InnoDB_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 MemoryInnoDB_buffer_pool_size = 2000M# System Tablespace configurationInnoDB_data_file_path= ibdata1:512M;ibdata2:512M:autoextend# Redo log and buffer configurationInnoDB-log-files-in-group=3InnoDB_log_file_size=100MInnoDB_log_buffer_size=30M#InnoDB file formateInnoDB_file_format = Barracuda# UNDO Tablespace ConfigurationInnoDB_undo_directory = /var/lib/mysql/InnoDB_undo_tablespaces = 3InnoDB_undo_logs = 128InnoDB_undo_log_truncate = ONInnoDB_rollback_segments = 128# Temp Tablespace Configurationtmpdir = /var/lib/mysql/InnoDB_temp_data_file_path = ibtmp1:20M:autoextend# Keyring configurationearly-plugin-load=keyring_file.sokeyring_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 tablespacemysql> 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: 27FILE_NAME: ./gen_tlbsp.ibdFILE_TYPE: TABLESPACETABLESPACE_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: 0mysql> 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'\GEmpty set (0.00 sec) InnoDB表TDE使用 keyring_file 插件mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINSWHERE 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 FROMINFORMATION_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 tablemysql> ALTER TABLE InnoDB_tde ENCRYPTION='N';Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> ALTER TABLE InnoDB_tde ENCRYPTION='Y';Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> 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 Rotationmysql> 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/扫描下方二维码了解更多内容