当前位置：首页 > article >正文

【MySQL】存储引擎

article 2024/11/6 7:23:28

MySQL采用的是可插拔的存储引擎架构，也就是说在运行期间可以动态的加载或卸载存储引擎；查看当前服务器存储引擎的方法show engines，其中重点关注两个字段即可，其一是Support-表示当前服务器是否支持，其二是它的数值yes-支持、no-不支持、default-默认

存储引擎的核心作用是对数据的处理，例如需要关心的核心问题有如何组织数据、保证数据安全、读写效率以及存储介质问题等

1. InnoDB存储引擎

1.1 InnoDB存储引擎特性

事务支持（ACID特性）：InnoDB完全支持事务的原子性、一致性、隔离性和持久性，确保数据操作的可靠性
行级锁定：采用行级锁而非表级锁，减少锁争用，提高并发性能
外键约束：支持外键，确保数据的参照完整性
自动崩溃恢复：通过重做日志（Redo Log）和回滚日志（Undo Log）实现崩溃后的自动恢复
多版本并发控制（MVCC）：实现了非阻塞的读操作，提升了并发性能
数据缓存：使用缓冲池（Buffer Pool）来缓存索引和数据，加速数据访问

1.2 InnoDB主要优势

主要优势总结

遵循ACID模型，事务具有提交、回滚和崩溃的恢复功能，从而可以保护用户数据
意外崩溃时，InnoDB的崩溃恢复功能会自动完成崩溃前的提交更改，并撤销崩溃前正在进行但是没有提交更改，从而保证数据的完整性
支持行级锁，提高了多用户的读取并发性和性能
InnoDB维护了自己缓冲池，访问数据的时候在内存缓存表和索引数据，对于经常访问到的数据可以直接从内存中拿到，从而提高效率
InnoDB优化了基于主的查询，每个InnoDB表都有一个成为聚簇索引和主键索引，可以实现通过最少的磁盘IO完成查找
支持外键约束，在插入等操作的时候保证数据的完整性
反复查询相同行的时候，自适应哈希索引会自动接管这些查询

1.3 InnoDB引擎最佳实践

最佳实践就是利用其特性从而实现其最佳的性能

表中查询频繁的列添加主键，如果没有则会自动创建一个自增的列为主键
多个表根据相同ID查询数据的时候，建议使用表连接，但是如果连接多个表的时候就需要注意该处对于内存的消耗了
事务默认是自动提交，但是当每秒都需要上百次的事务提交的时候，就需要考虑存储设备的写入速度，关闭事务的自动提交
相关的DML操作使用START TRANSACTION和COMMIT语句连在一起，分组为事务一起提交或者回滚
不使用LOCK TABLES语句

1.4 验证InnoDB是否为默认引擎

InnoDB设置为默认存储引擎的方法

配置文件进行设置

[mysqld]
default_storage_engine=INNODB #指定InnoDB为默认存储引擎

创建表的时候在语句后面加上ENGINE = InnoDB;

CREATE TABLE your_table_name (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=InnoDB;

控制台中进行设置

SET GLOBAL default_storage_engine = 'InnoDB';

验证InnoDB是否设置成为当前的存储引擎

SHOW VARIABLES LIKE 'default_storage_engine';

1.5 创建InnoDB表

2. MyISAM存储引擎

2.1 MyISAM存储引擎特性

主要特性

索引类型：支持B-tree索引，适用于多数查询要求
全文索引：内置全文搜索功能，便于处理文本数据的检索
空间数据支持：支持地理空间数据类型和索引，适合地理信息系统应用
压缩数据：通过myisampack工具，可将表压缩为只读格式，节省存储空间

主要限制

事务支持：不支持事务处理，无法保证 ACID 特性
外键约束：不支持外键，需在应用层手动维护数据完整性
锁定机制：采用表级锁定，可能在高并发写入场景下导致性能瓶颈
数据缓存：不提供数据缓存功能，需依赖操作系统的文件系统缓存

文件结构

每个MyISAM表在磁盘中都对应三个文件

表定义文件，存储在MySQL数据字典中
数据文件（.MYD）：存储表的实际数据
索引文件（.MYI）：存储表的索引信息

2.2 MyISAM存储引擎主要优势

MyISAM在读操作中可以提供比较高的读写性能，因为其使用的是表级锁定机制，适合读多写少的场景；其次支持全文索引功能，在大量文本数据的时候，可以快速的进行文本搜索。

MyISAM表的存储格式更紧凑，数据和索引是分开存储的，所以在磁盘中占用的空间一般比支持事务的引擎更少；其次MyISAM结构的简单，数据和索引单独存储在文件中，方便备份和恢复。

2.3 创建MyISAM表

2.4 MyISAM表的存储格式

主要支持三种存储格式，静态、动态和压缩格式，其中静态和动态格式会根据表中列的类型自动选择，压缩格式则需要使用myisampack工具手动创建

静态表格式

特点：每行的数据都占有固定的字节数，当表中不包含可变长度的列的时候，MyISAM引擎会自动选择该格式
优点：因为每行的长度都是固定，所以数据检索速度较快，而且在崩溃的时候更容易恢复数据
缺点：会占用大量空间

动态表格式

特点：每行的数据长度是可变，一般用于可变长度列的表
优点：节约磁盘空间，因为其只需要存储实际的数据长度
缺点：由于长度是不固定，所以很有可能导致数据碎片，最终影响性能

压缩表格式

特点：使用miisampack工具将表压缩为只读格式
优点：可以减少磁盘空间的占用，适用于存档和只读数据
缺点：压缩后的数据只可以读，不可以修改删除

2.5 动态格式表

主要特点

行头信息：每行数据前都有一个位图，指示哪些列包含空字符串或者零值（这个只针对于数值列）
存储效率：如果字符串在移除尾部空格后长度为0，那么在位图中进行标记，并且不会将该列的数据存储到磁盘中
因为行的长度是可变的，所以容易引起碎片化，最终影响查询性能

2.6 压缩格式表

压缩表主要特点总结

磁盘占用空间小，因为压缩会让数据占据磁盘空间变小；压缩后的数据只可以读；行级压缩可以减少开销，因为行头信息是根据行大小占用1-3个字节；

创建压缩表步骤

// 使用 myisampack 工具压缩表
myisampack your_table.MYI

//重建索引：压缩后，需要使用 myisamchk 工具重建索引
myisamchk -rq your_table.MYI

3. MEMORY存储引擎

存储在内存中的内容，但是服务器硬件问题或者崩溃的时候会造成数据的丢失，因为这些表只可以用作临时工作区。目前主流内存存储引擎是Redis

3.1 使用场景

临时数据存储：适用于需要快速访问的临时数据，如会话数据、缓存等
高性能需求：当读写速度比数据持久性更重要时，可以使用 MEMORY 表
小型数据集：对于小型数据集，MEMORY 存储引擎的性能优势

3.2 MEMORY存储引擎特性

因为其使用单线程，高负载场景下容易涉及到严重的锁竞争，尤其是在多个客户端并发执行更新的时候，性能不一定比得上InnoDB
所有数据都存储在内存中，读写速度极快，但是一旦服务器重启，那么数据就会丢失
支持哈希索引和B-tree索引，可以加速数据访问
提供行级锁定，支持高并发的读写操作

3.3 创建MEMORY表

CREATE TABLE users_1 (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE=MEMORY;

综合实践

-- 设置 MEMORY 表的最大大小
SET GLOBAL max_heap_table_size = 64 * 1024 * 1024; -- 设置为64MB
SET GLOBAL tmp_table_size = 64 * 1024 * 1024; -- 设置为64MB

-- 创建一个 MEMORY 表
CREATE TABLE user_sessions (
    session_id CHAR(32) NOT NULL,
    user_id INT NOT NULL,
    last_activity TIMESTAMP NOT NULL,
    PRIMARY KEY (session_id)
) ENGINE=MEMORY;

-- 插入数据
INSERT INTO user_sessions (session_id, user_id, last_activity)
VALUES ('abc123', 1, NOW()), ('def456', 2, NOW());

-- 查询数据
SELECT * FROM user_sessions;

-- 更新数据
UPDATE user_sessions
SET last_activity = NOW()
WHERE session_id = 'abc123';

-- 删除数据
DELETE FROM user_sessions
WHERE session_id = 'def456';

-- 查看 MEMORY 表的大小
SELECT table_name AS "Table", 
       round(data_length / 1024 / 1024, 2) AS "Data Size (MB)", 
       round(index_length / 1024 / 1024, 2) AS "Index Size (MB)"
FROM information_schema.tables 
WHERE table_schema = 'your_database_name' 
AND engine = 'MEMORY';

-- 删除 MEMORY 表
DROP TABLE user_sessions;

3.4 内存管理

使用的时候要密切监控内存的使用，当表数据较大的时候，会影响到计算机的性能
MEMORY表的数据是容易丢失的，不适合存储重要的数据，所以在服务器重启或者崩溃之前应该将数据持久化存储中

4. CSV存储引擎

该存储引擎允许将表数据存储为CSV格式的文本文件，便于与其他应用程序进行交互

主要特点

每个CSV表的数据都存储在一个.csv为拓展名的纯文本文件；表的元数据存储在一个以.CSM为拓展名的元文件职工
数据以该格式进行存储，可以与其他应用程序直接进行交互，例如Excel表格

4.1 CSV表

4.2 CSV表中的修复和查询

检查CSV表

通过CHECK TABLE语句可以验证CSV表的完整性，其可以检查到字符分隔符是否正确、字段是否被正确引用、字段数量是否与表的定义一致、是否存在对应的CSV元文件

修复CSV表

通过REPAIR TABLE语句修复，该命令会复制现有的CSV数据的所有有效行，然后将这些行替换原始的CSV文件

修复可能造成数据丢失：修复过程中只有从CSV文件开头到第一个损坏行之间的有效行被保留，从第一个损坏行开始到文件末尾的所有行都会被删除
修复之前最好提前备份一个数据，同时避免在写操作进行时候的中断

4.3 CSV表的限制

该存储引擎的限制

不支持索引，大量数据查询的时候效率低
缺乏事务支持，不适合高并发
没有外键支持，所以在数据完整性高的场景下不适应

5. ARCHIVE存储引擎

该存储引擎是一种专门用于存储大量索引数据的特殊引擎，主要特点就是压缩比和小存储占用特点，一般适用于存储大量历史数据、日志信息等（主要就是大量数据但是偶尔才需要查询的场景）

5.1 ARCHIVE存储引擎的特性

插入数据的时候，ARCHIVE存储引擎会对数据进行压缩，其使用zlib无损数据压缩算法，从而减少存储空间的占用
只支持查询和查询操作，不支持delete或者update操作
没有索引，所有查询都需要对全表进行扫描，只适合写多读少的应用场景
插入数据的时候，ARCHIVE存储引擎会使用行级别锁定机制，有助于提高并发插入的性能

5.2 使用场景

存储大量日志场景
存储需要长期保存但是访问频率较低的审计记录
将历史数据归档，然后释放主数据库的存储空间，用于保留数据以备将来查询

使用事例

6. BLACKHOLE存储引擎

接受数据但是不进行实际的存储，所有插入到该表的数据都会被抛弃，查询表的时候总是以空结果返回

6.1 BLACKHOLE主要特点与注意

主要特点

接受所有的写入操作，但是不存储任何数据，读取操作始终返回空集
支持各种索引类型，因为不存储数据，所以索引在该引擎中也就没有什么实际作用
提交的事务会被写入二进制日志，回滚的事务则不会

注意事项

因为该存储引擎是不存储数据的，自动递增列的数值不会自动增加，所以是有可能引发主键冲突的；INSERT触发器会正常触发，但是没有实际的存储数据。

6.2 BLACKHOLE存储引擎应用场景

性能测试，因为没有实际的存储操作，可以用于测试存储引擎的性能瓶颈
语法验证：可以验证存储文件的语法
在主服务器上设置一个默认存储引擎为 BLACKHOLE 的“虚拟”从服务器，应用所需的复制过滤规则，并将过滤后的二进制日志提供给实际的从服务器。这种设置可以减少主服务器的处理开销

代码实践

7. MERGE存储引擎

该存储引擎将多个结构相同的MyISAM表组一个逻辑表，从而方便统一查询管理，很适合大量分区表进行统一操作场景

7.1 MERGE存储引擎主要特点

参与合并的所有MyISAM表必须有相同的列定义、数据类型和索引顺序，列名和索引名可以不同，但是定义必须一致
对于MERGE表的查询会遍历所有底层表的数据，插入操作可以通过INSERT_METHOD参数指定插入到第一个或者最后一个底层表
MERGE表使用底层MyISAM表的索引，所以性能取决于这些表的索引设计

注意事项

所有参与合并的表必须有相同的结构和索引顺序，否则就会导致错误
不支持事务
插入只可以定义指定的底层表，无法插入到多个表中

7.2 MERGE实践

创建表并插入数据

创建MERGE表，将上述两个表合并在一起，并将新插入的数据添加到最后一个表

8. FEDERATED存储引擎

该存储引擎允许在不使用复制或者集群技术的情况下，访问远程MySQL数据库中的数据。通过在本地服务器上创建FEDERATED表，同时可以查询和操作远程服务器上的表数据，而无需在本地存储实际数据

8.1 主要特点

该存储引擎的表的定义是存储在本地服务器中上的，但是数据存储在远程服务器上，对于本地FEDERATED表的查询会自动从远程表中获取数据

本地服务器上仅存储表的定义文件（.frm文件），不存储实际的数据文件；其支持select、insert、update和delete操作，但是不支持事务、外键约束和索引。

正常情况的的使用场景，例如跨服务器查询，需要从多个MySQL服务器获取数据的场景下，该引擎提供了一种简便的方法；还可以实现在不同服务器的数据整合到一个统一的视图中，从而方便查询和分析

使用时注意

默认情况下MySQL是不启动该引擎的，所以需要手动启动该引擎，然后重启服务器，其次需要确保在连接字符串中使用安全的凭证，同时考虑使用SSL/TLS加密连接从而保护数据传输的安全性

8.2 具体使用

远程服务器创建表（用来存储真实数据）

CREATE TABLE test_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

本地服务器上创建FEDERATED表

CREATE TABLE federated_table (
    id INT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL DEFAULT '',
    other INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (id),
    INDEX name (name),
    INDEX other_key (other)
) ENGINE=FEDERATED
CONNECTION='mysql://username:password@remote_host:3306/database_name/test_table';