HelloWorld
登录

018：InnoDB 存储引擎、表空间

Wesley13

2021-10-11

1332 0 0

[TOC]

一.InnoDB 存储引擎

1. InnoDB的历史

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

2. InnoDB的特点

1• Fully ACID （InnoDB默认的Repeat Read隔离级别就支持）
2• Row-level Locking（支持行锁）
3• Multi-version concurrency control（MVCC）（支持多版本并发控制）
4• Foreign key support（支持外键）
5• Automatic deadlock detection（死锁自动检测）
6• 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: 创建一个通用表空间

1mysql> create tablespace ger_space add datafile '/r2/testdir/ger_space.ibd' file_block_size=8192;
2Query OK, 0 rows affected (0.07 sec)
3
4-- datafile 指定存储路径后，在datadir下会产生一个isl文件，该文件的内容为General space的ibd文件的路径
5-- 如果datafile不指定路径，则ibd文件默认存储在datadir目录下，且不需要isl文件了
6
7mysql> create tablespace ger2_space add datafile 'ger2_space.ibd' file_block_size=8192;
8Query OK, 0 rows affected (0.06 sec)
9
10shell> ll ger*
11-rw-r-----. 1 mysql mysql 32768 Jan 3 16:51 ger2_space.ibd # 未指定路径，存放于datadir目录
12-rw-r-----. 1 mysql mysql 26 Jan 3 16:50 ger_space.isl # 指定了其他路径，存在isl链接文件
13
14shell> cat ger_space.isl 
15/r2/testdir/ger_space.ibd # ibd文件真实存在的路径
16mysql>  select * from information_schema.innodb_sys_tablespaces where name='ger_space'\G;
17*************************** 1. row ***************************
18         SPACE: 55
19          NAME: ger_space
20          FLAG: 2089
21   FILE_FORMAT: Barracuda
22    ROW_FORMAT: Compressed
23     PAGE_SIZE: 16384  -- page_size是16k
24 ZIP_PAGE_SIZE: 8192
25    SPACE_TYPE: General  --General类型
26 FS_BLOCK_SIZE: 4096
27     FILE_SIZE: 65536
28ALLOCATED_SIZE: 65536
291 row in set (0.00 sec)

2: 创建表

1mysql> create table test_ger2 (a int) tablespace=ger_space;
2ERROR 1478 (HY000): InnoDB: Tablespace `ger_space` uses block size 8192 and cannot contain a table with physical page size 16384  
3
4--FILE_BLOCK_SIZE默认为innodb page size的大小（16kb），当你指定一个值时，例如8192，意味着你需要使用这个表空间来存储压缩表。否则就会报错：
5
6mysql> create table test_ger2 (a int)  ROW_FORMAT=COMPRESSED tablespace=ger_space; 
7Query OK, 0 rows affected (0.02 sec)
8
9shell> ll test_ger* # 在/r2/mysqldata/employees目录下
10-rw-r-----. 1 mysql mysql 8554 Jan 3 17:09 test_ger2.frm # 仅有一个frm文件
11
12shell> /r2/testdir/
13total 52
14drwxr-x---. 2 mysql mysql 4096 Jan 3 16:41 burn_test
15-rw-r-----. 1 mysql mysql 49152 Jan 3 17:09 ger_space.ibd # test_ger2的ibd文件其实存储在ger_space.ibd的通用表空间中
16
17mysql> create table test_ger3 (a int) ROW_FORMAT=COMPRESSED  tablespace=ger_space; -- test_ger3 也存放在ger_space.ibd中
18Query 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

点赞
收藏

评论区

加载中...

相关推荐

MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1

文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s

Oracle 分组与拼接字符串同时使用

SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(

MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_

皕杰报表之UUID

​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为

手写Java HashMap源码

HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22

2020年前端实用代码段，为你的工作保驾护航

有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )