MisakaTang's Blog

发表于 更新于 分类于

14.6.4 InnoDB数据字典(InnoDB Data Dictionary)

InnoDB数据字典由内部系统表构成,包含用于跟踪对象如表,索引和表的列的元数据.元数据实际位于InnoDB系统表空间内.由于历史原因,数据字典元数据在某种程度上与InnoDB表元数据文件(.frm文件)中存储的信息重叠.

14.6.5 双写缓冲区(Doublewrite Buffer)

双写缓冲区是位于系统表空间内的一块存储区域,InnoDB在页面被写到数据文件的正确位置之前会写入从InnoDB缓冲池中刷新的页面.只有在将页面刷新并写入双写缓冲区之后,InnoDB才将页面写到正确的位置上.如果在页面写入的过程中发生操作系统,存储子系统或mysqld进程崩溃,InnoDB可以在崩溃恢复之后从双写缓冲区中找到(之前写页面)的一个好的拷贝.

虽然数据总是要写两次,但是双写缓冲区不需要像两次写一样多的I/O负载或I/O操作.数据作为一个大的顺序块写入双写缓冲区,使用操作系统的单个fsync()调用.

在大多数情况下,默认启用双写缓冲区.设置 innodb_doublewrite=0来禁用双写缓冲区.

如果系统表空间(.ibd文件)位于Fusion-io设备上支持自动写入,双写缓冲区会自动禁用并且Fusion-io自动写会应用于所有的数据文件.因为双写缓冲区设置是全局的,所以对于没有存储在Fusion-io硬件设备上的数据文件也会禁用双写缓冲区.此功能仅在Fusion-io硬件上受支持,仅适用于Linux上的Fusion-io NVMFS.要充分利用此功能,建议设置innodb_flush_method为O_DIRECT.

发表于 更新于 分类于

14.6.3 表空间(Tablespaces)

14.6.3.1 系统表空间(The System Tablespace)

InnoDB系统表空间包含了InnoDB数据文件(InnoDB相关的对象元数据)并且存储区域是双写缓存,变更缓存和undo日志的存储区域.系统表空间也包含了用户创建在系统表空间中的表和索引数据.

系统表空间有一到多个数据文件.默认情况下名为 ibdata1的系统表空间数据文件被创建在数据目录下.系统表空间的大小和数量由innodb_data_file_path启动选项控制.

调整系统表空间(Resizing the System Tablespace)

增加InnoDB系统表空间的大小

增加InnoDB系统表空间大小的最简单方法是从一开始就将其配置为自动扩展.在表空间定义中指定最后一个数据文件为autoextend属性.然后InnoDB在空间不足时以64MB为增量自动增加该文件的大小.可以通过设置innodb_autoextend_increment系统变量的值来更改增量大小,以MB为单位.

你可以添加另一个数据文件指定数量来扩展系统表空间:

  1. 关闭MySQL服务器.
  2. 如果之前的最后一个数据文件定义了关键字 autoextend,根据基于实际增长的大小改变其定义为固定的大小.检查数据文件的大小,将其向下舍入为最接近的1024×1024字节的倍数(1MB),然后在 innodb_data_file_path指定这个大小.
  3. 在 innodb_data_file_path配置后添加一个新的数据文件,配置其自动扩展.只有 innodb_data_file_path中的最后一个文件可以被指定为自动扩展.
  4. 启动MySQL服务器.

例如,表空间只有一个自动扩展的数据文件ibdata1:

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假如这个数据文件在一段时间之后增长到了988MB.以下是修改原始数据文件使用固定大小并添加新的自动扩展数据文件后的配置:

1
2
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

当你在系统表空间配置中添加一个新的数据文件的时候,确保文件路径不会引用到已存在的文件.InooDB会在你重启服务器的时候创建这个文件.

减小InnoDB系统表空间的大小

你不能删除系统表空间的数据文件.要减小系统表空间,使用下面的步骤:

  1. 使用mysqldump备份你所有的InnoDB表,包括位于MySQL数据库中的InnoDB表.从5.6开始,MySQL数据库中包含五个InnoDB表: 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
    +---------------------------+
    | TABLE_NAME                |
    +---------------------------+
    | engine_cost               |
    | gtid_executed             |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | plugin                    |
    | server_cost               |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    +---------------------------+
  2. 关闭服务器
  3. 删除所有存在的表空间文件(*.ibd),包括 ibdata和 ib_log文件.不要忘记删除MySQL数据库中表的*.ibd文件.
  4. 删除全部InnoDB表的.frm文件.
  5. 配置一个新的表空间.
  6. 重启服务器.
  7. 导入备份文件

为系统表空间使用原始磁盘分区(Using Raw Disk Partitions for the System Tablespace)

你可以在InnoDB系统表空间中使用原始磁盘分区作为数据文件.此技术可在Windows和某些Linux和Unix系统上启用非缓冲I/O,而无需文件系统开销.使用和不使用原始分区执行测试以验证此更改是否实际上提高了系统性能.

使用原始磁盘分区时,请确保运行MySQL服务器的用户具有该分区的读写权限.例如,如果你使用mysql用户运行服务,mysql用户就必须有这个分区的读写权限.如果你使用 –memlock配置运行服务器,服务就必须使用root运行,root就必须有这个分区的读取权限.

下面描述的过程涉及选项文件修改

在Linux和Unix系统上分配原始磁盘分区

  1. 创建新数据文件时,在innodb_data_file_path选项的数据文件大小之后立即指定关键字newraw.分区必须至少与指定的大小一样大.请注意,InnoDB中的1MB是1024×1024字节,而磁盘规格中的1MB通常意味着1,000,000字节. 
    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
  2. 重启服务器.InnoDB注意到newraw关键字会初始化新分区.但是不要创建或更改任何InnoDB表.否则,当您下次重新启动服务器时,InnoDB将重新初始化分区,并且你的更改将丢失.
  3. 在InnoDB初始化新分区后,停止服务器,将数据文件规范中的newraw更改为raw: 
    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
  4. 重启服务器后InnoDB就允许进行更改.

在Windows上分配原始磁盘分区

在Windows系统上,除了用于Windows的innodb_data_file_path设置略有不同外,和Linux和Unix系统使用相同步骤和附带指南.

  1. 创建新数据文件时,请在innodb_data_file_path选项的数据文件大小之后立即指定关键字newraw 
    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw
  2. 重启服务器.InnoDB注意到newraw关键字并初始化新分区.
  3. 在InnoDB初始化新分区后,停止服务器,将数据文件规范中的newraw更改为raw 
    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Graw
  4. 重启服务器后InnoDB就允许进行更改.
14.6.3.2 单表单文件表空间(File-Per-Table Tablespaces)

在以前,InnoDB表存储在系统表空间中.这种存储方法只适用于专门用于数据库处理的机器,需要仔细计划数据增长,分配给MySQL的任何磁盘存储器永远不会用于其他目的.单表单文件特性提供了一个更加灵活的选择,每个InnoDB表都存储在其自己的表空间数据文件(.ibd)里.这个特性由 innodb_file_per_table配置选项控制,默认开启.

优势

  • 你可以在截断或删除存储在单表单文件表空间中的表时回收磁盘空间.截断或删除存储在系统表空间中的表会在系统表空间内部创建空闲空间,只能被新的InnoDB数据使用.

    同样的,驻留在通用表空间中表的表复制ALTER TABLE操作可以增加表空间可以使用的空间总量.此类操作可能需要与表中的数据和索引一样多的额外空间.表复制ALTER TABLE操作所需的额外空间不会像文件每表表空间一样释放回操作系统.

  • 截断表(TRUNCATE TABLE)操作在单表单文件表空间的表上运行更快.

  • 你可以将特定表存储在单独的存储设备上,以进行I/O优化,空间管理或备份.在以前的版本中,你必须将整个数据库目录移动到其他驱动器并在MySQL数据目录中创建符号链接.在MySQL 5.6.6及更高版本中，您可以使用 CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory指定每个表的位置.

  • 你可以使用OPTIMIZE TABLE来压缩或重建单表单文件表空间.当你运行OPTIMIZE TABLE时,InnoDB创建一个临时文件名的.ibd文件,只使用存储数据实际需要的空间.当优化完成后,InnoDB会删除旧的.ibd文件然后用新的替换.如果之前的.ibd文件显著增长但实际数据只用了很小一部分,执行OPTIMIZE TABLE来回收没有用到的空间.

  • 你可以移除单个的InnoDB表而不是整个数据库.

  • 你可以从一个数据库实例拷贝单个InnoDB表到另一个实例.

  • 创建在单表单文件表空间中的表使用 Barracuda文件格式. Barracuda文件格式支持例如压缩和动态行格式的特性.

  • 你可以使用动态行格式( dynamic row format)来更高效的存储大的BLOB和TEXT列.

  • 单表单文件表空间也提高了成功恢复的机会和节约了崩溃发生时的时间,例如服务器不能重启或备份和二进制日志不可用.

  • 你可以使用MySQL企业备份更快的备份或转存单个表而不会中断其他InnoDB表的使用.如果你的表不需要经常备份或需要使用不同的备份计划,这会是非常有用的.

  • 在复制或备份表时,单表单文件表空间报告每个表的状态会更便利.

  • 你可以在不访问MySQL的情况下监视文件系统级别的表大小.

  • 当 innodb_flush_method设置为 O_DIRECT时,常见的Linux文件系统不允许对单个文件的并发写.因此,将单表单文件表空间和 innodb_flush_method组合时可能会有性能提升.

  • 系统表空间存储了数据字典和重做日志(undo logs),并且InnoDB表空间大小限制也有限制.使用单表单文件表空间,每个表都有其自己的表空间,为增长提供了空间.

潜在缺点

  • 在单表单文件表空间里,每个表都会有没有使用的空间而且这些空间只能被同一张表的行使用.如果没有正确管理的话会导致空间浪费.

  • fsync 操作必须运行在每个表上而不是单文件.由于每个文件都有单独的fsync操作,因此无法将多个表上的写操作组合到单个I/O操作中.这可能需要InnoDB执行更高的fsync操作总数.

  • mysqld必须为每个表保留一个打开的文件句柄,如果在每个表的文件表空间中有许多表,这可能会影响性能.

  • 使用更多文件描述符.

  • MySQL 5.6及更高版本默认开启innodb_file_per_table.如果你要向早期版本兼容,考虑禁用.禁用innodb_file_per_table可防止表复制ALTER TABLE操作将隐藏在系统表空间中的表隐式移动到每个表的文件表空间.

    例如,当重组聚簇索引时,使用innodb_file_per_table的当前设置重新创建表.此行为不适用于使用INPLACE算法的ALTER TABLE操作.此行为也不适用于使用CREATE TABLE ... TABLESPACE或ALTER TABLE ... TABLESPACE语法添加到系统表空间的表.这些表不受innodb_file_per_table设置的影响.

  • 如果许多表在增长,则可能存在更多碎片,这可能会阻碍DROP TABLE和表扫描性能.但是,在管理碎片时,在自己的表空间中放置文件可以提高性能.

  • 删除每个表的文件表空间时会扫描缓冲池,对于大小为几十千兆字节的缓冲池,可能需要几秒钟.使用粒度大的内部锁执行扫描,这可能会延迟其他操作.系统表空间中的表不受影响.

  • innodb_autoextend_increment定义了增量大小(以MB为单位),用于自动扩展变满的通用表空间文件,但不适用于单表单文件表空间文件,无论innodb_autoextend_increment如何,这些文件都是自动扩展的.初始扩展是少量的,之后扩展以4MB增加.

启用单表单文件表空间(Enabling File-Per-Table Tablespaces)

innodb_file_per_table选项默认是开启的.

为了启动innodb_file_per_table,使用 –innodb-file-per-table参数来启动服务或在my.cnf配置文件的[mysqld]部分增加:

1
2
[mysqld]
innodb_file_per_table=1

你也可以在服务运行时动态设置 innodb_file_per_table:

1
mysql> SET GLOBAL innodb_file_per_table=1;

启用innodb_file_per_table后,可以将InnoDB表存储在tbl_name.ibd文件中.与MyISAM存储引擎将索引和数据分别存储在tbl_name.MYD和tbl_name.MYI文件中不同,InnoDB将数据和索引存储在一个.ibd文件中.

如果你在启动选择中禁用 innodb_file_per_table或使用SET GLOBAL命令禁用, InnoDB在系统表空间内创建新表,除非使用CREATE TABLE ... TABLESPACE选项将表显式放置在单表单文件表空间或通用表空间中.

无论每个表的文件设置如何,你始终可以读写任何InnoDB表.

要将表从系统表空间移动到其自己的表空间,更改innodb_file_per_table设置并重建表

1
2
mysql> SET GLOBAL innodb_file_per_table=1;
mysql> ALTER TABLE table_name ENGINE=InnoDB;

使用CREATE TABLE ... TABLESPACE或ALTER TABLE ... TABLESPACE语法将表添加到系统表空间而不受innodb_file_per_table的影响.要将这些表从系统表空间移动到单表单文件表空间,必须使用ALTER TABLE ... TABLESPACE语法.

14.6.3.3 通用表空间(General Tablespaces)

通用表空间是使用 CREATE TABLESPACE语法创建的共享的InnoDB表空间.通用表空间的功能和特性由下面的几个主题描述:

通用表空间功能(General Tablespace Capabilities)

通用表空间提供以下功能:

  • 和系统表空间一样,通用表空间属于共享表空间也可以存储多表数据.
  • 和单表单文件表空间相比通用表空间有潜在的内存性能优势.在表空间的生命周期内服务器把表空间的元数据放在内存中.在表数量相同的情况下,在通用表空间中的多个表比在单表单文件中消耗更少的内存.
  • 通用表空间的文件可以放在MySQL数据文件夹内部或外部,提供了和对单表单文件表空间一样的存储管理功能和更多的数据文件.和单表单文件表空间一样,把数据文件放到MySQL数据文件外面使你可以管理关键表的性能,为特定表设置RAID或DRBD或把特定表绑定到特定的磁盘上等等.
  • 通用表空间支持Antelope 和Barracuda 文件格式,因此支持所有表的行格式和相关特性.因为支持两种文件格式,通用表空间不依赖innodb_file_format或 innodb_file_per_table设置,这两个变量不会影响任何一个通用表空间.
  • 可以在 CREATE TABLE语句中添加TABLESPACE 选项在通用表空间,单表单文件表空间或系统表空间中创建表.
  • 可以在ALTER TABLE语句中添加TABLESPACE 选项在通用表空间,单表单文件表空间或系统表空间之间移动表.以前,无法将单表单文件表空间中的表移到系统表空间中.有了通用表空间特性,你现在可以这么做了.

创建一个通用表空间(Creating a General Tablespace)

使用 CREATE TABLESPACE语句创建通用表空间

1
2
3
4
CREATE TABLESPACE tablespace_name
  ADD DATAFILE 'file_name'
  [FILE_BLOCK_SIZE = value]
      [ENGINE [=] engine_name]

通用表空间可以创建在数据文件内或之外.为避免和隐式创建的单表单文件表空间冲突,不支持在数据目录的子目录中创建通用表空间.在数据目录外创建通用表空间时,该目录必须在创建表空间之前存在.

当在MySQL数据文件之外创建通用表空间的时候会在MySQL数据目录之内创建一个.isl文件.

例如:

在数据目录内创建一个通用表空间:

1
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

在数据目录外创建一个通用表空间:

1
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

只要表空间目录不在数据目录下,就可以指定相对路径.例如 my_tablespace目录和数据目录同级:

1
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

向通用表空间添加表(Adding Tables to a General Tablespace)

在创建了一个InnoDB通用表空间之后,你可以使用CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name或 ALTER TABLE tbl_name TABLESPACE [=] tablespace_name向表空间中添加表,如下:

CREATE TABLE:

1
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

ALTER TABLE:

1
mysql> ALTER TABLE t2 TABLESPACE ts1;

通用表空间支持的行格式

通用表空间支持所有行格式(REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)需要注意的是,由于物理页面大小不同,压缩和未压缩的表不能在同一个通用表空间中共存.

对于包含压缩表(ROW_FORMAT=COMPRESSED)的通用表空间必须指定 FILE_BLOCK_SIZE,并且 FILE_BLOCK_SIZE必须是和 innodb_page_size值相关的有效值.此外,压缩表的物理页面大小(KEY_BLOCK_SIZE)必须等于FILE_BLOCK_SIZE/1024.例如,如果 innodb_page_size=16KB且FILE_BLOCK_SIZE=8K, KEY_BLOCK_SIZE的值就必须是8.

下面的表展示了允许的 innodb_page_size, FILE_BLOCK_SIZE,和KEY_BLOCK_SIZE的组合. FILE_BLOCK_SIZE值也可以指定以字节为单位.为了对于给定FILE_BLOCK_SIZE的值决定合法的 KEY_BLOCK_SIZE值,需要把 FILE_BLOCK_SIZE 除以1024.表压缩不支持32k和64K的InnoDB页大小.

下面的示例演示了如何创建通用表空间并添加压缩表.例子假设 innodb_page_size为16KB.FILE_BLOCK_SIZE 的值为8192要求压缩表的KEY_BLOCK_SIZE 的值为8.

1
2
3
mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

如果创建通用表空间的时候没有指定FILE_BLOCK_SIZE ,其值默认为 innodb_page_size.当FILE_BLOCK_SIZE等于 innodb_page_size时,表空间可能只包含没有压缩的行格式的表(COMPACT, REDUNDANT,和 DYNAMIC行格式).

使用ALTER TABLE在两个表空间之间移动表

你可以使用 ALTER TABLE语句加 TABLESPACE 选项移动一个表到一个已存在的通用表空间,一个新的单表单文件表空间或到系统表空间中.

把单表单文件表空间或系统表空间中的表移动到通用表空间中时要指定通用表空间名,指定的通用表空间名要存在.

1
ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;

把通用表空间或单表单文件表空间中的表移动到系统表空间中时,要指定 innodb_system 为表空间名.

1
ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

把系统表空间或通用表空间中的表移动到单表单文件表空间中时,要指定 innodb_file_per_table为表空间名.

1
ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

ALTER TABLE ... TABLESPACE操作总是会导致完整的表重建,即使TABLESPACE属性没有更改.

ALTER TABLE ... TABLESPACE语法不支持将表从临时表空间移动到持久表空间.

CREATE TABLE ... TABLESPACE = innodb_file_per_table允许DATA DIRECTORY子,，但不支持与TABLESPACE选项结合使用.

删除通用表空间

DROP TABLESPACE语句用于删除InnoDB通用表空间.

必须在DROP TABLESPACE操作之前从表空间中删除所有表. 如果表空间不为空,则DROP TABLESPACE返回错误.

使用类似于以下内容的查询来标识通用表空间中的表:

1
2
3
4
5
6
7
8
9
mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,
       INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test/t1    |
| ts1        | test/t2    |
| ts1        | test/t3    |
+------------+------------+

如果空的通用表空间上的DROP TABLESPACE操作出错,则表空间可能包含由服务器出口中断的ALTER TABLE操作留下的临时表或中间表.

删除表空间中的最后一个表时,不会自动删除InnoDB的通用表空间.必须使用DROP TABLESPACE tablespace_name显式删除表空间.

通用表空间不属于任何数据库.DROP DATABASE操作可以删除属于通用表空间的表但不能删除表空间,即使DROP DATABASE操作删除属于表空间的所有表也一样.必须使用DROP TABLESPACE tablespace_name显式删除通用表空间.

此示例演示如何删除InnoDB通用表空间.通用表空间中只有t1一张表.必须在删除表空间之前删除表.

1
2
3
4
5
6
7
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts10 Engine=InnoDB;

mysql> DROP TABLE t1;

mysql> DROP TABLESPACE ts1;

通用表空间的限制

  • 生成的或现有的表空间不能更改为通用表空间.

  • 不支持创建临时通用表空间.

  • 通用表空间不支持临时表.

  • 存储在通用表空间中的表只能在支持通用表空间的MySQL版本中打开.

  • 与系统表空间类似,截断或删除存储在通用表空间中的表会在通用表空间.ibd数据文件内部创建可用空间,该数据文件只能用于新的InnoDB数据.空间不会像单表单文件表空间一样释放回操作系统.

    此外,共享表空间(通用表空间或系统表空间)中的表上的表复制ALTER TABLE操作可以增加表空间使用的空间量.此类操作需要与表中的数据和索引一样多的额外空间.表复制ALTER TABLE操作所需的额外空间不会像单表单文件表空间一样释放回操作系统.

  • 属于通用表空间中的表不支持LTER TABLE ... DISCARD TABLESPACE和 ALTER TABLE ...IMPORT TABLESPACE.

  • 在MySQL 5.7.24中不支持在通用表空间中放置表分区,在以后的MySQL版本中将删除.

14.6.3.4 Undo表空间

Undo表空间包含了undo日志,undo日志集合包含了如何撤销由事务引起到聚簇索引的最近的修改.undo日志包含在undo日志段中,这些日志段在回滚段中. innodb_rollback_segments变量定义了分配给每个undo表空间的回滚段数.

undo日志可以存储在多个undo表空间中,而不是系统表空间里.此布局与undo日志驻留在系统表空间中的默认配置不同.undo日志的I/O模式使得undo表空间很适合SSD存储,而同时可以把系统表空间存储在硬盘上.

InnoDB的undo表空间的数量由 innodb_undo_tablespaces变量控制.这个配置只能在MySQL实例启动的时候配置,之后不能更改.

undo表空间及这些表空间内相关的段不能被删除.然后undo表空间中的undo日志可以被截断.

配置undo表空间

为了配置MySQL实例的undo表空间,使用下面的步骤.在将配置部署到生产系统之前在测试实例上执行该过程.在部署到生产环境之前在测试环境测试.

  1. 使用 innodb_undo_directory配置选项指定undo表空间目录.如果没有指定目录,undo表空间会创建在数据目录内.

  2. 使用 innodb_rollback_segments定义回滚段的数量.从一个相对比较低的数值开始,然后逐渐增加数值来查看对性能的影响. innodb_rollback_segments的默认值是128,也可能就是最大值了.

    一个回滚段总是分配给系统表空间并且32个回滚段保留给临时表空间(ibtmp1).因此,为了给undo表空间分配回滚段, innodb_rollback_segments的值要比33大.例如,如果你有2个undo表空间,设置innodb_rollback_segments为35可以给每个undo表空间分配一个回滚段.回滚段以循环方式分布在undo表空间中.

    当你配置单独的undo表空间时,系统表空间中的回滚段会变为非活动状态.

  3. 使用 innodb_undo_tablespaces选项来定义使用undo表空间的数量.指定的undo表空间的数量会在MySQL生命周期内持续生效,所以如果你不知道最佳值,尽量调高数值.

  4. 创建一个新的MySQL测试实例来调试这些选项.

  5. 在测试服务器上使用和生产服务器上近似的工作负载来进行测试.

  6. 对I/O密集型的工作负载进行性能基准测试.

  7. 逐步增加innodb_rollback_segments的值并运行性能测试,直到I/O性能没有进一步提高

截断undo表空间(Truncating Undo Tablespaces)

截断undo表空间需要MySQL实例至少有两个活动的undo表空间,用于确保一个undo表空间保持活跃在另一个表脱机进行截断的时候.undo表空间的数量由 innodb_undo_tablespaces定义.默认值是0.使用下面的语句来检测 innodb_undo_tablespaces的值:

1
2
3
4
5
6
mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+

截断undo表空间要先启用 innodb_undo_log_truncate选项,例如:

1
mysql> SET GLOBAL innodb_undo_log_truncate=ON;

启用innodb_undo_log_truncate变量时,超出innodb_max_undo_log_size大小限制的undo表空间将被截断. innodb_max_undo_log_size是动态的,默认为1024MB.

1
2
3
4
5
6
mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

当 innodb_undo_log_truncate变量启用的时候:

  1. 超过 innodb_max_undo_log_size设置大小的undo表空间被标记为截断.对要截断的undo表空间的选择方式为循环选择来避免同一时间截断相同的undo表空间.

  2. 驻留在被选择的undo表空间中的回滚段会被变为非活动状态来防止不将其分配给新的事务.当前回滚段的事务允许执行到结束.

  3. 清理(purge)系统释放不再使用的回滚段.

  4. 当undo表空间的所有回滚段都释放之后,截断操作就会执行将undo表空间截断到初始化的大小.undo表空间的初始化大小由 innodb_page_size决定.对于默认的16KB页面大小,undo表空间的初始值是10Mib.对4KB,8KB, 32KB和64KB的页面大小,undo表空间的初始大小是7MiB,8MiB,20MiB,和40MiB.

    如果在截断操作完成后立即使用,undo表空间的大小可能会比初始化的大小更大.

    innodb_undo_directory变量定义了undo表空间文件的位置.如果 innodb_undo_directory变量未定义,undo表空间就在数据目录下.

  5. 重新启用回滚段以便分配给新的事务.

加速undo表空间截断(Expediting Truncation of Undo Tablespaces)

清除(purge)线程负责清空和截断undo表空间.默认情况下,清除线程查找需要截断的undo表空间,每隔128次查找就清除一次.清除线程查找要截断的undo表空间的频率由innodb_purge_rseg_truncate_frequency变量控制,默认是128.

1
2
3
4
5
6
mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+

为了增加频率需要减小 innodb_purge_rseg_truncate_frequency的值.例如你想每32次查找就调用一次清除的话,设置 innodb_purge_rseg_truncate_frequency为32.

1
mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

截断undo表空间文件的性能影响(Performance Impact of Truncating Undo Tablespace Files)

当一个undo表空间被截断时,上面的回滚段已经被停用.其他表空间中活动的回滚段会承担整个系统的负载,这可能导致系统性能略有下降.性能下降的程度取决于很多因素:

  • undo表空间的数量.
  • undo日志的数量.
  • undo表空间的大小.
  • I/O部分的速度.
  • 是否存在正在运行的长事务.
  • 系统负载.

避免截断撤销表空间时对性能影响最简单的方法是增加表空间的数量.

14.6.3.5 临时表空间(The Temporary Tablespace)

非压缩的用户创建的临时表和磁盘内部临时表被创建在共享临时表空间内.innodb_temp_data_file_path配置定义了临时表空间数据文件的相关路径,名称,大小和属性.如果没有给innodb_temp_data_file_path指定值,默认会在 innodb_data_home_dir目录下创建一个略大于12MB的自动扩展的名为ibtmp1的数据文件.

可压缩的临时表是用ROW_FORMAT=COMPRESSED属性创建的临时表,是在临时文件目录的单表单文件表空间中创建的.

临时表空间在正常关闭和终止初始化的时候被删除,并在每次服务器启动时重新创建.临时表空间在创建时获得一个动态生成的空间ID.如果无法创建临时表空间就不能启动.如果服务器意外终止的话临时表空间是不会删除的.在这种情况下,数据库管理员可以手动删除临时表空间或重启服务器,都可以自动地删除和重建临时表空间.

临时表空间不能驻留在原始设备上.

INFORMATION_SCHEMA.FILES提供了关于InnoDB临时表的元数据.执行下面的简单查询来查看临时表空间的元数据.

1
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO提供了现在还在InnoDB实例中活跃的由用户创建的临时表的元数据.

管理临时表空间数据文件的大小

默认情况下,临时表空间数据文件是自动扩展的,并且根据需要增加大小以容纳磁盘上的临时表.例如,如果一个操作创建了一个20MB的临时表,默认创建的12MB大小的临时表空间会自动扩大以适应它.当临时表被删除的时候,释放的空间可以被新的临时表使用,但是数据文件仍然保留扩展之后的大小.

在使用大型临时表或广泛使用临时表的情况下自动扩展的临时表数据文件会变得很大.大的数据文件也可能是使用临时表执行一个长时间运行的查询造成的.

检查 innodb_temp_data_file_path设置来决定临时表空间数据文件是否要自动扩展:

1
2
3
4
5
6
mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

查询 INFORMATION_SCHEMA.FILES表来检查临时表空间数据文件的大小:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE 
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES 
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

TotalSizeBytes的值表示当前临时表空间数据文件的大小.

另外,在你的操作系统上检查临时表空间的数据文件大小.默认情况下,临时表空间数据文件位于 innodb_temp_data_file_path定义的目录下.如果没有指定该值,一个名为ibtmp1的临时表空间数据文件创建在innodb_data_home_dir下,默认情况下为MySQL数据文件夹.

要回收临时表空间数据文件占用的磁盘空间,要重新启动MySQL服务器.重新启动服务器将根据innodb_temp_data_file_path定义的属性删除并重新创建临时表空间数据文件.

要防止临时数据文件变得太大,可以配置innodb_temp_data_file_path选项以指定最大文件大小.例如:

1
2
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

当数据文件达到最大大小时,查询会失败并显示一个表已满的错误.配置innodb_temp_data_file_path需要重新启动服务器.

另外,配置default_tmp_storage_engine和internal_tmp_disk_storage_engine选项,分别定义用于用户创建的和磁盘内部临时表的存储引擎.默认情况下,这两个选项都设置为InnoDB. MyISAM存储引擎为每个临时表使用单个文件,在删除临时表时将删除该文件.

14.6.3.6 在数据文件外创建表空间

CREATE TABLE ... DATA DIRECTORY语句允许在数据目录之外创建单表单文件表空间.例如,你可以用DATA DIRECTORY 语句在特定性能或容量的单独的存储设备上创建表空间,例如快速的SSD或者大容量的HDD.

确定你选择的位置没有问题.DATA DIRECTORY子句不能和 ALTER TABLE一起使用来改变位置.

表空间数据文件创建在指定的目录里,每个表都会有一个以表名命名的子目录.包含表空间目录的.isl文件创建在子目录下.可以把.isl视为符号链接.

下面的示例演示了在数据目录之外创建一个单表单文件表空间.假定 innodb_file_per_table变量是默认的.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> USE test;
Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/remote/directory';

# MySQL creates the tablespace file in a subdirectory that is named
# for the schema to which the table belongs

shell> cd /remote/directory/test
shell> ls
t1.ibd

# In the schema directory, MySQL creates an isl file that defines 
# the tablespace path 

shell> cd /path/to/mysql/data/test
shell> ls
db.opt  t1.frm  t1.isl

CREATE TABLE ... TABLESPACE语句也可以和DATA DIRECTORY 子句组合在数据目录外创建一个单表单文件表空间.要这样做的话,指定innodb_file_per_table作为表空间名.

1
2
mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
       DATA DIRECTORY = '/remote/directory';

使用此方法时不需要启用 innodb_file_per_table变量.

使用说明

  • MySQL最初保持表空间数据文件打开,阻止卸载设备,但如果服务器忙,最终可能会关闭表.注意不要在MySQL运行时意外卸载外部设备,或在设备断开连接时启动MySQL.在关联的表空间数据文件丢失时尝试访问表会导致严重错误,需要重新启动服务器.

    如果表空间数据文件文件不在预期路径服务器重新启动可能会失败.在这种情况下,请从目录中手动删除isl文件,并在重新启动后删除该表以从数据字典中删除.frm文件和有关该表的信息.

  • 在将表空间放在已安装NFS的卷上之前,请查看使用NFS与MySQL中列出的潜在问题.

  • 如果使用LVM快照,文件复制或其他基于文件的机制来备份表空间数据文件,首先使用FLUSH TABLES ... FOR EXPORT语句确保在备份之前将缓冲在内存中的所有更改都刷新到磁盘.

  • 不支持使用DATA DIRECTORY子句视作使用符号链接的替代方法.

14.6.3.7 复制表空间到其他实例

这个部分描述如何把单表单文件表空间从一个MySQL实例复制到另一个实例,也称为可传输表空间.

有许多理由可能导致你要复制一个InnoDB单表单文件表空间到另一个实例:

  • 在不对生产服务器施加额外负载的情况下运行报告.
  • 在新的slave服务器上对表设置相同的数据.
  • 在出现问题或错误后恢复备份版本的表或分区.
  • 作为一种更快速的数据移动方式,比使用mysqldump导入更快.数据立即可用并且不用重新插入和重建索引.
  • 将单表单文件表空间移动到具有更适合系统要求的存储介质的服务器.例如你想把频繁操作的表放在SSD上或把大型表放到大容量的HDD上.

局限性和使用注意(Limitations and Usage Notes)

  • 只有在 innodb_file_per_table设置为ON时才能进行表空间复制(默认设置).驻留在系统表空间中的表不能停顿.
  • 当一个表停顿的时候,该表上只允许只读事务.
  • 当导入表空间时,页面大小必须和导入的实例的页面大小匹配.
  • 当 foreign_key_checks设置为1时,有父子关系(主键-外键)的表空间不支持DISCARD TABLESPACE.在丢弃父子表的表空间之前,需要设置foreign_key_checks = 0.分区的InnoDB表不支持外键.
  • ALTER TABLE ... IMPORT TABLESPACE在导入数据时不强制外键约束.如果表之间有外键约束,所有的表应该在同一个时间节点(逻辑上)进行导出.分区的InnoDB表不支持外键.
  • ALTER TABLE ... IMPORT TABLESPACE和 ALTER TABLE ... IMPORT PARTITION ... TABLESPACE不需要.cfg元数据来导入表空间.但是,在没有.cfg文件的情况下导入时元数据检查不会执行,并发出类似下面的警告: 
    1
    2
    3
    Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
    test\t.cfg', will attempt to import without schema verification
    1 row in set (0.00 sec)
    当没有预期的模式不匹配的时候进行没有.cfg文件的导入会更加便利.另外,没有.cfg文件导入的功能在崩溃恢复时非常有用,在这种情况下无法从.ibd文件中收集元数据.
  • 因为.cfg元数据文件的限制,导入分区表的表空间文件时,不会报告分区类型或分区定义差异的模式不匹配.列的差异会被报告.
  • 在子分区表上运行 ALTER TABLE ... DISCARD PARTITION ... TABLESPACE和 ALTER TABLE ... IMPORT PARTITION ... TABLESPACE时,允许分区和子分区的表名.当指定分区名时,分区的子分区会被包含到操作中.
  • 如果两个实例都具有GA(General Availability)状态且其版本在同一系列中,则可以从另一个MySQL服务器实例导入表空间文件.否则,必须在导入该文件的同一服务器实例上创建该文件.
  • 在复制时,master和slave上的 innodb_file_per_table必须设置为ON.
  • 在Windows上,InnoDB隐式使用小写存储数据库,表空间和表名.为了避免在大小写敏感的操作系统上(如Linux和UNIX)产生导入错误,使用小写来创建所有的数据库,表空间和表名.一个方便的办法是在创建数据库,表空间或表之前在my.cnf或my.ini的[mysqld]部分下添加下面的行: 
    1
    2
    [mysqld]
    lower_case_table_names=1
  • ALTER TABLE ... DISCARD TABLESPACE和 ALTER TABLE ...IMPORT TABLESPACE不支持属于InnoDB通用表空间的表.
  • InnoDB的默认行格式使用innodb_default_row_format 配置选项.导入未明确定义行格式(ROW_FORMAT)的表或使用ROW_FORMAT = DEFAULT的表,如果源实例上的innodb_default_row_format设置与目标实例上的设置不同,可能导致模式不匹配错误.
  • 导出使用InnoDB表空间加密功能加密的表空间时,除了.cfg元数据文件外,InnoDB还会生成.cfp文件.在目标实例上执行ALTER TABLE ... IMPORT TABLESPACE操作之前,需要把.cfp文件和.cfg文件一起复制到目标实例上..cfp文件包含传输密钥和加密的表空间密钥.在导入时,InnoDB使用传输密钥来解密表空间密钥.
  • FLUSH TABLES ... FOR EXPORT不支持有全文索引的表.全文搜索辅助表不会刷新.在导入有全文索引的表后,执行 OPTIMIZE TABLE来重建全文索引.或者在导出操作之前删除全文索引,并在导入目标实例上的表后重新创建.
14.6.3.7.1 传输表空间例子

Example 1: 复制InnoDB表到另一个实例

此过程演示如何将常规InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.可以使用具有微小调整的相同过程在同一实例上执行完整表还原.

  1. 在源实例上,创建表(如果不存在): 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
  2. 在目标实例上,创建表(如果不存在): 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
  3. 在目标实例上丢弃存在的表空间: 
    1
    mysql> ALTER TABLE t DISCARD TABLESPACE;
  4. 在源实例上,运行 FLUSH TABLES ... FOR EXPORT来停顿表然后创建.cfg元数据文件. 
    1
    2
    mysql> USE test;
    mysql> FLUSH TABLES t FOR EXPORT;
    元数据文件在InnoDB数据目录中.
  5. 从源实例复制.ibd文件和.cfg文件到目标实例,例如: 
    1
    shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test
  6. 在源实例上,使用 UNLOCK TABLES释放 FLUSH TABLES ... FOR EXPORT获取的锁: 
    1
    2
    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. 在目标实例上,导入表空间: 
    1
    2
    mysql> USE test;
    mysql> ALTER TABLE t IMPORT TABLESPACE;

Example 2: 复制InnoDB分区表到另一个实例

此过程演示如何将分区的InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.可以使用具有微小调整的相同过程在同一实例上执行分区InnoDB表的完全恢复.

  1. 在源实例上,创建分区表(如果不存在).下面的例子中一个表创建了3个分区(p0,p1,p2): 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
    在/datadir/test目录下,三个分区都有单独的.ibd文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
  2. 在目标实例上创建相同的分区表: 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
    在/datadir/test目录下,三个分区都有单独的.ibd文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
  3. 在目标实例上丢弃分区表表空间 
    1
    mysql> ALTER TABLE t1 DISCARD TABLESPACE;
    构成分区表的表空间的三个.ibd文件将从/datadir/test中丢弃,留下以下文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm
  4. 在源实例上,运行 FLUSH TABLES ... FOR EXPORT来停顿表然后创建.cfg元数据文件. 
    1
    2
    mysql> USE test;
    mysql> FLUSH TABLES t FOR EXPORT;
    为每个表空间文件(.ibd)生成元数据文件(.cfg),被创建在源实例的/datadir/test目录中: 
    1
    2
    3
    mysql> \! ls /path/to/datadir/test/
    db.opt t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd
    t1.frm  t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg
  5. 从源实例复制.ibd文件和.cfg文件到目标实例,例如: 
    1
    shell>scp /path/to/datadir/test/t1*.{ibd,cfg} destination-server:/path/to/datadir/test
  6. 在源实例上,使用 UNLOCK TABLES释放 FLUSH TABLES ... FOR EXPORT获取的锁: 
    1
    2
    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. 在目标实例上,导入表空间: 
    1
    2
    mysql> USE test;
    mysql> ALTER TABLE t IMPORT TABLESPACE;

Example 3: 复制InnoDB表分区到另一个实例

此过程演示如何将InnoDB表分区从正在运行的MySQL服务器实例复制到另一个正在运行的实例.可以使用具有微小调整的相同过程在同一实例上执行InnoDB表分区的还原.在下面的示例中,将在源实例上创建具有四个分区(p0,p1,p2,p3)的分区表.其中两个分区(p2和p3)被复制到目标实例.

  1. 在源实例上,创建分区表(如果不存在).下面的例子中一个表创建了4个分区(p0,p1,p2,p3): 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
    在/datadir/test目录下,四个分区都有单独的.ibd文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
  2. 在目标实例上创建相同的分区表: 
    1
    2
    mysql> USE test;
    mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
    在/datadir/test目录下,四个分区都有单独的.ibd文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
  3. 在目标实例上丢弃计划要从源实例上导入的表空间分区. 
    1
    mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
    目标实例上两个被丢弃的分区的.ibd文件从/datadir/test中被删除,剩下下面的文件: 
    1
    2
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1.frm  t1#P#p0.ibd  t1#P#p1.ibd
  4. 在源实例上,运行 FLUSH TABLES ... FOR EXPORT来停顿表然后创建.cfg元数据文件. 
    1
    2
    mysql> USE test;
    mysql> FLUSH TABLES t FOR EXPORT;
    为每个表空间文件(.ibd)生成元数据文件(.cfg),被创建在源实例的/datadir/test目录中: 
    1
    2
    3
    mysql> \! ls /path/to/datadir/test/
    db.opt  t1#P#p0.ibd  t1#P#p1.ibd  t1#P#p2.ibd t1#P#p3.ibd
    t1.frm  t1#P#p0.cfg  t1#P#p1.cfg  t1#P#p2.cfg t1#P#p3.cfg
  5. 从源实例复制.ibd文件和.cfg文件到目标实例.在这个例子中,只需要复制分区2(p2)和分区3(p3)的.ibd和.cfg文件. 
    1
    shell> scp t1#P#p2.ibd  t1#P#p2.cfg t1#P#p3.ibd t1#P#p3.cfg destination-server:/path/to/datadir/test
  6. 在源实例上,使用 UNLOCK TABLES释放 FLUSH TABLES ... FOR EXPORT获取的锁: 
    1
    2
    mysql> USE test;
    mysql> UNLOCK TABLES;
  7. 在目标实例上,导入表空间: 
    1
    2
    mysql> USE test;
    mysql> ALTER TABLE t IMPORT TABLESPACE;
14.6.3.7.2 可传输表空间内幕

以下信息描述了常规InnoDB表的可传输表空间复制过程的内部和错误日志消息传递.

当 ALTER TABLE ... DISCARD TABLESPACE运行在目标实例上时:

  • 表被锁定在X模式下.
  • 表空间和表是分离的.

当 FLUSH TABLES ... FOR EXPORT运行在源实例上时:

  • 刷新后正在导出的表在共享模式下被锁定.
  • 清除(purge)协调器线程已停止.
  • 脏页面同步到磁盘.
  • 表元数据被写到二进制.cfg文件.

操作可能导致下面的预期错误:

1
2
3
4
2013-09-24T13:10:19.903526Z 2 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-09-24T13:10:19.903586Z 2 [Note] InnoDB: Stopping purge
2013-09-24T13:10:19.903725Z 2 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-09-24T13:10:19.904014Z 2 [Note] InnoDB: Table '"test"."t"' flushed to disk

当UNLOCK TABLES在源实例上运行时:

  • 二进制.cfg文件被删除.
  • 正在导入的表或表上的共享锁被释放并且清除协调器线程重新启动.

操作可能导致下面的预期错误:

1
2
2013-09-24T13:10:21.181104Z 2 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-09-24T13:10:21.181180Z 2 [Note] InnoDB: Resuming purge

当ALTER TABLE ... IMPORT TABLESPACE运行在目标实例上时,导入算法对要导入的每个表空间执行以下操作:

  • 检查每个表空间页面是否损坏.
  • 每页上的空间ID和日志序列号（LSN）都会更新.
  • 验证标志并更新起始页的LSN.
  • B树页被更新.
  • 页面状态设置为脏,以便将其写入磁盘.

操作可能导致下面的预期错误:

1
2
3
4
5
6
2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete
14.6.3.8 InnoDB表空间加密

(大致为机翻,没有太关注这部分内容)

表空间加密使用双层加密密钥体系结构,由主加密密钥和表空间密钥组成.当表空间被加密时,表空间密钥被加密并存储在表空间头中.当应用程序或经过身份验证的用户想要访问加密的表空间数据时,InnoDB使用主加密密钥来解密表空间密钥.表空间密钥的解密版本永远不会更改,但可以根据需要更改主加密密钥.此操作称为主密钥轮换.

表空间加密功能依赖于用于主加密密钥管理的密钥环插件.

所有MySQL版本都提供了一个keyring_file插件,它将密钥环数据存储在服务器主机本地的文件中.

MySQL企业版提供额外的密钥环插件:

  • keyring_encrypted_file插件,用于将密钥环数据存储在服务器主机本地的加密文件中.
  • keyring_okv插件,包括KMIP客户端(KMIP 1.1),该客户端使用兼容KMIP的产品作为密钥环存储的后端.支持KMIP的兼容产品包括集中式密钥管理解决方案,如Oracle Key Vault,Gemalto KeySecure,Thales Vormetric密钥管理服务器和Fornetix Key Orchestration.
  • keyring_aws插件,与Amazon Web Services密钥管理服务(AWS KMS)通信,作为密钥生成的后端,并使用本地文件进行密钥存储.

安全可靠的加密密钥管理解决方案对于安全性和遵守各种安全标准至关重要.当表空间加密功能使用集中式密钥管理解决方案时,该功能称为”MySQL企业透明数据加密(TDE)”.

表空间加密支持基于高级加密标准(AES)块的加密算法.它使用电子密码本(ECB)块加密模式进行表空间密钥加密,使用密码块链接(CBC)块加密模式进行数据加密.

InnoDB表空间加密要求(InnoDB Tablespace Encryption Prerequisites)

  • 必须安装和配置密钥环插件.Keyring插件安装在启动时使用early-plugin-load选项执行.早期加载可确保在InnoDB存储引擎初始化之前插件可用.

    一次只能启用一个密钥环插件.不支持启用多个密钥环插件.

    要验证密钥环插件是否处于活动状态,请使用SHOW PLUGINS语句或查询INFORMATION_SCHEMA.PLUGINS表.例如:

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE 'keyring%';
    +--------------+---------------+
    | PLUGIN_NAME  | PLUGIN_STATUS |
    +--------------+---------------+
    | keyring_file | ACTIVE        |
    +--------------+---------------+

  • 仅对每个表的文件表空间支持加密.要在每个表的文件表空间中创建一个表,请确保在执行CREATE TABLE语句之前启用了innodb_file_per_table(默认值).或者,在CREATE TABLE语句中使用TABLESPACE =’innodb_file_per_table’子句.

  • 加密生产数据时,请确保采取措施防止丢失主加密密钥.如果主加密密钥丢失,则存储在加密表空间文件中的数据将无法恢复.如果使用keyring_file或keyring_encrypted_file插件,请在创建第一个加密表空间之后,主密钥轮换之前以及主密钥轮换之后立即创建密钥环数据文件的备份.keyring_file_data配置选项定义keyring_file插件的密钥环数据文件位置.keyring_encrypted_file_data配置选项定义keyring_encrypted_file插件的密钥环数据文件位置.如果使用keyring_okv或keyring_aws插件,请确保已执行必要的配置.

启用或禁用单表单文件表空间加密

要为新的单表单文件表空间启用加密,请在CREATE TABLE语句中指定ENCRYPTION选项

1
mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';

要为现有的单表单文件表空间启用加密,请在ALTER TABLE语句中指定ENCRYPTION选项.

1
mysql> ALTER TABLE t1 ENCRYPTION='Y';

要禁用单表单文件表空间的加密,请使用ALTER TABLE设置ENCRYPTION =’N’.

1
mysql> ALTER TABLE t1 ENCRYPTION='N';

InnoDB表空间加密和主密钥轮换

只要您怀疑密钥已被泄露,就应定期轮换主加密密钥.

主密钥轮换是一种原子的实例级操作.每次旋转主加密密钥时,MySQL实例中的所有表空间键都会重新加密并保存回各自的表空间头. 作为原子操作,一旦启动旋转操作,所有表空间键的重新加密必须成功.如果服务器故障导致主密钥轮换中断,则InnoDB会在服务器重新启动时向前滚动操作.

旋转主加密密钥只会更改主加密密钥并重新加密表空间密钥.它不会解密或重新加密关联的表空间数据.

旋转主加密密钥需要SUPER权限.

要旋转主加密密钥,运行:

1
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

ALTER INSTANCE ROTATE INNODB MASTER KEY支持并发DML.但是,它不能与表空间加密操作同时运行,并且会采取锁定来防止可能由并发执行引起的冲突.如果ALTER INSTANCE ROTATE INNODB MASTER KEY操作正在运行,则必须在表空间加密操作继续之前完成,反之亦然.

InnoDB表空间加密和恢复(InnoDB Tablespace Encryption and Recovery)

如果在加密操作期间发生服务器故障,则在重新启动服务器时前滚操作.

如果在主密钥轮换期间发生服务器故障,InnoDB将继续执行服务器重新启动操作.

必须在存储引擎初始化之前加载密钥环插件,以便在InnoDB初始化和恢复活动访问表空间数据之前,可以从表空间头中检索解密表空间数据页所需的信息.

当InnoDB初始化和恢复开始时,主密钥轮换操作重新开始.由于服务器故障,某些表空间密钥可能已使用新的主加密密钥加密.InnoDB从每个表空间头读取加密数据,如果数据表明表空间密钥使用旧的主加密密钥加密,InnoDB从密钥环检索旧密钥并使用它来解密表空间密钥.InnoDB然后使用新的主加密密钥重新加密表空间密钥,并将重新加密的表空间密钥保存回表空间头.

导出加密表空间

导出加密表空间时,InnoDB会生成一个用于加密表空间密钥的传输密钥.加密的表空间密钥和传输密钥存储在tablespace_name.cfp文件中.需要此文件和加密的表空间文件才能执行导入操作.导入时,InnoDB使用传输密钥解密tablespace_name.cfp文件中的表空间键.

InnoDB表空间加密和复制

  • ALTER INSTANCE ROTATE INNODB MASTER KEY语句仅在主服务器和从服务器运行支持表空间加密的MySQL版本的复制环境中受支持.
  • 成功的ALTER INSTANCE ROTATE INNODB MASTER KEY语句被写入二进制日志以便在从站上进行复制.
  • 如果ALTER INSTANCE ROTATE INNODB MASTER KEY语句失败,则不会记录到二进制日志中,也不会在从属服务器上复制.
  • 如果密钥环插件安装在主服务器而不是从服务器上,则复制ALTER INSTANCE ROTATE INNODB MASTER KEY操作失败.
  • 如果主服务器和从服务器上都安装了keyring_file或keyring_encrypted_file插件,但是从服务器没有密钥环数据文件,则复制的ALTER INSTANCE ROTATE INNODB MASTER KEY语句会在从服务器上创建密钥环数据文件,假设密钥环文件数据为没有缓存在内存中.ALTER INSTANCE ROTATE INNODB MASTER KEY使用缓存在内存中的密钥环文件数据(如果有).

识别加密表空间

在CREATE TABLE或ALTER TABLE语句中指定ENCRYPTION选项时,它将记录在INFORMATION_SCHEMA.TABLES的CREATE_OPTIONS列中.可以查询此列以标识驻留在加密的每个表文件表空间中的表.

1
2
3
4
5
6
7
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
       WHERE CREATE_OPTIONS LIKE '%ENCRYPTION%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test         | t1         | ENCRYPTION="Y" |
+--------------+------------+----------------+

查询INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES以检索有关与特定架构和表关联的表空间的信息.

1
2
3
4
5
6
mysql> SELECT SPACE, NAME, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE NAME='test/t1';
+-------+---------+------------+
| SPACE | NAME    | SPACE_TYPE |
+-------+---------+------------+
|     3 | test/t1 | Single     |
+-------+---------+------------+

InnoDB表空间加密使用说明

  • 使用ENCRYPTION选项更改现有表空间时,请进行适当规划.使用COPY算法重建该表.不支持INPLACE算法.
  • 如果服务器在正常操作期间退出或停止,建议使用先前配置的相同加密设置重新启动服务器.
  • 当第一个新表空间或现有表空间被加密时,将生成第一个主加密密钥.
  • 主密钥轮换重新加密表空间密钥,但不会更改表空间密钥本身.要更改表空间键,必须禁用并重新启用加密,这是一个重建表的ALGORITHM = COPY操作.
  • 如果使用COMPRESSION和ENCRYPTION选项创建表,则在加密表空间数据之前执行压缩.
  • 如果密钥环数据文件(由keyring_file_data或keyring_encrypted_file_data命名的文件)为空或丢失,则首次执行ALTER INSTANCE ROTATE INNODB MASTER KEY会创建主加密密钥.
  • 卸载keyring_file或keyring_encrypted_file插件不会删除现有的密钥环数据文件.
  • 建议您不要将密钥环数据文件放在与表空间数据文件相同的目录下.
  • 在运行时或重新启动服务器时修改keyring_file_data或keyring_encrypted_file_data设置可能导致以前加密的表空间无法访问，从而导致数据丢失.

InnoDB表空间加密的限制

  • 高级加密标准(AES)是唯一受支持的加密算法.InnoDB表空间加密使用电子密码本(ECB)块加密模式进行表空间密钥加密和密码块链接(CBC)块加密模式进行数据加密.
  • 使用COPY算法更改表的ENCRYPTION属性.不支持INPLACE算法.
  • 仅对每个表的文件表空间支持加密.其他表空间类型(包括通用表空间和系统表空间)不支持加密.
  • 你不能将表从加密的每表表空间移动或复制到不支持加密的表空间类型.
  • 表空间加密仅适用于表空间中的数据.数据未在重做日志,撤消日志或二进制日志中加密.
  • 不允许更改驻留或先前驻留在加密表空间中的表的存储引擎.

发表于 更新于 分类于

14.6.2 索引(Indexes)

14.6.2.1 聚簇和二级索引(Clustered and Secondary Indexes)

每个InnoDB表都有一个称作聚簇索引的特殊索引,存储了行的数据.通常情况下,聚簇索引是主键的代名词.为了从查询,插入和其他数据库操作中获得最好的性能,你必须了解InnoDB如何使用聚簇索引来优化每个表最常见的查找和DML操作.

  • 当你在表上定义了主键,InnoDB用其作为聚簇索引.给你创建的每个表定义一个主键.如果没有逻辑上唯一和非空的列或列的集合,添加一个新的自增列(作为主键),它的值会自动填充.
  • 如果你没有为表定义主键,MySQL会找到第一个唯一索引,其中所有的列键都是非空的并且InnoDB会将其作为聚簇索引.
  • 如果表没有主键或合适的唯一索引,InnoDB会在包含行ID值的合成列内部生成一个名为GEN_CLUST_INDEX的隐藏的聚簇索引.行数据按照InnoDB给这类表分配的ID来排序.行ID是一个6位的字段,随着新行的插入单调递增.因此,行通过ID的排序顺序和物理插入顺序一致.

聚簇索引如何加速查询(How the Clustered Index Speeds Up Queries)

通过聚簇索引访问行很快,因为索引搜索直接指向包含所有行数据的页(page).如果表很大,聚簇索引架构与从不同页面索引记录来存储数据的组织方式相比可以节省磁盘的I/O操作.

二级索引如何关联聚簇索引(How Secondary Indexes Relate to the Clustered Index)

除了聚簇索引之外的索引都被成为二级索引.在InnoDB中,每个二级索引包含行的主键列和二级索引指定的其他列.InnoDB使用此主键来搜索聚簇索引中的行.

如果主键很长,二级索引会使用更多的空间,所以一个短的主键是有好处的.

14.6.2.2 InnoDB索引的物理架构(The Physical Structure of an InnoDB Index)

除了索引空间之外,InnoDB索引使用B树数据结构.空间索引使用R树,是一种用于索引多维数据的特殊数据结构.索引记录存储在B树或R树数据结构的叶节点页面中.默认的索引页大小是16KB.

当在InnoDB聚簇索引中插入一个新记录时,InnoDB会尝试保留页面的1/16用来将来更新和插入索引记录.如果索引记录按序插入(升序或降序),生成的索引页面大约是15/16.如果记录按随机顺序插入,占页面1/2到15/16大小.

InnoDB在创建或重建B树索引时执行批量加载.这种索引创建方法称为排序索引构建(sorted index build). innodb_fill_factor配置选项定义在排序索引构建期间填充每个B树页面的百分比,剩余的空间用于未来的增长需要.空间索引不支持排序索引构建. innodb_fill_factor设置为100会为未来增长的数据保留1/16的页面大小.

如果InnoDB索引页的填充因子低于MERGE_THRESHOLD(默认情况下为50%),InnoDB会尝试收缩索引树来释放页面.MERGE_THRESHOLD设置适用于B树索引和R树索引.

你可以通过在初始化MySQL实例之前设置innodb_page_size配置选项来定义MySQL实例中所有InnoDB表空间的页面大小.定义实例的页面大小后,如果不重新初始化实例,则无法更改它.支持的大小有64KB,32KB,16KB(默认),8KB和4KB.

MySQL5.7添加了对32KB和64KB的支持.

使用指定InnoDB页面大小的MySQL实例无法使用来自使用不同页面大小的实例的数据文件或日志文件.

14.6.2.3 (构建排序索引)Sorted Index Builds

在创建或重构索引的时候,InnoDB使用批量加载来代替一次插入一个记录的方式.这种创建索引的方式被称为排序索引构建.不支持构建索引空间.

索引构建有3个阶段.第一阶段,扫描聚簇索引并且生成索引条目然后添加到排序缓冲区.当排序缓冲区满的时候条目会被排序然后写到一个临时的中间文件中.此过程也被称为运行(run).第二阶段,一个或多个运行写入到临时中间文件的时候会对文件中的全部条目执行归并排序.第三阶段,排序过的条目被插入到B树中.

在引入排序索引构建之前,使用插入API来将条目一条条插入到B树索引中.这个方法设计打开B树游标用来查找插入位置,然后使用乐观插入将条目插入到B树页面中.如果由于页面已满导致插入失败,将会执行一个悲观插入,这会涉及打开B树游标并根据需要拆分和合并B树节点以找到该条目的空间.这种构建索引的”自上而下”方法的缺点是搜索插入位置的成本以及B树节点的常量拆分和合并.

排序索引构建使用自下而上方式来构建索引.使用这种方法,对最右侧叶节点的页面引用保留在B树的每一级上.分配必要B树深度的最右侧叶页,并根据其排序顺序插入条目.一旦叶节点的页面满了,节点指针将附加到父页面,并为下一个插入分配一个兄弟叶页面.此过程将继续到插入所有条目,可能导致插入到根节点级别.分配兄弟节点页面时,将释放对先前固定的叶子节点页面的引用,并且新分配的叶子节点页面将成为最右侧的叶子节点页面和新的默认插入位置.

为未来的索引增长保留B树页面空间

使用 innodb_fill_factor配置B树保留页面空间的百分比来给未来增长的索引预留空间.例如,设置innodb_fill_factor为80可以在排序索引构建的时候保留B树页面空间的20%.此设置适用于B树叶节点和非叶节点页面.但不适用于TEXT和BLOB条目的外部页面.保留的空间总量可能和配置的不完全相同,因为 innodb_fill_factor被解释为示意而不是强制限制.

排序索引构建和全文索引支持

全文索引支持排序索引构建.

排序索引构建和压缩表

对于压缩表,之前的索引创建方法是向压缩和未压缩的页面同时添加条目.当修改日志(表示压缩页面上的可用空间)变满时,压缩页面会被重新压缩.如果压缩因为空间不足失败,页面会被拆分.对于已排序的索引构建,条目只会添加到未压缩的页面上.当一个未压缩的页面变满时会被压缩.自适应填充用于确保在大多数情况下压缩成功,但如果压缩失败,则会拆分页面并再次尝试压缩.此过程将继续到压缩成功.

排序索引构建和redo日志

redo日志在排序索引构建期间是禁用的.相对的,有一个检查点来确保索引构建能够承受崩溃或故障.检查点强制将所有脏页写入磁盘.在排序索引构建期间,会定期发信号通知页面清理线程刷新脏页确保可以快速处理检查点操作.通常,当干净页面的数量低于设定的阈值时,页面清理线程会刷新脏页面.对于排序索引构建,会立即刷新脏页以减少检查点开销并且并行化I/O和CPU活动.

排序索引构建和统计优化(Optimizer Statistics)

排序索引构建可能导致统计优化与以前的索引创建方法生成的统计信息不同.统计数据的差异(不会影响工作负载性能)是由于用于填充索引的算法不同.

14.6.2.4 (InnoDB全文索引)InnoDB FULLTEXT Indexes

在基于文本的列上(CHAR,VARCHAR,或TEXT列)创建全文索引(FULLTEXT index)可以加速包含这些列的查询和DML操作,省略任何被定义为停用词的单词.

全文索引可以定义为CREATE TABLE语句的一部分或使用ALTER TABLE或CREATE INDEX添加到已存在的表中.

全文索引使用MATCH() ... AGAINST语法来执行.

InnoDB全文索引设计(InnoDB Full-Text Index Design)

InnoDB全文索引具有反向索引设计.反向索引存储单词列表,并且为每个单词存储出现该单词的文档列表.为了支持邻近搜索还存储了每个单词的位置信息作为字偏移.

InnoDB全文索引表(InnoDB Full-Text Index Tables)

当创建全文索引的时候,一系列的索引表会被创建,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES
       WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 |   289 |
|      334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 |   290 |
|      335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 |   291 |
|      336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 |   292 |
|      337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 |   293 |
|      338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 |   294 |
|      330 | test/FTS_0000000000000147_BEING_DELETED            |   286 |
|      331 | test/FTS_0000000000000147_BEING_DELETED_CACHE      |   287 |
|      332 | test/FTS_0000000000000147_CONFIG                   |   288 |
|      328 | test/FTS_0000000000000147_DELETED                  |   284 |
|      329 | test/FTS_0000000000000147_DELETED_CACHE            |   285 |
|      327 | test/opening_lines                                 |   283 |
+----------+----------------------------------------------------+-------+

前面的6个表表示反向索引并被成为辅助索引表.当传入的文档被标记时,相应的单词(也称为标记(tokens))与其对应的位置信息和关联的文档ID(DOC_ID)一起被插入索引表中.根据单词的第一个字符的字符集的排序权重对6个索引表中的单词进行完全排序和分区.

反向索引被划分为六个辅助索引表以支持并行索引创建.默认情况下,两个线程对单词和关联数据进行标记,排序和插入索引表.线程的数量使用 innodb_ft_sort_pll_degree来配置.在大表上创建全文索引的时候考虑增加线程的数量.

辅助索引表名称以FTS_为前缀，后缀为INDEX_ *.每个索引表都通过索引表名称中与索引表的table_id匹配的十六进制值与索引表相关联.例如,test/opening_lines表的table_id是327,其十六进制值是0x147.如前面所示,”147”十六进制值出现在与test/opening_lines表关联的索引表名中.

表示全文索引的index_id的十六进制值也出现在辅助索引表名称中.例如,在辅助表名test/FTS_0000000000000147_00000000000001c9_INDEX_1中,16进制的1c9就是10进制的457.opening_lines表上的索引定义可以使用 INFORMATION_SCHEMA.INNODB_SYS_INDEXES表上查询457来识别索引.

1
2
3
4
5
6
7
mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
       WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+

如果主表是在每个表的文件表空间中创建的,则索引表存储在它们自己的表空间中.

上例中显示的其他索引表称为公共索引表，用于删除处理和存储全文索引的内部状态.与为每个全文索引创建的反向索引表不同,这组表对于在特定表上创建的所有全文索引是通用的.

即使删除了全文索引,也会保留公共辅助表.删除全文索引时,将保留为索引创建的FTS_DOC_ID列,因为删除FTS_DOC_ID列将需要重建表.管理FTS_DOC_ID列需要下面的表:

  • FTS_*_DELETED和FTS_*_DELETED_CACHE

包含已删除但尚未从全文索引中删除其数据的文档的文档ID(DOC_ID).FTS_*_DELETED_CACHE是 FTS_*_DELETED在内存中的版本.

  • FTS_*_BEING_DELETED 和 FTS_*_BEING_DELETED_CACHE

包含已删除文档的文档ID(DOC_ID),其数据当前正在从全文索引中删除.FTS_*_BEING_DELETED_CACHE是 FTS_*_BEING_DELETED的内存版本.

  • FTS_*_CONFIG

    存储和全文索引内部状态相关的信息.最重要的是其存储了FTS_SYNCED_DOC_ID,它标识已解析并刷新到磁盘的文档.在崩溃恢复的情况下, FTS_SYNCED_DOC_ID的值于标识尚未刷新到磁盘的文档,以便可以重新解析文档并将其添加回全文索引缓存.

InnoDB全文索引缓存(InnoDB Full-Text Index Cache)

当一篇文档被插入的时候会被识别,相关的单词和关联的数据会被插入到全文索引中.即使对于小型文档,此过程也可能导致在辅助索引表中进行大量小插入,从而使对这些表的并发访问成为争用的焦点.为避免此问题,InnoDB使用全文索引缓存临时缓存对最近插入的行进行的索引表插入.此内存缓存结构保留插入,直到缓存已满,然后批量将它们刷新到磁盘.你可以查询 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE表查看最近插入的行的标记数据.

缓存和批处理刷新避免了对辅助索引表的频繁更新而导致的繁忙的插入和更新时间内的并发访问问题.批处理技术还避免了对同一个单词的多次插入,并最大限度地减少了重复条目.不是单独刷新每个单词,而是将同一个单词的插入合并并作为单个条目刷新到磁盘,从而提高插入效率,同时保持辅助索引表尽可能小.

innodb_ft_cache_size用于配置全文索引缓存大小(基于每个表),这会影响全文索引缓存的刷新频率.你还可以使用innodb_ft_total_cache_size配置来定义全局的全文索引缓存的大小限制.

全文索引缓存存储与辅助索引表相同的信息.但是,全文索引缓存仅缓存最近插入的行的标记后的数据.已经刷到磁盘的数据在查询时不会返回到全文索引缓存中.对辅助索引表中的数据的查询会直接进行,并在返回之前将辅助索引表的结果与全文索引缓存的结果合并.

InnoDB全文索引文档ID和FTS_DOC_ID列

InnoDB使用唯一的文档标识符作为文档ID(DOC_ID)来映射全文索引到文档中出现的单词记录.映射需要索引表中的FTS_DOC_ID列.如果FTS_DOC_ID 列没有被定义的话InnoDB会在全文索引被创建的时候添加一个隐藏的FTS_DOC_ID 列.下面的例子展示了这种行为:

下面是没有包括 FTS_DOC_ID 列的表定义:

1
2
3
4
5
6
mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

当你使用CREATE FULLTEXT INDEX 语句来创建全文索引的时候会返回一个警告,报告InnoDB正在重建FTS_DOC_ID 列.

1
2
3
4
5
6
7
8
9
10
mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

使用 ALTER TABLE向没有 FTS_DOC_ID 列的表添加全文索引的时候也会返回相同的警告.如果你在CREATE TABLE中创建全文索引时没有指定FTS_DOC_ID 列的话,InnoDB会增加一个隐藏的FTS_DOC_ID 列而不产生警告.

在 CREATE TABLE时指定FTS_DOC_ID 列比在已经加载了数据的表上创建全文索引代价要小.如果在加载数据之前在表上定义FTS_DOC_ID列的话,表和索引就不需要重建来添加新列.如果你不关心CREATE FULLTEXT INDEX性能,忽略FTS_DOC_ID列让InnoDB创建它.InnoDB在FTS_DOC_ID列上创建隐藏的FTS_DOC_ID列以及唯一索引(FTS_DOC_ID_INDEX).如果要创建自己的FTS_DOC_ID列,则必须将该列定义为BIGINT UNSIGNED NOT NULL并命名为FTS_DOC_ID(全部大写),如下例所示:

1
2
3
4
5
6
mysql> CREATE TABLE opening_lines (
       FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

如果你选择自己定义FTS_DOC_ID列,管理列以避免出现空值或重复值.无法重用FTS_DOC_ID值,这意味着FTS_DOC_ID值必须不断增加.

(可选)你可以在FTS_DOC_ID列上创建所需的唯一FTS_DOC_ID_INDEX(全部大写).

1
mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果你不创建FTS_DOC_ID_INDEX,InnoDB会自动创建它.

在MySQL 5.7.13之前,最大使用的FTS_DOC_ID值与新的FTS_DOC_ID值之间的允许间隔为10000.在MySQL 5.7.13及更高版本中,允许的间隙为65535.

为避免重建表,删除全文索引时将保留FTS_DOC_ID列.

InnoDB全文索引删除处理(InnoDB Full-Text Index Deletion Handling)

删除具有全文索引列的记录可能会导致辅助索引表中出现大量小删除,使这些表的并发访问成为性能瓶颈.为避免此问题,每当从索引表中删除记录时,已删除文档的文档ID(DOC_ID)将记录在特殊的FTS_*_DELETED表中,并且索引记录保留在全文索引中.在返回查询结果之前,FTS _ * _ DELETED表中的信息用于过滤掉已删除的文档ID.这种设计的好处是删除快速且廉价.缺点是删除记录后索引的大小不会立即减少.要删除已删除记录的全文索引条目,请使用innodb_optimize_fulltext_only = ON在索引表上运行OPTIMIZE TABLE以重建全文索引.

InnoDB全文索引事务处理(InnoDB全文索引事务处理)

由于其缓存和批处理行为,InnoDB 全文索引具有特殊的事务处理特性. 具体来说,全文索引的更新和插入在事务提交时处理,这意味着全文搜索只能看到提交的数据.以下示例演示了此行为.全文搜索仅在提交插入的行后返回结果.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

mysql> BEGIN;

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
       ('Call me Ishmael.','Herman Melville','Moby-Dick'),
       ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
       ('I am an invisible man.','Ralph Ellison','Invisible Man'),
       ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
       ('It was love at first sight.','Joseph Heller','Catch-22'),
       ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
       ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
       ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> COMMIT;

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

InnoDB全文索引监控

您可以通过查询以下INFORMATION_SCHEMA表来监视和检查InnoDB 全文索引

  • INNODB_FT_CONFIG
  • INNODB_FT_INDEX_TABLE
  • INNODB_FT_INDEX_CACHE
  • INNODB_FT_DEFAULT_STOPWORD
  • INNODB_FT_DELETED
  • INNODB_FT_BEING_DELETED

你还可以通过查询INNODB_SYS_INDEXES和INNODB_SYS_TABLES查看全文索引和表的基本信息.

0%