mysql5.6 innodb存储引擎

发表于 | 分类于

官方文档：https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html

目录

14.1 InnoDB简介

14.2 InnoDB和ACID模型

14.3 InnoDB多版本

14.4 InnoDB架构

14.5 InnoDB内存结构

14.6 InnoDB磁盘结构

14.7 InnoDB锁定和事务模型

14.8 InnoDB配置

14.9 InnoDB表压缩

14.10 InnoDB文件格式管理

14.11 InnoDB行格式

14.12 InnoDB磁盘I / O和文件空间管理

14.13 InnoDB和在线DDL

14.14 InnoDB启动选项和系统变量

14.15 InnoDB INFORMATION_SCHEMA表

14.16 InnoDB与MySQL性能架构的集成

14.17 InnoDB监视器

14.18 InnoDB备份和恢复

14.19 InnoDB和MySQL复制

14.20 InnoDB memcached插件

14.21 InnoDB故障排除

14.22 InnoDB限制

14.23 InnoDB的限制和限制

14.1 InnoDB简介

InnoDB是一种兼顾了高可靠性和高性能的通用存储引擎。在MySQL 5.6中，InnoDB是默认的MySQL存储引擎。除非您配置了其他默认存储引擎，否则发出CREATE TABLE不带ENGINE= 子句的语句将创建一个InnoDB表。

InnoDB 包括适用于MySQL 5.1的InnoDB插件的所有功能，以及特定于MySQL 5.5及更高版本的新功能。

InnoDB的主要优势

表14.1 InnoDB存储引擎功能

特征 支持
B树索引 是
备份/时间点恢复（在服务器中而不是在存储引擎中实现。） 是
集群数据库支持 没有
聚集索引 是
压缩数据 是
资料快取 是
加密数据 是（通过加密功能在服务器中实现；在MySQL 5.7和更高版本中，支持静态数据表空间加密。）
外键支持 是
全文搜索索引 是（MySQL 5.6和更高版本提供InnoDB对FULLTEXT索引的支持。）
地理空间数据类型支持 是
地理空间索引支持 是（MySQL 5.7和更高版本提供InnoDB对地理空间索引的支持。）
哈希索引 否（InnoDB在内部将哈希索引用于其自适应哈希索引功能。）
索引缓存 是
锁定粒度 行
MVCC 是
复制支持（在服务器中而不是在存储引擎中实现。） 是
储存限制 64TB
T树索引 没有
交易次数 是
更新数据字典的统计信息 是

要将其功能InnoDB与MySQL随附的其他存储引擎进行比较，请参阅第15章，备用存储引擎中的“ 存储引擎功能”表 。

Table 15.1 Storage Engines Feature Summary

Feature MyISAM Memory InnoDB Archive NDB
B-tree indexes Yes Yes Yes No No
Backup/point-in-time recovery (note 1) Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Clustered indexes No No Yes No No
Compressed data Yes (note 2) No Yes Yes No
Data caches No N/A Yes No Yes
Encrypted data Yes (note 3) Yes (note 3) Yes (note 4) Yes (note 3) Yes (note 3)
Foreign key support No No Yes No Yes (note 5)
Full-text search indexes Yes No Yes (note 6) No No
Geospatial data type support Yes No Yes Yes Yes
Geospatial indexing support Yes No Yes (note 7) No No
Hash indexes No Yes No (note 8) No Yes
Index caches Yes N/A Yes No Yes
Locking granularity Table Table Row Row Row
MVCC No No Yes No No
Replication support (note 1) Yes Limited (note 9) Yes Yes Yes
Storage limits 256TB RAM 64TB None 384EB
T-tree indexes No No No No Yes
Transactions No No Yes No Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes

14.2 InnoDB和ACID模型

该ACID模式是一组数据库设计原则强调的是，对于业务数据和关键任务应用重要的可靠性方面。MySQL包含诸如InnoDB存储引擎严格遵循ACID模型，因此数据不会损坏，结果也不会因软件崩溃和硬件故障等异常情况而失真。当您依赖于ACID的功能时，您无需重新发明一致性检查和崩溃恢复机制。如果您有其他软件保护措施，超可靠的硬件或可以容忍少量数据丢失或不一致的应用程序，则可以调整MySQL设置以牺牲一些ACID可靠性，以获得更高的性能或吞吐量。

以下各节讨论MySQL功能（尤其是InnoDB存储引擎）如何 与ACID模型的类别进行交互：

  • 答：原子性。
  • C：一致性。
  • 我：：隔离。
  • D：耐久性。

原子性

ACID模型 的原子性方面主要涉及InnoDB 事务。相关的MySQL功能包括：

  • 自动提交设置。
  • COMMIT 声明。
  • ROLLBACK 声明。
  • INFORMATION_SCHEMA 表中的 操作数据。

一致性

ACID模型 的一致性方面主要涉及内部InnoDB处理，以防止数据崩溃。相关的MySQL功能包括：

隔离

ACID模型 的隔离方面主要涉及InnoDB 事务，尤其是适用于每个事务的隔离级别。相关的MySQL功能包括：

  • 自动提交设置。
  • SET ISOLATION LEVEL 声明。
  • InnoDB 锁定 的底层细节。在性能调整期间，您可以通过INFORMATION_SCHEMA表格查看这些详细信息 。

耐用性

ACID模型 的持久性方面涉及与特定硬件配置交互的MySQL软件功能。由于取决于您的CPU，网络和存储设备的功能的可能性很多，因此为具体的准则提供最复杂的方面。（这些准则可能采取购买“ 新硬件 ”的形式 。）相关的MySQL功能包括：

  • InnoDB doublewrite buffer，由innodb_doublewrite 配置选项打开和关闭 。
  • 配置选项 innodb_flush_log_at_trx_commit。
  • 配置选项 sync_binlog。
  • 配置选项 innodb_file_per_table。
  • 存储设备（例如磁盘驱动器，SSD或RAID阵列）中的写缓冲区。
  • 存储设备中由电池支持的缓存。
  • 用来运行MySQL的操作系统，特别是它对fsync()系统调用的支持。
  • 不间断电源（UPS）保护运行MySQL服务器并存储MySQL数据的所有计算机服务器和存储设备的电源。
  • 您的备份策略，例如备份的频率和类型以及备份保留期。
  • 对于分布式或托管数据应用程序，MySQL服务器的硬件所在的数据中心的特定特性，以及数据中心之间的网络连接。

14.3 InnoDB MVCC

InnoDB是一个 多版本的存储引擎：它保留有关已更改行的旧版本的信息，以支持诸如并发和回滚之类的事务功能 。此信息存储在表空间中的数据结构中，该数据结构称为 回滚段（在Oracle中类似的数据结构之后）。InnoDB 使用回滚段中的信息来执行事务回滚中所需的撤消操作。它还使用该信息来构建行的早期版本，以实现 一致的读取。

在内部，InnoDB向数据库中存储的每一行添加三个字段。6个字节的DB_TRX_ID字段表示插入或更新该行的最后一个事务的事务标识符。同样，删除在内部被视为更新，在该更新中，行中的特殊位被设置为将其标记为已删除。每行还包含一个7字节的 DB_ROLL_PTR字段，称为滚动指针。回滚指针指向写入回滚段的Undo Log记录。如果行已更新，则Undo Log记录将包含在更新行之前重建行内容所必需的信息。一个6字节的DB_ROW_ID字段包含一个行ID，该行ID随着插入新行而单调增加。如果 InnoDB自动生成聚集索引，该索引包含行ID值。否则，该 DB_ROW_ID列不会出现在任何索引中。

回滚段中的Undo Log分为插入和更新Undo Log。插入Undo Log仅在事务回滚时才需要，并且在事务提交后可以立即将其丢弃。更新Undo Log也用于一致的读取中，但是只有在不存在为其InnoDB分配了快照的事务（ 一致的读取可能需要更新Undo Log中的信息来构建数据库的早期版本）后，才可以将其删除行。

定期提交您的事务，包括仅发出一致读取的事务。否则， InnoDB将无法丢弃更新Undo Log中的数据，并且回滚段可能会变得太大而填满表空间。

回滚段中的Undo Log记录的物理大小通常小于相应的插入或更新的行。您可以使用此信息来计算回滚段所需的空间。

在InnoDB多版本方案中，当您使用SQL语句删除行时，并不会立即将其从数据库中物理删除。InnoDB仅在丢弃为删除而编写的更新Undo Log记录时，才物理删除相应的行及其索引记录。此删除操作称为purge，它非常快，通常花费与执行删除操作的SQL语句相同的时间顺序。

如果您以大约相同的速率在表中以较小的批次插入和删除行，则由于所有“ 死 ”行，清除线程可能开始滞后并且表可能会变得越来越大 ，从而使所有内容都受磁盘约束慢。在这种情况下，请限制新行的操作，并通过调整innodb_max_purge_lag系统变量来向清除线程分配更多资源 。有关更多信息，请参见第14.14节“ InnoDB启动选项和系统变量”。

多版本索引和二级索引

InnoDB多版本并发控制（MVCC）对二级索引的处理方式不同于聚簇索引。聚簇索引中的记录将就地更新，其隐藏的系统列指向Undo Log条目，可以从中重建记录的早期版本。与聚簇索引记录不同，辅助索引记录不包含隐藏的系统列，也不会就地更新。

更新二级索引列时，将对旧的二级索引记录进行删除标记，插入新记录，并最终清除带有删除标记的记录。当二级索引记录被删除标记或二级索引页被较新的事务更新时，InnoDB在聚集索引中查找数据库记录。在聚集索引中，DB_TRX_ID检查记录的记录，如果在启动读取事务后修改了记录，则从Undo Log中检索记录的正确版本。

如果二级索引记录被标记为删除或二级索引页被更新的事务更新， 则不使用覆盖索引技术。而不是从索引结构中返回值，而是InnoDB在聚集索引中查找记录。

但是，如果启用了 索引条件下推（ICP）优化，并且WHERE只能使用索引中的字段来评估部分条件，则MySQL服务器仍会将WHERE条件的这一部分下推到存储引擎，并使用指数。如果找不到匹配的记录，则避免聚集索引查找。如果找到了匹配的记录，即使在删除标记的记录中，也要在 InnoDB聚簇索引中查找记录。


14.4 InnoDB架构

下图显示了构成InnoDB存储引擎体系结构的内存中和磁盘上的结构。有关每种结构的信息，请参见 第14.5节“ InnoDB内存结构”和 第14.6节“ InnoDB磁盘结构”。

图14.1 InnoDB架构


14.5 InnoDB内存结构

14.5.1 Buffer Pool

Buffer Pool是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行 缓存。Buffer Pool允许直接从内存中直接处理经常使用的数据，从而加快了处理速度。在专用服务器上，通常将多达80％的物理内存分配给缓冲池。

为了提高大容量读取操作的效率，Buffer Pool被分为多个页面，这些页面可能包含多个行。为了提高缓存管理的效率，Buffer Pool被实现为页面的链接列表。使用LRU算法的变体将很少使用的数据从缓存中老化掉 。

知道如何利用缓冲池将经常访问的数据保留在内存中是MySQL优化的重要方面。

缓冲池LRU算法

使用最近最少使用（LRU）算法的变体，将缓冲池作为列表进行管理。当需要空间以将新页面添加到缓冲池时，将驱逐最近使用最少的页面，并将新页面添加到列表的中间。此中点插入策略将列表视为两个子列表：

  • 最前面是最近访问过的新页面（“ 年轻 ”） 的子列表
  • 在末尾，是最近访问的旧页面的子列表

图14.2缓冲池列表

该算法将大量页面保留在新的子列表中。旧的子列表包含较少使用的页面。这些页面是驱逐的候选对象 。

默认情况下，该算法的运行方式如下：

  • 3/8的缓冲池专用于旧的子列表。
  • 列表的中点是新子列表的尾部与旧子列表的头相交的边界。
  • 当InnoDB将页面读入缓冲池时，它首先将其插入中点（旧子列表的头部）。可以读取页面，因为它是用户启动的操作（例如SQL查询）所必需的，或作为的自动执行的预读操作的一部分 InnoDB。
  • 访问旧子列表中的页面 使其变为“ 年轻 ”，将其移至新子列表的头部。如果由于用户启动的操作而需要读取页面，则将立即进行首次访问，并使页面年轻。如果由于预读操作而读取了该页面，则第一次访问不会立即发生，并且在退出该页面之前可能根本不会发生。
  • 随着数据库的运行，通过移至列表的尾部，缓冲池中未被访问的页面将“ 老化 ”。新的和旧的子列表中的页面都会随着其他页面的更新而老化。随着将页面插入中点，旧子列表中的页面也会老化。最终，未使用的页面到达旧子列表的尾部并被逐出。

默认情况下，查询读取的页面会立即移入新的子列表，这意味着它们在缓冲池中的停留时间更长。例如，针对mysqldump操作或SELECT不带WHERE子句的 语句 执行的表扫描可以将大量数据带入缓冲池，并驱逐同等数量的旧数据，即使不再使用新数据也是如此。同样，由预读后台线程加载且仅访问一次的页面将移到新列表的开头。这些情况可能会将常用页面推送到旧的子列表，在此它们会被逐出。有关优化此行为的信息，请参见 第14.8.3.2节“使缓冲池扫描具有抵抗力”和 第14.8.3.3节“配置InnoDB缓冲池预取（预读）”。

InnoDB标准监视器输出在BUFFER POOL AND MEMORY有关缓冲池LRU算法操作的部分中包含几个字段。有关详细信息，请参阅使用InnoDB Standard Monitor监视缓冲池。

缓冲池配置

那么innodb-buffer-pool-size的大小应该设置为什么呢？下面我们就开始谈到这个。

独立服务器

在一个独立的只使用InnoDB引擎的MySQL服务器中，根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的80%。
为什么不是90%或者100%呢？因为其它的东西也需要内存：

  • 每个查询至少需要几K的内存(有时候是几M)
  • 有各种其它内部的MySQL结构和缓存
  • InnoDB有一些结构是不用缓冲池的内存的(字典缓存，文件系统，锁系统和页哈希表等)
  • 也有一些MySQL文件是在OS缓存里的(binary日志，relay日志,innodb事务日志等)
  • 此处，你也必须为操作系统留出些内存

共享服务器

如果你的MySQL服务器与其它应用共享资源，那么上面80%的经验就不那么适用了。
在这样的环境下，设置一个对的数字有点难度。
首先让我们来统计一下InnoDB表的实际占用大小。执行如下查询：

1
2
3
4
5
6
7
8
9
10
11
12
> SELECT engine,
>   count(*) as TABLES,
>   concat(round(sum(table_rows)/1000000,2),'M') rows,
>   concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
>   concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
>   concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
>   round(sum(index_length)/sum(data_length),2) idxfrac
> FROM information_schema.TABLES
> WHERE table_schema not in ('mysql', 'performance_schema', 'information_schema')
> GROUP BY engine
> ORDER BY sum(data_length+index_length) DESC LIMIT 10;
>

这会给出一个参考，让你知道如果你想缓存整个数据集应该为InnoDB缓冲池设置多少内存合适。
不过大多数情况你不需要那样做，你只需要缓存你经常使用的数据集。
设置好之后，我们来看看如何检查InnoDB缓冲池大小是否设置足够。
在终端中，执行如下命令：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> $ mysqladmin ext -ri1 | grep Innodb_buffer_pool_reads
> | Innodb_buffer_pool_reads                 | 1832098003     |
> | Innodb_buffer_pool_reads                 | 595            |
> | Innodb_buffer_pool_reads                 | 915            |
> | Innodb_buffer_pool_reads                 | 734            |
> | Innodb_buffer_pool_reads                 | 622            |
> | Innodb_buffer_pool_reads                 | 710            |
> | Innodb_buffer_pool_reads                 | 664            |
> | Innodb_buffer_pool_reads                 | 987            |
> | Innodb_buffer_pool_reads                 | 1287           |
> | Innodb_buffer_pool_reads                 | 967            |
> | Innodb_buffer_pool_reads                 | 1181           |
> | Innodb_buffer_pool_reads                 | 949            |
>

你所看到的是从硬盘读取数据到缓冲池的次数(每秒)。上面的数据已经相当高了(幸运的是，这个服务器的IO设备能处理每秒4000的IO操作)，如果这个是OLTP系统，我建议提高innodb缓冲池的大小和如果必要增加服务器内存。

更改InnoDB缓冲池

最后，介绍如何更改innodb-buffer-pool-size。
如果你运行的是MySQL 5.7，那么非常幸运，你可以在线更改这个变量，只需要以root身份执行如下查询：

1
2
> mysql> SET GLOBAL innodb_buffer_pool_size=size_in_bytes;
>

这还没完，你仍然需要更改my.cnf文件，不过至少你不需要重启服务器让它生效。从mysql的错误日志中我们可以看到它生效的过程：

1
2
3
4
5
6
7
8
> [Note] InnoDB: Resizing buffer pool from 134217728 to 21474836480. (unit=134217728)
> [Note] InnoDB: disabled adaptive hash index.
> [Note] InnoDB: buffer pool 0 : 159 chunks (1302369 blocks) were added.
> [Note] InnoDB: buffer pool 0 : hash tables were resized.
> [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
> [Note] InnoDB: Completed to resize buffer pool from 134217728 to 21474836480.
> [Note] InnoDB: Re-enabled adaptive hash index.
>

在更早的mysql版本就需要重启了，所以：

  1. 在my.cnf中设置一个innodb_buffer_pool_size合适的值
  2. 重启mysql服务器

您可以配置缓冲池的各个方面以提高性能。

  • 理想情况下，您可以将Buffer Pool的大小设置为与实际一样大的值，从而为服务器上的其他进程留出足够的内存以运行而不会进行过多的分页。缓冲池越大，就越InnoDB像内存数据库一样，从磁盘读取一次数据，然后在后续读取期间从内存访问数据。使用innodb_buffer_pool_size 配置选项配置缓冲池大小 。
  • 在具有足够内存的64位系统上，可以将缓冲池分成多个部分，以最大程度地减少并发操作之间的内存结构争用。有关详细信息，请参见第14.8.3.1节“配置多个缓冲池实例”。
  • 您可以将频繁访问的数据保留在内存中，而不必考虑操作突然导致的活动高峰，这些操作会将大量不经常访问的数据带入缓冲池。有关详细信息，请参见 第14.8.3.2节“使缓冲池扫描具有抵抗力”。
  • 您可以控制何时以及如何执行预读请求，以异步方式将页面预取到缓冲池中，从而预期很快将需要这些页面。有关详细信息，请参见第14.8.3.3节“配置InnoDB缓冲池预取（预读）”。
  • 您可以控制何时进行后台冲洗，以及是否根据工作负荷动态调整冲洗速率。有关详细信息，请参见 第14.8.3.4节“配置缓冲池刷新”。
  • 您可以配置如何InnoDB保留当前缓冲池状态，以免在服务器重新启动后进行冗长的预热。有关详细信息，请参见 第14.8.3.5节“保存和恢复缓冲池状态”。

使用InnoDB标准监视器监视缓冲池

InnoDB可以使用访问的标准监视器输出， SHOW ENGINE INNODB STATUS提供有关缓冲池操作的度量。缓冲池度量标准位于BUFFER POOL AND MEMORY“ InnoDB标准监视器”输出中的部分，其 外观类似于以下内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 155455
Buffer pool size   131071
Free buffers       92158
Database pages     38770
Old database pages 14271
Modified db pages  619
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 322, created 38448, written 42083
0.00 reads/s, 222.30 creates/s, 159.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 38770, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

下表描述了InnoDB标准监视器报告的缓冲池度量 标准。

注意

InnoDB 标准监视器输出中 提供的每秒平均值基于自InnoDB上次打印标准监视器输出以来经过的时间 。

表14.2 InnoDB缓冲池指标

名称 描述
分配的总内存 为缓冲池分配的总内存（以字节为单位）。
分配了额外的池 为附加池分配的总内存（以字节为单位）。
分配的字典内存 为InnoDB数据字典分配的总内存，以字节为单位。
缓冲池大小 分配给缓冲池的页面总大小（以页为单位）。
可用缓冲区 缓冲池空闲列表的页面总大小。
数据库页面 缓冲池LRU列表的页面总大小。
旧数据库页面 缓冲池旧LRU子列表的页面总大小。
修改的数据库页面 缓冲池中当前修改的页面数。
待读 等待读入缓冲池的缓冲池页面数。
待写LRU 从LRU列表的底部开始写入的缓冲池中的旧脏页数。
等待写入刷新列表 检查点期间要刷新的缓冲池页面数。
待写单页 缓冲池中暂挂的独立页面写入数。
使页面年轻化 缓冲池LRU列表中变年轻的页面总数（已移至“ new ”页面的子列表的开头）。
页面不年轻 缓冲池LRU列表中未设置为年轻的页面总数（保留在“ old ”子列表中但未设置为年轻的页面）。
青少年 每秒平均访问缓冲池LRU列表中的旧页面所导致的页面年轻。有关更多信息，请参见此表后面的注释。
非年轻 每秒平均访问缓冲池LRU列表中的旧页面导致的页面不年轻。有关更多信息，请参见此表后面的注释。
阅读页面 从缓冲池读取的页面总数。
创建页面 在缓冲池中创建的页面总数。
写的页面 从缓冲池写入的页面总数。
读/秒 每秒平均每秒读取的缓冲池页面数。
创建/秒 每秒平均创建的缓冲池页面的每秒数量。
写/秒 每秒平均缓冲池页面写入数。
缓冲池命中率 从缓冲池内存与磁盘存储读取的页面的缓冲池页面命中率。
年青率 页面访问的平均命中率使页面更年轻。有关更多信息，请参见此表后面的注释。
不（成年率） 页面访问未使页面变年轻的平均命中率。有关更多信息，请参见此表后面的注释。
预读页面 预读操作的每秒平均数。
被逐出的页面无权访问 每秒从缓冲池访问而未访问的页面的平均值。
随机预读 随机预读操作的每秒平均数。
伦 缓冲池LRU列表的页面总大小。
unzip_LRU len 缓冲池unzip_LRU列表的页面总大小。
I / O总和 最近50秒内访问的缓冲池LRU列表页面的总数。
I / O电流 已访问的缓冲池LRU列表页面的总数。
I / O解压缩总和 已访问的缓冲池unzip_LRU列表页面的总数。
I / O解压缩 已访问的缓冲池unzip_LRU列表页面的总数。
名称 描述

注意事项：

  • 该youngs/s指标仅适用于旧页面。它基于对页面的访问次数，而不是页面数。可以对给定页面进行多次访问，所有访问都计入在内。如果youngs/s在不进行大扫描时看到非常低的 值，则可能需要减少延迟时间或增加用于旧子列表的缓冲池的百分比。增加百分比会使旧的子列表变大，因此该子列表中的页面需要更长的时间才能移到尾部，这增加了再次访问这些页面并使它们变年轻的可能性。
  • 该non-youngs/s指标仅适用于旧页面。它基于对页面的访问次数，而不是页面数。可以对给定页面进行多次访问，所有访问都计入在内。如果non-youngs/s执行大型表扫描时看不到较高的值（较高的youngs/s 值），请增加延迟值。
  • 该young-making比率说明了对所有缓冲池页面的访问，而不仅仅是访问了旧子列表中的页面。该young-making速率和 not速率通常不会加总到整个缓冲池的命中率。旧子列表中的页面命中会导致页面移动到新子列表，但是新子列表中的页面命中只会导致页面与列表的头部保持一定距离时才移动到列表的头部。
  • not (young-making rate)是由于innodb_old_blocks_time未满足所定义的延迟，或者由于新子列表中的页面点击未导致页面移动到头部而导致页面访问未使页面变年轻的平均点击率 。此速率说明了对所有缓冲池页面的访问，而不仅仅是访问旧子列表中的页面。

缓冲池服务器状态变量和 INNODB_BUFFER_POOL_STATS表提供了许多与InnoDB标准监视器输出中相同的缓冲池度量 标准。有关更多信息，请参见 示例14.10，“查询INNODB_BUFFER_POOL_STATS表”。


14.5.2 Change Buffer

Change Buffer是一种特殊的数据结构，当二级索引页不在缓冲池(Buffer Pool)中时，它们 会缓存这些更改 。当页面通过其他读取操作加载到缓冲池中时，可能由INSERT， UPDATE或 DELETE操作（DML）导致的缓冲更改 将在以后合并。

图14.3Change Buffer

与聚簇索引不同，二级索引通常是非唯一的，并且二级索引中的插入以相对随机的顺序发生。同样，删除和更新可能会影响索引树中不相邻的二级索引页。当稍后通过其他操作将受影响的页读入缓冲池时，合并缓存的更改将避免从磁盘将辅助索引页读入缓冲池所需的大量随机访问I / O。

在系统大部分处于空闲状态或缓慢关闭期间运行的清除操作会定期将更新的索引页写入磁盘。与将每个值立即写入磁盘相比，清除操作可以更有效地为一系列索引值写入磁盘块。

当有许多受影响的行和许多辅助索引要更新时，Change Buffer合并可能需要几个小时。在此期间，磁盘I / O会增加，这可能会导致磁盘绑定查询的速度大大降低。提交事务之后，甚至在服务器关闭并重新启动之后，Change Buffer合并也可能继续发生（ 有关更多信息，请参见第14.21.2节“强制InnoDB恢复”）。

在内存中，Change Buffer占用了缓冲池的一部分。在磁盘上，Change Buffer是系统表空间的一部分，当数据库服务器关闭时，索引更改将存储在其中。

Change Buffer中缓存的数据类型由 innodb_change_buffering变量控制。有关更多信息，请参见 配置变更缓冲。您还可以配置最大Change Buffer大小。有关更多信息，请参见 配置Change Buffer最大大小。

如果索引包含降序索引列或主键包含降序索引列，则辅助索引不支持更改缓冲。

有关Change Buffer的常见问题的答案，请参见第A.16节“ MySQL 5.6 FAQ：InnoDBChange Buffer”。

配置Change Buffer

在表上执行，和 操作时INSERT， 索引列的值（尤其是辅助键的值）通常处于未排序的顺序，需要大量的I / O才能使辅助索引保持最新状态。当相关页面不在 缓冲池中时，Change Buffer将 更改缓存到辅助索引条目 ，从而避免了不立即从磁盘读取页面而避免了昂贵的I / O操作。当页面加载到缓冲池中时，缓冲的更改将合并，更新的页面随后将刷新到磁盘。的UPDATEDELETEInnoDB当服务器接近空闲时以及在缓慢关闭期间，主线程会合并缓冲的更改 。

由于Change Buffer功能可以减少磁盘读写操作，因此它对于受I / O约束的工作负载（例如，具有大量DML操作的应用程序，例如批量插入）最有价值。

但是，Change Buffer占用了缓冲池的一部分，从而减少了可用于缓存数据页的内存。如果工作集几乎适合缓冲池，或者您的表具有相对较少的二级索引，则禁用更改缓冲可能很有用。如果工作数据集完全适合缓冲池，则更改缓冲不会带来额外的开销，因为它仅适用于不在缓冲池中的页面。

您可以InnoDB 使用innodb_change_buffering 配置参数来控制执行更改缓冲 的程度。您可以为插入，删除操作（最初将索引记录标记为删除）和清除操作（物理删除索引记录）启用或禁用缓冲。更新操作是插入和删除的组合。默认 innodb_change_buffering值为 all。

允许的innodb_change_buffering 值包括：

  • all

    默认值：缓冲区插入，删除标记操作和清除。

  • none

    不要缓冲任何操作。

  • inserts

    缓冲区插入操作。

  • deletes

    缓冲区删除标记操作。

  • changes

    缓冲插入和删除标记操作。

  • purges

    缓冲在后台发生的物理删除操作。

您可以innodb_change_buffering在MySQL选项文件（my.cnf或 my.ini）中设置 参数，或使用SET GLOBAL 语句动态更改参数，该 语句需要足够的权限来设置全局系统变量。请参见 第5.1.8.1节“系统变量特权”。更改设置会影响新操作的缓冲。现有缓冲条目的合并不受影响。

配置Change Buffer最大大小

该innodb_change_buffer_max_size 变量允许将Change Buffer的最大大小配置为Buffer Pool总大小的百分比。默认情况下， innodb_change_buffer_max_size设置为25。最大设置为50。

考虑innodb_change_buffer_max_size在具有大量插入，更新和删除活动的MySQL服务器上进行增加 ，其中Change Buffer合并不能跟上新的Change Buffer条目，从而导致Change Buffer达到其最大大小限制。

考虑innodb_change_buffer_max_size在使用静态数据进行报告的MySQL服务器上减少 存储空间，或者Change Buffer消耗的缓冲池共享的内存空间过多，从而导致页面比预期的更快地退出缓冲池。

使用代表性的工作负载测试不同的设置，以确定最佳配置。该 innodb_change_buffer_max_size 设置是动态的，允许在不重新启动服务器的情况下修改设置。

监视Change Buffer

以下选项可用于Change Buffer监视：

  • InnoDB标准监视器输出包括Change Buffer状态信息。要查看监视器数据，请发出该SHOW ENGINE INNODB STATUS语句。

    1
    mysql> SHOW ENGINE INNODB STATUS\G

    Change Buffer状态信息位于INSERT BUFFER AND ADAPTIVE HASH INDEX 标题下， 并显示类似以下内容：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -------------------------------------
    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 4425293, used cells 32, node heap has 1 buffer(s)
    13577.57 hash searches/s, 202.47 non-hash searches/s

    有关更多信息，请参见 第14.17.3节“ InnoDB标准监视器和锁定监视器输出”。

  • 该 INFORMATION_SCHEMA.INNODB_METRICS 表提供了在InnoDB标准监视器输出中找到的大多数数据点 以及其他数据点。要查看Change Buffer度量标准以及每个度量标准的描述，请发出以下查询：

    1
    mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

    有关INNODB_METRICS表用法的信息，请参见 第14.15.6节“ InnoDB INFORMATION_SCHEMA指标表”。

  • 该 INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 表提供有关缓冲池中每个页面的元数据，包括Change Buffer索引和Change Buffer位图页面。Change Buffer页面由标识 PAGE_TYPE。IBUF_INDEX是Change Buffer索引页面IBUF_BITMAP的页面类型，并且 是Change Buffer位图页面的页面类型。

    警告

    查询该INNODB_BUFFER_PAGE 表可能会带来很大的性能开销。为避免影响性能，请重现要在测试实例上调查的问题，然后在测试实例上运行查询。

    例如，您可以查询该 INNODB_BUFFER_PAGE表以确定缓冲池页面总数中所包含的IBUF_INDEX和 的大概数量 IBUF_BITMAP。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
           WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, 
           (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
           (SELECT ((change_buffer_pages/total_pages)*100)) 
           AS change_buffer_page_percentage;
    +---------------------+-------------+-------------------------------+
    | change_buffer_pages | total_pages | change_buffer_page_percentage |
    +---------------------+-------------+-------------------------------+
    |                  25 |        8192 |                        0.3052 |
    +---------------------+-------------+-------------------------------+

    有关该INNODB_BUFFER_PAGE表提供的其他数据的信息 ，请参见 第21.30.1节“ INFORMATION_SCHEMA INNODB_BUFFER_PAGE表”。有关相关用法信息，请参见 第14.15.5节“ InnoDB INFORMATION_SCHEMA缓冲池表”。

  • Performance Schema 提供了Change Buffer互斥锁等待工具，以进行高级性能监视。要查看Change Buffer检测，请发出以下查询：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> SELECT * FROM performance_schema.setup_instruments
           WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
    +-------------------------------------------------------+---------+-------+
    | NAME                                                  | ENABLED | TIMED |
    +-------------------------------------------------------+---------+-------+
    | wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
    +-------------------------------------------------------+---------+-------+

    有关监视InnoDB 互斥锁等待的信息，请参见 第14.16.1节“使用性能模式监视InnoDB Mutex等待”。


14.5.3自适应哈希索引

自适应哈希索引（innodb-adaptive-hash）功能可以InnoDB 在不牺牲事务功能或可靠性的情况下，在工作负载和缓冲池有足够内存的适当组合的系统上，更像是内存数据库。自适应哈希索引功能由innodb_adaptive_hash_index 变量启用 ，或在服务器启动时由禁用 --skip-innodb-adaptive-hash-index。

根据观察到的搜索模式，使用索引关键字的前缀构建哈希索引。该前缀可以是任何长度，并且可能是哈希树索引中仅B树中的某些值出现。哈希索引是根据对经常访问的索引页面的需求而建立的。

如果表几乎完全适合主内存，则散列索引可以通过启用直接查找任何元素的方式来加速查询，从而将索引值转换为某种指针。InnoDB 具有监视索引搜索的机制。如果 InnoDB发现查询可以从构建哈希索引中受益，它会自动这样做。

在某些工作负载下，哈希索引查找的速度大大超过了监视索引查找和维护哈希索引结构的额外工作。在繁重的工作负载（例如多个并发连接）下，访问自适应哈希索引有时可能会成为争用的源。与 LIKE运算符和% 通配符也往往不会受益。对于无法从自适应哈希索引功能中受益的工作负载，将其关闭可减少不必要的性能开销。由于很难预先预测自适应哈希索引功能是否适合特定的系统和工作负载，因此请考虑启用和禁用该功能的基准测试。与早期版本相比，MySQL 5.6中的体系结构更改使其更适合禁用自适应哈希索引功能。

您可以在输出SEMAPHORES部分中 监视自适应哈希索引的使用和争用 SHOW ENGINE INNODB STATUS。如果在中创建的RW锁上有许多线程正在等待btr0sea.c，则禁用自适应哈希索引功能可能很有用。

有关哈希索引的性能特征的信息，请参见第8.3.8节“ B树和哈希索引的比较”。


14.5.4 Log Buffer

日志缓冲区是存储区域，用于保存要写入磁盘上的日志文件的数据。日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。较大的日志缓冲区使大型事务可以运行，而无需在事务提交之前将Redo Log数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。

该 innodb_flush_log_at_trx_commit 变量控制如何将日志缓冲区的内容写入并刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。


14.6 InnoDB磁盘结构

本节介绍InnoDB磁盘上的结构和相关主题。


14.6.1 Tables

14.6.1.1创建InnoDB表

要创建InnoDB表，请使用以下 CREATE TABLE语句。

1
CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

如果将ENGINE=InnoDB 子句InnoDB定义为默认存储引擎（默认情况下为默认引擎），则无需指定该子句。要检查默认存储引擎，请发出以下语句：

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

ENGINE=InnoDB如果计划使用mysqldump或复制CREATE TABLE在没有默认存储引擎的服务器上重播该语句， 则 可能仍使用子句InnoDB。

一个InnoDB表及其索引可以在创建系统表空间或在一个 文件每个表的 表空间。当 innodb_file_per_table启用，这是默认像MySQL 5.6.6的，一 InnoDB台是隐含在一个单独的文件，每个表的表空间中创建。相反，如果 innodb_file_per_table禁用此选项，则会InnoDB在InnoDB系统表空间中隐式创建表。

创建InnoDB表时，MySQL 在MySQL数据目录下的数据库目录中创建一个.frm文件。有关.frm文件的更多信息，请参见 InnoDB表和.frm文件。对于在每个表文件表空间中创建的表，默认情况下，MySQL还在数据库目录中创建一个 .ibd表空间文件。在 InnoDB系统表空间中创建的表在现有ibdata文件中创建，该文件位于MySQL数据目录中。

在内部，InnoDB将每个表的条目添加到InnoDB数据字典中。该条目包括数据库名称。例如，如果t1在test 数据库中创建了table ，则数据库名称的数据字典条目为 'test/t1'。这意味着您可以t1在不同的数据库中创建一个具有相同名称（）的表，并且该表名不会在内部冲突InnoDB。

InnoDB表和.frm文件

MySQL将表的数据字典信息存储在数据库目录中的 .frm文件中。与其他MySQL存储引擎不同， InnoDB它还在系统表空间内的自身内部数据字典中编码有关表的信息。MySQL删除表或数据库时，将删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。您不能InnoDB仅通过移动.frm 文件来在数据库之间移动表。有关移动InnoDB 表的信息，请参见第14.6.1.4节“移动或复制InnoDB表”。

InnoDB表和行格式

默认的行格式的的InnoDB表 Compact。尽管此行格式适合基本实验，但请考虑使用 Dynamic or Compressed 格式来利用InnoDB 诸如表压缩和长列值的有效页外存储等功能。使用这些行格式要求将innodb_file_per_table其启用（MySQL 5.6.6中的默认值），并将其 innodb_file_format设置为 Barracuda：

1
2
3
4
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=barracuda;
CREATE TABLE t3 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;
CREATE TABLE t4 (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;

有关InnoDB行格式的更多信息，请参见第14.11节“ InnoDB行格式”。有关如何确定InnoDB表的行格式以及行格式的物理特性的信息InnoDB ，请参见第14.11节“ InnoDB行格式”。

InnoDB表和主键

始终为表定义一个主键InnoDB，并指定一个或多个满足以下条件的列：

  • 被最重要的查询引用。
  • 永远不会空白。
  • 永远不要有重复的值。
  • 一旦插入，就很少更改值。

例如，在包含有关人员的信息的表中，您不会在其上创建主键，(firstname, lastname)因为一个以上的人员可以具有相同的名称，某些人员的姓氏为空白，有时人们会更改其名称。有这么多的约束，通常没有明显的列集可以用作主键，因此您需要创建一个新的具有数字ID的列，以用作全部或部分主键。您可以声明一个 自动增量列，以便在插入行时自动填写升序值：

1
2
3
4
5
# The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

# The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

尽管在没有定义主键的情况下表可以正常工作，但是主键涉及性能的许多方面，并且对于任何大型或经常使用的表都是至关重要的设计方面。建议您始终在CREATE TABLE 语句中指定主键。如果创建表，加载数据，然后稍后运行 ALTER TABLE以添加主键，则该操作比创建表时定义主键要慢得多。

查看InnoDB表属性

要查看InnoDB表的属性，请发出一条SHOW TABLE STATUS 语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-03-16 16:26:52
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

有关SHOW TABLE STATUS输出的信息，请参见 第13.7.5.37节“ SHOW TABLE STATUS语句”。

InnoDB还可以使用InnoDBInformation Schema系统表查询表属性：

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 42
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 24
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
1 row in set (0.02 sec)

有关更多信息，请参见 第14.15.3节“ InnoDB INFORMATION_SCHEMA系统表”。


14.6.1.5将表从MyISAM转换为InnoDB

如果您具有MyISAM要转换InnoDB为更好的可靠性和可伸缩性的表，请在转换之前查看以下准则和提示。

调整MyISAM和InnoDB的内存使用量

从MyISAM表过渡时，降低key_buffer_size配置选项的值 以释放缓存结果不再需要的内存。增加innodb_buffer_pool_size 配置选项的值，该 选项执行与为InnoDB表分配缓存内存类似的作用。该 InnoDB 缓冲池可以缓存表数据和索引数据，加快了查询，查找并保持查询结果在内存中进行再利用。有关缓冲池大小配置的指导，请参见 第8.12.4.1节“ MySQL如何使用内存”。

在繁忙的服务器上，在关闭查询缓存的情况下运行基准测试。该InnoDB缓冲池提供类似的好处，所以查询缓存可能会不必要地占用内存。有关查询缓存的信息，请参见 第8.10.3节“ MySQL查询缓存”。

处理太长或太短的交易

因为MyISAM表不支持 事务，所以您可能没有过多注意 autocommit配置选项和COMMITand ROLLBACK 语句。这些关键字对于允许多个会话同时读取和写入InnoDB表很重要，从而在写繁重的工作负载中提供了可伸缩性的实质好处。

当事务打开时，系统会保留数据快照，如在事务开始时看到的那样，如果系统在杂散事务保持运行的同时插入，更新和删除数百万行，则可能导致大量开销。因此，请注意避免事务运行时间过长：

  • 如果您正在使用mysql会话进行交互式实验，请务必 COMMIT在完成时（完成更改）或 ROLLBACK（撤消更改）。关闭交互式会话，而不要长时间打开它们，以免意外使事务长时间打开。
  • 确保您的应用程序中的任何错误处理程序也未 ROLLBACK 完成更改或COMMIT 已完成更改。
  • ROLLBACK这是一个相对昂贵的操作，因为 INSERT， UPDATE和 DELETE操作会写入到InnoDB之前的表中 COMMIT，并期望大多数更改都能成功提交，并且很少进行回滚。试验大量数据时，请避免对大量行进行更改，然后回滚这些更改。
  • 当使用一系列INSERT语句加载大量数据时 ，请定期 COMMIT执行结果以避免事务持续数小时。在数据仓库的典型加载操作中，如果出现问题，请截断表（使用TRUNCATE TABLE），然后从头开始，而不是执行操作 ROLLBACK。

前面的技巧可以节省在过长的事务中可能浪费的内存和磁盘空间。当事务短于应有的时间时，问题就在于过多的I / O。对于每个 COMMIT，MySQL确保将每个更改安全地记录到磁盘上，其中涉及一些I / O。

  • 对于InnoDB表格的大多数操作，应使用设置 autocommit=0。从效率的角度看，这样就避免了在发出大量连续的不必要的I / O INSERT， UPDATE或 DELETE语句。从安全角度来看，ROLLBACK 如果您在mysql命令行或应用程序中的异常处理程序中出错，则允许您发布 语句以恢复丢失或乱码的数据。
  • autocommit=1适用于InnoDB表 的时间是运行一系列查询以生成报告或分析统计信息时。在这种情况下，不会存在与COMMIT或 相关的I / O损失ROLLBACK，并且InnoDB可以 自动优化只读工作负载。
  • 如果进行了一系列相关更改，请一次完成所有更改，最后一次 COMMIT完成。例如，如果您将相关的信息插入到多个表中，请COMMIT 在进行所有更改后执行一次。或者，如果您运行许多连续的 INSERT语句，则COMMIT在加载所有数据之后执行一次 ；如果您要执行数百万条 INSERT语句，则可能通过发出COMMIT每万或十万条记录来拆分庞大的事务 ，因此事务不会增长得太大。
  • 请记住，即使是一条SELECT 语句也会打开一个事务，因此在交互式mysql 会话中运行某些报表或调试查询后，请发出a COMMIT 或关闭mysql会话。
处理死锁

您可能会在MySQL错误日志中看到警告消息，该警告消息涉及 “ 死锁 ”或的输出 SHOW ENGINE INNODB STATUS。尽管名称听起来很吓人，但是 对于表来说，死锁并不是一个严重的问题InnoDB，并且通常不需要采取任何纠正措施。当两个事务开始修改多个表时，以不同的顺序访问表时，它们可以达到每个事务都在等待另一个的状态，而任何一个都不能继续进行。MySQL立即检测到这种情况并取消（回滚）“ 较小 ”的事务，从而允许其他事务继续进行。

您的应用程序确实需要错误处理逻辑来重新启动像这样被强制取消的事务。当您重新发出与以前相同的SQL语句时，原始的计时问题不再适用。另一笔交易已经完成，您可以继续进行，或者另一笔交易仍在进行中，您的交易会等到完成为止。

如果不断发生死锁警告，则可以检查应用程序代码以一致的方式对SQL操作进行重新排序，或缩短事务。您可以在innodb_print_all_deadlocks启用该选项的情况下进行测试， 以查看MySQL错误日志中的所有死锁警告，而不仅仅是SHOW ENGINE INNODB STATUS输出中的最后一个警告 。

有关更多信息，请参见第14.7.5节“ InnoDB中的死锁”。

规划存储布局

为了从InnoDB表中获得最佳性能，您可以调整许多与存储布局相关的参数。

当您将MyISAM是大的，经常访问的，并保持至关重要的数据表，调查和考虑innodb_file_per_table， innodb_file_format以及 innodb_page_size配置选项，以及 ROW_FORMAT 和KEY_BLOCK_SIZE条款中的 CREATE TABLE说法。

在初始实验期间，最重要的设置是 innodb_file_per_table。启用此设置后，这是MySQL 5.6.6中的默认设置，新 InnoDB表将在每表文件表 空间中隐式创建 。与InnoDB系统表空间相比，每表文件表空间允许在表被截断或删除时由操作系统回收磁盘空间。每表文件表空间还支持 梭子鱼文件格式和相关功能，例如表压缩，长变长列的有效页外存储和大索引前缀。有关更多信息，请参见 第14.6.3.2节“每表文件表空间”。

转换现有表

要将非InnoDB表转换为使用，请 InnoDB使用ALTER TABLE：

1
ALTER TABLE table_name ENGINE=InnoDB;

警告

不要不转换在MySQL系统表 mysql从数据库MyISAM 到InnoDB表。这是不受支持的操作。如果这样做，MySQL将不会重新启动，直到您从备份中还原旧的系统表或通过重新初始化数据目录来重新生成它们（请参见 第2.10.1节“初始化数据目录”）。

克隆表的结构

您可以制作一个InnoDB表，该表是MyISAM表的克隆，而不是ALTER TABLE用来执行转换，以便在切换之前并排测试新旧表。

创建InnoDB具有相同的列和索引定义的空表。使用看到完整的 语句来使用。将子句更改为 。 SHOW CREATE TABLE *table_name*\GCREATE TABLEENGINE``ENGINE=INNODB

传输现有数据

要将大量数据传输到InnoDB上一节中创建的空 表中，请使用插入行。 INSERT INTO *innodb_table* SELECT * FROM *myisam_table* ORDER BY *primary_key_columns*

您还可以InnoDB 在插入数据后为表创建索引。从历史上看，创建新的二级索引对于InnoDB而言是一项缓慢的操作，但是现在您可以在索引创建步骤中以相对较少的开销加载数据之后创建索引。

如果您UNIQUE对辅助键有限制，则可以通过在导入操作期间暂时关闭唯一性检查来加快表的导入：

1
2
SET unique_checks=0;... import operation ...
SET unique_checks=1;

对于大表，这可以节省磁盘I / O，因为 InnoDB可以使用其 Change Buffer将辅助索引记录批量写入。确保数据不包含重复的密钥。 unique_checks允许但不要求存储引擎忽略重复的密钥。

为了更好地控制插入过程，您可以分段插入大表：

1
2
INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

插入所有记录后，您可以重命名表。

在大表转换期间，增加InnoDB缓冲池的大小 以减少磁盘I / O，最多可占物理内存的80％。您还可以增加InnoDB日志文件的大小。

储存要求

如果打算InnoDB在转换过程中为表中的数据制作几个临时副本， 建议您在每个表文件表空间中创建表，以便在删除表时可以回收磁盘空间。当 innodb_file_per_table 配置选项启用（默认），新创建的 InnoDB表在文件的每个表的表空间隐式创建。

无论您是MyISAM直接转换表还是创建克隆InnoDB表，请确保在此过程中有足够的磁盘空间来容纳旧表和新表。 InnoDB表比MyISAM表需要更多的磁盘空间。 如果ALTER TABLE操作空间不足，则会启动回滚，如果它是磁盘绑定的，则可能要花费数小时。对于插入，InnoDB使用插入缓冲区将二级索引记录合并到批索引中。这样可以节省大量的磁盘I / O。对于回滚，不使用这种机制，回滚所花费的时间可能比插入时间长30倍。

对于失控的回滚，如果数据库中没有有价值的数据，建议您终止数据库进程，而不要等待数百万的磁盘I / O操作完成。有关完整过程，请参见 第14.21.2节“强制InnoDB恢复”。

为每个表定义一个主键

该PRIMARY KEY子句是影响MySQL查询性能以及表和索引空间使用的关键因素。主键唯一地标识表中的一行。表中的每一行都必须具有主键值，并且任何两行都不能具有相同的主键值。

这些是主键的指南，后面有更详细的说明。

  • PRIMARY KEY为每个表 声明一个。通常，它是WHERE查找单行时在子句中引用的最重要的列。
  • PRIMARY KEY在原始CREATE TABLE 语句中 声明该子句，而不是稍后通过一条ALTER TABLE语句添加它 。
  • 仔细选择列及其数据类型。数字列优先于字符列或字符串列。
  • 如果没有其他稳定，唯一，非空的数字列要使用，请考虑使用自动递增列。
  • 如果不确定主键列的值是否可以更改，则自动增量列也是一个不错的选择。更改主键列的值是一项昂贵的操作，可能涉及重新排列表内和每个二级索引内的数据。

考虑将主键添加到尚无主键的任何表中。根据表的最大投影尺寸使用最小的实用数字类型。这可以使每行稍微紧凑一些，从而可以为大型表节省大量空间。如果表具有任何二级索引，则节省的空间将成倍增加 ，因为在每个二级索引条目中都会重复主键值。除了减小磁盘上的数据大小之外，小的主键还使更多数据适合 缓冲池，从而加快了各种操作并提高了并发性。

如果表在某个较长的列（例如）上已经具有主键VARCHAR，请考虑添加一个新的无符号 AUTO_INCREMENT列，并将主键切换到该列，即使查询中未引用该列。这种设计更改可以在二级索引中节省大量空间。您可以将以前的主键列指定为UNIQUE NOT NULL强制执行与PRIMARY KEY子句相同的约束，即防止所有这些列之间出现重复或空值。

如果将相关信息分布在多个表中，则通常每个表的主键使用同一列。例如，人员数据库可能有几个表，每个表都有员工编号的主键。一个销售数据库可能有一些带有客户编号主键的表，而另一些带有订单编号主键的表。因为使用主键的查找非常快，所以您可以为此类表构造有效的联接查询。

如果您PRIMARY KEY完全忽略该子句，MySQL会为您创建一个不可见的子句。它是一个6字节的值，可能比您需要的时间更长，因此浪费了空间。因为它是隐藏的，所以您不能在查询中引用它。

应用程序性能注意事项

与InnoDB等效MyISAM表相比，的可靠性和可伸缩性功能 需要更多的磁盘存储。您可能会略微更改列和索引的定义，以提高空间利用率，减少处理结果集时的I / O和内存消耗，以及更好地利用索引查找来实现更好的查询优化计划。

如果确实为主键设置了数字ID列，请使用该值与任何其他表中的相关值进行交叉引用，尤其是对于联接查询。例如，与其接受一个国家名称作为输入并进行查询来搜索相同的名称，不如进行一次查找以确定国家ID，然后进行其他查询（或单个联接查询）以在多个表中查找相关信息。与其将客户或商品目录号存储为一串数字（可能会用完几个字节），不如将其转换为数字ID以进行存储和查询。4字节无符号 INT列可以索引超过40亿个项目（美国的含义是十亿：十亿）。有关不同整数类型的范围，请参见 第11.2.1节“整数类型（精确值）-INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT”。

了解与InnoDB表关联的文件

InnoDB文件比文件需要更多的照顾和计划MyISAM。


14.6.2 Index

本节涵盖与InnoDB 索引有关的主题。

14.6.2.1聚集索引和二级索引

每个InnoDB表都有一个特殊的索引，称为聚簇索引 ，用于存储行数据。通常，聚簇索引与主键同义 。为了从查询，插入和其他数据库操作中获得最佳性能，您必须了解如何InnoDB使用聚簇索引为每个表优化最常见的查找和DML操作。

  • 在PRIMARY KEY表上定义a 时，InnoDB将其用作聚簇索引。为您创建的每个表定义一个主键。如果没有逻辑唯一且非空的列或列集，请添加一个新的 自动递增 列，其值将自动填充。
  • 如果您没有PRIMARY KEY为表定义，MySQL会UNIQUE在所有键列所在的位置找到第一个索引，NOT NULL并将 InnoDB其用作聚集索引。
  • 如果表没有索引PRIMARY KEY或没有合适的 UNIQUE索引，则在InnoDB 内部生成一个隐藏的聚集索引GEN_CLUST_INDEX，该索引在包含行ID值的合成列上命名 。这些行由InnoDB分配给该表中各行的ID排序 。行ID是一个6字节的字段，随着插入新行而单调增加。因此，按行ID排序的行实际上在插入顺序上。
聚集索引如何加快查询

通过聚集索引访问行是快速的，因为索引搜索直接导致包含所有行数据的页面。如果表很大，则与使用不同于索引记录的页面存储行数据的存储组织相比，聚集索引体系结构通常可以节省磁盘I / O操作。

二级索引如何与聚簇索引相关

除聚集索引之外的所有索引都称为 辅助索引。在中InnoDB，辅助索引中的每个记录都包含该行的主键列以及为辅助索引指定的列。 InnoDB使用此主键值在聚集索引中搜索行。

如果主键较长，则辅助索引将使用更多空间，因此具有短的主键是有利的。

有关利用InnoDB 聚簇索引和二级索引的准则，请参见 第8.3节“优化和索引”。

14.6.2.2 InnoDB索引的物理结构

所有InnoDB索引都是 B树，索引记录存储在树的叶子页中。索引页的默认大小为16KB。

将新记录插入InnoDB 聚集索引时，请 InnoDB尝试使页面的1/16空闲，以备将来插入和更新索引记录。如果按顺序插入索引记录（升序或降序），则所得到的索引页大约为15/16。如果以随机顺序插入记录，则页面将充满1/2到15/16。如果索引页面的填充因子下降到1/2以下，请 InnoDB尝试收缩索引树以释放页面。

您可以通过 在初始化MySQL实例之前设置配置选项来定义MySQL实例中 所有表空间的页面大小。定义实例的页面大小后，如果不重新初始化实例就无法更改它。支持的大小为16KB，8KB和4KB。 InnoDBinnodb_page_size

使用特定InnoDB页面大小的MySQL实例不能使用来自使用不同页面大小的实例的数据文件或日志文件。

14.6.2.3 InnoDB全文索引

FULLTEXT索引是在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的， 以帮助加快对这些列中包含的数据的查询和DML操作，而忽略定义为停用词的任何单词。

甲FULLTEXT指数被定义为一个的一部分 CREATE TABLE说明或使用添加到现有的表ALTER TABLE 或CREATE INDEX。

使用MATCH() ... AGAINST语法执行全文搜索。有关用法信息，请参见 第12.9节“全文搜索功能”。

InnoDB FULLTEXT 本节中的以下主题描述了索引：

InnoDB全文索引设计

InnoDB FULLTEXT索引具有倒排索引设计。倒排索引存储一个单词列表，对于每个单词，存储该单词出现的文档列表。为了支持邻近搜索，每个单词的位置信息也作为字节偏移量存储。

InnoDB全文索引表

创建InnoDB FULLTEXT索引时，将创建一组索引表，如以下示例所示：

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 |
+----------+----------------------------------------------------+-------+

前六个表代表倒排索引，并称为辅助索引表。对传入文档进行标记时，各个单词（也称为 “ 标记 ”）与位置信息和关联的文档ID（DOC_ID）一起插入索引表中。根据单词的第一个字符的字符集排序权重，单词在六个索引表中得到完全排序和分区。

倒排索引分为六个辅助索引表，以支持并行索引创建。默认情况下，两个线程对索引表中的单词和相关数据进行标记，排序和插入。线程数可以使用该innodb_ft_sort_pll_degree 选项配置 。FULLTEXT在大型表上创建索引时，请考虑增加线程数 。

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

表示的十六进制值index_id的的 FULLTEXT索引也出现在辅助索引表名。例如，在辅助表名称中 test/FTS_0000000000000147_00000000000001c9_INDEX_1，十六进制值1c9的十进制值为457。可以通过查询 表中的该值（457）来识别opening_lines表（idx） 上定义的索引INFORMATION_SCHEMA.INNODB_SYS_INDEXES。

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 |
+----------+------+----------+-------+

innodb_file_per_table启用 索引表后，索引表将存储在其自己的表空间中 。如果 innodb_file_per_table禁用此选项，则索引表存储在 InnoDB系统表空间（空间0）中。

注意

由于MySQL 5.6.5中引入的错误，启用索引表后会在InnoDB系统表空间（空间0） 中创建索引表innodb_file_per_table。该错误已在MySQL 5.6.20和MySQL 5.7.5（Bug＃18635485）中修复。

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

即使删除了全文索引，也会保留公用辅助表。删除全文索引时，将 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

    存储有关FULLTEXT索引内部状态的信息 。最重要的是，它存储FTS_SYNCED_DOC_ID，用于标识已解析并刷新到磁盘的文档。在崩溃恢复的情况下， FTS_SYNCED_DOC_ID将使用值来标识尚未刷新到磁盘的文档，以便可以重新解析文档并将其添加回 FULLTEXT索引缓存中。要查看此表中的数据，请查询该 INFORMATION_SCHEMA.INNODB_FT_CONFIG 表。

InnoDB全文索引缓存

插入文档后，将对其进行标记化，并将各个单词和关联的数据插入 FULLTEXT索引。即使对于小型文档，此过程也可能导致在辅助索引表中进行大量小的插入，从而使对这些表的并发访问成为争用点。为避免此问题，请 InnoDB使用FULLTEXT 索引缓存来临时缓存最近插入的行的索引表插入。此内存中的高速缓存结构将保留插入，直到高速缓存已满，然后将其批量刷新到磁盘（至辅助索引表）。您可以查询该 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列，而不会发出警告。

与FTS_DOC_ID在CREATE TABLE已经加载了数据的表上创建全文索引相比，在时间上定义列 要便宜得多。如果FTS_DOC_ID 在加载数据之前在表上定义了列，则不必重建表及其索引即可添加新列。如果您不关心CREATE FULLTEXT INDEX性能，请忽略该FTS_DOC_ID列来 InnoDB为您创建性能 。 InnoDB创建隐藏的 FTS_DOC_ID列以及FTS_DOC_ID_INDEX该FTS_DOC_ID列上的唯一索引（） 。如果要创建自己的FTS_DOC_ID列，则必须将该列定义为BIGINT UNSIGNED NOT NULL并命名为 FTS_DOC_ID （全部大写），如以下示例所示：

注意

该FTS_DOC_ID列不必定义为AUTO_INCREMENT列，但 AUTO_INCREMENT可以使加载数据更加容易。

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_INDEX在FTS_DOC_ID列上创建所需的唯一 （全部大写） 。

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

如果您未创建FTS_DOC_ID_INDEX， InnoDB则会自动创建。

在MySQL 5.6.31之前，最大使用FTS_DOC_ID值与新 FTS_DOC_ID值之间的允许间隙 为10000。在MySQL 5.6.31及更高版本中，允许间隙为65535。

为避免重建表，FTS_DOC_ID 删除全文索引时将保留该列。

InnoDB全文索引删除处理

删除具有全文索引列的记录可能会导致辅助索引表中的许多小删除，从而使对这些表的并发访问成为争用点。为避免此问题，每当从索引表中删除DOC_ID记录时，已删除文档的Document ID（）就会记录在特殊FTS_*_DELETED表中，并且索引记录仍保留在全文索引中。返回查询结果之前，FTS_*_DELETED表格用于过滤删除的文档ID。这种设计的好处是删除既快速又便宜。缺点是删除记录后不会立即减小索引的大小。要删除已删除记录的全文索引条目，请OPTIMIZE TABLE在带有索引的表上 运行innodb_optimize_fulltext_only=ON 以重建全文索引。有关更多信息，请参见 优化InnoDB全文索引。

InnoDB全文索引事务处理

InnoDB FULLTEXT索引由于具有缓存和批处理行为，因此具有特殊的事务处理特性。具体来说，FULLTEXT索引的更新和插入是在事务提交时处理的，这意味着 FULLTEXT搜索只能看到提交的数据。下面的示例演示了此行为。该 FULLTEXT搜索只返回插入的行被提交之后的结果。

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全文索引

您可以InnoDB FULLTEXT通过查询下INFORMATION_SCHEMA 表来监视和检查索引的特殊文本处理方面：

您还可以FULLTEXT通过查询INNODB_SYS_INDEXES和 查看索引和表的 基本信息 INNODB_SYS_TABLES。

有关更多信息，请参见 第14.15.4节“ InnoDB INFORMATION_SCHEMA FULLTEXT索引表”。


14.6.3 Tablespace

本节涵盖与InnoDB 表空间有关的主题。

14.6.3.1系统表空间

系统表空间是InnoDB数据字典，Doublewrite Buffer，Change Buffer和 Undo Log的存储区 。如果在系统表空间中创建表，而不是在每个表文件中创建表，则它也可能包含表和索引数据。

系统表空间可以具有一个或多个数据文件。默认情况下，ibdata1在数据目录中创建一个名为的系统表空间数据文件 。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。有关配置信息，请参阅《 系统表空间数据文件配置》。

本节中以下主题下提供了有关系统表空间的其他信息：

调整系统表空间的大小

本节介绍如何增加或减少系统表空间的大小。

增加系统表空间的大小

增加系统表空间大小的最简单方法是将其配置为自动扩展。为此，请autoextend为设置中的最后一个数据文件指定 属性innodb_data_file_path ，然后重新启动服务器。例如：

1
innodb_data_file_path=ibdata1:10MB:autoextend

当autoextend指定的属性，则数据文件自动大小由8MB增量因为需要空间增加。所述 innodb_autoextend_increment 可变控制增量大小。

您还可以通过添加另一个数据文件来增加系统表空间的大小。为此：

  1. 停止MySQL服务器。
  2. 如果innodb_data_file_path 使用autoextend 属性定义了设置中的最后一个数据文件，则将 其删除，然后修改size属性以反映当前数据文件的大小。要确定要指定的适当数据文件大小，请检查文件系统中的文件大小，并将该值四舍五入为最接近的MB值，其中MB等于1024 x 1024。
  3. 将新的数据文件追加到 innodb_data_file_path 设置中，可以选择指定 autoextend属性。该 autoextend属性只能在最后一个数据文件中指定 innodb_data_file_path 的设置。
  4. 启动MySQL服务器。

例如，此表空间具有一个自动扩展数据文件：

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

假设数据文件随着时间增长到988MB。这是innodb_data_file_path 修改大小属性以反映当前数据文件大小之后，并指定新的50MB自动扩展数据文件之后的设置：

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

添加新数据文件时，请勿指定现有文件名。InnoDB启动服务器时，将创建并初始化新的数据文件。

注意：

您不能通过更改其大小属性来增加现有系统表空间数据文件的大小。例如，在启动服务器时，将innodb_data_file_path设置从更改 ibdata1:10M:autoextend为 ibdata1:12M:autoextend会产生以下错误：

1
2
3
4
> [ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system 
> data file './ibdata1' is of a different size 640 pages (rounded down to MB) than 
> specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!
>

该错误表明现有数据文件大小（以InnoDB页表示）与配置文件中指定的大小不同。如果遇到此错误，请恢复先前的 innodb_data_file_path 设置，然后参考系统表空间调整大小说明。

InnoDB页面大小由innodb_page_size变量定义 。默认值为16384字节。

减少InnoDB系统表空间的大小

您不能从系统表空间中删除数据文件。要减小系统表空间大小，请使用以下过程：

  1. 使用mysqldump转储所有 InnoDB表，包括 模式中的InnoDB表 mysql。使用以下查询标识 模式中的InnoDB表 mysql：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
    +----------------------+
    | table_name           |
    +----------------------+
    | innodb_index_stats   |
    | innodb_table_stats   |
    | slave_master_info    |
    | slave_relay_log_info |
    | slave_worker_info    |
    +----------------------+
    5 rows in set (0.00 sec)

  2. 停止服务器。

  3. 删除所有现有的表空间文件（*.ibd），包括 ibdata和ib_log 文件。不要忘记删除 架构*.ibd 中表的文件mysql。

  4. 删除表的所有.frm文件 InnoDB。

  5. 为新系统表空间配置数据文件。请参阅 系统表空间数据文件配置。

  6. 重新启动服务器。

  7. 导入转储文件。

注意

如果您的数据库仅使用InnoDB 引擎，则转储所有数据库，停止服务器，删除所有数据库和InnoDB日志文件，重新启动服务器以及导入转储文件可能更简单 。

对系统表空间使用原始磁盘分区

您可以将原始磁盘分区用作InnoDB 系统表空间中的数据文件 。此技术可在Windows以及某些Linux和Unix系统上启用无缓冲I / O，而不会增加文件系统开销。在有和没有原始分区的情况下执行测试，以验证此更改是否确实提高了系统性能。

使用原始磁盘分区时，请确保运行MySQL服务器的用户ID具有该分区的读写特权。例如，如果您以mysql用户身份运行服务器 ，则分区必须可由读取和写入mysql。如果使用该--memlock选项运行服务器，则该服务器必须以身份运行root，因此该分区必须可由读取和写入root。

下述步骤涉及选项文件的修改。有关更多信息，请参见第4.2.2.2节“使用选项文件”。

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

  1. When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

  2. 重新启动服务器。InnoDB注意 newraw关键字并初始化新分区。但是，请不要创建或更改任何 InnoDB表。否则，当您下次重新启动服务器时，将InnoDB 重新初始化分区，并且所做的更改将丢失。（为安全起见，InnoDB当newraw指定任何分区时，防止用户修改数据 。）

  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系统上，适用于Linux和Unix系统的相同步骤和随附的准则，只是innodb_data_file_pathWindows上的 设置略有不同。

  1. 创建新的数据文件时，请在newraw该innodb_data_file_path 选项的数据文件大小后立即 指定关键字 ：

    1
    2
    3
    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw

    在//./相当于Windows语法\\.\用于访问物理驱动器。在上面的示例中，D:是分区的驱动器号。

  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表的数据和索引 ，并存储在文件系统中自己的数据文件中。

每节文件表空间特征在本节的以下主题下描述：

每表文件表空间配置

InnoDB默认情况下，在每个表文件表空间中创建表。此行为由innodb_file_per_table变量控制 。禁用在系统表空间中创建表的innodb_file_per_table 原因InnoDB。

的innodb_file_per_table 设置可以在选项文件来指定，或者使用在运行时配置的 SET GLOBAL语句。在运行时更改设置需要足够的特权来设置全局系统变量。请参见第5.1.8.1节“系统变量特权”。

选项文件：

1
2
[mysqld]
innodb_file_per_table=ON

SET GLOBAL在运行时 使用：

1
mysql> SET GLOBAL innodb_file_per_table=ON;

innodb_file_per_table在MySQL 5.6和更高版本中默认启用。如果需要考虑与MySQL早期版本的向后兼容性，则可以考虑禁用它。

警告

禁用 innodb_file_per_table 可防止表复制ALTER TABLE操作将驻留在系统表空间中的表隐式移动到每个表文件表空间。表复制ALTER TABLE操作将使用当前innodb_file_per_table 设置重新创建表。添加或删除二级索引时，此行为不适用，也不适用于 ALTER TABLE使用该INPLACE算法的操作。

每表文件表空间数据文件

.idb在MySQL数据目录下的架构目录 中的数据文件中，将创建一个每表文件表空间 。该.ibd文件以表（*table_name*.ibd）命名。例如，在MySQL数据目录下的目录中test.t1 创建表的数据文件test：

1
2
3
4
5
6
7
8
9
10
mysql> USE test;  
  
mysql> CREATE TABLE t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(100) 
 ) ENGINE = InnoDB; 
 
shell> cd /path/to/mysql/data/test
shell> ls 
t1.ibd

您可以使用该语句的DATA DIRECTORY子句 CREATE TABLE在数据目录外部隐式创建每表文件表空间数据文件。有关更多信息，请参见 第14.6.1.2节“在外部创建表”。

每表文件表空间的优势

每表文件表空间比共享系统表空间具有以下优点。

  • 截断或删除每个表文件表空间中创建的表后，磁盘空间将返回操作系统。截断或删除存储在系统表空间中的表会在系统表空间内创建可用空间，该可用空间仅可用于 InnoDB数据。换句话说，在表被截断或删除后，系统表空间的大小不会缩小。
  • ALTER TABLE 对驻留在系统表空间中的表进行表 复制操作可能会增加表空间占用的磁盘空间量。此类操作可能需要与表中的数据加索引一样多的额外空间。该空间不会像每个表文件表空间那样释放回操作系统。
  • TRUNCATE TABLE 在每个表文件表空间中的表上执行时，性能会更好。
  • 可以在单独的存储设备上创建每表文件表空间数据文件，以进行I / O优化，空间管理或备份。请参见 第14.6.1.2节“在外部创建表”。
  • 您可以从另一个MySQL实例导入位于每个表文件表空间中的表。请参见 第14.6.1.3节“导入InnoDB表”。
  • 在每表文件表空间中创建的表使用梭子鱼文件格式。请参见 第14.10节“ InnoDB文件格式管理”。梭子鱼文件格式启用DYNAMIC与COMPRESSED 行格式关联的功能 。请参见第14.11节“ InnoDB行格式”。
  • 当发生数据损坏，备份或二进制日志不可用或无法重新启动MySQL服务器实例时，存储在单个表空间数据文件中的表可以节省时间并提高成功恢复的机会。
  • 您可以使用MySQL Enterprise Backup快速备份或还原在每表文件表空间中创建的表，而不会中断其他InnoDB 表的使用。这对于具有不同备份计划的表或需要较少备份频率的表很有用。有关详细信息，请参见 进行部分备份。
  • 每表文件表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小。
  • 当通用的Linux文件系统不允许并发写入到一个单一的文件，如系统表空间的数据文件 innodb_flush_method设置为O_DIRECT。因此，结合使用每表文件表空间和此设置时，可能会提高性能。
  • 共享系统表空间中的表包含InnoDB64TB表空间大小限制，该表包含数据字典和Undo Log等其他结构。相比之下，每个表的每个文件表空间都有64TB的大小限制，这为单个表的大小增加提供了足够的空间。
每表文件表空间的缺点

与共享系统表空间相比，每表文件表空间具有以下缺点。

  • 使用每表文件表空间，每个表可能有未使用的空间，只能由同一表的行使用，如果管理不当，则会浪费空间。
  • fsync对每个表的多个数据文件而不是共享系统表空间数据文件执行操作。由于 fsync操作是针对每个文件的，因此无法合并针对多个表的写操作，这可能导致更多的fsync 操作总数。
  • mysqld必须为每个表文件空间保留一个打开的文件句柄，如果每个表文件空间中有许多表，则可能会影响性能。
  • 每个表都有其自己的数据文件时，需要更多的文件描述符。
  • 可能存在更多碎片，这可能会影响 DROP TABLE表扫描性能。但是，如果管理碎片，则每表文件表空间可以提高这些操作的性能。
  • 删除驻留在每个表文件表空间中的表时，将扫描缓冲池，对于大型缓冲池可能要花费几秒钟。使用宽泛的内部锁定执行扫描，这可能会延迟其他操作。
  • 该innodb_autoextend_increment 变量定义用于在自动扩展系统表空间文件变满时扩展其大小的增量大小，该 变量不适用于每表文件表空间文件，无论innodb_autoextend_increment 设置如何，该文件都将自动扩展 。每个表的初始文件表扩展名很少，之后扩展名以4MB为增量。

14.6.3.3 Undo Tablespaces

撤消表空间包含Undo Log，Undo Log是Undo Log记录的集合，其中包含有关如何通过事务撤消对聚集索引记录的最新更改的信息。Undo Log段中包含Undo Log。该 innodb_rollback_segments变量定义分配给每个撤消表空间的回滚段的数量。

Undo Log可以存储在一个或多个撤消表空间中，而不是 系统表空间中。此布局与默认配置不同，在默认配置中，Undo Log位于系统表空间中。Undo Log的I / O模式使撤消表空间成为SSD存储的理想候选者 ，同时将系统表空间保留在硬盘存储上。

所使用的撤消表空间的数量InnoDB 由innodb_undo_tablespaces 配置选项控制 。仅在初始化MySQL实例时才能配置此选项。此后无法更改。

撤消表空间和这些表空间内的各个 段不能删除。

配置撤消表空间

要为MySQL实例配置撤消表空间，请执行以下步骤。假定您在将配置部署到生产系统之前正在测试实例上执行该过程。

重要

撤消表空间的数量只能在初始化MySQL实例时配置，并且在实例生命周期内是固定的。

  1. 使用innodb_undo_directory 配置选项为撤消表空间指定目录位置 。如果未指定目录位置，则在数据目录中创建撤消表空间。

  2. 使用innodb_rollback_segments 配置选项定义回滚段的数量 。从一个相对较低的值开始，然后随着时间的推移逐渐增加它，以检查对性能的影响。默认设置为 innodb_rollback_segments128，这也是最大值。

    一个回退段始终分配给系统表空间。因此，要将回滚段分配给撤消表空间，请设置 innodb_rollback_segments为大于1的值。例如，如果您有两个撤消表空间，则设置 innodb_rollback_segments为3可以为两个撤消表空间中的每一个分配一个回滚段。回滚段以循环方式分布在撤消表空间中。

    配置单独的撤消表空间时，系统表空间中的回滚段将变为非活动状态。

  3. 使用该innodb_undo_tablespaces 选项定义撤消表空间的数量 。在MySQL实例的生命周期中，指定的还原表空间数量是固定的，因此，如果不确定最佳值，请从高端进行估算。

  4. 使用您选择的选项值创建一个新的MySQL测试实例。

  5. 在测试实例上使用实际的工作负载，并使用与生产服务器类似的数据量来测试配置。

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

  7. 定期增加价值 innodb_rollback_segments 并重新运行性能测试，直到I / O性能没有进一步提高。


14.6.4 InnoDB数据字典

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


14.6.5 Doublewrite Buffer

doublewrite缓冲区是位于系统表空间中的存储区域，在此之前，InnoDB将从页面InnoDB缓冲池中刷新的页面写入页面，然后再将页面写入数据文件中的适当位置。仅在刷新页面并将页面写入doublewrite缓冲区之后，才 InnoDB将页面写入其适当位置。如果在页面写入过程中发生操作系统，存储子系统或 mysqld进程崩溃，InnoDB以后可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的良好副本。

尽管数据总是被写入两次，但Doublewrite Buffer并不需要两倍的I / O开销或两倍的I / O操作。fsync() 只需对操作系统进行一次调用，就可以将数据作为一个较大的顺序块写入Doublewrite Buffer本身。

默认情况下，Doublewrite Buffer处于启用状态。要禁用Doublewrite Buffer，请设置 innodb_doublewrite为0。


14.6.6 Redo Log

Redo Log是基于磁盘的数据结构，在崩溃恢复期间用于纠正不完整事务写入的数据。在正常操作期间，Redo Log对更改请求数据的请求进行编码，这些请求是由SQL语句或低级API调用引起的。在初始化期间以及接受连接之前，会自动重播未完成意外关闭之前未完成更新数据文件的修改。有关Redo Log在崩溃恢复中的作用的信息，请参见 第14.18.2节“ InnoDB恢复”。

默认情况下，Redo Log在磁盘上由两个名为ib_logfile0和的 文件物理表示ib_logfile1。MySQL以循环方式写入Redo Log文件。Redo Log中的数据按照受影响的记录进行编码；此数据统称为重做。通过Redo Log的数据传递以不断增加的LSN值表示。

有关相关信息，请参阅 Redo Log文件配置和 第8.5.4节“优化InnoDBRedo Log”。

更改Redo Log文件的数量或大小

要在MySQL 5.6.7或更早版本中更改InnoDB Redo Log文件的数量或大小，请执行以下步骤：

  1. 如果innodb_fast_shutdown设置为2，则设置 innodb_fast_shutdown为1：

    1
    mysql> SET GLOBAL innodb_fast_shutdown = 1;

  2. 确保 innodb_fast_shutdown未将其设置为2后，停止MySQL服务器并确保其关闭且没有错误（以确保日志中没有未完成事务的信息）。

  3. 将旧的日志文件复制到一个安全的地方，以防在关闭过程中出现问题并且需要它们来恢复表空间。

  4. 从日志文件目录中删除旧的日志文件。

  5. 编辑my.cnf以更改日志文件配置。

  6. 再次启动MySQL服务器。mysqld发现InnoDB启动时不存在任何日志文件，并创建了新的日志文件。

该innodb_fast_shutdown 改变的数量或大小时设置不再是相关的InnoDB日志文件。此外，尽管您可能仍希望将旧日志文件复制到一个安全的地方作为备份，但是不再需要删除旧日志文件。要更改InnoDB日志文件的数量或大小 ，请执行以下步骤：

  1. 停止MySQL服务器，并确保它关闭且没有错误。
  2. 编辑my.cnf以更改日志文件配置。要更改日志文件的大小，请配置 innodb_log_file_size。要增加日志文件的数量，请配置 innodb_log_files_in_group。
  3. 再次启动MySQL服务器。

如果InnoDB检测到 innodb_log_file_size与Redo Log文件大小不同，它将编写日志检查点，关闭并删除旧的日志文件，以请求的大小创建新的日志文件，然后打开新的日志文件。

组提交以Redo Log刷新

InnoDB像任何其他 符合ACID的数据库引擎一样，在提交事务之前刷新事务的Redo Log。InnoDB 使用组提交 功能将多个此类刷新请求分组在一起，以避免每次提交都进行一次刷新。使用组提交， InnoDB可以对日志文件进行一次写入操作，以对大约同时提交的多个用户事务执行提交操作，从而显着提高了吞吐量。

有关性能COMMIT和其他事务操作的更多信息 ，请参见第8.5.2节“优化InnoDB事务管理”。


14.6.7 Undo Logs

回滚段

InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式，每个回滚段又有多个undo log slot。

一共128个回滚段，每个回滚段维护了一个段头页，在该page中又划分了1024个slot (TRX_RSEG_N_SLOTS)，每个slot又对应到一个undo log对象，因此理论上InnoDB最多支持 96 * 1024个普通事务。

  1. rseg0预留在系统表空间ibdata中;
  2. rseg 1~rseg 32这32个回滚段存放于临时表的系统表空间中;
  3. rseg33~ 则根据配置存放到独立undo表空间中（如果没有打开独立Undo表空间，则存放于ibdata中）

Undo Log

​ undo Log是与单个读写事务关联的 undo Log 记录的集合。undo Log 记录包含如何 撤消事务对 聚簇索引 记录的最新更改 的信息。如果另一个事务读取原始数据，就需要在 undo Log记录读取。

​ undo Log存在于 undo log segment。默认情况下，undo log segment 实际上是 system tablespace 的一部分 ，但它们也可以驻留在 undo log tablespace 中。有关更多信息，请参见第14.6.3.3节“撤消表空间”。

  1. InnoDB支持128个回滚段，通过参数 innodb_rollback_segments 定义；

  2. 一个 rollback segment 支持的 事务数 取决（ rollback segment 中 undo slots 的数量） 和 （每个事务所需的 undo log 数）；

  3. 一个rollback segment 中 undo slots 的数量 又会因为 innodb_page_size 不同而不同；

InnoDB Page Size rollback segment 中的 undo slots 数（innodb_page_size/ 16）
4096 (4KB) 256
8192 (8KB) 512
16384 (16KB) 1024

以下每种操作类型，一个事务最多可以分配两个 undo Log：

  1. INSERT 操作 在事务提交前只对当前事务可见，因此产生的Undo日志可以在事务提交后直接删除；
  2. UPDATE和 DELETE 操作 产生的Undo日志被归成一类，即update_undo；

undo Log根据需要分配。例如，一个事务执行INSERT， UPDATE 和 DELETE 操作被分配了两个undo Log，仅执行INSERT操作的事务被分配有一个 undo Log；

分配给事务的Undo Log在其持续时间内始终与事务相关；

给定上述因素，可以使用以下公式来估计InnoDB能够支持的并发读写事务数。

注意：在达到 InnoDB 能够支持的并发读写事务数之前，事务可能会遇到并发事务限制错误。当分配给事务的 rollback segment 用完 undo slots 时，就会发生这种情况。在这种情况下，请尝试重新运行事务

  • 如果每个事务执行任一个 insert 或 update 或 delete 操作，InnoDB支持的 并发读-写事务的数目： 

    1
    (innodb_page_size / 16) * innodb_rollback_segments

  • 如果每个事务执行任一个 insert 和（ update 或 delete） 操作，InnoDB支持的 并发读-写事务的数目

    1
    (innodb_page_size / 16 / 2) * innodb_rollback_segments

14.12 InnoDB磁盘I / O和文件空间管理

作为DBA，您必须管理磁盘I / O以防止I / O子系统饱和，并管理磁盘空间以避免填满存储设备。该ACID设计模型需要一定量的I / O可能似乎是多余的，但有助于确保数据的可靠性。在这些限制内， InnoDB尝试优化数据库工作和磁盘文件的组织，以最大程度地减少磁盘I / O的数量。有时，I / O会推迟到数据库不忙之前，或者直到所有内容都需要进入一致状态为止，例如在快速关闭后重新启动数据库期间。

本节讨论默认类型的MySQL表（也称为InnoDB表）对I / O和磁盘空间的主要注意事项 ：

  • 控制用于提高查询性能的后台I / O数量。
  • 启用或禁用可提供额外耐用性的功能，但需要付出额外的I / O代价。
  • 将表组织成许多小文件，一些大文件或两者的组合。
  • 使Redo Log文件的大小与日志文件已满时发生的I / O活动保持平衡。
  • 如何重组表以获得最佳查询性能。

14.12.1 InnoDB磁盘I / O

InnoDB在可能的情况下使用异步磁盘I / O，方法是创建多个线程来处理I / O操作，同时允许其他数据库操作在I / O仍在进行时继续进行。在Linux和Windows平台上，InnoDB使用可用的OS和库函数来执行“ 本机 ”异步I / O。在其他平台上，InnoDB仍然使用I / O线程，但是这些线程实际上可能会等待I / O请求完成。该技术称为“ 模拟 ” 异步I / O。

预读

如果InnoDB可以确定很快有可能需要数据的可能性很大，它将执行预读操作将数据带入缓冲池，以便在内存中可用。对连续数据进行一些大的读取请求可能比对几个分散的小请求进行效率更高。有两种预读启发式InnoDB：

  • 在顺序预读中，如果InnoDB 注意到对表空间中某个段的访问模式是顺序的，则它会将一批数据库页的读取提前发布到I / O系统。
  • 在随机预读中，如果InnoDB注意到表空间中的某些区域似乎正在被完全读入缓冲池，则它将剩余的读操作发布到I / O系统。

有关配置预读启发式方法的信息，请参见 第14.8.3.3节“配置InnoDB缓冲池预取（预读）”。

Doublewrite Buffer

InnoDB使用一种新颖的文件刷新技术，该技术涉及一种称为doublewrite缓冲区的结构 ，默认情况下（innodb_doublewrite=ON）已启用。它增加了崩溃或断电后的恢复安全性，并通过减少fsync()操作需求来提高大多数Unix版本的性能。

在将页面InnoDB 写到数据文件之前，首先将它们写到称为doublewrite缓冲区的连续表空间区域中。仅在完成对双InnoDB 写缓冲区的写入和刷新之后，才将页面写入数据文件中的相应位置。如果在页面写入过程中发生操作系统，存储子系统或 mysqld进程崩溃（导致页面损坏的 情况），InnoDB则以后可以在恢复期间从doublewrite缓冲区中找到该页面的良好副本。

14.12.2文件空间管理

您使用innodb_data_file_path 配置选项在配置文件中定义的数据文件 形成InnoDB 系统表空间。这些文件在逻辑上串联在一起形成系统表空间。没有使用中的条带化。您无法定义表在系统表空间中的分配位置。在新创建的系统表空间中，InnoDB从第一个数据文件开始分配空间。

为避免将所有表和索引存储在系统表空间内所带来的问题，可以启用 innodb_file_per_table 配置选项（默认选项），该选项将每个新创建的表存储在单独的表空间文件中（扩展名为 .ibd）。对于以这种方式存储的表，磁盘文件中的碎片较少，并且当表被截断时，该空间将返回给操作系统，而不是仍由InnoDB在系统表空间中保留。

在整理InnoDB存储引擎的索引的时候，发现B+树是离不开页面page的。所以先整理InnoDB的数据存储结构。

关键词：Pages, Extents, Segments, and Tablespaces

如何存储表
MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在 .frm 文件中，后者存储在 .ibd 文件中，这一节就会对这两种不同的文件分别进行介绍。

.frm

无论在 MySQL 中选择了哪个存储引擎，所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说定义； .frm 文件的格式在不同的平台上都是相同的。

.ibd 文件
InnoDB 中用于存储数据的文件总共有两个部分，一是系统表空间文件，包括 ibdata1、 ibdata2 等文件，其中存储了 InnoDB 系统信息和用户数据库表数据和索引，是所有表公用的。
当打开 innodb_file_per_table 选项时， .ibd 文件就是每一个表独有的表空间，文件存储了当前表的数据和相关的索引数据。

表空间
innodb存储引擎在存储设计上模仿了Oracle的存储结构，其数据是按照表空间进行管理的。新建一个数据库时，innodb存储引擎会初始化一个名为ibdata1 的表空间文件，默认情况下，这个文件会存储所有表的数据，以及我们所熟知但看不到的系统表sys_tables、sys_columns、sys_indexes 、sys_fields等。此外，还会存储用来保证数据完整性的回滚段数据，当然这部分数据在新版本的MySQL中，已经可以通过参数来设置回滚段的存储位置了；

​ innodb存储引擎的设计很灵活，可以通过参数innodb_file_per_table来设置，使得每一个表都对应一个自己的独立表空间文件，而不是存储到公共的ibdata1文件中。独立的表空间文件之存储对应表的B+树数据、索引和插入缓冲等信息，其余信息还是存储在默认表空间中。

这个文件所存储的内容主要就是B+树（索引），一个表可以有多个索引，也就是在一个文件中，可以存储多个索引，而如果一个表没有索引的话，用来存储数据的被称为聚簇索引，也就是说这也是一个索引。最终的结论是，ibd文件存储的就是一个表的所有索引数据。 索引文件有段（segment）,簇（extends）（有的文章翻译为区），页面（page）组成。

关于行记录格式，单独整理一篇。

段（segment
段是表空间文件中的主要组织结构，它是一个逻辑概念，用来管理物理文件，是构成索引、表、回滚段的基本元素。
上图中显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB存储引擎表是索引组织的（index organized），因此数据即索引，索引即数据。那么数据段即为B+树的页节点（上图的leaf node segment），索引段即为B+树的非索引节点（上图的non-leaf node segment）。

创建一个索引（B+树）时会同时创建两个段，分别是内节点段和叶子段，内节点段用来管理（存储）B+树非叶子（页面）的数据，叶子段用来管理（存储）B+树叶子节点的数据；也就是说，在索引数据量一直增长的过程中，所有新的存储空间的申请，都是从“段”这个概念中申请的。

为了介绍索引为目的，所以不展开介绍回滚段等内容。

区/簇（extents）
段是个逻辑概念，innodb引入了簇的概念，在代码中被称为extent；

簇是由64个连续的页组成的，每个页大小为16KB，即每个簇的大小为1MB。簇是构成段的基本元素，一个段由若干个簇构成。一个簇是物理上连续分配的一个段空间，每一个段至少会有一个簇，在创建一个段时会创建一个默认的簇。如果存储数据时，一个簇已经不足以放下更多的数据，此时需要从这个段中分配一个新的簇来存放新的数据。一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

页（page）
InnoDB有页（page）的概念，可以理解为簇的细化。页是InnoDB磁盘管理的最小单位。

常见的页类型有：
数据页（B-tree Node）。
Undo页（Undo Log Page）。
系统页（System Page）。
事务数据页（Transaction system Page）。
插入缓冲位图页（Insert Buffer Bitmap）。
插入缓冲空闲列表页（Insert Buffer Free List）。
未压缩的二进制大对象页（Uncompressed BLOB Page）。
压缩的二进制大对象页（Compressed BLOB Page）。

在逻辑上（页面号都是从小到大连续的）及物理上都是连续的。在向表中插入数据时，如果一个页面已经被写完，系统会从当前簇中分配一个新的空闲页面处理使用，如果当前簇中的64个页面都被分配完，系统会从当前页面所在段中分配一个新的簇，然后再从这个簇中分配一个新的页面来使用；

页面，范围，段和表空间

每个表空间由数据库页面组成 。MySQL实例中的每个表空间都具有相同的页面大小。默认情况下，所有表空间的页面大小均为16KB；您可以innodb_page_size在创建MySQL实例时通过指定选项将页面大小减小到8KB或4KB 。

这些页面分为 大小为1MB的扩展区（64个连续的16KB页面，128个8KB页面或256个4KB页面）。在 “ 文件 ”表空间内被称为 段在 InnoDB。（这些段与回滚段不同， 回滚段实际上包含许多表空间段。）

当段在表空间内增长时， InnoDB将前32页一次分配给它。之后，InnoDB开始将整个扩展区分配给该段。InnoDB 一次最多可以向一个大段中添加4个扩展区，以确保数据的良好顺序。

为中的每个索引分配了两个段 InnoDB。一个用于 B树的非叶节点，另一个用于叶节点。将叶子节点保持在磁盘上连续可以实现更好的顺序I / O操作，因为这些叶子节点包含实际的表数据。

表空间中的某些页面包含其他页面的位图，因此表空间中的某些扩展数据InnoDB块无法整体分配给段，而只能分配给单个页面。

当您通过发出一条SHOW TABLE STATUS语句在表空间中请求可用空间时，请 InnoDB报告表空间中绝对可用的扩展区。InnoDB始终保留一定程度的清理和其他内部用途；这些保留的范围不包括在可用空间中。

从表中删除数据时，将InnoDB 收缩相应的B树索引。释放的空间是否可供其他用户使用取决于删除模式是否将单个页面或扩展区释放到表空间中。删除表或删除表中的所有行可以保证将空间释放给其他用户，但请记住，删除的行仅通过清除操作才能物理删除， 清除操作会在不再需要事务回滚或一致读取后的一段时间自动发生。 。（请参见 第14.3节“ InnoDB多版本”。）

要查看有关表空间的信息，请使用表空间监视器。请参见第14.17节“ InnoDB监视器”。

页面如何与表格行相关

最大行长度略小于数据库页面的一半。例如，对于默认的16KB InnoDB页面大小，最大行长度略小于8KB ，这是由innodb_page_size 配置选项定义的。

如果一行没有超过页面限制的一半，则所有行都将存储在页面内。如果某行超出了半页限制，那么 将选择可变长度列用于外部页外存储，直到该行适合半页之内。可变长度列的外部页外存储因行格式而异：

  • 紧凑和冗余行格式

    当将可变长度列选择用于外部页外存储时，InnoDB将前768个字节本地存储在该行中，其余部分从外部存储到溢出页中。每个此类列都有其自己的溢出页面列表。768字节的前缀附带一个20字节的值，该值存储列的真实长度，并指向存储剩余值的溢出列表。请参见 第14.11节“ InnoDB行格式”。

  • 动态和压缩行格式

    如果将可变长度列选择用于外部页外存储，InnoDB则在行中本地存储一个20字节的指针，其余部分在外部存储到溢出页中。请参见第14.11节“ InnoDB行格式”。

LONGBLOB和 LONGTEXT列必须小于4GB，并且总行长（包括 BLOB和 TEXT列）必须小于4GB。

14.12.3 InnoDB检查点

使你的日志文件非常大时可以减少磁盘I / O的 检查点。通常将日志文件的总大小设置为与缓冲池一样大，甚至更大。尽管从MySQL 5.5开始，过去大型日志文件可能会使崩溃恢复花费大量时间，但崩溃恢复的性能增强使崩溃后可以快速启动使用大型日志文件。（严格来说，对于具有InnoDB插件1.0.7和更高版本的MySQL 5.1，可以实现这种性能改进。对于MySQL 5.5，可以在默认的InnoDB存储引擎中实现此改进。）

检查点处理的工作原理

1
InnoDB`实现称为[模糊检查点](https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_fuzzy_checkpointing)的 [检查点](https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_checkpoint)机制。小批量地从缓冲池中刷新已修改的数据库页面。无需单批刷新缓冲池，这会在检查点过程中中断用户SQL语句的处理。 `InnoDB

在崩溃恢复期间， InnoDB查找写入日志文件的检查点标签。它知道在标签之前对数据库的所有修改都存在于数据库的磁盘映像中。然后InnoDB从检查点向前扫描日志文件，将记录的修改应用于数据库。

14.12.4对表进行碎片整理

随机插入二级索引或从二级索引中删除可能导致索引碎片化。碎片意味着磁盘上索引页的物理顺序与页面上记录的索引顺序不接近，或者64页块中有许多未使用的页已分配给索引。

碎片的一个症状是表占用的空间超过了它应 “占用”的空间。到底有多少是很难确定的。所有InnoDB数据和索引都存储在B树中，它们的填充因子可能在50％到100％之间变化。碎片化的另一个症状是，这样的表扫描花费的时间比“ 应该 ”花费的时间更多 ：

1
SELECT COUNT(*) FROM t WHERE non_indexed_column <> 12345;

前面的查询要求MySQL执行全表扫描，这是大型表的最慢查询类型。

为了加快索引扫描的速度，您可以定期执行 “ null ” ALTER TABLE 操作，这会导致MySQL重建表：

1
ALTER TABLE tbl_name ENGINE=INNODB

在MySQL 5.6.3中，你也可以使用 执行 “ 空 ”，重建表alter操作。以前，该选项已被识别但被忽略。 ALTER TABLE *tbl_name* FORCEFORCE

在MySQL 5.6.17，双方 并 使用 在线DDL。有关更多信息，请参见第14.13节“ InnoDB和在线DDL”。 ALTER TABLE *tbl_name* ENGINE=INNODBALTER TABLE *tbl_name* FORCE

执行碎片整理操作的另一种方法是使用 mysqldump将表转储到文本文件，删除表并从转储文件重新加载它。

如果对索引的插入总是递增的，并且记录仅从末尾删除，则InnoDB 文件空间管理算法可确保不会发生索引中的碎片。

14.12.5使用TRUNCATE TABLE回收磁盘空间

当回收操作系统的磁盘空间 截断的 InnoDB表，该表必须存放在自己的的.ibd文件。若要将表存储在其自己的.ibd 文件中，innodb_file_per_table必须在创建表时启用该表。此外，被截断的表和其他表之间不能有 外键约束，否则 TRUNCATE TABLE操作将失败。但是，允许在同一表的两列之间使用外键约束。

截断表后，将其删除并在新.ibd文件中重新创建 ，并将释放的空间返回给操作系统。这与InnoDB存储在 InnoDB 系统表空间 （在时创建的表 innodb_file_per_table=OFF）中InnoDB的表被截断相反 ，后者在表被截断后只能 使用释放的空间。

截断表并将磁盘空间返回给操作系统的能力还意味着 物理备份可以更小。截断存储在系统表空间中的表（时创建的表 innodb_file_per_table=OFF）在系统表空间中留下未使用空间的块。


14.17 InnoDB监视器

InnoDB监视器提供有关 InnoDB内部状态的信息。此信息对于性能调整很有用。

14.17.1 InnoDB监视器类型

InnoDB监视器 有四种类型：

  • 标准InnoDB监视器显示以下类型的信息：

    • 主后台线程完成的工作
    • 信号量等待
    • 有关最新外键和死锁错误的数据
    • 锁等待交易
    • 活动交易持有的表和记录锁
    • 待处理的I / O操作和相关统计信息
    • 插入缓冲区和自适应哈希索引统计信息
    • Redo Log数据
    • 缓冲池统计
    • 行操作数据

  • 该InnoDB锁监控打印附加锁信息作为标准的一部分 InnoDB监视器输出。

  • 的InnoDB表空间监视器打印在共享表文件的段的列表并验证表分配的数据结构。

  • 该InnoDB表监控打印内容InnoDB的内部数据字典。

    注意

    Tablespace Monitor和Table Monitor已过时，并将在以后的MySQL版本中删除。可以从InnoDB INFORMATION_SCHEMA表中获得表监视器的类似信息 。请参见 第21.30节“ INFORMATION_SCHEMA InnoDB表”。

有关InnoDB表和表空间监视器的其他信息，请参见 Mark Leith：InnoDB表和表空间监视器。

14.17.2启用InnoDB监视器

当InnoDB监视器用于周期性输出使能，InnoDB将输出写入 的mysqld服务器标准错误输出（stderr）每15秒，约。

InnoDB将监视器的输出发送到 内存缓冲区，stderr而不是stdout固定大小的缓冲区，以免潜在的缓冲区溢出。

在Windows上，stderr除非另有配置，否则定向到默认日志文件。如果要将输出定向到控制台窗口而不是错误日志，请从控制台窗口中带有--console选项的命令提示符启动服务器 。有关更多信息，请参见第5.4.2.1节“ Windows上的错误记录”。

在Unix和类似Unix的系统上，stderr除非另行配置，否则通常直接指向终端。有关更多信息，请参见第5.4.2.2节“在Unix和类似Unix的系统上记录错误”。

InnoDB仅在您实际希望查看监视器信息时才应启用监视器，因为输出生成会导致性能降低。另外，如果将监视器输出定向到错误日志，则如果以后忘记通过删除监视器表来禁用监视器，则日志可能会变得很大。

注意

为了帮助进行故障排除，请在某些情况下InnoDB 临时启用标准InnoDBMonitor输出。有关更多信息，请参见 第14.21节“ InnoDB故障排除”。

每个监视器都以包含时间戳和监视器名称的标题开头。例如：

1
2
3
=====================================
2014-10-16 16:28:15 7feee43c5700 INNODB MONITOR OUTPUT
=====================================

标准InnoDB监视器（INNODB MONITOR OUTPUT）的标头也用于锁定监视器，因为锁定监视器在增加额外的锁定信息的情况下会产生相同的输出。

启用InnoDB标准监视器和锁定监视器以进行定期输出可以通过以下两种方法之一执行：

  • 使用CREATE TABLE语句创建InnoDB与监视器关联的特别命名的表。例如，要启用标准InnoDBMonitor，您将创建一个InnoDB名为的表 innodb_monitor。还使用该CREATE TABLE方法启用了表空间监视器和表监视器。

    使用CREATE TABLE语法只是InnoDB 通过MySQL的SQL解析器将命令传递给引擎的一种方法。唯一重要的是表名以及它是一个InnoDB 表。表的结构与创建表的数据库无关。如果关闭服务器，则在重新启动服务器时监视器不会自动重新启动。删除监视器表并发出新的 CREATE TABLE语句以启动监视器。

    注意

    CREATE TABLE启用InnoDB监视器 的方法已过时，将来的发行版中可能会删除该方法。从MySQL 5.6.16开始，您可以使用和 系统变量启用标准InnoDB Monitor和InnoDBLock Monitor 。 innodb_status_outputinnodb_status_output_locks

  • 使用 MySQL 5.6.16中引入的 innodb_status_output和 innodb_status_output_locks系统变量。

该PROCESS权限才能启用或禁用InnoDB监视器。

启用标准InnoDB监视器

要启用标准InnoDB Monitor的定期输出，请创建innodb_monitor表：

1
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

要禁用标准InnoDB监视器，请删除表：

1
DROP TABLE innodb_monitor;

从MySQL 5.6.16开始，您还可以InnoDB通过将innodb_status_output系统变量设置为来启用标准 Monitor ON。

1
SET GLOBAL innodb_status_output=ON;

要禁用标准InnoDB监视器，请设置 innodb_status_output为 OFF。

关闭服务器时，该 innodb_status_output变量将设置为默认OFF值。

启用InnoDB锁定监视器

要启用InnoDB锁定监视器以进行定期输出，请创建innodb_lock_monitor表：

1
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

要禁用InnoDB锁定监视器，请删除表：

1
DROP TABLE innodb_lock_monitor;

从MySQL 5.6.16开始，您还可以InnoDB通过将innodb_status_output_locks系统变量设置为来启用 锁定监视器 ON。与CREATE TABLE启用InnoDB监视器 的 方法一样， 必须同时启用InnoDB标准监视器和 InnoDB锁定监视器，才能 InnoDB定期打印锁定监视器数据：

1
2
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;

关闭服务器时， innodb_status_output和 innodb_status_output_locks 变量将设置为默认OFF值。

要禁用InnoDB锁定监视器，请设置 innodb_status_output_locks为 OFF。设置 innodb_status_output为OFF也将禁用标准InnoDB监视器。

注意

要启用InnoDB锁定监视器的 SHOW ENGINE INNODB STATUS输出，只需启用 innodb_status_output_locks。

获得标准的InnoDB监视器按需输出

作为启用标准InnoDBMonitor定期输出的替代方法 ，您可以InnoDB使用SHOW ENGINE INNODB STATUSSQL语句按需获取标准Monitor输出，该语句将输出提取到客户端程序。如果您使用的是mysql 交互式客户端，则将常用的分号语句终止符替换为，则输出更具可读性\G：

1
mysql> SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUSInnoDB 如果InnoDB启用了锁定监视器，则输出还包括锁定监视器数据。

将标准InnoDB监视器输出定向到状态文件

1
InnoDB`通过`--innodb-status-file`在启动时指定选项，可以启用 标准监视器输出并将其定向到状态文件 。使用此选项时，`InnoDB`将 在数据目录中创建一个名称为文件的文件 ，并大约每15秒将输出写入其中。 `innodb_status.*`pid`*

InnoDB正常关闭服务器后，将删除状态文件。如果发生异常关闭，则可能必须手动删除状态文件。

该--innodb-status-file选项仅供临时使用，因为输出生成会影响性能，并且 文件会随着时间变得很大。 innodb_status.*pid*

启用InnoDB表空间监视器

要为InnoDB表空间监视器启用定期输出，请创建 innodb_tablespace_monitor表：

1
CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

要禁用标准InnoDB表空间监视器，请删除表：

1
DROP TABLE innodb_tablespace_monitor;

注意

Tablespace Monitor已弃用，并将在以后的MySQL版本中删除。

启用InnoDB表监视器

要为InnoDB表监视器启用定期输出，请创建innodb_table_monitor表：

1
CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

要禁用InnoDB表监视器，请删除表：

1
DROP TABLE innodb_table_monitor;

注意

Tablespace Monitor已弃用，并将在以后的MySQL版本中删除。

14.17.3 InnoDB标准监视器和锁定监视器输出

锁定监视器与标准监视器相同，只是它包含其他锁定信息。为任一监视器启用定期输出会打开同一输出流，但是如果启用了锁定监视器，则该流将包含其他信息。例如，如果启用“标准监视器”和“锁定监视器”，则将打开单个输出流。在禁用锁定监视器之前，流中将包含其他锁定信息。

使用该SHOW ENGINE INNODB STATUS语句生成时，Standard Monitor的输出限制为1MB 。此限制不适用于写入服务器标准错误输出（stderr）的输出。

标准监视器输出示例：

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2014-10-17 10:33:50 7f47bcd64700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 167 srv_active, 0 srv_shutdown, 3023 srv_idle
srv_master_thread log flush and writes: 3190
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1040
OS WAIT ARRAY INFO: signal count 959
Mutex spin waits 677, rounds 20336, OS waits 644
RW-shared spins 180, rounds 5400, OS waits 180
RW-excl spins 0, rounds 6420, OS waits 214
Spin rounds per wait: 30.04 mutex, 30.00 RW-shared, 6420.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-10-17 09:51:31 7f47bcde6700 Transaction:
TRANSACTION 436786, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 3
MySQL thread id 1, OS thread handle 0x7f47bcde6700, query id 96 localhost
root update
INSERT INTO child VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6)
Foreign key constraint fails for table `mysql`.`child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
  (`id`)
 ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

But in parent table `mysql`.`parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000006aa26; asc      &;;
 2: len 7; hex 9d000001610137; asc     a 7;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-10-17 09:52:38 7f47bcde6700
*** (1) TRANSACTION:
TRANSACTION 436801, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7f47bcda5700, query id 102 localhost
root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3693 page no 3 n bits 72 index `GEN_CLUST_INDEX` of
table `mysql`.`t` trx id 436801 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
 0: len 6; hex 000000003a00; asc     : ;;
 1: len 6; hex 00000006aa3f; asc      ?;;
 2: len 7; hex ad0000021d0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 436800, ACTIVE 34 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 1, OS thread handle 0x7f47bcde6700, query id 103 localhost
root updating
DELETE FROM t WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3693 page no 3 n bits 72 index `GEN_CLUST_INDEX` of
table `mysql`.`t` trx id 436800 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info
bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
 0: len 6; hex 000000003a00; asc     : ;;
 1: len 6; hex 00000006aa3f; asc      ?;;
 2: len 7; hex ad0000021d0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3693 page no 3 n bits 72 index `GEN_CLUST_INDEX` of
table `mysql`.`t` trx id 436800 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
 0: len 6; hex 000000003a00; asc     : ;;
 1: len 6; hex 00000006aa3f; asc      ?;;
 2: len 7; hex ad0000021d0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 437661
Purge done for trx's n:o < 437657 undo n:o < 0 state: running but
idle History list length 371
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 10, OS thread handle 0x7f47bcd64700, query id 1001 localhost
root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 436801, not started
MySQL thread id 2, OS thread handle 0x7f47bcda5700, query id 102 localhost
root ceaning up
---TRANSACTION 437660, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
43 lock struct(s), heap size 6544, 6474 row lock(s), undo log entries 7124
MySQL thread id 14, OS thread handle 0x7f47bcde6700, query id 1000 localhost
root update
INSERT INTO `dept_emp` VALUES (100258,'d002','1994-03-21','9999-01-01'),
(100259, 'd005','1998-11-04','9999-01-01'),(100259,'d008','1988-02-03',
'1998-11-04'),(100 260,'d005','1998-09-18','9999-01-01'),(100261,'d004',
'1989-03-11','9999-01-01'), (100262,'d008','1996-08-12','9999-01-01'),
(100263,'d002','1998-06-24','1998-10-0 5'),(100264,'d005','1989-11-09',
'9999-01-01'),(100265,'d001','1992-06-27','9999- 01-01'),(100266,'d009',
'1990-09-10','9999-01-01'),(100267,'d009','1992-04-14','9 999-01-01'),
(100268,'d005','1998-05-01','2000-04-07'),(100269,'d007','1994-01-02',
'1999-09-18'),(100269,'d009','1999-09-
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
344 OS file reads, 45666 OS file writes, 4030 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 202.80 writes/s, 48.33 fsyncs/s
-------------------------------------
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 4425293, node heap has 143 buffer(s)
137083.82 hash searches/s, 2495.92 non-hash searches/s
---
LOG
---
Log sequence number 3091027710
Log flushed up to   3090240098
Pages flushed up to 3074432960
Last checkpoint at  3050856266
0 pending log writes, 0 pending chkp writes
1187 log i/o's done, 14.67 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 155455
Buffer pool size   131071
Free buffers       92158
Database pages     38770
Old database pages 14271
Modified db pages  619
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 322, created 38448, written 42083
0.00 reads/s, 222.30 creates/s, 159.47 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 38770, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65536
Free buffers       46120
Database pages     19345
Old database pages 7121
Modified db pages  291
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 163, created 19182, written 21149
0.00 reads/s, 103.48 creates/s, 83.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 19345, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65535
Free buffers       46038
Database pages     19425
Old database pages 7150
Modified db pages  328
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 159, created 19266, written 20934
0.00 reads/s, 118.81 creates/s, 76.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 19425, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 54607, id 139946075744000, state: sleeping
Number of rows inserted 12163964, updated 0, deleted 3, read 4
67807.03 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

标准监视器输出部分

有关标准监视器报告的每个度量的说明，请参阅 《Oracle Enterprise Manager for MySQL数据库用户指南》中的“ 度量”一章 。

  • Status

    本节显示时间戳，监视器名称以及每秒平均值所基于的秒数。秒数是从当前时间到最后一次InnoDB打印监视器输出之间经过的时间。

  • BACKGROUND THREAD

    这些srv_master_thread行显示了由主后台线程完成的工作。

  • SEMAPHORES

    本节报告线程等待信号量的统计信息，并统计线程需要旋转或等待互斥量或rw-lock信号量的次数。等待信号量的大量线程可能是磁盘I / O或内部争用的结果InnoDB。争用可能是由于查询的高度并行性或操作系统线程调度中的问题。innodb_thread_concurrency 在这种情况下，将系统变量设置为 小于默认值可能会有所帮助。该Spin rounds per wait 行显示每个操作系统等待互斥锁的自旋锁轮数。

  • LATEST FOREIGN KEY ERROR

    本节提供有关最新外键约束错误的信息。如果没有发生此类错误，则不存在。内容包括失败的语句以及有关失败的约束以及所引用和引用表的信息。

  • LATEST DETECTED DEADLOCK

    本节提供有关最新死锁的信息。如果没有发生死锁，则不存在。内容显示涉及哪些事务，每个尝试执行的语句，它们拥有和需要的锁，以及哪个事务InnoDB 决定回滚以打破死锁。第14.7.1节“ InnoDB锁定”中说明了本节中报告的锁定模式 。

  • TRANSACTIONS

    如果此部分报告锁定等待，则您的应用程序可能具有锁定争用。输出还可以帮助跟踪事务死锁的原因。

  • FILE I/O

    本节提供有关InnoDB用于执行各种I / O的线程的信息 。其中的前几个专用于常规 InnoDB处理。内容还显示有关挂起的I / O操作的信息和有关I / O性能的统计信息。

    这些线程的数量由innodb_read_io_threads和 innodb_write_io_threads 参数控制 。请参见第14.14节“ InnoDB启动选项和系统变量”。

  • INSERT BUFFER AND ADAPTIVE HASH INDEX

    本部分显示 InnoDB插入缓冲区（也称为Change Buffer）和自适应哈希索引的状态。

    有关相关信息，请参见 第14.5.2节“Change Buffer”和 第14.5.3节“自适应哈希索引”。

  • LOG

    本部分显示有关InnoDB日志的信息 。内容包括当前日志序列号，已将日志刷新到磁盘的距离以及InnoDB上次执行检查点的位置 。（请参见 第14.12.3节“ InnoDB检查点”。）本节还显示有关挂起的写入和写入性能统计信息。

  • BUFFER POOL AND MEMORY

    本节为您提供有关已读和已写页面的统计信息。您可以从这些数字中计算出查询当前正在执行多少个数据文件I / O操作。

    有关缓冲池统计信息的描述，请参阅 使用InnoDB Standard Monitor监视缓冲池。有关缓冲池操作的更多信息，请参见第14.5.1节“缓冲池”。

  • ROW OPERATIONS

    本节显示了主线程在做什么，包括每种行操作的数量和性能比率。

14.17.4 InnoDB表空间监视器输出

注意

该InnoDB表空间监视器是过时的，并且可以在未来版本中删除。

的InnoDB表空间监视器打印关于共享表的文件段的信息，并验证该表空间分配数据结构。表空间监视器未描述使用该innodb_file_per_table选项创建的每表文件表空间 。

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
36
================================================
090408 21:28:09 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14
SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0
SEGMENT id 0 488 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 171 space 0; page 2; res 592 used 481; full ext 7
fragm pages 16; free extents 0; not full extents 2: pages 17
SEGMENT id 0 172 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 173 space 0; page 2; res 96 used 44; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 12
...
SEGMENT id 0 601 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 73
Validating tablespace
Validation ok
---------------------------------------
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================

表空间监视器输出包括有关共享表空间整体的信息，其后是一个列表，其中包含表空间内每个段的细分。

在此示例中，使用默认 页面大小，表空间由每个16KB的数据库页面组成。这些页面分为大小为1MB的扩展区（连续64个页面）。

显示总体表空间信息的输出的初始部分具有以下格式：

1
2
3
4
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845

表空间的总体信息包括以下值：

  • id

    表空间ID。值为0表示共享表空间。

  • size

    当前表空间大小（以页为单位）。

  • free limit

    空闲列表尚未初始化的最小页码。达到或超过此限制的页面是免费的。

  • free extents

    免费范围的数量。

  • not full frag extents， used pages

    未完全填充的片段扩展区数，以及这些扩展区中已分配的页数。

  • full frag extents

    完全完整的片段范围的数量。

  • first seg id not used

    第一个未使用的段ID。

各个细分受众群信息具有以下格式：

1
2
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0

细分信息包括以下值：

  • id：

    细分ID。

  • space， page

    表空间号和段“ inode ”所在的表空间内的页面。表空间号0表示共享表空间。 InnoDB使用inode来跟踪表空间中的段。为段显示的其他字段（id，res等）从inode中的信息派生。

  • res

    为该段分配（保留）的页面数。

  • used

    段正在使用的已分配页面数。

  • full ext

    完全使用的为段分配的扩展区数。

  • fragm pages

    已分配给该段的初始页面数。

  • free extents

    完全未使用的为段分配的扩展区数。

  • not full extents

    为该段分配的部分使用的扩展区数。

  • pages

    在未满范围内使用的页面数。

当一个段增长时，它从一个页面开始，并 InnoDB一次为其分配前几页，最多32页（这是fragm pages 值）。之后，InnoDB分配完整范围。InnoDB一次最多可以向一个大段中添加4个扩展区，以确保数据的良好顺序。

对于前面显示的示例段，它具有32个片段页面，外加2个完整扩展（每个64页），在分配的160个页面中总共使用了160个页面。以下部分包含32个片段页面和一个部分完整的范围，使用14个页面，在分配的96个页面中总共使用了46个页面：

1
2
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14

如果在扩展区分配之后fragm pages某些个别页面已被重新分配，则为其分配扩展区的段的值可能小于32。

14.17.5 InnoDB表监视器输出

注意

该InnoDB表监控已弃用，可能在将来的版本中删除。可以从InnoDB INFORMATION_SCHEMA表中获得类似的信息。请参见 第21.30节“ INFORMATION_SCHEMA InnoDB表”。

该InnoDB表监控打印内容InnoDB的内部数据字典。

输出每个表包含一个部分。在 SYS_FOREIGN与 SYS_FOREIGN_COLS部分是维持大约外键的信息的内部数据字典表。表监视器表和每个用户创建的InnoDB表也有一些部分。假设已在test数据库中创建以下两个表 ：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE parent
(
  par_id    INT NOT NULL,
  fname      CHAR(20),
  lname      CHAR(20),
  PRIMARY KEY (par_id),
  UNIQUE INDEX (lname, fname)
) ENGINE = INNODB;

CREATE TABLE child
(
  par_id      INT NOT NULL,
  child_id    INT NOT NULL,
  name        VARCHAR(40),
  birth       DATE,
  weight      DECIMAL(10,2),
  misc_info   VARCHAR(255),
  last_update TIMESTAMP,
  PRIMARY KEY (par_id, child_id),
  INDEX (name),
  FOREIGN KEY (par_id) REFERENCES parent (par_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = 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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
===========================================
090420 12:09:32 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 1
  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;
           FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
           REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
           N_COLS: DATA_INT len 4;
           DB_ROW_ID: DATA_SYS prtype 256 len 6;
           DB_TRX_ID: DATA_SYS prtype 257 len 6;
  INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3
   root page 46, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
  INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0
   root page 47, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  FOR_NAME ID
  INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0
   root page 48, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 7, indexes 1, appr.rows 1
  COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;
           POS: DATA_INT len 4;
           FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
           REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
           DB_ROW_ID: DATA_SYS prtype 256 len 6;
           DB_TRX_ID: DATA_SYS prtype 257 len 6;
  INDEX: name ID_IND, id 0 14, fields 2/6, uniq 2, type 3
   root page 49, appr.key vals 1, leaf pages 1, size pages 1
   FIELDS:  ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name test/child, id 0 14, columns 10, indexes 2, appr.rows 201
  COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
           child_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
           name: DATA_VARCHAR prtype 524303 len 40;
           birth: DATA_INT DATA_BINARY_TYPE len 3;
           weight: DATA_FIXBINARY DATA_BINARY_TYPE len 5;
           misc_info: DATA_VARCHAR prtype 524303 len 255;
           last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
           DB_ROW_ID: DATA_SYS prtype 256 len 6;
           DB_TRX_ID: DATA_SYS prtype 257 len 6;
  INDEX: name PRIMARY, id 0 17, fields 2/9, uniq 2, type 3
   root page 52, appr.key vals 201, leaf pages 5, size pages 6
   FIELDS:  par_id child_id DB_TRX_ID DB_ROLL_PTR name birth weight misc_info last_update
  INDEX: name name, id 0 18, fields 1/3, uniq 3, type 0
   root page 53, appr.key vals 210, leaf pages 1, size pages 1
   FIELDS:  name par_id child_id
  FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
             REFERENCES test/parent ( par_id )
--------------------------------------
TABLE: name test/innodb_table_monitor, id 0 15, columns 4, indexes 1, appr.rows 0
  COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4;
           DB_ROW_ID: DATA_SYS prtype 256 len 6;
           DB_TRX_ID: DATA_SYS prtype 257 len 6;
  INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, uniq 1, type 1
   root page 193, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i
--------------------------------------
TABLE: name test/parent, id 0 13, columns 6, indexes 2, appr.rows 299
  COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
           fname: DATA_CHAR prtype 524542 len 20;
           lname: DATA_CHAR prtype 524542 len 20;
           DB_ROW_ID: DATA_SYS prtype 256 len 6;
           DB_TRX_ID: DATA_SYS prtype 257 len 6;
  INDEX: name PRIMARY, id 0 15, fields 1/5, uniq 1, type 3
   root page 50, appr.key vals 299, leaf pages 2, size pages 3
   FIELDS:  par_id DB_TRX_ID DB_ROLL_PTR fname lname
  INDEX: name lname, id 0 16, fields 2/3, uniq 2, type 2
   root page 51, appr.key vals 300, leaf pages 1, size pages 1
   FIELDS:  lname fname par_id
  FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
             REFERENCES test/parent ( par_id )
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

对于每个表，表监视器输出包含一个部分，该部分显示有关表的常规信息以及有关其列，索引和外键的特定信息。

每个表的常规信息包括表名（ 内部表以外的格式），其ID，列数和索引数以及大概的行数。 *db_name*/*tbl_name*

COLUMNS表部分 的一部分列出了表中的每一列。每列的信息指示其名称和数据类型特征。某些内部列由添加InnoDB，例如 DB_ROW_ID（行ID）， DB_TRX_ID（事务ID）和 DB_ROLL_PTR（指向回滚/撤消数据的指针）。

  • DATA_*xxx*

    这些符号表示数据类型。给定列可能有多个 符号。 DATA_*xxx*

  • prtype

    列的“ 精确 ”类型。该字段包括诸如列数据类型，字符集代码，可空性，带符号以及是否为二进制字符串之类的信息。此字段在innobase/include/data0type.h源文件中描述 。

  • len

    列长度（以字节为单位）。

INDEX表部分的 每一部分提供一个表索引的名称和特征：

  • name

    索引名称。如果名称为PRIMARY，则索引为主键。如果名称为 GEN_CLUST_INDEX，则索引为聚集索引，如果表定义不包含主键或非NULL 唯一索引，则聚集索引将自动创建。请参见第14.6.2.1节“聚集索引和二级索引”。

  • id

    索引ID。

  • fields

    索引中的字段数， 格式为： *m*/*n*

    • m是用户定义的列数；也就是说，您将在CREATE TABLE 语句的索引定义中看到的列数。
    • n是索引列的总数，包括内部添加的索引列。对于聚集索引，总数包括表定义中的其他列，以及内部添加的所有列。对于二级索引，总数包括主键中不属于二级索引的列。

  • uniq

    足以唯一确定索引值的前导字段数。

  • type

    索引类型。这是一个位字段。例如，1表示聚集索引，2表示唯一索引，因此聚集索引（始终包含唯一值）的type值将为3。type值为0 的索引 既不是聚集也不是唯一。标志值在innobase/include/dict0mem.h源文件中定义 。

  • root page

    索引根页号。

  • appr. key vals

    近似索引基数。

  • leaf pages

    索引中叶子页的大概数量。

  • size pages

    索引中的大约总页数。

  • FIELDS

    索引中字段的名称。对于自动生成的聚集索引，字段列表以内部DB_ROW_ID（行ID）字段开头。 DB_TRX_ID并且 DB_ROLL_PTR总是在内部构成聚簇索引之后，紧跟构成主键的字段。对于二级索引，最终字段是主键中不属于二级索引的字段。

表部分的末尾列出了FOREIGN KEY适用于表的定义。无论表是引用表还是引用表，都会显示此信息。

14.21 InnoDB故障排除

以下一般准则适用于故障排除 InnoDB问题：

  • 当操作失败或怀疑有错误时，请查看MySQL服务器错误日志（请参见第5.4.2节“错误日志”）。 第B.3.1节“服务器错误消息参考”提供了有关InnoDB您可能遇到的一些常见特定错误的故障排除信息 。

  • 如果故障与死锁有关 ，请在innodb_print_all_deadlocks 启用该选项的情况下运行， 以便将有关每个死锁的详细信息打印到MySQL服务器错误日志中。有关死锁的信息，请参见第14.7.5节“ InnoDB中的死锁”。

  • 与InnoDB数据字典有关的问题包括失败的CREATE TABLE 语句（孤立表文件），无法打开 InnoDB文件以及系统找不到指定的路径错误。有关此类问题和错误的信息，请参见 第14.21.3节“对InnoDB数据字典操作进行故障排除”。

  • 在进行故障排除时，通常最好从命令提示符下运行MySQL服务器，而不是通过 mysqld_safe或作为Windows服务运行。然后，您可以查看mysqld打印到控制台的内容，因此可以更好地了解正在发生的情况。在Windows上，使用选项启动mysqld以 --console将输出定向到控制台窗口。

  • 使InnoDB监视器能够获取有关问题的信息（请参见 第14.17节“ InnoDB监视器”）。如果问题与性能有关，或者服务器似乎已挂起，则应启用标准监视器以打印有关的内部状态的信息InnoDB。如果问题出在锁上，请启用锁监视器。如果问题出在创建表或其他数据字典操作中，请启用表监视器以打印InnoDB内部数据字典的内容 。要查看表空间信息，请启用表空间监视器。

    InnoDB``InnoDB在以下情况下临时启用标准 Monitor输出：

    • 长时间的信号灯等待
    • InnoDB 在缓冲池中找不到可用的块
    • 超过67％的缓冲池被锁堆或自适应哈希索引占用

  • 如果怀疑表已损坏，请CHECK TABLE在该表上运行 。

14.21.1对InnoDB I / O问题进行故障排除

InnoDBI / O问题 的疑难解答步骤取决于问题发生的时间：在MySQL服务器启动期间，或在正常操作过程中，由于文件系统级别的问题而导致DML或DDL语句失败。

初始化问题

如果在InnoDB尝试初始化其表空间或日志文件时出现问题，请删除由InnoDB：所有 ibdata文件和所有ib_logfile文件创建的所有 文件。如果已经创建了一些 InnoDB表，还请从MySQL数据库目录中删除.frm这些表的相应 文件，以及.ibd如果使用多个表空间的所有 文件。然后InnoDB再次尝试 创建数据库。为了最简单的故障排除，请从命令提示符启动MySQL服务器，以便了解发生了什么。

运行时问题

如果InnoDB在文件操作过程中显示操作系统错误，通常该问题具有以下解决方案之一：

  • 确保InnoDB数据文件目录和InnoDB日志目录存在。
  • 确保mysqld具有在这些目录中创建文件的访问权限。
  • 确保mysqld可以读取正确的 文件my.cnf或my.ini选项文件，以便它以您指定的选项开头。
  • 确保磁盘未满，并且没有超出任何磁盘配额。
  • 确保为子目录和数据文件指定的名称不冲突。
  • 仔细检查innodb_data_home_dir和 innodb_data_file_path值的语法 。特别是，MAX该innodb_data_file_path选项中的任何值 都是硬限制，超过该限制会导致致命错误。

14.21.2强制InnoDB恢复

要调查数据库页面损坏，您可以使用来从数据库中转储表 SELECT ... INTO OUTFILE。通常，以这种方式获得的大多数数据都是完整的。严重损坏可能导致语句或 后台操作崩溃或断言，甚至导致前滚恢复崩溃。在这种情况下，您可以使用该 选项在阻止后台操作运行的同时强制启动存储引擎，以便转储表。例如，您可以在重新启动服务器之前将以下行添加到选项文件的部分中： SELECT * FROM *tbl_name*``InnoDB``InnoDBinnodb_force_recoveryInnoDB``[mysqld]

1
2
[mysqld]
innodb_force_recovery = 1

有关使用选项文件的信息，请参见 第4.2.2.2节“使用选项文件”。

警告

仅innodb_force_recovery 在紧急情况下设置为大于0的值，以便您可以启动InnoDB和转储表。这样做之前，请确保您拥有数据库的备份副本，以防万一您需要重新创建它。值大于等于4可能会永久损坏数据文件。innodb_force_recovery在数据库的单独物理副本上成功测试设置之后，请仅在生产服务器实例上使用 4或更大的设置。强制InnoDB恢复时，应始终从头开始， innodb_force_recovery=1并根据需要仅逐渐增加该值。

innodb_force_recovery默认情况下为0（正常启动而不强制恢复）。允许的非零值 innodb_force_recovery是1到6。较大的值包括较小值的功能。例如，值3包含值1和2的所有功能。

如果能够转储 innodb_force_recovery值为3或更小的表，则相对安全的是，仅丢失损坏的单个页面上的某些数据。4或更大的值被认为是危险的，因为数据文件可能会永久损坏。值6被认为是过大的，因为数据库页面处于过时状态，这反过来可能会使B树 和其他数据库结构遭受更多破坏。

为了安全起见，请InnoDB防止 INSERT， UPDATE或 DELETE在innodb_force_recovery大于0 时进行操作 。从MySQL 5.6.15开始， 在只读模式下innodb_force_recovery设置4个或更多位置InnoDB。

  • 1 （SRV_FORCE_IGNORE_CORRUPT）

    使服务器即使检测到损坏的页面也可以运行 。尝试 跳过损坏的索引记录和页，这有助于转储表。 SELECT * FROM *tbl_name*

  • 2 （SRV_FORCE_NO_BACKGROUND）

    阻止主线程和任何清除线程运行。如果在清除操作期间发生崩溃，则此恢复值可防止崩溃 。

  • 3 （SRV_FORCE_NO_TRX_UNDO）

    崩溃恢复后 不运行事务 回滚。

  • 4 （SRV_FORCE_NO_IBUF_MERGE）

    防止插入缓冲区合并操作。如果它们会导致崩溃，请不要这样做。不计算表 统计信息。此值可能会永久损坏数据文件。使用此值后，准备删除并重新创建所有二级索引。从MySQL 5.6.15开始，设置InnoDB为只读。

  • 5 （SRV_FORCE_NO_UNDO_LOG_SCAN）

    启动数据库时 不查看Undo Log： InnoDB甚至将未完成的事务也视为已提交。此值可能会永久损坏数据文件。从MySQL 5.6.15开始，设置InnoDB为只读。

  • 6 （SRV_FORCE_NO_LOG_REDO）

    不进行与恢复有关的Redo Log前 滚。此值可能会永久损坏数据文件。使数据库页面处于过时状态，从而可能导致B树和其他数据库结构遭受更多破坏。从MySQL 5.6.15开始，设置InnoDB为只读。

您可以SELECT从表中转储它们。随着 innodb_force_recovery3或更低就可以值DROP或 CREATE表。从MySQL 5.6.27开始， DROP TABLE还支持innodb_force_recovery大于3 的值。

如果您知道给定的表导致回滚崩溃，则可以将其删除。如果遇到由于批量导入或失败而导致的失控回滚ALTER TABLE，则可以终止mysqld进程并设置 innodb_force_recovery为 3不回滚就启动数据库，然后DROP启动导致失控回滚的表。

如果表数据中的损坏阻止您转储整个表内容，则带有子句的查询可能能够转储损坏部分后的表部分。 ORDER BY *primary_key* DESC

如果一个高innodb_force_recovery 值是必需的开始InnoDB，有可能是，可能导致（含有查询的复杂查询损坏的数据结构WHERE，ORDER BY或其它条款）失败。在这种情况下，您可能只能运行基本SELECT * FROM t 查询。

14.21.3对InnoDB数据字典操作进行故障排除

有关表定义的信息既存储在.frm文件中，又存储 在InnoDB 数据字典中。如果您移动.frm文件，或者服务器在数据字典操作过程中崩溃，则这些信息源可能会变得不一致。

如果数据字典损坏或一致性问题使您无法启动InnoDB，请参阅 第14.21.2节“强制InnoDB恢复”以获取有关手动恢复的信息。

由于孤立表导致CREATE TABLE失败

数据字典不同步的症状是 CREATE TABLE语句失败。如果发生这种情况，请查看服务器的错误日志。如果日志表明InnoDB内部数据字典中已经存在该 表，则InnoDB表空间文件中有一个孤立表，该表没有相应的.frm文件。错误消息如下所示：

1
2
3
4
5
6
7
8
9
10
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

您可以按照错误消息中给出的说明删除孤立表。如果仍然无法DROP TABLE成功使用 ，则问题可能是由于mysql客户端中的名称完成 。要解决此问题，请使用 选项启动mysql客户端， --skip-auto-rehash然后重试DROP TABLE。（启用名称完成功能后，mysql试图构造一个表名称列表，当存在上述问题时，该列表将失败。）

无法打开文件错误

数据字典不同步的另一个症状是MySQL打印出一个无法打开InnoDB文件的错误 ：

1
ERROR 1016: Can't open file: 'child2.ibd'. (errno: 1)

在错误日志中，您可以找到以下消息：

1
2
3
4
InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?

这意味着存在一个孤立.frm 文件，里面没有对应的表 InnoDB。您可以.frm通过手动删除孤立 文件来删除它。

孤立的中间表

如果MySQL在就地ALTER TABLE操作（ALGORITHM=INPLACE）中间退出，则 可能会留下孤立的中间表，该表占用了系统空间。本节介绍如何识别和删除孤立的中间表。

中间表名称以#sql-ib前缀（例如 #sql-ib87-856498050）开头 。随附 .frm文件具有 #sql-*前缀，并且命名不同（例如#sql-36ab_2.frm）。

要识别系统上的孤立中间表，可以查看表监视器的输出或查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以开头的表名#sql。如果原始表位于 每个 表文件表空间中，#sql-*.ibd则孤立中间表的表空间文件（该 文件）应在数据库目录中可见。

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

要删除孤立的中间表，请执行以下步骤：

  1. 在数据库目录中，重命名 #sql-*.frm文件以匹配孤立中间表的基本名称：

    1
    shell> mv #sql-36ab_2.frm #sql-ib87-856498050.frm

    注意

    如果没有.frm文件，则可以重新创建它。该.frm文件必须具有与孤立中间表相同的表架构（它必须具有相同的列和索引），并且必须放置在孤立中间表的数据库目录中。

  2. 通过发出一条DROP TABLE语句，在表名前面加上前缀 #mysql50#并将表名括在反引号中来删除孤立的中间表 。例如：

    1
    mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

    该#mysql50#前缀告诉MySQL忽略file name safe encoding在MySQL 5.1中引入的。要对具有特殊字符（例如，“ ＃ ”）的表名执行SQL语句，需要将表名括在反引号中。

孤儿临时表

如果MySQL在表复制ALTER TABLE操作（ALGORITHM=COPY）中间退出，则 可能会留下一个孤立的临时表，该表会占用系统空间。本节介绍如何识别和删除孤立的临时表。

孤立的临时表名称以#sql-前缀（例如 #sql-540_3）开头 。随附 .frm文件的名称与孤立临时表的名称相同。

注意

如果没有.frm文件，则可以重新创建它。该.frm文件必须具有与孤立临时表相同的表架构（它必须具有相同的列和索引），并且必须放置在孤立临时表的数据库目录中。

要在系统上标识孤立的临时表，可以查看 表监视器的输出或查询 INFORMATION_SCHEMA.INNODB_SYS_TABLES。查找以开头的表名#sql。如果原始表位于 每个 表文件表空间中，#sql-*.ibd则孤立临时表的表空间文件（该 文件）应该在数据库目录中可见。

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

要删除孤立的临时表，请通过发出一条DROP TABLE语句来删除该表，在该表 名的前面加上前缀，#mysql50#并将表名括在反引号中。例如：

1
mysql> DROP TABLE `#mysql50##sql-540_3`;

该#mysql50#前缀告诉MySQL忽略 file name safe encoding在MySQL 5.1中引入的。要对具有特殊字符（例如，“ ＃ ”）的表名执行SQL语句，需要将表名括在反引号中。

表空间不存在

与innodb_file_per_table 启用，如果可能会出现以下消息 .frm或.ibd文件（或两者）丢失：

1
2
3
4
5
6
InnoDB: in InnoDB data dictionary has tablespace id N,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.

如果发生这种情况，请尝试以下步骤解决问题：

  1. .frm在其他一些数据库目录中 创建一个匹配文件，并将其复制到孤立表所在的数据库目录中。
  2. DROP TABLE原始表的 发行。那应该成功删除该表，并且 InnoDB应该在错误日志中显示一条警告，指出该.ibd文件已丢失。

恢复每表孤立文件ibd文件

此过程描述了如何将每表 孤立文件还原 .ibd到另一个MySQL实例。如果系统表空间丢失或不可恢复，并且要.ibd 在新的MySQL实例上还原文件备份，则可以使用此过程。

该过程假定您只有 .ibd文件备份，并且要恢复到最初创建孤立.ibd文件的MySQL版本 ，并且 .ibd文件备份是干净的。有关创建干净备份的信息，请参见 第14.6.1.4节“移动或复制InnoDB表”。

第14.6.1.3节“导入InnoDB表”中 概述的表导入限制 适用于此过程。

  1. 在新的MySQL实例上，在同名数据库中重新创建表。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    mysql> CREATE DATABASE sakila;

    mysql> USE sakila;

    mysql> CREATE TABLE actor (
             actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
             first_name VARCHAR(45) NOT NULL,
             last_name VARCHAR(45) NOT NULL,
             last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
             PRIMARY KEY  (actor_id),
             KEY idx_actor_last_name (last_name)
           )ENGINE=InnoDB DEFAULT CHARSET=utf8;

  2. 丢弃新创建的表的表空间。

    1
    mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;

  3. 将孤立.ibd文件从备份目录复制到新的数据库目录。

    1
    shell> cp /backup_directory/actor.ibd path/to/mysql-5.6/data/sakila/

  4. 确保.ibd文件具有必要的文件权限。

  5. 导入孤立.ibd文件。发出警告，指示InnoDB将尝试在不进行模式验证的情况下导入文件。

    1
    2
    3
    4
    5
    6
    mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;    
    Query OK, 0 rows affected, 1 warning (0.15 sec)

    Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
    Error opening './sakila/actor.cfg', will attempt to import
    without schema verification

  6. 查询表以确认.ibd 文件已成功还原。

    1
    2
    3
    4
    5
    6
    mysql> SELECT COUNT(*) FROM sakila.actor;
    +----------+
    | count(*) |
    +----------+
    |      200 |
    +----------+

14.21.4 InnoDB错误处理

以下各项描述了如何InnoDB 执行错误处理。InnoDB有时仅回滚失败的语句，而其他时候回滚整个事务。

  • 如果表空间中的文件空间用完 ，Table is full则会发生MySQL 错误并 InnoDB回滚SQL语句。

  • 一个事务死锁 导致InnoDB要 回滚整个 事务。发生这种情况时，请重试整个事务。

    锁定等待超时导致InnoDB仅回滚正在等待锁定且遇到超时的单个语句。（要使整个事务回滚，请使用--innodb-rollback-on-timeout 选项启动服务器 。）如果使用当前行为，请重试该语句；如果使用，请重试该事务 --innodb-rollback-on-timeout。

    死锁和锁等待超时在繁忙的服务器上都是正常的，应用程序必须意识到它们可能发生并通过重试来处理它们。您可以通过在事务期间第一次更改数据和提交之间进行尽可能少的工作来减少它们的可能性，因此将锁保持在最短的时间内，并且行的数量最少。有时，在不同交易之间分配工作可能是实用且有用的。

    当由于死锁或锁定等待超时而导致事务回滚时，它将取消事务中语句的影响。但是，如果start-transaction语句为 START TRANSACTION或 BEGIN 语句，则回滚不会取消该语句。进一步的SQL语句成为交易的一部分，直到发生COMMIT， ROLLBACK或某些SQL语句导致隐式提交。

  • 如果未IGNORE在语句中指定选项，则重复键错误会回滚SQL 语句。

  • 一个row too long error回滚SQL语句。

  • 其他错误大多数由MySQL代码层（在InnoDB存储引擎级别之上）检测到，并且它们回滚相应的SQL语句。在单个SQL语句的回滚中不会释放锁。

在隐式回滚期间以及在执行显式 ROLLBACKSQL语句期间，将在相关连接的 列中SHOW PROCESSLIST 显示。 Rolling back``State