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索引原理，可以发现：

{索引名}，支持支持一次搜索多个索引，多个索引使用逗号分隔，例子：

按前缀匹配索引名：搜索索引名以order开头的索引。

当我们执行查询语句，返回的JSON数据格式如下

query子句主要用来编写类似SQL的Where语句，支持布尔查询（and/or）、IN、全文搜索、模糊匹配、范围查询（大于小于）。

es(1)—基础Rest API命令

es（2）—复杂的多条件查询(bool查询与constant_score查询)

es（4）—查询条件match和term

es(5)—terms的用法

es7.x（6）—minimum_should_match最低匹配度

es7.x（7）—短语搜索（match_phrase）

es7.x（8）— 多字段匹配检索 multi_match query

es7.x（9）— match query的参数

aggs子句，主要用来编写统计分析语句，类似SQL的group by语句

es7.x（10）aggs聚合查询

sort子句，用来设置排序条件，类似SQL的order by语句。

ES的默认排序时根据相关性分数排序，如果我们想根据查询结果中的指定字段排序，需要使用 sort 关键字处理。

语法：

sort子句支持多个字段排序，类似SQL的order by。

例子：

查询order_v2索引的所有结果，结果根据order_no字段降序，order_no相等的时候，再根据shop_id字段升序排序。

ES查询的分页主要通过from和size参数设置，类似MYSQL 的limit和offset语句。

_source用于设置查询结果返回什么字段，类似select语句后面指定字段。

仅返回，order_no和shop_id字段。

以下是本次实战的环境信息，请确保您的Elasticsearch可以正常运行：

实战用的数据依然是一些汽车销售的记录，在 第一章 有详细的导入步骤，请参考 *** 作，导入后您的es中的数据如下图：

接下来一起实战聚合排序吧；

之前文章中的聚合查询，我们都没有做排序设置，此时es会用每个桶的doc_count字段做降序，下图是个terms桶聚合的示例，可见返回了三个bucket对象，是按照doc_count字段降序排列的：

除了自定义排序，es自身也内置了两种排序参数，可以直接拿来使用：

返回结果如下，已经按照key的大小从大到小排序：

《Elasticsearch 权威指南》 里指出：_key只在 histogram 和 date_histogram 内使用，原文如下图红框所示：

但是在实际 *** 作中发现，6.7.1版本中，除了histogram 和 date_histogram，terms桶也可以用_key排序，如下图，是按照key的字母降序：

把desc改为asc之后返回如下图，变成了按照key的首字母升序排序：

3. 另外 《Elasticsearch 权威指南》 中还提到一种内置排序类型_term，但是 《Elasticsearch官方文档》 中宣布该类型在6.0之后已经废弃，如下：

也许是"手贱"的缘故，我还是用_term试了下，可以返回结果，但是会建议用_key替代_term，如下图：

常见的metrics有累加和(sum)、最大值(max)、最小值(min)、平均值(avg)，这些metrics的特点是处理结果只有一个值，我们可以按照这个结果来排序，例如计算每个汽车品牌的销售额，再按照销售额排序：

下面是聚合结果，可见已按照每个品牌的销售额大小做了降序的排序：

和sum、max这些只有一个结果的metrics不同，extended_stats的结果包含了数量、最大值、最小值、平均值、累加和等多种处理，此时必须要指定用其中的哪一项（否则会返回错误：Invalid aggregation order path [xxxx]. When ordering on a multi-value metrics aggregation a metric name must be specified）:

返回结果如下，可见已经按照metrics结果的avg子项做了升序排序：

在聚合查询中，经常对聚合的数据再次做聚合处理，例如统计每个汽车品牌下的每种颜色汽车的销售额，这时候DSL中就有了多层aggs对象的嵌套，这就是嵌套桶（此名称来自 《Elasticsearch 权威指南》 )，如下图所示：

嵌套桶的排序情况略为复杂，详情请参考 《Elasticsearch聚合的嵌套桶如何排序》 ；

至此，聚合返回结果排序的实战已经完成了，后面的章节会深入学习es的聚合有关的关键知识点；

---