MySQL存储引擎及其对比

MySql中有哪些存储引擎?

  1. MyISAM: 这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种:

    • 静态MyISAM:如果数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的,所以这种表存取和更新的效率非常高。当数据受损时,恢复工作也比较容易做。
    • 动态MyISAM:如果数据表中出现varchar、xxxtext或xxxBLOB字段时,服务器将自动选择这种表类型。相对于静态MyISAM,这种表存储空间比较小,但由于每条记录的长度不一,所以多次修改数据后,数据表中的数据就可能离散的存储在内存中,进而导致执行效率下降。同时,内存中也可能会出现很多碎片。因此,这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
    • 压缩MyISAM:以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储,但是这种表压缩之后不能再被修改。另外,因为是压缩数据,所以这种表在读取的时候要先时行解压缩。

    静态表是默认的存储格式,静态表中的字段都是非变长的字段,优点是:存储非常迅速,容易缓存,出现故障容易恢复;缺点是:占用的空间通常比动态表多。(注意: 在存储时,列的宽度不足时,用空格补足,当时在访问的时候并不会得到这些空格)
    动态表的字段是变长的,优点是:占用的空间相对较少,但是频繁地更新删除记录会产生碎片,需要定期改善性能,并且出现故障的时候恢复相对比较困难。 但是,不管是何种MyISAM表,目前它都不支持事务,行级锁和外键约束的功能

  2. MyISAM Merge引擎: 这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。

  3. InnoDB: InnoDB表类型可以看作是对MyISAM的进一步更新产品,它提供了事务、行级锁机制和外键约束的功能。

  4. memory(heap): 这种类型的数据表只存在于内存中。它使用散列索引,所以数据的存取速度非常快。因为是存在于内存中,所以这种类型常应用于临时表中.

  5. archive: 这种类型只支持select 和 insert语句,而且不支持索引。常应用于日志记录和聚合分析方面。

    当然MySql支持的表类型不止上面几种。

MyISAM vs InnoDB

InnoDB

定义:(默认的存储引擎)

InnoDB是一个事务型的存储引擎,有行级锁定和外键约束。

Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

1
2
//这个就是select锁表的一种,不明确主键。增删改查都可能会导致锁全表,在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

适用场景

  1. 经常更新的表,适合处理多重并发的更新请求。
  2. 支持事务。
  3. 可以从灾难中恢复(通过bin-log日志等)。
  4. 外键约束。只有他支持外键。
  5. 支持自动增加列属性auto_increment。

MySQL官方对InnoDB的讲解

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。
  2. InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读,这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。
  3. InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。
  4. InnoDB是为处理巨大数据量时的最大性能设计,它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
  5. InnoDB被用来在众多需要高性能的大型数据库站点上产生。

补充:什么叫事务?简称ACID

A 事务的原子性(Atomicity):指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.

C 事务的一致性(Consistency):指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.

I 独立性(Isolation):事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.

D 持久性(Durability):事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中,不会无缘无故的回滚.

MyIsam

定义

MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。

MyIsam 存储引擎独立于操作系统,也就是可以在windows上使用,也可以比较简单的将数据转移到linux操作系统上去。

意味着:引擎在创建表的时候,会创建三个文件,一个是.frm文件用于存储表的定义,一个是.MYD文件用于存储表的数据,另一个是.MYI文件,存储的是索引。操作系统对大文件的操作是比较慢的,这样将表分为三个文件,那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作,其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。

适用场景

  1. 不支持事务的设计,但是并不代表着有事务操作的项目不能用MyIsam存储引擎,可以在service层进行根据自己的业务需求进行相应的控制。
  2. 不支持外键的表设计。
  3. 查询速度很快,如果数据库insert和update的操作比较多的话不太适用。
  4. 整天 对表进行加锁的场景。
  5. MyISAM极度强调快速读取操作。
  6. MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。

缺点:

就是不能在表损坏后恢复数据。(是不能主动恢复)

0%