数据库引擎对比InnerDB和MYISAM

   数据库存储引擎,是不同的存储技术将数据存储在文件或者内存当中,这些存储引擎当中每种都会使用不同的技术来进行数据的存储,索引技巧实现,或者说数据库锁的实现,通过这些不同的技巧来最终达到一定的效果。

   主要来看下我们比较经常使用的InnerDB

   InnerDB

       InnerDB是一个事务型的存储引擎,主要是目的是大数据时提供高性能的数据服务,在运行时在内存当中建立缓冲池,用来缓冲数据和索引。

InnerDB的特点:

1、支持事务处理、ACID事务特性

2、实现了SQL标准的四种隔离级别

3、支持行级锁和外键约束

4、可以利用事务日志进行数据恢复

5、不支持FullText类型的索引,没有保存数据库行数,计算count(*)需要全局扫描

6、支持自动增加列属性auto_increment

7、最后也是非常重要的一点:InnerDB是为了处理大量数据时的最大性能设计,其CPU效率可能是其他基于磁盘的关系型数据库所不能匹敌的。

在以下两点情况下必须使用InnerDB

1、可靠性高或者必须要求事务处理

2、表更新和查询相当的频繁,并且表锁定的机会比较大的情况下,指定InnerDB存储引擎。

InnerDB引擎的索引实现:

   InnerDB引擎的索引结构是B+树的实现方式。InnerDB的索引文件存储的包括数据文件,所以B+Tree树当中叶子节点中存储的就是实际数据,其实这种索引就是聚集索引。

   InnerDB的辅助索引存储域存储的也是记录相应主键的值不是地址,所以当使用辅助索引查找时,会先通过辅助索引找到主键,再根据主键索引找到实际的数据。InnerDB不建议使用过长的主键,否则会使辅助索引变得很大。

   因为InnerDB的数据本身要按照主键进行聚集,所以InnerDB必须要有主键,如果没有显示指定,InnerDB会自动选择可以唯一标识的列作为主键,如果不存在这样的列,InnerDB会隐式生成一个隐含字段,作为主键。

   InnerDB的辅助索引data域当中存储的值是主键的值而不是地址,InnerDB的辅助索引都是用主键作为data域。

   InnerDB引擎索引的查找步骤为:将主键组织到B+树上,行数据存储在B+树的叶子节点上,如果使用主键检索,会通过主键检索到叶子节点,然后获得行数据。如果对name进行检索,会在辅助索引B+树上检索name,找到其叶子节点,获得相应的主键,第二步使用主键在B+树当中再执行一次检索,最终到达叶子节点,获取整行数据。

MyISAM存储引擎

    MyISAM是Mysql的默认引擎,其目标是快速读取。

MyISAM引擎的特点:

1、快速读取,如果频繁插入和更新的话,因为涉及到数据全表锁,效率并不高

2、保存了数据库行数,执行count时,不需要扫描全表;

3、不支持数据库事务;

4、不支持行级锁和外键;

5、不支持故障恢复。

6、支持全文检索FullText,压缩索引。

MyISAM建议使用场景:

1、做很多count计算的,(如果count计算后面有where还是会全表扫描)

2、插入和更新较少,查询比较频繁的

      MyISAM引擎在创建表的时候,会创建三个文件,.frm文件,存储表的定义,.myd存储数据库数据,.myi存储数据索引。

     MyISAM的索引和数据是分开的,并且索引是有压缩的,所以存储文件就会小很多,MyISAM应对错误码导致的数据恢复的速度很快,MyISAM数据是以文件的形式保存的,所以在跨平台当中数据移动很方便,

MyISAM索引实现:

    MyISAM引擎当中的索引也是采用B+树的方式,MyISAM当中节点的键值指向的地址,地址当中存储的数据

    B+树当中存储的内容为实际数据的地址,也就是索引和数据的存储是分开的,即非聚集索引的一种实现方式。MyISAM引擎中根据索引的搜查方式是,根究给定的条件基于索引查找,找到叶子节点当中的数据地址,然后再根据数据地址查找到数据。

Mrg_MyISAM存储引擎

   Mrg_MyISAM是一种水平分表的一种方式,Mrg_MyISAM是一组MyISAM引擎的组合,将多个MyISAM引擎聚合起来,但是其内部没有数据,数据保存在MyISAM引擎对应的数据库当中,但是可以直接进行查询,删除更新操作。

   比如用户表,我们有上亿的用户,这个时候,对用户表进行水平切分,分成user1,user2,并且两张表结构完全相同,

//用户表一
CREATE TABLE IF NOT EXISTS `user1` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//用户表二
CREATE TABLE IF NOT EXISTS `user2` (  
 `id` int(11) NOT NULL ,
 `name` varchar(50) DEFAULT NULL,  
 PRIMARY KEY (`id`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;  

//分别插入两条测试数据先
INSERT INTO `user1` (`name`) VALUES('辅助');  
INSERT INTO `user2` (`name`) VALUES('JackFrost'); 

接下来我们创建一个Mrg_MyISAM存储引擎的数据表

CREATE TABLE IF NOT EXISTS `alluser` (  
  `id` int(11) NOT NULL ,  
  `name` varchar(50) DEFAULT NULL,  
   PRIMARY KEY (`id`)
) ENGINE=MRG_MYISAM  
DEFAULT CHARSET=utf8 
UNION=(user1,user2)  ;  

在查询时,只需要查询主表,就可以把分表当中的数据查询出来,但是如果插入的时候,会提示插入失败,只有读权限,可以修改总表的method权限,来执行插入操作,同时也会指定插入主表时,插入的是具体哪个分表

ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = FIRST;

也可以设置成插入总表的时候,插入到最后的一个分表当中

//就是插入总表的时候,其实也是插入到最后一个分表。
ALTER TABLE `test_engine`.`alluser` INSERT_METHOD = LAST;

在实际开发当中,我们需要有一个Mrg_MyISAM引擎的表来保存主键,然后我们根据路由策略来决定将数据保存到哪张表中。

Mrg_MyISAM使用场景:

1、适合插入和查询比较高的系统,有MyISAM是全表锁,所以不适合更新比较频繁的场景。

2、实际开发当中比较适合的就是日志管理,将不同月份的日志保存在不同的表当中,然后使用工具压缩,最后通过一张表查询初出来。

Mrg_MyISAM使用时收到的限制:

1、主表必须使用Mrg_MyISAM引擎,子表必须使用MyISAM引擎。可能就会有部分限制,比如不支持事务和外键

2、主表不能使用MyISAM的特性,比如全文索引,可以为子表创建FullText类型的索引,但是查询的话只能通过主表查询

3、如果修改主表的存储引擎,那么主表和子表的映射关系就丢失了,会将子表中的数据拷贝到修改后的表中

4、主表和字表的主键都不能自动增长

5、子表之间不能存在唯一键约束,但是单个子表内可以存在唯一键,所以通过主表可能查询到重复的id

Memory存储引擎

 Memory存储引擎采用逻辑介质是内存,因此其访问速度会非常快,其默认使用的是hash索引,一旦服务关掉,数据就会丢失。Memory存储引擎要求存储的数据是长度不变的格式,比如blob和text类型都不可以

适合的场景:

1、适合保存目标数据比较小,并且频繁进行访问的,如果太大的话,容易造成内存溢出,通过max_heap_table_size来设定表的大小;

2、存储在Memory引擎的表中的数据,如果丢失也没有关系的

3、如果数据是临时的,必须立刻用的到,那么可以存在内存当中。

   Memory存储引擎支持hash索引和B树索引,hash索引用来比较相等会比较快,范围查找会比较慢,B树索引可以部分查询和通配查询,也可以使用<,>,= 等方便数据挖掘。

  Memory存储引擎创建的表,最好是使用完之后,就删除。

以上就是关于常用的数据库引擎的记录,如有错误,欢迎指正~

 

参考:MySQL存储引擎InnoDB和MyISAM区别及使用场景

          MySQL优化系列(五)--数据库存储引擎(主要分析对比InnoDB和MyISAM以及讲述Mrg_Myisam分表)

          MySQL数据库引擎

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值