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里面 索引的数量也会更多一些)
- 主键尽量不要更新,否则更新主键时,辅助索引也要跟着更新
呀,没想到某只蜗牛很勤快。那么,该如何给Typecho的存储引擎改成innodb呢?
5.5以后默认就是innodb了哦~
但是你的小站有两个表是MyISAM的唉?
咦?其实myisam也是有好处的,比如说迁移(笑哭)
ε=ε=ε=┏(゜ロ゜;)┛是的,我尝试转换到InnoDB但是失败了|´ ・ω・) ノ