【MySQL】MySQL如何存储数据？

MySQL 的数据存储机制依赖于其存储引擎（Storage Engine），不同的存储引擎有不同的存储方式。

1.InnoDB存储引擎

InnoDB 是MySQL的默认存储引擎，它提供了高可靠性和高性能，同时由以下主要优点：

（1）事务安全（符合ACID） 

ACID 特性：

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务执行前后，数据库必须保持一致状态。
• 隔离性（Isolation）：多个事务并发执行时，彼此之间互不干扰。InnoDB 支持多种隔离级别（如读未提交、读已提交、可重复读、串行化）。
• 持久性（Durability）：事务提交后，对数据的修改是永久性的，即使系统崩溃也不会丢失。InnoDB 通过 Redo Log 和 Undo Log 实现持久性。

（2）MVCC (多版本并发控制)

InnoDB 行级锁定：InnoDB 支持行级锁，允许多个事务同时读写不同的行，减少锁争用。

Oracle 风格的一致的非锁定读取：读操作不会阻塞写操作，写操作也不会阻塞读操作。读操作可以访问事务开始时的数据快照，而不会被未提交的写操作影响。

（3）布局表数据以优化基于主键的查询

InnoDB 使用 聚簇索引（Clustered Index） 来组织表数据：

• 聚簇索引：表数据按主键顺序存储，主键索引就是聚簇索引。如果没有定义主键，InnoDB 会选择一个唯一非空索引作为聚簇索引；如果也没有，则会隐式创建一个主键。
• 优化主键查询：基于主键的查询非常高效，因为数据按主键顺序存储，可以直接定位到目标行。

（4）支持外键引用完整性约束

InnoDB 支持 外键（Foreign Key），用于维护表与表之间的引用完整性：

• 外键约束：确保子表中的外键值必须在父表的主键或唯一键中存在。例如，订单表中的 customer_id 必须是一个有效的客户表中的 id。
• 级联操作：支持 ON DELETE CASCADE 和 ON UPDATE CASCADE，自动处理父表和子表之间的数据更新和删除。

（5）在大型数据卷上实现最高性能

InnoDB 针对大型数据卷进行了优化，能够处理高并发和大数据量的场景：

• 高效的索引结构：使用 B+ 树索引，支持高效的范围查询和点查询。
• 缓冲池（Buffer Pool）：将热点数据和索引缓存到内存中，减少磁盘 I/O。
• 多线程支持：支持多线程并发处理，充分利用多核 CPU 的性能。

（6）损坏后的快速自动恢复

InnoDB 提供了强大的崩溃恢复机制，确保数据的一致性和持久性：

• Redo Log（重做日志）：记录事务的修改操作，用于崩溃后重做已提交事务的修改。
• Undo Log（回滚日志）：记录事务的旧值，用于回滚未提交的事务。
• 自动恢复：在数据库启动时，InnoDB 会自动检查日志并恢复数据到一致状态。

（7）使用缓冲池在内存中缓存数据和索引

InnoDB 使用 缓冲池（Buffer Pool） 来缓存数据和索引，以提高性能。

缓冲池的作用：缓存表和索引数据，减少磁盘 I/O。读操作优先从缓冲池中获取数据，如果未命中，则从磁盘加载。写操作先写入缓冲池，再通过 Checkpoint 机制刷新到磁盘。

2.MyISAM存储引擎

MyISAM 是 MySQL 5.5 之前的默认存储引擎，它的特点有：

（1）快速简单

• MyISAM 的设计简单，适合读多写少的场景。
• 由于不支持事务和行级锁，MyISAM 的读写操作非常快。

（2）表损坏问题

• MyISAM 不支持事务和崩溃恢复机制，因此在服务器崩溃时可能导致表损坏。
• 表损坏后，可以使用 REPAIR TABLE 命令修复表。

（3）支持 FULLTEXT 索引

• MyISAM 支持 全文索引（FULLTEXT），适合文本搜索场景。
• 全文索引支持关键词搜索、模糊匹配等功能。

（4）支持空间数据类型和索引

• MyISAM 支持 空间数据类型（如点、线、面）和 空间索引（如 R 树）。
• 适合地理信息系统（GIS）等场景。

（5）支持表级锁

• MyISAM 只支持 表级锁，即在对表进行写操作时，会锁定整个表。
• 表级锁的并发性能较差，适合读多写少的场景。

（6）文件格式简单

• MyISAM 的存储文件格式简单，每个表对应三个文件：.frm：表结构定义。.MYD：表数据。.MYI：表索引。
• 由于文件格式简单，MyISAM 支持原始表级别的备份和恢复。

3.MEMORY存储引擎

Memory 存储引擎将数据存储在内存中，因此具有非常高的读写速度，但数据在服务器重启后会丢失。它的特点：

• 将行数据和索引存储在内存中。数据在服务器重新启动后不可用。
• 以固定长度格式存储行。
• 使用 --max-heap-table-size 选项限制表大小：该选项以旧的存储引擎名称HEAP 命名。
• 支持表级锁定，表级锁的并发性能较差，适合读多写少的场景。
• 无法存储 TEXT（用于存储大文本数据（字符串））或 BLOB （用于存储大二进制数据） 列，因为这些数据类型是可变长度的。

4.ARCHIVE存储引擎

ARCHIVE 存储引擎用于以压缩格式存储大量数据，从而实现非常小的占用空间。它具有以下主要特点：

• 不支持索引，在存储大量数据时，它的占用空间较小，但这也意味着查询速度较慢，适用于对数据检索不频繁的场景。
• 支持 INSERT 和 SELECT，但不支持 DELETE、REPLACE 或 UPDATE。它的设计理念是将数据写入后就不再修改，适用于日志存储、历史数据存档等场景。
• 支持 ORDER BY 操作和 BLOB 列
• 接受除空间数据类型之外的所有数据类型
• 使用行级锁定，可以同时处理多个插入操作，但因为不支持更新和删除操作，行级锁的作用主要体现在并发插入数据时的效率。
• 支持 AUTO_INCREMENT 列，允许自动生成唯一的ID来标识每一行数据。这对于存储和管理大量数据非常有用。

5.BLACKHOLE存储引擎

• 充当空存储引擎，它接受数据但不存储它，检索总是返回空结果
• 支持各种索引：虽然数据本身不被存储，但索引的处理仍然可以用于提高查询和日志转发的效率。
• 所有提交的事务会被写入二进制日志中，用于日志转发和复制。回滚的事务不会写入日志，这是为了减少不必要的日志开销。
• 验证备份和转储文件语法
• 测试和分析系统的性能，帮助识别与存储引擎无关的性能瓶颈，如磁盘I/O、网络延迟等。

6.MySQL如何使用磁盘空间？

MySQL 的磁盘空间主要用于以下几个方面：

(1)数据目录：磁盘空间的主要用途是存放数据目录。数据目录存储所有数据库、表数据、索引文件等。

• MySQL 的数据目录包括数据库文件（即所有数据库的子目录）、日志文件、配置文件、缓存等。数据目录的位置可以通过配置文件中的 datadir 参数进行配置。默认情况下，在 Linux 系统中，MySQL 数据目录的位置是 /var/lib/mysql。
• 每个数据库都有一个与之对应的子目录，在这个目录下存储该数据库的所有数据文件、索引文件、日志文件等。即使是 mysql 系统数据库，也会有一个对应的子目录，用于存储该数据库的系统表和相关数据。

(2)日志文件：用于故障排除、监控、复制和恢复等操作。主要包括：错误日志（Error Log）、查询日志（General Query Log）、慢查询日志（Slow Query Log）、二进制日志（Binary Log）、中继日志（Relay Log）（在复制中使用）、事务日志（InnoDB Transaction Log）。

(3)存储引擎特定文件：如 InnoDB 的 .ibd 文件(包含表数据和索引)和.sdi文件（存储与表、列、索引等数据库元数据相关的数据字典信息）、MyISAM 的 .MYD 和 .MYI 文件、ARCHIVE 的压缩文件等。