专栏首页Java架构师历程MYSQL 谈谈各存储引擎的优缺点

MYSQL 谈谈各存储引擎的优缺点

MySQL中的存储引擎:

1、存储引擎的概念

2、查看MySQL所支持的存储引擎

3、MySQL中几种常用存储引擎的特点

4、存储引擎之间的相互转化

一、存储引擎

1、存储引擎其实就是如何实现存储数据,如何为存储的数据建立索引以及如何更新,查询数据等技术实现的方法。

2、MySQL中的数据用各种不同的技术存储在文件(或内存)中,这些技术中的每一种技术都使用不同的存储机制,索引技巧,锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

二、MySQL 中查看引擎

1、show  engines;   // 查看mysql所支持的存储引擎,以及从中得到mysql默认的存储引擎。

2、show  variables  like ‘% storage_engine’;    //    查看mysql 默认的存储引擎

3、show  create   table  tablename ;   //    查看具体某一个表所使用的存储引擎,这个默认存储引擎被修改了!

4、show  table   status  from  database  where  name=”tablename”

//准确查看某个数据库中的某一表所使用的存储引擎

三、MySQL中常用的几种存储引擎:innoDB、myisam、memory、BlackHole以及这几个引擎的讲解

innoDB存储引擎

(1) innodb存储引擎的mysql表提供了事务,回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

(2)innodb支持自增长列(auto_increment),自增长列的值不能为空,如果在使用的时候为空的话怎会进行自动存现有的值开始增值,如果有但是比现在的还大,则就保存这个值。

(3)innodb存储引擎支持外键(foreign key) ,外键所在的表称为子表而所依赖的表称为父表。

(4)innodb存储引擎最重要的是支持事务,以及事务相关联功能。

(5)innodb存储引擎支持mvcc的行级锁。

(6)innodb存储引擎索引使用的是B+Tree

mvcc类似于Java中的读锁具体介绍可以看这篇文章:http://blog.csdn.NET/chosen0ne/article/details/18093187

MyISAM存储引擎

1、MyISAM   这种存储引擎不支持事务,不支持行级锁,只支持并发插入的表锁,主要用于高负载的select。

2、MyISAM   类型的表支持三种不同的存储结构:静态型、动态型、压缩型。

(1)静态型:就是定义的表列的大小是固定(即不含有:xblob、xtext、varchar等长度可变的数据类型),这样mysql就会自动使用静态myisam格式。

使用静态格式的表的性能比较高,因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是有空间换来的,因为在定义的时候是固定的,所以不管列中的值有多大,都会以最大值为准,占据了整个空间。

(2)动态型:如果列(即使只有一列)定义为动态的(xblob, xtext, varchar等数据类型),这时myisam就自动使用动态型,虽然动态型的表占用了比静态型表较少的空间,但带来了性能的降低,因为如果某个字段的内容发生改变则其位置很可能需要移动,这样就会导致碎片的产生。随着数据变化的怎多,碎片就会增加,数据访问性能就会相应的降低。

对于因为碎片的原因而降低数据访问性,有两种解决办法:

@1、尽可能使用静态数据类型

@2、经常使用optimize   table语句,他会整理表的碎片,恢复由于表的更新和删除导致的空间丢失。

(如果存储引擎不支持 optimize  table    则可以转储并重新加载数据,这样也可以减少碎片)

(3)压缩型:如果在这个数据库中创建的是在整个生命周期内只读的表,则这种情况就是用myisam的压缩型表来减少空间的占用。

3、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

MEMORY存储引擎

(1)memory存储引擎相比前面的一些存储引擎,有点不一样,其使用存储在内从中的数据来创建表,而且所有的数据也都存储在内存中。

(2)每个基于memory存储引擎的表实际对应一个磁盘文件,该文件的文件名和表名是相同的,类型为.frm。该文件只存储表的结构,而其数据文件,都是存储在内存中,这样有利于对数据的快速处理,提高整个表的处理能力。

(3)memory存储引擎默认使用哈希(HASH)索引,其速度比使用B-+Tree型要快,如果读者希望使用B树型,则在创建的时候可以引用。

(4)memory存储引擎文件数据都存储在内存中,如果mysqld进程发生异常,重启或关闭机器这些数据都会消失。所以memory存储引擎中的表的生命周期很短,一般只使用一次。

BlackHole存储引擎(黑洞引擎)

(1)支持事务,而且支持mvcc的行级锁,主要用于日志记录或同步归档,这个存储引擎除非有特别目的,否则不适合使用!

四、各存储引擎相互转化

1、alter  table  tablename  engine = INnodb /MyISAM/Memory    //       修改了这个表的存储引擎

优点:简单,而且适合所有的引擎。

缺点:(1)、这种转化方式需要大量的时间 和I/O,mysql要执行从旧表 到新表的一行一行的复制所以效率比较低

(2)、在转化这期间源表加了读锁

(3)、从一种引擎到另一种引擎做表转化,所有属于原始引擎的专用特性都会丢失,比如从innodb到 myisam 则 innodb的索引会丢失!

2、使用dump(转储) import(导入)

优点:使用mysqldump这个工具将修改的数据导出后会以 .sql 的文件保存,你可以对这个文件进行操作,所以你有更多更好的控制, 如修改表名,修改存储引擎等!

看看导出的一个表:

以上就是使用mysqldump 导出来的表 即 一个 .sql 文件 你可以按照需求编写这个文件,在将这个文件导入就ok!

3、第一种方式简便,第二种方式安全,这第三种方式就算是前两种方式的折中吧, create  select:

(1)、 create  table   newtable  like  oldtable;

(2)、alter  table  newtable engine= innodb/ myisam / memory

(3)、insert  into  newtable  select * from  oldtable;

如果数据量不大的话这种方式还是挺好的!

还有更高效的办法就是 增量填充,在填充完每个增量数据块之后提交一次事务,这样就不会导致撤销日志文件过大;

(1)start  transaction

(2)insert into newtable  select *  from oldtable  where  id(主键) between x and y;

(3) commit

这样等数据填充之后有了需要的新表,旧表也存在,不需要的haunt可以删除,很方便!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql存储过程

    存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件。虽然他们的作用不仅限于批处理。

    Java架构师历程
  • MySQL 谈谈Memory存储引擎

    memory存储引擎是MySQL中的一类特殊的存储引擎。其使用存储在内存中的内容来创建表,而且所有数据也放在内存中。这些特性都与InnoDB,MyISAM存储引...

    Java架构师历程
  • 如何运行50k +并发用户的负载测试

    本文将介绍使用50k并发用户测试轻松运行负载测试所需的步骤(以及最多200万用户的更大测试)。

    Java架构师历程
  • python 多层装饰器分析

    装饰器本质上是一个Python函数,它可以让其他函数在不需要做任何代码变动的前提下增加额外功能,装饰器的返回值也是1函数对象。它经常用于有切面需求的场景,比如:...

    py3study
  • 找出没有主键的表

    MySQL中, 如果表没有主键的时候,会造成主从延迟。 因此我们需要找出没有主键的表,然后人工加个主键。

    二狗不要跑
  • 关于Memory存储引擎的知识点

    Memory存储引擎在日常的工作中使用的是比较少的,但是在MySQL的某些语法中,会用到memory引擎的内存表,它有以下几个特点:

    AsiaYe
  • Docker安装ElasticSearch解决ERROR: bootstrap checks failed

    在使用docker(docker-compose)部署Elasticseatch的时候,总是启动之后容器就自己退出了,查看log发现报错信息:

    行 者
  • VC+++ 操作word

    最近完成了一个使用VC++ 操作word生成扫描报告的功能,在这里将过程记录下来,开发环境为visual studio 2008

    Masimaro
  • mysql系列一

    学习mysql必备工具即安装mysql客户端;mysql安装教程在网上有很多,在此处就不在仔细说明;

    沁溪源
  • 脚本找出mysql中缺少主键的表

    MYSQL_CMD="mysql -u$user -h$host -p$pass -S$sock"

    二狗不要跑

扫码关注云+社区

领取腾讯云代金券