返回 登录
1

MySQL索引专题一 认识索引

关于这个专题
想写MySQL的索引专题是源于之前自己在学习MySQL索引时痛苦的经历,你在网上搜索关于MySQL的索引的文章,大多是支离破碎,没有系统性的对知识点的罗列堆砌,文章中会说明你要如何如何做,但是很少涉及去讲为什么要这么做,哪些不能做,很难对MySQL有一个系统性的认知,学习如果没有系统性的话,就很难在实际的项目中灵活运用,出于此目的,自己就打算写一个关于MySQL索引的专题系列,算是自己一个学习的总结,如果同时能帮到你那再好不过了。下面进入正题,我们先来了解一下什么是索引以及索引的类型。

认识索引
认识索引是什么东西非常关键,一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系,为了方便查找书中的内容,通过对内容建立索引形成目录。因此,首先你要明白的一点就是,索引它也是一个文件,它是要占据物理空间的。

比如对于MyISAM存储引擎来说:
.frm后缀的文件存储的是表结构。
.myd后缀的文件存储的是表数据。
.myi后缀的文件存储的就是索引文件。
如下图所示:

对于InnoDB 存储引擎来说:
.frm后缀的文件存储的是表结构。
.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table 参数)

如下图所示:

因此,当你对一张表建立索引时,索引文件的大小也会改变,当你数据表中的数据因为增删改变化时,索引文件也会变化的,只不过MySQL会自动维护索引,这个过程不需要你介入,这也是为什么不恰当的索引会影响MySQL性能的原因。

总结:
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中,以便于快速查找;
2. 索引存在于磁盘中,会占据物理空间。

索引的类型
上面说到,索引文件时按照不同的数据结构来存储的,数据结构的不同也产生了不同的索引类型,常见的索引类型包括:

B-Tree索引
哈希索引
空间数据索引(R-Tree)
全文索引
下面做一一介绍:
1. B-Tree索引

B-Tree索引是最常用的一种索引,如果没有指定特定的类型,那么多半就是B-Tree索引,事实上,很多搜索引擎使用的是它的变种B+Tree,这是对B-Tree的一个优化,如果需要详细了解,可以参考数据结构方面的书籍,这里不做详细探讨。以下统称为B-Tree索引。
绝大多数的存储引擎,比如MyISAM和InnoDB都支持这种索引,因此说它是应用最广泛,最常用的一种索引方式,但是不同的存储引擎在具体实现时会稍有不同,比如MyISAM会使用前缀压缩的方式对索引进行压缩,InnoDB则不会。
下图展示了B-Tree索引是如何存储被索引的数据的:

说明:

左图是一个包含三列的数据表,右图则展示了数据是如何被索引的。
可以看出B-Tree是对索引列是按照顺序存储的,每个叶子节点指向被索引的数据,这也是B-Tree索引支持范围查找数据的原因。
2. 哈希索引

相比于B-Tree索引,哈希索引的实现就比较简单了,它是基于哈希表来实现的,对于要索引的列,存储引擎会计算出一一对应的哈希码,然后把哈希码存放在哈希表中作为key,value值是指向该行数据的指针。
下图是简单的原理展示:

说明:

左边紫色图表示一个二列的数据表。
中间表示对fname列进行哈希索引,计算出哈希值。
右边绿色图表示把生成的哈希值存放于哈希表中。
当我们执行以下查询时:

select * from testTable where fname = “mary”;
MySQL会首先计算查询条件mary的哈希值,然后到哈希表中去找该哈希值,如果找到了根据对应的指针也就找到了需要寻找的数据行。

哈希表的优势与限制:

优势:
只需比对哈希值,因此速度非常快,性能优势明显;
限制:
不支持任何范围查询,比如where price > 150,因为是基于哈希计算,支持等值比较。
哈希表是无序存储的,因此索引数据无法用于排序。
主流存储引擎不支持该类型,比如MyISAM和InnoDB。哈希索引只有Memory, NDB两种引擎支持。
因此,哈希索引虽然速度快,但其实使用很受限,只适用于某些特殊的场合。

  1. 空间数据索引(R-Tree)

空间索引可用于地理数据存储,它需要GIS相关函数的支持,由于MySQL的GIS支持并不完善,所以该索引方式在MySQL中很少有人使用。

  1. 全文索引

全文索引主要用于海量数据的搜索,比如淘宝或者京东对商品的搜索,你不可能使用like进行模糊匹配吧,MySQL从5.6开始支持InnoDB引擎的全文索引,功能没有专业的搜索引擎比如Sphinx或Solr丰富,如果你的需求比较简单,可以尝试一下MySQL的全文索引,否则建议使用专业的搜索引擎。

总结:
1. B-Tree索引使用最广泛,主流引擎都支持。
2. 哈希索引性能高,适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

索引和存储引擎之间的关系
上面讲述了索引有不同的类型,存储引擎也有不同的类型,那么索引和存储引擎之间有什么关系呢?
首先你需要知道,在MySQL中,索引是在存储引擎中实现的,并不是所有的存储引擎都支持所有的索引类型,比如哈希索引,MyISAM和InnoDB是不支持的;同样,即使对于同一类型的索引,不同的存储引擎实现的方式也可能是不同的,比如MyISAM和InnoDB对B-Tree索引,具体的实现是有差别的。

总结:
1. 不同的存储引擎可能支持不同的索引类型;
2. 不同的存储引擎对同一中索引类型可能有不同的实现方式。

B-Tree索引与唯一索引,主键索引,普通索引的关系
最开始对B-Tree索引与唯一索引,主键索引,普通索引这几种索引的关系很模糊,网上也没搜索到相关的资料,以为他们的关系是并列的,其实并不是,B-Tree只是底层的算法实现,唯一索引,主键索引,普通索引都是基于B-Tree索引算法的,只不过又有各自的特点。
通过下图也可看出这种关系:

至于唯一索引,主键索引,普通索引之间的区别,下面补充一下:

主键索引:数据列不允许重复,不允许为NULL.一个表只能有一个主键。
唯一索引:数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
普通索引:基本的索引类型,没有唯一性的限制,允许为NULL值。
总结:
这篇文章先说到这里,目的主要是对MySQL的索引有个概念上的认识,以及了解索引的类型,索引和存储引擎之间的关系,本专题会继续更新,继续对MySQL索引知识逐渐展开,如果你感兴趣的话可以关注该专栏,以及顺便动动手指关注一下我(^_^),希望本文对你有所帮助。

评论