MySQL常见存储引擎特点

参考资料

概述

  • InnoDB
    • mysql默认的存储引擎
    • 事务型引擎
    • 自动崩溃恢复
    • InnoDB基于聚簇索引建立，MySQL innodb的主键索引是簇集索引，不是主键索引的就是非簇集索引
    • 适合执行大量更新、插入操作
    • 适合高并发和高QPS
  • MyISAM
    • 不是事务安全的
    • 不支持外键
    • 只支持表级锁
    • 适合执行大量查询操作
  • memory
    • 所有数据都保存在内存中
    • Memory 表的结构在重启以后还会保留，但数据会丢失
  • archive
    • 针对高写入压缩做了优化的简单引擎
    • 适合日志和数据采集应用
  • blackhole
    • 没有实现任何存储机制，它会舍弃所有写入数据，但是服务器会记录 Blackhole 表的日志。（不推荐）
  • federated
    • 访问其他 MySQL 服务器的一个代理，默认禁用。（开发出来属于商业竞争行为）
  • merge （已被放弃）

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

简单介绍区别

  1. MyISAM是非事务安全的，而InnoDB是事务安全的
  2. MyISAM锁的粒度是表级的，而InnoDB支持行级锁
  3. MyISAM支持全文类型索引，而InnoDB不支持全文索引
  4. MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM5、MyISAM表保存成文件形式，跨平台使用更加方便

数据结构

两种引擎所使用的索引的数据结构都是B+树!

关于索引详情：MyISAM 和 InnoDB 索引的区别

  • MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。（但是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。）
  • Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

簇集索引：索引的叶子节点存的是整个单条记录的所有字段值。
非簇集索引：索引的叶子节点存的是被索引字段的值。

应用场景

  1. MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM
  2. InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

InnoDB

InnoDB 是MySQL默认的事务型引擎，也是最重要、最广泛的存储引擎。

  • Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。
  • 还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
  • 当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
  • InnoDB 的数据存储在表空间中，表空间是由 InnoDB 管理的黑盒文件系统，由一系列系统文件组成。InnoDB 可以将每个表的数据和索引存放在单独的文件中。InnoDB 也可以使用裸设备作为表空间存储介质。
  • InnoDB 通过间隙锁（next-key locking）防止幻读的出现。InnoDB 是基于聚簇索引建立，与其他存储引擎有很大的区别，聚簇索引对主键查询有很高的性能，不过它的二级索引（secondary index，非主键索引）必须包含主键列。所以如果主键列很大的话，索引会很大。

MyISAM (Indexed Sequential Access Method )

在 5.1 之前，MyISAM 是默认的引擎，MyISAM 有大量的特性，包括全文索引、压缩、空间函数。但是 MyISAM 不支持事务和行级锁，而且在崩溃后无法安全恢复。

MyISAM 并不是无所是处。对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用 MyISAM。

  • 和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。因此，如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。
  • MyISAM 会将表存储在两个文件中：数据文件、索引文件。分别是.MYD、.MYI 扩展名。MyISAM 表可以包含动态或者静态行。MySQL 会根据表定义选择哪种行格式。MyISAM 表的行记录数，取决于磁盘空间和操作系统中的单个文件最大尺寸。

在 MySQL 中，默认配置只能存储 256TB 的数据。因为指向数据记录的指针长度是 6 字节。需要修改可以修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项。两个相乘是最大的大小。会导致重建索引。

  • MyISAM 是对整个表加锁，而不是行锁，读取的时候对表加共享锁，写入的时候加排他锁。但是在表有读取查询的同时，也可以往表内写入记录。由于没有行锁机制，所以在海量写入的时候，会导致所有查询处于 Locked 状态。
  • 对于 MyISAM，即使是 Blob，Text 等等长字段，也可以基于前 500 字符创建索引，MyISAM 支持全文索引，这是一个基于分词创建的索引，也可以支持复杂的查询。
  • MyISAM 可以选择延迟更新索引键，在创建表的时候指定 delay_key_write 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是写到缓存区，只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能，但是在主机崩溃时会造成索引损坏，需要执行修复操作。
  • MyISAM 另一个特性是支持压缩表。如果数据在写入后不会修改，那么这个表适合 MyISAM 压缩表。可以使用 myisampack 对 MyISAM 表进行打包，压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用，因此可以减少磁盘 IO，提升性能，压缩表也支持索引，但是索引也是只读的。

Memroy

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表（以前也叫做 HEAP 表）是非常有用的。Memory 表至少比 MyISAM 表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘 I/O。Memory 表的结构在重启以后还会保留，但数据会丢失。

Memroy 表在很多场景可以发挥好的作用：

  • 用于查找（lookup）或者映射（mapping）表，例如将邮编和州名映射的表。
  • 用于缓存周期性聚合数据（periodicallyaggregateddata）的结果。
  • 用于保存数据分析中产生的中间数据。

Memory 表支持 Hash 索引，因此查找操作非常快。虽然 Memory 表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy 表是表级锁，因此并发写入的性能较低。它不支持 BLOB 或 TEXT 类型的列，并且每行的长度是固定的，所以即使指定了 VARCHAR 列，实际存储时也会转换成 CHAR，这可能导致部分内存的浪费。如果 MySQL 在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是 Memory 表。如果中间结果太大超出了 Memory 表的限制，或者含有 BLOB 或 TEXT 字段，则临时表会转换成 MyISAM 表。

Archive

Archive 引擎支持是 Insert，Select 操作，现在支持索引，Archive 引擎会缓存所有的写，并利用 zlib 对写入行进行压缩，所以比 MyISAM 表的磁盘 IO 更少。但是在每次 Select 查询都需要执行全表扫描。所以在 Archive 适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的 Insert 操作场景中也可以使用。

Archive 引擎支持行级锁和专用的缓存区，所以可以实现高并发写入，在查询开始到返回表存在的所有行数之前，Archive 会阻止其他 Select 执行，用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见，这种机制模仿了事务和 MVCC 的特性，但是 Archive 不是一个事务型引擎，而是针对高写入压缩做了优化的简单引擎。

Blackhole

Blackhole 没有实现任何存储机制，它会舍弃所有写入数据，不做任何保存，但是服务器会记录 Blackhole 表的日志，用于复制数据到备库，或者只是简单的记录到日志，这种特殊的存储引擎可以在一些特制的复制架构和日志审核时发挥作用。但是不推荐。

Federated

Federated 引擎是访问其他 MySQL 服务器的一个代理，它会创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如 MicrosoftSQLServer 和 Oracle 的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Merge

Merge 引擎是 MyISAM 引擎的一个变种。Merge 表是由多个 MyISAM 表合并而来的虚拟表。如果将 MySQL 用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃。

CSV

CSV 引擎可以将普通的 CSV 文件作为 MySQL 表来处理，但是这种表不支持索引，CSV 可以在数据库运行时拷贝或者拷出文件，可以将 Excel 等电子表格中的数据存储未 CSV 文件，然后复制到 MySQL 中，就能在 MySQL 中打开使用。同样，如果将数据写入到一个 CSV 引擎表，其他外部程序也可以从表的数据文件中读取 CSV 的数据。因此 CSV 可以作为数据交换机制。非常好用。

posted @ 2020-05-28 23:29  holm  阅读(326)  评论(0)  编辑  收藏  举报