MySQL 之存储引擎（MySQL Storage Engine）

MySQL 之存储引擎

常见存储引擎及其特点

‌InnoDB‌：
- ‌特点‌：支持事务处理、行级锁定、外键约束，使用聚簇索引，适合高并发读写和事务处理的场景‌。
- ‌适用场景‌：需要高可靠性、高并发读写和事务处理的场景‌。
‌MyISAM‌：
- ‌特点‌：不支持事务处理和行级锁定，但支持全文索引，以高效率的插入和查询速度著称，适合读操作比写操作更频繁的场景‌。
- ‌适用场景‌：不需要事务处理，主要进行大量读操作的应用‌。
‌Memory‌：
- ‌特点‌：所有数据存储在内存中，适合快速读写操作，但数据在服务器重启后会丢失，适用于缓存和临时数据存储‌。
- ‌适用场景‌：需要快速访问数据的缓存应用‌。

存储引擎的选择依据

选择合适的存储引擎需要根据具体的应用需求来决定：

‌事务处理需求‌：如果需要事务处理，选择InnoDB。
‌读写比例‌：如果读操作远多于写操作，MyISAM可能更合适。
‌数据安全性和并发需求‌：如果需要高并发和高数据安全性，InnoDB是更好的选择。

Connectors：连接器

连接器对 Native，JDBC，ODBC，NET，PHP，Perl，Python，Ruby，Cobol 等客户端工具或编程语言的 API 对MySQL 的连接功能进行管理。连接器是 Server 端的第一个模块。

JDBC：Java 数据库连接（ Java DataBase Connectivity ）

它是一套用于执行 SQL 语句的 Java API。应用程序可通过这套API连接到关系数据库，并使用 SQL 语句来完成对数据库中数据的查询、更新和删除等操作。

ODBC：开放数据库互连（Open Database Connectivity）

是微软公司开放服务结构(WOSA，Windows Open Services Architecture)中有关数据库的一个组成部分，它建立了一组规范，并提供了一组对数据库访问的标准API。

Connection Pool：线程池

MySQL 是单进程多线程模型，因此，每个用户连接，都会创建一个连接线程，客户端和服务端通过这个线程进行数据交互。MySQL 通过线程池来管理这些线程，线程池组件的功能包括登录验证(authentication)，线程重用(Connection Pool) 等，connection limit 值决定了线程池中线程数量，也就

决定了MySQL服务的最大并发连接数，check memory 用来检测内存，caches 实现线程缓存。

#最大并发连接数，在实际的生产环境中，可以将此参数调大mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.00 sec)

SQL Interface：SQL 语句接口

完整的 sql 命令的解释器，对 SQL 语句进行检查，是否有错误，并且进行词法分析，语法分析，识别出具体操作，对象，参数等。

Parser：查询解析器

解析器会根据已经检查过的SQL语句生成一个数据结构，一般是树形结构，我们称其为解析树。在这个过程中也会校验当前连接的客户端是否有权限操作库和表等。

Optimizer：查询优化器

根据解析器生成的解析树中的各个节点，决定一个最优的执行顺序路径，保证在使用最少的开销的情况下返回正确的结果。

Cache & Buffer：查询缓存

将已查询过的结果进行缓存，下次使用相同的查询语句查询时，可以直接从缓存中返回查询结果。

存储引擎

存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。MySQL 数据库中的数据，本质上来讲，是存在于硬盘上的物理文件，不同的存储引擎有不同的特性，实际上就是使用不同的技术和方式来管理文件中的数据。

其它

MySQL 是一个复杂的系统，除了上述核心组成部分之外，还有一些其它的管理和服务工具，例如：备份和恢复工具，安全工具，集群服务，配置工具，迁移工具等。

1.1 存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力，此种技术称为存储引擎，MySQL 支持多种存储引擎，其中目前应用最广泛的是 InnoDB 和 MyISAM 两种。

在 MySQL5.5 之后默认存储引擎是 InnoDB，在之前是 MyISAM。

MySQL中常见的存储引擎特性说明

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes（B树索引）	Yes	Yes	Yes	No	No
Backup/point-in-time recovery （备份/时间点恢复）	Yes	Yes	Yes	Yes	Yes
Cluster database support（集群模式）	No	No	No	No	Yes
Clustered indexes（聚集索引）	No	No	Yes	No	No
Compressed data（数据压缩）	Yes	No	Yes	Yes	No
Data caches（数据缓存）	No	N/A	Yes	No	Yes
Encrypted data（数据加密）	Yes	Yes	Yes	Yes	Yes
Foreign key support（外键）	No	No	Yes	No	Yes
Full-text search indexes（全文检索）	Yes	No	Yes	No	No
Geospatial data type support（地理空间数据类型）	Yes	No	Yes	Yes	Yes
Geospatial indexing support（地理空间数据索引）	Yes	No	Yes	No	No
Hash indexes（HASH索引）	No	Yes	No	No	Yes
Index caches（索引缓存）	Yes	N/A	Yes	No	Yes
Locking granularity（锁颗粒度）	Table	Table	Row	Row	Row
MVCC（多版本并发控制）	No	No	Yes	No	No
Replication support（数据同步）	Yes	Limited	Yes	Yes	Yes
Storage limits（表空间上限）	256TB	RAM	64TB	None	384EB
T-tree indexes（T树索引）	No	No	No	No	Yes
Transactions（事务）	No	No	Yes	No	Yes
Update statistics for data dictionary（元数据静态更新）	Yes	Yes	Yes	Yes	Yes

官方文档

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.htmlhttps://docs.oracle.com/cd/E17952_01/mysql-5.7-en/storage-engines.html

表锁与行锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在计算机中，除了计算机自身的资源，如CPU，内存，IO等争用以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有系统必须解决的问题。

[root@rocky86 ~]# ls anaconda-ks.cfg abcls: cannot access 'abc': No such file or directoryanaconda-ks.cfg
[root@rocky86 ~]# ls anaconda-ks.cfg abc 1>ls.log 2>ls.log
#两个设备同时写一个文件，数据丢失[root@rocky86 ~]# cat ls.loganaconda-ks.cfgs 'abc': No such file or directory

在关系型数据库中，按数据操作的颗粒度划分，分为表锁，行锁和页锁。

表锁：表锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁（哪怕只操作表里面的一行数据），它实现简单，消耗资源少，被大部分 MySQL 存储引擎支持，MyISAM 存储引擎使用表锁，表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

表锁的特点是开销小，加锁快，不会出现死锁，锁定颗粒度大，发生锁冲突的概率高，并发性差。

行锁：行锁是 MySQL 中锁定粒度最小的一种锁，表示只对当前操作的行加锁，行锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。InnoDB存储引擎默认采用行锁。

行锁的特点是开销大，加锁慢，会出现死锁，锁定颗粒度最小，发生锁冲突的概率最低，并发性好。

1.1.1 MyISAM 存储引擎

MyISAM 存储引擎特点

不支持事务

表级锁定

读写相互阻塞，写入不能读，读时不能写

只缓存索引

不支持外键约束

不支持聚簇索引

支持全文索引

读取数据较快，占用资源较少

不支持MVCC（多版本并发控制机制）高并发

崩溃恢复性较差

MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景
读多写少的业务（或者只读的业务）
不需要事务支持的业务（比如转账，充值这种业务就不行）

并发访问低的业务

对数据一致性要求不高的业务

表较小（可以接受长时间进行修复操作）

MyISAM 存储引擎相关文件
tbl_name.frm 表格式定义
tbl_name.sdi 表格式定义（mysql8.0开始）

tbl_name.MYD 数据文件

tbl_name.MYI 索引文件

查看表文件

mysql> select version();+-----------+| version() |+-----------+| 5.1.52    |+-----------+1 row in set (0.00 sec)
mysql> use mysql;
#user表使用 MyISAM 存储引擎mysql> show table status like 'user'\G
#查看表文件[root@c6 ~]# ll /var/lib/mysql/mysql/user*-rw-rw---- 1 mysql mysql 10466 Jan 29 16:24 /var/lib/mysql/mysql/user.frm-rw-rw---- 1 mysql mysql 260 Jan 29 16:24 /var/lib/mysql/mysql/user.MYD-rw-rw---- 1 mysql mysql 2048 Jan 29 16:24 /var/lib/mysql/mysql/user.MYI

1.1.2 InnoDB 存储引擎

InnoDB 存储引擎特点

支持事务，适合处理大量短期事务

行级锁定

读写阻塞与事务隔离级别相关

可缓存数据和索引

支持聚簇索引

崩溃恢复性更好

支持MVCC高并发

支持表分区，支持表空间

从MySQL5.5 后支持全文索引

从MySQL5.5.5 开始为默认的数据库引擎

InnoDB 存储引擎适用场景
数据读写都较为频繁的业务
需要事务支持的业务

对并发要求较高的业务

对数据一致性要求较高的业务

InnoDB 存储引擎相关文件
tbl_name.frm 表格式定义
tbl_name.ibd 数据和索引文件

InnoDB 中有共享表空间和独立表空间的概念。

共享表空间是指表结构文件单独放在以数据命名的文件夹中，格式为 tbl_name.frm，所有 InnoDB 引擎表的数据都放在一个文件中 (ibdata1,ibdata2,...)。

独立表空间是指用独立文件存放每个表的表结构 tbl_name.frm 和数据及索引 tbl_name.ibd，在独立表空间的前提下，共享表空间中的 ibdata 文件还是存在，独立表空间文件只存储该表的数据，索引和插入缓冲的BITMAP等信息，其它信息还是存放在共享表空间中。

MySQL 5.5 版本以后默认采用独立表空间

MySQL 8.0 开始，InnoDB 引擎的 frm 文件被取消了，并入到 idb文件中了

查看表文件

mysql> select version();+-----------+| version() |+-----------+| 8.0.30    |+-----------+1 row in set (0.00 sec)
#开启了独立表空间mysql> show variables like 'innodb_file_per_table';+-----------------------+-------+| Variable_name         | Value |+-----------------------+-------+| innodb_file_per_table | ON    |+-----------------------+-------+1 row in set (0.01 sec)
mysql> use mysql;Database changed
#user表使用 InnoDB 存储引擎mysql> show table status like 'user'\G
#查看文件，没有 mysql 表的相关数据[root@rocky86 ~]# ll /var/lib/mysql/mysql/user*ls: cannot access '/var/lib/mysql/mysql/user*': No such file or directory
[root@rocky86 ~]# ll /var/lib/mysql/mysql/total 32-rw-r----- 1 mysql mysql 5972 Jan 26 11:27 general_log_213.sdi-rw-r----- 1 mysql mysql 35 Jan 26 11:27 general_log.CSM-rw-r----- 1 mysql mysql 0 Jan 26 11:27 general_log.CSV-rw-r----- 1 mysql mysql 12505 Jan 26 11:27 slow_log_214.sdi-rw-r----- 1 mysql mysql 35 Jan 26 11:27 slow_log.CSM-rw-r----- 1 mysql mysql 0 Jan 26 11:27 slow_log.CSV
#在 mysql8.0中，mysql 数据库的文件不在数据库目录中[root@rocky86 ~]# ll /var/lib/mysql/mysql.ibd -h-rw-r----- 1 mysql mysql 27M Jan 29 18:36 /var/lib/mysql/mysql.ibd
#看其它表mysql> use wordpress;Database changedmysql> show table status like 'wp_posts'\G
#只有一个 ibd文件[root@rocky86 ~]# ll /var/lib/mysql/wordpress/wp_posts*-rw-r----- 1 mysql mysql 196608 Jan 26 13:47 /var/lib/mysql/wordpress/wp_posts.ibd

1.1.3 InnoDB 存储引擎中的 MVCC

MVCC（Multi-Version Concurrency Control）：多版本并发控制

MVCC 是一种实现并发控制的方法，一般用在数据库管理系统中，实现对数据库的并发访问。

MVCC 使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，而是把数据库的行锁与数据版本结合起来，只需要很小的开销，就可以实现非锁定读，从而大大提高了数据库系统的并发性能。

如果A进程从数据库中读取数据的同时，B进程往数据库中写入数据，则A进程有可能读到 "半写" 的数据，从而造成混乱。为了解决这个问题，我们有很多种方法，最简单的，就是加锁，让所有的读进程等待写进程处理完成后再读，但是这种方法效率会很差，写入的数据越频繁，写数据的进程越多，加锁的时间就越长，性能就越差。

MVCC并不使用加锁的方式来实现并发控制，每个连接到数据库的读进程，在某个瞬间看到的都是数据库的一个快照，在写进程没有完成提交之前，读进程的快照并不会受到该写进程的数据影响。

基于对提升并发性能的考虑，各大数据库厂商的事务型存储引擎一般都同时实现了多版本并发控制（MVCC）。不仅是MySQL，包括Oracle、PostgreSQL等其他数据库系统也都实现了。但各个数据库厂商的实现机制不尽相同。可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。

InnoDB 中的 MVCC

在MySQL中，使用 InnoDB 存储引擎的表，在创建时，系统会自动给表加上两个版本控制字段，这是两个隐藏列，这两个字段不显示在表结构中。其中一个字段保存了行的创建时间，一个保存了行的过期时间（或删除时间），（实际上存储的并不是时间，而是版本号，为了方便理解，我们认为记录的是时间）。当客户端要执行SQL语句时，会根据当前的时间与数据表中记录的时间进行比较，确保不会让客户端得到非法数据。

id	name	age	insert	delete
1	tom	10	1000	5000
2	jerry	20	2000	3500
3	alice	30	3000	4000
1	tom	11	5000

1001

#时间轴------|------|------|-----|-----|-----|------|------>     1000   2000   3000  3500  4000  5000
#查询时数据与时间点的关系#能查询到的数据 = insert 时间小于或等于当前时间 且 (delete 时间为空 或 delete 时间大于当前时间)

1.1.4 其它存储引擎

Performance_Schema：Performance_Schema 数据库使用。

Memory：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎。

MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库。

Archive：为存储和检索大量很少参考的存档或安全审核信息，只支持 SELECT 和 INSERT 操作；支持行级锁和专用缓存区。

Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境。

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换。

BLACKHOLE：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储。

example："stub"引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎。

1.1.5 管理存储引擎

查看 mysql 支持的存储引擎

show engines;

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf[mysqld]default_storage_engine=InnoDB

查看库中所有表使用的存储引擎

show table status from db_name;

查看库中指定表的存储引擎

show table status like 'tb_name';show create table tb_name;

设置表的存储引擎

CREATE TABLE tb_name(... ) ENGINE=InnoDB;ALTER TABLE tb_name ENGINE=InnoDB;

1.2 MySQL 中的系统数据

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)
MariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema |+--------------------+3 rows in set (0.000 sec)