mysql8学习笔记29--InnoDB内核2
InnoDB存储引擎介绍
在MySQL实例中执行show engines命令查看存储引擎情况
Support=YES代表当前支持的存储引擎，DEFAULT代表默认存储引擎
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql>
InnoDB体系结构
InnoDB表数据存储格式
特点：
根据主键寻址速度很快
主键值递增的insert插入效率较好
主键值随机insert插入操作效率差
如果建表时没有创建主键：
InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB维护了一个全局的dictsys.row_id，所以未定义主键的表都会共享该row_id，每次插入一条数据都把全局row_id当成主键id，然后全局row_id加1。
该全局row_id在代码实现上使用的事bigint unsigned类型，但实际上只给row_id保留了6字节，所以这种设计就会存在一个问题：如果全局row_id一直涨，直到2的48次幂-1时，这个时候再加1，row_id的低48位都会变为0，如果再插入新一行数据时，拿到的row_id就为0，这样的话就存在主键冲突的可能，所以为了避免这种隐患，每个表都需要一个主键。
• 缓存池
buffer pool缓存池是InnoDB在内存中开辟的用来缓存表数据和索引数据的区域，一般可以设置为50%~80%的物理内存大小，通过对经常访问的数据放置到内存当中来加快访问速度。
Buffer pool以page页的格式组成，页之间组成list列表，并通过LRU算法（最近最少使用算法）对长久不使用的页进行置换。数据的读写需要经过缓存（缓存在buffer pool 即在内存中）数据以整页（16K）位单位读取到缓存中缓存中的数据以LRU策略换出（最少使用策略）
IO效率高，性能好
1、越常用的数据，放在列表的最前端，越少用的数据，放在列表的末尾，也最容易被置换掉。
2、数据库读写操作，都要经过buffer_pool，比如读数据，会先把数据从磁盘写到buffer pool，再由buffer pool到客户端。
mysql> show variables like '%buffer_pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 134217728 | +-------------------------------------+----------------+ 10 rows in set (0.00 sec) mysql>
• Adaptive Hash Index（自适应哈希索引）
Adaptive Hash index属性使得InnoDB更像是内存数据库。该属性通过innodb_adaptive_hash_index开启，也可以通过—skip-innodb_adaptive_hash_index参数关闭。
InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive） 的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。
mysql> show variables like '%innodb_adaptive%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_hash_index_parts | 8 | | innodb_adaptive_max_sleep_delay | 150000 | +----------------------------------+--------+ 5 rows in set (0.00 sec) mysql>
•自适应哈希索引
哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数
据。
而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。
innodb会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升，则自动建立哈希索引，称之为自适应哈希
索引（Adaptive Hash Index，AHI）。
AHI有一个要求，就是对这个页的连续访问模式必须是一样的。
例如对于（a,b）访问模式情况：
where a = xxx
where a = xxx and b = xxx
AHI启动后，读写速度提高了2倍，辅助索引的连接操作性能可以提高5倍。
AHI，是数据库自动优化的，DBA只需要指导开发人员去尽量使用符合AHI条件的查询，以提高效率。
• Redo log buffer
Redo log buffer是一块用来存放写入redo log文件内容的内存区域，内存的大小由innodb_log_buffer_size参数确定。该buffer的内容会定期刷新到磁盘的redo log文件中。参数innodb_flush_log_at_trx_commit决定了刷新到文件的方式，参数innodb_flush_log_at_timeout参数决定了刷新的频率。
mysql> show variables like '%innodb_log%'; +------------------------------------+----------+ | Variable_name | Value | +------------------------------------+----------+ | innodb_log_buffer_size | 16777216 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_log_spin_cpu_abs_lwm | 80 | | innodb_log_spin_cpu_pct_hwm | 50 | | innodb_log_wait_for_flush_spin_hwm | 400 | | innodb_log_write_ahead_size | 8192 | +------------------------------------+----------+ 10 rows in set (0.00 sec) mysql> select 16777216/1024/1024; +--------------------+ | 16777216/1024/1024 | +--------------------+ | 16.00000000 | +--------------------+ 1 row in set (0.00 sec) mysql>
mysql> show variables like '%innodb_flush%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | fsync | | innodb_flush_neighbors | 0 | | innodb_flush_sync | ON | | innodb_flushing_avg_loops | 30 | +--------------------------------+-------+ 6 rows in set (0.00 sec) mysql>
提交事务的时候将 redo 日志写入磁盘中，所谓的 redo 日志，就是记录下来你对数据做了什么修改，比如对 “id=10 这行记录修改了 name 字段的值为 xxx”，这就是一个日志。如果我们想要提交一个事务了，此时就会根据一定的策略把 redo 日志从 redo log buffer 里刷入到磁盘文件里去。此时这个策略是通过 innodb_flush_log_at_trx_commit 来配置的，他有几个选项。
值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。如果不在乎事务丢失，0和2能获得更高的性能。
innodb日志持久化相关参数
innodb_flush_log_at_trx_commit
0：每秒写入并持久化一次（不安全，性能高，无论mysql或服务器宕机，都会
丢数据最多1秒的数据）
1：每次commit都持久化（安全，性能低，IO负担重）
2：每次commit都写入内存的内存缓存，每秒再刷新到磁盘（安全，性能折中
，mysql宕机数据不会丢失，服务器宕机数据会丢失最多1秒的数据）
innodb_flush_log_at_timeout参数决定最多丢失多少秒的数据，默认是1秒
• redo log
redo日志是存在于磁盘上的文件，包括ib_logfile0和ib_logfile1两个文件，常用于在crash恢复发生时将还没来得及写入到数据文件中但已经完成提交的事务在数据库初始化时重新执行一遍。
InnoDB对redo log buffer写入到redo log文件的方式提供了组提交(groupcommit)的方式，意味着针对一次写磁盘操作可以包含多个事务数据，用此方法提高性能。
[root@localhost ~]# ll -h /mysql8/mysql_data/ib_log* -rw-r-----. 1 mysql mysql 48M May 19 09:38 /mysql8/mysql_data/ib_logfile0 -rw-r-----. 1 mysql mysql 48M Jun 27 2020 /mysql8/mysql_data/ib_logfile1 [root@localhost ~]#
PS：一般redo log日志文件设置多大合适：1个小时内不会被覆盖。
系统表空间
InnoDB的系统表空间用来存放表和索引数据，同时也是doublewriter缓存，change缓存和回滚日志的存储空间，系统表空间是被多个表共享的表空间。默认情况下，系统表空间只有一个系统数据文件，名为ibdata1。系统数据文件的位置和个数由参数innodb_data_file_path参数决定。
mysql> show variables like '%innodb_data%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | +-----------------------+------------------------+ 2 rows in set (0.00 sec)
• Doublewrite缓存
Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。
数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入。
在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write
doublewrite组成：
内存中的doublewrite buffer,大小2M，
物理磁盘上共享表空间中连续的128个页，即2个区（extend），大小同样为2M。
对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中
的doublewrite buffer，
之后通过doublewrite 再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程中，
因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页
的写入后，再将doublewrite buffer 中的页写入各个 表空间文件中，此时的写入则是离散的。如果
操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中的
doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。
