[MySQL Reference Manual]14 InnoDB存储引擎

从之前版本升级MySQL 5.5之后，还是要检查InnoDB是不是能够正常在被程序使用。使用参数—default-storage-engine=Innodb设置默认存储引擎。设置之后只会影响后来新建的表。测试应用程序可以正常运行，确定读写正常，如果有依赖myisam特性可能会失败。如果想要知道老表在innodb运行的情况，可以使用alter table table_name ENGINE=InnoDB。

或者

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;

14.1.1.7 验证InnoDB是默认存储引擎

检查InnoDB状态：

1.使用SHOW ENGINE查看所有MySQL所有的引擎，查看DEFAULT列。

2.如果没有InnoDB说明编译的时候，没有加InnoDB。

3.如果InnoDB存在但是不可用，回到配置文件或者配置，去掉skip-innodb选项。

14.1.2 检查InnoDB可用

使用SHOW ENGINE查看InnoDB是否存在来决定InnoDB是否可用。

14.1.3 关闭InnoDB

如果你不要使用InnoDB：

1.使用—InnoDB=OFF或者—skip-innodb选项禁用innodb存储引擎

2.因为innodb是默认的存储引擎，所以启动报错，需要设置—default-storage-engine和—default-tmp-storage-engine，

3.为了让服务在查询information_schema innodb相关的表是不会奔溃，还需要配置一下参数

loose-innodb-trx=0

loose-innodb-locks=0

loose-innodb-lock-waits=0

loose-innodb-cmp=0

loose-innodb-cmp-per-index=0

loose-innodb-cmp-per-index-reset=0

loose-innodb-cmp-reset=0

loose-innodb-cmpmem=0

loose-innodb-cmpmem-reset=0

loose-innodb-buffer-page=0

loose-innodb-buffer-page-lru=0

loose-innodb-buffer-pool-stats=0

loose-innodb-metrics=0

loose-innodb-ft-default-stopword=0

loose-innodb-ft-inserted=0

loose-innodb-ft-deleted=0

loose-innodb-ft-being-deleted=0

loose-innodb-ft-config=0

loose-innodb-ft-index-cache=0

loose-innodb-ft-index-table=0

loose-innodb-sys-tables=0

loose-innodb-sys-tablestats=0

loose-innodb-sys-indexes=0

loose-innodb-sys-columns=0

loose-innodb-sys-fields=0

loose-innodb-sys-foreign=0

loose-innodb-sys-foreign-cols=0

14.2 InnoDB概念和体系结构

14.2.1 Mysql和ACID模型

ACID模式是数据库设计的标准的集合。

A: atomicity原子性

C:consistency 一致性

I:isolation 隔离性

D:durability 持久性

原子性

原子性主要由InnoDB事务来保持

一致性

一致性方面 ACID主要设计InnoDB内部处理来保护数据。

1.InnoDB双写 buffer

2.InnoDB灾难恢复

隔离性

ACID的隔离性的模型，主要涉及InnoDB事务在指定隔离级别上的执行。

1.自动提交设置

2.set isolation level隔离级别

3.低级InnoDB锁，可以通过INFORMATION_SCHEMA查看。

持久性

ACID的持久性，涉及mysql和指定的硬件的交互，

1.innodb 双写

2.innodb_flush_log_at_trx_commit配置参数

3.sync_binlog 配置参数

4.innodb_file_per_table配置参数

5.存储设备的，write buffer

6.存储设备battery-backed cache

7.运行MySQL的操作系统，特别是支持fsync系统调用的

8.UPS支持的环境

9.备份策略

10.硬件设备和网络连接

14.2.2 InnoDB事务模式和锁

在InnoDB事务模式，目标是把高性能的多版本的数据和2阶段锁定结合。InnoDB锁定在行级别并且查询默认以nolocking一致性执行。和Oracle一样。Lock信息在InnoDB是空间高效实用的。锁不需要创建。也不会照成内存压力。

InnoDB所有用户行为都在事务内。若打开了自动提交，每个SQL都是一个事务。默认会话都是自动提交的，若出错会根据错误回滚。自动提交的会话，可以通过start transaction 或者begin语句，以commit或者rollback结束，启动一个事务。通过set autocommit=0取消自动提交。

默认InnoDB的隔离级别是REPEATABLE READ，InnoDB提供了4个隔离级别，READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ和SERIALIZABLE。

用户可以通过SET TRANSACTION语句设置隔离级别，使用—transaction-isolation选项来设置默认隔离级别。

对于行级锁，InnoDB通常使用next-key锁定。也就是说除了索引记录，InnoDB还可以lock页内空隙，不让其他会话插入数据。Next-key lock是锁定了index record和它之前的gap。

14.2.3 InnoDB锁定模式

InnoDB实现标准的行锁有2中强度类型，S锁和X锁。粒度类型有3类，record锁，gap锁，next-key锁。

14.2.3.1 意向锁

InnoDB可以支持多粒度锁定允许，就引入了意向锁。

IS:事务T意向在表上使用S锁。

IX:事务T以上在表上使用X锁。

意向锁协议：

1.为了获取在行上获取S锁，就必须在表上获取IS锁。

2.为了获取行上的X锁，必须在表上限获取IX锁。

Lock兼容表

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

14.2.3.2 死锁例子

略

14.2.4 一致性无锁读

一致性读的意思是，InnoDB使用多版本来生产查询结果的快照。查询只能看到事务启动时，已经提交完的事务修改。有个异常就是查询可以看到事务内前面语句的修改内容。这个异常会导致一个情况，在其他事务刚好也更新了同样的表，这种情况下，你看到的表状态重来就没出现在数据库过。

如果隔离级别是所有的一致性读，从第一个读开始。

使用READ COMMIT隔离级别，每个事务中的一致性读来至于自己刷新的快照。

不管是READ COMMIT,REPEATABLE READ都默认使用一致性读。一致性读不会锁定表访问，所以其他会话可以同时更新这些表。

假设你默认是REPEATABLE READ隔离级别。当查询数据是会给一个时间点，当行被其他事务删除，在查询不会发现行被删除。

Note

快照在事务内有效。尽管查不到数据，但是update，delete还是可以修改这些数据库的。

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz'; -- Returns 0: no rows match.

DELETE FROM t1 WHERE c1 = 'xyz'; -- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc'; -- Returns 0: no rows match.

UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc'; -- Affects 10 rows: another txn just committed 10 rows with 'abc' values.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba'; -- Returns 10: this txn can now see the rows it just updated.

如果要推进快照点，可以执行事务提交，再执行另一个select或者START TRANSACTION WITH CONSISTENT SNAPSHOT。

如果想要看最新的数据库状态，可以使用read committed，或者锁定读。

SELECT * FROM t LOCK IN SHARE MODE;

使用READ COMMITTED隔离级别，事务里面的每次一致性读都是独自己刷新的快照。使用LOCK IN SHARE MODE,发生读锁。

一致性读不能覆盖DDL语句,insert into select ,update…(select)，create table select都不能指定for update或者 lock in shared mode。

14.2.5 锁读

在事务里select不会被保护，其他事务可以同时修改查询的值。Innodb提供额外的锁读，提供安全性：

1.Select … lock in share mode设置

2.select … for update

使用以上标记的锁，到事务完成时才能释放。

14.2.6 InnoDB Record，Gap，Next-key锁

InnoDB有几类锁：record lock，Gap lock，Next-key lock

14.2.6.1 Record Lock

Record Lock锁定Index记录，不管有没有定义索引。

14.2.6.2 Next-Key Lock

默认，InnoDB使用REPEATABLE READ隔离级别并且innodb_locks_unsafe_for_binlog不可用。Next-key lock查询可以防止幻读出现。

Next-key是index-row和gap的组合。Next-key锁定record和，record之前的gap。若会话共享，独占锁定了R，那么另外一个就不能再R之前插入数据。

14.2.6.3 Gap Lock

Gap lock不会使用在使用唯一索引查询上。

有一种gap lock类型被称为插入意向gap lock，由Insert操作设置。这个锁表示尝试插入。多事务插入到同一个index gap就会被堵塞。

使用read commit或者 innodb_locks_unsafe_for_binlog来取消gap lock。

14.2.7 使用Next-key锁定避免幻影

幻影问题，事务内相同查询在不同时间运行，返回的数据不一样。

如：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

如果不加gap锁，就可以插入101，那么再次使用查询，就会出现幻影行。

为了避免幻影问题，使用next-key lock锁定index record和gap。

14.2.8 不同语句的锁定设置

锁读，update，delete，当扫描是，会对所有的扫描到的index record上锁。并不会理睬是不是在where条件之外。InnoDB只知道要扫描的Index Range。

InnoDB指定lock类型：

1.select from 一致性读不加锁,除非在SERIALIZABLE隔离级别

2.select from lock in share mode 共享锁读，next-key lock。

3.select from for update 排他锁读

4.update where排他的next-key lock

5.delte from where排他next-key lock

6.insert在插入的行上设置排他锁，是record锁，不是next-key锁。

如果发生重复键错误，那么index record会设置共享锁，当多个事务同时获得共享锁，就有可能会出现死锁。

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations in order:

Session 1:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;

INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

当第一个在会话1执行，那么会话2和会话3会报主键冲突，会共享锁。当会话1释放。会话2，会话3同时去获取排他锁。就会导致2,3死锁。

7.Insert on duplicate key upate，和insert不同，当出现重复键错误，或使用x锁，不是s锁。

8.replace当在没有冲突的情况下，和insert一样。如果有冲突获取next-key x锁，替换行。

39insert into t select * from s where，会在T中设置record x锁。若隔离级别是read committed或者设置了innodb_locks_unsafe_for_binlog，并且不是SERIALIZABLE否则innodb设置共享next-key锁在S表上。

Create table select,insert select都会申请共享next-key锁。如果表在结构replace into t selct from s where 或者update t where col in(select from s)innodb都会在s上设置共享next-key锁。

10.在初始化auto_increament时，会在相关的索引上加上x锁。在访问自增counter时，使用auto-inc锁，语句执行完就释放。

11.如果表上定义了外键，任何写入操作都需要去检查行，需要设置共享行锁查看。

12.lock tables设置表锁

Innodb自动死锁诊断，不会发现设计到table locks的死锁。因为table locks是mysql层的不知道innodb的行级别锁。

14.2.9 事务提交和回滚的影响

默认MySQL启动会话，以自动提交方式提交事务。如果语句返回错误，会根据错误处理commit和rollback。

如果会话取消了自动提交，没有显示提交，最终会回滚。一些语句会影响事务提交，在commit执行的话，会导致影响事务提交。

14.2.10 死锁发现和回滚

InnoDB自动诊断发现事务死锁回滚事务或者打破事务回滚。

如果配置innodb_table_locks=1默认并且autocommit=0 InnoDB会认识table locks否则不认识。

在InnoDB中一个事务rollback，所有的锁都会被释放。然而对于单个语句因为出错rollback，可能锁会被保留，因为InnoDB不知道锁是属于哪个语句的。

如果select调用了用户函数在事务中，语句出错，语句被回滚。

14.2.11 如何处理死锁

死锁是事务数据库的典型问题，除非经常发生，不然不是问题。在写程序的时候要进行重试。

InnoDB使用自动行锁。在单行写入也可能发生死锁，因为不是真的原子性。他们自动在index record上设置锁。

可以使用如下方法来减少死锁：

1. 任何时候，可以使用show engine innodb status来确认最近死锁。

2. 使用innodb_print_all_deadlocks，把错误信息print到error log上。

3. 发生错误的时候要重试

4. 完成修改后，立马提交

5. 如果使用锁读，使用比较低的隔离级别，比如read committed

6. 当修改不同的表，或者不同的行集，使用一致的顺序。

7. 增加合适的索引。

8. 减少锁，如果可以使用老的快照，就不要使用锁读。

9. 如果还是没有缓减，使用串行事务，表级锁。

10. 另外一个方式是创建一个辅助的信号量表。事务update先要访问信号量表。

14.2.12 InnoDB多版本

InnoDB是多版本存储引擎，保存了老的修改的行。支持并发和回滚。这些数据被保存在rollback segment。InnoDB使用在rollback segment的信息来执行undo。

14.2.12.1 多版本内部细节

内部，InnoDB为每个行存储增加了3个列。6个字节的DB_TRX_ID表示最后写入的事务ID，7字节DB_ROLL_PTR roll指针，roll指针指向rollback segment中undo log record。6字节的DB_ROW_ID，包含rowid，rowid是单调递增的。

undo log在rollback segment被分为insert，和update undo log。insert undo log只在回滚时需要commit才能释放。update undo logs也可以用在一致性读上，只有在事务没有使用的时候才会消失。

14.2.12.2 Rollback Segment管理指导

InnoDB不能从update undo log中放数据，回滚段会变得很大。undo log记录在回滚段是一般比insert和update的行小。

在InnoDB多版本结构，一个行不会在删除后，马上物理上删除。只有当取消update undo log记录后才会在物理上删除。删除操作被称为清空，通常清空的顺序和sql顺序一样。

如果insert和delete行。

如果你插入和删除行在小批处理。清理现场会滞后会越来越大导致变慢。这时，可以使用innodb_max_purge_lag参数来调整清理操作。

14.2.13 InnoDB表和索引结构

14.2.13.1 .frm文件在innodb表的角色

MySQL以.frm文件方式，把数据目录保存在数据库目录中。InnoDB也会把表的元数据信息存放在表空间中。当MySQL drop表或者数据库，会删除一个或者多个.frm文件。但也不能通过移动.frm文件来移动表。

14.2.13.2 聚集和secondary索引

每个InnoDB表有一个特别的聚集索引，聚集索引和primary key同义。

1. 当你定义了primary key，InnoDB会作为聚集索引。

2. 如果没有定义primary key，MySQL会使用unique，并且非null的列作为聚集索引

3. 如果表没有primary key和unique索引，InnoDB自动生成一个隐藏聚集索引聚合列和rowid。

14.2.13.2.1 聚集索引加快查询速度

如果通过访问聚集索引访问一行很快是因为开销索引查询可以直接访问到有所有数据的page。如果表很大，通过聚集索引访问会减少I/O。

14.2.13.2.2 聚集索引和secondary关联

除了聚集索引外，其他都是secondary索引，InnoDB secondary 索引，都包含了primary key的列。如果primary key很长，secondary就需要更多的空间，所以建议primary key比较短。

14.2.13.3 FULLTEXT索引

特别的索引，FULLTEXT索引，帮助innodb处理关于基于文字的查询。全文索引可以使用create table，alter table，create index创建。 MATCH() ... AGAINST 语法查询。

14.2.13.4 索引的物理结构

所有InnoDB所以是b树结构，索引的page为16KB，当新的记录插入，InnoDB试图保留1/16的空间用于未来的插入和修改。

如果index record是顺序插入的，page为15/16，若是随机插入的1/2~15/16。当page低于1/2，page会试图调整释放page。

Note

page的大小可以通过参数innodb_page_size制定。

14.2.13.5 Insert Buffering

数据库应用程序总是以主键自增的循序插入新行，因为聚集索引和primary key顺序一样，插入table不需要做随机I/O。

secondary index是不唯一的，insert到secondary顺序是随机的。删除和update会影响数据页是随机的。

当索引被删改，innodb会检查page是否在buffer pool。如果在，会直接修改index page，如果不在，InnoDB会记录到insert buffer中。insert buffer会保持的很小，可以放到buffer pool，修改会快速修改。这个进程被称为change buffering。

定期，insert buffer会合并到secondary index上，通常吧多个修改合并到一个page，来减少I/O操作。

insert buffer在事务提交后，会合并到insert buffer。当很多索引要被delete和update，insert buffer合并会话很长时间。这个时候磁盘I/O会增长，会导致依赖于磁盘查询延迟。另外一个重要I/O操作时清理线程。

14.2.13.6 自适应hash索引

自适应hash索引让innodb很像内存数据库，适应负荷和内存。不会牺牲任何事物特性和可靠性，使用innodb_adaptive_hash_index来启动和禁用。

根据查询部分，mysql创建一个hash索引使用固定的索引键。固定键的长度可以是任意长度的。只有btree的一些值会出现在hash索引上。hash索引只会的page进行访问。

当表整个放入住内存，hash index可以加快访问速度。innodb有机制可以监控索引访问。如果InnoDB觉得查询可以从创建hash index后获得好处，就会自动创建。

在某些情况下，hash索引查找提高的速度远远大于额外的监控的和维护hash索引结构。有时候，read/write锁守护数据在搞负荷情况下，会变成争用焦点。

你可以使用show engine innodb status的semaphores段来监控hash index。若看到大量的RW-latch在btr0sea.c被创建，可能取消自适应hash索引更好。

14.2.13.7 物理行结构

物理行结构依赖于InnoDB表依赖于表创建时候的row format。可以使用show table status检查。

14.3 InnoDB配置

InnoDB表空间和日志文件概述

2个重要的磁盘管理的资源是，InnoDB的数据文件和日志文件。如果没有InnoDB选项。MySQL创建自动扩展的文件大于12MB，ibdata1和2个日志文件ib_logfile0,ib_logfile1。大小由innodb_log_file_size系统参数。

考虑存储设备

把log文件放到不同的磁盘可以提升性能。也可以使用原始的磁盘分区作为innodb数据文件，可能可以提升性能。

指定innodb表空间位置和大小

innodb_data_file_path选项指定了数据文件，使用分号隔开。

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

以下设置显示的创建最小系统表空间

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend

若指定了autoextend InnoDB会自动扩展。max指定文件最大限制

[mysqld]

innodb_data_file_path=ibdata1:12M:autoextend:max:500M

innodb_data_home_dir指定了表空间文件的路径

[mysqld]

innodb_data_home_dir = /ibdata

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Note

innodb不会创建目录所以要保证，目录存在。并且保证有权限。

innodb_data_home_dir默认值为mysql 数据目录。

也可以直接在innodb_data_file_path上指定文件路径。

创建InnoDB系统表空间

当创建系统表空间时，最好使用命令行启动。

C:> "C:Program FilesMySQLMySQL Server 5.6inmysqld" --console

14.3.1 配置InnoDB为只读

如果MySQL数据目录在只读媒体中，带—innodb-read-only来启动服务。就可以查询了。

14.3.1.1 如何启动

准备只读操作，首先要保证数据被刷新到数据文件中。然后取消change buffer innodb_change_buffering=0，使用slow shutdown。使用参数—innodb-read-only =1。

14.3.1.2 使用场景

1.有一部分数据在只读介质中

2.多个MySQL实例并发查询相同的数据目录

3.因为数据安全或者数据集成的需要设置为只读

14.3.1.3 如何工作

如果MySQL启动了—innodb-read_only选项，InnoDB会关闭一些特性和组件：

1.没有启动change buffer

2.启动阶段没有carsh recovery

3.因为只读不需要redo log，可以把redo log 设置到最小

4.素有后台IO除了IO都可以关闭

5.deadlock，monitor输出等等都不会被写入临时文件。Show engine innodb status不会输出任何信息。

6.如果Mysql以—innodb-read-only启动，如果数据目录放在读写上，还是可以运行DCL命令grant，revoke。

7.修改配置选项可以修改写入操作，当read-only时，没有影响。

8.MVCC强制隔离级别被关闭

9.undo log不能使用。

14.4 InnoDB管理

InnoDB管理设置到很多方面：

1.管理数据文件表示系统表空间，innodb表和其他相关的索引

2.管理redo log文件

3.尽量使用innodb

4.性能相关的通用管理

14.5 InnoDB表空间管理

启动前，先检查innodb数据文件存在，并且有足够的权限访问。第一次启动使用命令行比较好。因为会把信息都print到命令行上。

14.5.2 InnoDB File-Per-Table模式

File-Per-Table的好处：

1.当表删除或者truncate，空间可以被OS回收。

2.Truncate table在单个.ibd文件上执行更快。

3.可以为每个表指定一个特定存储。优化IO，空间管理。CREATE TABLE ... DATA DIRECTORY =absolute_path_to_directory。

4.运行OPTIMEIZE TABLE，压缩或者重建创建表空间。运行OPTIMIZE TABLE InnoDB会创建一个新的ibd文件。当完成时，老的表空间会被新的代替。

5.可以移动单个表，不需要移动整个数据库

6.可以把表复制到另外一个实例

7.innodb_file_per_table启动后才能使用Barracuda文件格式。

8.可以更有效的存储带BLOB，TEXT使用动态行模式的表。

9.使用innodb_file_per_table可以提高recovery的成功率，减少损坏错误发生恢复的时间。

10.可以快速的备份，恢复单个表。

11. innodb_file_per_table 可以从备份中去除一个表

12. innodb_file_per_table在备份和复制表时，容易观察每个表的状态。

13.可以通过文件系统直接观察表的大小。

14.当innodb_flish_method设置为O_DIRECT，通常linux文件系统不允许并发的写入同一个文件。使用innodb_file_per_table就会有性能提升。

15.不启用innodb_file_per_table，数据都会放在系统表空间中，最大64TB，如果使用innodb_file_per_table每个表可以64TB。

File-Per-Table一些缺点：

1.表空间中的空间只能被这个表使用

2.fsync操作必须在每个表上都运行一遍

3.mysqld必须保持一个打开的文件句柄，表太多会影响性能。

4.会消耗很多文件描述

5. innodb_file_per_tablezhiyou 5.6.6或更高版本才能用，有向下兼容问题。

6.如果很多表都增长，会出现文件碎片问题。导致drop 表和表扫描性能下降。

7.当drop表的时候会扫描buffer pool，如果太大会比较耗时。

8.innodb_autoextend_increment指定当文件满了之后增长的空间。

14.5.3 启动禁用File-Per-Table模式

File-Per-Table启动禁用方式：

设置配置文件或者全局变量：innodb_file_per_table。

启动innodb_file_per_table，InnoDB会吧数据存放在ibd文件中，和MyISAM不通，MyISAM存放在tbl_name.myd,tbl_name.myi。

InnoDB的数据和索引都放在ibd文件中，frm文件依然还是会创建。如果关闭innodb_file_per_table那么InnoDB的表都会创建在系统表空间中。

通过以下方法可以移动innodb到自己的表空间：

-- Move table from system tablespace to its own tablespace.

SET GLOBAL innodb_file_per_table=1;

ALTER TABLE table_name ENGINE=InnoDB;

-- Move table from its own tablespace to system tablespace.

SET GLOBAL innodb_file_per_table=0;

ALTER TABLE table_name ENGINE=InnoDB;

14.5.4 指定TableSpace的路径

启动了innodb_file_per_table，可以再创建表的时候指定文件路径。具体看create table。

这个位置很重要，因为之后不能通过alter table修改。在数据目录上，MySQL在目标目录上创建一个以数据库命名的子目录，里面存放新的ibd文件。在DataDir上创建一个tbl_name.isl文件，包含了这个文件的路径。这个文件相当于symbolic link。

Note

1.MySQL初始化的时候会把ibd文件保持打开装填，以防止设备被unmount。不要在mysql运行的时候unmount，不要在unmount的时候启动服务。

2.不要把Mysql表放在NFS mounted卷上。NFS使用消息来修改文件，如果消息不对，可能会影响数据一致性。

3.如果使用LVM快照，file copy，或者基于文件的备份，先执行FLUSH TABLS FRO EXPORT.

4.data directory子句可以使用symbolic link来代替。

14.5.5 把表空间复制到另外一个服务上

表空间复制的限制和注意点

1.表空间复制过程只有当innodb_file_per_table启用的时候才能用。

2.只有读操作可以再该表上使用。

3.导入时，page size必须和被导入实例上的一致。

4.DISCARD TABLESPACE不支持分区表。

5. DISCARD TABLESPACE有主外键约束的也不支持，在discard之前要先设置foreign_key_checks=0。

6.alter table import tablespace不会强制外键约束

7.alter table import tablespace 不需要cfg文件，但是当没有cfg文件的时候导入时不会检查元数据。

8.MySQL 5.6之后，如果2个服务都是GA状态，并且在一个系列里面，可以导入。否则不许先在没导入服务上创建文件。

9.在windows上innodb，表空间，表名都是小写，为了避免这个问题，要注意linux，unix上的大小写。

例子：移动表空间到另外一个服务上

1. 源服务器上创建创建表:

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

2. 在目标服务器上创建表，如果不存在:

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

3. 目标服务器上discard表

mysql> ALTER TABLE t DISCARD TABLESPACE;

4. 源服务器上运行 FLUSH TABLES ... FOR EXPORT 限制表，并创建了cfg文件:

mysql> use test;

mysql> FLUSH TABLES t FOR EXPORT;

cfg文件被创建在数据目录上.

5. 复制ibd和cfg文件:

shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

6. Unlock 表释放flush table for export的锁:

mysql> use test;

mysql> UNLOCK TABLES;

7. 导入到目标服务:

mysql> use test;

mysql> ALTER TABLE t IMPORT TABLESPACE;

表空间复制内部细节

当运行alter table discard tablespace，目标服务：

1.表会被X锁

2.表空间会被分离

当运行flush tables for export：

1.表被S锁

2.清理线程停止

3.脏数据被同步到磁盘

4.表元数据生产到cfg文件

当运行unlock tables

1.cfg被删除

2.s锁被释放，清理线程启动

当运行alter table import tablespace：

1.检查表空间page是否损坏

2.空间Id和LSN被更新

3.检查flag，LSN被更新

4.b树被更新

5.page设置为dirty，让数据库可以写入磁盘。

14.5.6 把undo log移出System表空间

你可以把undo log分离到独立的表空间文件。详细看14.13.16

14.5.7 修改InnoDB日志文件，增长InnoDB表空间

MySQL 5.6.8之后，修改innodb日志文件和大小：

1.没有错误下，关闭服务。

2.编辑my.cnf，设置innodb_log_file_size,innodb_log_files_in_group

3.启动服务。

当innodb 发现innodb_log_file_size和老的不一样。会删除redo log文件，创建新的文件。

MySQL 5.6.7和之前的版本，修改innodb文件个数和文件大小：

1.设置innodb_fast_shutdown 为1

2.关闭服务没有错误

3.复制老的日志文件到其他地方

4.在目录中删除日志文件

5.编辑配置文件

6.启动服务

增加InnoDB表空间

最简单的方法是通过自增长来实现，innodb_autoextend_incremnt参数。也可以通过增加新的文件方式来扩展空间：

1.关闭服务

2.若之前的文件定义了自增长，修改变为固定长度，设置大小。可以再参数innodb_data_file_path中显示指定。

3.在innodb_data_file_path后增加新的文件，可以设置为自增长。

4.启动服务。

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

一段时间之后，文件增长到了988M。

innodb_data_home_dir =

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

减少InnoDB表空间

减少innodb表空间过程：

1.使用mysqldump，导出所有innodb表包括在mysql数据库中的表。

2.停止服务

3.删除所有存在的ibd文件，ibdata，ib_log

4.删除frm文件

5.配置新的表空间

6.启动服务

7.导入文件。

14.5.8 使用原生磁盘分区作为系统表空间

你可以把系统表空间放到原生分区中。当使用原生磁盘分区，保证服务有读写分区的权限。

在Linux和unix系统分配原生磁盘分区

1.当你创建一个新的数据文件，指定在innodb_data_file_path选项设置newraw

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

2.重启服务，InnoDB注意到newraw关键字，并初始化这个行分区，不创建或者修改任何InnoDB表。否则下次重启InnoDB会重新初始化修改会丢失。

3.InnoDB初始化新分区之后，停止服务，把newraw修改为raw。

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

4.重启服务，InnoDB允许修改。

14.6 InnoDB表管理

14.6.1 创建InnoDB表

创建表

-- Default storage engine = InnoDB.

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

-- Backward-compatible with older MySQL.

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

根据innodb_file_per_table设置，InnoDB创建每个表在系统表空间或者独立的表空间，使用show table status语句查看这些表的属性：

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)

使用新的行模式

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;

为primary key设置主键，列的选择：

1.被很多查询引用

2.左边无空白

3.不会有重复键

4.一旦插入后很难修改

14.6.2 把InnoDB表移动到另一个机器

14.6.2.1 跨平台复制移动使用小写

为了跨平台移动，创建的数据库和表使用小写。在配置文件中设置

[mysqld]

lower_case_table_names=1

14.6.2.2 传输表空间

使用flush tables for export准备innodb表复制到另外一个服务。当然InnoDB必须开启innodb_file_per_table

14.6.2.3 MySQL企业备份

略

14.6.2.4 复制数据文件

可以使用InnoDB数据库，简单的复制所有相关文件。像MyISAM数据文件，InnoDB数据和日志文件是binary，所有的平台都兼容。

14.6.2.5 ibd文件可移植性

当移动ibd文件，数据库目录名必须相同。如果你有干净的ibd文件备份，你可以恢复到新装MySQL：

1.复制ibd之后，不要drop或者truncate，因为这样会修改表空间中存在的表ID。

2.alter table删除元数据

ALTER TABLE tbl_name DISCARD TABLESPACE;

3.复制ibd文件到目标库中

4.使用alter table语句导入元数据

ALTER TABLE tbl_name IMPORT TABLESPACE;

干净的ibd文件备份要满足一下条件：

1.没有未提交的修改。

2.没有未合并的insert buffer

3.清理所有删除标记的index record

4.把所有修改也从buffer pool写入到文件中。

使用以下步骤创建干净的ibd备份。

1.停止所有活动并提交所有事务

2.等待直到show engine innodb status没有活动，主线程的状态为waiting for server activity。

另外一个方式是：

1.使用Mysql企业版备份备份mysql

2.启动mysqld服务，清理ibd文件。

导入导出

可以使用mysqldump导出表，然后在另一个表上导入。在导入时关闭自动提交可以提高性能。

14.6.3 使用事务批量DML操作

连接到MySQL默认是自动提交模式。多余多语句事务，可以set autocommit=0取消自动提交。使用start transaction开启，commit，rollback结束。

14.6.4 把MyISAM转化为InnoDB

14.6.4.1 减少MyISAM内存使用，增加InnoDB内存使用

从MyISAM中过渡，减少key_buffer_size配置释放内存。增加innodb_buffer_pool_size配置。

1.尽可能的多分配内存，最多80%的内存

2.当os总是出现内存短切，减少innodb_buffer_pool_size

3.若innodb_buffer_pool_size有好几个G可以使用innodb_buffer_pool_instance增加buffer pool，减少访问buffer pool的冲突。

4.在忙绿服务，把query cache关闭。

14.6.4.2 查看太长或者太短的事务

因为MyISAM不支持事务，所以不用在意自动提交。主要是用在innodb上的。当大量的写入的服务上，若事务太长会生成大量的负荷。因此小心避免事务运行时间太长：

1.如果你使用mysql交互式会话，完成的时候总是使用commit，rollback。及时关闭交互式会话。

2.保证任何错误，rollback不完整修改或者commit完整提交

3.ROLLBACK是比较昂贵的操作。当处理大量数据后，避免执行rollback

4.大量批量insert语句，同期的commit可以提高性能。

事务太长，会浪费内存和磁盘空间。事务太短，因为commit太频繁，也会浪费I/O。对于1.频繁使用InnoDB表，应该取消自动提交避免不必要的I/O。

2.自动提交适合与生产报表或者分析统计。

3.如果做了一列相关修改，最后要使用一个commit。

4.select语句也会打开一个事务。

14.6.4.3 不要太担心死锁

MySQL会很快发现死锁并且取消小的事务。应用应该处理错误并重启事务。如果死锁发生很频繁，就要检查应用程序代码，整理代码顺序，或者缩短事务。Innodb_print_all_deadlock会吧错误信息输出到error log中。

14.6.4.4 计划存储分布

为了获得做好的性能，需要调整几个和存储有关的参数。当你转化一个MyISAM表，数据大，访问频繁，并且重要的。可以考虑innodb_file_per_table,innodb_file_format和innodb_page_size配置，和create table中的row_format，key_block_size。最重要的参数是innodb_file_per_table。

14.6.4.5 转化一个现有的表

使用alter命令转换

ALTER TABLE table_name ENGINE=InnoDB;

14.6.4.6 克隆表结构

使用show create table table_name 获取表结构，然后修改engine子句。

14.6.4.7 转化已存在数据

把数据转移到空的innodb表，可以使用：

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns.

可以再插入之后在创建索引，以前索引创建是很慢的，现在消耗已经降低。如果有唯一性约束，可以先关闭约束检查，提高插入性能

SET unique_checks=0;

... import operation ...

SET unique_checks=1;

对于达标，可以减少I/O因为innodb可以使用insert buffer来批量写入secondary index的修改。

对于大表也可以使用如下来控制insert

INSERT INTO newtable SELECT * FROM oldtable

WHERE yourkey > something AND yourkey <= somethingelse;

14.6.4.8 存储需求

不管是转化MyISAM到InnoDB，都要保证有足够的空间存放新老2份数据。如果alter table出现空间不足，会回滚，并且化很长时间。Innodb使用insert buffer批量合并到index rolback没有这样的机制，会比insert多近30倍的时间。

14.6.4.9 仔细选择Primary Key

Primary key是总要的因素，会影响mysql查询性能和表索引空间使用。

创建primary key的指导：

1.为每个表定义主键

2.最好在创建表的时候就定义，而不是使用alter table

3.仔细的选择列和数据类型，最好使用数值列

4.如果没有合适的列，考虑自增列

5.如果不再到主键会不会被修改的情况下，自增列也是很好的选择。

根据表的预计大小，使用最小的类型，可以让表收窄减少空间，若有secondary索引，可以减少索引空间。

创建表，没有指定主键，mysql会自动创建一个6字节长度的主键，但是不能用于查询。

14.6.4.10 应用程序性能考虑

额外的可用性和扩展性，需要的空间比MyISAM多。可以窄设计来节省空间。

14.6.4.11 InnoDB表和文件的关联

InnoDB文件需要比MyISAM更加小心，有计划性：

1.不能删除innodb系统表空间

2.复制innodb到另外一个服务，需要使用flush table for export并且复制cfg和ibd文件。

14.6.5 InnoDB自增处理

InnoDB对自增列的插入做了优化。InnoDB表的自增必须是索引的一部分。

14.6.5.1 传统InnoDB自增长锁

若在InnoDB上使用了自增，InnoDB数据目录包含auto-increment计数器。用来分配自增值，计数器保存在内存中，不是在磁盘中。

InnoDB初始化自增计数器，服务启动后，第一个插入的语句会运行如下语句：

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加这个值，并分配给counter和这个insert。默认增加为1，可以通过auto_increment_increment来配置。

若表是空的，就使用1，可以用auto_increment_offset来设置便宜。

若在初始化钱使用了语句show table status，innodb初始化计数器，但不增加。初始化使用排它锁。

在初始化之后，如果不显示的指定值，自增计数器会分配一个值。如果指定的值大于计数器，计数器就会被更新。如果为自增列指定了null或者0，innodb会为自增分配一个新值。

访问计数器，会使用auto-inc锁，知道语句结束才释放。Auto-inc提高了并发性，但是长期持有会导致性能问题。如：

Insert into t select from t1;

服务重启后，第一次插入会初始化自增计数器，也会取消create table中auto_increment设置。

若事务rollback，计数器不会rollback。

14.6.5.2 配置InnoDB自增锁

InnoDB的自增使用特殊的锁AUTO-INC。语句结束就释放。对于基于语句的复制，意味着slave，master使用想用的语句，会导致insert语句不确定。

有表如下：

CREATE TABLE t1 (

c1 INT(11) NOT NULL AUTO_INCREMENT,

c2 VARCHAR(10) DEFAULT NULL,

PRIMARY KEY (c1)

) ENGINE=InnoDB;

插入数据

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB不知道有多少行要通过select插入。在语句执行是一个一个的分配。Tx1生产的自增时连续的，Tx2分配的自增，和Tx1比大小，由谁先运行决定。

只要执行顺序和binary log一样，结果是一样的。所以在插入的时候可以使用表锁，这样自增分配对复制来说是安全的，然而当有多个会话insert时表锁会影响并发性和可扩展性。

如果没有加表锁，那么tx1和tx2分配的自增将是不确定的。有类insert事先知道插入的行数，就可以避免表级auto-inc锁，任然可以保证给予语句复制的安全性。如果不需要使用binlog来恢复或者复制，可以取消表级auto-inc锁来提高性能。但是会出现自增值是交错的。

对于可以知道insert行数的InnoDB可以快速分配值，而不用加锁，前提是auto-inc没有分配。这些insert语句获取自增值是有mutex控制，分配过程完成就释放。

新的锁机制可以提高可扩展性，但是和之前比原理有些不同，可以通过参数innodb_autoinc_lock_mode配置.insert因为auto-inc锁定的问题被分为几类：

Insert-like:

所有在表上生成新行的语句,insert，insert select，replace,replace select,load data

Simple insert:

行插入可以预知行数。Insert on duplicate key update除外。

Bulk insert:

插入行数无法预知，insert select，replace select，load data。

Mixed-Mode Insert:

其实是simple insert 但是中间有些指定的自增列

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

有以下一些取值：

0：传统锁模式，所有的insert-like语句都获取auto-inc锁保持知道语句结束

1：连续锁模式，bluk insert语句会使用auto-inc锁，simple-insert可以使用轻量的mutex。但是如果有语句使用了auto-inc就要锁释放。分配的自增值都是顺序的。对于mixed-mode insert因为用户指定，所以会分配比需要的更多的自增。

2：交错锁模式，多个语句可以同时执行，但是对于给予语句的复制是不安全的。

Innodb_autoinc_lock_mode对其他使用的影响：

在复制中自增：如果使用基于语句的复制，建议设置为1,0，保证自增值确定性。

丢失自增值和顺序空隙：在所有的模式下，如果事务回滚，都会导致自增值丢失。

Bulk insert自增值空隙：在1,0下自增值分配是顺序的。没有空隙，但是如果为2，bluk insert和insert like语句一起执行的时候就可能会出现空隙。

Mixed-mode insert自增值的分配：因为混合模式，有些会指定自增，有些不会。语句在不同锁定模式下，反应也不同：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

Innodb_autoinc_lock_mode = 0

+-----+------+

| c1 | c2 |

+-----+------+

| 1 | a |

| 101 | b |

| 5 | c |

| 102 | d |

+-----+------+

下一个值是103

Innodb_autoinc_lock_mode = 1

+-----+------+

| c1 | c2 |

+-----+------+

| 1 | a |

| 101 | b |

| 5 | c |

| 102 | d |

+-----+------+

下一个值是105而不是103

Innodb_autoinc_lock_mode = 2

+-----+------+

| c1 | c2 |

+-----+------+

| 1 | a |

| x | b |

| 5 | c |

| y | d |

+-----+------+

X,y是唯一的大于之前生成的任何行。

如果下一个自增时4，那么上面insert语句就会报错，建冲突。因为5会分配给b，和c冲突。

14.6.6 InnoDB和外键约束

略

14.6.7 InnoDB表的限制

不要把MySQL数据库上的表转移到InnoDB上，不支持。一旦转化后，MySQL不能重启除非还原到以前的备份。不要把InnoDB表创建在NFS上。

14.6.7.1 最大和最小

1.一个表最多能够包含1017个列

2.最多能有64个secondary 索引

3.单列索引，key的长度最多只能767个字节，使用innodb_large_prefix可以突破767的限制最多为3072.如果再复制情况下，如果slave不能设置，master也不要设置

4.InnoDB最大key长度为3500，但是MySQL限制到3072字节

5.若page size奢侈为8KB或者4KB对应的index key也会按比例收缩：

16KB->3072B,8KB->1536B,4KB->768B

6.最大行长度，也会根据page size变化而变化

16KB->8000B,8KB->4000B,4KB->2000B

7.在老的操作系统上，文件做多为2GB。

8.innodb 日志文件最多为512GB。

9.表空间最小为10MB，最大为64TB，也是表做大的大小。

10.默认数据库page size为16KB。

14.6.7.2 索引类型

1.全文索引

2.InnoDB支持空间数据类型，但是不能创建索引

14.6.7.3 InnoDB表的限制

1.通过analyze table确定索引中数据密度。，analyze table随机采样并且更新表的评估值。

当启动innodb_stats_persistent启动，要在大量修改之后运行analyze table，因为启动后，统计信息不会被定期计算。

在生产统计信息是，可以通过innodb_stats_persistent_sample_page修改采样率，或者使用innodb_stats_transient_sample_pages。

若join优化器优化有问题，可以使用analyze table重新分析。也可以使用force index强制缩影使用。也可以通过变量max_seeks_for_key，设置最大seek，超过就变扫描。

2.若语句或者事务在一个表上运行，并且有一个analyze table运行，后面还跟了一个analyze table。那么第二个analyze table就会被堵塞。直到语句或者事务完成。因为analyze table 运行完成必须要标记当前加载的表定义过期。新的事务或者语句必须加载新的表定义。在当前事务没有完成前老的表定义不能被清理。

3.show table status不能提供准确的统计信息，只能看被表保留的物理空间。InnoDB内部不保存行数。准确的行数需要count，若不需要太准确可以使用show table status的结果。

4.在windows平台上，innodb数据库和表名内部都用小写保存

5.auto_increment列必须为索引的一部分

6.在auto_increment初始化的时候，innodb会在和自增列相关的索引尾上加一个排它锁。当访问auto-increment列时innodb使用特定的auto-inc锁，直到语句完成。

7.当重启mysql服务，innodb可能重用老的值，因为分配后没有被保存到表里面。

8.当auto_increment超出之后的insert会报错重复key错误。

9.delete from table_name 不会重新生成表，是删除所有行。

10.外键的级联操作，不会触发触发器

11.不能创建列和innodb内部列一样的名字。

14.6.7.4 锁定和事务

1.在MySQL层lock table会锁定MySQL层，也会请求InnoDB 表锁。如果innodb_table_locks=1，lock tables会申请mysql层的表锁和innodb的表锁。如果为0不需要申请innodb的表锁。在没有innodb表锁情况下，lock tables在其他事务在表上锁定了一些记录的时候，也可以成功锁定。

2.innodb锁在事务内有效，事务结束后释放。

3.不能再事务中锁定其他表，因为lock tables会隐式执行commit和unlock tables。

4.从1023并发事务修改，被升级到128*1023,同事写入undo record.

14.7 InnoDB压缩表

使用SQL语法和MySQL配置，可以创建压缩保存数据的表。压缩可以提高原设备的性能和可扩展性。压缩也表示内存和磁盘传输变小。占用的空间变小。

14.7.1 表压缩概述

InnoDB可以创建压缩表，row_format=compressed并制定较小的page size。Page越小请求的I/O越少，特别是SSD。

Page size可以通过key_block_size指定。不同的page size 表示表必须在自己的ibd文件里面，必须启动innodb_file_per_table。如果key_block_size越小，因为page越小，I/O效率提高。但是如果过分小，如果不能压缩的单个page中，就需要额外的操作去重组page。

在buffer pool压缩数据以小page保存。Page size依据key_block_size，对于提取和更新，MySQL会分配16KB的非压缩数据。然后在生产压缩页。可能需要加大buffer pool来保存压缩和非压缩页，尽管非压缩页会在内存不够时被牺牲。

14.7.2 启动压缩表

在创建压缩表前，先要启动innodb_file_per_table和innodb_file_format=Barracuda然后创建表或者修改，并指定row_format=compressed,key_block_size。

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

CREATE TABLE t1

(c1 INT PRIMARY KEY)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8;

1.若指定了row_format=commpressed可以忽略key_block_size,默认page是innodb_page_size的一半。

2.指定了key_block_size，可以忽略row_format=compressed，会自动启动压缩。

3.确定key_block_size的最好方法是，对同一个表按size不同分配创建压缩，并且比较负荷。

4.key_block_size被认为是提示，如果是0就表示innodb_page_size的一般，key_block_size<=innodb_page_size,若innodb_page_size>key_block_size就会被忽略，并设置为innodb_page_size的一半。如果innodb_strict_mode=on则会报错。

5.其他性能相关的看14.7.3 压缩表调优

MySQL key_block_size可以1KB，2KB，4KB，8KB，16KB，但是不会影响压缩本身的算法，只是决定每个chunk的大小。

设置Key_block_size等于InnoDB page size就表示可能没有太大的压缩比率。但是对于TEXT，BLOB，varchar还是有用的，可以减少overflow page。

表上的所有索引被压缩都使用相同的page size。由create table或者alter table指定。Create index中的row_format，key_block_size不是innodb表的属性，如果指定会被忽略。

压缩表的限制：

Innodb系统表空间不能使用表压缩。压缩只能应用在整个表和索引上。不能用在指定行集上。

14.7.3 InnoDB压缩表调优

压缩比率和数据有关，但是可以决定压缩表的性能影响：

1.什么表要压缩。

2.使用多大的page size

3.runtime情况下如何调整buffer pool大小。

4.若系统执行了DML操作在压缩表上，而数据是分布方式导致高昂的压缩操作失败，可能需要额外的高级配置选项。

14.3.7.1 什么时候使用压缩

一般压缩在读多写少的表上效果好。

14.3.7.2 数据特性和压缩

关键决定压缩效果好坏的是数据本身。决定是否使用压缩表，需要测试，可以使用gzip对ibd文件压缩，简单的评估压缩比率。MySQL的压缩比率比工具的压缩比率少，MySQL只能以key_block_size进行压缩，并且系统数据不会被压缩。而压缩工具可以有更大的chunk，压缩比率会更高。

另外一种方法是复制为压缩的表，然后进行压缩，看压缩比率。

查看在特定负荷下，压缩是否是有效率的：

1.简单的测试，在实例上只有一个压缩表，测试并查看information_schema.innodb_cmp表。

2.若果要做精心的测试，运行information_schema.innodb_cmp_per_index表，因为表信息收集开销比较大，需要启动innodb_cmp_per_index_enabled。

3.检查成功压缩的比率，对比innodb_cmp_per_index表和innodb_cmp表。

4.如果压缩成功操作率很高，那么比较适合做压缩。

5.如果压缩失败比较好，应该调整innodb_compression_level，innodb_compression_failure_threshold_pct,innodb_compression_pad_pct_max选项。

14.3.7.3 数据库压缩VS应用程序压缩

决定是否在数据库压缩或者在应用程序压缩，如果都压缩只会浪费cpu。

当启动，MySQL表的压缩是自动应用到所有的列和索引上。

在应用程序中压缩，insert之前程序需要压缩文本数据。可以减少负荷，但是会出现有的数据压缩比率高，有的数据压缩比率低。

14.3.7.4 工作负荷特点和压缩

在决定要压缩什么表的时候，工作负荷特点也是会影响性能的重要因素之一。若应用程序以读为主，基本不需要重新组织和压缩索引page。Delete相对高效，因为可以通过修改非压缩数据表示数据已经被删除。

如果你的工作负荷是IO密集的，不是cpu密集，使用压缩表可能可以提高性能。

14.3.7.5 配置特点和压缩

压缩试图通过CPU压缩，解压，来减少I/O。尤其是在多用户，多cpu环境下。Page在内存中被压缩往往会保留非压缩压在内存中。LRU算法会试图平和压缩页和非压缩页。

14.3.7.6 选择压缩page大小

压缩page大小设置，依赖于数据类型和分布，压缩page size必须必最大的行大小大，不然会报错。

Page太大会浪费空间，若果太小insert，update要花时间重新压缩导致b树分页频繁，导致文件变更大，但是查询效率很低。

通常，可以设置压缩page 为8k，4K。

14.7.4 监视压缩

查看整体程序性能，CPU，I/O和文件大小可以很好的表示压缩效率。要深入分析压缩表性能，你需要监控information_schema中的表，这些表整体的反应了内部的内存使用和压缩率。

Innodb_cmp表，根据page大小来收集信息。在没有其他压缩表情况下，可以使用这个数据。

Innodb_cmp_per_index,对每个表和索引进行信息收集。这些信息对于诊断性能问题很有帮助。需要去启动参数innodb_cmp_per_index_enabled才能启动表数据的收集。

最关键的是考虑压缩和解压的次数。比较innodb_cmp_per_index在innodb_cmp中成功压缩的比率，调整buffer pool，调整page size，或者不压缩。

如果因为压缩cpu上升，可以升级cpu，增加buffer pool，可以让压缩和非压缩的页都放在内存减少减压操作。

大量的压缩操作，说明写入压缩表操作频繁，影响压缩效率。如果成功压缩操作 (COMPRESS_OPS_OK)在总操作(COMPRESS_OPS)比率高，表示系统执行良好。若比率低，考虑不要压缩这个表或者增加页大小，比率低说明MySQL重新组织，重新压缩，分页操作频繁。如果压缩失败大于1%~2%考虑停止压缩。

14.7.5 InnoDB如何压缩

14.7.5.1 压缩算法

MySQL使用LZ77压缩，是无损压缩通常压缩率在50%以上。InnoDB压缩应用在用户数据和索引中，很多情况下，索引占了40%~50%甚至更高的比率，所以差异是很明显的。可以通过参数innodb_compression_level来平衡cpu和压缩率。

14.7.5.2 InnoDB存储引擎和压缩

Innodb所有数据都以b树方式存储，索引也是b树，索引会包含索引key和一个指向聚集索引的指针。Btree的压缩和overflow的压缩不同，后面章节解释。

14.7.5.3 B树页压缩

因为连续的更新，b树要特别对待，最小化分页，最小化解压重新压缩。MySQL以非压缩的方式，可以提高一些in-place update，比如delete-marked。

另外MySQL视图避免不必要的解压，重新压缩。在每个btree page系统维护了一个未解压的“modification log”来记录page的修改。小的insert和update会被记录在这个log中，没必要去重构整个page。

当modification log空间不足，InnoDB解压page，应用修改并重新压缩page。如果重新压缩失败，btree分页，这个操作会持续直到成功insert或者update。

为了避免频繁出现压缩错误在写入密集负荷中，MySQL有时候会保留一些空闲空间，就是pge的padding空间。这样modification log会执行的比较快。Page重新压缩也有足够的空间，不会分页。

在繁忙的系统压缩表插入频繁，可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max。

通常MySQL要求page要包含2条记录，对于压缩表，要求在非压缩页上容纳一行记录。如果再innodb_strict_mode=on MySQL在create table，create index的时候检查最大行大小，如果不能放入，会出现row too big的错误。

如果innodb_strict_mode=off,接下来的insert或者update试图创建row的时候因为无法插入就会报错。为了解决这个问题重新设置key_block_size，或者不要压缩。

14.7.5.4 压缩BLOB,VARCHAR,TEXT列

在InnoDB中BLOB,VARCHAR,TEXT可能保存在overflow。Row_format=dynamic或者compressed，BLOB,VARCHAR,TEXT字段可能保存在page外面，聚集索引保存了20B的指针指向这些page。当行太长不能被保存在同一个page中，MySQL会选择一个最长的列保存到off-page中.

Note

如果row_format=dynamic或者compressed如果text，blob>=40个字节会被in-line保存。

如果是Antelope文件格式，MySQL会保存前768字节，后面的20字节指向后面的overflow page。

如果表是compressed格式的，所有的overflow page也会被压缩。MySQL使用zlib压缩算法压缩一个数据项。压缩的overflow页包含非压缩的头和一个page checksum和连接到下一个overflow的连接。所以如果blob，varchar，text是重大的空间节约。Image数据一般都是被压缩过的，再压缩只会浪费cpu。

使用16KB压缩页大小，也可以减少blob，varchar，text的IO消耗。

14.7.5.5 压缩和innodb buffer pool

在压缩的innodb表，压缩页是1KB，2KB，4KB，8KB。16KB不压缩。为了最小化I/O和page解压，buffer pool包含压缩的page和未压缩的page。为了空间MySQL会牺牲buffer pool中的非压缩，或当有一段时间没有访问后，会把压缩写入disk释放内存。

MySQL使用自适应LRU算法来决定压缩页和非压缩页之间的平衡。目标是避免cpu密集的时候，花时间去解压。Cpu空间的时候避免I/O。

当I/O忙，会牺牲非压缩，释放内存。

当cpu忙，会保留压缩和非压缩page。

14.7.5.6 压缩和InnoDB Redo Log文件

在压缩页写入数据文件前page会被写入到redo log，保证redo log了，crash之后依然可用。因此一些实例会变大，或者checkpoint会变频繁，由压缩页被修改导致重新组织和重新压缩决定。

14.7.6 OLTP负荷的压缩

通常推荐在只读，或者读多写少的负荷下做压缩。在SSD上使用压缩,可以减少空间和IOps，即使在写多的负荷下使用。

压缩的配置：

1.innodb_compression_level，设置压缩级别，越高，就可以放入越多的数据

2.innodb_compression_failure_threshold_pct，指定压缩错误率的中止点，如果超过这个值，MySQL在分配新页的时候保留一些空白空间，自动调整空闲比率。最大值由innodb_compression_pad_pct_max来决定。

3.innodb_compression_pad_pct_max,可以调整每个页做大的保留空间。

14.7.7 压缩语法警告和错误

Row_format=comressed或者key_block_size在create table或者alter table语句中，如果没有启动Barracuda文件格式，会有用以下几个错误。

Level	Code	Message
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning	1478	InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning	1478	InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning	1478	InnoDB: assuming ROW_FORMAT=COMPACT.

默认这些警告，会导致表创建但是不带压缩功能。当启动了innodb_strict_mode就会报错。

在“non-strict”条件下，mysqldump可以导入到不支持压缩的数据库。如果源数据库中有压缩表，那么就会创建row_format-compact而不是阻止错误。当dumpfile导入到新数据库，在原始库上的表都会被重建。保证服务设置了适当的innodb_file_format或者innodb_file_per_table。

Key_block_size设置只允许在row_format=compressed下使用，或者被忽略。

Level	Code	Message
Warning	1478	InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

如果设置了innodb_strict_mode就不会警告，会直接报错，表不会被创建。

Create table和alter table选项

Option	Usage Notes	Description
ROW_FORMAT=REDUNDANT	Storage format used prior to MySQL 5.0.3	Less efficient than ROW_FORMAT=COMPACT; for backward compatibility
ROW_FORMAT=COMPACT	Default storage format since MySQL 5.0.3	Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page
ROW_FORMAT=DYNAMIC	Available only withinnodb_file_format=Barracuda	Store values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix)
ROW_FORMAT=COMPRESSED	Available only withinnodb_file_format=Barracuda	Compresses the table and indexes using zlib
KEY_BLOCK_SIZE=n	Available only withinnodb_file_format=Barracuda	Specifies compressed page size of 1, 2, 4, 8 or 16 kilobytes; implies ROW_FORMAT=COMPRESSED

当innodb_strict_mode=off,会忽略设置，并生产警告，如果为on，会生产错误，不会被创建。

Syntax	Warning or Error Condition	Resulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=REDUNDANT	None	REDUNDANT
ROW_FORMAT=COMPACT	None	COMPACT
ROW_FORMAT=COMPRESSED orROW_FORMAT=DYNAMIC orKEY_BLOCK_SIZE is specified	Ignored unless bothinnodb_file_format=Barracuda andinnodb_file_per_table are enabled	COMPACT
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)	KEY_BLOCK_SIZE is ignored	the requested row format, or COMPACT by default
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specified	None; KEY_BLOCK_SIZE specified is used	COMPRESSED
KEY_BLOCK_SIZE is specified withREDUNDANT, COMPACT or DYNAMICrow format	KEY_BLOCK_SIZE is ignored	REDUNDANT, COMPACT orDYNAMIC
ROW_FORMAT is not one ofREDUNDANT, COMPACT, DYNAMIC orCOMPRESSED	Ignored if recognized by the MySQL parser. Otherwise, an error is issued.	COMPACT or N/A

14.8 InnoDB文件格式管理

在InnoDB中，新的文件格式，就意味着新的特性。

14.8.1 启动文件格式

Innodb_file_format选项在创建新的表的时候起作用。并且必须要启动innodb_file_per_table。目前只支持Antelope和Barracuda文件格式。Innodb_file_format可以再配置文件中设置，也可以通过set global设置。

14.8.2 验证文件兼容模式

向后兼容性

新的InnoDB创建的表可能不会安全被上个版本的InnoDB读写。InnoDB 1.1提供了保证这些条件的机制，来保证数据文件和innodb版本的兼容性，这个机制可以使用新版本提醒，有要保留这些选项，并防止不兼容特性的使用。

若改版本InnoDB支持某版本的文件格式，那么就可以读写任意这个版本之前版本的表。特定的文件格式会限制一些特性，反过来若表空间使用了当前不支持的文件格式，那么就无法访问。只能降级innodb表空间到之前的版本，然后复制到新的表。

最简单决定文件格式的方法是使用show table status命令或者查询表tables的row_format字段。

内部细节

每个innodb表空间都有一个文件格式标记，系统表空间是hightest。创建压缩表和dynamic会更新ibd文件头，在innodb数据字典中标记barracuda文件格式。InnoDB会根据这个标记来检查兼容性。

Ib_file集合的定义

Ib_file集合包含：

1.系统表空间一个或者多个ibdata文件，包含了系统内部信息。

2.多个简单表空间，*.ibd文件

3.InnoDB日志文件，ib_logfile0,ib_logfile1。Redo日志文件。

Ib-file集合，不包含frm文件。Frm邮件是由mysql创建的。

14.8.2.1 InnoDB打开时的兼容性检查

为了防止在打开ib-file set的时候出现crash，就会检查是否完全支持ib-file的文件格式。若当前软件的格式太新，在恢复的阶段，会照成验证的数据破坏，因此启动检查文件格式可以防止之后的一些列问题。

从innodb 1.0.1版本开始系统表空间会记录，把使用过的最高的文件格式作为ib-file set的一部分。文件格式检查有参数innodb_file_format_check控制。如果当参数为on，并且mysql支持版本低于文件的版本。

InnoDB: Error: the system tablespace is in a

file format that this version doesn't support

有些情况下，可能要读取太新的文件，如果innodb_file_format_check=off。Innodb打开数据库的时候会警告：

InnoDB: Warning: the system tablespace is in a

file format that this version doesn't support

Note

如果允许怎么运行，是很危险的。

参数innodb_file_format_check,影响数据库被打开的时候，innodb_file_format只决定在新表是否可以以某格式创建，并不影响是否可以打开数据库。

若有更高的格式的表被创建或者已经存在可读写的表的格式高于当前软件。系统表不会被更新，但是会启动表级别的兼容性检查。

14.8.2.2 表打开时的兼容性检查

当表被访问，InnoDB会检查文件格式。防止当表使用太新的数据结构时，crash，损坏。只要release支持可以读写任意文件格式的表。指定innodb_file_format会阻止创建一个指定特定文件格式的新表，甚至是release是支持的。这样设置会阻止向后兼容性，但是不会妨碍对支持格式的访问。

InnoDB在文件打开的会后检查文件格式兼容性。若现在InnoDB把呢吧不支持InnoDB文件中的标记。

ERROR 1146 (42S02): Table 'test.t1' doesn't exist

也会写入error log

InnoDB: table test/t1: unknown table type 33

尽管innodb设置了参数innodb_file_format_check,允许打开不支持文件格式，表级别检查还是会被应用。

14.8.3 识别使用的文件格式

Innodb_file_format只影响新增的表，若你新增了一个表，表被标记使用最早的文件格式。比如启动了barracuda并且创建了新表，没有使用dynamic，compressed。那么表会被标记为Antelope。

如果得知表或者表空间，可以使用表tables，或者show create table获得文件格式。若没有使用comressed，dynamic。就会使用Antelope的文件格式，row_format为redundant或者compact。

内部细节

InnoDB有2个文件格式，Antelope，Barracuda，4个不同的行格式。文件和行格式，以32bit卸载文件的54位置。可以使用od -t x1 -j 54 -N 4 tablename.ibd.查看。

表空间flag钱10个bit：

0bit:0表示Antelope,1-5bit为0,1表示Barracuda，1-5为1.

1-4bit：表示压缩文件的page size，1=1k，2-2k，3=4k，4=8k。

5bit：antelope=0,Barracuda=1.

6-9bit：4字节数值，0=16K,3=4K,4=8K,5=16K

10bit：表空间位置，0=默认，1=使用了data directory子句。

Table flag要使用语句查看中的flag：

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES

前7bit：

0bit:0=Redundant 行格式，1-5就为0。1=compact 行格式。

1-4bit：4bit数值，表示压缩页，1=1k，2-2k，3=4k，4=8k。

5bit:0:Antelope,1=Barracuda，如果为1,0bit也要为1

6bit: 表空间位置，0=默认，1=使用了data directory子句。

14.8.4 文件格式降级

最简单的方法：

ALTER TABLE t ROW_FORMAT=format_name;

14.9 InnoDB 行存储和行格式

14.9.1 InnoDB行存储概述

行和相关列的存储会影响查询和DML性能。更多的行放到一个page里面，查询的时候会更快，内存更好，I/O也会变少。InnoDB中数据被放入page中，page通过构成B树结构来保存。一行的数据都被保存在page里面，但是变长字段是例外。因为太长会溢出，所以这些只被保存在overflow page上。有个link连接到这些page。

14.9.2 位表指定行格式

语句如下：

CREATE TABLE t1 (f1 int unsigned) ROW_FORMAT=DYNAMIC ENGINE=INNODB;

14.9.3 DYNAMIC和COMPRESSED行格式

Dynamic，compressed只有在文件格式为Barracuda才能使用。Barracuda也支持compact，redundant格式。

使用Dynamic，compressed长列会被保存在off-page上。聚集索引上会保留20byte的执行。

是否使用off-page，取决于page大小和行大小。当行太长，innodb会吧最长的列作为溢出直接放到溢出page。Text，blob如果小于40字节都会in-line保存。

如果行可以存放在page中，Dynamic行模式也是很有效的。Dynamic为有长数据保存在off-page设计的。因此效率比老的行模式好。

Compressed行格式off page的存储和dynamic类似。区别是compressed对表和索引进行了压缩。

14.9.4 Compact和REDUNDANT行格式

早前的InnoDB使用者2个行格式。对于溢出，InnoDB会把钱768字节保存在page上，后面的保存在溢出页上。对于少于768的列会被保存在page内，对于有很多blob的列会显得太满。

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

14.10.1 InnoDB磁盘I/O

在linux和windows平台，innodb使用OS自带的来支持原生异步I/O。在其他平台使用模拟的异步I/O。

预读

如果innodb可以决定，数据被马上需要的可能性很高，就会执行预读把输入读入内存，预读有2种方式：

1.线性预读，当innodb发现访问方式是顺序的，发起额外的page读。

2.随机预读，innodb发现数据可能要全部处理，会持续的读。

双写buffer

InnoDB使用新文件刷新技术，涉及到双写buffer。增加了crash的安全性和提高性能减少fsync()。

在写入数据文件之前，会先写入到双写buffer。写入到双写buffer之后innodb把这些写入到数据文件中。

14.10.2 文件空间管理

为了避免index和表都保存在系统表空间，使用参数innodb_file_per_table，每个新的创建表有独立的表空间文件，这样可以减少文件碎片。当表被截断可以返回给系统，而不是系统表空间。

page，extents，segments和tablespace

tablespace由page组成innodb_page_size来制定page的大小。组成1MB的extents，表空间中的文件在innodb中被称为segment。一开始innodb分配32page，之后会分配整个extents。innodb可以一次性分配4个extents。innodb会为每个索引分配2个segments，一个非叶子，一个叶子。叶子节点在磁盘中连续，顺序I/O操作顺序会更好。因为叶子节点包含了表的实际数据。

表空间中的有些page包含其他page的bitmap。因此有些extents在表空间中，不能被整体分配。只能page单独分配。

当使用show table status查看表空间中可用的空闲空间。会发现有些extents是空闲的。innodb为了清理和启动目的而保留的一些空间。

空闲出来的空间是可以让别的用户使用依赖于是否释放了几个page还是extentds。drop table或者delete所有行空间释放给用户，但是物理删除操作在事务回滚和一致性读不需要的时候执行。

page如何关联表记录

行最大记录为8000字节。longblob和longtext必须少于4GB，所有的行长度，包含blob和text必须少于4gb。

若行少于一半的page，则都有放在page中，若超过了一半page会选择变成放入overflow，直到可以放入page的一半。

14.10.3 InnoDB 检查点

检查点如何处理进程

innodb实现了一个模糊检查点的机制。innodb会刷新小批量的脏数据，但是刷新时会中断语句执行。

在crash恢复，innodb会查看写入到log文件的检查点标记。检查点标记表示在标记之前的数据都被写入到了磁盘中。innodb应用之后的log即可。

14.10.4 表整理

随机插入和删除会造成碎片，碎片表示空隙。碎片的症状是会花比更多的空间。另外一个症状是扫描会花更多的时间。

为了整理碎片要定期执行空的alter table。

ALTER TABLE tbl_name ENGINE=INNODB

alter table tbl_name engine=innodb和alter table tbl_name force都使用online DDL(ALGORITHM=COPY)。

14.10.5 使用TRUNCATE TABLE清理表空间

表有自己的表空间时，truncate table空间会被os回收。truncate table表和其他表不能有外键约束。

当表被truncate，会删除并且重建表，生成ibd文件，并且空闲空间返回给OS。

14.11 InnoDB和Online DDL

Online DDL有一些好处：

1.提高了繁忙环境的相应和可用性，避免在繁忙环境下为了一个新的索引停止很长时间。

2.可以让你在性能和并发性上平衡。

3.可以in-place完成，不需要创建一个新的表。

14.11.1 Online DDL概述

历史上，没有online操作，很多alter table操作会创建一个新表来代替老的表。MySQL 5.5，MySQL 5.1之后 innodb plugin create table和drop table来避免表复制行为。叫做快速索引创建。MySQL 5.6之后所有的alter table都可以避免表复制。

新的机制通过先导入数据再创建索引方式来提高速度。虽然在语法上没有改变，但是一些特性会影响性能，空间使用和操作的语义。

Online DDL增强了很多DDL操作，表复制，DML Blocked。

Secondary Index

创建索引，CREATE INDEX name ON table (col_list) or ALTER TABLE table ADD INDEXname (col_list).

删除索引，DROP INDEX name ON table; or ALTER TABLE table DROP INDEX name

MySQL 5.6版本之后，索引被创建或删除时，可以让DML继续使用。

类属性

设置默认值，ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal or ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT

修改自增下一个值, ALTER TABLE table AUTO_INCREMENT=next_value;

重命名列， ALTER TABLE tbl CHANGE old_col_name new_col_name datatype

外键

外键创建和删除

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) REFERENCES tbl2(col2) referential_actions;

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

使用foreign_key_checks启动主键和外键。

若不知道约束名，可以使用语句查看：

show create table tableG

也可以在一个语句内删除外键和索引

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

如果已经存在altered表中，应用DDL操作有额外的限制。

1.在这些子表上在线DDL不允许并发DML

2.alter table子表会等待另外一个事务提交。如果在父表上的修改会影响到子表，比如设置了cascade，或者set null。

若一个表是父表，若DML带有on update，on delete那么就要等alter table 完成。

注意ALGORITHM=COPY

alter table使用algorithm=copy,并发DML就不能使用。并发查询还是允许。表复制操作在lock=shared下运行，也可以在lock=exculusive下。

并发DML和表复制

有些DML操作允许并发DML但是还是要执行表复制。以下操作比MySQL 5.5快：

1.添加，删除，重排列

2.增加，删除主键

3.修改row_format和key_block_size

4.修改列是否为空

5.optimize table

6.force选项来创建表

7.alter table engine=innodb来重建表。

Note

尽管 online DDL很有效，但是还是要创建一个稳定的表结构。主要的例外是secondary index，对于大表，通常是最后建比较有效。不管create table，index，alter table什么顺序都可以使用show create table来获得当前表结构。

14.11.2 Online DDL的性能和并发考虑

Online DDL有几个方面的提升：

1.DDL操作时，也可以查询和DML，可用性更高。

2.对于in-place操作，重建表时，避免disk I/O，CPU。

3.对于in-place操作，读入的数据更少，读入buffer pool的数据比复制所有的数据少，避免临时的性能问题。

如果online操作需要临时文件，InnoDB创建在零食文件夹中，而不是在数据目录中。

Online DDL锁操作

在DDL操作时，表可能会被锁，取决于内部操作过程和lock子句。默认mysql是使用最小的锁定。如使用的锁不支持就会报错。

1.对于DDL操作，使用lock=none,查询和并发DML都可以被允许。如果DDL不允许lock=none，alter table就会报错。

2.对于DDL操作，使用lock=shared，任何写入都会被block，若不支持锁定类型就会报错。

3.对于DDL操作，使用lock=default，mysql使用最小的锁级别。

4.对于DDL操作，使用lock=exclusive,查询和DML都会被block。

Online DDL语句都等innodb表上的事务运行完成后再运行，因为DDL在准备时要申请X锁。online DDL操作可能要比老的DDL操作要慢。

In-Place vs Table-Copy DDL

online ddl的性能受到in-place，copying和rebuilding整个表影响很大。in-place使用在secondary上，不能使用在primary，primary上依然只能使用copying data。

当使用子句ALGORITHM=INPLACE,尽管使用的是copy data，但是性能还是要比ALGOTITHM=COPY好：

1.没有undo或者徐昂管的redo生成。

2.secondary可以预先排序，所以可以按顺序被导入

3.没有使用change buffer，因为没有随机写入到secondary索引。

简单的识别是否使用了inplace或者copy就是看相应行数，如果响应为0行，说明inplace：

1.修改列的默认值，没有响应行数

Query OK, 0 rows affected (0.07 sec)

2.增加索引(虽然然有时间花费，但是没有响应行)

Query OK, 0 rows affected (21.42 sec)

3.修改数据类型(时间消耗，并且重建了表)

Query OK, 1671168 rows affected (1 min 35.54 sec)

在处理大表之前，最好先进行测试：

1.克隆表结构

2.导入少量数据

3.执行DDL操作

4.检查响应行数，看是否inplace

14.11.3 Online DDL的SQL语法

Table 14.6, “Summary of Online Status for DDL Operations” 列出了那些DDL操作可以inplace，允许并发DML，那些需要特定的设置或者alter table。

可以通过ALGORITHM和lock来控制online DDL的不同方面。Lock是并发性控制，ALGORITHM是和老的copy table最主要的区别之一，如：

1.避免了不小心，让表处于不可读，写状态，可以通过alter table lock=none来避免。

2.可以使用ALGORITHM=inplace和ALGORITHM=copy来比较性能。

3.使用ALGORITHM=inplace,如果语句不能用inplace方式，会立刻停止。

14.11.4 组合和独立DDL语句

DDL语句，可以把多个DDL操作放在一起，因为每个alter table都会涉及复制和重建表，可以一次性全部完成。

如果可以使用inplace，就可以把多个DDL分解成多个语句

ALTER TABLE t1 ADD INDEX i1(c1), ADD UNIQUE INDEX i2(c2),

  CHANGE c4_old_name c4_new_name INTEGER UNSIGNED;

分解为：

ALTER TABLE t1 ADD INDEX i1(c1);

ALTER TABLE t1 ADD UNIQUE INDEX i2(c2);

ALTER TABLE t1 CHANGE c4_old_name c4_new_name INTEGER UNSIGNED NOT NULL;

有些情况下，还可以使用多alter table：

1.如果操作需要特定的顺序

2.如果操作使用相同的lock，并且要一起成功或者失败。

3.操作不能以inplace方式执行，任然需要复制，重建表。

4.或者指定了，ALGORITHM=COPY或者old_alter_table=1

14.11.5 Online DDL的例子

具体看：14.10.5 Examples of Online DDL

14.11.6 Online DDL的细节

任何alter table操作都有以下几个方面：

1.是否会修改表的物理表示，或者只修改元数据，不会涉及表本身。

2.数据量是否会变化

3.修改的表是否涉及，聚集索引，secondary索引或者两则

4.是否和其他表有外键关系

5.是否分区，如果是分区表，会被调到低级别操作涉及到多个表，这些操作要是用Online DDL需要特定的规则。

6.表是否一定复制，是否表可以被以inplace方式重组。

7.表是否包含自增列

8.需要的锁级别。

Online DDL的错误条件

以下是online ddl报错的主要原因：

1.如果指定了锁级别过低，DDL操作不支持。

2.等到锁曹氏

3.tmpdir文件系统超出了空间

4.alter table太长导致超出innodb_online_alter_log_max_size。

5.如果并发DML修改表，在之前的定义被允许，但是在新的不被允许。

Online DDL在有没有启动innodb_file_per_tale无关。innodb中有2个索引，聚集索引和secondary索引。聚集索引是表本身，所以所有的修改都会导致，copy。secondary索引是表的副本，也只是一部分数据，不需要copy。

删除secondary索引比较简单，只要修改元数据即可。增加索引，innodb会扫描并使用buffer和临时表来排序。这样比随机插入到一个建好的索引效率高。因为当page满了会产生分页。

Primary Key和Secondary Key索引

MySQL服务和InnoDB会保存他们的元数据。MySQL服务会把信息保存在frm文件中。InnoDB把数据字典保存在系统表空间中。如果DDL操作crash中断，造成2边数据不一致，会出现启动问题，表不能被访问。

14.11.7 Online DDl Crash如何恢复

虽然不会丢失数据，但是恢复方式和b树索引不同。如果在创建secondary索引crash。MySQL会删除创建的索引，需要重新创建。

创建聚集索引时，因为数据从一个表复制到另外一个表，crash会比较复杂。MySQL在创建时先复制数据到临时表，一旦完成，新表替换老的表。

若在创建新聚集索引，系统crash，没有数据丢失但是使用临时表来恢复。因为很少出现所以手册不提供这样的场景。

14.11.8 分区表上的Online DDL

除了alter table分区语句， online ddl分区表和普通表类似。alter table分区子句内部api和非分区表不同只能使用ALGORITHN=DEFAULT和lock=default。

如果在alter table上使用alter table分区子句，使用alter table copy算法分区表会被重新分区。也就是说分区表以新的结构被重建。

若不使用alter table修改分区表分区，alter table会在每个分区上使用inplace算法。使用inplace的alter table选项，分区越多会需要越多的资源。

尽管分区表和非分区表使用的alter table api不同，MySQL依然视图最小化数据复制和锁定：

1.add partition和drop partition在range或者list分区上不会复制数据。

2.truncate partition不会复制数据。

3.在range，list上进行add partition和coalesce partition，可以和查询并发。

4. REORGANIZE PARTITION, REBUILD PARTITION,ADD PARTITION 或者 COALESCE PARTITION在linear hash或者list上，允许并发查询。

14.11.9 Online DDL限制

当执行DDL有一下限制：

1.当执行online ddl时，复制表，写入临时目录，临时表要足够大。

2.alter table drop inxex和add index子句，2就会使用表复制，而不是快速索引创建。

3.在临时表上创建索引，使用表复制。

4.在删除被外键使用的索引会报错。

5.不能在on…cascade或者on…set null语句下使用alter table lock=none

6.在online DDL时不管使用什么lock子句，在开始和结束都会请求X锁。所以要等待上个事务完成。

7.当执行online alter table，执行alter table的会启动一个online log，用于记录其他会话的DML操作。当执行DML操作就有可能会出现重复键错误。可能错误是暂时的，之后在online log中被恢复。

8.InnoDB表的OPTIMIZE TABLE被映射到alter table操作，重建表并更新索引，释放没用的空间。

9.在5.6版本之前，innodb不支持alter table ALGORITHM=INPLACE。

14.12 innodb启动选项和系统变量

查看：14.11 InnoDB Startup Options and System Variables

14.13 InnoDB性能

14.13.1 InnoDB buffer pool配置

14.13.1.1 配置InnoDB Buffer Pool预读(Read-Ahead)

预读是I/O请求异步的获取多个page。InnoDB有2个预读算法来提高I/O性能：

Liner

线性预读根据buffer pool中的page，预测需要什么page。通过参数调整什么时候执行预读，读取多少page，使用innodb_read_ahead_threshold就可以。

innodb_read_ahead_threshold值越大越不会触发。

Random

若buffer pool找打了13个page来自同一个extent，innodb会启动异步I/O获取其他page。由innodb_randmon_read_ahead参数控制。

SHOW ENGINE ENNODB STATUS显示统计信息来评估预读算法的效果。innodb_buffer_pool_read_ahead通过预读读入的page，innodb_buffer+pool_ahead_evicted,通过预读读入但是被牺牲的page。

14.13.1.2 配置InnoDB刷新比率

当脏页超过innodb_max_dirty_page_pct时之后会启动刷新进程。innodb使用算法根据当前redo log生产和当前的刷新来生产当前刷新率。

innodb使用log文件，在重用log前，要把相关的脏页写入到数据文件中，这就是sharp checkpoint，当log file中所有可用空间都用完了，也会发生sharp checkpoint，即使innodb_max_dirty_page_pct没到达也会发生。

innodb通过启发式的算法，避免这种情况发生。通过评估dirty和生成的redo来决定flush多少脏页。

因为自适应flush会影响I/O,innodb_adaptive_flushing可以开关自适应flush，默认为true。

14.13.1.3 Buffer Pool处理scan

InnoDB的LRU算法目的是让hot数据尽量保存在内存中。新的page被读入默认插入到LRU列表距离尾部3/8处。若再次被提到队列前面，如果不再被访问，就不放到队列前面。可以通过参数innodb_old_block_pct来调整插入到LRU列表的点。默认是37也就是3/8处。取值范围是5-95。

为了避免预读有类似问题，影响buffer pool配置innodb_old_blocks_time判断第一次访问后放入列表头的倒计时。

innodb_old_blocks_pct和innodb_old_blocks_time可以在配置文件，启动参数，也可以set global设置。

通过show engine innodb status命令查看：

Total memory allocated 1107296256; in additional pool allocated 0

Dictionary memory allocated 80360

Buffer pool size   65535

Free buffers       0

Database pages     63920

Old database pages 23600

Modified db pages  34969

Pending reads 32

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 414946, not young 2930673

1274.75 youngs/s, 16521.90 non-youngs/s

Pages read 486005, created 3178, written 160585

2132.37 reads/s, 3.40 creates/s, 323.74 writes/s

Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000

Pages read ahead 1510.10/s, evicted without access 0.00/s

LRU len: 63920, unzip_LRU len: 0

I/O sum[43690]:cur[221], unzip sum[0]:cur[0]

old database pages表示LRU 列表 old段的page个数

pages made young和not young，表示old段的page被标记为young和没有被标记的个数。

youngs/s和non-youngs/s，表示表示被youngs的速度。

young-making rate和not提供了一样的速率，但是是整个buffer pool的不是old段。

Note

每秒的速度是通过上次监视器和这次监视器的取值的差值。

这些选项根据硬件配置关系较大，所以在设置前要先进行压力测试。

在混合负荷下，oltp为主，和定期的报表查询，可以使用innodb_old_blocks_time参数。

当读取的表放不进buffer pool可以通过设置innodb_old_blocks_pct，如果为5 表示只有5%的buffer pool别使用，不影响其他的buffer pool。

若扫描表的表，innodb_old_blocks_pct可以默认或者更高，对性能影响较小。

innodb_old_blocks_time更难预测，因为和负荷类型关系密切。

14.13.1.4 使用多buffer pool实例

对于有几个G的buffer pool可以吧buffer pool分为多个实例提高并发减少冲突。可以使用innodb_buffer_pool_instances来配置。使用innodb_buffer_pool_size来配置buffer大小。

当buffer pool变大，很多数据可以存放在buffer pool中，但是多个线程同时访问buffer pool可能会出现瓶颈。可以使用多个buffer pool来最小化争用。每个page被分配到哪个buffer pool是随机的。每个buffer pool都有自己的信号量。

Innodb_buffer_pool_instances取值1~64，只有当innodb_buffer_pool_size>1GB，上面的参数才有用。指定的总大小会被分到所有的buffer pool。

14.13.1.5 快速重启后加载buffer pool

为了避免重启后长时间的warm up，你可以再服务关闭的时候保存innodb buffer pool状态，在启动的时候，恢复buffer pool状态。

一般重启之后，warm up是逐步增加的，把之前的page加入到buffer pool。Preload可以再关闭之前的buffer pool加载到内存。而不需要DML来warmup。

你可以再服务运行的任何时间，保存buffer pool状态。尽管buffer pool有好几G的大小，但是保存数据还是很少的，值保存了表空间id，page id用来定位page的数据。这些数据来源于INNODB_BUFFER_PAGE_LRU 默认表空间id，page id被保存在ib_buffer_pool,innodb_buffer_pool_filename可以设置buffer pool状态文件位置。

后台专门有个线程来处理buffer pool dump和load。压缩表上的page，以压缩方式被加载到buffer pool。

保存buffer pool 状态

在关闭服务前，保存buffer pool状态

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

在服务运行时，保存buffer pool状态

SET GLOBAL innodb_buffer_pool_dump_now=ON;

恢复buffer pool状态

在恢复时，load buffer pool状态

mysqld --innodb_buffer_pool_load_at_startup=ON;

在服务运行时，恢复buffer pool状态

SET GLOBAL innodb_buffer_pool_load_now=ON;

查看buffer pool dump进度

显示Save进度

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

或者

SELECT variable_value FROM information_schema.global_status WHERE

variable_name = 'INNODB_BUFFER_POOL_DUMP_STATUS';

如果没有运行，显示not started，如果已经完成显示 completed at 150928 17:27:51，还在处理，显示Dumping buffer pool 5/7, page 237/2873。

取消buffer pool load

SET GLOBAL innodb_buffer_pool_load_abort=ON;

14.13.1.6 调整InnoDB buffer pool刷新

参数innodb_flush_neighbors和innodb_lru_scan_depth可以调整innodb buffer pool的刷新。

innodb_flush_neighbors：刷新innodb page是否把同一个extent的也刷新。

innodb_lru_scan_depth：扫描深度，若I/O有余可以放大。

对于DML活跃的若不够聚合刷新操作会被拉开，造成高昂的内存消耗，如果太激进会导致I/O能力耗光。最好的办法是依赖工作负荷，数据访问方式和存储来配置。

对于基本不变的繁忙负荷，或者负荷波动较大的，以下一些参数可以调整innodb表的刷新行为：innodb_adaptive_flushing_lwm，innodb_max_dirty_pages_pct_lwm，innodb_io_capacity_max，innodb_flushing_avg_loops这些通过公式被innodb_adaptive_flushing使用。

Innodb_adaptive_flushing,innodb_io_capacity和innodb_max_dirty_pages_pct 受限于:

innodb_adaptive_flushing_lwm,innodb_io_capacity_max ,innodb_max_dirty_pages_pct_lwm:

1.InnoDB自适应刷新，并不适用于所有case，可能在redo log在filling up危险时又好处。Innodb_adptive_flusing_lwm表示一个redo log能力的低水位百分比，当达到阀值，innodb可以在没有指定innodb_adaptive_flushing的情况下启动flush。

2.若flush活动，被落的很多，innodb会比innodb_io_capacity_max更加积极的进行刷新。Innodb_io_capacity_max表示I/O能力的上限，为了不让I/O丢被耗光。

3.为了让dirty page不超过innodb_max_dirty_pages_pct，innodb会刷新buffer pool中的数据。默认参数值为75。

Note

Innodb_max_dirty_pages_pct，建立起刷新活动的目标。

Innodb_max_dirty_pages_pct_lwm选项指定低水位表示脏页的百分比，来控制脏页比率防止到达阀值innodb_max_dirty_pages_pct。

很多这些选项适用于长时间写入并几乎不会没有减少负荷的时间，让写入磁盘跟上。

Innodb_flushing_avg_loops表示之前被计算刷新状态的快照，控制了自适应flush如何快速的反应前台负荷变化。Innodb_flushing_avg_loops值越大，保存快照的时间越长，自适应刷新响应越慢。值大，也会减少前后台工作的反馈，设置的很高但是要保证innodb redo log不能到达75%，并且根据负荷合理设置innodb_max_dirty_pages_pct。

系统负荷基本不变，大innodb_log_file_size和small spike没有到达redo log的75%空间使用，可以把innodb_flushing_avg_loops设置的稍微高一点，减慢flush。对于极端的情况或者空间不够，可以考虑设置的小一点，避免redo log超过75%，更上负荷。

Innodb_flush_avg_loops关于刷新频率控制在代码 buf0flu.cc::page_cleaner_flush_pages _if_needed，参考：InnoDB原生Checkpoint策略及各版本优化详解

14.13.2 InnoDB信号量和Read/Write锁实现

InnoDB多线程访问共享数据，innodb同步使用自己实现的mutex和read/write lock。在很多平台，有很多更高效的方式来实现read/write lock。原子操作通常用来同步多个线程的行为比pthread更有效。每个操作获取和释放锁需要的指令更少。在访问冲突时浪费的时间更少，也就是说在多核平台上扩展性更好。

Innodb实现的mutex，read/write lock使用gcc自带的原子内存访问而不是pthread。特别是innodb通过gcc 4.1.2编译，使用gcc自带的原子访问来代替pthread_mutex_t。

这些修改提高了innodb在多核系统中的扩展性。对于原子内存访问不可用的，innodb会使用传统的pthreads方法来代替。

在启动mysql，innodb会写一个消息到日志文件表示原子内存访问是否被用于mutex，read/write lock。另外compare-and-swap操作可以被用在线程表示，然后innodb同时也可以使用read-write lock。

14.13.3 配置InnoDB的内存分配

在innodb被开发，内存分配由os和runtime library提供。如果innodb在mem子系统自己实现内存分配，分配器由一个mutex决定，可能会变成瓶颈。Innodb也对系统分配器最了分装，像mutex一样。

现在多核系统广泛使用，OS也成熟，OS提供的内存分配页有提升，比之前的更好，扩展性也更好。高效的内存分配器如Hoard, libumem, mtmalloc, ptmalloc, tbbmalloc, 和TCMalloc。对于跟多workload，内存分配释放频繁，使用高调整的分配器，比使用innodb的分配器更好。可以通过innodb_use_sys_malloc来决定使用自己的分配器或者OS的，1使用OS的分配器，0，使用自己的分配器。

当innodb内存分配器禁用了，innodb会忽略参数innodb_additional_mem_pool_size的配置，innodb使用额外的内存池，保证安全分配不会被退回到系统内存分配器。当innodb内存分配禁用，所有分配都由系统内存分配器完成。

当使用系统备份分配器(innodb_use_sys_malloc=on)，因为innodb不能根治所有的内存使用。在show engine innodb status命令的BUFFER POOL AND MEMEORY段输出只有total memory allocated的统计。

14.13.4 配置innodb Change Buffer

当insert，update，delete操作在表上执行。Innodb有一个insert buffer当page不在内存中会缓存secondary index的修改，避免不必要的I/O操作。当被load到buffer pool就会和insert buffer进行合并。Innodb主线程在空间或者shutdown的时候会meger缓存的修改。

Insert buffer是buffer pool的一部分，会占用一些内存。如果workset几乎要占用所有内存，取消insert buffer可能会更好。

Innodb_change_buffering用来控制insert buffer，取值如下：

All：默认值，buffer insert，delete-marking和purges

None:不buffer任何操作

Insert：buffer insert操作

Delete：buffer delete-marking操作

Changes：buffer insert和delete-marking

Purges：buffer 后台清理操作。

可以设置的my.ini里面，也可以使用set global设置。

14.13.5 配置InnoDB的线程并发

Innodb使用os线程来处理用户事务，现在的os和服务器，有多个核，上下文切换高效，很多工作在没有并发线程限制下运行的很好。

Innodb会使用很多技术限制osthread并发执行数量，从而最小化上下文切换。当innodb接到用户会话的请求，若并发线程的数量被限制，那么新的请求会sleep，请求也不会sleep后被调度而是进去了先进先出的队列。

通过innodb_thread_concurrency来限制并发线程，在innodb_thread_sleep_delay在进入队列之前sleep的微秒。

MySQL 5.6.3之后设置innodb_adaptive_max_sleep_delay可以自适应调节sleep，是innodb_thread_sleep_delay的最高值。

Innodb_thread_concurrency=0表示并发线程没有限制。只有当并发线程数被限制的时候，才会sleep。如果不限制innodb_thread_sleep_delay被忽略。

当有线程限制时，innodb通过允许多个请求进入一个正在执行的会话，来减少上下文切换，不需要去观察是否超出innodb_thread_concurrency。因为SQL语句包含了多个行操作，innodb会分配tickets允许线程重复被调用。

当SQL开始被运行,没有tickets会看innodb_thread_concurrency获得进去innodb的权力，并分配tickets。当tickets被用完会就查看innodb_thread_concurrency获得ticket，分配的tickets个数由innodb_concurrency_tickets决定。

14.13.6 配置innodb I/O后台线程数量

InnoDB使用一些后台线程来为不同的I/O服务。使用innodb_read_io_threads和innodb_write_io_threads来配置读写的线程个数。这些参数的默认值为4，范围在1-64。

这个目的是为了让innodb在高端system上有更好的扩展性。每个后台线程可以控制256个堵塞I/O请求。大多数后台I/O的来源是预读。Innodb视图平衡输入的负荷，很多后台线程都是共享任务的。Innodb也试图把对同一个extent的读放到同一个线程上。如果再高端的I/O子系统上看到大于64*innodb_read_io_threads的堵塞读请求，在show engine innodb status上，可以增加innodb_read_io_threads。

14.13.7 分组提交

历史上InnoDB和其他数据库一样，在commit之前先要flush redo log。Innodb使用group commit，避免每个commit做一次flush。使用分组提交，innodb使用一个写入对多个用户事务进行提交，可提高吞吐量。

在MySQL 5.1之后被取消了。

分组提交功能，在innodb内不是使用二阶段提交协议的，重新使用是为了保证binlog和

中的顺序和以前一样。当启动了binlog，只有当sync_binlog=0时，才能使用分组提交。

14.13.8 配置InnoDB主线程I/O率

InnoDB主线程执行各种后台任务，大多数和I/O有关：1.flush脏页，2.把insert buffer写入到对应的index。

主线程试图以新的方式执行这任务，并不影响其他正常工作。主线程尝试评估I/O带宽可用，在空闲时执行。

参数innodb_io_capacity表示innodb可用的I/O，这个参数表示每秒的I/O操作。主线程根据innodb_io_capacity评估可用的带宽。

Innodb_io_capacity设置也限制所有的buffer pool实例。当脏数据刷新，buffer pool会均分innodb_io_capacity。

在MySQL5.6.5和更高版本，这些IO操作被移动到了后台其他线程，这些值由innodb_purge_threads控制。

14.13.9 在InnoDB自旋循环中使用PAUSE指令

Innodb内部同步通常会使用自旋锁，在等待的时候，innodb运行语段小的循环指令，避免重新调度。若自旋运行的太多会浪费资源，而造成失误吞吐量下降。大多数现代处理器都实现了PAUSE指令在自旋上的使用。这样处理器可以更加高效。

Innodb使用PAUSE指令可以增加CPU密集操作的性能。

14.13.10 设置自旋锁轮询

很多innodb mutexes和rw-lock被保存很短的是时间，多和系统，一个线程在sleep之前，连续的检查mutex和rw-lock可能会更加有效。若mutex或者rw-lock在轮询变可用，thread可以再同一个时间片上马上运行。如果太频繁的轮询会导致cache ping pong。Innodb最小化这个问题是，在轮询之间等待一个随机事件。

通过参数innodb_spin_wait_delay轮询之间的最大延迟，延迟的时间由编译器和目标处理器决定。对于所有的cores共享一个cache可以减少延迟，或者设置innodb_spin_wait_delay=0限制繁忙循环在同时进行。

默认innodb_spin_wait_delay=6,可以设置在my.cnf或者set global。

14.13.11 InnoDB使用MySQL性能框架整合

从InnoDB 1.1开始，可以profile整个innodb操作,这种tuning是为了专家级用户。DBA也可以使用这个功能做容量计划，看是典型的workload是否有性能瓶颈问题。来判断是否可以通过增加一些系统能力来提高性能。具体查看22章

使用这个特性需要检查：

1.必须使用MySQL5.5或者更高版本，启动了performance schema功能。查看：22.2 Performance Schema Configuration

2.必须熟悉如何使用performance schema特性

3.在performance_schema检查一下不同的innodb对象。查看：22.9 Performance Schema Table Descriptions

a.mutex在mutex_instances表

b.rw-lock在rwlock_instances表

c.文件io操作，file_instances,file_summary_by_event_name和 file_summy_by_instance表

d.线程在PROCESSLIST表。

4.在性能测试的时候检查，events_waits_current,events_waits_history_long表中的数据。

14.13.12 使用多RollBack Segment获得更好的扩展性

从innodb 1.1开始并发事务有极大的扩展，删除了之前innodb rollback segment的瓶颈限制。

现在从一个rollback segment分成128个segment每个都支持1023个事务读写。总共可以适应128K个并发事务。

每个事务都会被分到一个rollback segment。使用innodb_rollback_segment来配置rollback segment个数。

14.13.13 配置InnoDB清理调度

清理操作由innodb独立自动运行的一个或者多个线程执行，而不是master thread的一部分。可以使用innodb_purge_threads配置线程个数。若DML比较集中就设置的小一点，这样就不会和其他访问争用资源。若比较分散就设置的大一点。

另外一个相关的参数innodb_purge_batch_size默认为20，最大为5000

14.13.14 优化InnoDB只读事务

如果是只读事务，可以避免启动事务ID的一些相关开销。事务ID只有在写事务，或者select for update才会被需要。

InnoDB如何识别只读事务：

1.以start transaction read only启动的事务。

2.自动提交事务非锁定select，没有for update或者lock in shared mode。因此读密集的应用，可以把一组查询放在start transaction read only和commit内。

14.13.15 使用CRC32 Checksum算法，快速checksum

CRC32 checksum算法，在mysql5.6.3加入，一次扫描32bits，通过优化只需要扫描8bit。可以使用innodb_checksum_algorithm=crc32来启动。

当crc32算法启动，一旦被使用crc32算法写入，以前的版本就无法读取。当启动新的mysql实例。并且innodb数据使用crc32算法创建。可以使用strict_crc32比crc32快，因为不需要向后兼容。

14.13.16 undo log保存在独立包空间

从MySQL 5.6.3可以创建InnoDB undo log可以存放在独立表空间中。Undo log也就是rollback segment。有几个相关的配置参数：

1.innodb_undo_tablesspaces

2.innodb_undo_directory

3.innodb_rollback_segment,变成了innodb_undo_logs，前缀为了兼容而存在。

注意点

1.决定使用高速存储存放undo logs，使用innodb_undo_directory指定路径。

2.决定innodb_undo_logs的非0值，一开始可以使用较小的，之后在放大。

3.innodb_undo_tablespaces非0只，有innodb_undo_logs个数的undo log被创建在制定的独立表空间中。

4.创建新的MySQL实例，把这些值写到配置文件上

5.测试I/O性能。

6.定期增加innodb_undo_logs并且重新性能测试。

7.以理想的配置部署到生产环节。

性能和可扩展性考虑

把undo logs放到另外的文件里面允许MySQL统一的维护I/O和内存。如undo数据被写入磁盘并且很少被使用，就没必要文件系统内存cache，可以把更多内存给buffer pool使用。

内部

物理表空间文件已undoN命名,N是空间ID

14.13.17 优化统计信息

14.13.17.1 配置持久优化统计信息参数

InnoDB计算innodb每个表的统计信息机帮助优化器查找最有效的执行计划。这个特性默认是启动的，通过innodb_stats_persistent参数。

可以通过innodb_stats_persistent_sample_pages采样page个数。是否自动重新计算统计信息通过参数innodb_stats_auto_recalc控制。当数据有大于10%被修改就会重新统计。

如果关闭了innodb_stats_auto_recalc,需要通过analyze table来保证优化器统计信息的准确性。当新索引被增加到表中，索引统计信息会被计算并且添加到innodb_index_stats表上不管有没有开innodb_stats_auto_recalc。

在创建表时，可以使用innodb_stats_persistent，innodb_stats_auto_recalc，或者使用create table和alter table的stats_persistent,stats_auto_recalc，stats_sample_pages子句。后者会覆盖前者。

统计信息在服务重启后被清理，在表下次被访问的时候重新计算。统计信息使用随机采样，2次统计信息可能不一样，会导致不同的执行计划。

配置innodb优化统计信息采样

MySQL查询优化器使用评估的统计信息来选择索引。采样page个数通过设置参数innodb_stats_persistent_sample_pages，默认为20。

修改采样率时考虑一下状况：

1.若统计信息不够优化器选择的计划可能不是罪理想的。统计信息准确性可以通过使用select distinct和mysql.innodb_index_stats对比。

innodb_stats_persistent_sample_pages太少会导致统计信息不够准确，太多会导致分析执行太慢。

2.analyze table太慢可以减少采样率，若太少会导致统计信息不准。

InnoDB持久化统计信息表

持久化特性依赖于MySQL内部的表管理，innodb_table_stats和innodb_index_stats.

innodb_table_stats：

Column name	Description
database_name	数据库名
table_name	表名，分区名或者子分区名
last_update	最后一次更新时间戳
n_rows	表中数据行数
clustered_index_size	聚集索引page个数
sum_of_other_index_sizes	非聚集索引page个数

innodb_index_stats：

Column name	Description
database_name	数据库名
table_name	表名，分区名或者子分区名
index_name	索引名
last_update	最后一次更新时间戳
stat_name	统计信息名
stat_value	统计信息不同值个数
sample_size	采样page个数
stat_description	描述

14.13.17.2 配置非持久性统计信息值

从MySQL 5.6.6开始统计信息默认是持久化的。通过参数innodb_stats_persistent控制。优化器通过统计信息来选择索引。Innodb_stats_transient_sample_pages控制采样pages个数，默认为8。Innodb_stats_transient_sample_pages可以runtime设置。

Note

Innodb_stats_transient_sample_pages在innodb_stats_persistent=0的时候影响采样。注意点：

1.若值太小，会导致评估不准

2.若果值太大，会导致disk read增加。

3.会生产很不同的执行计划，因为统计信息不同。

当使用show table status，show index时，查询 INFORMATION_SCHEMA.TABLES 或INFORMATION_SCHEMA.STATISTICS并设置了innodb_stats_on_metadata必须更新统计信息。

Note

在MySQL 5.6.6,默认启动统计信息持久化，默认innodb_stats_on_metadata为0.可能会减少schema的访问速度，若表或所有太大。

当MySQL Client以—auto-rehash启动，innodb所有的表都会被打开，相关的索引统计信息都会被更新。

不管innodb_stats_transient_sample_pages是什么，选择一个值会被有太多I/O有能保证统计信息相对准确。

14.13.18 评估ANALYZE TABLE复杂性

Analyze table的复杂性依赖于：

1.innodb_stats_persistent_sample_pages

2.被索引的列数

3.分区个数

复杂度=采样个数*索引列*分区个数

14.14 InnoDB INFORMATION_SCHEMA表

具体看：14.12 InnoDB INFORMATION_SCHEMA Tables

14.15 InnoDB监视器

14.15.1 InnoDB监视器类型

有4种InnoDB监视器：

标准InnoDB监视有一下信息：

1.每个活跃事务保留的表和记录锁

2.事务锁等待

3.线程信号量等待

4.挂起文件的IO请求。

5.buffer pool统计信息。

6.清理和insert buffer合并活动

InnoDB锁监视器提供额外的锁信息。

InnoDB表空间监视器打印出所有共享表空间中的segment，验证表空间分配的数据结构

InnoDB表监视器打印innodb内部数据字典。

14.15.2 启动innodb监视器

当可以启动innodb监视器定期输出，innodb会定期输出到mysqld标准错误输出。一般会输出到错误日志中。

innodb会把诊断输出到stderr或者文件中，不会输出到buffer中，以免buffer溢出。SHOW ENGINE INNODB STATUS输出，每个15秒钟都会写入到一个状态文件，innodb_status.pid，文件在服务关闭后被删除。pid是服务的进程id。只有在innodb-status-file=1的情况下才会被创建。

innodb监视器可以在使用时启动。但是最好只在想要看的时候启动，监视器会照成一定的性能下降。如果把监视器信息输出到相关表，如果忘记把表关闭了你的错误日志可能会变的很大。

启动标准innodb监视器

启动监视器定时输出：

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;

禁用标准监视器定时输出：

DROP TABLE innodb_monitor;

MySQL 5.6.16之后，可以使用参数innodb_status_output来启动和禁用。

set GLOBAL innodb_status_output=ON;

需要时输出标准监视器

mysql> SHOW ENGINE INNODB STATUSG

启动InnoDB锁监视器

启动定时输出

CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;

关闭定时输出

DROP TABLE innodb_lock_monitor;

MySQL 5.6.16之后，启动innodb锁监视器，需要设置innodb_status_output，innodb_status_output_locks。

set GLOBAL innodb_status_output=ON;

set GLOBAL innodb_status_output_locks=ON;

因为标准监视器会输出锁信息。所以启动锁监视器必须全部配置。

启动InnoDB表空间监视器

启动定时输出：

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB;

关闭定时输出：

DROP TABLE innodb_tablespace_monitor;

启动InnoDB表监视器

启动定时输出：

CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB;

关闭定时输出：

DROP TABLE innodb_table_monitor;

14.15.3 InnoDB标准监控输出和锁监控输出

锁监控和标准监控基本一样，特别是锁信息部分。如果启动了标准监控和锁监控，其实是一个输出流，就是多了额外的锁信息。

具体输出的例子：14.14.3 InnoDB Standard Monitor and Lock Monitor Output

输出段

Status：

显示时间戳，监视器名，生产结果基于之前几秒钟

BACKGROUND THREAD：

srv_master_thread显示，主后台显示是如何工作的。

SEMAPHORES：

输出线程等待信号量和统计信息关于多少次spin等到，等待mutex或者rw-lock。大量的线程等待，在innodb中可能是磁盘IO,和资源争用问题。innodb_thread_concurrency减少并发线程，可以减少并发。spin rounds per wait显示每个os wai等待mutex的spinlock的rounds。

LATEST FOREIGN KEY ERROR

这个信息是关于最近的外键错误。

LATEST DETECTED DEADLOCK

最近的deadlock信息。

TRANSACTIONS

当前信息锁等待信息报告，可以发现锁冲突。

FILE I/O

显示I/O线程信息，执行各种不同类型的I/O。由innodb_read_io_threads和innodb_write_io_threads来控制这些线程个数。

INSERT BUFFER AND ADAPTIVE HASH INDEX

显示innodb insert buffer和自适应hash index，和各自的统计信息。

LOG

显示了innodb log信息，当前log顺序，flushed的log，checkpoint的log信息。也显示了挂起的写入和写入性能信息。

BUFFER POOL AND MEMORY

page的写入和读取统计信息。

ROW OPERATIONS

显示了主线程的行为，包括每个类型行操作的性能。

参考：

https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/

show engine innodb status 详解

14.15.4 InnoDB表空间监视器输出

InnoDB表空间监视器输出在共享表空间中，文件segment和验证表空间分配数据结构。表空间监视器不描述由innodb_file_per_table分配的表。

整体的表空间信息：

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

size：当前表空间的page个数

free limit：free list没有被初始化的最小的page number。

free extents:free extents个数。

not full frag extents:没有被完全填满的extent。

used page:被分配的pages。

full frag extents：被分配满的extents。

first seg id not used:没有使用的第一个segment ID

segment信息：

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:segment id

space，page:表空间号 0 表示共享表空间，page号 2表示inode

res：segment保留的page

used：segment分配的页的使用。

full text：被完全使用的extent

fragm pages：分配的初始化。

free extents：空闲的extents

not full extents：不满的extents

pages:非满的extents使用的page

14.15.5 表监视器

因为要在未来版本删除，略具体看：14.14.5 InnoDB Table Monitor Output

14.16 InnoDB备份和恢复

热备

mysql企业版提供备份工具，可以热备innodb和myisam。可以查看：http://dev.mysql.com/doc/mysql-enterprise-backup/en/.

percona 也出了一个免费的热备工具具体可以看：

Percona XtraBackup User Manual 阅读笔记

冷备

冷备就是在mysql服务关闭之后，复制走所有的文件。

替换备份类型

另外一种备份类型是mysqldump+binlog的方式。在之前有介绍。

执行恢复

innodb恢复在服务启动时自动运行。若出现损失或者磁盘错误，必须通过备份恢复。可以使用checktable来检查表是否顺滑哦。有时候page顺坏，可能是系统的文件缓存造成的，而不是磁盘数据。最好的方法是重启电脑。

14.16.1 innodb恢复进程

innodb crash恢复有一些步骤：

1.应用redo log。如果redo log找不到了就会被跳过。跳过redo log会加快恢复过程，但是不建议。

2.这一步可以并发执行：

a.回滚没有完成的事务。

b.insert buffer 合并,把insert buffer修改到secondary index上。

c.清理，删除删除标记的记录，不需要再被活动事务可见。

14.17 InnoDB和MySQL复制

InnoDB复制和MyISAM复制没什么区别。

对于为master安装一个新的slave，复制innodb表空间和log文件，也要复制innodb表的frm文件。把复制移动到slave上，如果innodb_file_per_table启动，还要复制ibd文件。

master中事务失败，并不影响整个复制，MySQL复制是基于binlog的，事务失败并不会被写入bin log。

master上外键的级联炒作，只有在slave上也有外键并设置了级联，才有效果。

具体看：14.16 InnoDB and MySQL Replication

14.18 InnoDB集成memcached

因为网上有对比性能测试，集成memcached性能远不如memcached。所以不做笔记

具体看：14.17 InnoDB Integration with memcached

14.19 InnoDB Troubleshooting

常用troubleshooting指南：

1.若出现错误，或者bug，查看error log

2.若关系到deadlock，启动了innodb_print_all_deadlocks，所有死锁信息都会打印到error log中。

3.发生DDL错误，无法打开innodb文件，就会有system cannot find the path specified错误。

4.当troubleshooting最好在命令行执行。

5.启动innodb监视器获取错误信息，若问题和锁有关启动lock 监视器，若和DDL有关开table监视器。InnoDB临时启动标准监视器输出，要一下条件：

a.长型号等待。

b.innodb不能buffer pool中空闲的block

c.67%的buffer pool被lock heap或者自适应hash index占用

6.使用check table诊断错误表。

14.19.1 troubleshooting InnoDB I/O问题

排查步骤依赖于问题发生在启动时，还是执行DML和DDL语句等。

初始化问题

如果innodb在试图初始化表空间或者日志文件出错，删除所有innodb创建的文件然后尝试重建。最简单的方式是以命令行启动，就可以看的很清楚。

runtime 问题

如果在炒作文件是，innodb打印一个操作系统问题，如下方式解决问题：

1.保证innodb文件目录存在，innodb log目录存在

2.保证mysqld有访问权限

3.保证mysqld可以读取配置文件

4.保证磁盘空间未满

5.保证数据文件目录没有冲突。

6.检查innodb_data_home_dir和innodb_data_file_path的值。

14.19.2 启动损坏的数据库

为了查询数据库page损坏，可以使用select into outfile通常是没有问题的，严重损坏会导致innodb后台操作crash，触发assert，导致redo crash。

这时候使用innodb_force_recovery强制innodb启动，阻止后台操作执行，这样可以dump表。

[mysqld]

innodb_force_recovery = 1

Warning

在使用参数钱保证已经做了备份，并从小的值开始递增，使用到了3以上要先在测试环境尝试，MySQL 5.6.6之后4以上都是只读的。

innodb_force_recovery默认是0，高值包容低值的特性。

1(SRV_FORCE_IGNORE_CORRUPT)

如果发现损坏page，让服务继续运行。

2(SRV_FORCE_NO_BACKGROUND)

阻止master thread和任何清理线程运行。

3(SRV_FORCE_NO_TRX_UNDO)

crash recovery之后不运行事务rollback

4(SRV_FORCE_NO_IBUF_MERGE)

防止insert buffer merge操作运行

5(SRV_FORCE_NO_UNDO_LOG_SCAN)

当启动数据库的时候，不查看undo log

6(SRV_FORCE_NO_LOG_REDO)

不使用redo log来恢复，这个值只能select * from t复杂的语句会导致错误发生。若表中的损坏不能让你dump全表数据，可以使用order by pk desc部分导出。为了安全起见不能使用写入。5.6.16版本之后4以上只能只读。

14.19.3 排查InnoDB数据字典操作

表定义保存在frm文件和innodb数据字典中，若移走frm文件或者服务crash信息会变的不一致。

CREATE TABLE问题

当error log有以下错误，表示有数据字典信息但是没有数据文件。

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.

打开表问题

mysql输出如下错误：

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

如果error log有以下错误，表示有遗留的frm文件

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?

被遗忘的中间表

如果alter table时crash，可能会有遗留的中间表。中间表名以“#sql-”开头。可以在数据目录上看到，表监视器，还有INFORMATION_SCHEMA库的表中看到。

移除遗留的中间表，执行一下步骤：

1.确定中间表，是修改前，还是修改后的。可以通过表监视器或者INFORMATION_SCHEMA的INNODB_SYS_TABLES或者INNODB_SYS_COLUMNS和INNODB_SYS_INDEXES查询表结构。

2.一旦确定是alter前，或者alter后，创建一个和中间表一样的数据库目录。

ysql> CREATE TABLE tmp LIKE employees.salaries; ALTER TABLE tmp DROP COLUMN to_date;

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

3.复制到数据目录

shell> cp tmp.frm employees/#sql-ib87.frm

4.删除中间表

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

Query OK, 0 rows affected (0.01 sec)

5.如果左边能够覆盖#sql-*.frm文件，删除。出现错误可以忽略

mysql> DROP TABLE `#mysql50##sql-36ab_2`;

ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'

表空间消失错误

如果启动了innodb_file_per_table,出现以下错误

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原来的表，这样可以成功删除表，并且innodb可以打印警告到错误信息，ibd文件消失。

14.19.4 InnoDB错误处理

以下items表示innodb如何错误处理：

1.如果运行超出空间，出现table is full错误并且innodb回滚错误处理。

2.事务死锁回滚事务，重试整个事务。锁超时innodb只回滚一个等待超时的语句。当事务回滚，因为死锁或者锁等待超时会取消事务内所有语句，如果以start transaction或者begin语句开始，rollback不会取消语句，后面的语句会变成事务的一部分知道commit或者rollback或者隐式提交的语句。（mariadb 测试中发现，关闭自动提交和start transaction一样，只回滚出错语句。）

3.重复键错误回滚sql语句，如果语句没有指定ignore选项。

4.行太长错误，回滚SQL语句

5.其他错误由MySQL层代码发现，回滚相应的SQL语句。

14.19.5 InnoDB错误代码

具体看：14.18.5 InnoDB Error Codes

14.19.6 系统错误代码

略

相关阅读:
P2731 骑马修栅栏 Riding the Fences
P1351 联合权值
 [脚本]为所有节点挂载磁盘
 [转]expect的安装
 挂载磁盘linux
[转]Mac 科研常用软件
 [转]error while loading shared libraries 错误解决办法总结
 linux安装SVN
Java web项目在linux环境下自动编译和部署脚本
 个人所得税计算方法
原文地址：https://www.cnblogs.com/Amaranthus/p/4857624.html