互联网常识：InnoDB的数据存储文件和MyISAM的不同

导读跟大家讲解下有关InnoDB的数据存储文件和MyISAM的不同，相信小伙伴们对这个话题应该也很关注吧，现在就为小伙伴们说说InnoDB的数据存储

跟大家讲解下有关InnoDB的数据存储文件和MyISAM的不同，相信小伙伴们对这个话题应该也很关注吧，现在就为小伙伴们说说InnoDB的数据存储文件和MyISAM的不同，小编也收集到了有关InnoDB的数据存储文件和MyISAM的不同的相关资料，希望大家看到了会喜欢。

MySQL教程栏目介绍的索引为什么用B+Tree

前言

这篇文章的题目是我真实在面试过程中遇到的问题某互联网众筹公司在考察面试者MySQL相关知识的第一个问题我当时还是比较懵的没想到这年轻人不讲武德不按套路出牌一般的问MySQL的相关知识的时候不都是问索引优化以及索引失效等相关问题吗怎么还出来了存储文件的不同哪怕考察个MVCC机制也行啊。所以这次我就好好总结总结这部分知识点。

为什么需要建立索引

首先我们都知道建立索引的目的是为了提高查询速度那么为什么有了索引就能提高查询速度呢 我们来看一下一个索引的示意图。如果我有一个SQL语句是：lect * from Table where id = 15那么在没有索引的情况下其实是警示教育观后感会进行全表扫描的就是挨个去找直到找到id=15的这条记录时间复杂度是O(n)；

如果在有索引的情况下去进行查询呢。首先会根据id=15在索引值里面进行二分查找二分查找的效率是很高的它的时间复杂度是O(logn)；

这就是索引为什么能提高查询效率了但是索引数据的量也是比较大的所以一般并不是存储在内存中的都是直接存储在磁盘中的所以对磁盘中的文件内容进行读取免不了要进行磁盘IO。

MySQL的索引为什么使用B+Tree

上面我们也说了索引数据一般是存储在磁盘中的但是计算数据都是9月3日是什么日子要在内存中进行的如果索引文件很大的话并不能一次都加载进内存所以在使用索引进行数据查找的时候是会进行多次磁盘IO将索引数据分批的加载到内存中因此一个好的索引考试反思怎么写的数据结构在得到正确的结果前提下一定是磁盘IO次数最少的。

Hash类型

目前MySQL其实是有两种索引数据类型可以选择的一个是BTree（实际是B+Tree）、一个Hash。

但是为什么在实际的使用过程中基本上大部分都是选择BTree呢

因为如果使用Hash类型的索引MySQL在创建索引的时候会对索引数据进行一次Hash运算这样根据Hash值就能快速的定位到磁盘指针了就算数据量很大也能快速精准的定位到数据。

但是像
lect * from Table where id > 15这种范围查询Hash类型的索引就搞不定了对这种范围查询会直接全表扫描另外Hash类型的索引也搞不定排序。还有就是虽然MySQL底层做了一系列的处理但还是不能完全的保证不产生Hash碰撞。

二叉树

那MySQL为什么没有二叉树作为它的索引数据结构呢我们都知道二叉树是通过二分查找来进行定位数据的所以效果还是不错的时间复杂度是O(logn)；但是二叉树有个问题就是在特殊情况下它会退化成一根棍子也就是一个单向链表。这个时候它的时间复杂度就会退化成O(n)；所以当我们要查询id=50的记录时其实和全表扫描是一样的了。所以因为存在这种情况二叉树不适合作为索引的数据结构。

平衡二叉树

那么既然二叉树在特殊情况下会退化成链表那么平衡二叉树为什么不可以呢

平衡二叉树的子节点高度差不能超过1像下图中的二叉树关键字为15的节点它的左子节点高度为0右子节点高度为1高度差不超过1所以下面这棵树是一棵平衡二叉树。因为能保持平衡所以它的查询时间复杂度为O(logN)至于怎么保持平衡的主要是做一些左旋右旋等具体保持平衡的细节不是本文主要内容想了解的可自行搜索。

用这个数据结构来做MySQL的索引会有 什么问题呢

磁盘IO过多：在MySQL当中一次IO操作只读取一个节点那么一个节点若是最多就两个子节点的话那么就只有这两个子节点的查询范围所以要精确到具体的数据时就需要进行多次读取如果树非常深的话那么将会进行大量的磁盘IO。性能自然下降了。空间利用率低：对于平衡二叉树来说每个节点值保存一个关键字一个数据区两个子节点的指针。这样导致了一次辛辛苦苦的IO操作就只加载这么点数据实在是有点杀鸡用牛刀了。查询效果不稳定：如果在一个高度很深的平衡二叉树中若是查询的数据正好是根节点那么就会很快的查到若是查询的数据正好是叶子节点那么会进行多次磁盘IO后才能返回响应时间有可能和根节点的不在一个数量级上。

虽然说二叉树解决的平衡的问题但是也带来了新的问题那就是由于它本身树的深度的会造成一系列的效率问题。

那么为了解决平衡二叉树的这类问题平衡多叉树（Balance Tree）就成为了更好的选择。

平衡多叉树（Balance Tree–B-Tree）

B-Tree的意思是平衡多叉树一般B-Tree中的一个节点有多少个子节点我们就称为多少阶的B-Tree。通常用m表示阶数当m为2的时候就是平衡二叉树。

一棵B-Tree的每个节点上最多能有m-1个关键字最少要存放Math.ceil(m/2)-1个关键字所有的叶子节点都在同一层。如下图就是一个4阶的B-Tree。那么我们看一下B-Tree是如何进行查找数据的：

若是查询id=7的数据先将关键字20的节点加载进内存判断出7比20小；那么加载第一个子节点若查询的数据等于12或17则直接返回不等于就继续向下找发现7小于12；那么继续加载第一个子节点中去找到7之后直接将7下面的data数据返回。

这样整个操作其实进行了3次IO操作但实际上一般的B-Tree每层都是有很多分支（通常都大于100）。

MySQL为了能更好的利用磁盘的IO能力将操作页的大小设置为了16K即每个节点的大小为16K。如果每个节点中的关键字都是int类型的那么就是4个字节若数据区的大小为8个字节节点指针再占4个字节那么B-Tree的每个节点中可以保存的关键字个数为：(16*1000) / (4+8+4)=1000每个节点最多可存储1000个关键字每一个节点最多可以有1001个分支节点。

这样在查询索引数据的时候一次磁盘IO操作可以将1000个关键字读取到内存中进行计算B-Tree的一次磁盘IO的操作顶上平衡二叉数据的N次磁盘IO操作了。

要注意的是：B-Tree为了保证数据的平衡会做一系列的操作这个保持平衡的过程比较耗时间所以在创建索引的时候要选择合适的字段并且不要过多的创建索引创建索引过多的话在更新数据的时候更新索引的过程也比较耗时。

还有就是不要选择低区分度字段值作为索引例如性别字段总共就两个值那么就有可能会造成B-Tree的深度过大索引效率降低。

B+Tree

B-Tree已经很好的解决平衡二叉树的问题了并且也能保证查询效率了那么为什么会有B+Tree呢

我们先来B+Tree是什么样子的。

B+Tree是B-Tree的变种B+Tree的每个节点关键字和m阶的公式关系和B-Tree的不一样了。

首先每个节点的子节点数量和每个节点可存储的关键字比例是1:1其次就是查询数据的时候采用的是左闭合区间进行查询还有就是分支节点中没有数据了只保存关键字和子节点指向数据都存储在叶子节点。那么来看一下在B+Tree中是如何进行数据查询的。

例如：

现在要查询id=2的数据那么会先将根节点取出加载到内存中发现
id=2存在于根节点因为是左闭合区间存储数据所以
id<=2的都在根节点的第一个子节点上；那么取出第一个子节点加载到内存中发现当前节点存在
id=2的关键字并且已经到了叶子节点了那么直接取出叶子节点中的数据返回。

现在来看一下B-Tree和B+Tree的区别

B+Tree的查询采用的左闭合区间这样能更好的支持了自增索引的查询效果所以一般在创建主键的时候通常都是自增的。这一点和B-Tree是不一样的。B+Tree中的根节点和分支节点上是不保存数据的关键字相关的数据只保存在叶子节点上这样保证了查询效果的稳定任何查询都要走到叶子节点才能获取数据。而B-Tree在分支节点中保存了数据若是命中关键字则直接返回数据。B+Tree的叶子节点是顺序排列的并且相邻的两个叶子节点中具有顺序引用的关系这样能更好的支持了范围查询。而B-Tree是没有这个顺序关系的。MySQL的索引为什么选择了B+Tree

经过上面的层层分析现在我们可以总结一下MySQL为什么选择了B+Tree作为它索引的数据结构呢。

首先和平衡二叉树相比B+Tree的深度更低节点保存关键字更多磁盘IO次数更少查询计算效率更好。

B+Tree的全局扫描能力更强若是想根据索引数据对数据表进行全局扫描B-Tree会将整棵树进行扫描然后逐层遍历。而B+Tree呢只需要遍历叶子节点即可因为叶子节点之间存在顺序引用的关系。

B+Tree的磁盘IO读写能力更强因为B+Tree的每个分支节点上只保存了关键字这样每次磁盘IO在读写的时候一页16K数据量可以存储更多的关键字了每个节点上保存的关键字也比B-Tree更多了。这样B+Tree的一次磁盘IO加载的数据比B-Tree的多很多了。

B+Tree数据结构中有天然的排序能力比其他数据结构排序能力更强而且排序时是通过分支节点来进行的若是需要将分支节点加载到内存中排序一次加载的数据更多。

B+Tree的查询效果更稳定因为所有的查询都是需要扫描到叶子节点才将数据返回的。效果只是稳定而不一定是最优若是直接查询B-Tree的根节点数据那么B-Tree只需要一次磁盘IO就可以直接将数据返回反而是效果最优。

经过以上几点的分析MySQL最终选择了B+Tree作为了它的索引的数据结构。

InnDB的数据存储文件和MyISAM的有何不同

上面总结了MySQL的索引的数据结构这次就可以说第二个问题了因为这个问题其实和MySQL的索引还是有一定的关系的。 下面来看一下先找到服务器桑MySQL存储数据的目录： 登录MySQL打开MySQL的命令行界面：输入show variables like '%datadir%';就能看到存储数据的目录了。 我的服务器中MySQL的存储数据的目录是在：

/var/lib/mysql/

进入到这个目录里后能看到所有数据库的目录新建一个study_test的数据库。 然后就进入

/var/lib/mysql/study_test

这个目录下目前就只有一个文件这个文件是用来记录创建数据库时配置的字符集的内容。

-rw-r----- 1 mysql mysql 60 1月 31 10:28 db.opt

现在新建两个表第一个表的引擎类型选择InnoDB第二个表的引擎类型选择MyISAM。

student_innodb：

CREATE TABLE `student_innodb` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE COMMENT 'name索引') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='innodb引擎表';

student_myisam：

CREATE TABLE `student_myisam` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8mb4_bin DEFAULT中国好声音好听的歌有哪些 NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE COMMENT 'name索引') ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='myISAM引擎类型表';

将两个表创建完成后我们再进入到/var/lib/mysql/study_test看一下：

-rw-r----- 1 mysql mysql 60 1月 31 10:28 db.opt-rw-r----- 1 mysql mysql 8650 1月 31 10:41 student_innodb.frm-rw-r----- 1 mysql mysql 114688 1月 31 10:41 student_innodb.ibd-rw-r----- 1 mysql mysql 8650 1月 31 10:58 student_myisam.frm-rw-r----- 1 mysql mysql 0 1月 31 10:58 student_myisam.MYD-rw-r----- 1 mysql mysql 1024 1月 31 10:58 student_myisam.MYI

通过目录中的文件可看到创建表之后多了几个文件这样也看出来了InnoDB引擎类型的表和MyISAM引擎类型的表的文件差异。

这几个文件每个都是有自己的作用：

InnoDB引擎的表文件一共有两个：*.frm 这类文件是表的定义文件。*.ibd 这类文件是数据和索引存储文件。表数据和索引聚集存储通过索引能直接查询到数据。MyIASM引擎的表文件一共有三个：*.frm 这类文件是表的定义文件。*.MYD 这类文件是表数据文件表中的所有数据都保存在此文件中。*.MYI 这类文件是表的索引文件MyISAM存储引擎的索引数据单独存储。

MyISAM数据存储引擎索引与数据的存储结构

MyISAM存储引擎在存储索引的时候是将索引数据单独存储并且索引的B+Tree最终指向的是数据存在的物理地址而不是具体的数据。然后再根据物理地址去数据文件（*.MYD）中找到具体的数据。

如下图所示： 那么当存在多个索引时多个索引都指向相同的物理地址。 如下图所示：通过这个结构我们可以看出来MyISAM的存储引擎的索引都是同级别的主键和非主键索引结构和查询方式完全一样。

InnoDB数据存储引擎索引与数据的存储结构

首先InnoDB的索引分为聚簇索引和非聚簇索引聚簇索引即保存关键字又保存数据在B+Tree的每个分支节点上保存关键字叶子节点上保存数据。 “聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。一个表只能有一个聚簇索引因为在一个表中数据的存放方式只有一种一般是主键作为聚簇索引如果没有主键InnoDB会默认生成一个隐藏的列作为主键。

如下图所示：非聚簇索引又称为二级索引虽然也是在B+Tree的每个分支节点上保存关键字但是叶子节点不是保存的数据而是保存的主键值。通过二级索引去查询数据会先查询到数据对应的主键然后再根据主键查询到具体的数据行。

如下图所示：由于非聚簇索引的设计结构导致了非聚簇索引在查询的时候要进行两次索引检索这样设计的好处可以保证了一旦发生数据迁移的时候只需要更新主键索引即可非聚簇索引并不用动而且也规避了像MyISAM的索引那样存储物理地址在数据迁移的时候的需要重新维护所有索引的问题。

总结

这次把MySQL的索引的数据结构以及文件存储结构总结清楚了后面在实际的工作过程中设计索引的时候能够考虑的更全了通过了解了索引的数据结构也能让自己在实际写SQL的时候能考虑到哪些情况走索引哪些不走索引了。

MySQL使用B+Tree作为索引的数据结构因为B+Tree的深度低节点保存的关键字多磁盘IO次数少从而保证了查询效率更高。B+Tree能够保证MySQL无论是主键索引还是非主键索引的查询效果都是稳定的每次都要查询到叶子节点才能返回数据B+Tree的叶子节点的深度是一样的而且为了更好的支持自增主键B+Tree的查询节点范围是左闭合右开放。MySQL的MyISAM存储引擎表数据和索引数据是分别放到两个文件中进行存储的由于它本身的索引的B+Tree的叶子节点指向的表数据所在的磁盘地址而且索引没有主键和非主键之分所以分开存储能够更好的统一管理索引；MySQL的InnoDB存储引擎表数据和索引数据是存储在一个文件中的因为InnoDB的聚簇索引的叶子节点指向的具体的数据行而且为了保证查询效果的稳定InnoDB表中必须要有一个聚簇索引二级索引在进行索引检索时会先通过二级索引检索到数据的主键值再根据主键去聚簇索引中检索到具体的数据。

相关免费学习推荐：mysql视频教程

以上就是InnoDB的数据存储文件和MyISAM的不同的详细内容！

来源：php中文网