存储引擎MySQL和InnoDB(数据库管理与高可用)

1、存储引擎

存储引擎是核心组成部分,

是构成数据库最基础最底层的部件,

利用这个部件,你的Mysql能够对数据进行查询、创建、更新、删除等操作,

也就是说,用户所输入的一系列的mysql语句,是由存储引擎来执行的,

2、存储引擎的分类

注:innodb支持行级锁定

不同的存储引擎,它们在生成相关表的时候,都对应的有一个相关文件,

上图中的蓝色的三个文件是系统自带的,

然后,cd到mysql里面,ls查看一下,

注:上图圈中的三个文件共同构成了mysql中的一个表,

user.frm——存储了数据表表的结构(比如这个表有多少列,每个列有什么名字等)

user.MYD——存储了表里的每一行记录(数据)

user.MYI——存储了这个表的索引信息

上图的这种构成方式是由MyISAM引擎生成的文件,是有一个单独存放索引的文件的(这一点与Innodb引擎是不同的)

因为在安装mysql时,在它的配置文件里面指定的默认引擎是使用InnoDB,那就意味着

先登录进mysql里面,创建一个表,

use进这个auth库中,创建一个表,

然后退出mysql,在data里面查看一下,

注:在mysql里面,每一个库都是一个独立的目录,如果到这个库里面ls查看一下的话,

每一个表都会对应两个文件,既users.frm和users.ibd,而实际上ls查看到的是三个文件:

只要把库创建出来,上图中的opt这个文件就有了,

如果说,此时再去登录mysql里面创建一个表的话,

use进auth里面,创建001这个表:

然后退出mysql,再去使用ls查看一下:

而前面的opt文件还是那一个,没有多出来,

注:只要把库创建出来,opt这个文件就已经有了,

每一次创建表的时候会多出两个文件(.ibd和.ifm的文件)

opt文件(该库里所有的表共用的)存放的是该库的配置信息、编码、规则等,

.frm——存放的是元数据,即表的相关结构等信息,

.ibd——存放的是核心数据、索引信息,即表里的每一行记录是存放在.ibd里面的

上图中的这种结构是由InnoDB生成的文件,

小结:当想要去看一个库用的是什么存储引擎的时候,可以先cd到这个库的目录里面用ls查看一下都有哪些文件,大致就知道它用的是什么存储引擎了,因为每一种存储引擎所生成的文件是不一样的

在选择存储引擎的时候,如果说程序有事物的需求,就只能用InnoDB的引擎

3、修改存储引擎

(1)如何修改存储引擎?

同一个数据库里的不同的表,能否设置不同的存储引擎?

可以,存储引擎是针对表的,可以针对不同的表去设置不同的存储引擎,

方式一:

先use到库里面,

alter是修改表结构的语句,

然后可以去查看一下,现有的存储引擎变了没有,

修改完之后,退出mysql,再去ls一下这个auth目录,

方式二:还可以在配置文件/etc/my.cnf里面修改它的存储引擎的参数,改完之后再去重启一下mysqld服务,

(2)设置完引擎之后,如何查看当前的存储引擎?

注:\G表示格式化输出的意思,

在显示出来的页面里,找一下存储引擎是什么?

(3)如果想要在创建表的时候,就指定存储引擎呢?

所以,在创建表的时候,默认指定的存储引擎就是Innodb了,

如果你想要Innodb的存储引擎——那就在创建该表的时候,什么都不指定,就可以了

如果你想要用MyIsm的存储引擎——那就需要在创建表的时候,指定存储引擎,

注:在去设置存储引擎的时候,myisam的大小写是没有影响的

设置完之后,再去查看一下设置成功了没:

然后再去看一下,表t3的存储引擎是什么:

(4)如何在mysql里面,临时设置默认的存储引擎呢?

即不必去修改配置文件来设置存储引擎,也不必在创建表的时候去指定存储引擎,

然后再去创建一个表t3的时候,就不必在后面指定存储引擎了,

因为此时我们在配置文件里,指定的默认存储引擎是Inondb,那刚刚设置的临时默认存储引擎是否永久生效呢?

先退出mysql数据库,再登录进mysql里面,先use进auth这个库里面,

注:临时设置的默认存储引擎,只在mysql的命令行生效,一旦退出了连接,再登录进去的话,临时设置的就失效了

4、比较Innodb和Myisam这两种存储引擎,在处理数据方面的速度

测试读、写的速度:

需要创建出两个使用不同存储引擎的表,

先创建出测试所要用到的库,然后use到该库中:

创建两个结构相同的表,但是它们各自的存储引擎是不同的,

第一个表,用Myisam做存储引擎,

然后,用相同的语句把第二个表创建出来,

第二个表使用Innodb的存储引擎,

然后,可分别查看一下这两个表中的信息,是否设置成功了,

因为这两个表中,都给它们设置了id这一列为主键,那测试读的时候,就可以用索引去读,

然后,需要先在这两个表里面添加一些信息,

注:在表中数据量很少的情况下,测试读或写的性能,都没有任何可比性,需要写入大量数据才能测试出读、写的性能好坏,

这里可以使用一个脚本往这两个表里面,写数据

如何用脚本快速插入几百万行、上千万行的数据量?

即创建一个存储过程,然后利用这个存储过程去快速地执行这个存储过程所包含的脚本,

存储过程——存放在数据库服务器里面的一个脚本,只需要吊用这个脚本的名字就可以执行该脚本所有的语句了,

注:不要在创建存储过程的时候,用分号做结束符,

所以要先把存储过程的结束标识给改了,比如可以用$做Mysql语句的结束,

改好了之后,在用mysql去编辑存储过程的时候,编辑语句中,末尾就可以用$表示结束了,


create procedure insertm()

begin

set @i=1;

while @i

do

insert into tm(name) values(concat("zhangsan",@i));

set @i=@i+1;

end while;

end

$


利用上面的这个存储过程,就可以在tm这个表中添加1千万行记录,

create procedure inserti()

begin

set @i=1;

while @i

do

insert into ti(name) values(concat("zhangsan",@i));

set @i=@i+1;

end while;

end

$

先把上面的两个脚本复制一下,然后来到mysql的命令行里面,先把第一个存储过程给创建出来,给粘贴过来,

然后再把第二个存储过程给粘贴过来,

然后,就可以利用存储过程向两个表添加1千万行存储记录,

这时候,会消耗一段时间,但过程中不要Ctrl+C,只需要等,

然后来调用第二个存储过程,

注:从上图的结果耗费的时间去看的话,

下面要比较一下,两种存储引擎读的性能,

比较读(搜索)性能的时候,就要分为有索引和没索引两种情况了:

上面创建的这两个表都是以id那一列作为索引的,

(1)没索引的情况(不按id去搜索,按照name名字去搜索):

注:读的时候,要尽量确保读的量更大一些,才有可比性,

所以,无索引时,读取的性能里,myisam会更快一点,

(2)用带有索引去读的时候(拿id去搜索)

同样的量级别,再去搜索一下tm这个表,

所以,带索引的搜索时,innodb会更快一些,

  • 21
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值