高性能MySQL学习二(常用存储引擎功能摘要、InnoDB内存结构)

本文链接：https://blog.youkuaiyun.com/yyyxxxs/article/details/98201327

高性能MySQL学习一(逻辑架构、并发控制、事务、四种隔离级别、多版本并发控制MVCC)
一、存储引擎
存储引擎是MySQL组件，用于处理不同表类型的SQL操作，SHOW ENGINES语句可以查看服务器支持哪些存储引擎。

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

存储引擎功能摘要
在这里插入图片描述
[a] InnoDB支持地理空间索引在MySQL5.7.5版本或者更高的版本是可用的

[b] InnoDB为了其适应性哈希索引功能利用内部的哈希索引

[c] InnoDB支持全文索引在MySQL5.6.4版本或者更高的版本是可用的

[d] 压缩MyISAM表只有当使用压缩行格式是支持的。表使用MyISAM的压缩行格式只能读。

[e] 压缩InnoDB表需要InnoDB Barracuda文件格式。

[f] 在服务器中实施（通过加密功能）。数据静止加密表，在MySQL5.7版本或者更高的版本是可用的。

[g] 实施在服务器，而不是在存储引擎。

[h] 实施在服务器，而不是在存储引擎。

二、InnoDB存储引擎
MySQL 8.0中的默认存储引擎。

InnoDB是一种适用于MySQL的事务安全（ACID兼容）存储引擎，具有提交，回滚和崩溃恢复功能，可保护用户数据。
InnoDB行级锁定（没有升级到更粗略的粒度锁）和Oracle风格的一致非锁定读取增加了多用户并发性和性能。
InnoDB表格将您的数据排列在磁盘上，以根据主键优化查询。每个 InnoDB表都有一个称为聚簇索引的主键索引，用于组织数据以最小化主键查找的I / O。
要保持数据完整性，请 InnoDB支持 FOREIGN KEY约束。使用外键，将检查插入，更新和删除，以确保它们不会导致不同表之间的不一致。

InnoDB架构
在这里插入图片描述
1、InnoDB内存结构

(1)缓冲池是主存储器中的一个区域，用于在访问时缓存表和索引数据。缓冲池允许直接从内存处理常用数据，从而加快处理速度。在专用服务器上，通常会将最多80％的物理内存分配给缓冲池。使用least recently used (LRU) 算法将缓冲池作为列表管理。
在这里插入图片描述

mysql> SHOW ENGINE INNODB STATUS;		//可以查看缓冲池指标
...	...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 429783
Buffer pool size   8192		//分配给缓冲池的页面的总大小。
Free buffers       7186		//缓冲池空闲列表的页面总大小。
Database pages     995		//缓冲池LRU列表的页面总大小。
Old database pages 347		//缓冲池旧LRU子列表的页面总大小。
Modified db pages  0		//缓冲池中修改的当前页数。
Pending reads      0		//等待读入缓冲池的缓冲池页数。
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 73, not young 488
0.00 youngs/s, 0.00 non-youngs/s
Pages read 834, created 165, written 322
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 995, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...	...

在这里插入图片描述
(2)更改缓冲区是一种特殊的数据结构，当这些页面不在缓冲池中时，它会更改二级索引页面。缓冲后的更改(可能由插入、更新或删除操作(DML)导致)将在稍后由其他读操作将页面加载到缓冲池时合并。

当对表执行插入、更新和删除操作时，索引列的值(特别是二级索引的值)通常是无序的，这需要大量的I/O来更新二级索引。当相关页不在缓冲池中时，更改缓冲区将更改缓存到二级索引项，从而通过不立即从磁盘读入页来避免昂贵的I/O操作。当页面加载到缓冲池中时，将合并已缓存的更改，然后将更新后的页面刷新到磁盘。InnoDB主线程在服务器接近空闲和缓慢关闭期间合并缓冲更改。

(3)自适应哈希索引特性使InnoDB能够在不牺牲事务特性或可靠性的情况下，在具有适当的工作负载和足够的缓冲池内存组合的系统上执行更像内存中的数据库。自适应哈希索引特性由innodb_adaptive_hash_index变量启用，或在服务器启动时通过-skip-innodb- adaptive_hash_index关闭。

(4)日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域。日志缓冲区大小由innodb_log_buffer_size变量定义。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新，插入或删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘I / O。

三、MyISAM存储引擎
MySQL5.1及之前版本，MyISAM是默认存储引擎。提供全文索引、压缩、空间函数等特性，但不支持事务和行级锁，崩溃后无法安全恢复，但对于只读数据，或者表比较小，可以使用MyISAM。
这些表占用空间小。表级锁定限制了读/写工作负载的性能，因此它通常用于Web和数据仓库配置中的只读或大部分读取工作负载。

四、Memory存储引擎
Memory表所有数据都保存在内存中，不进行磁盘I/O ，至少比MyISAM表快一个数量级。表结构重启后会保留，但数据会丢失。
在这里插入图片描述