一、内存存储结构
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 会使用首个唯一索引(所有列非空)作为聚簇索引。
- 如果表既没有主键也没有合适的唯一索引,则 InnoDB 会为表创建一个隐藏的聚簇索引 GEN_CLUST_INDEX,该索引基于 InnoDB 为表自动添加的包含行ID值的列,所有表数据会基于该ID值排序。行ID值是一个6字节数值,会随着数据的插入单调递增,因此基于此列排序的表在物理上保持着数据插入顺序。
除了聚簇索引,其它的索引都是二级锁索引,二级索引除了设置的索引列外,还包含主键,最终 InnoDB 都要通过主键来查找聚簇索引里的数据。
B 树索引使用:
- 支持列 =、>、>=、<、<= 及 BETWEEN 操作。
- 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;
- 对于 is NULL 条件,如果条件列有索引,则查询会使用到索引。
- 对于多列复合索引,如果要使用它们,则在每一个 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
索引重建通常分为三步:
- 扫描聚簇索引,生成索引记录并添加到 sort buffer。sort buffer 满了之后,记录会被排序并写入一个临时的中介文件
- 随着多个第一步这个过程写入数据到临时中介文件,文件里的索引记录会执行合并。
- 排序的索引记录写入 B-tree。
Sorted index builds 基于自底而上的方式来构建索引。从 B-tree 每层最右侧的叶子节点开始,基于索引记录顺序写入。当一个节点页写满,则向其父节点添加一个新的子节点用于新的写入。
2、table space 表空间
system table space |
可以有一个或多个数据存储文件,默认为一个 ibdata1,大小和数量可以通过 |
File-Per-Table Tablespaces |
|
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。