MySQL探秘(二)MySQL存储引擎

MySQL探秘(二)MySQL存储引擎

+——————–+———+—————————————————————-+————–+——+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+——————–+———+—————————————————————-+————–+——+————+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | 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 |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+——————–+———+—————————————————————-+————–+——+————+
9 rows in set (0.00 sec)

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

3.如何选择 MySQL 存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

可以根据以下的原则来选择 MySQL 存储引擎：

• 如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
• 如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。
• 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• 如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

提示：使用哪一种引擎要根据需要灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。

4.MySQL 存储引擎类型

查看当前表使用的存储引擎

mysql> show table status like 'user' \G;
*************************** 1. row ***************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 5
Create_time: 2019-11-22 20:01:57
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

定义存储引擎

在创建表的时候，在create语句最后加上engine=innodb/…

或者用alter table语句修改

mysql> alter table user engine=innodb;

下面介绍一些常用的存储引擎和各自的优缺点以及应用场景

5.InnoDB存储引擎sql语句的执行

mysql> SHOW STATUS LIKE '%innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 200310  9:12:27 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 381                                              |
| Innodb_buffer_pool_bytes_data         | 6242304                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 56                                               |
| Innodb_buffer_pool_pages_free         | 7811                                             |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 8192                                             |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 2057                                             |
| Innodb_buffer_pool_reads              | 330                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 631                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)

mysql> SHOW VARIABLES like '%innodb_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> SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+

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_write_ahead_size | 8192     |
+-----------------------------+----------+
7 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

需要注意:redo log 的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自 buffer pool。redo log 写入磁盘，不是写入数据文件。

那么，Log Buffer 什么时候写入 log file?

在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush 就是把操作系统缓冲区写入到磁盘。

log buffer 写入磁盘的时机，由一个参数控制，默认是 1。

mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

• The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.(每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file，并且刷到磁盘 中去。)
• With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.(log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush 操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。)
• With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.(每次事务提交时 MySQL 都会把 log buffer 的数据写入 log file。但是 flush 操作并不会同时进行。该模式下，MySQL 会每秒执行一次 flush 操作。)

这是内存结构的第 4 块内容，redo log，它又分成内存和磁盘两部分。redo log 有 什么特点?

1、redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

2、不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。

3、redo log 的大小是固定的，前面的内容会被覆盖。

check point 是当前要覆盖的位置。如果 write pos 跟 check point 重叠，说明 redo log 已经写满，这时候需要同步 redo log 到磁盘中。

这是 MySQL 的内存结构，总结一下，分为:
Buffer pool、change buffer、Adaptive Hash Index、 log buffer。

磁盘结构里面主要是各种各样的表空间，叫做 Table space。

5.2.2.磁盘结构

表空间可以看做是innodb存储引擎结构的最高层，所有的数据都存放在表空间中。

innodb中表空间可以分为以下几种：

• 系统表空间（system tablespace）
• 独占表空间（file-per-table tablespace）
• 通用表空间（general tablespace）
• 临时表空间（temporary tablespace）
• undo表空间（undo log tablespace）

系统表空间 system tablespace

在默认情况下 InnoDB 存储引擎有一个共享表空间(对应文件/var/lib/mysql/ ibdata1)，也叫系统表空间。系统表空间包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 Undo Logs)，也包含用户创建的表和索引数据。

系统表空间可以通过参数innodb_data_file_path对其进行配置，默认配置如下：

mysql> show variables like 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

#ibdata1:指定默认文件名
#12M:指定默认文件大小
#autoextend： 表示文件是自增的，当超过12M时，会自动增加大小，没有指定表空间的位置，默认是在数据库目录下面的。

采用了默认的表空间，但实际大小如下
mysql> system ls -lh /data/mysql/ibdata1;
-rw-rw---- 1 mysql mysql 76M Oct 16 15:30 /data/mysql/ibdata1

如果启用了innodb_file_per_table参数(默认开启)，则每张表的数据可以单独放到一个表空间。这个文件就是数据目录下的 ibd 文件(例如/data/mysql/test/user.ibd)，存放表的索引和数据。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

需要注意的是： 每张表的表空间内存放的只是数据，索引，和插入缓冲Bitmap页，其他类型的数据，如回滚信息，插入缓冲索引页，系统事务信息，二次写缓冲等，还是存在在原来的系统表空间中。

即便启用了独立表空间，共享表空间还是会不断增大。

即便是执行了rollback撤回了事务，共享表空间的大小也不会变小，虽然innodb存储引擎不会回收这些表空间，但是innodb存储引擎 会自动判断这些undo信息是否还需要，如果不需要则会将这些空间标记为可用空间，供下次使用.

通用表空间 general tablespaces

通用表空间也是一种共享的表空间，跟 ibdata1 类似。

可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定 义。

创建通用表空间的语法如下：

CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    [FILE_BLOCK_SIZE = value]    #指定通用表空间文件块的大小
        [ENGINE [=] engine_name] #指定存储引擎

创建通用表空间

mysql> create tablespace test_ts add datafile 'test_ts.ibd' file_block_size=16K engine=innodb;
Query OK, 0 rows affected (0.00 sec)

#datafile若是使用绝对路径，则可以指定datadir之外的目录，默认是在datadir指定的目录

绑定表空间，把创建的表的表空间指定到上面创建的通用表空间。

create table test_table(id integer) tablespace test_ts;

不同表空间的数据是可以移动的。 删除表空间需要先删除里面的所有表:

drop table test_table;
drop tablespace test_ts;

临时表空间 temporary tablespaces

存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录 下的 ibtmp1 文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。

重做日志（redo log）

物理日志

事务执行时需要将执行的事务日志写入到日志文件里,对应的日志文件为REDO日志。当每条SQL进行数据库更新操作时,首先将REDO日志写入到日志缓冲区。当客户端执行COMMIT命令提交时,日志缓冲区的内容将被刷新到磁盘,日志缓冲区的刷新方式或者时间间隔可以通过参数innidb_flush_log_at_trx_commit控制。

REDO日志对应于磁盘上的ib_logfileN文件,该文件默认为5MB,建议设置成512MB以便容纳较大的事务。在MySQL崩溃恢复时会重新执行REDO日志中的记录。

undo表空间 undo log tablespace

回滚日志（undo log）

逻辑日志

UNDO日志与 REDO日志相反,UNDO日志主要用于事务异常时的数据回滚,具体内容就是复制事务前的数据库内容到UNDO缓冲区,然后在合适的时间将内容刷新到磁盘。

与REDO日志不同的是,磁盘上不存在单独的UNDO日志文件,所有的UNDO日志均存放在表空间对应的.ibd数据文件中,即使MySQL服务启用了独立表空间,依然如此。UNDO日志又称为回滚段。

mysql> show global variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory    | ./         |
| innodb_undo_log_truncate | OFF        |
| innodb_undo_logs         | 128        |
| innodb_undo_tablespaces  | 0          |
+--------------------------+------------+
5 rows in set (0.00 sec)

5.2.3 Binlog

binlog 以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是 数据值，属于逻辑日志)，可以用来做主从复制和数据恢复。

跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操 作重放一遍，来实现数据的恢复。

binlog 的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器 的 binlog，然后执行一遍。

有了这些日志之后，我们来总结一下一个更新操作的流程，这是一个简化的过程。 name 原值是 zhangsan。

例如一条语句:update user set name = ‘lisi’ where id=1;

1、事务开始，先查询到这条数据，如果有缓存，也会用到缓存，结果返回给 Server 的执行器。

2、执行器修改这一行数据的值为 lisi。

3、执行器调用引擎的 API 接口，将修改结果更新到内存(Buffer Pool)。

4、存储引擎记录 name=zhangsan 到 undo log。

5、存储引擎记录 name=lisi 到 redo log，这时 redo log 进入 prepare 状态。

6、返回结果给执行器，执行完成了，可以随时提交。

7、执行器收到通知后记录 binlog。

8、执行器调用存储引擎的 API 接口，commit提交事务。

9、存储引擎设置 redo log 为 commit 状态。

5.3. InnoDB 的逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，我们称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block），1 extent = 64 pages,InnoDB存储引擎的逻辑存储结构大致如图所示：

表空间 Table Space

上面磁盘结构的时候说过了，表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为:系统表空间、独占表空间、通用表空间、 临时表空间、Undo 表空间。

段 Segment

表空间是由各个段组成的，常见的段有数据段(leaf node segment)，索引段(none-leaf node segment)，回滚段(rollback segment)等，段是一个逻辑 的概念。一个 ibd 文件(独立表空间文件)里面会由很多个段组成。

Innodb是索引聚集表，所以数据就是索引，索引就是数据，数据段即是B+树的叶子节点（图中的leaf node segment），索引段即为B+树的非索引节点（图中的non-leaf node segment）;数据段管理叶子节点的数据，索引段管理非叶子节点的数据。 也就是说，一个表的段数，就是索引的个数乘以 2。

区 Extent

一个段(Segment)又由很多的区(也可以叫簇)组成，每个区的大小是 1MB(64 个连续的页)。

每一个段至少会有一个区，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是区。对于大的数据段，为了保证区中页的连续性，InnoDB每次最多申请4个区。

页 Page

为了高效管理物理空间，对区进一步细分，就得到了页。区是由连续的页(Page) 组成的空间，一个区中有 64 个连续的页。 (1MB/16KB=64)。这些页面在物理上和逻辑上都是连续的。

跟大多数数据库一样，InnoDB 也有页的概念(也可以称为块)，每个页默认 16KB。 页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。

一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB（如下图所示），也就是说一个 表空间最多存储 64TB 的数据。

mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

注意，文件系统中，也有页的概念。
操作系统和内存打交道，最小的单位是页 Page。文件系统的内存页通常是 4K。

假设一行数据大小是 1K，那么一个数据页可以放 16 行这样的数据。

上图为 Page 数据结构，File Header 字段用于记录 Page 的头信息，其中比较重要的是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表。Page Header 字段用于记录 Page 的状态信息。接下来的 Infimum 和 Supremum 是两个伪行记录，Infimum（下确界）记录比该页中任何主键值都要小的值，Supremum （上确界）记录比该页中任何主键值都要大的值，这个伪记录分别构成了页中记录的边界。

User Records 中存放的是实际的数据行记录，具体的行记录结构将在本文的第二节中详细介绍。Free Space 中存放的是空闲空间，被删除的行记录会被记录成空闲空间。Page Directory 记录着与二叉查找相关的信息。File Trailer 存储用于检测数据完整性的校验和等数据。

常见的页类型有：

（1）、数据页（B-tree Node）

（2）、undo页（undo Log Page）

（3）、系统页（System Page）

（4）、事务数据页（Transaction System Page）

（5）、插入缓冲位图页（Insert Buffer Bitmap）

（6）、插入缓冲空闲列表页（Insert Buffer Free List）

（7）、未压缩的二进制大对象页（Uncompressed BLOB Page）

（8）、压缩的二进制大对象页（compressed BLOB Page）

行 Row

InnoDB 存储引擎是面向行的(row-oriented)，也就是说数据的存放按行进行存放。每个页存放的行记录也是有定义的，最多允许存放16KB/2 -200，即最多7992行记录。