InnoDB存储引擎

InnoDB存储引擎：

InnoDB就是为了满足大数据量环境下高性能为目标而设计的

遵循ACID模型，支持事务，拥有从服务崩溃中恢复的能力，最大限度地保护用户的数据。对于服务器软、硬件问题导致的宕机，MySQL拥有自动进行故障恢复能力

支持行级锁，提升多用户并发时的读写性能

InnoDB引擎表组织数据时按照主键(Primary Keys)聚簇，通过主键查找数据时性能极为优异

InnoDB支持外键(Foreign-Key)约束

InnoDB拥有自己独立的缓存池(对应innodb_buffer_pool_size系统变量，类似Oracle的SGA)，常用数据(含缓存)都在缓存中

对于INSERT、UPDATE、DELETE操作，会被一种称为change buffering的机制自动优化。InnoDB提供了一致性读，而其还能够缓存变更的数据，以便减少磁盘I/O

(system@localhost) [hugc]> create table t_idb1 (id int not null auto_increment primary key,v1 varchar(20)) engine=innodb auto_increment=1;

InnoDB特性：

所有表都要创建主键

多表关联，使用join，为提高join性能，最好在join列上创建索引，并且join条件的这些列最好使用相同的数据类型和定义

综合考虑磁盘I/O能力，必要时可以禁用autocommit自动提交功能

相互关联的DML操作放到同一个事务中进行

尽量使用select…for update语句

启动innodb_file_per_table选项，以使表中数据和索引保存在单独的文件中，而不是保存到系统表空间(System Tablespace)

评估数据和读写行为是否适用心的压缩(compression)特性

启动MySQL服务时附加--sql_mode=NO_ENGINE_SUBSTITUTION，防止表被创建成其他存储引擎

在新版本中，删除或创建索引性能有所提升，对系统的冲击有所降低

清空(truncate)表非常快，并且释放的空间能够被操作系统重用

使用DYNAMIC格式保存大数据类型将更有效率

在INFORMATION_SCHEMA库中提供了若干新的表对象，可用于监控存储引擎的工作和负载情况

在PERFORMANCE_SCHEMA库，可以用来查看存储引擎的性能统计数据

InnoDB引擎配置：数据文件放在哪里，内存给多少

指定InnoDB引擎数据文件保存路径，默认情况下InnoDB引擎只对应一个表空间，即系统表空间，所有InnoDB引擎表的数据(含索引)都存储在该表空间中，注意仅仅是保存数据，表对象的结构仍然需要保存在与表对象同名的.frm文件中

innodb_data_home_dir参数设置InnoDB数据文件位置，默认保存在MySQL的data目录下

innodb_data_file_path设置数据文件

innodb_data_file_path=datafile_spec1[;datafile_spec2]…

datafile_spec=file_name:file_size[:autoextend[:max:max_file_size]] 4个部分

file_name文件名

file_size文件大小

autoextend是否扩展，默认一次扩展8M，通过innodb_autoextend_increment进行设置

:max:max_file_size最大占用空间

innodb_data_file_path=ibdata01.df:2048M:autoextend:max:100G

Multiple Tablespaces多重表空间，每个表对象拥有一个独享的.ibd为扩展名的数据文件，这个文件就是一个独立的表空间，还有一个同名的.frm表结构定义文件。是否启用多重表空间是由系统变量innodb_file_per_table来控制，5.6以后默认开启

多重表空间的优点：

各表对象的数据独立存储至不同的文件，可以更灵活地分散I/O、执行备份及恢复操作

能够支持compressed row format压缩存储数据

当执行TRUNCATE/DROP删除表对象时，空间可以即时释放回操作系统层

空间自动扩展，无需额外配置

(system@localhost) [hugc]> show variables like 'innodb_file_per_table';

如果目标对象拥有同名.ibd文件，那么就表明该对象使用了独立表空间，否则就是使用的系统表空间。对象同名的.ibd文件不能被随意移动。

配置InnoDB日志文件：

默认情况下，InnoDB引擎会创建两组大小均为5MB的日志文件，分别命名为ib_logfile0和ib_logfile1。默认保存在MySQL的data目录下

innodb_log_group_home_dir指定InnoDB的REDOLOG日志文件保存路径

innodb_log_file_size指定日志文件大小，默认5M，最大不能超过512G

innodb_log_files_in_group指定日志文件组的数量，默认是2个

InnoDB日志文件相关的这3个参数均不支持动态修改

根据实际情况进行设置

innodb_log_file_size=512M

innodb_log_file_in_group=3

 

(system@localhost) [hugc]> show global variables like 'innodb_fast_shutdown';

innodb_fast_shutdown参数，用来控制InnDB的关闭模式，共有0、1、2三种

默认值是1，InnoDB将关闭会话终止连接，将已提交的数据刷新至数据文件，未提交的事务则进行回滚，这种方式称为快速关闭(Fast Shutdown)，类似Oracle中的shutdown immediate

0模式，要等到会话关闭、所有事务结束、缓存区中的数据刷新到磁盘等，类似Oracle中的shutdown normal

2模式，将忽略当前执行的所有操作，直接关闭，类似Oracle中的shutdown abort，下次启动时InnoDB执行故障恢复，重新读取日志文件中的数据，回滚未提交事务等

 

修改所有日志文件大小为512M，组数为5组，路径为/data/mysqldata/3306/data/redolog，步骤如下：

停止MySQL服务：

$mysql_db_shutdown.sh

移动旧的日志文件到备份路径下：

$mv /data/mysqldata/3306/data/ib_logfile* /data/mysqldata/backup/

修改my.cnf中控制日志文件相关的参数：

$vim /data/mysqldata/3306/my.cnf

innodb_log_group_home_dir=/data/mysqldata/3306/data/redolog

innodb_log_file_size=512M

innodb_log_file_in_group=5

 

下面列出表空间和日志文件位置：

[root@mysql1 data]# ll

total 1835028

-rw-rw----. 1 mysql mysql         56 Jul  2 23:47 auto.cnf

-rw-rw----. 1 mysql mysql 1073741824 Jul  4 14:44 ibdata1

-rw-rw----. 1 mysql mysql  268435456 Jul  4 14:44 ib_logfile0

-rw-rw----. 1 mysql mysql  268435456 Jul  2 23:47 ib_logfile1

-rw-rw----. 1 mysql mysql  268435456 Jul  2 23:47 ib_logfile2

drwx------. 2 mysql mysql       4096 Jul  4 13:56 hugc

drwx------. 2 mysql mysql       4096 Jul  4 10:01 mysql

drwx------. 2 mysql mysql       4096 Jul  2 23:47 performance_schema

 

UNDO表空间：

在MySQL数据库中，回滚段默认都是保存在系统表空间内。从5.6开始，InnoDB引擎中的UNDO日志可以单独设置表空间

想要使用独立的UNDO表空间，相关参数如下

innodb_undo_directory用于指定保存UNDO日志的物理文件的位置

innodb_undo_tablespaces用于指定UNDO表空间的数量，每个UNDO表空间都是独立的.idb文件，可以理解为UNDO数据文件的数量

独立UNDO表空间，必须在建库之前进行配置。UNDO表空间一旦创建就不能删除，因为这其中包含了一些当前或以后需要使用的数据，UNDO表空间会在该MySQL实例的生命周期中生效，无法变更，一旦使用，就无法降级到5.6之前的版本

 

创建和使用InnoDB表对象：

session级禁用自动提交

(system@localhost) [mysql]> set autocommit=off;

全局禁用自动提交

方法1：myql>set global init_connect=’SET autocommit=0’;

方法2：在初始化参数文件中设置

[mysqld]

init_connect=’set autocommit=0’

方法3：启动时带上参数--init_connect=’SET autocommit=0’

显示声明事务：

(system@localhost) [mysql]> set autocommit=on;

(system@localhost) [mysql]> start transaction;

(system@localhost) [mysql]> insert into t_idb1 values (1,’test’);

(system@localhost) [mysql]> commit;

(system@localhost) [mysql]> select * from t_idb1;

 

转换其他引擎表为InnoDB引擎

alter table myt engine=InnoDB;

 

逻辑存储结构：

使用innodb_file_per_table选项创建的表会减少磁盘文件的碎片，并且单表被truncate时，也能将占用的空间释放回操作系统层，而不像系统表空间那样，一旦占用就无法释放。

InnoDB的逻辑存储结构，从小到大分为4中粒度：

页(Pages，也叫块)，是InnoDB中的最小管理单位，同一个MySQL库，不管有多少表空间，所有表空间都拥有相同的页大小。默认Page size为16K，通过innodb_page_size进行配置，有4K，8K，16K

扩展(Extend，也叫区)，每个扩展固定1M大小，由多个Pages组成

段(Segments)，有很多种，比如像数据段，索引段，回滚段等，对于InnoDB来说，指的是独立于表空间对应的数据文件

表空间(Tablespaces)，InnoDB逻辑存储单元中的最高粒度

InnoDB表不是传统的堆组织表(Heap Organized)，而是索引组织表(Index Organized)。数据就是索引，索引就是数据。InnoDB表的索引需要两个段，一个用于B树的非页节点，另一个则用于B树的页节点

当使用SHOW TABLE STATUS语句查看当前可用的空闲空间时，InnoDB会显示表空间中空闲的Extent。InnoDB会保留一些Extent，用于内部操作，这些Extent不会被包含在空闲空间中。

执行SHOW ENGINE INNODB STATUS可以查看InnoDB的基础状态信息

如果一条记录的长度小于Page的一半，那么它能被完整地存储在一个Page内。当它增长到Page的一半时，变长列将选择在另外的Page保存，直到满足记录长度不超过Page一半的条件，类似Oracle的行链接。

 

表对象中的数据经过一段时间的增删改操作，可能会出现碎片。通过重建表的方式消除碎片。任意ALTER TABLE的操作都有可能导致表的重建，从而消除碎片。