mysql 之引擎与索引的关系

两种常用引擎

MyISAM与InnoDB是两种最常见的Mysql引擎。

而他们之所以不同的大部分原因还是在索引和锁机制，因为他们的这两点不同，会导致很多性能上的不同，甚至表结构都会根据这两种引擎的特性来做相应的改变。

题外话

在谈及索引之前，有点题外话，也是必要的，为了下面的内容做铺垫。

那就是Mysql的三层结构。

第一层为客户端，当你安装的时候，默认会带一个Mysql控制台版的客户端。

第二层为DBMS(Database Management System)，数据库管理系统，实际上我们是与这个中间层做交互，而不是数据本身(说到这里你能想到set names utf8是对哪些进行操作了吗？)，这玩意就像是车的发动机，是数据库的核心。

第三层为数据本身，根据数据库表的引擎不同，分别以各种形式存储在磁盘上，等待DBMS的调度。

MyISAM

在很久以前，创建表时，如果不指明engine,表的默认引擎就是MyISAM。现在的是InnoDB了。

数据存储形式

如何查看数据存在哪？打开客户端，键入以下命令:

show variables where Variable_name ='datadir';

我的是/usr/local/var/mysql，打开后会发现很熟悉，那都是你的库名字。

选择一个拥有MyISAM引擎表的库(文件夹)，打开会发现有很多文件，找到那张MyISAM引擎的表名，你能找到三个不同后缀的文件。

分别是.frm,.MYD,.MYI。就是这三个文件组成了你的那张表，这三个不同的文件有着不同的作用，但都是描述同一张表的(可以看成是一本书)。

.frm,里面记录着表的信息，包含创建时间，表名等(书的封面，前言，介绍)。

.MYD,为这张表的所有数据内容如(书的内容)。

.MYI，这里记录着这张表的所有索引(书的目录)。

从数据存储结构上来看，MyISAM引擎的索引，是属于聚簇索引。聚簇索引也决定在大多数情况下，通过索引查找数据，是需要回行的(后面说)。

索引结构与一般查找流程

它的索引是一种查找树 B+Tree (更好区间查询能力，因为在子节点中还增加了相邻节点的顺序访问指针),当需要使用到索引时(假设为主键)，程序便会找到这个表的索引文件。

在这种树下查找数据非常快速，如果使用 int 类型作为主键，那么通过树查找(类似于二分查找)，最多只需要找 32 次。

当从索引中找到主键对应的节点后，该节点下的 data 域存储着数据地址(行指针)，找到数据地址后，直接去MYD文件拿到数据，这个过程叫回行。

InnoDB

该引擎下的索引称之为聚簇索引。如果没有表没有设置索引，那么系统会默认将主键建立索引，如果没有主键，会选择一个唯一索引作为建立索引树，如果还是没有的话，系统会自动生成 rowid 作为主键。

该引擎在5.5后，支持全文索引。

数据存储形式

在以前，默认情况下，所有数据都会存放在共享表空间文件(每个表的.frm还是独立文件的)，现在默认的都是独享空间文件，文件后缀为.ibd。

相较于 MyISAM 引擎而言，少了一个 .MYI 文件，上文得知，这个是存储索引结构数据的文件，那innodb 的索引存储在哪里了呢？这就是为什么 innodb 的索引为什么叫聚簇索引了。

索引结构与一般查找流程

他的索引结构也是一颗 B+Tree 树，只不过子节点不是存储着数据指针，而是数据本身。可以这么理解，索引和数据都在 .ibd 文件内。索引 id 对应的 data 域存储着数据本身。

打个简单的比方，有个瞎子找到了，一颗叫 MyISAM 的苹果树，要找 27 号苹果，当他找到 27 号树叶的时候，树叶告诉他，恭喜你找到了，但是你得去旁边地上的 27 号箱子里拿到 27 号苹果。

又有个瞎子找到了一个叫 InnoDB 的树，也要找 27 号苹果，当找到 27 号叶子后，叶子告诉他，苹果就在我身后。

这里可以知道，innodb 不需要做回行操作。但是它没有缺点了吗？

如果主键没有规律呢？不是自增的呢？这样就会导致页分裂，可以想象，本来如果是自增的数据，插入会很快，挨着插入就行了，如果主键(肯定会有一个主键)不规律的话，那就会导致页分裂，页分裂将会严重影响到插入速度。

而当乱序插入后，直接 select * from xxxx ;查出来的数据可以看到是有序的(在有主键 id 的情况下)，这是为什么呢？

因为它是聚簇索引，数据和索引都在同一个文件，即使是乱序插入，对于索引树来说也是有序的，查询出来也是按照索引挨个取的，并花不了多少时间。

那为什么乱序插入 MyISAM 表后查出来也是乱序的呢？如果是 select * from xxxx ;的话，程序自然不会再去索引树上找了，直接去MYD文件上全部取出来就行了。

这里我们能从侧面看出聚簇索引和非聚簇索引的一个很重要的区别。

innodb的次级索引

通过上面的介绍，可以了解到，所有数据都跟索引树在一起。那么如果一个表，有两个索引呢?这个 innodb 是怎么处理的？

如果说重建一棵完整的树，那么势必会导致数据冗余，因为所有数据都挂载在树上，因此，次级索引的 data 域是不会保留主树上的内容的，而是保留的指向主树节点的指针。

因此，可以这么理解，主树是一颗大树，很沉重。次级索引是小树，作为辅助。因此，当数据量一大，我们需要考虑建立次级索引，从次级索引搜索甚至可以比主键索引更快。

Hash索引

通过 Hash 算法，将数据散列到某个位置，什么都不用记，有请求过来，根据规则再次对数据进行Hash 处理，再次散到内存中即找到数据。

该索引速度是最快的，第一，没有磁盘 io ，第二直接在内存中计算位置。

缺点也是很明显的，该索引只存在内存当中，也就是说，关机后，就没了，需要重建索引。

第二个缺点，无法进行范围查询，因为 Hash 散列的数据是没有规律可言的，每个散列点都是独立的。
第三个缺点，无法利用前缀索引。
这个了解一下就好了。

最后

之所以先写这篇，主要是为了后面的索引的使用做铺垫，所有的mysql文章都是按顺序来写的。

原文链接: mysql之引擎与索引的关系