[TOC]

一.InnoDB 存储引擎

1. InnoDB的历史

年份 事件 备注
1995 由Heikki Tuuri 创建Innobase Oy公司，并开发InnoDB存储引擎 Innobase开始做的是数据库，希望卖掉该公司
1996 MySQL 1.0 发布
2000 MySQL3.23版本发布
2001 InnoDB存储引擎集成到MySQL数据库 作为插件的方式集成
2006 Innobase被Oracle公司收购（InnoDB作为开源产品，性能和功能很强大） InnoDB在被收购后的，MySQL中的InnoDB版本没有改变
2010 MySQL5.5版本InnoDB存储引擎称为默认存储引擎 MySQL被Sun收购，Oracle被Oracle收购，使得MySQL和InnoDB重新在一起配合开发
至今 其他存储引擎已经不再得到Oracle官方的后续开发

2. InnoDB的特点

• Fully ACID （InnoDB默认的Repeat Read隔离级别就支持）
• Row-level Locking（支持行锁）
• Multi-version concurrency control（MVCC）（支持多版本并发控制）
• Foreign key support（支持外键）
• Automatic deadlock detection（死锁自动检测）
• High performance、High scalability、High availability（高性能，高扩展，高可用）

3. InnoDB存储引擎的文件

3.1 概述

InnoDB的文件主要分为两个部分，一个是表空间文件，一个是重做日志文件

• 表空间文件
  • 独立表空间文件
  • 全局表空间文件
  • undo表空间文件（from MySQL5.6）
• 重做日志文件
  • 物理逻辑日志
  • 没有Oracle的归档重做日志

3.2 InnoDB - 表空间

• 表空间的概念

  • 表空间是一个 逻辑存储 的概念
  • 表空间可以由多个文件组成
  • 支持裸设备（可以直接使用 O_DIRECT方式绕过缓存，直接写入磁盘）

• 表空间的分类

  • 系统表空间 （最早只有系统表空间）
  • 存储元数据信息
  • 存储Change Buffer信息
  • 存储Undo信息
  • 甚至一开始 所有的表和索引 的信息都是存储在系统表空间中
    • 随后InnoDB对其做了改进，可以使用独立的表空间

• 独立表空间

  • innodb-file-per-table=1 （开启支持每个表一个独立的表空间）
  • 每张用户表对应一个独立的 ibd文件
  • 分区表可以对应多个ibd文件

• Undo表空间

  • MySQL5.6版本支持独立的Undo表空间
  • innodb_undo_tablespaces

• 临时表空间

  • MySQL5.7增加了临时表空间（ibtmp1）
  • innodb_temp_data_file_path

  shell> ll ib* # MySQL的datadir目录 -rw-r-----. 1 mysql mysql 22913 Dec 27 23:56 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 Jan 3 15:27 ibdata1 # 系统表空间，默认所有信息存在这里 -rw-r-----. 1 mysql mysql 134217728 Jan 3 15:27 ib_logfile0 -rw-r-----. 1 mysql mysql 134217728 Jan 3 15:27 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 Jan 3 15:27 ibtmp1 # 临时表空间

  shell> cd burn_test # 在MySQL的datadir目录下，burn_test是自定义数据库，根据配置，默认为innodb的表

  shell> ll test_1* -rw-r-----. 1 mysql mysql 8554 Dec 3 20:14 test_1.frm #test_1表的表结构文件 #mysqlfrm --diagnostic test_1.frm可查看表结构 -rw-r-----. 1 mysql mysql 49152 Dec 3 20:14 test_1.ibd #ibd就是test_1这张表对应的innodb文件 #ibd中包含了索引和数据

• 说明：

  • 同一个表空间（ibdata1） 存储和 独立表空间 存储就 性能 上而言没有区别；
  • 当需要删除表（drop table）时， 独立的表空间 存储可以直接删除文件 ，而 ibdata1 存储也只是把 该部分表空间标记为可用 ，所以从速度上看很难说哪个更快；但是 删除文件 后， ibdata1 占用的 空间不会释放 ；
  • 分区表 会产生 独立 的 ibd文件 ；
  • 独立的表空间 ，一个表对应一个 ibd文件 ，给人的感觉更加直观；
  • 单个 ibd文件 直接拷贝到新的数据库中无法直接恢复：
    • 原因一： 元数据 信息还是在 ibdata1 中
    • 原因二：部分索引文件存在于 Change Buffer 中，目前还是存放于 ibdata1文件中

  select * from information_schema.innodb_sys_tablespaces; -- 查看表空间的元数据信息

3.3 General表空间

官方General表空间文档

假如，新建一张表，并让该表的存储路径不是默认的/r2/mysqldata 。而是 指定存储的位置 应该如何处理？

• 方法一，链接idb文件

  shell> mkdir /GeneralTest

  shell> chown mysql.mysql /GeneralTest

  mysql> create table test_ger1 (a int) data directory='/GeneralTest'; Query OK, 0 rows affected (0.15 sec)

  shell> cd /GeneralTest

  shell> tree . └── burn_test # dbname └── test_ger1.ibd # 表空间文件 1 directory, 1 file

  shell> ll test_ger1* # 在datadir 的 burn_test 目录下 -rw-r-----. 1 mysql mysql 8554 Jan 3 16:41 test_ger1.frm -rw-r-----. 1 mysql mysql 36 Jan 3 16:41 test_ger1.isl # 这是链接文件，链接到上面的ibd文件

  shell> cat test_ger1.isl # 一个文本文件，内容就是idb文件的路径 /GeneralTest/burn_test/test_ger1.ibd

• 方法二，使用通用表空间

1: 创建一个通用表空间

mysql> create tablespace ger_space add datafile '/r2/testdir/ger_space.ibd' file_block_size=8192;
Query OK, 0 rows affected (0.07 sec)

-- datafile 指定存储路径后，在datadir下会产生一个isl文件，该文件的内容为General space的ibd文件的路径
-- 如果datafile不指定路径，则ibd文件默认存储在datadir目录下，且不需要isl文件了

mysql> create tablespace ger2_space add datafile 'ger2_space.ibd' file_block_size=8192;
Query OK, 0 rows affected (0.06 sec)

shell> ll ger*
-rw-r-----. 1 mysql mysql 32768 Jan 3 16:51 ger2_space.ibd # 未指定路径，存放于datadir目录
-rw-r-----. 1 mysql mysql 26 Jan 3 16:50 ger_space.isl # 指定了其他路径，存在isl链接文件

shell> cat ger_space.isl 
/r2/testdir/ger_space.ibd # ibd文件真实存在的路径
mysql>  select * from information_schema.innodb_sys_tablespaces where name='ger_space'\G;
*************************** 1. row ***************************
         SPACE: 55
          NAME: ger_space
          FLAG: 2089
   FILE_FORMAT: Barracuda
    ROW_FORMAT: Compressed
     PAGE_SIZE: 16384  -- page_size是16k
 ZIP_PAGE_SIZE: 8192
    SPACE_TYPE: General  --General类型
 FS_BLOCK_SIZE: 4096
     FILE_SIZE: 65536
ALLOCATED_SIZE: 65536
1 row in set (0.00 sec)

2: 创建表

mysql> create table test_ger2 (a int) tablespace=ger_space;
ERROR 1478 (HY000): InnoDB: Tablespace `ger_space` uses block size 8192 and cannot contain a table with physical page size 16384  

--FILE_BLOCK_SIZE默认为innodb page size的大小（16kb），当你指定一个值时，例如8192，意味着你需要使用这个表空间来存储压缩表。否则就会报错：

mysql> create table test_ger2 (a int)  ROW_FORMAT=COMPRESSED tablespace=ger_space; 
Query OK, 0 rows affected (0.02 sec)

shell> ll test_ger* # 在/r2/mysqldata/employees目录下
-rw-r-----. 1 mysql mysql 8554 Jan 3 17:09 test_ger2.frm # 仅有一个frm文件

shell> /r2/testdir/
total 52
drwxr-x---. 2 mysql mysql 4096 Jan 3 16:41 burn_test
-rw-r-----. 1 mysql mysql 49152 Jan 3 17:09 ger_space.ibd # test_ger2的ibd文件其实存储在ger_space.ibd的通用表空间中

mysql> create table test_ger3 (a int) ROW_FORMAT=COMPRESSED  tablespace=ger_space; -- test_ger3 也存放在ger_space.ibd中
Query OK, 0 rows affected (0.09 sec)

• 通过使用 General Space ，一个表空间可以对应多张表

• 当对表进行alter等操作时，还是和原来一样，无需额外语法指定表空间位置

• 可以简单的理解为把多个表的ibd文件合并在一起了

  mysql> create tablespace ger3_space add datafile '/r2/testdir/ger3_space.ibd' file_block_size=4096; -- 创建4K大小的General Space也是可以的 Query OK, 0 rows affected (0.06 sec) -- 但是注意，如果设置了innodb_page_size,且大小不是file_block_size，那么在创建表的时候会报错

  mysql> create table test_ger (a int) tablespace=ger3_space; ERROR 1478 (HY000): InnoDB: Tablespace ger3_space uses block size 4096 and cannot contain a table with physical page size 8192

  • 既然无法创建表，那应该在创建general space时就应该报错啊？
  • 后续涉及压缩表时可以使用
  • 这里的 file_block_size 就是 page_size
  • 注意：需要考虑在使用General Space后，备份工具是否能够支持

3.4 Undo表空间文件

• innodb_undo_tablespaces = 3

• 创建3个undo表空间

  shell> ll undo*
  -rw-r -----. 1 mysql mysql 7340032 Jan 3 17:21 undo001 -rw-r -----. 1 mysql mysql 7340032 Jan 3 17:40 undo002 -rw-r -----. 1 mysql mysql 7340032 Jan 3 17:21 undo003

3.5 重做日志文件

• innodb_log_file_size

• 该文件设置的尽可能的大 ，模板中给出的大小是 4G

• 设置太小可能会导致脏页刷新 时hang住

  [root@localhost-m(252) /r2/mysqldata]# ll ib_logfile* -rw-r----- 1 mysql mysql 2147483648 1月 4 11:32 ib_logfile0 -rw-r----- 1 mysql mysql 2147483648 1月 4 11:32 ib_logfile1