6.8.1 存储引擎选择与对比（InnoDB/MyISAM/Memory）

存储引擎选择直接影响事务可靠性、并发性能、功能特性与运维成本。本节通过原理草图、对比要点与可执行示例，指导在 InnoDB / MyISAM / Memory 间做出合理选型。

文章图片

对比维度与要点
- 事务与一致性：InnoDB支持ACID与崩溃恢复；MyISAM、Memory不支持事务与MVCC。
- 锁机制与并发：InnoDB行级锁/间隙锁，适合高并发写；MyISAM表级锁，写入并发差；Memory表级锁，适合读多写少。
- 崩溃恢复：InnoDB有redo/undo与双写；MyISAM需手动修复；Memory重启数据丢失。
- 外键与约束：InnoDB支持外键；MyISAM/Memory不支持。
- 存储与索引：InnoDB聚簇索引；MyISAM索引与数据分离；Memory索引默认Hash或BTree。
- 空间与压缩：InnoDB支持压缩表/行格式；MyISAM支持静态压缩；Memory占用内存，容量受限。
- 复制与一致性：InnoDB在复制与崩溃场景更稳定；MyISAM在写负载下可能产生不一致风险。

安装与初始化（示例）
- 安装 MySQL 8.0（以 RHEL/CentOS 为例）

sudo yum -y install mysql-server
sudo systemctl enable --now mysqld
mysql -uroot -p -e "SELECT VERSION();"
  • 关键参数检查（确认默认引擎与内存限制）
SHOW VARIABLES LIKE 'default_storage_engine';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'tmp_table_size';

引擎选择与建表示例
- InnoDB（事务与外键）

CREATE DATABASE ops_demo;
USE ops_demo;

CREATE TABLE orders_innodb (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

START TRANSACTION;
INSERT INTO orders_innodb(user_id, amount) VALUES (1001, 99.00);
ROLLBACK; -- 事务回滚验证
  • MyISAM（读多写少、历史归档）
CREATE TABLE log_archive_myisam (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  msg VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

INSERT INTO log_archive_myisam(msg) VALUES ('archive line 1');
  • Memory（临时统计）
CREATE TABLE stat_tmp_memory (
  k VARCHAR(32) PRIMARY KEY,
  v INT
) ENGINE=MEMORY;

INSERT INTO stat_tmp_memory VALUES ('pv', 100);

命令解释与查看引擎状态
- 查看表引擎与行格式

SHOW TABLE STATUS FROM ops_demo;
  • 查看 InnoDB 引擎状态（定位锁与事务）
SHOW ENGINE INNODB STATUS\G
  • 显示锁等待
SELECT * FROM information_schema.innodb_lock_waits;

说明：SHOW ENGINE INNODB STATUS 用于排查死锁、长事务、缓冲池命中率；innodb_lock_waits 用于定位阻塞链路。

性能与容量关键配置（示例）
- 推荐在配置文件中明确默认引擎与内存表限制
文件：/etc/my.cnf

[mysqld]
default_storage_engine=InnoDB
innodb_buffer_pool_size=2G
innodb_flush_log_at_trx_commit=1
max_heap_table_size=128M
tmp_table_size=128M

预期效果：默认建表为 InnoDB；Memory 表上限受 max_heap_table_size/tmp_table_size 约束，避免内存被耗尽。

常见排错与修复
- MyISAM 索引损坏修复

# 停库后修复（推荐）
sudo systemctl stop mysqld
myisamchk -r /var/lib/mysql/ops_demo/log_archive_myisam.MYI
sudo systemctl start mysqld

说明：myisamchk -r 修复索引；需停库或至少卸载表避免数据不一致。

  • Memory 表数据丢失确认
-- 重启后检查表数据为空是预期行为
SELECT COUNT(*) FROM stat_tmp_memory;

说明：Memory 引擎数据仅存内存，重启后清空，需业务侧重建。

  • InnoDB 长事务阻塞
SELECT trx_id, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

处理：终止阻塞会话或优化事务生命周期。

选型建议（落地标准）
- 默认优先 InnoDB，除非业务明确可放弃事务与崩溃恢复。
- 读多写少且数据可重建时可考虑 MyISAM，但需评估表锁与修复成本。
- Memory 用于临时或缓存用途，必须设计数据回填与重建策略。
- 统一引擎有利于运维一致性与监控标准化，避免混用带来的复杂度。

练习与实操
1. 在同一数据库中创建 InnoDB / MyISAM / Memory 三张表，分别插入 10 万行数据，比较查询性能。
2. 通过 SHOW ENGINE INNODB STATUS 找出一次人为制造的锁等待（两个会话更新同一行）。
3. 配置 max_heap_table_size=64M，验证 Memory 表插入超过限制时的报错信息并记录。
4. 模拟 MyISAM 索引损坏（直接关机或 kill -9），使用 myisamchk -r 修复并验证。