当前位置:Gxlcms > mysql > 【MySQL】MySQL的存储引擎

【MySQL】MySQL的存储引擎

时间:2021-07-01 10:21:17 帮助过:48人阅读


在创建表时可以指定表的类型,也就是表的存储引擎。表的存储引擎决定了数据如何存储及如何访问,还有事务如何存储。表的存储引擎在很大程度上影响着处理SQL语句所需的存储空间和速度。不同的存储引擎所具有的特点也有所不同,有些存储引擎在处理很多复杂的SELECT语句时非常适合,而另一些则在实现快速更新时比较适合。

InnoDB

InnoDB是MySQL的默认事务型引擎,也是最重要,使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务,短期事务大部分情况下是正常提交的,很少会被回滚。InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中也很流行。

除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。————《高性能MySQL》

  • InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读),并且通过间隙锁策略防止幻读的出现。

  • InnoDB表示基于聚簇索引建立的

  • 支持外键约束。

  • 支持自动增加列AUTO_INCREMENT属性。

  • 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

  • 删除或增加索引时不需要复制全表数据。

InnoDB内部做了很多优化,包括从磁盘读取数据时采用的可预测预读,能够自动在内存中创建hash索引以加速度操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区。

InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他引擎有很大不同,聚簇索引对主键查询有很高的性能。但是二级索引中必须包含主键列,所以主键列很大的话,其他的所有索引都会很大。因此,若表上的索引较多的话,主键应当尽可能的小。

MyISAM

MyISAM提供了大量的特性,包括全文索引,压缩,空间函数(GIS)等,但MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。在MySQL 5.1及以前的版本中MyISAM为默认的存储引擎,正是由于该引擎的缘故,即使MySQL支持事务已经很长时间了,在很多人的概念中MySQL还是非事务型的数据库。

  • MyISAM对整张表加锁,而不是针对行。

  • 支持全文索引。

  • 支持压缩表。压缩表是不能进行修改的,可以极大地减少磁盘空间占用,因此也可以减少磁盘I/O操作,从而提升查询性能。

存储

MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表的定义来决定采用何种行格式。
在MySQL5.0中,MyISAM表如果是变长行,则默认配置只能处理256TB的数据

特性

作为MySQL最早的存储引擎之一,还是有一些特性。

加锁与并发–表锁

  MyISAM对整张表加锁,而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。但是在表有读取查询的同时,也可以往表中插入新的记录(并发插入)。

修复

  对于MyISAM表,MySQL可以手工或者自动执行检查和检修操作,但这里说的修复和事务恢复以及崩溃恢复是不同的概念。

索引特性

  对于MyISAM表,即使是BLOB和TEXT等长字段,也可以基于其前500个字符创建索引。MyISAM也支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询。
延迟更新索引键
  创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘。
  

MyISAM性能

MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下的性能很好。但由于表锁的存在对于性能有很大的影响。

Archive 引擎

Archive存储引擎只支持INSERT和SELECT。Archive引擎会缓存所有的写并利用zlib对插入进行压缩,所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用,这类应用做数据分析时往往需要全表扫描。

Archive引擎支持行级锁和专用的缓冲区,所以可以实现高并发的插入。在一个查询开始直至返回表中存在的所有的行数之前,Archive会阻止其他的SELECT执行,以实现一致性读。另外,也实现了批量插入在完成之前对读操作不可见。这种机制模仿了事务和MVCC的一些特性,但是Archive引擎不是一个事务型的引擎,而是一个针对高速插入和压缩做了优化的引擎。

CSV存储引擎

这个引擎可以将普通的CSV文件作为MySQL的表来处理,但这种表不支持索引。只需要将CSV文件copy到CSV存储引擎的数据目录下,就能使用在MySQL中已表的规则打开使用。

Memory引擎

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

  • 支持Hash索引,因此查找操作非常快。

  • 是表级锁,因此并发写入性能较低。

  • 不支持BLOB或TEXT类型的列,并且每行长度是固定的,即使指定了varchar,实际存储中也会被转换为char。

说明

除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎——《高性能MySQL》

另外上面只是列举了一些经常遇见的存储引擎,并不全面。

在创建表时可以指定表的类型,也就是表的存储引擎。表的存储引擎决定了数据如何存储及如何访问,还有事务如何存储。表的存储引擎在很大程度上影响着处理SQL语句所需的存储空间和速度。不同的存储引擎所具有的特点也有所不同,有些存储引擎在处理很多复杂的SELECT语句时非常适合,而另一些则在实现快速更新时比较适合。

InnoDB

InnoDB是MySQL的默认事务型引擎,也是最重要,使用最广泛的存储引擎。它被设计用来处理大量的短期(short-lived)事务,短期事务大部分情况下是正常提交的,很少会被回滚。InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中也很流行。

除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。————《高性能MySQL》

  • InnoDB采用MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ(可重复读),并且通过间隙锁策略防止幻读的出现。

  • InnoDB表示基于聚簇索引建立的

  • 支持外键约束。

  • 支持自动增加列AUTO_INCREMENT属性。

  • 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

  • 删除或增加索引时不需要复制全表数据。

InnoDB内部做了很多优化,包括从磁盘读取数据时采用的可预测预读,能够自动在内存中创建hash索引以加速度操作的自适应哈希索引,以及能够加速插入操作的插入缓冲区。

InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他引擎有很大不同,聚簇索引对主键查询有很高的性能。但是二级索引中必须包含主键列,所以主键列很大的话,其他的所有索引都会很大。因此,若表上的索引较多的话,主键应当尽可能的小。

MyISAM

MyISAM提供了大量的特性,包括全文索引,压缩,空间函数(GIS)等,但MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。在MySQL 5.1及以前的版本中MyISAM为默认的存储引擎,正是由于该引擎的缘故,即使MySQL支持事务已经很长时间了,在很多人的概念中MySQL还是非事务型的数据库。

  • MyISAM对整张表加锁,而不是针对行。

  • 支持全文索引。

  • 支持压缩表。压缩表是不能进行修改的,可以极大地减少磁盘空间占用,因此也可以减少磁盘I/O操作,从而提升查询性能。

存储

MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表的定义来决定采用何种行格式。
在MySQL5.0中,MyISAM表如果是变长行,则默认配置只能处理256TB的数据

特性

作为MySQL最早的存储引擎之一,还是有一些特性。

加锁与并发–表锁

  MyISAM对整张表加锁,而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。但是在表有读取查询的同时,也可以往表中插入新的记录(并发插入)。

修复

  对于MyISAM表,MySQL可以手工或者自动执行检查和检修操作,但这里说的修复和事务恢复以及崩溃恢复是不同的概念。

索引特性

  对于MyISAM表,即使是BLOB和TEXT等长字段,也可以基于其前500个字符创建索引。MyISAM也支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询。
延迟更新索引键
  创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘。
  

MyISAM性能

MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下的性能很好。但由于表锁的存在对于性能有很大的影响。

Archive 引擎

Archive存储引擎只支持INSERT和SELECT。Archive引擎会缓存所有的写并利用zlib对插入进行压缩,所以比MyISAM表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用,这类应用做数据分析时往往需要全表扫描。

Archive引擎支持行级锁和专用的缓冲区,所以可以实现高并发的插入。在一个查询开始直至返回表中存在的所有的行数之前,Archive会阻止其他的SELECT执行,以实现一致性读。另外,也实现了批量插入在完成之前对读操作不可见。这种机制模仿了事务和MVCC的一些特性,但是Archive引擎不是一个事务型的引擎,而是一个针对高速插入和压缩做了优化的引擎。

CSV存储引擎

这个引擎可以将普通的CSV文件作为MySQL的表来处理,但这种表不支持索引。只需要将CSV文件copy到CSV存储引擎的数据目录下,就能使用在MySQL中已表的规则打开使用。

Memory引擎

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

  • 支持Hash索引,因此查找操作非常快。

  • 是表级锁,因此并发写入性能较低。

  • 不支持BLOB或TEXT类型的列,并且每行长度是固定的,即使指定了varchar,实际存储中也会被转换为char。

说明

除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎——《高性能MySQL》

另外上面只是列举了一些经常遇见的存储引擎,并不全面。

以上就是【MySQL】MySQL的存储引擎的内容,更多相关内容请关注PHP中文网(www.gxlcms.com)!

人气教程排行