MySQL_11.InnoDB Buffer Pool原理与配置

1.buffer pool原理
(1)innodb_buffer_pool_instances：
windows default 1个实例、windows default 8个实例;
将热点打散,提高并发的性能改善并发,通过降低竞争因为不同的线程读和写cached pages（缓存页）
内存小于1G默认1个,这个选项只有当设置 innodb_buffer_pool_size 值大于1G时才生效
总共你指定的大小是分配给所有的buffer pools。
计算buffer pool每个instance大小?

select @@innodb_buffer_pool_size / @@innodb_buffer_pool_instances / 1024 / 1024 "MB" ;

(2)innodb_page_size：

innodb_buffer_pool以页PAGE单位,大小同 innodb_page_size一样,16K大小;
select @@innodb_page_size / 1024 "KB";

(3)innodb buffer pool组成部分：

free list：?? ??? ?innodb启动时,有多个16K的空白页,这些页就存在free list 中。
lur list：?? ??? ?当读取一个数据页的时候,就从free list 中取出一个页,存放数据,并将这个页放入到lur list。
flush list：?? ?当lur list 中的页第一次被修改时,就将LRU页的指针(page number) 放到flush list(只要被修改过的,无论被改了多少次),flush list 包含脏页,即数据改过未刷到磁盘的页。

free list ?? ?>?? ? LUR list?? ? > ?? ? flush list ?>?? ?磁盘 ?> free list
? ? ? ? ? ? ??

(4)LUR list管理的方式、mid point 算法 ：
这个页第1次读取的时候,该页先放到 MID point的位置。
当被读到的第2次,才将这个页放到 new page的首部。?? ?

-----------------------------------------------
LRU LIST mid point
-----------------------------------------------
new page ? ? ?||?? ?old page
-----------------------------------------------
mid point > new page?
mid point > old page ?
mid point > old page > new page?
mid point > old page > ?flush disk?
mid point > new page > old page > flush disk

2.innodb_buffer_pool_filename ?buffer pool 启动和卸载
mysql 5.6 之前每次启动buffer pool 中页是空的,每次都需要大量的时间加载新的页到内存中,启动后有一段时间性能差
mysql 5.6 之后:每次停机会dump出buffer pool的数据(space,page number),然后启动时候load进buffer pool预热

select @@innodb_buffer_pool_filename;

3.innodb buffer pool 大小的调整
mysql 5.7 之前:不能在线调整,需要重启才能生效。
mysql 5.7 之后:可以在线调整,需要修改my.cnf后重启永久生效。

4.查看 innodb buffer 状态

show engine innodb status\G;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744?? ??? ??总分配的mysql的内存?? ?
Dictionary memory allocated 116177?? ??? ??? ???数据字典内存
Buffer pool size ? 262112?? ??? ??? ??? ??? ??? 有多少innodb buffer页
Free buffers ? ? ? 261070?? ??? ??? ??? ??? ??? free list 、有多少空白的页
Database pages ? ? 1042?? ??? ??? ??? ??? ??? ??lru list 、有多少页在使用
Old database pages 0?? ??? ??? ??? ??? ??? ??? ?lru list 、old page页
Modified db pages ?0?? ??? ??? ??? ??? ??? ??? ?脏页

# 查询 innodb buffer和lru list 状态

SELECT p. POOL_ID "innodb pool ID",
? ? ? ?p. POOL_SIZE "pool页数量",
? ? ? ?p. FREE_BUFFERS "free list 空白页数量",
? ? ? ?p. DATABASE_PAGES "lru list 使用页数量",
? ? ? ?p. OLD_DATABASE_PAGES "lru list 旧页数量",
? ? ? ?p. MODIFIED_DATABASE_PAGES "lru list 脏页数量",
? ? ? ?l. LRU_POSITION "lru位置",
? ? ? ?l. SPACE "表空间号",
? ? ? ?l. PAGE_NUMBER "页号",
? ? ? ?l. PAGE_TYPE "页类型",
? ? ? ?l. TABLE_NAME "页关联表名",
? ? ? ?l. OLDEST_MODIFICATION "第一次修改的页值",
? ? ? ?l. NEWEST_MODIFICATION "最近修改页的LSN值"
? FROM information_schema.innodb_buffer_pool_stats p
?INNER JOIN information_schema.innodb_buffer_page_lru l
? ? ON p. POOL_ID = l. POOL_ID;

5.InnoDB 数据字典,数据库的元数据(数据库名,表名,数据类型,结构,访问权限)
(1)mysql 5.7 之前: 系统表空间 + frm 文件
(2)mysql 8.0 之后: 系统表空间
(3)获取mysql数据字典,数据库的元数据内容SQL语句

SELECT C.COLUMN_NAME ? ?AS '字段名',
? ? ? ?C.COLUMN_TYPE ? ?AS '数据类型',
? ? ? ?C.IS_NULLABLE ? ?AS '允许为空',
? ? ? ?C.EXTRA ? ? ? ? ?AS 'PK',
? ? ? ?C.COLUMN_COMMENT AS '字段说明'
? FROM information_schema.columns C
?INNER JOIN information_schema .tables T
? ? ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
? ?AND C.TABLE_NAME = T.TABLE_NAME
?WHERE T.TABLE_SCHEMA = 'scott'
? ?AND T.TABLE_NAME = 'emp';