Loading... 这篇文章将会总结mysql中Innodb存储引擎与Myisam存储引擎的基本知识点。 在本博客中,”mysql”是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。 mysql文章列表直达链接:<a href="https://www.zsythink.net/archives/tag/mysql/" target="_blank" rel="noopener">mysql知识点总结</a> <img class=" ls-is-cached lazyloaded" title="1480776820928760.jpg" src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" alt="mysql.jpg" data-src="https://www.zsythink.net/wp-content/uploads/ueditor/php/upload/image/20161203/1480776820928760.jpg" style=""> mysql中,存储引擎是插件式的,同一个数据库中的不同的表可以使用不同的存储引擎,所以,存储引擎是表级别的概念,存储引擎也被称为”表类型”,每张表可以使用不同的存储引擎类型。 目前来说,mysql中最常用的存储引擎为innodb与myisam,每种存储引擎都有自己的特性,适用于不同的场景,那么,我们先来对比一下最常用的两种存储引擎的特性 <strong>MYISAM</strong>:支持表级锁,不支持行级锁,不支持事务,不支持外检约束,支持全文索引,表空间文件相对小。 <strong>INNODB</strong>:支持表级锁,行级锁,支持事务,支持外检,不支持全文索引,表空间文件相对较大。 除了上述最长用的两种存储引擎,其他各存储引擎类型的特性如下: 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中的存储引擎。 <h2 id="wznav_0">查看表类型,查看存储引擎</h2> 在mysql中,应该怎样查看搜索引擎的相关信息呢?可以通过如下语句查看默认的存储引擎。 show engines; <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_1.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_1.png" style=""> 我们还能够通过查看创建表语句的方法,查看表的存储引擎,当然,如果创建表的时候没有指定特定的存储引擎,则使用当时默认的存储引擎类型,如果后来修改了表的表类型,此处显示的表类型也将会是最新的。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_2.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_2.png" style=""> 我们也可以一次性查看某个数据库中所有表中都使用那些存储引擎,示例如下,zsythink数据库中的students表与t1表使用的都是innoDB的存储引擎。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_3.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_3.png" style=""> 或者单独查看某张表的表类型。 <img class="lazyload" src="https://www.zsythink.net/wp-content/themes/zibll/img/thumbnail-lg.svg" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_4.png" style=""> <h2 id="wznav_1">设置存储引擎</h2> 之前已经说过,存储引擎就是表类型,我们可以在创建表的时候就指定当前表使用哪种存储引擎。 <img class="lazyload" src="https://www.zsythink.net/wp-content/themes/zibll/img/thumbnail-lg.svg" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_5.png" style=""> <h2 id="wznav_2">修改表的存储引擎/修改默认表类型</h2> 修改存储引擎的语句其实就是修改表的语句,但是在已经存在数据的情况下,不要随意的修改存储引擎,除非你确定你在干什么,示例如下。 <img class="lazyload" src="https://www.zsythink.net/wp-content/themes/zibll/img/thumbnail-lg.svg" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_6.png" style=""> 上述方法可以修改表的表类型。 如果想要修改默认的存储引擎,可以使用如下方法。以centos6.8中的mysql为例,centos6.8中mysql的默认版本为5.1.73 <img class="lazyload" src="https://www.zsythink.net/wp-content/themes/zibll/img/thumbnail-lg.svg" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_7.png" style=""> 5.1.73版本mysql其默认的存储引擎为MyISAM,假设我们想要将默认的存储引擎改为InnoDB。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_8.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_8.png" style=""> 首先停止mysql服务。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_9.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_9.png" style=""> 服务停止后,修改my.cnf配置文件。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_10.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_10.png" style=""> 在配置文件中加入 default-storage-engine=innodb 即可,当然,为了方便日后管理,还可以开启innodb_file_per_table,表示每表使用单独的表空间,如下图所示,配置完成后保存退出即可。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_11.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_11.png" style=""> 按照上述配置后,重新启动mysql服务即可。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_12.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_12.png" style=""> 再次查看存储引擎的信息,innodb已经成为了默认的存储引擎。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_13.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_13.png" style=""> 默认存储引擎修改完毕。 <h2 id="wznav_3">innodb与myisam的数据文件</h2> 从刚才的my.cnf配置文件可以看出,我们的表空间数据文件存放路径如下。 datadir=/var/lib/mysql 进入到上述目录中,可以看到一个名为ibdata1的文件。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_14.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_14.png" style=""> 当在使用innodb存储引擎时,如果在配置文件中没有开启innodb_file_per_table,默认情况下,所有库中的所有使用innodb存储引擎的表的数据都存储在此文件中,换句话说,默认情况下,ibdata1为所有innodb表共享的表空间数据文件,表的数据信息与索引信息都存储与ibdata1中,但是,如果在my.cnf中开启了innodb_file_per_table,情况会发生改变,如果设置了此参数,则表示每个innodb表类型的表使用单独的表空间数据文件。 现在,我们先看一下目前都有哪些数据库。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_15.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_15.png" style=""> 从数据存放目录可以看出,目前有三个数据库,每个数据库使用单独的目录存放对应的数据文件,此处,我们进入zabbix目录,zabbix数据库中的所有表都是用的是innodb类型的存储引擎,我们看看innodb表类型的表都是用哪种格式的表空间文件,如下图所示。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_16.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_16.png" style=""> 可以看到,每张表都有两种后缀名的文件,分别为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目录看看。 <img class=" lazyloaded" src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_17.png" alt="" data-src="https://www.zsythink.net/wp-content/uploads/2017/01/011617_0434_17.png" style=""> 从上图中可以看出,如果表使用了myisam类型的存储引擎,每张表都有三种后缀名的文件,分别是.frm后缀、.MYD后缀、.MYI后缀。 frm后缀的文件中存放了表结构信息。 MYD后缀的文件中存放了数据信息。 MYI后缀的文件中存放了索引信息。 转载自朱双印日志https://www.zsythink.net/archives/1131 Last modification:May 29, 2024 © Allow specification reprint Like 如果觉得我的文章对你有用,请随意赞赏