MySQL存储引擎
目录
内容概述
1.什么是存储引擎
2.查看存储引擎信息
3.修改存储引擎
4.存储引擎实验
5.InnoDB存储引擎
内容概述
1.什么是存储引擎
数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。
存储引擎就是一种存数据的方式，如何存数据，如何为存储的数据创建索引以及如何更新、查询数据。
因为关系型数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型。数据库中的表有不同的类型，表的类型不同，会对应MySQL不同的存取机制，进而对应不同的存储引擎。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎，MySQL的核心就是存储引擎。
MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
MySQL5.7支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE等。
# 可以使用SHOW ENGINES;语句查看系统所支持的引擎类型
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | 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表示该引擎为当前默认的存储引擎。
1.1详解多种存储引擎
查看存储引擎信息
查看当前数据库中支持的存储引擎
mysql> show engines;
| 存储引擎 | 描述 |
|---|---|
| ARCHIVE | 用于数据存档的引擎，数据被插入后就不能在修改了(只支持SELECT和INSERT语句)，且不支持索引。 |
| CSV | 在存储数据时，会以逗号作为数据项之间的分隔符。 |
| BLACKHOLE | 会丢弃写操作，该操作会返回空内容。 |
| FEDERATED | 将数据存储在远程数据库中，用来访问远程表的存储引擎。 |
| InnoDB | 支持事务、行级锁以及外键等功能。 |
| MEMORY | 置于内存的表，也就是说将数据存放于内存之中，类似于Redis。 |
| MERGE | 用来管理由多个 MyISAM 表构成的表集合。 |
| MyISAM | 主要的非事务处理存储引擎，性能相对于InnoDB来说要高一些。 |
| NDB | MySQL 集群专用存储引擎 |
# 临时设置数据库中支持的存储引擎
mysql> set default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM |
+--------------------------+
1 row in set (0.00 sec)
2.查看存储引擎信息
# 查看某个表的存储引擎
mysql> show create table xiaohu.rol\G
*************************** 1. row ***************************
Table: rol
Create Table: CREATE TABLE `rol` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> select engine from information_schema.tables where table_name="gun";
+--------+
| engine |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)
# 查看MyISAM的表有哪些？
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
+--------------+------------------+--------+
| table_schema | table_name | engine |
+--------------+------------------+--------+
| linux13 | t2 | MyISAM |
| mysql | columns_priv | MyISAM |
| mysql | db | MyISAM |
| mysql | event | MyISAM |
| mysql | func | MyISAM |
| mysql | ndb_binlog_index | MyISAM |
| mysql | proc | MyISAM |
| mysql | procs_priv | MyISAM |
| mysql | proxies_priv | MyISAM |
| mysql | tables_priv | MyISAM |
| mysql | user | MyISAM |
+--------------+------------------+--------+
11 rows in set (0.00 sec)
3.修改存储引擎
在使用数据库时，我们经常会去使用不同的存储引擎来存储数据，那就难免需要修改存储引擎。
3.1修改配置文件(永久)
vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1 # InnoDB独立表空间
3.2临时修改存储引擎（临时）
mysql> set default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM |
+--------------------------+
1 row in set (0.00 sec)
3.3创建表的时候修改存储引擎
create table jerryli(id int) engine=MyISAM;
4.存储引擎实验
我们来验证下几个存储引擎的区别
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table t1(id int)engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2(id int)engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t3(id int)engine=memory;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t4(id int)engine=blackhole;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
+---------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
ls /mysql_data/db1
t1.frm t1.ibd t2.MYD t2.MYI t2.frm t3.frm t4.frm
# 发现后存在两种存储引擎只有表结构，没有数据。
# Memory，在重启mysql或者重启机器后，表内数据清空。
# BLACKHOLE，向表内插入数据，都相当于丢入黑洞，表内永远不存记录。
# InnoDB支持事务，而MyISAM存储引擎是不支持事务的。
5.InnoDB存储引擎
InnoDB存储引擎是MySQL数据库的一个重要的存储引擎，和其他的存储引擎相比，InnoDB引擎的优点是支持事务以及参数完整性（有外键）等。MySQL5.5.5以后默认的存储引擎都是InnoDB引擎。
InnoDB的逻辑架构原理
InnoDB的逻辑架构有三个部分组成，分别是：在内存中的架构、操作系统架构和在硬盘上的架构。这三个架构组成了高性能的InnoDB存储引擎。
内存中的架构（In-Memory Structures）
InnoDB在内存架构上主要分成四个部分，分别是：缓冲池（Buffer Pool）、写缓冲（Cache Buffer）、日志缓冲（Log Buffer）以及自适应哈希索引（Adaptive Hash Index)。
1.缓冲池（Buffer Pool）缓冲池，顾名思义就是一块用于缓存被访问过的表和索引数据的内存区域，缓冲池允许在内存中处理一些用户频繁访问的数据。缓冲池的存在主要是为了通过降低磁盘IO的次数来提升数据的访问性能。
2. 写缓冲（Cache Buffer）写缓冲是为了缓存缓冲池中不存在的二级索引页的变更操作的一种特殊的内存数据结构。
3.日志缓冲InnoDB将数据的每次写优化为了批量写，这便于降低磁盘IO的次数，为了防止一些数据尚未写入硬盘就断电了，需要记录日志；而日志缓冲就是用来缓存一些即将要被写入磁盘日志文件（log files)中的数据。
4.自适应哈希索引(Adaptive Hash Index)自适应哈希索引是InnoDB为了加速查询性能，会根据实际需要来决定是否对于一些频繁需要被访问的索引页构建哈希索引。
操作系统缓存（Operating System Cache）
操作系统为了提升性能而降低磁盘IO的次数，在InnoDB的缓存体系与硬盘文件之间加了一层操作系统的缓存/页面缓存。用户态innodb存储引擎的进程向操作系统发起write系统调用时，在内核态完成页面缓存写入后即返回，如果想立即将页面缓存的内容立即刷入磁盘，innodb存储引擎需要发起fsync系统调用才可以。
硬盘上的架构（On-Disk Structures）
InnoDB在硬盘上总共分为六个部分，分别是表（Tables）、表空间（Tablespaces）、索引（Indexes）、双写缓冲（Doublewrite Buffer）、Redo日志和Undo段
innodb存储引擎执行流程
执行一条更新SQL语句，存储引擎执行流程可以分为三大阶段，8个小步骤：
三大阶段1.执行阶段：数据从硬盘加载到内存，写undo log,更新内存中数据，写redo log buffer
2.事务提交阶段：redo log和binlog刷入硬盘，commit标记写入redo log中
3.写入磁盘：后台IO线程随机把内存中脏数据刷到硬盘中
# 注意：InnoDB存储引擎中的每一条修改数据的SQL都是一个事务
八个小阶段：
a. 把该行数据从磁盘加载到buffer pool中，并对该行数据进行加锁
b. 把旧数据写入undo log，以便修改出错情况下的回滚
c. 在buffer pool中的数据更新，得到脏数据
d. 把修改后的数据写入到redo log buffer当中
e. 准备提交事务redo log刷入磁盘
f. 把修改的操作记录准备写入binlog日志
g. 把binlog的文件名和位置写入commit标记，commit标记写入redolog中（redo log中存放的修改后的数据与binlog中的修改操作对应上，双管齐下），事务才算提交成功；否则不会成功
h. IO线程Buffer Pool中的脏数据刷入磁盘文件，完成最终修改
# 什么是脏数据？脏数据就是写入了内存中还没有来得及写入硬盘的数据就称之为脏数据
InnoDB中的进程
Innodb存储引擎是多线程的模型，因此其在后台有多个不同的后台进程，负责处理不同工作任务
master thread
master thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷到磁盘，保障数据的一致性，其中包括脏页的刷新、合并插入缓冲(insert buffer) undo页的回收。
IO thread
在Innodb存储引擎中大量使用了AIO(Async IO)来处理写IO请求，这样可以极大提高了数据库的性能。而IO thread 的工作主要负责这些IO请求的回调(call back)处理。
innodb 1.0版本之前共有4个IO thread ，分别是write 、read 、insert buffer和log io thread。在linux平台下，iO thread 的数量不能进行调整，但是在window平台上可以通过参数innodb_file_io_thread来增大IO thread来增大IO thread ,从innodb 1.0.x版本开始，read thread 和write thread分别增大到了4个，并且不再使用innodb_file_io_threads参数，而是分别使用innodb_read_io_thread和innodb_write_io_threads参数进行设置。
# 查看IO thread进程状态
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-09-27 15:20:40 0x7fa18c620700 INNODB MONITOR OUTPUT
=====================================
--------
FILE I/O
--------
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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
327 OS file reads, 158 OS file writes, 31 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
可以看到IO thread 0为insert buffer thread。io thread 1为log thread.之后就是根据参数innodb_read_io_thread及innodb_write_io_threads来设置的读写线程，并且读线程iD总是小于写线程。
Purge Thread
事务被提交后，其所使用的undolog可能不再需要，因为需要purge thread来回收已经使用并分配的undo页。在innodb1.1版本之前，purge操作仅在innodb存储引擎的master thread中完成，而从innodb1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻master thread的工作，从而提供cpu的使用率以及提升存储引擎的性能。
Page cleaner thread
Page cleaner thread作用是将之前版本中的脏页的刷新操作都放入到单独的线程中来完成，而其目的是为了减轻master thread 的工作及对于用户查询线程的阻塞，进一步提高innodb存储引擎的性能。
# 什么是肮页？当内存数据页跟硬盘数据页内容不一致的时候，我们称这个内存页为‘脏页’。
