数据库存储引擎,是不同的存储技术将数据存储在文件或者内存当中,这些存储引擎当中每种都会使用不同的技术来进行数据的存储,索引技巧实现,或者说数据库锁的实现,通过这些不同的技巧来最终达到一定的效果。
主要来看下我们比较经常使用的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区别及使用场景