【MySQL-存储引擎，逻辑存储结构，内存，磁盘】

Posted on by hackdl
  • 目录：
  • 一、MySQL存储引擎
  • 二、InnoDB存储引擎

  • 1、InnoDB逻辑存储结构

  • 2、InnoDB架构

  • 3、InnoDB内存结构

  • 4、InnoDB磁盘结构

一、MySQL存储引擎

1、查看当前版本支持的存储引擎

show engines;

8.0.26版本，输出结果如下：

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| 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         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

可以看到当前数据库默认使用的是​​InnoDB存储引擎​​，当然也可以建表的时候指定想要使用的存储引擎。

2、常见的存储引擎及特点

  • ① InnoDB：支持事务、支持行锁/表锁、支持外键；
  • ② MyISAM：不支持事务、支持表锁、支持全文索引(InnoDB5.6版本之后)；
  • ③ Memory：不支持事务、支持表锁、支持Hash索引。

3、存储引擎的选择

  • ① InnoDB

如果应用对​​事务的完整性​​​有比较高的要求，在并发条件下要求​​数据的一致性​​​，数据操作除了插入和查询之外，还包含很多的​​更新​​​、​​删除​​操作，那么InnoDB存储引擎是比较合适的选择；

  • ② MyISAM

如果应用是​​以读操作和插入操作为主​​，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的；

  • ③ MEMORY

将所有数据保存在内存中，访问速度快，通常用于​​临时表​​​及​​缓存​​。（大小受限、无法保障数据安全）

二、InnoDB存储引擎

1、InnoDB逻辑存储结构

  • ① 表空间（Tablespace）

表空间是InnoDB存储引擎逻辑结构的最高层， 如果用户启用了参数 ​​innodb_file_per_table​​(在8.0版本中默认开启) ，则每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

查看.ibd文件存储位置的命令：​​show global variables like '%datadir%';​​​，位置为​​/var/lib/mysql/​​，如下：

show global variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

  • ② 段（Segment）

段，分为​​数据段（Leaf node segment）​​​、​​索引段（Non-leaf node segment）​​​、​​回滚段（Rollback segment）​​​，段用来管理多个Extent（区）。
关于段举例：InnoDB是索引组织表，数据段就是B+树的叶子节点， 索引段即为B+树的非叶子节点。

  • ③ 区（Extent）

区，表空间的单元结构，每个区的大小为​​1M​​​。 默认情况下， InnoDB存储引擎页大小为16K， 即一个区中一共有​​64个连续的页​​。

  • ④ 页（Page）

页，是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 ​​16KB​​​。为了保证页的连续性，InnoDB 存储引擎​​每次从磁盘申请 4-5 个区​​。

  • ⑤ 行（Row）

行，InnoDB 存储引擎数据是按行进行存放的。

在行中，默认有两个隐藏字段：

  • ​​Trx_id​​​：每次对某条记录进行改动时，都会把对应的​​事务id​​赋值给trx_id隐藏列。
  • ​​Roll_pointer​​​：每次对某条引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该​​记录修改前的信息​​。

2、InnoDB架构

官方文档查看位置： InnoDB Architecture

3、InnoDB内存结构

内存结构包含：​​Buffer Pool​​​、​​Change Buffer​​​、​​Adaptive Hash Index​​​、​​Log Buffer​​。

  • ① Buffer Pool（缓冲池）

Ⅰ 缓冲池官方文档：Buffer POOL

在专用服务器上，通常将多达80％的物理内存分配给缓冲池 。

show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

在InnoDB的缓冲池中不仅缓存了​​索引页​​​和​​数据页​​​，还包含了​​undo页​​​、​​插入缓存​​​、​​自适应哈希索引​​​以及​​InnoDB的锁信息​​等等。

缓冲池 Buffer Pool，是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

  • free page：空闲page，未被使用。
  • clean page：被使用page，数据没有被修改过。
  • dirty page：脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。
  • ② Change Buffer（更改缓冲区）

Ⅰ 更改缓冲区官方文档：  Change Buffer

更改缓冲区是一种特殊的数据结构，当 二级索引页​​​不在​缓冲池中时，它将缓存这些页的更改。由INSERT、UPDATE DELETE（DML） 操作导致的缓冲更改先保存在更改缓冲区，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

与 聚集索引不同，二级索引通常是非唯一的，并且插入到二级索引中按相对随机的顺序发生。同样，删除和更新可能会影响不在索引树中相邻的二级索引页。这些大量随机访问的 I/O会大大影响效率，在更改缓冲区中对这些​​更改做合并​​​，以​​减少IO操作​​。

  • ③ Adaptive Hash Index（自适应哈希索引）

Ⅰ 自适应哈希索引官方文档： Adaptive  Hash  Index

hash索引在进行等值匹配时，一般性能是要高于B+树的。InnoDB存储引擎会监控对表上各索引页的查询情况，根据实际情况判断是否创建自适应哈希索引。自适应哈希索引，无需人工干预，是系统根据情况​​自动完成​​。

show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
  • ④ Log Buffer（日志缓冲区）

Ⅰ 日志缓冲区官方文档：  Log  Buffer

日志缓冲区，用来保存要写入到磁盘中的log日志数据（​​redo log​​​、​​undo log​​​），​​默认大小为 16MB​​，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

查看缓冲区大小：

show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

刷盘时机参数：

show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

刷盘时机参数说明：

  • 0: 每秒将日志写入并刷新到磁盘一次；
  • 1: 日志在每次事务提交时写入并刷新到磁盘，默认值；
  • 2: 日志在每次事务提交后写入，并每秒刷新到磁盘一次。

4、InnoDB磁盘结构

  • ① The System Tablespace（系统表空间）

Ⅰ 系统表空间官方文档： The  System  Tablespaces。

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间中创建的，而不是在按表的文件或常规表空间中创建的，则它还可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)。

show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

系统表空间，默认的文件名： ​​ibdata1​​。

  • ② File-Per-Table Tablespaces（每个表的文件空间）

Ⅰ 每个表的文件空间官方文档： File-Per-Table  Tablespaces。

每个表的文件表空间包含单个表的数据和索引，并存储在文件系统上的单个数据文件中(​​.ibd文件​​)。

show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
  • ③ General Tablespaces（常规表空间）

Ⅰ 常规表空间官方文档： General   Tablespaces

语法：

CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

在数据目录中创建常规表空间：

CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

创建表：

CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
  • ④ Undo Tablespaces（撤消表空间）

Ⅰ 撤消表空间官方文档：  Undo  Tablespaces

撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志。

  • ⑤ Temporary Tablespaces（临时表空间）

Ⅰ 临时表空间官方文档： Temporary  Tablespacs

InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

  • ⑥ Doublewrite Buffer Files（双写缓冲区）

Ⅰ 双写缓冲区官方文档： Doublewrite   Buffer

双写缓冲区，innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

#ib_16384_0.dblwr
#ib_16384_1.dblwr
  • ⑦ Redo Log（重做日志）

Ⅰ 重做日志官方文档：   Redo  Log

重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中, 用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用。

三、结尾

以上即为MySQL-存储引擎的部分内容

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.e1idc.net

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: