说说关于MySQL的存储引擎的那些事!

·  阅读 269
说说关于MySQL的存储引擎的那些事!

这是我参与8月更文挑战的第11天,活动详情查看:8月更文挑战

| 作者:江夏

| CSDN:blog.csdn.net/qq_41153943

| 掘金:juejin.cn/user/651387…

| 知乎:www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

本文大概1165字,建议阅读9分钟

一、概念

和大多数的数据库不同,MySQL中有一个存储引擎的概念,针对不同的存储需求可以选择最优的存储引擎。存储引擎就是存储数据,建立索引,更新查询数据等技术的实现方式。存储引擎就是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。

对于存储引擎的概念百度百科是这样介绍的:MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。

通俗点说也就是同一个数据库如果使用不同的功能(比如存储文件的类型、是否需要支持事务等等),则需要选择对应的存储引擎,来提升存储的性能。这里的存储引擎就好比发动机,如果是一辆轿车(数据库),平时只是上下班用来跑通勤,那么普通的发动机引擎(存储引擎)即可,但是也有发烧友喜欢改装汽车,给它装上跑车的发动机引擎,这样的话功能和用途又是不一样的。

Orcal、SqlServer等数据库只有一种存储引擎,而MySQL提供了插件式的存储引擎架构,所以MySQL支持多种存储引擎,可以根据需要使用相应的引擎,或者开发者可以自己编写存储引擎。

MySQL5.0开始支持的存储引擎有很多,比如:InnoDB、BDB、Memory、Merge、Example、Archive 、CSV,Blackhole、Federated等等,其中InnoDBh和BDB是提供事务安全表,而其他的存储引擎是非事务安全表。

可以通过下面的命令来查看当前数据库支持的存储引擎

查看支持的存储引擎
show engines;
查看默认的存储引擎
show variables like ‘storage_engine’
复制代码

图片

这里可以看到MySQL数据库默认支持的存储引擎是InnoDB,如果在创建新表的时候不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL5.5之前默认的存储引擎是MyISAM,5.5之后改成了InnoDB。

二、常见存储引擎的特性

对于MySQ来说常见的存储引擎有三种:InnoDB,MyISAM,Memory,下面注重介绍这三种存储引擎。

1、InnoDB存储引擎

InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键,MySQL默认使用的存储引擎就是InnoDB。InnoDB主要特征如下:

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。所以对比于MyISAM存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间用来保留数据和索引。

2、InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他Mysql的表类型混合起来,甚至在同一个查询中也可以混合。

3、InnoDB是为处理大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎所不能匹敌的

4、InnoDB存储引擎完全与Mysql服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维护它自己的缓存池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被现在为2GB的操作系统上。

5、MySQL支持外键完整性约束的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候,也会自动的创建对应的索引。并且在存储表中的数据时,每张表的存储都按主键存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键

6、InnoDB被用在众多需要高性能的大型数据库站点上。

7、InnoDB不创建目录,使用InnoDB时,MySQL将在Mysql数据目录下创建一个名为ibdata1的10M大小的自动扩展数据文件,以及两个名为ib_logfile()和lib_logfile1的5M大小的日志文件

8、InnoDB存储表和索引有两种方式:

  • 使用共享表空间存储,这种方式创建的表的表结构存储在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件;

  • 使用多表空间存储,这种方式创建的表的表结构依然存在.frm文件中,但是每个表的数据和索引单独保存在.ibd中。

2、MyISAM存储引擎

MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事务和外键。MyISAM主要特征有:

1、大文件(达到63位文件长度)在支持大文件的文件系统和操作系统上被支持。

2、当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块,以及若下一个块被删除,就扩展到下一块自动完成

3、每个MyISAM表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16

4、BLOB和TEXT列可以被索引

5、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

6、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将刷新。所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCERMENT更快

使用MyISAM引擎创建数据库,将产生3个文件。文件的名称以表名字开始,扩展名之处文件类型:frm文件存储表定义、数据文件的扩展名为MYD(MYData)、索引文件的扩展名时,MYI(MYIndex)

3、MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。MEMORY主要特性有:

1、MEMORY表的每个表可以有多达32个索引,每个索引16列,以及500字节的最大键长度

2、MEMORY存储引擎执行HASH和BTREE索引

3、可以在一个MEMORY表中有非唯一键值

4、MEMORY表使用一个固定的记录长度格式

5、MEMORY不支持BLOB或者TEXT列

6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

7、MEMORY表在所有客户端之间共享(就像其他任何非TEMPORARY表)

8、MEMORY表被存储在内存中,内存是MEMORY表和服务器在查询处理时的空闲中,创建的内部表共享。服务一旦关闭,表中的数据就会丢失。

9、当不再需要MEMORY表的内容时,要释放被MEMORY表使用的内存,应该执行DELETE FROM或TRUNCATE TABLE,或者删除整个表(使用DROP TABLE)

将这三种存储引擎的主要特征总结成图表,如下:

特征
InnoDB
MyISAM
Memory
事务安全机制
支持
不支持
不支持
锁机制
行级锁(适用于高并发)
表锁
表锁
支持的索引
B树、全文、集群、数据索引
B树、全文索引
B树、哈希、数据索引
索引缓存
支持
支持
支持
存储限制
64TB
256TBRAM
批量插入速度


支持外键
支持
不支持
不支持
内存使用


一般
空间使用



三、存储引擎的选择

根据应用系统的特点选择合适的存储引擎,能够大大提高数据存储和访问的速度已经数据安全性。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合,以下是常用的集中存储引擎的使用环境。

InnoDB:InnoDB是MySQL的默认存储引擎,用于事务处理应用程序,支持外键,所以如果应用对于事务的完整性的要求比较高,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包含很多的更新、删除操作,那么应该选择InnoDB存储引擎比较好。InnoDB存储引擎除了有效的降低由于删除和更新导致的行锁定,还可以确保事务的完整提交和回滚。

MyISAM:如果应用是以读操作和插入操作为主,而只有很少的更新和删除操作,并且对于事务的完整性、并非性能的要求并不高,那么可以选择MyISAM存储引擎。

Memory:Memory存储引擎将所以的数据保证在内存中,在需要快速定位记录和其他类似数据环境下,可以提供很快的访问速度。Memory存储引擎的缺陷就是对于表的大小有限制,太大的表会无法缓存在内存中,其次就是要确保数据库异常终止之后表中的数据可以恢复。所以Memory存储引擎一般适用于不太频繁更新的小表,这样能够快速的获取到访问结果。

图片 图片 图片

四、总结

以上就是关于MySQL的存储引擎的介绍。只有熟练的了解了关于数据库存储引擎的底层概念,这样在开发中我们才能够更好的使用数据库,有助于提高系统的性能。

有任何问题或者不正确的地方欢迎讨论指正!

今日推荐

你还不知道什么是数据库的索引吗

面试题系列之String,Stringbuffer,StringBuilder的区别(源码分析)

你知道String类为什么不能被继承吗?

SpringBoot整合Druid配置数据源监控

Spring注解(五):容器注册组件的四种方式

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改