MySQL 中常用的存储引擎
1、MySQL中的存储引擎
MySQL支持多种数据库引擎,每种引擎都有其特定的优势和适用场景。
1.1 InnoDB:
这是MySQL的默认数据库引擎。它提供了事务安全(ACID兼容)的表,支持行级锁定和外键约束。InnoDB还具有崩溃恢复能力,对于需要高并发读写和事务支持的应用场景非常适用。
- 默认存储引擎,支持事务处理(ACID特性),具备崩溃恢复能力。
- 支持行级锁定,提高并发性能,尤其是在多用户同时更新同一表的不同行时。
- 支持外键约束,确保数据引用完整性。
- 采用聚簇索引,数据行和相邻的键值索引存储在一起,优化了点查询和范围查询的性能。
- 从MySQL 5.6.4开始支持全文索引。
1.2 MyISAM
这是MySQL的传统数据库引擎。它提供了较快的查询性能,特别是对于只读或大量读取的应用。然而,MyISAM不支持事务处理和外键约束,并且在高并发写入时可能表现不佳。
- 在MySQL早期版本中是默认存储引擎,但后来被InnoDB取代。
- 不支持事务和行级锁定,仅支持表级锁定,因此在高并发写入场景下性能较低。
- 支持全文索引,适合全文搜索的应用场景。
- 数据和索引分开存储在
.MYD
和.MYI
文件中,适用于读取密集型应用。- 不支持外键约束,不提供崩溃恢复功能。
1.3 Memory (HEAP)
这是一种内存引擎,它将数据存储在内存中,因此提供了非常快的访问速度。但是,由于数据存储在内存中,因此数据容量受到内存大小的限制,并且当数据库服务器重启时,数据会丢失。这种引擎适用于缓存、会话管理等轻量级应用。
- 数据全部存储在内存中,适合临时表或经常需要进行全表扫描的小型表。
- 因为数据存储在内存中,所以重启服务后数据会丢失,适用于短期存储或计算密集型任务。
1.4 NDB Cluster(或Cluster/NDB)
这是MySQL的簇式数据库引擎,专为高性能查找和高可用性而设计。它可以将数据分布在多台服务器上,提供高可扩展性和高并发性能,适用于大规模分布式系统。
- 分布式存储引擎,专为高可用性和大规模并行处理而设计。
- 数据分布在多台机器上,提供高可用性和可扩展性。
- 特别适合大数据量和高并发的在线事务处理和分析(OLTP/OLAP)场景。
1.5 CSV
这是一种文本文件引擎,可以将数据存储在CSV格式的文本文件中。它适用于需要与其他系统进行数据交换的场景,但同样不支持事务处理和外键约束。
1.6 BLACKHOLE
这是一种虚拟引擎,它不会存储任何数据。任何对这种引擎的写操作都会被忽略,而任何读操作都会返回空结果集。这种引擎通常用于临时禁止对数据库的应用程序输入。
1.7 FEDERATED
这是一种远程引擎,它允许连接到远程MySQL服务器并执行查询。
1.8 ARCHIVE
这是一种用于存储和检索大量归档数据的引擎。
- 专门用于大容量、只追加、很少更新和查询的场景,例如日志记录。
- 不支持索引,只支持INSERT和SELECT操作。
1.9 Merge
这是一种将多个MyISAM表组合为一个表的引擎。
- 用于合并多个MyISAM表作为一个逻辑表来处理,常用于数据分析和数据仓库场景。
注意:
在选择数据库引擎时,需要根据具体的业务需求和特点进行考虑。例如,如果应用需要事务支持和高并发读写,那么InnoDB可能是更好的选择;而如果应用主要是读取操作且对性能有较高要求,那么MyISAM可能更合适。同时,还需要考虑数据的持久性、备份和恢复策略等因素。
2、MySQL中InnoDB存储引擎
MySQL中的InnoDB存储引擎是一个高度可靠的事务型存储引擎,它的设计目标是满足高并发事务处理和数据安全的需求。
以下是InnoDB存储引擎的主要特点:
事务支持:
- InnoDB完全支持ACID(原子性、一致性、隔离性、持久性)事务特性,保证了数据的完整性和一致性。
- 提供了事务管理机制,允许用户执行BEGIN, COMMIT, ROLLBACK等事务操作。
行级锁定:
- InnoDB使用行级锁定,大大提高了多用户并发环境下的性能,尤其在频繁修改不同记录的情形下,可以减少锁冲突,提升并发读写的效率。
外键约束:
- 支持外键约束(FOREIGN KEY),确保了数据库表间的数据引用完整性。
MVCC(多版本并发控制):
- 实现了多版本并发控制机制,从而在多个事务同时读取相同数据时,减少了锁竞争,提升了并发性能,同时也支持“可重复读”(Repeatable Read)事务隔离级别,有效避免了幻读现象。
崩溃恢复:
- 内置崩溃恢复机制,即使在数据库宕机或系统崩溃后,也能通过redo日志(重做日志)和undo日志(回滚日志)来恢复未完成的事务和回滚已提交的事务。
缓冲池:
- 使用缓冲池技术,将频繁读写的页缓存在内存中,减少磁盘I/O操作,提高整体性能。
其他高级特性:
- 插入缓冲(Insert Buffer),优化了非唯一二级索引的插入操作;
- 两次写(Double Write),用于防止因电源故障等原因导致的数据页损坏;
- 自适应哈希索引(Adaptive Hash Index),动态创建和维护哈希索引以加速查询;
- 预读(Read Ahead),预先读取连续的数据页到内存,减少未来查询时的磁盘I/O。
InnoDB存储引擎非常适合那些需要事务处理、高并发读写操作、数据完整性和高性能的应用场景,这也是它成为MySQL默认存储引擎的原因之一。
2、MySQL中MyISAM存储引擎
MyISAM是MySQL中常用的存储引擎之一,尤其在MySQL 5.5版本之前的版本中,它是默认的存储引擎。
MyISAM存储引擎具有一些独特的特点和适用场景。
特点:
- 非事务性:MyISAM不支持事务处理,这意味着它不能执行跨多个查询的事务。每个查询都是原子性的,但无法回滚。
- 表级锁定:MyISAM使用表级锁定,当一个线程获得写锁定时,其他线程对表的读取和写入都会被阻塞。这可能导致较低的并发性能,特别是在写密集的应用中。
- 全文索引:MyISAM支持全文索引,这对于需要进行文本搜索的应用非常有用。
- 访问速度快:对于只读或大量读取的应用,MyISAM通常提供较快的查询性能。
文件结构:
MyISAM存储引擎的表在磁盘上存储为三个文件:
.frm
文件:存储表的定义结构。.MYD
文件:存储表的数据(MYData)。.MYI
文件:存储表的索引(MYIndex)。这种文件结构使得数据文件和索引文件可以放置在不同的目录,从而平均分布I/O负载,提高查询速度。
适用场景:
MyISAM适用于以下场景:
- 只读或大量读取的应用:对于主要进行读取操作的应用,MyISAM通常能提供较好的性能。
- 全文搜索:如果应用需要进行全文搜索,MyISAM是一个很好的选择,因为它支持全文索引。
- 不需要事务支持:如果应用不需要事务处理或复杂的并发控制,MyISAM可以是一个简单而高效的解决方案。
需要注意的是:由于MyISAM不支持事务和行级锁定,它在高并发写入或需要保证数据一致性的场景中可能不是最佳选择。在这些情况下,InnoDB存储引擎可能更合适。
3、MySQL中InnoDB和MyISAM存储引擎的区别是什么?
事务支持:
- InnoDB:支持事务(Transaction)。它提供了ACID兼容的事务安全表,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。这意味着在InnoDB中,你可以执行一系列操作作为一个单独的事务,要么全部成功,要么全部失败(回滚),即使在出现错误或者系统崩溃的情况下也能回滚事务,保证数据完整性。。
- MyISAM:不支持事务。每个查询都是原子性的,但无法执行跨多个查询的事务。这意味着一旦发生错误或者意外中断,自上次成功提交以来对数据所做的更改可能会丢失。
锁机制:
- InnoDB:使用行级锁定(Row-level locking)。这允许多个用户并发地访问不同的行,提高了并发性能。但是,如果查询条件不是基于主键,InnoDB可能会升级为表锁。
- MyISAM:使用表级锁定(Table-level locking)。当一个线程获得一个表的写锁定时,其他线程对该表的读取和写入都会被阻塞。这可能会导致较低的并发性能,尤其是在写密集的应用程序中。
外键约束:
- InnoDB:支持外键(Foreign Key)约束。可以确保数据引用的完整性和一致性。
- MyISAM:不支持外键。意味着无法强制关联表之间的参照完整性。
崩溃恢复:
- InnoDB:具有崩溃恢复能力(Crash Recovery Capabilities)。如果数据库服务器崩溃,InnoDB可以恢复未提交的事务,确保数据的完整性。
- MyISAM:不提供崩溃恢复功能,在服务器崩溃或意外关机时,如果没有正确的关闭,可能发生数据损坏,且修复过程可能较复杂。
存储格式:
- InnoDB:数据和索引是存储在一起的,称为聚簇索引(Clustered Index)。主键索引的叶子节点包含数据记录,而辅助索引的叶子节点包含主键值。
- MyISAM:数据和索引是分开存储的。数据文件(.MYD)和索引文件(.MYI)是分开保存的。
性能:
- InnoDB:将数据和索引存储在一起,并支持聚簇索引(数据行按照主键顺序存放)。在创建索引和加载大量数据时,性能可能稍逊于MyISAM。但在高并发和需要事务支持的应用中,InnoDB通常表现更好。
- MyISAM:数据和索引分别存储,更适合只读或者以读为主的场景,且在全表扫描以及只读查询的性能上有时优于InnoDB,尤其是在早期版本的MySQL中。
全文索引:
- InnoDB:早期版本不支持全文索引,但在MySQL 5.6.4以后的版本中引入了全文索引的支持。
- MyISAM:之前一直是全文索引的主要存储引擎,原生支持全文索引,适用于文本搜索较多的场景。
如何在数据库中使用或者转换成InnoDB引擎
在MySQL中使用InnoDB存储引擎非常简单,因为从MySQL 5.5版本开始,InnoDB就已经是默认的存储引擎了。但如果你需要显式地指定一个表使用InnoDB存储引擎,或者更改现有表的存储引擎为InnoDB,可以按照以下步骤操作:
1、创建新表时指定InnoDB存储引擎
当你使用
CREATE TABLE
语句创建新表时,可以通过ENGINE
关键字指定存储引擎为InnoDB。例如:CREATE TABLE user( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, description TEXT ) ENGINE=InnoDB;
在这个例子中,
user
表将被创建为使用InnoDB存储引擎。2、更改现有表的存储引擎为InnoDB
如果你已经有一个使用其他存储引擎(如MyISAM)的表,并且想要将其更改为InnoDB,可以使用
ALTER TABLE
语句。例如:ALTER TABLE existing_table ENGINE=InnoDB;
这条命令会将
existing_table
表的存储引擎更改为InnoDB。请注意,在执行此操作之前,最好备份你的数据,以防万一出现任何问题。检查表的存储引擎
要查看数据库中所有表的存储引擎,可以使用以下查询:
SHOW TABLE STATUS FROM your_database_name;
在返回的结果中,你将看到
Engine
列,它显示了每个表的存储引擎。如果你只想查看特定表的存储引擎,可以结合使用
SHOW CREATE TABLE
命令:SHOW CREATE TABLE your_table_name;
在返回的
CREATE TABLE
语句中,你将看到ENGINE
关键字后面跟着的就是该表的存储引擎。
更换引擎时需特别注意:
备份数据:在更改存储引擎之前,特别是当处理大型数据库或关键数据时,强烈建议备份数据库或表。虽然InnoDB和MyISAM之间的转换通常很安全,但总是好的习惯以防万一。
外键约束:InnoDB支持外键约束,而MyISAM不支持。如果你正在从MyISAM迁移到InnoDB,并且打算使用外键,请确保在转换后定义它们。
性能考虑:InnoDB和MyISAM在性能上有不同的特点。InnoDB通常在高并发写入和需要事务支持的场景中表现更好,而MyISAM可能在某些只读或大量读取的场景中更快。在更改存储引擎之前,最好了解你的应用需求并进行性能测试。
空间占用:InnoDB和MyISAM在磁盘空间使用上也有所不同。InnoDB通常会占用更多的空间,因为它存储了更多的元数据,并维护了双写缓冲区等特性。
版本兼容性:确保你的MySQL版本支持InnoDB,并且是最新的稳定版本,以利用最新的功能和性能改进。
推荐阅读
-
分析 MySQL 中的插入阻塞问题
-
字节与比特之间的关系:了解编程中的数据存储
-
如何从 CAD 图纸中扣除 - 你值得学习的大师级常用 CAD 扣除方法 - 工具 2:风云 CAD 编辑器从 CAD 图纸中扣除的方法
-
什么是 mysql 中的自增约束关键字?
-
创建随机生成 ID 号的 MySQL 存储过程
-
硬件安全模块(HSM)、硬件安全引擎(HSE)和安全硬件扩展(SHE)之间的区别 - HSE 在汽车中的主要应用:
-
位、字节、WORD、DWORD 的区别和联系 - Unicode 和 ANSI 的区别就像输入法中 "全宽 "和 "半宽 "的区别一样。 由于不同的 ANSI 编码有不同的标准(不同的字符集),对于给定的多字节字符串,我们必须知道它使用的是哪种字符集,才能知道它包含哪些 "字符"。对于 UNICODE 字符串来说,无论环境如何,它所代表的 "字符 "内容始终是相同的。Unicode 有一个统一的标准,定义了世界上大多数字符的编码,因此拉丁文、数字、简体中文、繁体中文和日文都可以存储在一个编码中。统一码是一个统一的标准,定义了世界上大多数字符的编码。 比特(Bit)和字节(Byte)的区别:例如USB2.0 标准接口的传输速率为 480Mbps,有一些人误认为是每秒 480 兆比特,同样网络带宽为 2MB,就容易误认为是每秒 2 兆比特。其实,480Mbps 应该是 480 兆比特/秒或 480 兆字节/秒,它等于 "60 兆字节/秒";同样,2MB,应该是 256 兆字节/秒。 Bit 和 Byte 译为 "比特",都是数据计量单位,比特="位 "或 "比特"。 Byte = 字节,即 1byte = 8bits,两者的换算关系为 1:8。 Mbps = mega bits per second(兆位/秒)是速率单位,因此 2M 带宽应为 2 兆位/秒,即 2MBps。MB = 兆字节(Megabytes,兆字节)是单位量,1MB/S(兆字节/秒)= 8MBPS(兆字节/秒)。 通常所说的硬盘容量是指 40GB、80GB、100GB,其中的 B 是指 Byte 也称为 "字节"。 1 KB=1024 字节 1 MB=1024 KB=1024*1024 字节 1 GB=1024 MB=1024*1024*1024 字节 例如,以前所谓的 56KB MODEM 转换过来的 56KBps 除以 8 就是 7Kbyte,所以真正从网上下载文件存在硬盘上的速度也是每秒 7Kbyte;也就是说,用 B 表示传输速度一般指 Bit;用 B 表示容量一般指 Byte。比特、字节、WORD、DWORD 的本质。
-
[Elasticsearch <3>✈️✈️] 常用基本属性的用法及与 MySQL 的区别
-
mysql 中 bigint、int、mediumint、smallint 和 tinyint 的范围
-
MySQL 和 Hive 存储引擎在表、索引数量上有哪些限制?-MySQL 存储引擎的限制