概述
很多朋友可能会有许多关于 Innodb 如何使用内存的问题. 我这里将简单介绍一下 innodb 内存结构, 然后以 innodb 启动时的分配情况做一个解释.
1. INNODB 内存结构
(1) 聚集索引与非聚集索引:
聚集索引: 主键, 有序, 存储顺序与内存一致
非聚集索引: 非主键, 无序
聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时, 拿到叶子上的主键再去查到想要查找的数据.(拿到主键再查找这个过程叫做回表)
(2) 缓冲池:
缓冲池用于存放各种数据的缓存. Innodb 总是将磁盘中的数据 (数据库文件) 按页 (16K) 读取到缓冲池, 然后按最近最少使用算法 (LRU) 来保存缓冲池中的数据.
如果数据库文件需要修改, 总是先修改在缓存池中的页(发生修改后, 该页为脏页), 然后按一定频率刷新到磁盘中.
(3) insert buffer(插入缓冲):
使用条件: 1. 索引是辅助索引; 2. 索引不是唯一的.
也就是说, 主键索引不使用插入缓冲. 主键索引是聚集索引, 插入是顺序的, 执行效率比较高, 就不借助缓冲了.
但是, 当表中存在辅助索引 (非聚集索引, 非主键) 时, 不一定是顺序的了, 这时需要离散的访问, 插入性能会降低.
所以, 对于非聚集索引的插入和更新操作, 不是直接插入到索引页中, 而是插入到缓冲中, 再以一定频率执行插入缓冲和非聚集索引叶子节点的合并操作.
注: 由于非主键索引叶子节点存的是主键和当前列值, 所以使用非聚集索引查询时, 先查辅助索引的那颗树找到对应的主键, 再查主键索引的那颗树, 会查两次, 效率不高.
(4) redo log(重做日志):
在事务提交的时候, Innodb 会先把数据从磁盘中读到内存进行修改, 然后把事务日志写到日志缓冲 (log buffer), 然后再刷新到重做日志文件(redo log file) 中进行持久化, 然后再定期刷新到磁盘中.
用于在实例故障恢复时, 继续那些已经 commit 但数据尚未完全回写到磁盘的事务.
(5) double write(两次写):
为防止 redo log 在写的过程中损坏, 我们需要留个备份. 若出现故障, 先从备份中恢复 redo log, 再进行数据恢复.
(6) undo log:
记录数据修改前的镜像, 用于将未提交的事务回滚到事务开始前的状态.
undo 操作: 当 Innodb 存储引擎回滚时, 它实际上做的是与之前相关的工作, 对于 insert 操作, Innodb 会完成一个 delete, 对于 update, 则会执行一个相反的 update, 将修改前的行放回去.
(7) 自适应哈希索引:
Innodb 会监控表上索引的查找频率, 若发现建立哈希索引会提升速度, 则自动创建哈希索引. 不是对整张表建立索引, 而是根据访问频率对某些页建立.
(8) 事务提交:
事务进行过程中, 每次 sql 语句执行, 都会记录 undo log 和 redo log, 然后更新数据形成脏页. 然后 redo log 按照时间或空间等条件进行落盘, undo log 和脏页按照 checkpoint 进行落盘, 落盘后相应的 redo log 就可以删除了.
此时, 事务还未 commit, 如果发生崩溃, 则首先检查 checkpoint 记录, 使用相应的 redo log 进行数据和 undo log 的恢复, 然后查看 undo log 的状态发现事务尚未提交, 然后就使用 undo log 进行回滚. 事务执行 commit 操作时, 会将本事务相关的所有 redo log 都进行落盘, 只有所有 redo log 落盘成功, 才算 commit 成功.
然后内存中的数据脏页继续按照 checkpoint 进行落盘. 如果此时发生了崩溃, 则只使用 redo log 恢复数据.
2. 一些重要的概念:
NBLOCKS=Innodb_buffer_pool 有多个页(block)=innodb_buffer_pool_size/16384(16k)
- OS_THREADS= if ( innodb_buffer_pool_size>= 1000Mb) = 50000
- else if (innodb_buffer_pool_size>= 8Mb) = 10000
- else = 1000 (该值只用在 * nixes 系统上, 对于 Windows 有一点小的区别计算 OS_THREADS)
3. Innodb 使用的内存包括:
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- innodb_log_buffer_size
- adaptive index hash ,size (innodb buffer 索引管理区)= innodb_buffer_pool_size/64
- system dictionary hash,size(innodb 内部字典区) = 6 * innodb_buffer_pool_size/512
- memory for sync_array,size(用于 Innodb 内部 syncronzation 的开销)=OS_THREAD * 512
- memory for os_event,size(用于 innodb 内存的 syncronzation 的开销)=OS_THREAD * 216
- memory for locking system(内存的锁管理系统),size = 5 * 4 *NBBLOCKS
4. innodb 内存使用的计算公式为:
Innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812/16384 * innodb_buffer_pool_size + OS_THREADS * 368
对于 812/16384 * Innodb_buffer_pool_size 可以简单的用 innodb_buffer_pool_size / 20 计算, 对于 OS_THREADS * 368
- OS_THREADS * 368 = 17.5 MB if innodb_buffer_pool_size > 1000MB
- OS_THREADS * 3368 = 3.5 MB if innodb_buffer_pool_size > 8MB
举一个例子:
如果你的 innodb_buffer_pool_size 有 1500MB,innodb_additional_mem_pool_size =20 MB,innodb_log_buffer_size = 8M,
Innodb 将会向系统申请内存为 = 1500M + 20M + 8M + 1500/20 M +17.5 = 1620.5M
根据以上的条件可以算出 Innodb 最根本最需要多少内存, 这样对于服务器的内存使用也可以有一个规划了.
来源: http://database.51cto.com/art/201911/605343.htm