以下内容是在看书(《深入MySQL innodb存储引擎》)的过程中随手敲的,很混乱,做个记录
InnoDB
后台线程:
IO线程(由 innodb_file_io_threads参数控制)、1个master thread、一个锁监控线程、1个错误监控线程
show engine innodb status\G
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
mysql> show variables like 'innodb_%io_threads'\G
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
Value: 4
2 rows in set (0.00 sec)
——————————————————————————
内存:
buffer pool、redo log buffer 以及 额外的内存池
参数:innodb_buffer_pool_size、 innodb_log_buffer_size
数据库文件按页读取(16K)到缓冲池,LRU替换
索引页、数据页、undo页、插入缓冲……
对脏页,以一定的频率修改。
-----------------------------------------------
1.master thread:定时进行loop
删除无用undo页、合并插入缓冲、刷新脏页……
2.插入缓冲:和数据页一样,是物理页的一个组成部分
需要满足两个条件:索引是辅助索引、索引不是唯一的
当非聚集索引页不在缓冲池中时,则先放入到一个插入缓冲区中
写密集的情况下,插入缓冲会占用过多的缓冲池内存,默认最大为1/2
在ibuf0ibuf.c文件中进行了定义:
/* Buffer pool size per the maximum insert buffer size */
#define IBUF_POOL_SIZE_PER_MAX_SIZE 2
我们可以对上述值进行修改,来调整插入缓冲的大小
3.两次写
redo log 记录的是对页的物理操作,比如偏移量800.写入'aaa'操作。。
double write由两部分组成:内存中的double write buffer(2MB)和物理磁盘上的共享表空间中连续的128个页(2MB)
需要刷新脏页时
->memcpy到doublewrite buffer
->分两次,每次写1MB到共享表空间的物理磁盘上,并马上调用fsync,同步磁盘
->再将double write buffer中的页写入到各个表空间文件中
参数skip_innodb_doublewrite禁止两次写功能
4.自适应哈希索引:
通过缓冲池的B+树构造而来,建造速度快,不需要将整个表建哈希索引
只能搜索等值查询,即 select....where a = 'xx'
参数:innodb_adaptive_hash_index 禁用或启动此特性,默认为开启
5.
mysql> select @@innod