mysql/mariadb知识点总结(18):存储引擎

  • A+
所属分类:mysql  数据库

这篇文章将会总结mysql中Innodb存储引擎与Myisam存储引擎的基本知识点。

 

在本博客中,"mysql"是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。

mysql文章列表直达链接:mysql知识点总结

 mysql/mariadb知识点总结(18):存储引擎

mysql中,存储引擎是插件式的,同一个数据库中的不同的表可以使用不同的存储引擎,所以,存储引擎是表级别的概念,存储引擎也被称为"表类型",每张表可以使用不同的存储引擎类型。

目前来说,mysql中最常用的存储引擎为innodb与myisam,每种存储引擎都有自己的特性,适用于不同的场景,那么,我们先来对比一下最常用的两种存储引擎的特性

MYISAM:支持表级锁,不支持行级锁,不支持事务,不支持外检约束,支持全文索引,表空间文件相对小。

INNODB:支持表级锁,行级锁,支持事务,支持外检,不支持全文索引,表空间文件相对较大。

 

除了上述最长用的两种存储引擎,其他各存储引擎类型的特性如下: 

CSV:将CSV文件(以逗号分隔字段的文本文件)作为MySQL表文件。

MRG_MYISAM此引擎也被称为MERGE存储引擎,如果一些myisam表的表结构完全相同,可以将这些MyISAM表合并成的一张MRG_MYISAM虚拟表。

BLACKHOLE:类似于/dev/null,不真正存储数据。

MEMORY:内存存储引擎,速度快,但是一旦断电数据将会丢失,支持hash索引,支持表级锁,常用于临时表。

PERFORMANCE_SCHEMA:从mysql5.5之后,多出了PERFORMANCE_SCHEMA数据库,PERFORMANCE_SCHEMA数据库中的表的表类型均为PERFORMANCE_SCHEMA,此数据库用于存储与数据库的性能相关的信息,用户无法创建使用这种存储引擎的表,但是dba可以通过PERFORMANCE_SCHEMA数据库中的信息进行性能分析,PERFORMANCE_SCHEMA数据库服务启动后此库中将不断的收集数据,mysql停机后此库中的表将不存在数据,类似于linux中的/proc。

FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口。

ARCHIVE: 见名知义,创建此种表类型的表往往用于存储归档信息、安全审计信息、历史信息等,创建数据仓库时,可能会用到此种表类型,使用archive表类型的表只支持select和insert操作,不能更新和删除操作,支持行级锁。

FEDERATED利用federated引擎可将本地数据表映射至远程 MySQL 数据表,从而就可以解决应用程序中繁多的跨机器连接数据库问题,其实federated相当于一个访问其他远程mysql server的代理接口,它通过创建一个到远程mysql server的客户端连接,通过FEDERATED引擎创建的表只是在本地有表定义文件,数据文件则存在于远程数据库中。

 

大致了解了存储引擎的相关知识后,我们来了解一下怎样管理mysql中的存储引擎。

查看表类型,查看存储引擎

在mysql中,应该怎样查看搜索引擎的相关信息呢?可以通过如下语句查看默认的存储引擎。

show engines;

mysql/mariadb知识点总结(18):存储引擎

 

我们还能够通过查看创建表语句的方法,查看表的存储引擎,当然,如果创建表的时候没有指定特定的存储引擎,则使用当时默认的存储引擎类型,如果后来修改了表的表类型,此处显示的表类型也将会是最新的。

mysql/mariadb知识点总结(18):存储引擎

 

我们也可以一次性查看某个数据库中所有表中都使用那些存储引擎,示例如下,zsythink数据库中的students表与t1表使用的都是innoDB的存储引擎。

mysql/mariadb知识点总结(18):存储引擎

 

或者单独查看某张表的表类型。

mysql/mariadb知识点总结(18):存储引擎

 

设置存储引擎

之前已经说过,存储引擎就是表类型,我们可以在创建表的时候就指定当前表使用哪种存储引擎。

mysql/mariadb知识点总结(18):存储引擎

 

修改表的存储引擎/修改默认表类型

修改存储引擎的语句其实就是修改表的语句,但是在已经存在数据的情况下,不要随意的修改存储引擎,除非你确定你在干什么,示例如下。

mysql/mariadb知识点总结(18):存储引擎

上述方法可以修改表的表类型。

 
 

如果想要修改默认的存储引擎,可以使用如下方法。以centos6.8中的mysql为例,centos6.8中mysql的默认版本为5.1.73

mysql/mariadb知识点总结(18):存储引擎

 
 

5.1.73版本mysql其默认的存储引擎为MyISAM,假设我们想要将默认的存储引擎改为InnoDB。

mysql/mariadb知识点总结(18):存储引擎

 
 

首先停止mysql服务。

mysql/mariadb知识点总结(18):存储引擎

 

服务停止后,修改my.cnf配置文件。

mysql/mariadb知识点总结(18):存储引擎

在配置文件中加入 default-storage-engine=innodb 即可,当然,为了方便日后管理,还可以开启innodb_file_per_table,表示每表使用单独的表空间,如下图所示,配置完成后保存退出即可。

mysql/mariadb知识点总结(18):存储引擎

 

按照上述配置后,重新启动mysql服务即可。

mysql/mariadb知识点总结(18):存储引擎

 

再次查看存储引擎的信息,innodb已经成为了默认的存储引擎。

mysql/mariadb知识点总结(18):存储引擎

默认存储引擎修改完毕。

 

innodb与myisam的数据文件

从刚才的my.cnf配置文件可以看出,我们的表空间数据文件存放路径如下。

datadir=/var/lib/mysql

 

进入到上述目录中,可以看到一个名为ibdata1的文件。

mysql/mariadb知识点总结(18):存储引擎

 

当在使用innodb存储引擎时,如果在配置文件中没有开启innodb_file_per_table,默认情况下,所有库中的所有使用innodb存储引擎的表的数据都存储在此文件中,换句话说,默认情况下,ibdata1为所有innodb表共享的表空间数据文件,表的数据信息与索引信息都存储与ibdata1中,但是,如果在my.cnf中开启了innodb_file_per_table,情况会发生改变,如果设置了此参数,则表示每个innodb表类型的表使用单独的表空间数据文件。

现在,我们先看一下目前都有哪些数据库。

mysql/mariadb知识点总结(18):存储引擎

从数据存放目录可以看出,目前有三个数据库,每个数据库使用单独的目录存放对应的数据文件,此处,我们进入zabbix目录,zabbix数据库中的所有表都是用的是innodb类型的存储引擎,我们看看innodb表类型的表都是用哪种格式的表空间文件,如下图所示。

mysql/mariadb知识点总结(18):存储引擎

可以看到,每张表都有两种后缀名的文件,分别为frm后缀与ibd后缀。

frm后缀的文件中存储了表的表结构信息。

ibd后缀的文件中存放了表的数据信息与索引信息。

如果在配置文件中没有开启innodb_file_per_table参数,在数据库对应的目录中则不会出现 .ibd 为后缀的文件,因为默认情况下会共用ibdata1,如果在一开始没有开启innodb_file_per_table,并且已经存在某些使用innodb存储类型的表,那么这些表将共用ibdata1,如果这时,又开启了innodb_file_per_table,那么原来的表的数据仍然存在于ibdata1中,新创建的表才会使用单独的以.ibd为后缀的表空间文件。

 

看完了innodb表类型的表空间文件,再看看myisam表类型的表空间文件,默认的"mysql库"中的大部分表都使用了MyISAM的表类型,那么我们进入mysql目录看看。

mysql/mariadb知识点总结(18):存储引擎

从上图中可以看出,如果表使用了myisam类型的存储引擎,每张表都有三种后缀名的文件,分别是.frm后缀、.MYD后缀、.MYI后缀。

frm后缀的文件中存放了表结构信息。

MYD后缀的文件中存放了数据信息。

MYI后缀的文件中存放了索引信息。

 

weinxin
我的微信公众号
关注"实用运维笔记"微信公众号,当博客中有新文章时,可第一时间得知哦~
朱双印

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:7   其中:访客  5   博主  2

    • avatar 半杯湖底沙 1

      跪求朱哥出一个nginx的详细教程,配上各种案例 [渴望的小眼神],感谢大神

      • avatar 小土豆 4

        深入了解,详细

        • avatar 随心 2

          之前拷贝表文件,很纳闷表为什么有三个文件,,看了这篇文章,豁然开朗

          • avatar 妞妞 4

            循序渐进 。
            可以出书啦。
            老太太过马路我都不扶就服你。 :mrgreen:

              • avatar 朱双印 Admin

                @妞妞 客官幽默, :mrgreen: 哈哈~~
                能帮助到你就好,常来常往呦~~

              • avatar echo 6

                虽然不太懂 先把笔记记下了再说

                  • avatar 朱双印 Admin

                    @echo 坚持就是胜利,兄弟加油!~