mysql的存储引擎
一、存储引擎的核心概念
MySQL 的存储引擎是负责数据存储和读取的底层组件，它与 MySQL 服务层解耦，采用 “插件式” 架构 —— 你可以为不同的表选择不同的存储引擎，满足不同的业务需求。
简单来说：服务层负责处理 SQL 逻辑（解析、优化、执行），存储引擎层负责具体的 “数据怎么存、怎么取”，这是 MySQL 架构的一大特色。
二、主流存储引擎详解
1. InnoDB（MySQL 5.5+ 默认引擎）
定位:
InnoDB 是 MySQL 5.5+ 版本的默认存储引擎，专为高并发、高可靠性的 OLTP（在线事务处理）场景设计，核心目标是保证事务一致性、数据可靠性和并发性能。
结构：
InnoDB 的结构可分为逻辑结构（从表到行的层级）和物理结构（磁盘文件、内存结构）两部分
这是逻辑存储结构的示意图（从大到小）
补：什么是逻辑存储结构？
逻辑存储结构是 InnoDB 为了高效管理数据，从逻辑层面（而非物理硬件层面）将数据划分的层级结构，就像我们给仓库做 “区域规划”：
仓库（表空间）→ 货架（段）→ 箱子（区）→ 盒子（页）→ 物品（行）
它不直接对应硬盘上的某一个文件 / 内存地址，而是 InnoDB 内部管理数据的 “组织方式”，目的是平衡存储效率、IO 效率和管理复杂度。
其实就是搞清楚 InnoDB 从宏观到微观是如何逻辑上划分数据存储单元的，而非具体的物理文件 / 内存布局。
什么是ibd/ibdata1
ibdata1：系统表空间（System Tablespace）文件，是 InnoDB 的 “共享文件”；
.ibd：独立表空间（File-Per-Table Tablespace）文件，是单表专属的 “私有文件”（文件名格式：表名.ibd）。
接下来是物理结构（磁盘文件、内存结构）
关联关系：
逻辑上的 “页” 是内存和磁盘交互的核心单元：磁盘上的页被加载到 Buffer Pool 中，修改后异步刷回磁盘；
逻辑上的 “行” 存储在 “页” 中，每页可存储数百行数据，行的隐藏列数据最终持久化到表空间文件；
Redo Log 记录 “页的物理修改”，而非行的逻辑修改，保证崩溃后能恢复页的状态；
Undo Log 记录行的历史版本，关联行的 DB_TRX_ID 和 DB_ROLL_PTR，支撑 MVCC。
总结：
- InnoDB 结构分两大维度：逻辑存储结构（五层抽象层级，管 “数据怎么组织”）和 物理存储结构（内存 + 磁盘，管 “数据怎么存 / 取”）；
- 核心枢纽是 “页”：既是逻辑结构的核心单元，也是物理 IO 的最小单元；
- 设计：通过内存缓存（Buffer Pool）减少磁盘 IO，通过日志（Redo/Undo）保证数据可靠性，通过层级化逻辑结构平衡管理效率和性能。
核心特性：
1.事务支持（ACID 实现）
InnoDB 是 MySQL 中唯一能完整支持 ACID 事务的主流引擎，依赖两大日志实现：
redo log（重做日志）：
作用：保证持久性（Durability），记录数据页的物理修改（而非 SQL 逻辑），崩溃后可通过 redo log 恢复未刷盘的数据；
特点：循环写的固定大小文件，先写日志再刷盘（WAL 预写日志机制），避免频繁磁盘 IO。
undo log（回滚日志）：
作用：保证原子性（Atomicity） 和隔离性（Isolation），记录数据修改前的状态，用于事务回滚和 MVCC 多版本控制；
特点：逻辑日志（记录 “把 id=1 的 name 从 A 改回 B”），会被定期清理。
事务隔离级别（InnoDB 默认 REPEATABLE READ）：
-- 查看当前隔离级别SELECT@@transaction_isolation;-- 修改隔离级别（会话级）SETSESSIONTRANSACTIONISOLATIONLEVELREADCOMMITTED;2.锁机制（行级锁 + 意向锁）
InnoDB 采用行级锁（粒度最小），仅锁定修改的行，大幅提升高并发写性能，常见锁类型：
共享锁（S 锁）：读锁，多个事务可同时加 S 锁，允许读但禁止写；
排他锁（X 锁）：写锁，仅一个事务可加 X 锁，禁止其他事务读 / 写；
意向锁（IS/IX）：表级锁，用于快速判断表中是否有行锁，避免全表扫描检查锁状态；
间隙锁（Gap Lock）：在 RR 隔离级别下，为解决幻读问题，会锁定索引间隙（如 id 1-5 之间的区间），防止插入新数据。
3. MVCC（多版本并发控制）
InnoDB 通过 MVCC 实现读不加锁（快照读），提升并发读性能：
原理：每行数据包含隐藏列（DB_TRX_ID事务 ID、DB_ROLL_PTR回滚指针），通过 undo log 构建数据的历史版本；
快照读：普通 SELECT 语句读取的是数据的快照（历史版本），无需加锁；
当前读：加锁的 SELECT（SELECT ... FOR UPDATE）、INSERT/UPDATE/DELETE 读取的是最新版本，需加锁。
补：什么是MVCC？
MVCC（Multi-Version Concurrency Control，多版本并发控制）是 InnoDB 为了解决读写冲突设计的并发控制机制 —— 它会为每行数据维护多个版本，读操作时读取数据的 “历史版本”（快照），而非最新版本，从而实现：
读不加锁：普通查询（快照读）无需加锁，不会被写操作阻塞；
写不阻塞读：修改数据时，不影响其他事务的读操作；
本质是 “用数据的多版本快照” 替代 “锁等待”，平衡并发读写性能和数据一致性。
4. 存储结构（聚簇索引）
InnoDB 采用聚簇索引（Clustered Index）结构，这是其性能特点的核心：
主键索引（聚簇索引）：索引和数据存储在同一个 B + 树中，叶子节点直接存储整行数据；
二级索引（辅助索引）：叶子节点存储的是主键值，而非整行数据，查询需先查二级索引→再查主键索引（回表）；
无主键时：InnoDB 会自动生成 6 字节的隐藏主键，因此建议为每张 InnoDB 表显式设置主键（最好是自增 INT/BIGINT）。
适用场景：
高并发读写的业务（如电商订单、用户账户、金融交易）；
需保证数据一致性、支持事务的场景；
需使用外键、行级锁的场景。
优化：
主键设计：
优先用自增整型主键（INT/BIGINT），避免 UUID（无序，导致 B + 树频繁分裂）；
主键字段不宜过长（二级索引会存储主键值，主键越长，二级索引占用空间越大）。
事务优化：
避免长事务（会占用 undo log，导致 MVCC 版本过多，锁等待时间长）；
尽量批量操作，减少事务次数（如用INSERT ... VALUES (),(),()批量插入）。
锁优化：
避免在 RR 隔离级别下的间隙锁导致的锁等待，可降级为 RC 隔离级别；
加锁查询（FOR UPDATE）尽量命中索引，避免行锁升级为表锁。
IO 优化：
开启innodb_flush_log_at_trx_commit=1（默认）保证数据安全，若允许少量数据丢失，可设为 2 提升性能；
调整innodb_buffer_pool_size（建议设为物理内存的 50%-70%），减少磁盘 IO。
2. MyISAM（MySQL 5.5 前默认引擎）
定位：
MyISAM 是 MySQL 5.5 版本前的默认存储引擎，专为读多写少**、**无需事务的场景设计，核心特点是 “简单、快速、轻量”，放弃了事务、行锁等复杂特性，换取查询性能和存储效率。
存储结构（文件分离）：
MyISAM 的每张表对应3 个物理文件（存储在数据库目录下），结构简单易管理：
| 文件后缀 | 名称 | 作用 |
|---|---|---|
| .frm | 表结构文件 | 存储表的结构定义（所有引擎都有这个文件） |
| .MYD | 数据文件 | 存储表的实际数据行 |
| .MYI | 索引文件 | 存储表的所有索引（B + 树结构，索引和数据完全分离） |
核心特性：
无事务 / 锁机制（最核心特征）
不支持事务：无法保证 ACID，INSERT/UPDATE/DELETE 都是 “即时生效”，没有回滚能力；
仅支持表级锁：修改数据（写操作）时会锁定整张表，高并发写场景下性能极差（比如一张表同时有 100 个写请求，需排队执行）；
无外键约束：无法通过引擎层面保证数据的参照完整性，需业务代码制。
性能与存储特点
查询速度快：索引和数据分离，读操作无需考虑事务 / 锁的开销，纯读场景下比 InnoDB 快；
批量插入高效：无事务日志开销，批量导入数据时速度远快于 InnoDB；
存储占用小：数据存储格式更紧凑，且支持压缩（myisampack工具），适合存储冷数据；
支持全文索引：MySQL 5.6 版本前，只有 MyISAM 支持全文索引（如MATCH AGAINST）；
无崩溃恢复能力：数据写入时若服务器崩溃，可能导致.MYD/.MYI 文件损坏，且无法自动恢复。
其他
支持表损坏修复：提供REPAIR TABLE 表名命令，可修复损坏的 MyISAM 表（InnoDB 无此命令）；
支持计数优化：COUNT(*)无需扫描数据，直接读取索引统计值（无 WHERE 条件时），速度极快；
不支持 MVCC：读操作会被写锁阻塞（表级锁导致），无法实现 “读不加锁”。
适用场景：
纯读 / 极少写的业务：如日志报表、静态数据、数据仓库、历史归档表；
需快速批量导入数据的场景：如定时同步的统计数据、离线数据导入；
老系统兼容：MySQL 5.6 前需全文索引的场景（新版本 InnoDB 已支持全文索引）；
需频繁执行 COUNT (*) 且无 WHERE 条件的场景：如统计网站总访问量、总用户数。
实操命令：
-- 1. 创建MyISAM表CREATETABLEuser_log(idINTPRIMARYKEY,log_contentVARCHAR(255),create_timeDATETIME)ENGINE=MyISAM;-- 2. 修复损坏的MyISAM表REPAIRTABLEuser_log;-- 3. 压缩MyISAM表（只读，需先停止写操作）myisampack/var/lib/mysql/test/user_log.MYD;-- 4. 查看表的存储引擎SHOWCREATETABLEuser_log;3. Memory（内存引擎）
定位：
Memory 引擎是纯内存存储的临时引擎，所有数据都存储在内存中，而非磁盘，核心特点是 “速度极快、无持久化、轻量”，专为临时数据、缓存数据等无需持久化的场景设计。
核心特性：
数据存储在内存中，读写速度极快（比 InnoDB 快 10 倍以上）；
不支持事务，仅支持表级锁；
数据重启后丢失（依赖内存，无持久化）；
支持哈希索引，查询效率极高，但不支持范围查询；
表大小受内存限制，不宜存储大量数据。
适用场景：
临时表、缓存表（如实时统计、临时计算结果）；
读写频繁、无需持久化的临时数据；
会话级的临时数据存储。
4. 其他小众存储引擎（了解即可）
| 引擎名称 | 核心特性 | 适用场景 |
|---|---|---|
| Archive | 仅支持插入、查询，压缩存储，占用空间极小 | 日志归档、冷数据存储 |
| CSV | 以 CSV 文件格式存储数据，可直接用 Excel 打开 | 数据导入导出、简单的日志记录 |
| Blackhole | 写入的数据会被丢弃，仅记录日志 | 主从复制的中继节点、数据测试 |
三、主流引擎核心对比
| 特性 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 事务支持 | ✅ | ❌ | ❌ |
| 行级锁 | ✅ | ❌（表级锁） | ❌（表级锁） |
| 外键支持 | ✅ | ❌ | ❌ |
| 崩溃恢复 | ✅ | ❌ | ❌ |
| 数据持久化 | ✅（磁盘） | ✅（磁盘） | ❌（内存） |
| 全文索引 | ✅（5.6+） | ✅ | ❌ |
| 聚簇索引 | ✅ | ❌ | ❌ |
| 高并发写性能 | 优秀 | 差 | 一般 |
四、存储引擎选型建议
- 优先选 InnoDB：90% 以上的业务场景（电商、金融、社交、企业系统）都适合，是 MySQL 官方推荐的默认引擎，兼顾事务、并发、可靠性；
- MyISAM 仅用于特殊场景：如纯读的报表、日志，或老系统兼容；
- Memory 仅用于临时数据：如缓存、临时计算，切勿存储核心业务数据；
- 特殊需求场景：归档数据用 Archive，简单数据交换用 CSV。
五、查看 / 修改存储引擎的实操命令
-- 1. 查看数据库支持的存储引擎SHOWENGINES;-- 2. 查看表的存储引擎（以user表为例）SHOWCREATETABLEuser;-- 或SELECTENGINEFROMINFORMATION_SCHEMA.TABLESWHERETABLE_NAME='user'ANDTABLE_SCHEMA='your_db_name';-- 3. 创建表时指定存储引擎CREATETABLEuser(idINTPRIMARYKEY,nameVARCHAR(20))ENGINE=InnoDB;-- 改为MyISAM/Memory即可-- 4. 修改已有表的存储引擎ALTERTABLEuserENGINE=MyISAM;总结
- 核心引擎：InnoDB 是主流（支持事务、行锁、崩溃恢复），MyISAM 仅用于纯读场景，Memory 用于临时数据；
- 选型原则：优先看是否需要事务 / 高并发，其次看是否需要持久化 / 索引特性；
- 架构特点：MySQL 存储引擎采用插件式设计，可按需为不同表选择不同引擎，灵活适配业务需求。
