Mysql核心之存储引擎 有更新!

存储引擎是什么?

例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者,你也许需要一个支持[事务处理]的数据库(以确保事务处理不成功时数据的回退能力)。

这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎,可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎,以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

选择如何存储和检索你的数据的这种灵活性是MySQL为什么如此受欢迎的主要原因。其它数据库系统仅支持一种类型的数据存储。遗憾的是,其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能,要么你就用几个小时甚至几天的时间详细调整你的数据库。使用MySQL,我们仅需要修改我们使用的存储引擎就可以了 –百度百科

总的来说,存储引擎是组织数据的方式,不同的存储引擎,能够满足不同的业务需求,同时存在不同方面的性能差异,选择合适的存储引擎对于不同的数据的存储要求提供一种合适的存储策略,对于数据的操作来说,是事半功倍的。

Mysql的常用的存储引擎及特点

通过终端命令:SHOW ENGINES \G我们可以查看数据库支持的存储引擎,Mysql5.6支持9种存储引擎,如下查询结果,通过表格组织。

mysql> SHOW ENGINES \G
-------------------------------- 1. row -------------------------------- 
      Engine: FEDERATED                       存储引擎名                
     Support: NO                              数据库是否支持此引擎  
     Comment: Federated MySQL storage engine  简介
Transactions: NULL                            事务支持
          XA: NULL                            分布式事务
  Savepoints: NULL                       
-------------------------------- 2. row --------------------------------
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 3. row --------------------------------
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 4. row --------------------------------
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 5. row --------------------------------
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 6. row --------------------------------
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 7. row --------------------------------
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
-------------------------------- 8. row --------------------------------
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
-------------------------------- 9. row --------------------------------
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

看到这么多,你肯能有点迷了,这么多存储引擎,到底该如何选择,到底有什么用。其实常用的就那么几种,最容易遇到(考到)的也就那么两三种。也许你除了InnoDB其他的都不知道那也没关系,因为在数据库的快速发展中,很多存储引擎支撑的业务,正在逐渐被某些数据库替代,这些引擎基本不会被用到,术业有专攻就是这个道理,目前的Mysql默认引擎是InnoDB,InnoDB特点是对事务的支持,各种索引结构的支持,而我们大多数的业务都离不开事务。

几种存储引擎的简介:

摘自[百度百科]:
1、MyISAM:拥有较高的插入,查询速度,但不支持事务
2、InnoDB:5.5版本后Mysql的默认数据库,事务型数据库的首选引擎,支持ACID事务,支持行级锁定
3、Memory:所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
4、Merge :将一定数量的MyISAM表联合而成一个整体,在超大规模数据存储时很有用
5、Archive :非常适合存储大量的独立的,作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度,但其对查询的支持相对较差
6、Federated: 将不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。非常适合[分布式应用]
7、CSV: 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件,每个数据行占用一个文本行。CSV存储引擎不支持索引。
8、BlackHole :黑洞引擎,写入的任何数据都会消失,一般用于记录binlog做复制的中继

InnoDB与MyISAM存储引擎的比较

名称事务支持存储限制锁机制索引支持缓存支持数据压缩空间使用内存使用插入速度外键
InnoDB支持64T行锁B树/全文/空间数据/索引支持支持
MyISAM不支持不限表锁B树/全文/空间索引不支持不支持
以上两种是Mysql最常用的存储引擎,特点也很明显,MyISAM不支持事务,那么他就不支持并发的Update,但是它支持高负载的查询,他的表锁支持他的并发插入。InnoDB和MyiSAM目前都支持全文检索。like %xxx%这种操作是十分耗时的,而且性能低下。而全文检索就可以用来支持高效模糊匹配。另外InnoDb是唯一支持外键的MySQL引擎,而外键约束作为保证数据库完整性的一种机制,在数据插入,修改,导入,等操作中都有很大的限制。

指定存储引擎<操作>

在创建表的时候可以指定存储引擎,在test数据库中创建tetsuser和testuser2两个表如下:

USE test;

CREATE TABLE `testuser` (
  `oId` INT(10) NOT NULL,
  `username` INT(11) NOT NULL,
  `userage` INT(11) NOT NULL,
  PRIMARY KEY (`oId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `testuser2` (
  `oId` VARCHAR(10) NOT NULL,
  `username` INT(11) NOT NULL,
  `userage` INT(11) NOT NULL,
  PRIMARY KEY (`oId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

查看表信息:

mysql> SHOW  TABLE   STATUS  FROM  test  WHERE  NAME="testuser" \G
-------------------------------- 1. row --------------------------------
           Name: testuser
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avgrowlength: 0
    Data_length: 16384
Maxdatalength: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-09-21 11:07:35
    Update_time: NULL
     Check_time: NULL
      Collation: utf8generalci
       Checksum: NULL
 Create_options:
        Comment:
		
mysql> SHOW  TABLE   STATUS  FROM  test  WHERE  NAME="testuser2"  \G
-------------------------------- 82. row --------------------------------
           Name: testuser2
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avgrowlength: 0
    Data_length: 0
Maxdatalength: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-09-21 11:12:35
    Update_time: 2017-09-21 11:12:35
     Check_time: NULL
      Collation: utf8generalci
       Checksum: NULL
 Create_options:
        Comment:

从上面的表状态可以稍微的了解到不同存储引擎所带来的差别,具体的差异,可以通过测试,进行验证…这里就略过…

修改表的存储引擎

mysql> alter table testuser2 engine = innodb; 

如何选择存储引擎

在选择存储引擎时,应根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根 据实际情况选择多种存储引擎进行组合。 下面是常用存储引擎的适用环境。
 MyISAM:默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。MyISAM 是在Web、数据仓储和其他应用环境下最常使用的存储引擎 之一.

 InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的 要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、 删除操作,那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低 由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback), 对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 都是合适的选择。

 MEMORY:将所有数据保存在 RAM 中,在需要快速定位记录和其他类似数据的环境 下,可提供极快的访问。MEMORY 的缺陷是对表的大小有限制,太大的表无法 CACHE 在内 存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。 MEMORY 表通常用于更新不太频繁的小表,用以快速得到访问结果。

 MERGE:用于将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为一个对象 引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制,并且通过将不同 的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB 环境十分适合。

InnoDB和MyISAM的系统架构

。。。待续

补充:

«mysql深入浅出»中各种存储引擎的对比,由于这本书的内容跟现在的版本已经比较过时了,其中有一些内容只能选看。
8747975ab3404424816626bc47f88f96-image.png
目前,InnoDB已经支持全文索引.

. - - —— ————THE END——— —— - - .

⚠求而不得,往往不求而得!
⚠此文章为原创作品,转载务必保留本文地址及原作者。

评论

发表评论

validate