一、核心区别概览

特性	InnoDB	MyISAM
事务支持	✅ 支持ACID	❌ 不支持
外键约束	✅ 支持	❌ 不支持
锁粒度	行锁（默认）	表锁
MVCC	✅ 支持	❌ 不支持
崩溃恢复	✅ 自动恢复（redo log）	❌ 需要修复
全文索引	✅ 支持（5.6+）	✅ 支持
主键索引	聚簇索引	非聚簇索引
数据文件	.ibd（表空间）	.MYD（数据）+ .MYI（索引）
表行数存储	不存储	存储
适用场景	事务、高并发写	只读、统计分析
默认引擎	MySQL 5.5+	MySQL 5.5之前

二、事务支持

InnoDB：完整的ACID支持

-- InnoDB支持事务
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    total_amount DECIMAL(10, 2)
) ENGINE=InnoDB;

-- 事务操作
START TRANSACTION;

INSERT INTO orders (id, user_id, total_amount) 
VALUES (1, 100, 1000.00);

UPDATE users SET balance = balance - 1000 WHERE id = 100;

-- 发生错误可以回滚
ROLLBACK;

-- 或成功提交
COMMIT;

ACID保证：

【原子性（Atomicity）】
- 通过undo log实现
- 事务要么全部成功，要么全部失败

【一致性（Consistency）】
- 通过原子性、隔离性、持久性共同保证
- 数据库从一个一致状态转换到另一个一致状态

【隔离性（Isolation）】
- 通过锁 + MVCC实现
- 支持4种隔离级别：
  - READ UNCOMMITTED
  - READ COMMITTED
  - REPEATABLE READ（默认）
  - SERIALIZABLE

【持久性（Durability）】
- 通过redo log实现
- 事务提交后，数据永久保存

MyISAM：不支持事务

CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    message TEXT
) ENGINE=MyISAM;

-- ❌ 事务无效
START TRANSACTION;
INSERT INTO logs VALUES (1, 'test1');
INSERT INTO logs VALUES (2, 'test2');
ROLLBACK;  -- 无法回滚！

-- 查询结果：两条数据都插入了
SELECT * FROM logs;
-- id=1, id=2 都存在

问题：
- 无法回滚
- 批量操作中途失败，数据不一致
- 不适合金融、订单等业务

三、锁机制

InnoDB：行锁（Row-level Locking）

-- 表结构
CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    balance DECIMAL(10, 2),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

行锁示例

-- 会话1：锁定id=1的行
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 未提交

-- 会话2：更新不同行（不阻塞）
UPDATE accounts SET balance = balance + 50 WHERE id = 2;  -- ✅ 立即执行

-- 会话3：更新同一行（阻塞）
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- ⏳ 等待锁释放

-- 会话1提交
COMMIT;
-- 会话3立即执行

行锁优势：

1. 并发性能高
   - 不同行可以并发修改
   - 适合高并发写入

2. 锁冲突少
   - 只锁定需要的行
   - 其他行不受影响

3. 适用场景
   - OLTP（在线事务处理）
   - 高并发修改
   - 订单、交易等业务

锁类型

-- 1. 记录锁（Record Lock）
-- 锁定单行记录
UPDATE accounts SET balance = 100 WHERE id = 1;

-- 2. 间隙锁（Gap Lock）
-- RR隔离级别下，锁定范围间隙，防止幻读
SELECT * FROM accounts WHERE id > 100 FOR UPDATE;
-- 锁定 (100, +∞) 的间隙

-- 3. Next-Key Lock（记录锁 + 间隙锁）
-- 默认的锁类型
SELECT * FROM accounts WHERE id >= 100 FOR UPDATE;
-- 锁定 [100, +∞) 的范围

-- 4. 意向锁（Intention Lock）
-- 表级锁，表示事务想要获取行锁
-- IS（意向共享锁）、IX（意向排他锁）

MyISAM：表锁（Table-level Locking）

CREATE TABLE logs (
    id BIGINT PRIMARY KEY,
    message TEXT
) ENGINE=MyISAM;

表锁示例

-- 会话1：更新任意一行
UPDATE logs SET message = 'updated' WHERE id = 1;
-- 锁定整张表

-- 会话2：更新其他行（阻塞）
UPDATE logs SET message = 'test' WHERE id = 999;  -- ⏳ 等待表锁释放

-- 会话3：读取（不阻塞，如果是读锁）
SELECT * FROM logs WHERE id = 500;  -- ✅ 立即执行（读锁不互斥）

表锁特点：

1. 锁粒度大
   - 一次锁定整张表
   - 并发性能差

2. 锁类型简单
   - 读锁（共享锁）：多个读不互斥
   - 写锁（排他锁）：写锁与所有锁互斥

3. 开销小
   - 锁管理简单
   - 内存开销小

4. 适用场景
   - 只读表
   - 低并发
   - 批量导入/导出

并发性能对比

-- 测试场景：100个并发更新不同的行

-- InnoDB（行锁）
CREATE TABLE test_innodb (
    id BIGINT PRIMARY KEY,
    value INT
) ENGINE=InnoDB;

-- 100个并发，更新不同的id
-- 结果：TPS = 5000+
-- 原因：行锁，互不阻塞

-- MyISAM（表锁）
CREATE TABLE test_myisam (
    id BIGINT PRIMARY KEY,
    value INT
) ENGINE=MyISAM;

-- 100个并发，更新不同的id
-- 结果：TPS = 200
-- 原因：表锁，串行执行

性能差距：25倍

四、索引结构

InnoDB：聚簇索引

【主键索引（聚簇索引）】

           Root
      [10, 30, 50]
     /     |      \
 [5,10]  [20,30]  [40,50]
   ↓       ↓        ↓
完整数据行（叶子节点）

叶子节点：
[id=5, name='张三', age=25, city='北京', ...]
[id=10, name='李四', age=30, city='上海', ...]

特点：
✅ 数据即索引
✅ 叶子节点存储完整数据行
✅ 主键查询极快（无需回表）

【二级索引】

           Root
      [李, 王, 赵]
     /     |      \
 [张,李]  [王,吴]  [赵,钱]
   ↓       ↓        ↓
(索引列值, 主键值)

叶子节点：
[name='张三', id=5]
[name='李四', id=10]

特点：
✅ 叶子节点存储主键值
⚠️ 需要回表获取完整数据

MyISAM：非聚簇索引

【主键索引和二级索引结构相同】

数据文件（users.MYD）：
Offset 0x0000: [id=1, name='张三', age=25, ...]
Offset 0x0100: [id=2, name='李四', age=30, ...]
Offset 0x0200: [id=3, name='王五', age=22, ...]

主键索引（users.MYI）：
           Root
      [2, 4, 6]
     /     |      \
  [1,2]   [3,4]   [5,6]
   ↓       ↓        ↓
(id值, 数据指针)

叶子节点：
[id=1, 指针=0x0000]
[id=2, 指针=0x0100]

二级索引（name索引）：
           Root
      [李, 王, 赵]
     /     |      \
  [张,李]  [王,吴]  [赵,钱]
   ↓       ↓        ↓
(name值, 数据指针)

叶子节点：
[name='张三', 指针=0x0000]
[name='李四', 指针=0x0100]

特点：
✅ 主键索引和二级索引结构相同
✅ 都存储数据文件的物理指针
⚠️ 数据移动时，所有索引都要更新

查询性能对比

-- 主键查询
SELECT * FROM users WHERE id = 12345;

InnoDB：
- 聚簇索引查询：2-3次IO
- 直接获取完整数据
- 耗时：0.002秒 ✅

MyISAM：
- 主键索引查询：2-3次IO
- 根据指针访问数据文件：1次IO
- 总计：3-4次IO
- 耗时：0.003秒 ✅

差距不大

-- 二级索引查询
SELECT * FROM users WHERE name = '张三';

InnoDB：
- 二级索引查询：2-3次IO
- 回表到聚簇索引：2-3次IO
- 总计：4-6次IO
- 耗时：0.005秒 ⚠️

MyISAM：
- 二级索引查询：2-3次IO
- 根据指针访问数据文件：1次IO
- 总计：3-4次IO
- 耗时：0.003秒 ✅

MyISAM二级索引略快

-- 范围查询
SELECT * FROM users WHERE id BETWEEN 1000 AND 2000;

InnoDB：
- 聚簇索引顺序扫描
- 数据物理相邻
- 顺序IO，预读高效
- 耗时：0.01秒 ✅

MyISAM：
- 索引顺序扫描
- 根据指针访问数据文件
- 数据可能分散，随机IO
- 耗时：0.05秒 ⚠️

InnoDB范围查询更快

五、崩溃恢复

InnoDB：自动恢复

【Crash Recovery机制】

1. redo log（重做日志）
   - 记录数据页的物理修改
   - 事务提交时先写redo log
   - 保证持久性

2. undo log（回滚日志）
   - 记录数据的旧版本
   - 用于事务回滚
   - 支持MVCC

3. 崩溃恢复过程

MySQL崩溃：
1. 服务器重启
2. InnoDB自动读取redo log
3. 重做已提交但未写入数据文件的事务
4. 回滚未提交的事务
5. 数据库恢复到一致状态

耗时：秒级到分钟级（取决于redo log大小）

示例：
-- 崩溃前
START TRANSACTION;
INSERT INTO orders VALUES (1, 100, 1000);
COMMIT;  ← redo log写入，但数据可能还在内存

-- 崩溃...

-- 重启后
-- InnoDB自动恢复
-- 从redo log中重做事务
-- 订单数据存在 ✅

MyISAM：需要手动修复

【崩溃后问题】

MySQL崩溃：
1. 数据文件可能损坏
2. 索引文件可能不一致
3. 表处于不可用状态

手动修复：

-- 方法1：CHECK TABLE
CHECK TABLE logs;
-- 输出：Table is marked as crashed

-- 方法2：REPAIR TABLE
REPAIR TABLE logs;
-- 尝试修复表

-- 方法3：myisamchk工具
# 停止MySQL
$ myisamchk --recover /var/lib/mysql/db/logs.MYI

-- 方法4：如果修复失败
-- 只能从备份恢复

问题：
❌ 无法保证数据完整性
❌ 可能丢失最近的数据
❌ 修复耗时长（大表可能数小时）
❌ 需要人工介入

六、MVCC支持

InnoDB：支持MVCC

【多版本并发控制】

机制：
- 每行数据有隐藏列：
  - TRX_ID：最近修改的事务ID
  - ROLL_PTR：回滚指针，指向undo log
  
- ReadView：
  - 读取时创建视图
  - 决定哪些版本可见

优势：
✅ 读不阻塞写，写不阻塞读
✅ 高并发读写性能
✅ 实现不同隔离级别

示例：
-- 会话1
START TRANSACTION;
UPDATE accounts SET balance = 1000 WHERE id = 1;
-- 未提交

-- 会话2（RC隔离级别）
SELECT balance FROM accounts WHERE id = 1;
-- 读取旧版本：balance = 900（修改前的值）
-- 无需等待锁 ✅

-- 会话1提交
COMMIT;

-- 会话2再次读取
SELECT balance FROM accounts WHERE id = 1;
-- 读取新版本：balance = 1000

MyISAM：不支持MVCC

锁机制：
- 读取时加表级共享锁
- 写入时加表级排他锁
- 读写互斥

示例：
-- 会话1
UPDATE logs SET message = 'updated' WHERE id = 1;
-- 获取表级写锁

-- 会话2
SELECT * FROM logs WHERE id = 1;
-- ⏳ 等待表锁释放（读写互斥）

问题：
❌ 读写阻塞
❌ 并发性能差
❌ 不适合高并发

七、其他重要区别

1. 外键约束

-- InnoDB：支持外键
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
) ENGINE=InnoDB;

-- 级联操作
DELETE FROM users WHERE id = 100;
-- 自动删除 orders 表中 user_id=100 的订单 ✅

-- MyISAM：不支持外键
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    FOREIGN KEY (user_id) REFERENCES users(id)  -- ❌ 语法通过，但不生效
) ENGINE=MyISAM;

-- 删除用户
DELETE FROM users WHERE id = 100;
-- orders表数据不受影响，可能产生脏数据 ⚠️

2. COUNT(*) 性能

-- MyISAM：极快
SELECT COUNT(*) FROM logs;

-- 原因：MyISAM存储了表的总行数
-- 直接返回，无需扫描
-- 耗时：0.001秒 ✅

-- InnoDB：较慢
SELECT COUNT(*) FROM orders;

-- 原因：
-- 1. MVCC机制，不同事务看到的行数不同
-- 2. 需要扫描表或索引
-- 耗时：0.1-10秒（取决于数据量） ⚠️

-- InnoDB优化
-- 1. 使用覆盖索引
SELECT COUNT(*) FROM orders;  -- 使用最小的二级索引

-- 2. 使用近似值
SELECT table_rows FROM information_schema.tables
WHERE table_name = 'orders';

-- 3. 维护计数器表
CREATE TABLE counter (
    table_name VARCHAR(50) PRIMARY KEY,
    count BIGINT
) ENGINE=InnoDB;

3. 表空间管理

-- InnoDB
-- 每个表一个文件（innodb_file_per_table=ON）
users.ibd  -- 包含数据和索引

-- 或共享表空间
ibdata1  -- 多个表共享

-- MyISAM
-- 每个表三个文件
users.frm  -- 表结构
users.MYD  -- 数据文件（MyData）
users.MYI  -- 索引文件（MyIndex）

4. 全文索引

-- MyISAM：一直支持
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content)
) ENGINE=MyISAM;

-- InnoDB：5.6+支持
CREATE TABLE articles (
    id BIGINT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content)
) ENGINE=InnoDB;  -- MySQL 5.6+

使用：
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

5. 表的行数限制

MyISAM：
- 受限于文件系统（通常2^32行）
- 约42亿行

InnoDB：
- 无明确行数限制
- 受限于表空间大小（64TB）
- 实际可达万亿行级别

八、适用场景

InnoDB适用场景

✅ 推荐使用：

1. 事务型应用
   - 订单系统
   - 支付系统
   - 金融系统

2. 高并发写入
   - 用户注册/登录
   - 实时数据更新
   - 在线事务处理（OLTP）

3. 需要外键约束
   - 数据一致性要求高
   - 级联操作

4. 需要崩溃恢复
   - 核心业务
   - 不能接受数据丢失

5. 行级锁需求
   - 高并发修改
   - 不同行同时更新

6. 读写混合
   - MVCC支持高并发读写

MyISAM适用场景

✅ 可以使用：

1. 只读或读多写少
   - 日志表（只插入）
   - 历史数据归档
   - 数据仓库

2. 不需要事务
   - 临时表
   - 缓存表

3. 全表查询频繁
   - COUNT(*)性能好
   - 统计分析

4. 表锁可接受
   - 低并发
   - 单用户

5. 全文索引（老版本MySQL）
   - MySQL 5.6之前
   - 全文搜索需求

⚠️ 注意：
- MySQL 5.5+默认InnoDB
- MyISAM已逐渐被淘汰
- 新项目不建议使用MyISAM

实际使用建议

-- ✅ 推荐：默认使用InnoDB
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

-- ⚠️ 特殊场景：日志表（只插入）
CREATE TABLE access_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(200),
    access_time DATETIME
) ENGINE=MyISAM;  -- 或考虑Archive引擎

-- ❌ 不推荐：核心业务用MyISAM
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    total_amount DECIMAL(10, 2)
) ENGINE=MyISAM;  -- ❌ 风险高

九、性能对比测试

测试1：并发INSERT

测试环境：
- 表：100万行
- 并发：64线程
- 操作：INSERT 1000条/线程

InnoDB（行锁）：
- TPS：15000
- 耗时：4.3秒
- CPU：60%

MyISAM（表锁）：
- TPS：1200
- 耗时：53秒
- CPU：25%（大量锁等待）

结论：InnoDB写入快12.5倍

测试2：并发SELECT

测试环境：
- 表：100万行
- 并发：64线程
- 操作：随机SELECT 1000条/线程

InnoDB（MVCC）：
- QPS：25000
- 耗时：2.6秒

MyISAM（表锁）：
- QPS：28000
- 耗时：2.3秒

结论：纯读场景，MyISAM略快

测试3：混合读写

测试环境：
- 表：100万行
- 并发：64线程
- 操作：80%读 + 20%写

InnoDB：
- QPS：18000
- TPS：4500
- 耗时：稳定

MyISAM：
- QPS：5000（读被写阻塞）
- TPS：1000
- 耗时：波动大

结论：混合场景，InnoDB快3.6倍

十、迁移建议

从MyISAM迁移到InnoDB

-- 1. 备份数据
mysqldump -u root -p dbname tablename > backup.sql

-- 2. 转换引擎
ALTER TABLE tablename ENGINE=InnoDB;

-- 或批量转换
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = 'dbname'
  AND engine = 'MyISAM';

-- 3. 注意事项

-- 3.1 主键
-- InnoDB必须有主键（否则自动创建隐藏主键）
ALTER TABLE tablename ADD PRIMARY KEY (id);

-- 3.2 调整配置
# my.cnf
innodb_buffer_pool_size = 8G  # 增大缓存
innodb_flush_log_at_trx_commit = 1  # 确保持久性

-- 3.3 测试COUNT(*)性能
-- 考虑维护计数器表

-- 3.4 测试并发性能
-- 验证行锁的性能提升

十一、面试要点总结

核心区别

1. 事务支持
   • InnoDB：支持ACID，redo/undo log
   • MyISAM：不支持，无法回滚
2. 锁机制
   • InnoDB：行锁，高并发
   • MyISAM：表锁，低并发
3. 索引结构
   • InnoDB：聚簇索引（主键），二级索引存主键值
   • MyISAM：非聚簇索引（都存指针）
4. MVCC
   • InnoDB：支持，读写不阻塞
   • MyISAM：不支持，读写互斥
5. 崩溃恢复
   • InnoDB：自动恢复
   • MyISAM：需要手动修复

选型建议

默认选择：InnoDB

除非：
- 只读表/日志表
- 不需要事务
- 低并发
- MySQL 5.6之前需要全文索引

现代应用：几乎全部使用InnoDB

一句话总结

InnoDB支持事务、外键、行锁和MVCC，通过redo log实现崩溃自动恢复，使用聚簇索引，适合高并发的事务型应用；MyISAM不支持事务，使用表锁，索引和数据分离，COUNT(*)快但并发性能差，已逐渐被淘汰，MySQL 5.5+默认使用InnoDB。