【MySQL百日打怪升级第2天】存储引擎的选择：InnoDB vs MyISAM

没有那多
0 阅读5分钟

存储引擎的选择：InnoDB vs MyISAM

大家好，我是一名拥有10年以上经验的DBA老兵（没有那多）。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第2天内容。

背景引入

💡 说白了：选错存储引擎，就像用自行车拉货——不是不行，是费劲。

很多同学建表时不指定存储引擎，默认就是 InnoDB。但你知道吗？在 MySQL 5.5 之前，默认是 MyISAM。

今天的目标：搞清楚 InnoDB 和 MyISAM 的区别，面试必问！

核心概念

什么是存储引擎？

MySQL 的特点是插件式存储引擎，数据怎么存、怎么读，完全由存储引擎决定。

-- 查看MySQL支持的存储引擎及执行结果
> SHOW ENGINES;
+------------------+---------+-------------------------------------------------------------------+--------------+------+------------+
| Engine           | Support | Comment                                                           | Transactions | XA   | Savepoints |
+------------------+---------+-------------------------------------------------------------------+--------------+------+------------+
| MEMORY           | YES     | Hash based, stored in memory, useful for temporary tables        | NO           | NO   | NO         |
| InnoDB           | DEFAULT | Supports transactions, row-level locking, and foreign keys       | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES   | Performance Schema                                               | NO           | NO   | NO         |
| MyISAM           | YES     | MyISAM storage engine                                            | NO           | NO   | NO         |
| MRG_MYISAM       | YES     | Collection of identical MyISAM tables                            | NO           | NO   | NO         |
| BLACKHOLE        | YES     | /dev/null storage engine (anything you write to it disappears)   | NO           | NO   | NO         |
| CSV              | YES     | CSV storage engine                                               | NO           | NO   | NO         |
| ARCHIVE          | YES     | Archive storage engine                                           | NO           | NO   | NO         |
+------------------+---------+-------------------------------------------------------------------+--------------+------+------------+

💡 说白了：存储引擎就是"仓库管理员"， InnoDB 是现在的标配，曾经的 MyISAM 是老江湖。

InnoDB vs MyISAM 核心区别

特性InnoDBMyISAM
事务支持✅ 支持 ACID❌ 不支持
锁粒度行锁（Row Lock）表锁（Table Lock）
外键✅ 支持❌ 不支持
崩溃恢复✅ 自动恢复❌ 需手动修复
存储方式聚簇索引非聚簇索引
COUNT(*)需要遍历索引直接读取行数
适用场景业务数据、事务需求日志、只读数据

面试必问：

  • InnoDB 和 MyISAM 有什么区别？
  • 为什么现在都用 InnoDB？
  • MyISAM 还有人用吗？什么时候用？

📝 面试解答：

Q: InnoDB 和 MyISAM 有什么区别？

最核心的区别：InnoDB 支持事务，MyISAM 不支持。

  • InnoDB 是行锁，高并发下性能好；MyISAM 是表锁，写操作会锁整张表
  • InnoDB 支持外键，MyISAM 不支持
  • InnoDB 有崩溃自动恢复，MyISAM 坏了可能需要手动修复
  • InnoDB 主键索引就是数据，MyISAM 主键索引是指向数据的指针

Q: 为什么现在都用 InnoDB？

因为绝大多数业务都需要事务支持。转账、订单、库存...没有事务，数据分分钟错乱。

而且 InnoDB 的行锁在并发场景下性能碾压 MyISAM 的表锁。

Q: MyISAM 还有人用吗？什么时候用？

极少数场景：

  • 只读/历史数据：不需要事务，写入一次就不变了
  • 日志表：写入频繁，但不需要事务
  • ** COUNT() 频繁**：MyISAM 整表行数存内存里，COUNT() 特别快（但 InnoDB 8.0 也优化了）
  • 空间紧张：MyISAM 索引更小

如何选择？

-- 查看表的存储引擎
SHOW TABLE STATUS FROM database_name LIKE 'table_name';

-- 创建表时指定存储引擎
CREATE TABLE t1 (...) ENGINE=InnoDB;
CREATE TABLE t2 (...) ENGINE=MyISAM;

💡 无脑选 InnoDB，除非有特殊理由。

💡 AI辅助选择：让AI根据你的业务场景推荐存储引擎，直接问： "我的业务是XXX，需要事务/高并发吗？选InnoDB还是MyISAM？"

💡 个人Skill建议：在你的Skill里加一个建表规范检查，检查项包括：

  • 存储引擎是否指定为InnoDB
  • 主键是否自增
  • 字符集是否为utf8mb4
  • 是否有create_time/update_time时间字段
业务场景推荐引擎原因
订单/交易/用户InnoDB必须事务，行锁高并发
日志/审计表InnoDB8.0后性能足够，可靠性更重要
只读报表/历史数据MyISAM仅追加写无并发读写要求，定期归档
临时缓存表MEMORY内存存储，速度最快

实战案例

场景一：事务回滚

InnoDB 支持事务，可以回滚：

-- 事务测试及执行结果
BEGIN;
INSERT INTO user (name) VALUES ('Tom');
ROLLBACK;
-- 数据被回滚了

MyISAM 不支持事务，ROLLBACK 对它无效：

-- InnoDB 表：事务可以回滚
BEGIN;
INSERT INTO t_innodb (name) VALUES ('Tom');
ROLLBACK;
SELECT COUNT(*) FROM t_innodb;  -- 结果：0（数据被回滚了）

-- MyISAM 表：ROLLBACK 对 MyISAM 无效，数据还在
BEGIN;
INSERT INTO t_myisam (name) VALUES ('Tom');
ROLLBACK;
SELECT COUNT(*) FROM t_myisam;  -- 结果：1（MyISAM 不支持事务，ROLLBACK 无效）

场景二：并发更新

InnoDB 行锁，两个事务可以同时更新不同行：

-- 行锁测试及执行结果
-- 事务1一个连接：UPDATE user SET name='A' WHERE id=1;
-- 事务2另开一个连接：UPDATE user SET name='B' WHERE id=2;  -- 同时执行成功

MyISAM 表锁，写操作会锁整张表：

-- InnoDB 行锁：两个连接同时更新不同行，互不阻塞
-- 连接1：
BEGIN;
UPDATE user SET name='A' WHERE id=1;  -- 只锁 id=1 这一行
-- 连接2（同时执行）：
UPDATE user SET name='B' WHERE id=2;  -- ✅ 不阻塞，更新的不是同一行

-- MyISAM 表锁：用 LOCK TABLES 显式持锁才能演示阻塞
-- 连接1：
LOCK TABLES user WRITE;
UPDATE user SET name='A' WHERE id=1;  -- 锁整张表
-- 连接2（同时执行）：
UPDATE user SET name='B' WHERE id=2;  -- ❌ 被阻塞，必须等连接1释放锁
-- 连接1：
UNLOCK TABLES;  -- 释放锁后，连接2才能执行

避坑指南

⚠️ 真实踩过的坑：

  1. 建表不指定 ENGINE，默认可能是 MyISAM

    • MySQL 5.5 之前默认 MyISAM，5.5 之后默认 InnoDB
    • 有一些公司规范禁止指定存储引擎，由DBA统一把控

  2. MyISAM 崩溃数据丢失

    • MyISAM 没有崩溃恢复，异常关机可能损坏
    • 重要业务数据千万别用 MyISAM

  3. COUNT(*) 慢？

    • MyISAM 确实快，但 InnoDB 8.0 优化后也不差
    • 别为了 COUNT(*) 快选 MyISAM，不值得
  • InnoDB 8.0 优化后，COUNT(*) 会选最小的二级索引遍历，不再全表扫描

思考题

🤔 互动时间：

  1. 如果某个表不需要事务，就是存日志，选什么引擎？为什么？
  2. 为什么阿里规约要求「表必须使用 InnoDB 存储引擎」？

总结

🎯 面试考点

  • InnoDB vs MyISAM 核心区别：事务、行锁、崩溃恢复
  • InnoDB 支持事务、Row Lock、外键
  • MyISAM 只支持表锁，无事务，适用于只读场景
  • 无脑选 InnoDB，除非有特殊理由

💡 AI实战建议：让自己的AI助手记住建表规范检查清单，每次写CREATE TABLE时自动检查

下期预告：InnoDB存储引擎核心结构 —— 深入底层

全本合集：《每天一个MySQL知识点，百日打怪升级》

有问题欢迎评论区交流，明天见！

avatar
文章
阅读
粉丝