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存储引擎的主要特点:

  1. 事务支持

    • InnoDB完全支持ACID(原子性、一致性、隔离性、持久性)事务特性,保证了数据的完整性和一致性。
    • 提供了事务管理机制,允许用户执行BEGIN, COMMIT, ROLLBACK等事务操作。
  2. 行级锁定

    • InnoDB使用行级锁定,大大提高了多用户并发环境下的性能,尤其在频繁修改不同记录的情形下,可以减少锁冲突,提升并发读写的效率。
  3. 外键约束

    • 支持外键约束(FOREIGN KEY),确保了数据库表间的数据引用完整性。
  4. MVCC(多版本并发控制)

    • 实现了多版本并发控制机制,从而在多个事务同时读取相同数据时,减少了锁竞争,提升了并发性能,同时也支持“可重复读”(Repeatable Read)事务隔离级别,有效避免了幻读现象。
  5. 崩溃恢复

    • 内置崩溃恢复机制,即使在数据库宕机或系统崩溃后,也能通过redo日志(重做日志)和undo日志(回滚日志)来恢复未完成的事务和回滚已提交的事务。
  6. 缓冲池

    • 使用缓冲池技术,将频繁读写的页缓存在内存中,减少磁盘I/O操作,提高整体性能。
  7. 其他高级特性

    • 插入缓冲(Insert Buffer),优化了非唯一二级索引的插入操作;
    • 两次写(Double Write),用于防止因电源故障等原因导致的数据页损坏;
    • 自适应哈希索引(Adaptive Hash Index),动态创建和维护哈希索引以加速查询;
    • 预读(Read Ahead),预先读取连续的数据页到内存,减少未来查询时的磁盘I/O。

InnoDB存储引擎非常适合那些需要事务处理、高并发读写操作、数据完整性和高性能的应用场景,这也是它成为MySQL默认存储引擎的原因之一。

2、MySQL中MyISAM存储引擎

MyISAM是MySQL中常用的存储引擎之一,尤其在MySQL 5.5版本之前的版本中,它是默认的存储引擎。

MyISAM存储引擎具有一些独特的特点和适用场景。

特点

  1. 非事务性:MyISAM不支持事务处理,这意味着它不能执行跨多个查询的事务。每个查询都是原子性的,但无法回滚。
  2. 表级锁定:MyISAM使用表级锁定,当一个线程获得写锁定时,其他线程对表的读取和写入都会被阻塞。这可能导致较低的并发性能,特别是在写密集的应用中。
  3. 全文索引:MyISAM支持全文索引,这对于需要进行文本搜索的应用非常有用。
  4. 访问速度快:对于只读或大量读取的应用,MyISAM通常提供较快的查询性能。

文件结构

MyISAM存储引擎的表在磁盘上存储为三个文件:

  • .frm 文件:存储表的定义结构。
  • .MYD 文件:存储表的数据(MYData)。
  • .MYI 文件:存储表的索引(MYIndex)。

这种文件结构使得数据文件和索引文件可以放置在不同的目录,从而平均分布I/O负载,提高查询速度。

适用场景

MyISAM适用于以下场景:

  • 只读或大量读取的应用:对于主要进行读取操作的应用,MyISAM通常能提供较好的性能。
  • 全文搜索:如果应用需要进行全文搜索,MyISAM是一个很好的选择,因为它支持全文索引。
  • 不需要事务支持:如果应用不需要事务处理或复杂的并发控制,MyISAM可以是一个简单而高效的解决方案。

需要注意的是:由于MyISAM不支持事务和行级锁定,它在高并发写入或需要保证数据一致性的场景中可能不是最佳选择。在这些情况下,InnoDB存储引擎可能更合适。

3、MySQL中InnoDB和MyISAM存储引擎的区别是什么?

  1. 事务支持

    • InnoDB:支持事务(Transaction)。它提供了ACID兼容的事务安全表,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。这意味着在InnoDB中,你可以执行一系列操作作为一个单独的事务,要么全部成功,要么全部失败(回滚),即使在出现错误或者系统崩溃的情况下也能回滚事务,保证数据完整性。。
    • MyISAM:不支持事务。每个查询都是原子性的,但无法执行跨多个查询的事务。这意味着一旦发生错误或者意外中断,自上次成功提交以来对数据所做的更改可能会丢失。
  2. 锁机制

    • InnoDB:使用行级锁定(Row-level locking)。这允许多个用户并发地访问不同的行,提高了并发性能。但是,如果查询条件不是基于主键,InnoDB可能会升级为表锁。
    • MyISAM:使用表级锁定(Table-level locking)。当一个线程获得一个表的写锁定时,其他线程对该表的读取和写入都会被阻塞。这可能会导致较低的并发性能,尤其是在写密集的应用程序中。
  3. 外键约束

    • InnoDB:支持外键(Foreign Key)约束。可以确保数据引用的完整性和一致性。
    • MyISAM:不支持外键。意味着无法强制关联表之间的参照完整性。
  4. 崩溃恢复

    • InnoDB:具有崩溃恢复能力(Crash Recovery Capabilities)。如果数据库服务器崩溃,InnoDB可以恢复未提交的事务,确保数据的完整性。
    • MyISAM:不提供崩溃恢复功能,在服务器崩溃或意外关机时,如果没有正确的关闭,可能发生数据损坏,且修复过程可能较复杂。
  5. 存储格式

    • InnoDB:数据和索引是存储在一起的,称为聚簇索引(Clustered Index)。主键索引的叶子节点包含数据记录,而辅助索引的叶子节点包含主键值。
    • MyISAM:数据和索引是分开存储的。数据文件(.MYD)和索引文件(.MYI)是分开保存的。
  6. 性能

    • InnoDB:将数据和索引存储在一起,并支持聚簇索引(数据行按照主键顺序存放)。在创建索引和加载大量数据时,性能可能稍逊于MyISAM。但在高并发和需要事务支持的应用中,InnoDB通常表现更好。
    • MyISAM:数据和索引分别存储,更适合只读或者以读为主的场景,且在全表扫描以及只读查询的性能上有时优于InnoDB,尤其是在早期版本的MySQL中。
  7. 全文索引

    • 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关键字后面跟着的就是该表的存储引擎。

更换引擎时需特别注意:

  1. 备份数据:在更改存储引擎之前,特别是当处理大型数据库或关键数据时,强烈建议备份数据库或表。虽然InnoDB和MyISAM之间的转换通常很安全,但总是好的习惯以防万一。

  2. 外键约束:InnoDB支持外键约束,而MyISAM不支持。如果你正在从MyISAM迁移到InnoDB,并且打算使用外键,请确保在转换后定义它们。

  3. 性能考虑:InnoDB和MyISAM在性能上有不同的特点。InnoDB通常在高并发写入和需要事务支持的场景中表现更好,而MyISAM可能在某些只读或大量读取的场景中更快。在更改存储引擎之前,最好了解你的应用需求并进行性能测试。

  4. 空间占用:InnoDB和MyISAM在磁盘空间使用上也有所不同。InnoDB通常会占用更多的空间,因为它存储了更多的元数据,并维护了双写缓冲区等特性。

  5. 版本兼容性:确保你的MySQL版本支持InnoDB,并且是最新的稳定版本,以利用最新的功能和性能改进。

© 版权声明
THE END
喜欢就支持一下吧
点赞10 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容