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

发表于 2019-03-01 更新于 2024-11-18 分类于技术

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

14.6.1 表(Tables)

14.6.1.1 创建InnoDB表

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

1	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表.通用表空间支持全部行格式.

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

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

1	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]部分添加下面的行.

1 2	[mysqld] lower_case_table_names=1

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

可传输表空间(Transportable Tablespaces)
Mysql企业备份(MySQL Enterprise Backup)
复制数据文件(冷备份方法)(MySQL Enterprise Backup)
导入和导出(mysqldump)(Export and Import )

可传输表空间

可传输表空间特性使用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语句.

1	RENAME TABLE db1.tbl_name TO db2.tbl_name;

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

在你拷贝了.ibd文件之后这个表不能被删除或者截断,因为这样会改变存储在表空间中的表ID.
使用ALTER TABLE语句删除当前的.ibd文件:

1	ALTER TABLE tbl_name DISCARD TABLESPACE;

复制备份的.ibd文件到正确的目录下
使用下面的ALTER TABLE语句来告诉InnoDB使用新的.ibd文件

1	ALTER TABLE tbl_name IMPORT TABLESPACE;

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

.ibd文件中没有未被提交的事务修改
.ibd文件中不能有没有被合并的insert缓存
清除操作(purge)已经移除了.ibd文件中的全部被标记删除的索引.
mysqld已经把所有被修改的页面都从缓冲池刷到了.ibd文件中.

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

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

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

使用MySQL企业备份备份InnoDB的安装
在备份上启动第二个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表的并发读写很重要,对于写入繁重的工作来说提供了坚实的可拓展性优势.

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

如果你使用一个mysql会话来进行交互性实验,在结束的时候总是进行COMMIT或ROLLBACK.关闭交互式会话而不是长时间保持开启,来避免意外地长时间保持一个事务在开启状态.
确保你应用中的任何错误处理也会使用ROLLBACK回滚未完成的更改或COMMIT提交已完成的更改.
ROLLBACK是一个相对开销比较大的操作,因为INSERT,UPDATE和DELETE操作在COMMIT操作之前都被写入到了InnoDB表中,因为大部分的改变都会被成功的提交并且很少被回滚.在实验大量数据时,避免改变大量的行然后回滚这些变动.
使用INSERT语句加载大量数据时,定期使用COMMIT提交结果来避免长达几小时的事务.在典型的数据仓库加载操作中,如果出了问题,使用TRUNCATE TABLE来重新开始而不是使用ROLLBACK.

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

对于InnoDB表的多数操作,你应该使用autocommit=0的设置.从效率的角度来看,这避免了在你提交大量连续的INSERT,UPDATE或DELETE时候的不必要的I/O.从安全的角度来看,这允许你使用ROLLBACK语句来恢复丢失或者乱码的数据,在你在mysql命令行犯了错误或者你的应用进行错误处理的时候.
当运行一系列的查询来生成报告或进行分析统计的时候autocommit=1对于InnoDB表来说是合适的.在这种情况下,没有与COMMIT和ROLLBACK相关的I/O,InnoDB可以自动地优化只读的工作负载.
如果你进行了一系列的相关更改,最后用一个COMMIT来完成全部的更改.例如,如果你把相关的信息插入到几个表中,使用一个COMMIT进行所有的更改.或者你运行许多连续的INSERT语句然后在所有数据被加载之后使用一个COMMIT;如果你在运行百万行的INSERT语句,也许通过使用COMMIT每次提交一万或十万条记录来拆散这个大事务可以使得事务不会变得太大.
请记住,甚至一个SELECT语句也会打开一个事务,所以在mysql交互式会话中运行一些报告或者调试的查询之后,执行一个COMMIT或者关闭mysql会话.

处理死锁(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:

1	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约束,你可以在导入操作期间暂时性的关闭唯一性检查来加入表的导入:

1
2
3

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

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

给每个表定义一个主键

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

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

对每一个表声明一个主键.通常,它是在查找单行时在WHERE子句中引用的最重要的列.
在CREATE TABLE建表语句时指定主键要比建表之后使用ALTER TABLE指定要好.
仔细选择列的数据类型.首选数字列而不是字符或者字符串.
考虑使用自增列如果表中没有其他稳定的,不重复的,非空的数组列使用的话.
如果对主键的值是否会发生变化有疑问的话,自增的列也是一个好选择.更改主键列的值是一个代价高昂的操作,可能涉及重新排列表格中的数据和每一个二级索引.

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

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

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

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

应用程序性能注意事项

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

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

了解InnoDB表关联的文件

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

你不能删除InnoDB系统表空间中的ibdata文件
移动和拷贝InnoDB表到不同的服务器参考14.6.1.2 移动和复制InnoDB表

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配置的描述:

INSERT-like子句

在表中生成新行的所有语句,包括INSERT,INSERT ... SELECT,REPLACE,REPLACE ... SELECT和LOAD DATA.包括简单插入(simple-inserts),批量插入(bulk-inserts)和混合模式插入(mixed-mode).
简单插入(simple-inserts)

语句中可以预先确定要插入的行数.这包括没有嵌套子查询的单行和多行的插入以及替换语句但是不包括INSERT ... ON DUPLICATE KEY UPDATE.
批量插入(bulk-inserts)

语句中预知要插入的行数.包括INSERT ... SELECT,REPLACE ... SELECT和LOAD DATA语句,但不是普通的INSERT.InnoDB在处理每一行的时候都为自增列生成一个新值.
混合模式插入(mixed-mode)

这些是简单插入语句但是指定了其中的一些自增列的值.例如:
1
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种混合模式插入是INSERT ... ON DUPLICATE KEY UPDATE,最坏的情况下是在INSERT后面加一个UPDATE,在更新阶段可能会或不会使用自增列的已分配的值.

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

innodb_autoinc_lock_mode = 0(传统锁模式)

传统锁模式提供了和MySQL5.1引入innodb_autoinc_lock_mode配置之前相同的行为.提供传统锁模式选项是为了向后兼容,性能测试以及解决”混合模式插入”问题,因为在语义上可能存在差异.

在这个锁模式下,所有”INSERT-like”语句都会获得一个特殊的表级AUTO-INC锁,用来插入有自增列的表.此锁通常保持在插入语句的末尾(而不是事务的末尾)来确保对于给定的一系列插入语句能够以可预测和可重复的顺序为其分配自增值,并且确保对于任意给定的语句自增值都是连续的.

在基于语句复制的情况下,在slave上执行sql语句的时候会使用与master在自增列上相同的值.执行多个插入语句的结果是确定的并且slave上产生的数据和master是相同的.如果多个插入语句生成的自增值是交错的,那么两个并发的INSERT操作产生的结果是不确定的并且使用基于语句的复制传递到slave的数据是不可靠的.

为了清楚起见,使用下面的示例:
1
2
3
4
5
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
假设有2个事务在运行,每一个插入的行都有一个自增的列.一个事务使用INSERT ... SELECT语句插入1000行,另一个使用简单的INSERT插入操作.
1
2
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB无法预知Tx1事务到底SELECT了多少行进行插入,所以随着语句的执行每次分配一个自增的值.由于使用保持在语句末尾的表级锁,在t1表上同时只能有一个插入语句执行,并且不同的插入语句生成的自增的数值也是无关的.所以Tx1事务中生成的数值是自增的而Tx2中的自增数值比Tx1大还是小取决于两个事务哪个先执行.

只要以相同的顺序执行binlog中的sql语句,结果就和Tx1和Tx2第一次执行时一样.因此,保持到语句结束的表级锁使得使用自动增量的插入语句可以安全地用于基于语句的复制.但是,当多个事务同时执行插入语句的时候,表级锁限制了并发性和可伸缩性.

在上面的例子里,如果没有表级锁,Tx2事务的插入语句中自增列的值就取决于语句执行的时间.如果Tx2的插入语句在Tx1插入语句正在执行的时候执行,这两个插入语句产生的自增列的值就变得不确定了,而且每次运行都可能不同.

在”连续锁”模式下,InnoDB可以避免对能够预先知道插入行数的”简单插入”语句使用表级的AUTO-INC锁,并且仍旧保持执行顺序的确定性和基于语句复制的安全性.

如果你不使用binlog重播sql语句(的形式)作为恢复或复制的一部分,交错锁模式(interleaved lock mode)可用于消除所有表级锁AUTO-INC的使用来实现更高的并发和性能,以允许语句产生的自增数的差距和由于并发产生自增数字交错为代价.
innodb_autoinc_lock_mode = 1 (连续锁模式)

这是默认的锁模式.在这种模式下,批量插入使用了特殊的AUTO-INC表级锁并且保持到语句结束.适用于所有INSERT ... SELECT,REPLACE ... SELECT和LOAD DATA语句.同时只能有一个语句持有AUTO-INC锁.如果批量插入的原表和目标表不同,AUTO-INC锁会在从原表第一行获取了共享锁之后对目标表加锁.如果原表和目标表相同的话,AUTO-INC锁会在获取了共享锁之后对所有被SELECT的行加锁.

“简单插入”(预先知道行数的插入)通过在互斥锁(一个轻量锁)的控制下获得所需的自增值的数量来避免表级的AUTO-INC锁,互斥锁只会在分配的过程中持有,而不是直到语句执行结束之后.除非有另一个事务持有AUTO-INC锁,否则不会使用AUTO-INC锁.如果另一个事务持有AUTO-INC锁,简单插入会等待AUTO-INC释放,就像批量插入一样.

这种锁模式确保在不知道给定的INSERT语句要插入的行数的情况下,所有INSERT-like的语句分配的自增值都是连续的,并且基于语句的复制是安全的.

简而言之,这种锁模式显著的提高了可伸缩性同时可以安全的使用基于语句的复制.此外,和传统锁模式一样给任意语句的自增值都是连续的.对于任何有自动增量的语句,与传统模式相比语义上没有变化,但是有一个重要的例外.

这个例外是对于”混合模式”插入,因为用户提供了其中的一部分自增列的值,InnoDB申请的自增列的数量会比需要的量更多,但是因为自增列的值生成都是连续的,所以后执行的插入语句的值会高于之前的,(前一个语句)多出来的数值就丢失了.
innodb_autoinc_lock_mode = 2 (交错锁模式)

在这种锁模式下,任何插入相关的语句都不会使用AUTO-INC锁,多个语句可能同时执行.这是最快和最具扩展性的锁模式,但是使用基于从binlog回放语句的复制或恢复的时候,这是不安全的.

在这种模式下,自增的值保证是唯一的并且在所有执行的插入语句中单调递增,但是并发的插入会导致生成的自增值会产生交错,所以对于一个插入语句来说里面的自增值可能是不连续的.

如果唯一执行的语句是在插入之前知道要插入的行数的简单插入语句那么单个语句生成的自增值之间是没有间隙的,除了”混合模式插入”.然而,当执行批量插入的时候,任何给定的语句中的自增数值可能存在间隙.

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

使用自增(auto-increment)进行复制

如果你使用基于语句的复制,在master和slave上把innodb_autoinc_lock_mode设置成相同的值(0或1).如果你设置”innodb_autoinc_lock_mode = 2”或者主从不使用相同的锁模式则不能保证主从上的自增键的值是相同的.

如果你使用基于行或者混合格式的复制,任何一个模式都是安全的因为基于行的复制对于sql的执行顺序不敏感(混合格式就是对不安全的基于语句的复制使用基于行的复制).
丢失自增值和序列间隙(sequence gaps)

在所有的锁模式中(0,1和2),如果生成自增值的事务回滚的话,这些自增值就会丢失.一旦为自增列生成了值,无论是插入的语句没有完成还是包含的事务是否回滚这些生成的值都不能回滚.这些丢失的值不会被重用.因此,表中的自增列可能会存在间隙(sequence gaps).
给自增的列指定NULL或0

在所有的锁模式下,用户在INSERT语句中给自增的列指定NULL或0,InnoDB都会视为这些行没有被指定值然后为其生成一个新值.
给自增的列指定一个负值

在所有锁模式中,如果你给自增列指定一个负值那么就不会给其定义自增的机制.
如果自增列的值大于对应列数据类型的最大值

在所有锁模式下,如果值大于指定整数类型可以存储的最大值就不会给其定义自动增加机制.
批量插入中的自增值的间隙

在innodb_autoinc_lock_mode设置为0(传统的)和1(连续的)时,任何给定的语句生成的自增值都是连续的,没有间隙,因为AUTO-INC锁会保持到语句结束并且同时只能执行一个语句.

在innodb_autoinc_lock_mode设置为2(交错)时,生成批量插入的自增值的时候可能会产生间隙,因为只有这个时候会并行执行多个INSERT-like语句.
对于锁模式1和2,在连续的语句之间可能会存在间隙,因为对于批量插入不可能知道每个语句确切需要的数量并且可能会高估.
混合模式插入指定的自增值

考虑一个混合模式插入:在一个简单插入中指定一些(但不是全部)自增值.这样的语句在模式0,1,2中表现不同.例如,假定t1表有一个自增列c1,并且最近生成的自增值是100.
1
2
3
4
mysql> CREATE TABLE t1 (
-> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> c2 CHAR(1)
-> ) ENGINE = INNODB;
现在,考虑下面的混合模式插入的语句:
1
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
innodb_autoinc_lock_mode为0会产生下面4行:
1
2
3
4
5
6
7
8
9
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
下一个可用的自增值是103因为每次分配一个自增值而不是在处理语句时一次性分配.无论是否同时执行了插入语句,结果都是一样的.

innodb_autoinc_lock_mode为1会产生下面4行:
1
2
3
4
5
6
7
8
9
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
然而这种情况下下一个可用的自增值是105而不是103,因为在处理语句时分配了4个值而只使用了2个.无论是否同时执行了插入语句,结果都是一样的.

innodb_autoinc_lock_mode为1会产生下面4行:
1
2
3
4
5
6
7
8
9
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| x | b |
| 5 | c |
| y | d |
+-----+------+
x和y的值是唯一的并且比之前生成的值都大.但是x和y的具体值取决于并发执行语句时生成的自增值.

最后,考虑下面的语句在最近生成的自增值为100时执行:
1
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
在任何一个innodb_autoinc_lock_mode设置下都会生成一个重复键错误 error 23000(Can’t write; duplicate key in table)因为101是分配给(NULL, ‘b’)这一行的所以插入(101, ‘c’)会失败.

在一系列插入语句中间修改自增列的值

在所有锁模式下,在一系列插入语句中间修改自增列的值都会导致重复条目(Duplicate entry)错误.例如,如果你执行了一个UPDATE操作更改自增列的值为大于当前自增列最大值的话,后续的未指定自增列值的操作可能会遇到重复条目(Duplicate entry)错误.下面是一个演示:

mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

InnoDB自增计数器初始化

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

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

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

1	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表的外键定义受到以下约束:

InnoDB允许外键引用任何索引或索引组.但是在引用的表中必须有一个被列为first列的列被引用且是以相同的顺序.
InnoDB当前不支持用户定义分区的表的外键.这意味着没有用户分区的InnoDB表可以包含外键引用或被外键引用的列.
InnoDB允许外键约束引用一个非唯一键.这是InnoDB对标准SQL的一个拓展.

引用操作(Referential Actions)

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

虽然MySQL服务器允许SET DEFAULT但是InnoDB视其为无效.在CREATE TABLE和ALTER TABLE语句中使用这个子句都不会被InnoDB表允许.
如果在父表中有几行数据有相同的引用键值,InnoDB在外键检查中会起作用就像父表中其他有相同键值的行不存在一样.例如,如果你定义了一个RESTRICT类型约束,并且一个子行有多个父行,InnoDB不允许删除父行中的任何一个.
InnoDB基于索引中对应的外键约束记录通过深度优先算法进行级联操作.
如果ON UPDATE CASCADE或ON UPDATE SET NULL递归更新它在级联期间更新的同一个表,行为就像RESTRICT.这也就意味着你不能在ON UPDATE CASCADE或ON UPDATE SET NULL中使用自我指涉.这是为了防止级联更新导致无限循环.另一方面ON DELETE SET NULL这样的自我指涉是可能的,这个就像ON DELETE CASCADE一样.指涉操作深度不能操作15级.
就像MySQL通常做的,一个SQL语句插入,删除或更新多行,InnoDB逐行检查唯一(UNIQUE)和外键(FOREIGN KEY)约束.执行外键检查时,InnoDB会在需要查看的子记录和父记录上设置共享行级锁.InnoDB会立即检查外键约束;检查不会延迟到事务提交.根据SQL标准,默认行为应该是延迟检查.也就是在处理完整个SQL语句后才会检查约束.在InnoDB实现延迟约束检查之前,有些事是不可能的,例如删除使用外键引用自身的记录.

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

对存储的生成列的外键约束不能使用ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULL, ON DELETE SET DEFAULT, 或 ON UPDATE SET DEFAULT.
外键约束不能引用虚拟生成列.
在5.7.16之前,外键约束不能引用定义在虚拟生成列上的二级索引.
在MySQL5.7.13及之前,InnoDB不允许在索引虚拟生成列的基列上使用级联引用操作定义外键约束.在MySQL 5.7.14解除了这个限制.
在MySQL 5.7.13及更早版本中,InnoDB不允许在显式包含在虚拟索引中的非虚拟外键列上定义级联引用操作.在MySQL 5.7.14中解除了这个限制.

14.6.1.6 InnoDB表的限制

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

一张表最多包含1017列(MySQl5.6.9之前是1000列).虚拟生成列也包含在这个限制里.
一张表最多包含64个二级索引.
如果innodb_large_prefix启用(默认值),对InnoDB使用DYNAMIC或COMPRESSED行格式的表的索引前缀的限制是3072字节.如果innodb_large_prefix禁用,对表的任何行格式索引前缀的限制都是767字节.

innodb_large_prefix已弃用,在以后的版本中将删除.innodb_large_prefix在MySQL5.5中引入用来禁用大前缀索引,为了和早期的InnoDB版本兼容.

对于使用REDUNDANT或COMPACT行格式的InnoDB表,索引前缀的限制是767字节.例如,你可能在TEXT或VARCHAR列超过255个字符的时候遇到这个列前缀索引限制,假定是一个utf8mb3字符集,每个字符最大是3个字节.

尝试使用超出限制的索引键前缀长度会返回错误.要避免复制配置中的这类错误,避免在master上开启innodb_large_prefix配置如果在slave上不生效的话.

适用于索引前缀键的限制也适用于全列索引键.
如果在启动MySQL实例时通过修改innodb_page_size将InnoDB页大小减少到8kb或4kb,根据16kb页面3072字节的限制,索引键的最大长度也会按比例降低.8kb最大索引键长度是1536字节,4kb最大索引键长度是768字节.

多列索引最多允许16列,超过限制会返回下面错误:

1	ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

除了可变长列(VARBINARY,VARCHAR,BLOB和TEXT)之外最大行长度略小于4KB,8KB,16KB和32KB的页面大小.例如,innodb_page_size默认的最大行长度是16KB大约8000字节.对64KB大小的InnoDB也来说,最大行长度大概是16000字节.LONGBLOB和LONGTEXT列必须小于4GB,并且包括BLOB和TEXT列的总长度必须小于4GB.
如果一行的长度小于半页,所有行都存储在页内.如果超过半页,可变长列会被选择存储到页外部直到行适合半页.

虽然InnoDB内部支持行大小超过65535字节,但是MySQL本身对所有列组合添加65535字节的行大小限制.

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
  -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
  -> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs

在一些旧的操作系统中,文件必须小于2GB.这不是InnoDB自身的限制,但是如果你需要一个大表空间配置其使用几个小的数据文件而不是一个大的数据文件.
InnoDB日志文件总大小可以达到512GB.
最小的表空间大小比10MB稍大.最大表空间大小取决于InnoDB页面大小.

最大表空间大小也就是表的最大大小.
在Windows32位系统中表空间文件不能超过4GB(Bug #80149).
表空间文件的路径包括文件名,不能超过Windows中最大路径(MAX_PATH)的限制.Windows10中MAX_PATH限制为260个字符.从Windows10 1607版本开始MAX_PATH限制将从常见的Win32文件和目录函数中删除,但是你必须启用新行为.
InnoDB默认页面大小是16KB.你可以在创建MySQL实例的时候通过配置innodb_page_size来增加或减少页面大小.

Barracuda文件格式中的ROW_FORMAT=COMPRESSED假定页面大小最多16KB并且使用14位指针.

支持32KB和64KB页面大小,但是 ROW_FORMAT=COMPRESSED不支持不支持超过16KB的页面大小.对于32KB和64KB页面大小最大记录大小是16KB. innodb_page_size=32k范围为2MB, innodb_page_size=32k范围是4MB.

使用特定页面大小的Mysql实例不能使用来自不同页面大小实例的数据文件和日志文件.

InnoDB表的限制

ANALYZE TABLE通过在每个索引树上执行随机下潜(random dives)来确定索引基数和更新相应的索引基数.因为只是估计,重复运行ANALYZE TABLE可能产生不同的数值.这使得在InnoDB表上ANALYZE TABLE很快但不是100%准确,因为不会考虑所有行.

可以通过开启 innodb_stats_persistent选项使得ANALYZE TABLE更精确更稳定.启用该设置后,在对索引列数据进行重要更改后运行ANALYZE TABLE非常重要,因为统计数据不会定期重新计算(比如服务器重启).

如果启用了持久统计信息设置,你可以通过修改 innodb_stats_persistent_sample_pages系统变量来改变随机下潜(random dives)的次数.如果禁用持久统计信息设置,修改 innodb_stats_transient_sample_pages来代替.

MySQL在join优化中使用索引基数估计.如果join操作没有被正确优化,尝试使用ANALYZE TABLE.在少数情况下,ANALYZE TABLE不能为特定的表生成足够好的值,你可以对查询使用FORCE INDEX来强制使用特定索引或通过设置max_seeks_for_key系统变量来确保MySQL优先考虑通过表扫描进行索引查找.
如果语句或事务正在表上运行,一个 ANALYZE TABLE运行在相同的表上紧接着第二个 ANALYZE TABLE操作,第二个 ANALYZE TABLE操作会被阻止直到语句或事务完成.这个问题出现的原因是 ANALYZE TABLE结束运行后会使得当前加载表被定义为已过时.新语句或事务(包括第二个 ANALYZE TABLE语句)必须将新表加载到表缓存中,在完成当前运行的语句或事务并清除表的已过时定义之前(加载新表)这种情况是不会发生的.加载多个并发表定义是不支持的.
SHOW TABLE STATUS不能给出InnoDB表的确切信息除了表格保留的物理尺寸.行计数只是SQL优化中使用的粗略估计.
InnoDB不保留表中的内部行数,因为并发事务可能同时”看到”不同数量的行.因此,SELECT COUNT(*)语句只计算当前事务可见的行数.
在Windows上,InnoDB始终在内部以小写的形式存储数据库和表名.要以二进制格式将数据库从Unix移动到Windows或从Windows移动到Unix,使用小写名创建所有的数据库和表.
必须将自增列ai_col定义为索引的一部分,以便在表上执行和SEELCT MAX(ai_col)等效的索引查找时获取最大值.通常这是通过是列成为某些表索引的第一列来实现的.
InnoDB在初始化表上先前指定的自增列时在自增列相关的索引末尾设置独占锁.

innodb_autoinc_lock_mode=0时,InnoDB使用特殊的AUTO-INC表级锁模式,在此模式下获取锁并在访问自动增量计数器时保持到当前SQL语句的末尾.在保持AUTO-INC表锁时，其他客户端无法插入表中.使用innodb_autoinc_lock_mode = 1的”批量插入”时也会出现相同的行为.表级锁AUTO-INC不与innodb_autoinc_lock_mode = 2一起使用
当你重新启动MySQL服务器时,InnoDB可能会重复使用为自增列生成但从未存储过的旧值(也就是在回滚的旧事务期间生成的值).
当自增整数列用完值时，后续INSERT操作将返回重复键错误(duplicate-key error).这是正常的MySQL行为.
DELETE FROM tbl_name不会重新生成表,而是逐个删除所有行.
级联外键操作不会激活触发器.
您不能创建一个列名与内部InnoDB列的名称匹配的表(包括DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR 和 DB_MIX_ID).此限制适用于把名称任何字母小写的情况.
1
2
mysql> CREATE TABLE t1 (c1 INT, db_row_id INT) ENGINE=INNODB;
ERROR 1166 (42000): Incorrect column name 'db_row_id'
锁和事务(Locking and Transactions)

如果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有效.

事务提交或中止时,将释放事务持有的所有InnoDB锁.因此,在autocommit = 1模式下在InnoDB表上调用LOCK TABLES没有多大意义,因为获取的InnoDB表锁将立即释放.
你无法在事务中锁定其他表,因为LOCK TABLES隐式执行COMMIT和UNLOCK TABLES.

InnoDB官方文档--14.4 InnoDB 架构|14.5 InnoDB 在内存中的结构

发表于 2019-02-18 更新于 2024-11-18 分类于技术

14.4 InnoDB 架构

14.5 InnoDB 在内存中的结构

14.5.1 缓冲池(Buffer Pool)

缓冲池是一个用来缓存被访问的表和索引数据的在主存储中的区域.缓冲池允许直接从内存中处理常用数据用来加快处理速度.在专用服务器上,通常给缓冲池分配80%的内存.

为了大量读操作的处理效率,缓冲池被分成了可能有多行数据的页面.为了缓存管理的效率,缓冲池被实现为页面相连为列表(list)的形式,使用LRU算法使得很少使用的数据在缓存中老化.

了解如何利用缓冲池将频繁访问的数据保存在内存中是MySQL调优的一个重要方面.

缓冲池的LRU(最近最少使用)算法

缓冲池是一个使用最近最少使用(least recently used)算法变种管理的列表.当需要空间来向缓冲池中插入新页面的时候,最近最少使用的页面被赶出,新页面被加到列表的中间.中间插入策略把列表看做两个子列表:

在头部是一个最近被访问的”年轻的”列表
在尾部是一个最近访问较少的列表

缓冲池列表示意图:

该算法保留了新子列表(new sublist)中查询时大量使用的页面.旧子列表(old sublist)中包含了较少使用的页面,这些页面被视为驱逐(evicted)的候选.

默认情况下,算法的操作如下:

3/8的缓冲池被分给旧子列表
列表的中点是新子列表的尾部和旧子列表头部相交的地方.
当InnoDB从缓冲池中读取了一个页面,将其插入到中点(旧子列表的头部).一个页面被读取的原因是用户的特定操作例如一个SQL查询或者是InnoDB自动的预读(read ahead)操作.
访问旧子列表中的页面会使其变”年轻”,将其移动到缓冲池的头部(新子列表的头部).如果页面是因为被需要而读取,第一次访问会立即发生并且页面变得”年轻”.如果页面是因为预读被读取,第一次访问就不会立即执行.
随着数据库的运行,缓冲池中的页面会随着未访问年龄的增大而向列表尾部移动.新旧子列表中的页面都会随着其他页面的变化而变旧.旧子列表中的页面会随着页面向中点插入而逐渐老化.最终,未被使用的页面到达旧子列表的尾部后被赶出(内存).

在默认情况下,查询导致读取的页面会立即移动到新子列表中,意味着其可以在缓冲池中待得更久.一个表扫描(例如mysqldump或者没有where子句的select语句)会把大量的数据带进缓冲池并且赶出等量的旧数据哪怕这些新数据不会被再次使用.相似的,由后台预读线程加载页面移到新列表的头部然后只访问一次.这些情况会将经常使用的页面推到旧子列表中,在那里页面会被赶出内存.

InnoDB标准监视器输出包含几个和LRU算法相关的字段在BUFFER POOL AND MEMORY中.

缓冲池配置

你可以从多个方面配置缓冲池来提高性能

理想情况下,你可以在不影响服务器其他进程的情况下把缓冲池的内存设的尽可能的大.缓冲池越大InnoDB就越像一个内存型数据库,从磁盘上读取一次然后在后续的访问中从内存读取数据.
在有足够内存的64位系统上,你可以把缓冲池拆分为多个部分来最大限度减少并发操作对内存结构的竞争.
你可以把频繁访问的数据保留在内存中,无论来自操作的峰值是否将大量的不经常访问数据带入缓冲池.
你可以控制何时以及如何执行预读请求以异步读取预期很快就会需要的页面进入缓冲池.
你可以控制何时进行后台刷新操作以及是否根据工作负载动态调整刷新率.
你可以微调缓冲池刷新行为的各个方面以提高性能.
你可以配置InnoDB如何保留当前缓冲池状态,以避免服务器重新启动后的长时间预热.

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

使用SHOW ENGINE INNODB STATUS可以获取InnoDB标准监视器输出,缓冲池相关的信息在BUFFER POOL AND MEMORY部分:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 00.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 50600.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

缓冲池各项指标对照表:

14.5.2 变更缓存区(Change Buffer)

变更缓存区是一个特殊的数据结构,当页面不在缓冲池里的时候它会缓存对这些页面二级索引的变更.缓存的变化可能是由于insert,update,delete操作(DML)引起的,在这些页面被其他读操作加载到缓冲池的时候会合并这些改变.

变更缓存区结构:

不像聚簇索引,二级索引通常不是唯一的,并且二级索引的插入顺序是相对随机的.同样,删除和更新可能会影响不在索引树中的相邻的二级索引页.变更缓存区的合并会被延迟到受影响的页面被其他操作读取到缓冲池的时候进行,避免从磁盘读取二级索引页面到缓冲池所需的大量随机访问I/O.

清除操作会在系统空闲或者缓慢关机的时候定期地把更新过的索引页写到磁盘上.在此期间,磁盘I/O会增加,可能会导致和磁盘相关的查询速度显著下降.缓存区变更的合并在事务被提交之后可能会继续发生,甚至在服务器关闭和重启的时候.

在内存里,变更缓存区拥有一部分缓冲池.在磁盘上,变更缓存区是系统表空间的一部分,当数据库服务关闭的时候被缓存的索引会变更.

innodb_change_buffering变量管理变更缓存区缓存的数据类型.

如果索引包含降序的索引列或者主键包含递减索引列的话二级索引就不支持变更缓存区.

变更缓存区的配置

当在表上执行insert,update和delete的时候,索引列的值(尤其是二级索引的值)通常是未排序的,需要大量的I/O才能更新二级索引.当相关页面不在缓冲池里的时候变更缓存区会改变二级索引的条目,以此来避免立即从磁盘上读取页面的大量的I/O操作消耗.当页面加载到缓冲池的时候,缓存的变更会被合并,然后被更新的页面会随后刷新到磁盘.InnoDB主线程会在服务器空闲以及慢关机(slow shutdown)期间合并缓存的变更.

由于可以减少磁盘的读写次数,变更缓存区功能对于I/O绑定的工作最有价值.例如有批量插入这样的大量的DML操作的应用.

然而,变更缓存区占据了一部分的缓冲池空间,减少了可以用来缓存数据页面的内存.如果工作完全适合于缓冲池或者你的表里二级索引很少,关闭变更缓存区可能更有用.如果工作中的数据集(的大小)完全适合缓冲池,变更缓存区不会增加额外的开销,因为它只适用于不在缓冲池的页面.

你可以使用innodb_change_buffering参数来控制InnoDB对变更缓存区的处理.你可以打开或关闭对插入,删除操作和清除操作的缓存.更新操作被分解为插入和删除操作.默认的innodb_change_buffering值是all.

合法的innodb_change_buffering参数如下:

all
默认值:缓存插入,标记删除操作和清除(purges)
none
不缓存任何操作
inserts
缓存插入操作
deletes
缓存标记删除操作
changes
缓存插入和标记删除
purges
缓存发生在后台的物理删除操作

通过MySQL配置文件(my.cnf 或my.ini)的innodb_change_buffering参数或者SET GLOBAL语句设置(需要权限).更改设置只会影响新的缓冲,现有的缓冲数据不受影响.

设置变更缓存区的最大大小

innodb_change_buffer_max_size变量可以设置变更缓存区占缓冲池的百分比大小.默认innodb_change_buffer_max_size是25,最大可以设置50.

考虑增加innodb_change_buffer_max_size如果服务器有大量的insert,update和delete操作.

考虑减少innodb_change_buffer_max_size如果服务器使用静态数据来做报告.

在典型的工作负载下测试不同的设置来决定使用哪一个配置.innodb_change_buffer_max_size可以在不重启服务器的情况下动态修改.

监控变更缓存区

标准监视器:

1	mysql> SHOW ENGINE INNODB STATUS\G

信息如下:

-------------------------------------
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 0Hash table size 4425293, used cells 32, node heap has 1 buffer(s)13577.57 hash searches/s, 202.47 non-hash searches/s

INFORMATION_SCHEMA.INNODB_METRICS表里提供了InnoDB标准监视器里的大部分数据,还有额外的数据.使用下面的sql查看变更缓存区的指标:

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
INFORMATION_SCHEMA.INNODB_BUFFER_PAGE提供了缓冲池里每个页面的元数据,包括变更缓存区索引和变更缓存区位图页面.变更缓存区由PAGE_TYPE标识,IBUF_INDEX是变更缓存区索引页面的页面类型,IBUF_BITMAP是变更缓存区位图页的页面类型.

警告:
查询INNODB_BUFFER_PAGE表会带来显著的性能损耗.为了避免性能影响,请在测试实例上运行查询和排除问题.
例如,你可以查询INNODB_BUFFER_PAGE表来决定IBUF_INDEX和IBUF_BITMAP页面占总缓冲池页的百分比.

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

性能概要(Performance Schema)提供变更缓存区互斥等待监控表进行高级性能监控.使用下面的查询:

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

14.5.3 自适应哈希索引(Adaptive Hash Index)

自适应哈希索引特性使得InnoDB在合适的工作负载和提供给缓冲池(buffer pool)足够的内存的情况下表现的更像一个内存型数据库并且不会损失事务特性和可靠性.自适应哈希索引特性由 innodb_adaptive_hash_index变量来控制,或者是在启动服务器时加上--skip-innodb-adaptive-hash-index.

根据观察到的搜索模式,使用索引键(index key)的前缀来构建哈希索引.前缀可以是任意长的,并且有可能只是B树中的一些值出现在哈希索引里.哈希索引是根据需要经常访问的页面索引构建的.

如果一张表几乎全部进入主存,哈希索引可以通过允许直接查找任何元素来加速,把索引的值转换为一种指针.InnoDB有一种监控索引搜索的机制.如果InnoDB注意到查询可以从构建哈希索引中受益就会自动进行(这个过程).

对于一些工作来说,从哈希索引获得的加速远远超过了监控索引查找和维护一个哈希索引结构的速度.在高工作负载下,对自适应哈希索引的访问有时会变成资源竞争的来源,例如多个连接的joins.LIKE操作和%符号的查询也不会受益.对于不能从自适应哈希索引中获益的工作,关闭以减少不必要的性能消耗.因为很难预测对一个特定的系统来说自适应哈希索引特性是否有用,考虑使用基准测试来比较开启和不开启的性能差距.MySQL5.6的架构变化使其更适于禁用自适应哈希索引相比于更早的版本来说.

在MySQL5.7中,自适应哈希索引特性是分区的.每一个索引绑定到一个特定的分区,每一个分区都由一个锁存器来保护.分区由innodb_adaptive_hash_index_parts来控制.在早期版本中,自适应哈希索引由一个锁存器来保护,这可能会成为高负载下的一个竞争点.innodb_adaptive_hash_index_parts默认值是8,最大可以设置512.

可以通过SHOW ENGINE INNODB STATUS的SEMAPHORES部分来监控自适应哈希索引.如果有大量的线程在等待btr0sea.c创建RW-latches,考虑增大自适应哈希索引分区的数量或者关闭自适应哈希索引特性.

日志缓冲区(Log Buffer)

日志缓冲区是保存要写到磁盘上的日志文件的区域.日志缓冲区的大小由innodb_log_buffer_size定义.默认的大小是16MB.日志缓冲区的内容会定期刷新到磁盘上.一个大的日志缓冲区使大型的事务可以运行而无需在事务提交之前把重做日志(redo log)的数据写到磁盘上.因此,如果你有事务更新,插入或删除很多行的话,增大日志缓冲区来节省磁盘I/O.

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

记一次从阿里云rds备份单库到服务器

发表于 2019-02-16 更新于 2024-11-18 分类于技术

问题产生

在线上主从同步的slave数据库中产生了如下的错误：

1
2

ERROR 1787 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and 
DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.

使用show slave status可以查看从库当前的同步状态,发现I/O状态已经变为NO,尝试使用:

1	set global sql_slave_skip_counter = 1;

之后产生如下的问题:

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

根据错误说明使用一个空事务来跳过异常,代码如下:

STOP SLAVE;
SET GTID_NEXT=# binlog中事务的下一个位置;
BEGIN; COMMIT; # 产生一个空事务并提交
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

然而这样会导致主从的数据可能产生不一致的情况,这是我们想要避免的,并且希望主库可以不锁表导出数据即可进行备份.

Xtrabackup

查看阿里云的官方文档:RDS for MySQL 物理备份文件恢复到自建数据库发现可以使用Xtrabackup来进行恢复.

操作系统中已安装数据恢复工具Percona XtraBackup。MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3。MySQL 5.7版本需要安装 Percona XtraBackup 2.4。可以从Percona XtraBackup官网下载安装，安装指导请参见官方文档 Percona XtraBackup 2.3、Percona XtraBackup 2.4。

Xtrabackup的单表恢复

但是上面的官方文档中仍旧有一个问题,在文档中写到:

说明由于软件限制，目前只支持将云数据库MySQL的备份文件恢复到安装在Linux系统中的自建MySQL数据库中。

而在恢复数据的时候,我们想要只对业务用到的数据库进行恢复,mysql的配置信息库不希望恢复到从库中去(由于主从用户信息等都不相同).也就是说从备份的源上无法进行库的指定(Xtrabackup支持表的指定但是阿里云RDS没有这个选项).

在查看了Xtrabackup网站上的教程之后,发现Xtrabackup可以单独对一张表进行恢复,即在阿里云文档的:

## 解包
cat <数据备份文件名>_qp.xb | xbstream -x -v -C /home/mysql/data
## 解压
innobackupex --decompress --remove-original /home/mysql/data

操作也就是解压了需要同步的数据之后:

创建新表

使用sql语句创建对应需要恢复的表(没有数据库就先创建数据库):

-- 官方例子中用到的表
mysql> CREATE TABLE `name_p4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`imdb_index` varchar(12) DEFAULT NULL,
`imdb_id` int(11) DEFAULT NULL,
`name_pcode_cf` varchar(5) DEFAULT NULL,
`name_pcode_nf` varchar(5) DEFAULT NULL,
`surname_pcode` varchar(5) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2812744 DEFAULT CHARSET=utf8

删除表空间

1	mysql> ALTER TABLE name_p4 DISCARD TABLESPACE;

拷贝数据

1
2
3

# 将对应的数据库的对应的表(备份数据目录下)拷贝到mysql文件夹下同数据库的同表目录下
$ cp /mnt/backup/2012-08-28_10-29-09/imdb/name#P#p4.exp /var/lib/mysql/imdb/name_p4.exp
$ cp /mnt/backup/2012-08-28_10-29-09/imdb/name#P#p4.ibd /var/lib/mysql/imdb/name_p4.ibd

导入表空间

1	mysql> ALTER TABLE name_p4 IMPORT TABLESPACE;

导入指定的库

知道了上面的对单表导入的原理,那么对指定的库的数据导入过程其实就是重复导入表的过程直至全部的表都恢复到库中为止.

mysqlfrm介绍

mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。在大多数情况下，生成的CREATE语句用于在另一个服务器上创建表或进行诊断等。

我们使用mysqlfrm来生成数据表创建语句.

恢复单库的脚本

#!/bin/bash
# 注意:只能用在InnoDB表引擎的数据库上
# Usage: inno_restore_database.sh <db_name_to_restore_to> <db_backup_directory> 末尾不需要 /

# mysql数据库磁盘位置
datadir=/var/lib/mysql
myport=3306
# 随意指定
mysqlfrmport=3310
#username=dbrestoredude
#password=dbrestoredudespasswd

#######################################################################################
# Gotta be root.
if [ $UID -ne 0 ]; then echo "Run this as root" ; exit ; fi

database=$1
restoredir=$2

# mysqlfrm检查
which mysqlfrm > /dev/null 2>&1
if [ $? -ne 0 ] ; then echo "Could not find the mysqlfrm utility. Install mysql-utilities."; exit ; fi
# mysql目录检查
if [ ! -f $datadir/mysql/user.frm ] ; then echo "MySQL datadir not correct" ; exit ; fi
# Check the restore directory, looking for a cfg/exp/idb for each frm.
if [ ! -f $restoredir/db.opt ] ; then echo "Restore directory invalid, couldn't find db.opt in it"; exit ; fi
stoperror=0
for restorename in $restoredir/*.frm
do
    chkname=$(echo $restorename|sed s/.frm$//)
    for exten in cfg exp ibd
    do
        if [ ! -f $chkname.$exten ] ; then stoperror=1 ; fi
    done
done
if [ $stoperror -eq 1 ] ; then
    echo "Could not file valid restore directory files (need a cfg, exp and ibd for each frm)"
    echo "Did you specify a valid database directory within a backup?"
    echo "Did you prepare or apply-log to the backup directory?"
    exit
fi

# Get username and password if the fields are blank.
if [ -z $username ] ; then read -p "Username: " username ; fi
if [ -z $password ] ; then read -s -p "Password: " password ; echo ; fi
# Check mysql permissions for the given user.
stoperror=0
grants=$(mysql -B -u $username -p$password mysql -e "show grants for current_user"|grep 'ON *.* TO')
if [ $? -ne 0 ] ; then exit ; fi
if [[ $grants == *"ALL PRIVILEGES"* ]] ; then stoperror=1 ; fi
if [[ $grants == *CREATE* ]] && [[ $grants == *DROP* ]] && [[ $grants == *ALTER* ]] ; then stoperror=1 ; fi
if [ $stoperror != 1 ] ; then echo "User $username does not have global CREATE, DROP and ALTER" ; exit ; fi

################################################################################
# 删除存在的数据库
mysql -B -u $username -p$password -e "DROP DATABASE IF EXISTS $database"
# 创建数据库
mysql -B -u $username -p$password -e "CREATE DATABASE $database"
# get directory for the original DB name
backupdb=$(find $restoredir -maxdepth 0 -type d -printf "%f\n" |cut -d '/' -f 1)

# 使用mysqlfrm来生产建表语句并且执行 流程:
# mysqlfrm生成语句-管道->mysql
# | sed s/'ENGINE=InnoDB' / 'ENGINE=InnoDB ROW_FORMAT=compact' | 用来指定行格式
echo "Importing create table statements from frm files..." ; echo
mysqlfrm -q --user=root --server=$username:$password@localhost:$myport --port=$mysqlfrmport $restoredir |
    grep -vE "^#|WARNING: Using a password on the command line interface can be insecure." |
    sed s/^$/';'/ | sed s/^'CREATE TABLE `'$backupdb/'CREATE TABLE `'$database/ |
    mysql -B -u $username -p$password
echo "Table structure imported."

# 主要代码,循环执行删除表空间,移动文件,恢复表空间
for frmname in $restoredir/*.frm
do
    tablename=$(find $frmname -printf "%f\n"|sed s/.frm$//)
    # ALTER TABLE ... DISCARD TABLESPACE
    mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename DISCARD TABLESPACE"
    for exten in exp ibd #cfg :: exp ibd cfg 文件取决于数据库版本
    do
        which rsync > /dev/null 2>&1
        if [ $? -eq 0 ]
        then
            rsync --progress $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten
        else
            \cp -v $restoredir/$tablename.$exten $datadir/$database/$tablename.$exten
        fi
        chown $(find $datadir/$database/$tablename.frm -printf "%u.%g") $datadir/$database/$tablename.$exten
    done
    # ALTER TABLE ... IMPORT TABLESPACE
    mysql -B -u $username -p$password $database -e "ALTER TABLE $tablename IMPORT TABLESPACE"
done
echo "All done"

注意

一开始由于部分表仍旧使用MyISAM引擎导致行格式不一致而使得表空间更改语句不生效,而MyISAM表也不支持compact,所以从库全部使用的InnoDB作为存储引擎.
如果数据库用户配置比较复杂,可以优先尝试mysql相关命令是否生效,不生效则进行调整.

启动主从同步

在启动之前,需要设置GTID相关信息(仅限于自己的生产服务器,文档中没有提到):

slave1> reset master;
slave1 > show global variables like 'GTID_EXECUTED';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed |       |
+---------------+-------+
slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
slave1> start slave io_thread;
slave1> show slave status\G
[...]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[...]

然后使用CHANGE MASTER语句来进行主从同步设置.

参见How to create/restore a slave using GTID replication in MySQL 5.6

参考连接

xtrabackup restore specific database from a full backup(单库的恢复方法)

MySQL管理工具MySQL Utilities — mysqlfrm

Backing Up and Restoring Individual Partitions