MisakaTang's Blog
Go back

InnoDB官方文档--14.6 InnoDB在磁盘上的结构--14.6.1表(Tables)

Edit page

14.6 InnoDB在磁盘上的结构(InnoDB On-Disk Structures)

14.6.1 表(Tables)

14.6.1.1 创建InnoDB表

使用CREATE TABLE语句来创建一个InnoDB表

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

如果InnoDB是默认的存储引擎的话你不需要特定指定ENGINE=InnoDB,可以使用下面语句检查默认的存储引擎

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

如果你计划使用mysqldump或Replication在一个默认存储引擎不是InnoDB的服务器上重用CREATE TABLE语句时仍旧可以使用ENGINE=InnoDB子句.

InnoDB表和它的索引被创建在系统表空间(system tablespace),单表单文件(file-per-table)表空间或通用表空间( general tablespace)中.当innodb_file_per_table启用的时候,默认情况下,InnoDB表会被隐式的创建在一个单独的单表单文件表空间中.相反的,当innodb_file_per_table禁用的时候,InnoDB表会被隐式的创建在系统表空间中.使用CREATE TABLE ... TABLESPACE可以在通用表空间中创建表.

当你创建InnoDB表的时候,MySQL会创建一个**.frm文件在MySQL数据文件的数据库目录下.对于创建在单表单文件表空间中的表,MySQL也会默认创建一个.ibd表空间文件在数据库目录下.在InnoDB的系统表空间中创建的表被创建在一个已经存在的ibdata文件中,该文件保存在MySQL的数据目录下.在通用表空间中创建的表被创建在已存在的.ibd**文件中.通用表空间文件可以被创建在MySQL数据目录内或之外.

在内部,InnoDB在InnoDB数据字典里为每一张表增加一个目录.这个目录包括了数据库名.例如,在test数据库里创建t1表,数据目录的名字就是test/t1.这意味着你可以在不同的数据库里创建一个同名的表并且在InnoDB里不会冲突.

InnoDB表和.frm文件

InnoDB表默认的行格式(row format)由innodb_default_row_format来定义,默认值为DYNAMIC.Dynamic和Compressed行格式允许你使用InnoDB特性例如表压缩和有效的长列值的页外存储.要使用行格式,innodb_file_per_table必须要启用并且innodb_file_format必须设置为Barracuda.

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;

另外,你可以使用CREATE TABLE ... TABLESPACE语句在通用表空间中创建InnoDB表.通用表空间支持全部行格式.

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

CREATE TABLE ... TABLESPACE也可以被用来在系统表空间里创建一个行格式为Dynamic以及Compact或Redundant的InnoDB表.

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE = innodb_system ROW_FORMAT=DYNAMIC;

InnoDB表和主键(Primary Keys)

总是为InnoDB表指定主键,指定下面的一列或多列:

例如,在一张包含人员信息的表里,你不会在(firstname, lastname)上创建主键,因为不止一个人可以有相同的名字,一些人last name是空的,一些人会改名.在这么多的约束下,通常不会有很明显的列来做主键,所以你创建了一个新的列叫数字ID作为主键或者主键的一部分.你可以声明一个自增(auto-increment)列使得在插入行的时候自增.

# 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表属性

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 15:13:31
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

InnoDB表的属性也可以使用INFORMATION_SCHEMA表来查看:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 45
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 35
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
1 row in set (0.00 sec)
14.6.1.2 移动或复制InnoDB表

这个部分介绍移动或者拷贝部分或全部InnoDB表到不同的服务器或实例的技术.例如,你也许想把整个MySQL实例移动到一个更大,更快的服务器上去;你也许想克隆整个MySQL实例到一个新的主从同步的从服务器上去;你也许要拷贝个别的表到另一个实例上用来开发或者测试应用,或者到一个数据仓库用来产生报表.

在Windows上,InnoDB总是使用小写来存储数据库和表名.用二进制格式移动数据库从Unix到Windows或者从Windows到Unix,使用小写名称来创建所有数据库和表名.一个简便的方法是在创建数据库和表名之前在你的my.cnf或者my.ini文件的[mysqld]部分添加下面的行.

[mysqld]
lower_case_table_names=1

移动或者复制InnoDB表的技术包括:

可传输表空间

可传输表空间特性使用FLUSH TABLES ... FOR EXPORT来准备要从一个服务器实例拷贝到另一个的服务器的表.为了能够使用这个特性,InnoDB表的innodb_file_per_table必须为ON,使得每一张InnoDB表都有它自己的表空间.

Mysql企业备份

MySQl企业备份使你可以在一个正在运行的MySQL数据库上以最小的损害产生快照.当MySQL企业备份在拷贝表的时候,读和写操作可以继续.此外,MySQL企业备份可以创建一个压缩过的备份文件并且可以只备份表的一部分.结合MySQL的binlog,你可以进行时间点恢复.MySQL企业备份包括在MySQL企业订阅中.

复制数据文件

你可以简单地移动一个InnoDB数据库通过拷贝冷备份(Cold Backups)相关的所有文件.

InnoDB数据和日志文件在所有平台上是二进制兼容的,有相同的浮点数格式.如果浮点数格式不同但是在你的表中没有使用FLOAT和DOUBLE数据类型的话,过程是一样的:拷贝相关文件.

当你移动或者拷贝每个表的.ibd文件时,源系统和目标系统的数据库文件夹名必须是相同的.存储在InnoDB共享表空间中的表的定义中包括了数据库名.存储在表空间文件中的事务ID和日志序列号也和数据库中的不同.

要移动.ibd文件和相关的表到另一个数据库,使用RENAME TABLE语句.

RENAME TABLE db1.tbl_name TO db2.tbl_name;

如果你有.ibd文件的”干净”的备份,你可以将其还原到安装的MySQL上,操作如下:

  1. 在你拷贝了.ibd文件之后这个表不能被删除或者截断,因为这样会改变存储在表空间中的表ID.
  2. 使用ALTER TABLE语句删除当前的.ibd文件:
  ALTER TABLE tbl_name DISCARD TABLESPACE;
  1. 复制备份的.ibd文件到正确的目录下
  2. 使用下面的ALTER TABLE语句来告诉InnoDB使用新的.ibd文件
  ALTER TABLE tbl_name IMPORT TABLESPACE;

在这里,一个”干净”的.ibd备份文件要满足下面的要求:

你可以使用下面的方法来制作一个”干净”的.ibd文件:

  1. 停止mysqld服务的全部活动和提交所有的事务.
  2. 等到SHOW ENGINE INNODB STATUS显示数据库没有活动的事务并且InnoDB的主线程状态是waiting for server activity之后,你可以复制一个.ibd文件.

另一个制作干净的.ibd文件的办法是使用MySQL企业备份:

  1. 使用MySQL企业备份备份InnoDB的安装
  2. 在备份上启动第二个mysqld服务来清理.ibd文件

导入和导出

你可以使用mysqldump转存你的表然后在另一台机器上导入转存的文件.使用这种方式,格式是否一样或你的表包含浮点数据都没关系.

一个提高性能的方法是在导入数据的时候关闭自动提交(autocommit),如果表空间有足够的空间用于导入事务生产的大回滚段(rollback segment).在导入表或者表的一部分后手动执行提交.

14.6.1.3 把表从MyISAM转换到InnoDB

如果你想要以更高的可靠性和更好的拓展性把MyISAM表转换成InnoDB表的话,在进行转换之前查看下面的引导:

调整MyISAM和InnoDB的内存使用

当你在转换MyISAM表的时候,降低key_buffer_size配置的值来释放不需要的缓存结果.提高 innodb_buffer_pool_size配置的值,它扮演了类似为InnoDB表分配缓存内存的角色.InnoDB的缓冲池同时缓存表数据和索引数据用来加速查询的查找速度和保存查询结果在内存里以重用.

在繁忙的服务器上,关闭查询缓存后运行性能测试.InnoDB缓存池提供了类似的好处,所以查询缓存也许会占用不必要的内存.

处理过长或过短的事务

因为MyISAM表不支持事务,你也许需要把更多注意力放在自动提交(autocommit)配置选项以及COMMIT和ROLLBACK语句上.这些关键字对于允许多会话对InnoDB表的并发读写很重要,对于写入繁重的工作来说提供了坚实的可拓展性优势.

当一个事务打开的时候,系统会保存一个事务开始时的数据快照,如果在事务保持运行的时候系统进行了百万行杂散的插入,更新和删除操作的话会造成大量的开销.因此,请注意避免运行时间过长的事务:

前面的提示可以减少长时间事务中内存和磁盘的浪费.当事务比它应当的大小更小时,问题更多出在I/O上.对于每一个COMMIT,MySQL都会确保每一个变更都安全地记录到磁盘上了,这会涉及到一些I/O.

处理死锁(Deadlocks)

你也许会在MySQL错误日志看到一些警告信息提到死锁或者在SHOW ENGINE INNODB STATUS的输出中.尽管名字听起来很可怕,但是对于InnoDB表来说这个问题一点都不严重并且通常不需要任何的纠正动作.当两个事务开始修改多张表的时,它们以不同的顺序访问表,可以到达一种状态:每一个事务都在等在其他的事务并且都不能继续下去.当死锁检测(deadlock detection)开启的时候(默认开启),MySQL会马上检测到这种情况并且取消(rollback)“较小”的事务,允许另一个继续下去.如果死锁检测被innodb_deadlock_detect选项配置禁用的话,InnoDB会根据innodb_lock_wait_timeout设置回滚发生死锁的事务.

无论哪种方式,你的应用都需要一个错误处理逻辑来重启由于死锁被强制取消的事务.当你提交了和之前相同的sql语句,原来提交的就不会被接受了.如果另一个事务已经完成那么你可以继续,另一个事务还在处理中那么就要等到它结束.

如果死锁警告持续发生,你也许需要重审你的应用代码,以一致的方式编排SQL语句或者缩短事务.你可以开启innodb_print_all_deadlocks配置进行测试用以在MySQL错误日志中看到全部的死锁警告,要比查看SHOW ENGINE INNODB STATUS的输出只能看到最后一个警告好得多.

规划存储布局

为了InnoDB表获得最好的性能,你可以调整和存储布局相关的一些参数.

当你转换一个大的,经常使用并且保存重要数据的MyISAM表的时候,调查并考虑innodb_file_per_table,innodb_file_format和innodb_page_size配置选项,以及CREATE TABLE语句中的ROW FORMAT和KEY BLOCK SIZE选项.

在你的初始实验期间,最重要的设置是innodb_file_per_table.当这个设置启用的时候(MySQL5.6.6之后是默认值),新的InnoDB表会被隐式的创建在单表单文件(file-per-table)表空间里.和InnoDB系统表空间相比,单表单文件表空间允许操作系统在表被删除和截断(truncate)后回收磁盘空间.单表单文件的表空间也支持 Barracuda文件格式以及相关的特性,例如:表压缩,可变长的长列的页外效率存储和大索引前缀.

你也可以把InnoDB表存储在共用的通用表空间里.通用表空间支持Barracuda文件格式并且包含多个表.

转换现有表

使用下面的sql:

ALTER TABLE table_name ENGINE=InnoDB;

注意
不要把在mysql库中的MySQL系统表从MyISAM转换到InnoDB.这是一项不受支持的操作.

克隆一个表的结构

你可以通过克隆一个MyISAM表来创建InnoDB表而不是使用ALTER TABLE进行转换,在转换之前要在新旧表的两边进行测试.

用相同的列和索引的定义来创建一个空的InnoDB表.使用SHOW CREATE TABLE table_name\G来查看建表时使用的完整的语句.把ENGINE部分改成ENGINE=INNODB.

传输现有数据

把大量的数据传输到如上面所示的空的InnoDB表中,使用INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns来插入行.

你也可以在数据插入结束后为InnoDB表创建索引.在以前,创建一个新的二级索引对InnoDB来说是一个比较慢的操作,但是现在你可以在数据加载结束之后创建索引而索引创建步骤的开销相对较小.

如果你对二级键有UNIQUE约束,你可以在导入操作期间暂时性的关闭唯一性检查来加入表的导入:

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

对于大表来说可以节省磁盘I/O,因为InnoDB可以使用变更缓冲区(change buffer)对二级索引进行批量的写入.要确保数据不包含重复键.唯一性检查( unique_checks)允许但不要求存储引擎忽略重复键.

为了更好地控制插入过程，您可以用下面的代码块插入大型表格:

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

在插入了所有数据后,你可以重命名表.

在转换大表期间,增加InnoDB缓冲池的大小来减少磁盘I/O,最大设置物理内存的80%.你也可以增加InnoDB日志文件的大小.

存储要求

如果你打算在转换过程中创建多个InnoDB表数据的临时拷贝,建议你在单表单文件表空间中创建表以便在删除表的时候回收磁盘空间.当innodb_file_per_table配置选项启用的时候(默认启用),新创建的表会被隐式的创建到每个表的表空间文件中去.

无论你是直接转换到InnoDB表还是创建InnoDB的克隆表,确保在此过程中有足够的磁盘空间来容纳旧表和新表.InnoDB表比MyISAM表需要更多的磁盘空间.如果ALTER TABLE操作空间不足的话会开启回滚,如果受到磁盘限制的话,可能需要几小时.对于插入,InnoDB使用插入缓存将二级索引批量合并到索引中.这能节省大量磁盘I/O.对于回滚没有使用这样的机制,(所以)回滚可能比插入时间长30倍.

在回滚失控的情况下,如果你的数据库里没有有价值的数据,建议杀死数据库进程而不是等到数百万的磁盘I/O完成.

给每个表定义一个主键

PRIMARY KEY子句是MySQL查询和表和索引的使用情况的一个重要影响因子.主键唯一标识表中的行.表中的每一行都一定有一个主键并且没有两行会有相同的主键.

下面是主键的准则和详细的解释:

考虑将主键添加到还没有使用的表中.基于项目最大的大小来使用最小的数字类型.这可以使得列之间更加紧凑,这可以为大表节省大量空间.如果表具有二级索引的话,节省的空间是成倍增加的,因为主键值在每个二级索引中重复.除了减少磁盘上的数据大小,一个小的索引也使得更多的数据进入缓冲池,加速各种操作和提高了并发.

如果表已经在一些大的列上有了索引,例如VARCHAR,考虑增加一个新的自增的无符号列并且把主键切换到上面,即使查询中没有使用这列.这种改变可以在二级索引中节省大量空间.你可以将以前的主键列指定为UNIQUE NOT NULL来确保其和主键有相同的约束来防止这些列出现重复和空值.

如果你在多个表中传递信息,通常对每个表使用相同的列作为主键.例如,一个人员数据库可能有几张表,每一张都用员工号来做主键.销售数据库可能有一些表用顾客编号来做主键并且另一些表以订单ID做主键.因为使用主键的查找速度非常快,你可以对每张表构建有效的连接查询.

如果你不使用PRIMARY KEY子句,Mysql会为你创建一个不可见的.它是一个6位的值可能比你要的还长,这会浪费空间而且你不能在查询中引用它(因为是不可见的).

应用程序性能注意事项

InnoDB的可靠性和可拓展性特性使得其相对于MyISAM表来说需要更多的磁盘空间.你可以稍微更改列和索引定义,为了更好地利用空间,在处理结果集的时候减少I/O和内存消耗,以及使用索引查找的时候更好的查询优化计划来提高效率.

如果你使用数字ID作为主键,使用这个值和其他表中相关的值进行交叉引用,特别是join查询.例如,不是接受一个国家名称作为输入进行查询和使用相同的名称进行查询,而是执行一次查找确定国家ID,然后执行在其他表中查询相关信息的查询.不是将客户或目录编号作为数字串,而是使用几个字节转化为数字ID来进行存储和查询.一个4位的无符号整数列可以索引超过40亿件物品.

了解InnoDB表关联的文件

InnoDB文件比MyISAM文件需要更多的关注和计划.

14.6.1.4 InnoDB中的自增处理

InnoDB提供了一种可配置的锁机制可以显著提高向具有自增(AUTO_INCREMENT)列的表添加数据时的可扩展性和性能.为了在InnoDB表中使用自增机制,一个自增列必须被定义为索引的一部分,这样就可以在表上执行等效的被索引的SELECT MAX（ai_col）查找以获得最大列值.通常,这是通过在某些表中使列成为第一列来实现的.

这个部分描述了自增(AUTO_INCREMENT)锁的行为模式,不同设置模式的含义和InnoDB如何初始化自增计数器.

InnoDB自增锁模式(InnoDB AUTO_INCREMENT Lock Modes)

这个部分描述了自增锁模式被用来生成自增值时候的行为以及每种锁模式如何影响复制.自增锁模式的配置参数是innodb_autoinc_lock_mode

下面是innodb_autoinc_lock_mode配置的描述:

innodb_autoinc_lock_mode有3种可能的设置参数.分别是0,1,2代表传统的(traditional),连续的(consecutive)和交叉的(interleaved)锁模式.

InnoDB 自增锁模式的使用含义(InnoDB AUTO_INCREMENT Lock Mode Usage Implications)

InnoDB自增计数器初始化

这个部分介绍InnoDB怎么初始化自增计数器.

如果你为InnoDB表指定一个自增的列,InnoDB数据字典的表句柄会包含一个计数器叫做自增计数器(auto-increment counter)用来给列分配新值.这个计数器只存储在主内存里而不是磁盘上.

为了在服务器重启后重新初始化自增计数器,InnoDB会在包含自增列的表中进行第一次插入时执行和下面等价的语句:

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB会递增这个检索到的值然后将其分配给列和表的自增计数器.默认情况下,自增的值是1.这个默认值可以由auto_increment_increment配置覆盖.

如果表是空的,InnoDB使用1.这个默认值可以由auto_increment_offset覆盖.

如果SHOW TABLE STATUS在自增计数器初始化之前检查该表,InnoDB会初始化(自增计数器)但不会递增该值.这个值会被存储用于用户的下一次插入.这个初始化使用常规独占锁在表上进行读取并且锁会持续到事务结束.InnoDB遵循相同的过程来初始化新建表的自增计数器.

在自增计数器被初始化之后,如果你没有给自增列显式地指定值的话,InnoDB会递增计数器然后给列分配新值.如果插入显式指定自增列值的行并且值比计数器当前值更大的话,计数器会被设置为这个指定的列值.

InnoDB会在服务器运行的时候使用内存中的自增值计数器.当服务器停止并重启时,InnoDB会初始化每个表的计数器用于表的第一次插入.

服务器重启还会取消CREATE TABLE和ALTER TABLE语句中AUTO_INCREMENT = N对表的影响,你可以使用InnoDB表来设置初始计数器的值或更改当前计数器的值.

14.6.1.5 InnoDB和外键约束

外键定义(Foreign Key Definitions)

InnoDB表的外键定义受到以下约束:

引用操作(Referential Actions)

InnoDB表的外键引用操作受到下面限制:

生成列和虚拟索引的外键限制

14.6.1.6 InnoDB表的限制

最大值和最小值(Maximums and Minimums)

InnoDB表的限制

如果innodb_table_locks=1(默认值)LOCK TABLES会在每个表上获取2个锁.除了MySQL层面上的表锁也需要InnoDB的表锁.MySQL 4.1.2之前版本不需要InnoDB表锁;通过设置innodb_table_locks=0来使用这种旧的行为.如果没有获取InnoDB表锁,即使表的某些记录被其他事务锁定,LOCK TABLES也会完成.

在MySQL 5.7中,innodb_table_locks=0对于使用 LOCK TABLES ... WRITE显式锁定表没有任何影响.它对隐式的LOCK TABLES ... WRITE(例如通过触发器)或LOCK TABLES ... READ有效.

Edit page
Share this post on:
Share this post via WhatsApp Share this post on Facebook Share this post on X Share this post via Telegram Share this post on Pinterest Share this post via email
Previous Post
阿里云RDS逻辑备份恢复主从
Next Post
InnoDB官方文档--14.4 InnoDB 架构|14.5 InnoDB 在内存中的结构