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

首先用cmd进入你mysql的安装目录的bin目录下：输入如下命令 mysql -u(用户名) -p ->回车 输入密码 -> 回车 mysqladmin -u用户名 -p旧密码 password 新密码 没有旧密码可以省略 -p旧密码 回车。all is over

MySQL支持全文索引和搜索功能。在MySQL中可以在CHAR、VARCHAR或TEXT列使用FULLTETXT来创建全文索引。

FULLTEXT索引主要用MATCH()AGAINST语法来实现搜索：

MySQL的全文搜索存在以下局限：

通常来说MySQL自带的全文搜索使用起来局限性比较大，性能和功能都不太成熟，主要适用于小项目，大项目还是建议使用elasticsearch来做全文搜索。

ElasticSearch是一个分布式的开源搜索和分析引擎，适用于所有类型的数据，包括文本、数字、地理空间、结构化和非结构化数据，以下简称ES。

Elasticsearch 在 Apache Lucene 的基础上开发而成，Elasticsearch 以其简单的 REST 风格 API、分布式特性、速度和可扩展性而闻名，是 Elastic Stack 的核心组件。Elastic Stack 是适用于数据采集、充实、存储、分析和可视化的一组开源工具。

Elasticsearch 的实现原理主要分为以下几个步骤，首先用户将数据提交到Elasticsearch 数据中心，再通过分词控制器去将对应的数据分词，将其权重和分词结果一并存入数据，当用户搜索数据时候，再根据权重将结果排名，打分，再将返回结果呈现给用户。

由于ES是基于RESTfull Web接口的，因此我们直接按照惯例传递JSON参数调用接口即可实现增删改查，并且不需要我们做额外的管理 *** 作就可以直接索引文档，ES已经内置了所有的缺省 *** 作，可以自动帮我们定义类型。

再次执行PUT，会对库中已有的id为1的数据进行覆盖，每修改一次_version字段的版本号就会加1。

默认搜索会返回前10个结果：

返回的几个关键词：

查询字符串搜索，可以像传递URL参数一样传递查询语句。

精确查询：

全文搜索：

以上两种方法都需要考虑数据更改后如何与ES进行同步。

filesystem类似于我们在mysql上建立一层redis缓存；

es的搜索引擎严重依赖于底层的filesystem cache，如果给filesystem cache更多的内存，尽量让内存可以容纳所有的indx segment file索引数据文件，那么你搜索的时候就基本都是走内存的，性能会非常高。

两者差距非常大，走磁盘和走systenfile cache的读取的性能差距可以说是秒级和毫秒级的差距了；

要让es性能要好，最佳的情况下，就是我们的机器的内存，至少可以容纳你的数据量的一半

最佳的情况下，是仅仅在es中就存少量的数据，存储要用来搜索的那些索引，内存留给filesystem cache的，如果就100G，那么你就控制数据量在100gb以内，相当于是，你的数据几乎全部走内存来搜索，性能非常之高，一般可以在1秒以内

的少数几个字段就可以了，比如说，就写入es id name age三个字段就可以了，然后你可以把其他的字段数据存在mysql里面，我们一般是建议用 es + hbase 的一个架构。

hbase的特点是适用于海量数据的在线存储，就是对hbase可以写入海量数据，不要做复杂的搜索，就是做很简单的一些根据id或者范围进行查询的这么一个 *** 作就可以了

如果确实内存不足，但是我们又存储了比较多的数据，比如只有30g给systemfile cache，但是存储了60g数据情况，这种情况可以做数据预热；

我们可以将一些高频访问的热点数据（比如微博知乎的热榜榜单数据，电商的热门商品（旗舰版手机，榜单商品信息）等等）提前预热，定期访问刷到我们es里；（比如定期访问一下当季苹果旗舰手机关键词，比如现在的iphone12）

对于那些你觉得比较热的，经常会有人访问的数据，最好做一个专门的缓存预热子系统，就是对热数据，每隔一段时间，提前访问一下，让数据进入filesystem cache里面去。这样下次别人访问的时候，一定性能会好一些。

我们可以将冷数据写入一个索引中，然后热数据写入另外一个索引中，这样可以确保热数据在被预热之后，尽量都让他们留在filesystem os cache里，别让冷数据给冲刷掉。

尽量做到设计document的时候就把需要数据结构都做好，这样搜索的数据写入的时候就完成。对于一些太复杂的 *** 作，比如join，nested，parent-child搜索都要尽量避免，性能都很差的。

es的分页是较坑的 ，为啥呢？举个例子吧，假如你每页是10条数据，你现在要查询第100页，实际上是会把 每个shard上存储的前1000条数据都查到 一个协调节点上，如果你有个5个shard，那么就有5000条数据，接着 协调节点对这5000条数据进行一些合并、处理，再获取到最终第100页的10条数据。

因为他是分布式的，你要查第100页的10条数据，你是不可能说从5个shard，每个shard就查2条数据？最后到协调节点合并成10条数据？这样肯定不行，因为我们从单个结点上拿的数据几乎不可能正好是所需的数据。我们必须得从每个shard都查1000条数据过来，然后根据你的需求进行排序、筛选等等 *** 作，最后再次分页，拿到里面第100页的数据。

你翻页的时候，翻的越深，每个shard返回的数据就越多，而且协调节点处理的时间越长。非常坑爹。所以用es做分页的时候，你会发现越翻到后面，就越是慢。

我们之前也是遇到过这个问题，用es作分页，前几页就几十毫秒，翻到10页之后，几十页的时候，基本上就要5~10秒才能查出来一页数据了

你系统不允许他翻那么深的页，或者产品同意翻的越深，性能就越差

如果是类似于微博中，下拉刷微博，刷出来一页一页的，可以用scroll api

scroll api1 scroll api2

scroll会一次性给你生成所有数据的一个快照，然后每次翻页就是通过游标移动 ，获取下一页下一页这样子，性能会比上面说的那种分页性能也高很多很多

scroll的原理实际上是保留一个数据快照，然后在一定时间内，你如果不断的滑动往后翻页的时候，类似于你现在在浏览微博，不断往下刷新翻页。那么就用scroll不断通过游标获取下一页数据，这个性能是很高的，比es实际翻页要好的多的多。

缺点：

首先获取数据是一个动态过程，静态html肯定无法单独完成数据的 *** 作；其次静态页面的作用只是用来展示渲染数据的前端技术；因此要实现搜索mysql数据的功能，需要后台获取数据的技术，如java实现后台数据 *** 作，以及后台与前端交互的技术，如ajax、freemarker实现前后台数据交互。

以上就是关于MySQL和ES的索引对比全部的内容，包括:MySQL和ES的索引对比、问一个ESSamp关于mysql数据库的问题、全文搜索之MySQL与ElasticSearch搜索引擎等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！