205_mysql_innodb_5_Innodb_Buffer Pool1

进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

Mysql服务器启动的时候就向操作系统申请了一片连续的内存,他们给这片内存起了个名,叫做Buffer Pool（中文名是缓冲池）

运维

1 查询

select @@innodb_buffer_pool_size; # 建议物理内存的 50-75% select @@innodb_buffer_pool_instances; #建议4-8个 innodb_buffer_pool_size的值必须是2G或者2G的整数倍

2 不够用场景/如何判断不够用

产生不够用的情景有哪些？ 1. 设置太小 2. 大事务（更新多行） 3. chpt触发不及时  4. IO比较慢  5. 查询语句优化的不好

如何判断是否够用  Innodb_buffer_pool_wait_free 如果出现非0的话，可以考虑增加buffer_pool

show global status like  "%innodb%wait%"

一 data buffer

二 Change buffer

Change buffer  Change Buffer默认占Buffer Pool的25%，最大设置占用50%：

特点

1 对于聚簇索引会立即更新

2  对二级索引且insert，update，delete语句有效

3 对于不在buffer pool的辅助索引需要更新，不是实时更新的(如果在内存则直接更新)

4 Change buffer 功能是临时缓冲辅助索引需要的数据更新，当需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时 辅助索引就是最新的

show variables like "%innodb_change_buffer_max_size%"   #比例

写缓存（Change Buffer） 是一种特殊的数据结构，用于在对数据变更时，如果数据所在的数据页没有在 buffer pool 中的话，在不影响数据一致性的前提下，InnoDB 引擎会将对数据的操作缓存在 Change Buffer 中，这样就省去了从磁盘中读入这个数据页 change buffer为什么针对非唯一普通索引页 聚簇索引比如insert，update，delete 数据, 对于聚簇索引会立即更新 （更新操作都要先判断这个操作是否违反唯一性约束） 这必须要将数据页读入内存才能判断如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了 唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用 普通索引 不需要判断唯一性，正常使用 change buffer 更新, 可能涉及相关页的分裂和维护，为了加快速度使用了 Change Buffer

以下几种情况开启 Change Buffer，会使得 MySQL 数据库明显提升：

1、数据库大部分是非唯一索引

2、业务是写多读少

3、写入数据之后并不会立即读取它

Change Buffer 的架构图

Change Buffer 中的数据最终还是会刷回到数据所在的原始数据页中将 change buffer 中的操作合并到原数据页（System Tablespace 中可以看到持久化 Change Buffer 的空间），得到最新结果的过程称为 merge

以下情况会发生 merge 操作：

1 访问这个数据页；

2 后台master线程会定期 merge；

3 数据库缓冲池不够用时；

4 数据库正常关闭时；

5 redo log写满时

监视变更缓冲区

Show engine innodb status

size： seg size：变更缓冲区的大小（以页为单位） #这行显示了关于Ibuf: size 1, free list len 0, seg size 2, 0 merges Size 变更缓冲区使用的页数。变更缓冲区大小等于 seg size - (1 + free list len) (1 + free list len) 表示变更缓冲区头页面 free list The number of pages free within the change buffer.代表了空闲页的数量 seg size The size of the change buffer, in pages. 插入缓冲的大小为2 *16KB merges: The total number of change buffer merges.表示合并次数 merged operations - insert: The number of inserted records merged.merged插入的记录数 merged operations - delete mark: The number of deleted records merged.merged删除记录数 merged operations - delete: The number of purge records merged.merged清除记录数 discarded operations - insert: The number of insert merge operations discarded. discarded operations - delete mark: The number of delete merge operations discarded. discarded operations - delete: The number of purge merge operations discarded.

INFORMATION_SCHEMA.INNODB_METRICS 表提供了在InnoDB Standard Monitor 输出中指标信息。要查看 变更缓冲区 度量指标，可以使用以下查询语句

select name, COMMENT from  INFORMATION_SCHEMA.INNODB_METRICS where name like "%ibuf%"

INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 表提供有关缓冲池中每个页面的元数据，包括变更缓冲区索引和变更缓冲区位图页面。

变更缓冲区页面由PAGE_TYPE标识。 IBUF_INDEX 是变更缓冲区 索引页面的页面类型，而IBUF_BITMAP是变更缓冲区 位图页面的页面类型。

查询 INNODB_BUFFER_PAGE 表，以确定 IBUF_INDEX 和 IBUF_BITMAP 页的大约数量（占缓冲池页总数的百分比）

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages, (SELECT ((change_buffer_pages/total_pages)*100)) AS change_buffer_page_percentage

三 AHI 自适应hash索引

AHI作用： 自动评估"热"的内存索引page,生成HASH索引表，帮助InnoDB快速读取索引页。加快索引读取的效果。 相当与索引的索引。

MySQL的InnoDB引擎，能够创建只有Btree

MySQL 5.7 – innodb_adaptive_hash_index_parts默认设置为8，所以自适应哈希索引被切割为8个分区，因为不存在全局互斥

show variables like '%hash_index%';  # 如果内存较大 推荐开启，如果内存小，效果一般

四 Log Buffer（redo buffer）

作用： 用来缓冲 redo log日志信息

select @@innodb_log_buffer_size; 默认大小：16M  生产：和innodb_log_file_size有关, 1-2倍, 重启生效; 影响事物的并发度

show variables like "%innodb_log_buffer_size"

show variables like "%innodb_log_%"

设置

innodb_log_file_size=2G

innodb_log_files_in_group=3

innodb_log_buffer_size=1G

show status like "%innodb_log_%"

Innodb_log_write_requests平均每秒日志写请求次数

innodb_log_writes平均每秒向日志文件的物理写次数

Innodb_log_waits等待日志缓冲区空闲的次数 （大于0 要进行调整innodb_log_buffer_size和innodb_log_file_size ）

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。