Mysql索引

Mysql

这篇文章算是对自己对于高性能Mysql中的索引那一章的理解。

其实在本科的时候，我们的数据库老师压根就没有和我们提过索引，很奇葩是吧，不过确实是这样的。我一直以来都对索引这个东西感觉有点玄乎，我知道它可以提高搜索的效率，但是我一直不知道索引究竟是怎么一回事，直到最近读了高性能Mysql。

什么是索引呢？人们总是告诉你，索引就像是书上的目录，确实是这样的，但是稍微的还有一些不同。

我们用得最多的应该是B+树索引，在高性能Mysql中有一些很形象的图，我这里贴出来：

对应的中文图:

我稍微解释一下这张图。

在第一次看这张图的时候，我有一个疑问，那就是为什么叶子页中的数据是有顺序的（因为那时我认为叶子页里面存储的就是实际的数据）,在我的印象当中，比如说我创建了这么一张表：

CREATE TABLE demo(
title VARCHAR(50) NOT NULL,
id int NOT NULL,
PRIMARY KEY (title),
INDEX (id)
);

很明显，我在id列上创建了一个索引，我那个时候是这么想的，我们插入的时候不可能按照id列的顺序来插入吧，一般都是胡乱插入的，是吧。为什么在叶子页的数据项是有序的？好吧，可能你认为这个问题有些幼稚。

后来我意识到自己错了，在叶子页中的每一项并不是数据本身，而是数据被索引的那一项，然后还有一个指向实际数据的指针，这样一看，即使实际的数据并没有按照我建立索引的那一列进行排序，其实也没有关系是吧，因为指向它们的叶子页的每一项都进行了排序。

实际的存储更像下图。

当然上面的那张图做了一定程度的简化，比如各个叶子页应该有指针相连，不过这些你可以暂时忽略。

看到这里，我们大概就明白了索引大概是怎么一回事了，叶子页对于实际的数据做了一定程度的抽取，只提取了创建了索引的那些列的数据，然后进行了排序，我们看到，如果我们使用索引来查询某一项的话，在叶子页这一层可以使用二分法来搜索，效率快了非常非常多。即使这样，还不够，因为数据量特别多的时候，查询的次数依旧会非常多。

如同计算机里面的文件系统一样，我们再往上提取一层，如果第二层的数据量依旧很大，我们可以再提取，这样下来，查询的速度会更上一层楼。

这大概是B+树索引的全部的故事。可是还不够。

什么是聚簇索引？

在数据库系统概念里是这么定义的：

包含记录的文件按照某个搜索吗指定的顺序排序，那么该搜索码对应的索引称为聚簇索引。

里面的搜索码就是我们的索引列。

这里我只记录一下MYISAM和INNODB两个引擎对于聚簇索引的处理方式。

首先是MYISAM，说实话，MYISAM的聚簇索引和普通的B+树索引并没有什么不一样,只是被索引的列换成了主键。

INNODB这个引擎对于主键的聚簇索引方式和之前的B+树索引有一个不同的地方，那就是B+树索引的叶子页中的每一项只是对于真实的数据项被索引的列的一个抽取，然而聚簇索引里面的叶子页的每一项就是真实的数据，仅此而已!

这个东西可以帮助我们理解很多事情！
我们看到，别人创建表的时候一般都是这么写:

CREATE TABLE table_name(
id int AUTO_INCREMENT;
...
PRIMARY KEY (id)
);

我以前一直以为，id项是多余的，因为一般在应用里面，我们不会用到id这一项，所以我以前创建的表，从来没有id这种说法，有必要吗？

答案是非常有必要。我们看到之前的INNODB的聚簇索引的叶子页的每一项实际上就是数据本身，这意味这什么呢？聚簇索引也是索引啊，亲。

只要是索引，必定要维护叶子页中的数据的顺序啊。否则索引就没有什么意义了。

我们如果不要前面的id项(设置为自动增长)，而把主键设置为其他的列。我们插入的时候多半不是按照主键的顺序来插入的。

下面是一个示例:

上图中的UUID(字符)是一个主键,我们要插入002775对应的那一项数据，却发现在0016c9那一项后面已经有数据了，为了维护主键的顺序，数据库只好将0016c9后面的数据项往后挪动一个位置，如果恰好插入之前该叶子页已经满了，那么只好另外再分一页(千万别以为数据库里所有的数据都存储在一块连续的块中，事实上，数据库将数据分页存储，然后用指针将这些页连接起来。)，可想而知，这会造成多么大的空间的浪费和不必要的开销，前面的分析对于000e2f这一项也同样成立。

如果我们对加了id项的表进行数据插入呢？

我们看到，效果要好的多，因为id项都是连续的，不存在移动数据项的问题，也不存在页内空洞的问题。

当然，这个问题对于MYISAM是不存在的。