进阶功法：Mysql存储引擎详解

🕗 发布于 2024-10-10 11:22 mysql 数据库

存储引擎

在讲解存储引擎前我们先来了解一下MYSQL的整体体系结构

MYSQL整体分为四个部分：

连接层
服务层
引擎层
存储层

在这里插入图片描述

存储引擎简介

存储引擎概念：

存储数据，建立索引，更新/查询数据等技术的实现方式

存储引擎注意点：

存储引擎是基于表的，而不是基于库的，所以不同表可以有不同的存储引擎，同时存储引擎也被称为表类型

首先我们要先直到存储引擎是在哪里定义的：

# 下面是一个表的创建语句
create table Name (
    ~~~~~~~~
)engine = InnoDB;
# 在上面的engine = 存储引擎类型 就是存储引擎的设计语句
# 我们默认情况下是InoDB存储引擎

同样我们可以通过查看创建方法来得到表的存储引擎类型：

show create table 表名;

存储引擎类型以及特点

我们可以在DG或其他数据库软件中直接查看存储引擎的类型：

# 下述代码会给出该数据库中支持的存储引擎类型
show engines;

在这里我们仅详细介绍三种存储引擎：

1. InnoDB

介绍

InnoDB 是一种兼顾可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后成为 MySQL 的默认存储引擎。

特点

事务支持：DML 操作遵循 ACID 模型，支持事务。
行级锁：提高并发访问性能。
外键约束：支持 FOREIGN KEY 约束，保证数据的完整性和正确性。
文件：
- xxx.ibd：每张表对应一个表空间文件，存储该表的表结构、数据和索引。
- 可以通过 SHOW VARIABLES LIKE 'innodb_file_per_table' 查看存储引擎分类情况。如果为 ON 表示一个表分配一个 .ibd 文件。

优点

高可靠性：支持事务处理，确保数据的一致性和完整性。
高并发性能：行级锁定机制提高了多用户并发访问时的性能。
数据完整性：支持外键约束，维护引用完整性。

缺点

磁盘空间使用较高。
内存使用较高。

2. MyISAM

介绍

MyISAM 是 MySQL 早期的默认存储引擎。

特点

不支持事务：所有更改都是永久性的。
表级锁：只支持表级锁定。
访问速度快：对于大量读取操作，性能较好。
文件：
- xxx.frm：存储表结构信息。
- xxx.MYD：存储数据。
- xxx.MYI：存储索引。

优点

读取速度快：适合读多写少的应用场景。
磁盘空间使用较低。

缺点

不支持事务。
并发性能较差：表级锁定可能导致高并发环境下的性能瓶颈。
不支持外键约束。

3. Memory

介绍

Memory 引擎的表数据是存储在内存中的，由于受到硬件问题或断电问题的影响，只能作为临时表或缓存使用。

特点

内存存放：数据存储在内存中，访问速度非常快。
Hash 索引：默认使用 Hash 索引。
文件：
- xxx.frm：存储表结构信息。

优点

访问速度快：数据存储在内存中，读写速度非常快。
适合临时表和缓存：适用于需要快速访问且不需要持久化存储的数据。

缺点

数据易失：断电或重启后数据会丢失。
存储限制：受内存大小限制，不适合存储大量数据。

三者区别

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
B+Tree 索引	支持	支持	支持
Hash 索引	-	-	支持
全文索引	支持（5.6版本）	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	-	-

InnoDB 适合需要事务支持、高并发读写、数据完整性和高可靠性的应用场景，如金融系统、电子商务平台等。
MyISAM 适合读多写少的应用场景，如日志记录、数据仓库、报表生成等。
Memory 适合需要快速访问且不需要持久化存储的数据，如临时表和缓存。

存储引擎选择

我们在各个表都可以选择不同的存储引擎，而存储引擎的选择大多遵循以下特征：

优选InnoDB：

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

优选MyISAM：

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并对事务的完整性，并发性要求不高，那么MyISAM存储引擎是比较合理的选择

优选Memory：

将所有数据保存到内存中，访问速度快，常用于临时表和缓存
Memory的缺陷是对表的大小有限制，太大的表无法存储在内存中，且无法保证安全性

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们处理，核实后本网站将在24小时内删除侵权内容。