mysql在线调整bufferpool mysql buffer pool 什么时候调整

一、MySQL简介

1.1 配置信息查询

SHOw GLOBAL STATUS like 'Innodb_page_size '; --1页

select 16384/1024; -- 16Kb---

show global variables like "%datadir%";

show global variables like "%log%";

show global variables like '%innodb_file_per_table%';

show global variables like '%innodb_old_blocks_pct%'; -- LRU链表冷热区域配置默认为37

show global variables like '%innodb_old_blocks_time%'; -- LRU链表冷区域的数据隔多久可以放入到热区域

show global variables like '%innodb_log_file_size%'; -- 单个logfile的大小默认为48M

show global variables like '%innodb_log_files_in_group%'; --配置有几个logfile

show global variables like '%innodb_buffer_pool_size%'; --配置buffer pool大小默认为128M

show global variables like '%innodb_change_buffer_max_size%'; --配置change pool大小默认为25个字节

show global variables like '%innodb_log_buffer_size%'; -- redo log buffer的大小y默认为16M

1.2 MySQL页结构

1.3 MySQL存储结构

1.4 主键索引B+树

主键索引树，是聚簇索引，叶子节点存放完整的数据。

1.5 二级索引B+树

MySQL非主键索引组织的B+树，为二级索引树，它的叶子节点为什么没有存储完整的数据记录呢？

一方面，假如索引很多，每一个索引都维护一个完整的索引树，占用空间很多。

另一方面，对于更新，每个索引树都需要去更新记录，耗费性能。

所以，二级索引树叶子节点，存放索引值和主键值。

1.6 SQL语句执行流程

二、Buffer Pool

三、Redo log

logfile的默认大小是48M，并且就这两个文件，当这两个文件都满了以后，还有redo log要持久化要放在哪里呢？

logfile0和logfile1是循环的，当这两个logfile都满了以后，redo log还要持久化，那么还是把redo log放在logfile0，那这样岂不是把之前的redo log给冲掉了吗？

这里面有一个checkpoint，检查点的概念，当两个logfile文件都满了以后，就会触发一次检查点，一旦触发了这个检查点，就会看buffer pool里面的那些脏页，和redo log里面是否有对应的数据，如果找到的话，就会把这些数据持久化到磁盘里面来，当把和logfile里面所有对应的页，全部持久化到磁盘里面之后，我们才会把新的redo log日志加载logfile文件里面来。

一旦触发了logfile的检查点，那么性能就会变慢了，所以，这里面有一个优化，就是可以把logfile文件调大。但是，有一个问题，当文件调大以后，当myql的重启的时候，会变得很慢，因为需要根据redo log日志恢复数据，文件变大了，速度自然变慢了。

什么时候才会对redo log进行持久化？

update 语句id=9

事务

begin;

update 语句id=8;  --当执行一个update语句等的操作的时候，并不会立即持久化

生成一个redo log;  --生成的一个redo log对象，它会存在于内存区域Log Buffer中，后面事务提交后，才会把它持久化到磁盘里面来

commit;

redo log持久化；--当事务提交后才会进行持久化，假如，事务都回滚了，根本就不需要再持久化了

redo log配置项

从图中可以看到，从内存到硬盘经历了一次操作系统缓存，这一层，那它到底有什么用呢？

写文件的时候，write和flush

write-->先写到操作系统里面

flush-->再刷到磁盘里面

四、Binlog

Redo log和Undo log是Innodb里面的概念，而Binlog是Mysql里面的概念。

既然，mysql已经有了Binlog了，为什么还要搞这个Redo log呢？

Binlog记录的都是一些sql语句，Redo log记录的是对于某一页而言，哪个位置的数据进行了修改，相当于Redo log记录的是物理位置。

当我们基于这两个日志，进行数据恢复的时候，肯定是Redo log性能更高一些，因为Redo log记录的是物理日志，直接找到那个位置，把它改了就行了。

而Binlog记录的就是一些sql语句，恢复的时候是要执行sql的，又会有很多逻辑去执行，自然性能会低一些。所以，又有了Redo log。

当进行Mysql主从的时候，只能用Binlog了，因为Redo log两个文件也就48M，当满了以后会被冲掉。

五、Undolog

原来c=666

update语句id=8 c=777
begin;
update 语句id=8 c=777;  --当执行一个update语句等的时候，buffer pool的数据已经修改了(777)，当回滚以后怎么还原？肯定是要在修改的时候，记录一个修改前的数据，这就是undo log
rollback;

undo log也是会持久化的