MySQL InnoDB Architecture 简要介绍

一、内存存储结构

1、Buffer Pool

为了提升大数据量读操作效率，buffer pool 被设计划分为能够存储多条记录的数据页。同时，基于链表结构存储实现，LRU算法支持，能够极大的提高缓存管理的效率。

Buffer Pool LRU 算法

中间插入策略会把列表当成两个子列表：

尾部用于存储旧的最少访问的数据页

如下图：Buffer Pool List

通常 LRU 算法按如下方式运行:

列表的中间包括新列表的尾部和旧列表的头部。
旧的子列表数据访问会改变其数据特性，并将其移动到新的子列表头部（预读操作除外）。
随着数据库操作的执行，buffer pool 中未被访问的页数据会逐渐移动到列表的尾部，并淘汰。

mysqldump 操作导致的表扫描或者没有附加 where 条件的 select 查询会导致大量的数据写入 buffer pool，并淘汰旧的记录。但是这些新的记录可能永远不会被使用。

2、Change Buffer

如下入示意 Change Buffer：

purge operation 会周期性的把更新的页数据批量写入磁盘，这样比即时单条写入更有效率。

在内存中，change buffer 会占用一部分的 buffer pool 存储使用。在磁盘里，change buffer 是 system tablespace 的一部分，用以存储数据库服务器关机时产生的索引变化数据。

3、 Adaptive Hash Index

innodb_adaptive_hash_index 配置启用。

4、Log Buffer

存储内存日志数据，用于磁盘日志文件数据写入。配置：innodb_log_buffer_size。默认大小 16MB。log buffer 的数据会周期性的刷盘。较大的 log buffer 有利于较大的事务日志数据写入需求。对于执行大批量更新、写入或删除操作的事务可以适当调高 log buffer 以减少磁盘IO。

二、磁盘存储结构

1、Index 索引

a）聚簇索引及二级索引

如果表未定义主键，则 InnoDB 会使用首个唯一索引（所有列非空）作为聚簇索引。
如果表既没有主键也没有合适的唯一索引，则 InnoDB 会为表创建一个隐藏的聚簇索引 GEN_CLUST_INDEX，该索引基于 InnoDB 为表自动添加的包含行ID值的列，所有表数据会基于该ID值排序。行ID值是一个6字节数值，会随着数据的插入单调递增，因此基于此列排序的表在物理上保持着数据插入顺序。

除了聚簇索引，其它的索引都是二级锁索引，二级索引除了设置的索引列外，还包含主键，最终 InnoDB 都要通过主键来查找聚簇索引里的数据。

B 树索引使用：

like 操作支持：like 后面的参数需要为常量并且不能以通配符起始。

//可以命中索引 SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'; //无法使用索引 SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE other_col;

对于多列复合索引，如果要使用它们，则在每一个 and 条件分组里都必须使用它们：

如下使用到了索引:
... WHERE index_part1=1 AND index_part2=2 AND other_column=3

/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

/* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

/* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

如下未使用到索引

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

/* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10

/* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10

一些特殊情况，如优化器测算使用索引会需要访问表中大量的数据，那么即使条件列命中了索引使用条件也不会使用索引。

b）InnoDB 索引物理结构

除了空间索引（基于 R-trees，用以组织存储多维数据），InnoDB 索引都是基于 B-tree 结构。数据存储于树的叶子结点。

当向聚簇索引插入新的记录时，InnoDB 会保留1/16页空间用以应对将来可能的插入和更新。如果是顺序插入，则索引页空间会保持差不多15/16大小。如果是随机的，则页空间大小会在1/2 到 15/16之间。一般低于1/2（MERGE_THRESHOLD 配置）会触发索引树压缩。

c）Sorted Index Builds

索引重建通常分为三步：

随着多个第一步这个过程写入数据到临时中介文件，文件里的索引记录会执行合并。
排序的索引记录写入 B-tree。

Sorted index builds 基于自底而上的方式来构建索引。从 B-tree 每层最右侧的叶子节点开始，基于索引记录顺序写入。当一个节点页写满，则向其父节点添加一个新的子节点用于新的写入。

2、table space 表空间

system table space	可以有一个或多个数据存储文件，默认为一个 ibdata1，大小和数量可以通过 `innodb_data_file_path` 进行配置。
File-Per-Table Tablespaces	`InnoDB` 默认在 file-per-table tablespaces 创建表，可以通过 `innodb_file_per_table` 配置变更，禁用则会导致在 system tablespace 创建。优势：操作表复制时的空间额外使用在共享表空间不会返还操作系统。 TRUNCATE TABLE 操作性能在 file-per-table tablespaces 表现更优。可以从其它 MySQL 实例中倒入表数据。 DYNAMIC 及 `COMPRESSED` 行格式特性。基于 MySQL Enterprise Backup 备份和恢复单标数据更加快捷，并且不影响其它表使用。通常 Linux 系统不允许并发写入同一个文件，当 `innodb_flush_method` 设置为 `O_DIRECT` 时，可能会提升数据库性能表现。劣势：每张表都会存在未使用的空间，只能用于本表记录使用，管理不当会造成空间的浪费。系统 fsync 操作只针对单个文件，多表写操作会导致系统 fsync 操作增加 mysqld 需要对每个 `file-per-table tablespace` 保持一个打开的文件句柄，如果表数量比较多多的话，可能影响数据库性能。可能的文件及内存碎片问题会影响 `DROP TABLE` 及 table scan 操作。如果可以很好的管理碎片问题那么这些操作的性能反而会更好自动扩展，空间增长不受 `innodb_autoextend_increment` 配置控制，期初增长因子很小，一段时间会以 4MB 大小增量扩展。
General Tablespaces	共享的 InnoDB 表空间。
Undo Tablespaces	undo logs 存储。
The Temporary Tablespace	非压缩的，用户创建的临时表及磁盘上的内部临时表存储。

3、double buffer

4、Redo Log

redo log 是一种基于磁盘的数据结构，用于修正数据库崩溃恢复期间未完成事务造成的数据脏写。

ib_logfile0 和 ib_logfile1，MySQL 以环形方式写入。

innodb_log_file_size；3、数量：innodb_log_files_in_group。

5、Undo logs

6、InnoDB Data Dictionary

包括一系列系统表，存储包括表、索引及表列等相关元数据，物理存储在系统表空间。由于一些历史原因，data dictionary metadata 部分存储在 InnoDB 表空间文件 (.frm files。

编程笔记 » MySQL InnoDB Architecture 简要介绍