罗马城之所以是这样的罗马城是因为市民就是这样的市民! This City is what it is because our citizens are what they are@柏拉图

InnoDB简介(一)

MySQL,InnoDB 2018-03-21 浏览量: 907 字数统计: 3234 最后更新: 2018-03-21 20:51

文章目录[显示]

InnoDB简介

  • innodb 引擎 基本上就是一个小型的 oracle
  • 基于B+树的聚集索引组织表,主键采用聚集索引高效存取
  • 实现基于索引的行锁
  • 完整支持ACID
  • 支持外键、全文索引
  • 具备四种事务隔离级别
  • 快速创建、删除二级索引
  • 支持多种文件格式,数据压缩
  • 支持crash recovery(故障恢复)
  • 高性能、高并发、高效的buffer pool 管理

InnoDB组成(三个)

  • caching
  • transaction system
  • sorange

内存级别

主要是 innodb buffer pool:
每个表都会 尽可能把 更多的数据放在内存的buffer 里面

  • page cache
    数据的缓存, 对普通索引的一个更新(如果有修改的数据 ,需要通过刷新线程,首先刷新到数据文件里,其次再刷新到doublewrite buffer 里面,还有一些可能要刷到 对应的change buffer里面
  • adaptive hash indexes(自适应哈希索引,对数据缓存的再一次的哈希的索引)
  • buffer pool 里面有两个列表:
    • 一个是 LRU的列表(热点数据 都放在 LRU里面 )
    • 一个是flush 的列表(脏 数据 列表,事务产生了脏数据 放在 flush 列表中 然后 刷新)

事务系统

有一个 log buffer,这里面的 log 指的是 redo log 是一个自己独立的 buffer
还包含 redo log ,至少两个,因为是循环使用的,log0 用完以后 就用log1,然后再用 log 2,等等 ,然后 一直的不断循环使用。

存储系统

  • 系统表空间
  • 独立表空间
  • undo tablespace ,mysql 5.6+才有
  • temporary tablespace(临时表空间) ,mysql 5.7+ 才有
  • general tablespace(通用表空间),mysql 5.7+ 才有

系统表空间

(ibdata1、ibdata2、ibdata3)

系统表空间是必须要有的,也就是说 ibdata1 文件是必须要存在的,这个文件要是不存在 那么 innodb 直接就挂了。

系统表空间是由 innodb_data_file_path 这个选项定义的,这里面可以定义系统表空间的绝对路径,也可以定义相对路径,可以设置自动化的初始化的大小。初始化 默认是12 M,我们建议是 默认 1G 或者是 500M 左右。也可以设置最大值,比如设置 innodb的系统表空间,最大只能 1G,超过 1G以后 ,那这里面的事务 可能就会受到影响,可能影响到事务并发,但是一般不会设置他的 最大值,只设置他的 初始 大小,连自动扩展策略都不用,默认是他的一次自动扩展的大小是 8M。

系统表空间里面存储的数据 主要有:

  • data dictionary(数据字典)
  • double write buffer(一块在内存里,一块在 磁盘里,这两块是并行存在的)
  • insert buffer /change buffer(以前叫 insert buffer,现在叫 change buffer,同样他也是基于内存 和磁盘两个都有的)
  • rollback segments(回滚段)
  • UNDO space
  • foreign key constraint system tables
  • user data ,if innodb_file_per_table=0

主要的是 回滚段和 是undo

这两个如果放在系统表空间里面,有可能在高并发的时候,有可能会把系统表空间,也就是 ibdata1 文件全部撑爆,比如默认初始化可能是 1G,但是跑一段时间以后可能就变成 2G,再过一段时间可能就变成 3G,所以,独立出来就没事了。(5.6 开始允许 把undo 表空间独立出来)

ibdata 1 文件里面怎么存的?

FSP_HRD:文件头
IBUF_BITMAP:change buffer 里面的
INODE:INODE的一些信息
INDEX:change buffer 的根节点


独立表空间

innodb_file_per_table =1 这个选项启用以后,可以使用独立表空间,这个选项 从5.5 开始就已经是 默认启用。

每个表都会有自己的 独立的XX.ibd 文件,但是 rollback segments,doublewrite buffer 等仍存储在系统表空间文件里面。

数据表空间里面主要存储的就是 聚集索引 和其他的 普通索引数据 ,所以说 innodb他的数据表空间里面其实存储的都是索引,要么是聚集索引,要么是普通索引


general tablespace(通用表空间 )

5.7 开始支持的 功能,类似于 oracle 里面的 表空间的做法,可以把多个表放在一个公共的通用表空间里面,然后我们还可以定义多个 通用表空间,分别 放在不同的磁盘上。

通用表空间的好处:

主要是 为了可以减少 metadata (元数据/中继数据) ,这方面的存储的开销,以及 如果我们删除一个大表,但是我们使用 的是通用表空间的话,那么我们删起来更快一些 ,因为他只需要删除metadata 里面的数据 ,所以更快一些。

通用表空间 VS 系统表空间

两者是比较类似的,已分配占用的表空间没有办法回收还给操作系统的,只有需要重建 才可以。


temporary tablespace(临时表空间)

5.7 新增的,以前 临时表空间是和myisam 一起存储的,或者是memory,表空间文件叫 ibtmp1,默认是 12M。

  • 实例关闭以后,这个文件会删掉
  • 实例启动后,这个文件也会被新创建,所以 临时表空间里面的文件,相当于实例一旦重新创建,就会全部消失,另外,临时表空间是不需要参与 crash recovery ,因此也不记录 redo log ,只记录 undo (需要回滚,但不需要 前滚,所以不需要记录 redo)
  • 不支持 压缩
  • 由 innodb_temp_data_file_path定义
  • 通用临时表 create temporary table 和 SQL执行过程中产生的内部临时表 (using temporary table) 会共用 这个临时表空间

undo tablespace

5.6 开始支持,存储事务中的旧数据

  • innodb_undo_logs 设置 undo 表空间个数(也就是我们使用多少个 undo 表空间)
  • 系统表空间总是 需要1 个undo 表空间
  • 临时表空间总是需要 32 个undo 表空间, 因此,undo 表空间总是必须大于 33个,并且循环轮流使用
  • 5.7 可以被在线 truncate (删记录) ,也就是 undo 表空间用完以后,如果没有及时释放,那我们可以在线的手动的 truncate undo 表空间,避免暴涨。
  • 当undo 超过 innodb_max_undo_log_size (undo 表空间最大的值)时, 会触发 truncate 工作,会把 所有的不需要的 undo 表空间都 truncate 掉
  • purge (清除工作)执行 innodb_purge_rseg_truncate_frequency 次后,也会触发truncate 工作,默认是 128次(也就是当执行 128次 破解操作以后 ,也会触发undo 的 truncate 操作,建议使用独立的 undo 表空间,这样可以避免 ibdata1 文件暴涨)

建议使用独立 undo 表空间

独立表空间 VS 共享表空间

独立表空间

  • 表空间更方便回收(比如一个表 使用独立表空间的,如果 把这个表删掉的话,他的表空间文件也会跟着删掉,立刻就能释放 这个表的存储空间)
  • 透明表空间文件迁移(可以从一个实例迁移到 另一个实例)
  • 如果我们使用地理表空间的话 会 需要更多的 file handler(文件描述符),尤其是 当table 数量很多,或者有很多表分区的时候,可能会是个 麻烦。

共享表空间

  • 也就是把所有的文件 都放在 ibdata 1 文件里面,或者是 ibdata1 或者是 ibdata2 文件 这样的存储文件里面

共享表空间的优势:

  • 当 删除大表或删除大量数据时候,因为他不会涉及到一些文件系统层面的 操作,他只需要一些更新innodb 里面的 matadata ,也就是 drop table / truncate table 这些操作很快
  • 可以使用 裸设备
    据说 裸设备性能 会更好一些,但是实际上 innodb 基本上 没有人会使用裸设备,会有些不方便,因为 不支持表空间的迁移但是据说性能可能 有提升。

四种行格式

  • redundant
  • compact
  • dynamic
  • compressed

>select * from information_schema.innodb_sys_tables;
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME                            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+---------------------------------+------+--------+-------+-------------+------------+---------------+------------+
|       14 | SYS_DATAFILES                   |    0 |      5 |     0 | Antelope    | Redundant  |             0 | System     |
|       11 | SYS_FOREIGN                     |    0 |      7 |     0 | Antelope    | Redundant  |             0 | System     |
|       12 | SYS_FOREIGN_COLS                |    0 |      7 |     0 | Antelope    | Redundant  |             0 | System     |
|       13 | SYS_TABLESPACES                 |    0 |      6 |     0 | Antelope    | Redundant  |             0 | System     |
|       15 | SYS_VIRTUAL                     |    0 |      6 |     0 | Antelope    | Redundant  |             0 | System     |
|       40 | master_test/t1                  |   33 |      5 |    26 | Barracuda   | Dynamic    |             0 | Single     |
|       34 | mysql/engine_cost               |   33 |      9 |    20 | Barracuda   | Dynamic    |             0 | Single     |
|       32 | mysql/gtid_executed             |   33 |      6 |    18 | Barracuda   | Dynamic    |             0 | Single     |

里面的 file_format 有 antelope (旧的 file_format ,支持redundant和 compact )也有barracuda (支持 dynamic 和 compressed ,都是新增的 两种形式,同时也会兼容 redundant和 compact )

虽然支持四种行格式,但是主要用 compact 和 dynamic 这两种行格式

5.6 默认的行格式为 compact
5.7 开始默认的行格式 是 dynamic

compact 是比较紧凑的行格式,相对来说更节省空间,dynamic 跟 compact 差不多,最大的区别就是最大对象的列存储方式不一样,如果是 compact ,如果遇到大对象的列,他需要做溢出存储的时候,会把前 768个字节 放在当前的page里,但是如果是dynamic,会之存储 20个字节的指针,把整个列的数据放在独立的一个page里面,

查看一下行格式的状态
select * from infomation_schema.innodb_sys_tables where name like '%table_name%';

dynamic VS compact

  • compact 格式,会存储 前768个字节,放在当前的page里面,多出来的放在 额外的overflow page里面
  • dynamic 格式,会存储 20个字节 的指针,然后剩下的整个列全部放在 overflow page (独立的一个page)里面。

例如 有一个列 65KB,这个时候一个16KB的 page是存不下的,那么就需要 5 个page来存储,如果很巧的是一个列的长度 正好是16KB+1个字节(1B),也就是说一个page是存不下的,但是实际上,这个列是 8192 =8KB ,如果这个page,他的行 长度超过 8KB的话,正好是 8193B,这个时候 两个page存的,另一行是16385B=16KB,这个时候也需要两个page来存储

一个存16KB,另一个存1B,这种就很浪费,所以说我们再做表设计的时候,尽可能让他和innodb的page size 是对应的,如果涉及到这种大对象的话,最好是定长的,innodb的page是按照实际存储的字节的长度来区分的,不是按照我们设计的长度区分的

当一个行记录,他的长度大约超过一个page 的一半,默认一半是 8KB (5.6 后可以调整大小),就会依次将 最长的列拆分到多个page 存储,直到不再超过page的 一半为止(例如说,现在的表里面,有很多个 大对象列,最长的是A列:32KB,B列:25KB,然后是C列: 15KB,)
A列首先被分离出去,溢出存储的列(至少需要2个page存储),然后 B同理,也要分离出去(也至少需要2个page),C需要1个page, (由此可见,32KB也是占用2个page,20KB也是两个page,所以说B这列就存在浪费)
其中 A的两个列是单独存在不能共享的,同理B也是
溢出的存在的page 是不能被共享的,都是独享的,是单独的对象,会压缩的大对象列
大对象列 指的是 text这种,出现 filesort 排序 或者是 temporary table(产生临时表,像两个表做join)的话,这种列是无法放在内存里面处理的,MySQL会认为这个对象太大了,会浪费内存,直接不在内存进行处理,会直接要求他们转成 disk tmp table (基于磁盘的临时表),所以说I/O 代价会很高,效率很低,所以尽量少使用,尤其是要做 排序和表连接的时候 。

select 会同时读取这些溢出的列,所以会有很高的IO 代价,所以说建议尽量只读 必要的列,而不是每次都读 多有的列

innodb每个行至少都要存储以下几个基本的信息

  • db_row_id,6 bytes,指向对应的行记录,每次写新数据该ID都会自增,如果已有显示声明的主键,则不需要存储db_row_id
  • db_trx_id(当前行的最新的事务ID),6bytes,每个事务的唯一标识符。
  • db_roll_ptr,7bytes,指向 undo log 的回滚指针(当前这行指向上一个版本的回滚段的一个指针)
  • 用于实现 MVCC(traction id 和 rollback point 结合起来,主要是为了实现多版本)

表空间整理

消除碎片

  • alter table table_name engine=innodb;(相当于对整个表都重新整理了一遍,可以消除碎片的)
  • 或者 optimize table table_name
  • 尽量使用pt-osc 操作

回收表空间

  • 独立表空间:alter table table_name engine= innodb
  • 共享包空间:重新导出,导入
  • 尽量用pt-osc 来操作

表空间文件迁移

  • 目标服务器上:alter table table_name discard tablespace
  • 源服务器上:flush table table_name for export,备份过去
  • 目标服务器上:alter table table_name import tablespace
  • 表结构务必一致
  • innodb_page_size 也要一致

InnoDB 索引

innodb 是基于 B+树的 聚集索引组织表

数据以 聚集索引 (clustered index )逻辑顺序 存储,聚集索引选择的顺序

  • 聚集索引优先选择显式 定义的主键
  • 其次选择第一个非 NULL 的唯一索引
  • 再次使用隐藏的 rowId

注意

  • 聚集索引叶子节点 存储整行数据
  • 而普通索引存的是主键索引键值
  • 所有的索引都由 2 个 segment 组成
    • leaf page segment (叶子结点)
    • non-leaf page segment (非叶子结点)

InnoDB 主键索引

InnoDB 主键索引建议

  • 最好是自增属性,int/bigint 最佳(对于innodb 而言,数据新写入的时候基本上等同于像堆表写入的效率一样比较高
  • 没有业务用途的的,通常也不会去更新主键的,所以说只要不涉及到 列从很小变成很长这种更新的话,基本上性能不会有太大的影响
  • 如果是数据表 每次都是变长的更新的话,变成更长的那种更新的话,那么他的PTS和 每次变短的更新相比,两种的性能差是很大的。
  • 数据类型长度小,性能更佳
  • 数据顺序写入也是顺序的,不会离散,最佳
  • 这样也有利于更多普通索引放在buffer page中(因为普通索引他后面也要跟着主键 键值的那个列,所以说 如果主键的列比较短的话,那么放在buffer pool里面 索引的数量也会更多一些)
  • 主键尽量不要更新,否则更新主键时,辅助索引也要跟着更新
小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2018/03/mysql-innodb-1-1.html

5 条评论

  1. 小土豆子

    呀,没想到某只蜗牛很勤快。那么,该如何给Typecho的存储引擎改成innodb呢?

    1. 小蜗牛

      5.5以后默认就是innodb了哦~

      1. 小土豆子

        但是你的小站有两个表是MyISAM的唉?

        1. 小蜗牛

          咦?其实myisam也是有好处的,比如说迁移(笑哭)

          1. 小土豆子

            ε=ε=ε=┏(゜ロ゜;)┛是的,我尝试转换到InnoDB但是失败了|´ ・ω・) ノ

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到