一、引擎是表底下那一层，不是服务器的开关

先破一个直觉：一个 MySQL 服务器里不是只有一种引擎。 一个库里、甚至同一个库的不同表，都可以用不同的引擎。

所以「这台 MySQL 用什么引擎」这个问题本身就不完整。 该问的是「这张表用什么引擎」。 引擎管的事就四件：数据怎么摆、锁加多细、能不能回滚、有没有外键。 这四件全在表这一层定，定了之后上层 SQL 一个字都不用改。 这就是「引擎可插拔」的意思：换引擎不动 SQL，只动表的存法。 听着很美，代价在最后：换的时候要把数据重抄一遍。

二、查一下这台 MySQL 支持哪些

教材 5.1.2 给了三种查法，三种都该会，因为用的时候场景不同。

SHOW ENGINES;                          -- 看全部，带「支持级别」
SHOW VARIABLES LIKE 'default_storage_engine';   -- 只看默认的
SHOW TABLE STATUS LIKE 'tb_goods';      -- 只看某张表用的哪个

第一句的输出有四列值得看：Support、Comment、Transactions、XA。 Support 是 DEFAULT 的那一行就是默认引擎，8.0 里是 InnoDB。

第四种最容易踩：DISABLED 不是「不存在」，是「存在但关着」。 报错会说 engine disabled，而不是 unknown engine，这两种错要分清。 第三种查法最实用。 接手一个老库，SHOW TABLE STATUS 一路看下去，哪张表还是 MyISAM 一眼就出来。

三、InnoDB：事务、行锁、外键、崩溃恢复

InnoDB 从 5.5 起就是默认引擎，它赢在「没有短板」。 教材 5.1.3 列的四条特性，其实是同一个设计的四个侧面。

最关键的一行是「聚簇索引」这个设计：InnoDB 的表本身就是一棵按主键排序的 B+ 树。 也就是说，数据不在「表」里，在主键索引的叶子节点上。 这带来两个直接后果。 第一，按主键查是最快的，因为顺着树走到叶子就是整行。 第二，主键不要用随机值（比如 UUID），否则每插一行都在树中间撬一个位置。 很多人问「主键用自增还是 UUID」，答案就在这一层：自增是往树尾追加，UUID 是随机插入。 InnoDB 的二级索引里存的也不是行地址，是主键值——所以主键越长，所有二级索引越大。

四、MyISAM：只读快，代价是没有事务

MyISAM 是 MySQL 5.5 之前的默认引擎，现在只剩少数场景还值得用。 它的特点是「简单」：一张表三个文件，没有日志、没有事务、没有 MVCC。

三个文件对应「结构、数据、索引」三样东西，这也是它简单的根源：没有额外的事务日志。 没有日志就没有恢复能力，断电时正在写的那个页可能只写了一半。 它有一个别人比不了的优点：SELECT COUNT(*) 不用扫表。 MyISAM 在文件头存了一个精确行数，直接读出来就行。 但注意这个优点是「无条件」的：一旦带上 WHERE，还是得扫。 而且因为有表锁，这个行数在并发写的时候也不一定准。 今天的建议很短：只读的历史归档、一次写入再也不改的日志，可以考虑 MyISAM。 其余场景一律 InnoDB。

五、MEMORY：全在内存，重启就空

MEMORY 引擎的表，数据全在内存里，服务器一重启就没了，表结构还在。 教材 5.1.5 强调的两点，一点是快，一点是丢。

第二行是这一节的重点：默认哈希索引，只支持等值查询。 WHERE k = 1 走索引，WHERE k > 1 全表扫。 Demo 里把这件事量了一遍：哈希做范围查询要把 10 万条全扫，B 树只碰命中的 11 条。 所以 MEMORY 表适合当「字典」——按 key 取一个值，不适合当「区间统计表」。 还有一条容易被忽略：内存不够时，MySQL 会清空 MEMORY 表来腾地方。 所以它连「缓存」都不太算得上，更像一块「临时草稿纸」。

六、怎么选：四个问题定下来

教材 5.1.6 给了一张选择表，实际用的时候按四个问题走更快。

这张表的用法是「从上往下问，第一个「要」就定下来」。 四问都答完还没定，就是 InnoDB。 之所以这么偏，是因为 InnoDB 的短板最少。 它慢的地方（比如 COUNT(*) 要扫索引）都有替代写法，而 MyISAM 缺的事务没有替代写法。 最后给一句判断标准：如果一个方案需要你「保证不出事」，那就用 InnoDB。 MyISAM 的所有优势都建立在「不会出事」这个假设上。

七、给已有的表换引擎：这一步很贵

教材 5.1.7 讲的是设置引擎，建表时写 ENGINE 很简单，难的是改已有的表。

CREATE TABLE tb_a (id INT PRIMARY KEY) ENGINE = InnoDB;

ALTER TABLE tb_a ENGINE = MyISAM;      -- 改已有的表
SET default_storage_engine = MyISAM;   -- 只影响以后新建的表

第三句最容易误解：它只改「以后新建的表」，已经建好的表一个都不会变。 这一点和第 4 章的「改库字符集」是同一个道理。 第二句才是真改，而它是重活：MySQL 会建一张新表、把数据一行行抄过去、再换名字。 这个过程只能是 COPY，没有「原地改」的算法，期间原表基本不能写。

上面这三个档是按「每秒抄几万行」粗估的，实际取决于磁盘和行宽，只能当量级看。 还有一条硬约束：改的时候要有差不多等量的空闲磁盘，因为新旧两份同时存在。 结论就一句：引擎在建表那一刻定好。 事后要改，当成一次数据迁移来做，而不是当成一条 DDL。

八、动手 Demo：四种引擎的四个差别，一项一项真测

这一课把四个差别各造一个最小模型：事务、锁、索引结构、容量。 沙箱里没有 MySQL 服务端，也就没有「引擎」这一层，所以每一项都是等价复现，不是原样跑。 能测的都测了，测不到的（比如 InnoDB 的页开销）在正文里明说测不到。

# 一、事务：同一批插入，一个包在事务里，一个逐条提交
c = sqlite3.connect(p1, isolation_level=None)   # None = 自动提交
c.execute("BEGIN")
c.executemany("INSERT INTO tb_innodb (id, v) VALUES (?, ?)", rows)
c.execute("ROLLBACK")        # 归零

# 二、锁：四个线程各改自己那一行，表锁抢一把、行锁各拿各的
def bench(rowlock, n_thr=4, per=150, gap=0.0004):
    ...

# 三、索引：B 树与哈希，同一条范围查询
c.execute("CREATE INDEX i_k ON tb_idx (k)")
print(c.execute("EXPLAIN QUERY PLAN SELECT v FROM tb_idx WHERE k = 77777"))

# 四、容量：同一批数据，三种表的样子
print(os.path.getsize(p5))

实跑输出：

--------------------------------------------------------------------
第二十三课：四种存储引擎的差别，一项一项真测
--------------------------------------------------------------------

  **存储引擎是 MySQL 里真正干活的那一层。**
  **同一条 `CREATE TABLE`，换个引擎，数据的存法、锁法、能不能回滚全变。**
  **这一课把四个差别拆开：事务、锁粒度、索引结构、容量。**
  **四项都能在这个沙箱里造出来真测，测完你就知道「选错引擎补不回来」是什么意思。**

  一、先看 8.0 支持哪些引擎
  --------------------------------------------------------------------
  **`SHOW ENGINES` 会列出九行，但「支持」那一列只有一个是 DEFAULT。**
  **教材 5.1 节的那张表，就是这九行的摘抄。**

  引擎                支持级别  事务    锁粒度          存哪
  ------------------------------------------------------------------------
  InnoDB              DEFAULT   支持    行锁            磁盘＋缓冲池
  MyISAM              YES       不支持  表锁            磁盘
  MEMORY              YES       不支持  表锁            纯内存
  ARCHIVE             YES       不支持  行锁（只许插）  磁盘压缩
  CSV                 YES       不支持  表锁            磁盘 CSV 文本
  BLACKHOLE           YES       不支持  无              什么都不存
  MRG_MYISAM          YES       不支持  表锁            多张 MyISAM 拼一张
  PERFORMANCE_SCHEMA  YES       不支持  表锁            内存计数
  FEDERATED           NO        不支持  表锁            远端表代理
  **九个里只有 InnoDB 前四项都占：能回滚、锁行、还能崩溃恢复。**
  **其余八个是「某一项特别强、其余项付代价」的专用引擎。**
  **`FEDERATED` 那一行是 NO，意思是这个编译版本没编进去。**
  **「支持级别」不是「好不好用」，是「这个引擎在不在这份二进制里」。**

  二、事务：差别在「能不能退回去」
  --------------------------------------------------------------------
  **InnoDB 能 `BEGIN` 之后后悔，MyISAM 不能。**
  **这一项差别最贵：它决定了「转账扣了一半断电」能不能救。**
  **先演 InnoDB 那一路：`BEGIN` 里插 1000 行，插完当场看得见，`ROLLBACK` 之后归零。**

  时刻                    表里剩几行
  ----------------------------------
  BEGIN 之后插完 1000 行  1000
  ROLLBACK 之后           0
  **再演 MyISAM 那一路：不写 `BEGIN`，每一条插完就落盘，之后想回滚——**

  动作                          结果
  ------------------------------------------------------------------------
  逐条插入 1000 行（自动提交）  表里 1000 行
  再执行 ROLLBACK               cannot rollback - no transaction is active
  ROLLBACK 之后表里             1000 行，一行没退
  **两行合起来就是这一项差别的全部。**
  **有事务的库，`ROLLBACK` 是「撤销」；没事务的库，`ROLLBACK` 只是一句没人理的命令。**
  **真 MySQL 里 MyISAM 收到 `BEGIN` 不报错、收到 `ROLLBACK` 也不报错，只是什么都不做。**
  **这比报错更危险：你以为退回去了，其实没有。**

  三、锁：表锁和行锁，差在并发时谁等谁
  --------------------------------------------------------------------
  **四个人同时改同一张表的四行，表锁要排队，行锁不用。**
  **这一项差别只在并发时才看得见，单线程测不出来。**
  **四个线程各改自己那一行，每改一次占 0.4 毫秒。**
  **表锁是四个人抢同一把锁，行锁是各拿各的。**

  锁粒度          涉及的锁数  实际耗时  改动次数
  ----------------------------------------------
  表锁（MyISAM）  1 把        0.420 秒  600
  行锁（InnoDB）  4 把        0.094 秒  600
  **同一件事，同一台机器，只是锁的对象换了。**
  **表锁那一路的耗时基本等于四次串行相加，行锁那一路接近一次。**
  **这就是「秒杀时表锁直接卡死」的由来。**
  **不是因为表锁慢，是因为它把不相干的写操作也排在了一起。**

  四、索引结构：MEMORY 默认哈希，等值快、范围废
  --------------------------------------------------------------------
  **InnoDB 和 MyISAM 用 B+ 树，MEMORY 默认用哈希表。**
  **哈希表按 key 直取，等值查询一步到位；但它不排序，所以范围查询用不上。**
  **先看 B+ 树（SQLite 的索引也是 B 树，结构与 InnoDB 同源）：两条语句都走索引。**

  查询                            执行计划
  ------------------------------------------------------------------------------------
  等值 k = 77777                  SEARCH tb_idx USING INDEX i_k (k=?)
  范围 k BETWEEN 50000 AND 50010  SEARCH tb_idx USING COVERING INDEX i_k (k>? AND k<?)
  **再拿哈希表比一比。**
  **哈希等值一次定位，B 树要从根走到叶；哈希做范围只能把整个表扫一遍。**

  结构                       等值一次    范围一次    范围要碰多少条
  -----------------------------------------------------------------
  哈希（dict，模拟 MEMORY）  0.21 微秒   14.93 毫秒  10 万条全扫
  B 树（SQLite 索引）        59.31 微秒  57.54 微秒  只碰 11 条
  **等值那一列的差距里，绝大部分是 SQL 解析和游标的开销，不是索引慢。**
  **所以这一列不作为结论，只看它「同一个量级」就够了。**
  **能当结论的是第二、三列：哈希做范围必须全扫 10 万条，B 树只碰命中的那十几条。**
  **差别不在快慢，在「要不要碰那些不相干的行」。**
  **所以 `CREATE TABLE ... ENGINE = MEMORY` 建出来的表，默认只能等值查。**
  **想要范围查得显式写 `USING BTREE`——但那就回到「内存里维护一棵树」，收益打折。**

  五、容量：同数据换引擎，磁盘上差多少
  --------------------------------------------------------------------
  **最后量一件最容易被忽略的事：引擎不同，占的空间也不同。**
  **这里量的是 SQLite 文件，只能看「有索引和没索引」的差距，量不到 InnoDB 的页开销。**

  表的样子              文件大小      摊到每千行
  --------------------------------------------------
  主键加两个普通列      634880 字节   31.0 KB／千行
  再加一个二级索引      839680 字节   41.0 KB／千行
  再把 s 列填成 200 字  4538368 字节  221.6 KB／千行
  **第三行不是「加了索引变贵」，是「多了一列 200 字文本」。**
  **这一课要你记住的是第二行那种：索引本身也是一份数据，它要落盘。**
  **真 MySQL 里还要多两块开销：InnoDB 的聚簇索引本身就存整行，二级索引里存的是主键值。**
  **这两条量不到，比例也随行宽和主键长度变，所以这里不给倍数。**

第一段的结果是 1000 行、0 行。 有事务的那一路：BEGIN 里插的 1000 行，ROLLBACK 之后一行不剩。 逐条提交的那一路：ROLLBACK 直接被拒。 报错原文是 cannot rollback - no transaction is active，这就是「没有事务」的样子。 第二段的两个数是 0.422 秒和 0.093 秒，改动次数都是 600。 同样的活、同样的机器，只是锁的对象换了：一个抢一把，一个各拿各的。 这个数每次跑会有一点点浮动，但四倍左右的差距是稳定的。 第三段最有意思的是那两行执行计划。 等值那条是 USING INDEX i_k (k=?)，范围那条是 USING COVERING INDEX i_k。 COVERING 是「覆盖索引」的意思：这条查询要的列全在索引里，连表都不用回。 哈希那一行是我拿 Python 的 dict 模拟的，不是 MySQL 的 MEMORY 引擎。 它等值 0.21 微秒、范围 14.93 毫秒，差了三万多倍——差的不是速度，是「范围查询没有入口， 只能一条条看」。 第四段的三个数是 634880、839680、4538368 字节。 中间那一档是加了一个二级索引，多了 20 万字节左右。 这就是「索引本身也是一份数据」的实样，容量规划时别只算行数据。

30 秒实验

1. 把 default_storage_engine 改成 MyISAM，然后新建一张表。新表是 MyISAM，老表一个都不变。 和第 4 章「改库字符集只改以后」是同一个坑。
2. 给 Demo 里那张 tb_idx 再加一个 UNIQUE 索引，重新量一遍文件大小。 会比只有一个普通索引再涨一截，因为唯一索引要额外查重。
3. 把锁那一段的线程数从 4 改成 8，看两边的差距是变大还是变小。 表锁那一路大概翻倍，行锁那一路几乎不变——这就是「锁粒度」的意义。

常见误区

1. 「MyISAM 比 InnoDB 快，所以查询多的表用 MyISAM。」 这句在 5.5 之前是对的，现在基本不成立。 现代版本的 InnoDB 在只读场景已经不比它慢，而它缺的东西补不回来。
2. 「一个库只能用一种引擎。」 引擎是表级属性，一个库里混用完全正常。 老系统升级时经常是「新表 InnoDB、老表还是 MyISAM」，一直共存着跑。
3. 「MEMORY 表快，可以当缓存层用。」 它重启就空、内存紧张还会被清、默认只有哈希索引。 要缓存该用 Redis，要临时中间结果才用 MEMORY。
4. 「改引擎就是一句 ALTER，不影响业务。」 它是把整张表重抄一遍，期间锁表、还要双份磁盘。 大表上这一句要按「一次迁移」来排期。

5.1 到这儿结束。下一篇 5.2 讲数据类型—— 引擎决定表怎么存，类型决定每一列能装什么、装多大。

• ☐ （本篇完，下一篇 5.2 MySQL 数据类型）

相关笔记

• 创建数据库
• 数据表操作管理
• MySQL 的三种用法
• 🛢️ MySQL从入门到精通 · 索引