MySQL和ES的索引对比

[toc]

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论是MyISAM和InnoDB两个存储引擎的B+Tree索引的实现方式。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，下面是MyISAM索引的原理图：

上图是一个MyISAM表的主索引示意，可以看出MyISAM的索引文件仅仅保存数据记录的地址，在MyIASM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。B+Tree的所有叶子节点包含所有关键字且按照升序排列的。

MyISAM表的索引和数据是分离的，索引保存在“表名.MYI”文件内，而数据保存在“表名.MYD”中。

MyISAM的索引方式也叫做 非聚集 的，之所以这么称呼是为了与InnoDB的聚集索引区分。

虽然InnoDB也使用B+Tree作为索引结构，但是具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件，从上文知道MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录地址，而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引(同时也是数据文件)的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显示指定，则MySQL系统会自动选择一个唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段为主键，这个字段长度为6字节，类型为长整形。

第二个与MyISAM索引的不同时InnoDB的辅助索引data域存储相应记录主键值而不是地址，换句话说，InnoDB的所有辅助索引都引用主键作为data域，例如，下图定义在col3上的辅助索引：

这里的英文字符的ASCII码作为比较准则。聚集索引这种方式使得按照主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键索引到主索引中检索获取记录。

了解不同存储引擎的索引实现对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白不建议使用过长的字段作为主键， 因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变的更大 ，在例如， 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree非单调的主键会造成在插入新纪录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

ES的索引不是B+Tree树，而是倒排索引，ES的倒排索引由 Term index，Term Dictionary和Posting List 组成的。

有倒排索引(inverted index)就用正排索引(forward index)，正排索引就是文档(Document)和他字段Fields正向对应的关系对应表如下：

那么倒排索引是字段Field和拥有这个Field的文档对应的关系如下：

Sex字段：

Age字段：

Jack、lucy或者17，18叫做term，而[1,3]就是Posting list。Posting list就是一个int数组，存储了所有包含某个term的文档id，那么什么是term index和term dictionary？

如上如果name字段很多个term，比如Carla,Sara,Elin,Ada,Patty,Kate,Selena，如果按照这样的顺序排列，找出某个特定的term一定很慢，因为term没有排序，需要全部过滤一遍，才能找出特定的term。排序之后就变成了：Ada,Carla,Elin,Kate,Patty,Sara,Selena。

这样就可以使用二分法的方式，比全遍历更快的找出目标的term，如果组织这些term的方式就是 term dictionary ，意思就是term的字典，有了term dictionary之后，就可以用比较少的比较次数和磁盘读次数查找目标。但是磁盘的随机读 *** 作仍然是非常昂贵的，所以尽量少的读磁盘，有必要把一些数据缓存到内存里，但是整个Term dictionary本身又太大了，无法完整的放到内存中，于是就有了term index，Term index有点像一本字典的打的章节表。比如：

A开头的term ……………. Xxx页

C开头的term ……………. Xxx页

E开头的term ……………. Xxx页

如果所有的term都是英文字符的话，可能这个term index就真的是26个英文字符表构成了。但是实际情况是，term未必都是英文字符，term可以是任意的byte数组。而且26个英文字符未必是每一个字符都有均等的term，比如x字符开头的term可能一个都没有，而s开头的term又特别多，实际的term index是一颗字典树(trie 树)：

上面例子是一个包含A", "to", "tea", "ted", "ten", "i", "in", 和 "inn" 的trie树。这棵树不会包含所有的term，他包含的是term的一些 前缀 ，通过term index可以快速定位到term dictionary的某个offest，然后从这个位置在往后顺序查找，再加上一些压缩技术，Term index的尺寸可以只有所有的term的尺寸的十分之一，用内存缓存整个term index变成可能，整体上来说就是这样的效果：

由Term index到Term Dictionary，再到posting list，通过某个字段的关键字去查询结果的过程比较清楚了，通过多个关键字的posting list进行and或者or进行交集并集的查询也简单了( 倒排索引介绍了交集并集的过程 )

对比MySQL的B+Tree索引原理，可以发现：

了解mysql的索引类型的时候，我觉得按照以下4中方式划分逻辑是比较清晰的。

1.存储结构 2.物理存储 3.作用字段 4.功能

按照数据存储的结构可以分B树索引和hash索引。

又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

查询必须从索引的最左边的列开始。

查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。

存储引擎不能使用索引中范围条件右边的列。

也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。

其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。

不能使用 HASH 索引排序。

HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。

HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

聚集索引是按照所以把数据排好序了，所以一个表只能存在一个聚集索引，其它的都是非聚集索引。

因这个特性，聚集索引是查询数据范围的时候有很大的性能优势。

但是也需要注意的是如果频繁更新的列不适合设置为聚集索引，

原因很简单，每次更新都需要从新排序，频繁的更新给的压力也大。

如果不指定的话，默认主键为聚集索引。

一个表里除了一个聚集索引外其他的都是非聚集索引，虽然不能把数据按照索引排序，但是索引数据是可以排序的。

所以非聚集索引查询范围的时候是先找索引列的范围，再通过这个索引查询行的值。

单列索引即一个索引只包含单个列。

组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

既不是主键索引也不是唯一索引的一般索引。

FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

空间索引主要用于地理空间数据类型 GEOMETRY。

下面是 mysql官网给出的几个存储引擎和索引之间的关系 。

欢迎大家的意见和交流

email: li_mingxie@163.com

InnoDB在创建或重建索引时执行批量加载，而不是一次插入一个索引记录。这种索引创建方法也称为排序索引构建。空间索引不支持排序索引构建。

索引构建分为三个阶段。在第一阶段， 扫描聚集索引，生成索引条目并添加到排序缓冲区。当排序缓冲区变满时，条目将被排序并写入临时中间文件。此过程也称为 “运行”。在第二阶段，将一个或多个运行写入临时中间文件，对文件中的所有条目执行合并排序。在第三个也是最后一个阶段，排序后的条目被插入到 B-tree中。

在引入排序索引构建之前，使用插入 API 将索引条目一次插入 B 树中的一条记录。此方法涉及打开 B 树 游标以查找插入位置，然后使用 乐观插入将条目插入 B 树页面。如果由于页面已满而导致插入失败， 则将执行悲观插入，这涉及打开 B-tree 游标并根据需要拆分和合并 B-tree 节点以找到条目空间。这种“自上而下”的弊端建立索引的方法是搜索插入位置的成本以及 B 树节点的不断拆分和合并。

排序索引构建使用“自下而上”建立索引的方法。使用这种方法，对最右侧叶页的引用保存在 B 树的所有级别。分配必要 B 树深度的最右侧叶页，并根据其排序顺序插入条目。一旦叶页已满，就会将节点指针附加到父页，并为下一次插入分配一个兄弟叶页。这个过程一直持续到所有条目都被插入，这可能导致插入到根级别。分配同级页时，释放对先前固定叶页的引用，新分配的叶页成为最右边的叶页和新的默认插入位置。

要为将来的索引增长留出空间，您可以使用innodb_fill_factor变量来保留一定百分比的 B 树页面空间。例如，设置 innodb_fill_factor为 80 会在排序索引构建期间保留 B 树页面中 20% 的空间。此设置适用于 B 树的叶子页面和非叶子页面。它不适用于用于 TEXT或 BLOB条目的外部页面。保留的空间量可能与配置不完全相同，因为innodb_fill_factor值被解释为提示而不是硬限制。

全文索引支持排序索引构建 。以前，SQL 用于将条目插入全文索引。

对于压缩表，以前的索引创建方法将条目附加到压缩页和未压缩页。当修改日志（表示压缩页面上的可用空间）变满时，将重新压缩压缩页面。如果由于空间不足而导致压缩失败，则页面将被拆分。使用排序索引构建，条目仅附加到未压缩的页面。当一个未压缩的页面变满时，它就会被压缩。自适应填充用于确保在大多数情况下压缩成功，但如果压缩失败，则会拆分页面并再次尝试压缩。这个过程一直持续到压缩成功。

在排序索引构建期间禁用重做日志记录。相反，有一个 检查点来确保索引构建可以承受意外退出或失败。检查点强制将所有脏页写入磁盘。在排序索引构建期间，页面清理线程会定期收到信号以刷新 脏页，以确保可以快速处理检查点 *** 作。通常，当干净页面的数量低于设置的阈值时，页面清理线程会刷新脏页面。对于排序索引构建，脏页会被及时刷新以减少检查点开销并行化 I/O 和 CPU 活动。

排序索引构建可能会导致 优化器统计信息与以前的索引创建方法生成的统计信息不同。统计数据差异是由于用于填充索引的算法不同造成的。

---