mysql如何选择合适的存储引擎-mysql教程-PHP中文网

InnoDB是现代应用首选，因其支持事务、行级锁和外键，适合高并发与数据一致性要求高的场景；MyISAM适用于读多写少、对事务无要求的场景，如日志或报表；其他引擎如Memory用于内存临时表，CSV用于数据交换，Archive用于归档，Blackhole用于复制过滤。选择应基于业务需求与数据处理模式匹配。

mysql如何选择合适的存储引擎

选择MySQL存储引擎，核心在于匹配你的业务需求和数据处理模式。对绝大多数现代应用而言，InnoDB无疑是首选，它提供了强大的事务支持、数据完整性和高并发处理能力。然而，在某些特定场景下，其他引擎如MyISAM，乃至Memory和CSV，也各有其不可替代的价值。关键在于理解不同引擎的设计哲学和适用范围，避免盲目跟风。

解决方案

在我看来，选择存储引擎，就像是为不同的任务挑选合适的工具。没有哪个工具是万能的，只有最适合当前任务的。

首先，我们得把目光聚焦到两个最常用的引擎上：InnoDB和MyISAM。

InnoDB：现代应用的首选

如果你的应用需要处理大量并发请求，对数据一致性有严格要求（比如电商订单、金融交易），或者需要支持事务（要么全部成功，要么全部失败），那么InnoDB几乎是唯一的选择。它提供了：

ACID特性： 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这是数据库事务的基石。这意味着你的数据操作要么完全完成，要么完全不发生，不会出现中间状态。
行级锁： 这是InnoDB最显著的优势之一。当多个用户同时修改同一张表的不同行时，InnoDB可以只锁定被修改的行，而不是整张表。这大大提高了并发性能，减少了锁等待。我见过太多项目，因为用了表级锁而导致性能瓶颈，尤其在高并发场景下，简直是灾难。
外键支持： 确保数据的引用完整性。比如，你不能删除一个被其他表引用的用户ID，这能有效防止数据出现“孤儿”记录。
崩溃恢复能力： InnoDB通过重做日志（redo log）和撤销日志（undo log）来保证即使数据库突然崩溃，也能在重启后恢复到崩溃前的状态，保证数据不丢失。
缓冲池（Buffer Pool）： 这是InnoDB在内存中缓存数据和索引的地方，极大地提升了读写性能。

MyISAM：特定场景下的遗珠

虽然InnoDB在大多数场景下都表现出色，但MyISAM并非一无是处。它在某些读密集型、对数据一致性要求不高的场景下，仍然能发挥作用。

表级锁： 这既是它的缺点，也是在某些场景下的“优点”。在读操作远超写操作，且写入操作互不冲突的场景下，表级锁的开销反而比行级锁小，性能可能更高。
全文索引： 在MySQL 5.6版本之前，MyISAM的全文索引功能是其一大亮点。虽然现在InnoDB也支持了，但如果你还在使用老版本，或者对全文搜索的性能有极致要求，MyISAM仍值得考虑。
数据压缩： MyISAM支持数据压缩，可以节省存储空间，这对于存储大量历史数据或日志文件非常有用。
快速读取： 在纯粹的读操作上，特别是在旧硬件或低并发环境下，MyISAM可能因为其简单的结构而表现出更快的读取速度。

我个人觉得，除非你有非常明确的理由（比如遗留系统、特定分析场景），否则新项目直接选择InnoDB会省去很多后顾之忧。

InnoDB与MyISAM：何时选择，为何选择？

说实话，刚开始接触MySQL的时候，我也被这些引擎搞得一头雾水。但随着经验的积累，我发现选择它们无非是基于几个核心点：数据完整性、并发性、性能和特定功能需求。

选择InnoDB的理由：

高并发事务处理： 你的应用是电商、金融、社交网络等需要频繁进行数据修改、对数据一致性要求极高的场景。
数据完整性： 需要外键来维护数据之间的关联性，防止出现无效引用。
崩溃恢复： 无法承受数据丢失的风险，即使系统崩溃也能保证数据恢复。
热备需求： InnoDB支持在线热备份，可以在不影响业务的情况下进行备份。
大数据量、高更新频率： 行级锁能有效减少锁冲突，保证系统在高负载下的稳定性。

选择MyISAM的理由：

读多写少且对数据一致性要求不高： 例如，一些统计报表、日志记录（如果日志记录本身不需要事务保证），或者一些缓存表，写入操作很少，且对写入的原子性、持久性要求不高。
全文索引需求（旧版本MySQL）： 如果你使用的是MySQL 5.6之前的版本，且需要内置的全文索引功能。
存储空间敏感： 如果数据量巨大，且对压缩率有较高要求，MyISAM的压缩表可能是一个选择。
简单数据仓库或数据分析： 在一些简单的OLAP场景中，如果数据更新不频繁，MyISAM的表级锁可能不会成为瓶颈，反而因为其结构简单而有较好的查询性能。

我见过不少项目，明明是高并发的事务系统，却因为历史原因用了MyISAM，结果各种锁冲突，头疼不已。反之，也有一些纯粹的日志系统，为了追求极致的写入速度，选择了MyISAM，效果也不错。这就像是你买车，轿车、SUV、跑车，各有各的用处，不能一概而论。

除了InnoDB和MyISAM，还有哪些存储引擎值得关注？

当然，MySQL的存储引擎远不止这两个。在某些小众但关键的场景下，其他引擎也能大放异彩。了解它们，可以让你在解决问题时拥有更广阔的思路。

自学 PHP、MySQL和Apache

本书将PHP开发与MySQL应用相结合，分别对PHP和MySQL做了深入浅出的分析，不仅介绍PHP和MySQL的一般概念，而且对PHP和MySQL的Web应用做了较全面的阐述，并包括几个经典且实用的例子。本书是第4版，经过了全面的更新、重写和扩展，包括PHP5.3最新改进的特性（例如，更好的错误和异常处理），MySQL的存储过程和存储引擎，Ajax技术与Web2.0以及Web应用需要注意的安全

400

查看详情

Memory（或HEAP）： 顾名思义，数据存储在内存中。它的特点是速度极快，但数据易失。服务器重启后，Memory表中的数据会全部丢失。
- 适用场景： 临时表、缓存数据、会话管理、或者一些不需要持久化的中间计算结果。比如，我有时会用它来存储一些需要快速读写的临时聚合数据，计算完成后就丢弃。
CSV： 以CSV（Comma Separated Values）格式存储数据。
- 适用场景： 数据导入导出。你可以直接把CSV文件放在数据目录下，MySQL就能把它当作表来查询；反之，也可以直接把表数据导出为CSV文件。这对于和其他系统进行数据交换非常方便。
Archive： 用于存储大量不经常访问的历史数据。
- 适用场景： 归档数据。它支持高压缩，但不支持索引，只允许插入和查询操作（不允许更新和删除）。如果你有大量的历史日志或监控数据，需要长期保存但很少查询，Archive能帮你节省大量存储空间。
Blackhole： 这个引擎很特别，它像一个黑洞，所有写入的数据都会被“吞噬”，不进行任何存储。
- 适用场景： 主要用于复制架构中的主从过滤。例如，你可能想在主库上记录所有操作，但某些操作不希望同步到从库，就可以在主库上创建一个Blackhole表来接收这些操作。

这些引擎虽然不常用，但在特定问题上能提供非常优雅的解决方案。别想着一步到位，先跑起来，再根据实际情况优化，这才是常态。

如何查看和修改现有表的存储引擎？

在实际操作中，你可能会遇到需要查看或修改表存储引擎的情况。这通常是为了优化性能、解决兼容性问题，或者只是为了适应新的业务需求。

查看表的存储引擎：

要查看一张表的存储引擎，最常用的方法是使用 SHOW CREATE TABLE 语句。

SHOW CREATE TABLE your_table_name;

登录后复制

这条语句会返回创建该表的SQL语句，其中包含了 ENGINE=YourEngineName 的信息。例如：

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

登录后复制

你也可以查询 information_schema.TABLES 表来获取信息：

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

登录后复制

修改表的存储引擎：

修改表的存储引擎，可以使用 ALTER TABLE 语句。

ALTER TABLE your_table_name ENGINE = InnoDB;

登录后复制

或者：

ALTER TABLE your_table_name ENGINE = MyISAM;

登录后复制

注意事项：

数据转换： 修改存储引擎是一个“重量级”的操作。MySQL需要将表中的数据从旧引擎的格式转换成新引擎的格式。这可能需要消耗大量时间和系统资源，尤其对于大表来说，甚至可能导致长时间的表锁定，影响业务可用性。
功能兼容性：
- 从MyISAM转换为InnoDB时，如果原表没有主键，InnoDB会自动创建一个隐藏的主键。如果原表有外键，需要确保它们在InnoDB中能正确定义。
- 从InnoDB转换为MyISAM时，InnoDB的某些特性（如外键、事务）将会丢失。如果你的应用依赖这些特性，转换后可能会出现问题。
备份： 在执行任何 ALTER TABLE 操作之前，强烈建议对数据库进行完整备份。以防万一出现意外，数据可以恢复。
测试： 在生产环境执行前，务必在测试环境进行充分的测试，评估转换的时间成本和可能带来的影响。