一篇文章让你理解MySQL引擎

引擎

MySQL的存储引擎是数据库管理系统用于存储、处理和保护数据的核心组件，它们决定了表的存储结构、索引方式、锁定级别以及事务支持等特性。MySQL支持多种存储引擎，每种存储引擎都有其特定的使用场景和优缺点。以下是对MySQL中几种常见存储引擎的分类和区别的详细介绍：

一、常见存储引擎分类

1. InnoDB

• • 特点：InnoDB是MySQL的默认存储引擎（从MySQL 5.5版本开始），支持事务处理、行级锁定和外键约束，日志具有提交、回滚和崩溃恢复能力。
  • 适用场景：适用于需要高并发、事务处理、数据完整性和一致性保证的应用场景。

1. MyISAM

• • 特点：MyISAM是MySQL早期版本的默认存储引擎，不支持事务处理和行级锁定，但读取速度较快。
  • 适用场景：适用于读密集型应用，如Web应用中的大量数据读取操作。

1. Memory（HEAP）

• • 特点：将数据存储在内存中，访问速度非常快，但服务器重启后数据会丢失。
  • 适用场景：适用于临时数据的存储，如缓存、会话数据等。

区别对照：

存储引擎	事务支持	锁定级别	外键约束	崩溃恢复	索引类型	读取速度	写入速度
InnoDB	支持	行级锁	支持	支持	B+树	中等	中等
MyISAM	不支持	表级锁	不支持	不支持	B+树	快	快
Memory	不支持	表级锁	不支持	不支持	HASH/B+树	非常快	非常快

二、外键约束：

外键约束（Foreign Key Constraint）是数据库中的一种约束，用于维护两个表之间的关系。通过外键约束，可以确保一个表中的数据（外键字段）必须匹配另一个表中的某个值（通常是主键或唯一键），从而保持数据的参照完整性。

外键约束的作用：

1. 保持数据的一致性：外键约束可以防止在关联表中插入不符合逻辑的数据，比如，你不能在员工表中插入一个不存在的部门ID作为员工所属的部门。
2. 维护数据的完整性：外键约束保证了数据的完整性，因为当一个表中的数据被另一个表引用时，你不能随意删除或修改被引用的数据，除非你先删除了所有引用它的数据或更新了引用它的数据。
3. 实现表之间的关联：外键约束使得两个或多个表之间可以建立明确的关联关系，这有助于查询优化和数据分析。

外键约束的基本规则：

• 外键列必须是一个有效的列，且其数据类型必须与主键列或唯一键列的数据类型相匹配。
• 外键列的值必须引用主键列或唯一键列的值。
• 如果外键列的值是NULL，则表示该字段不引用任何主键或唯一键的值。
• 如果外键列的值不是NULL，则该值必须在关联表的主键或唯一键列中存在。

InnoDB 和 myisam 的区别

InnoDB的数据结构

1. 存储方式：

• • InnoDB使用一个或多个数据文件（表空间）来存储数据和索引。每个数据文件可以自动扩展或固定大小，默认扩展名为.ibd。
  • 每个InnoDB表都存储在其自己的.ibd文件中，除非启用了共享表空间。

1. 索引结构：

• • InnoDB使用B+树数据结构来存储表和索引。B+树是一种平衡树，可以快速搜索、插入和删除数据。
  • 在B+树中，所有数据都存储在叶子节点上，而非叶子节点仅存储索引信息。
  • InnoDB表包含一个称为聚簇索引的索引，该索引定义了表中数据的物理顺序。聚簇索引通常是主键索引，如果没有定义主键，则InnoDB会选择唯一索引作为聚簇索引，如果表中没有唯一索引，则InnoDB会创建一个隐藏的主键列作为聚簇索引。主键索引--唯一索引--隐藏的主键列
  • 除了聚簇索引外，InnoDB表还可以包含多个非聚簇索引。非聚簇索引也是B+树结构，用于提高查询效率。非聚簇索引存储记录的键值及其对应的聚簇索引键值，本身+主键 id。

1. 行格式：

• • InnoDB支持两种行格式：Compact和Redundant。Compact是默认行格式，它将NULL值和固定长度的数据类型存储为二进制表示--char，对于可变长度的数据类型，仅存储实际使用的字节数---varchar。
  • 数据和索引组织成大小为16KB的页面--数据页，每个页面都包含一个页头和页体，页头包含页面的元数据信息，页体包含数据和索引信息。

1. 多版本并发控制（MVCC）：

• • InnoDB使用MVCC来管理事务的并发访问，允许多个事务同时访问同一行而不会互相干扰。--ReadView + undo log

MyISAM的数据结构

1. 存储方式：

• • MyISAM在磁盘上存储了三个文件：表的定义文件(.frm)、数据文件(.MYD)和索引文件(.MYI)。
  • 数据文件和索引文件是分离的，索引文件仅保存数据记录的地址。

1. 索引结构：

• • MyISAM使用B+树作为索引结构，但与InnoDB不同，MyISAM的索引文件和数据文件是分开的。
  • 索引的叶子节点data域存放的是数据记录的地址，而不是数据本身。
  • MyISAM的索引方式都是“非聚簇”的，即索引顺序与物理存储顺序不同。

1. 查询性能：

• • 由于索引文件和数据文件分离，MyISAM在执行大量SELECT查询时可能具有较好的性能，因为它支持不加锁读取。
  • 但是，在执行修改数据操作（如UPDATE、DELETE）时，性能可能不如InnoDB，因为MyISAM只支持表锁。

总结

• InnoDB：数据文件和索引文件结合在一起，使用聚簇索引和非聚簇索引，支持事务、行锁和MVCC，适合需要高并发写入和事务支持的应用场景。
• MyISAM：数据文件和索引文件分离，索引文件仅保存数据记录的地址，支持非聚簇索引，不支持事务和行锁，适合读多写少、不需要事务支持的应用场景。

1. 聚簇索引和非聚簇索引

聚簇索引即主键索引，如果没有主键 id，MySQL 会默认隐藏生成一个主键索引，索引结构和数据一起放。更新代价大

非聚簇索引，索引结构和数据分开放，依赖有序数据，更新代价小，可能回表。