ES(ElasticSearch)数据建模最佳实践之「一对多对多关系建模」
一、开门见山
关系型数据库 MySQL 的 join 关系如何在 ES 中实现。
官方文档链接介绍如下:
https://www.elastic.co/guide/en/elasticsearch/reference/6.3/joining-queries.html
- Nested object:嵌套对象
- Parent child:父子关系
二、商铺SPU模型
电商系统常见的一对多对多关系:
一个商铺下有多个商品,一个商品下有多个单品,如北京 iphone xxx 店铺,有 iphone 手机、mac 电脑,这些属于商品,而用户购买的 iphone13 128G 黑色国行手机,这个就属于售卖的单品。关系图如下所示:
下面以父子文档为例,介绍 ES 如何构建多表之间的复杂关联数据模型
可参考官方文档:
https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html
附:索引 Mapping Type 有:text, keyword, date, integer, long, double, Boolean 等
三、实战演练
从官网下载 elasticsearch 和对应版本的 kibana-win 版本的安装包,可以按下文一步步操作,效果更好:
(1)双击 elasticsearch.bat,启动本地 es 服务:
\elasticsearch-6.3.2\bin\elasticsearch.bat
(2)然后双击 kibana.bat 文件,启动对应版本的 kibana 服务:
\kibana-6.3.2-windows-x86_64\bin\ kibana.bat
(3)本地访问kibana:http://localhost:5601/。
(4)点击右侧菜单栏【Dev Tools】,如下所示:
(5)构建祖孙三层结构索引
// ①创建store_spu_sku_index索引并构建store_spu_sku类型PUT /store_spu_sku_index{ "mappings": { "store_spu_sku": { "properties": { "store_spu_sku_join": { "type": "join", "relations": { "store": "spu", "spu": "sku" } }, "storeId": { "type": "keyword" }, "storeName": { "type": "text" }, "spuId": { "type": "keyword" }, "spuName": { "type": "text" }, "skuId": { "type": "keyword" }, "skuName": { "type": "text" } } } }} |
---|
// ②查询索引类型的结构GET /store_spu_sku_index/store_spu_sku/_mapping或GET /store_spu_sku_index |
---|
// ③删除索引DELETE store_spu_sku_index |
---|
注(以下对ES6.x适用,其他版本可能不适宜,但是万变不离其宗):
- 每个索引只允许一个 join 类型 Mapping 定义;
- 父文档和子文档必须在同一个分片,路由设置相同;
- 一个文档可以存在多个子文档,但只能有一个父文档;
- 可以为已经存在的 join 类型添加新的关系;
- 当一个文档已经成为父文档后,可以为该文档添加子文档;
- 子文档不能独立存在,先有父文档,才能创建子文档。
(6)创建父文档:
// 插入父类PUT /store_spu_sku_index/store_spu_sku/s1?refresh{ "storeId":"s1", "storeName":"店铺名称s1", "store_spu_sku_join":"store"} |
---|
PUT /store_spu_sku_index/store_spu_sku/s2?refresh{ "storeId":"s2", "storeName":"店铺名称s2", "store_spu_sku_join":"store"} |
(7)创建子文档:
// 插入子文档PUT /store_spu_sku_index/store_spu_sku/spu1?routing=s1&refresh{ "spuName":"spu名称1-s1", "spuId":"spu1", "store_spu_sku_join":{ "name":"spu", "parent":"s1" }} |
---|
PUT /store_spu_sku_index/store_spu_sku/spu2?routing=s1&refresh{ "spuName":"spu名称2-s1", "spuId":"spu2", "store_spu_sku_join":{ "name":"spu", "parent":"s1" }} |
PUT /store_spu_sku_index/store_spu_sku/spu3?routing=s2&refresh{ "spuName":"spu名称3-s2", "spuId":"spu3", "store_spu_sku_join":{ "name":"spu", "parent":"s2" }} |
PUT /store_spu_sku_index/store_spu_sku/spu4?routing=s2&refresh{ "spuName":"spu名称4-s2", "spuId":"spu4", "store_spu_sku_join":{ "name":"spu", "parent":"s2" }} |
(8)创建孙子文档
即SPU的子文档(SKU文档)
// 插入孙子文档PUT /store_spu_sku_index/store_spu_sku/sku1?routing=s1&refresh{ "skuName":"sku名称1-spu1", "skuId":"sku1", "store_spu_sku_join":{ "name":"sku", "parent":"spu1" }} |
---|
PUT /store_spu_sku_index/store_spu_sku/sku2?routing=s1&refresh{ "skuName":"sku名称2-spu1", "skuId":"sku2", "store_spu_sku_join":{ "name":"sku", "parent":"spu1" }} |
PUT /store_spu_sku_index/store_spu_sku/sku3?routing=s1&refresh{ "skuName":"sku名称3-spu2", "skuId":"sku3", "store_spu_sku_join":{ "name":"sku", "parent":"spu2" }} |
PUT /store_spu_sku_index/store_spu_sku/sku4?routing=s1&refresh{ "skuName":"sku名称4-spu2", "skuId":"sku4", "store_spu_sku_join":{ "name":"sku", "parent":"spu2" }} |
PUT /store_spu_sku_index/store_spu_sku/sku5?routing=s2&refresh{ "skuName":"sku名称5-spu3", "skuId":"sku5", "store_spu_sku_join":{ "name":"sku", "parent":"spu3" }} |
PUT /store_spu_sku_index/store_spu_sku/sku6?routing=s2&refresh{ "skuName":"sku名称6-spu3", "skuId":"sku6", "store_spu_sku_join":{ "name":"sku", "parent":"spu3" }} |
PUT /store_spu_sku_index/store_spu_sku/sku7?routing=s2&refresh{ "skuName":"sku名称7-spu4", "skuId":"sku7", "store_spu_sku_join":{ "name":"sku", "parent":"spu4" }} |
PUT /store_spu_sku_index/store_spu_sku/sku8?routing=s2&refresh{ "skuName":"sku名称8-spu4", "skuId":"sku8", "store_spu_sku_join":{ "name":"sku", "parent":"spu4" }} |
注意:
- 孙子文档 sku 所在分片必须与其父母 spu 和祖父母 store 相同
- 孙子文档 sku 的父文档 id 必须指向其父亲 spu 文档
四、搜索实践
(1)父查子实践
// 父查子GET store_spu_sku_index/_search{ "query":{ "has_parent":{ "parent_type": "store", "query": { "match": { "storeId": "s1" } } } }} |
---|
// 执行结果{ "took": 3, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 2, "max_score": 1, "hits": [ { "_index": "store_spu_sku_index", "_type": "store_spu_sku", "_id": "spu2", "_score": 1, "_routing": "s1", "_source": { "spuName": "spu名称2-s1", "spuId": "spu2", "store_spu_sku_join": { "name": "spu", "parent": "s1" } } }, { "_index": "store_spu_sku_index", "_type": "store_spu_sku", "_id": "spu1", "_score": 1, "_routing": "s1", "_source": { "spuName": "spu名称1-s1", "spuId": "spu1", "store_spu_sku_join": { "name": "spu", "parent": "s1" } } } ] }} |
(2)子查父实践
// 子查父GET store_spu_sku_index/_search{ "query":{ "has_child":{ "type": "spu", "query": { "match": { "spuName": "spu" } } } }} |
---|
(3)子查孙实践
// 子查孙GET store_spu_sku_index/_search{ "query":{ "has_parent":{ "parent_type": "spu", "query": { "match": { "spuId": "spu1" } } } }} |
---|
// 执行结果{ "took": 16, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 2, "max_score": 1, "hits": [ { "_index": "store_spu_sku_index", "_type": "store_spu_sku", "_id": "sku2", "_score": 1, "_routing": "s1", "_source": { "skuName": "sku名称2-spu1", "skuId": "sku2", "store_spu_sku_join": { "name": "sku", "parent": "spu1" } } }, { "_index": "store_spu_sku_index", "_type": "store_spu_sku", "_id": "sku1", "_score": 1, "_routing": "s1", "_source": { "skuName": "sku名称1-spu1", "skuId": "sku1", "store_spu_sku_join": { "name": "sku", "parent": "spu1" } } } ] }} |
(4)子查孙加过滤条件实践
// 子查孙并过滤GET store_spu_sku_index/_search{"query": {"bool": {"should": {"has_parent": {"parent_type": "spu","query": {"match": {"spuId": "spu1"}}}},"filter": {"term": {"skuId": "sku1"}}}}} |
---|
// 执行结果{ "took": 28, "timed_out": false, "_shards": { "total": 5, "successful": 5, "skipped": 0, "failed": 0 }, "hits": { "total": 1, "max_score": 1, "hits": [ { "_index": "store_spu_sku_index", "_type": "store_spu_sku", "_id": "sku1", "_score": 1, "_routing": "s1", "_source": { "skuName": "sku名称1-spu1", "skuId": "sku1", "store_spu_sku_join": { "name": "sku", "parent": "spu1" } } } ] }} |
五、小结
通过以上实战演示,相信大家对 ES 父子文档有了一定初步的了解。继而在项目实践中,将一对多、一对多对多的关系按实际搜索场景应用并设计出合理的 ES 索引结构,以满足业务需求。
相关文章
- ClickHouse 最近跟Es杠上了,日志场景谁更适合
- python操作ES数据库「建议收藏」
- ES集群原理
- ES 基于查询结果的聚合
- 【ES三周年】+windows安装es、kibana教程
- ES海量数据的优化实践
- 【ES图文教程】4:给ES的扩展词词典及停用词词典
- ES三周年:从初体验到个人优化建议
- 【ES三周年】ELK日志分析平台 | 记一次基于鲲鹏麒麟操作系统分析平台搭建过程
- 【ES三周年】Elastic Security: 恶意代码防范
- IOS – OpenGL ES 调节图像反色 GPUImageColorInvertFilter
- 【ES三周年】ES最佳实践案例
- IOS – OpenGL ES 设置图像滤镜 GPUImageSoftEleganceFilter
- IOS – OpenGL ES 设置图像反遮罩锐化 GPUImageUnsharpMaskFilter
- 【ES三周年】ElasticSerach基础概念知识梳理
- 【ES三周年】ES查询—海量数据搜索深度分页优化
- ES与MySQL:混合技术的优势(es和mysql)
- Mongodb与ES组合,实现数据存储和搜索的双重效益(mongodb和es)
- Oracle数据实时全量同步至Elasticsearch(oracle全量同步es)
- ES实现Oracle数据实时双向同步(es 同步 oracle)
- Oracle与ES协同同步实现最佳性能(oracle与es同步)