一、开门见山

关系型数据库 MySQL 的 join 关系如何在 ES 中实现。

官方文档链接介绍如下:

https://www.elastic.co/guide/en/elasticsearch/reference/6.3/joining-queries.html

7d534034df18eebfcf1b2dbf53f51ce5.png

  • Nested object:嵌套对象

  • Parent child:父子关系

二、商铺SPU模型

电商系统常见的一对多对多关系:

一个商铺下有多个商品,一个商品下有多个单品,如北京 iphone xxx 店铺,有 iphone 手机、mac 电脑,这些属于商品,而用户购买的 iphone13 128G 黑色国行手机,这个就属于售卖的单品。关系图如下所示:

eda8ee31b8f173206e021ac2d9713ffe.png

98fefd9fd55d1d7176f330ce10538490.png

下面以父子文档为例,介绍 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

402caa08ef5ac46bed6898b384dcb149.png

(2)然后双击 kibana.bat 文件,启动对应版本的 kibana 服务:

\kibana-6.3.2-windows-x86_64\bin\ kibana.bat

f4db19eefed0aa0317ff6fce7e1619e6.png

(3)本地访问kibana:http://localhost:5601/。

55c0a98074081095c98d324095eb2743.png

(4)点击右侧菜单栏【Dev Tools】,如下所示:

b58257e906ad290cf827dadebb479704.png

(5)构建祖孙三层结构索引

// ①创建store_spu_sku_index索引并构建store_spu_sku类型

PUT /store_spu_sku_index

{

  "mappings": {

    "store_spu_sku": {

      "properties": {

        "store_spu_sku_join": {

          "type": "join",  #join关系

          "relations": {

            "store": "spu",    #左父又子

            "spu": "sku"

          }

        },

        "storeId": {

          "type": "keyword"

        },

        "storeName": {

          "type": "text"

        },

        "spuId": {

          "type": "keyword"

        },

        "spuName": {

          "type": "text"

        },

        "skuId": {

          "type": "keyword"

        },

        "skuName": {

          "type": "text"

        }

      }

    }

  }

}

88da551da4f5b6e1cae5ce08f781f78d.png

// ②查询索引类型的结构

GET /store_spu_sku_index/store_spu_sku/_mapping

GET /store_spu_sku_index

fb1a4c51aeefde7688a385867ca16dcd.png

// ③删除索引

DELETE store_spu_sku_index

29ec1fb2547cfb76be5c18868218c829.png

注(以下对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"

}

ec270320b4673f82873d8e046e33ee8f.png

(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"

  }

}

8c9c2af7964b278d27e00320dfee02b7.png

(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"

  }

}

8cfed0ac2fad6b028ad0f2b8ce94e8da.png

注意:

  • 孙子文档 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"

          }

        }

      }

    ]

  }

}

(5) 孙查父(嵌套)

GET store_spu_sku_index/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "storeId": "s1"
          }
        },
        {
          "has_child": {
            "query": {
              "bool": {
                "filter": [
                  {
                    "term": {
                      "spuId": "spu1"
                    }
                  },
                  {
                    "has_child": {
                      "query": {
                        "bool": {
                          "filter": [
                            {
                              "term": {
                                "skuId": "sku2"
                              }
                            }
                          ]
                        }
                      },
                      "type": "sku"
                    }
                  }
                ]
              }
            },
            "type": "spu"
          }
        }
      ]
    }
  }
}

#执行结果

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 0,
    "hits": [
      {
        "_index": "store_spu_sku_index",
        "_type": "store_spu_sku",
        "_id": "s1",
        "_score": 0,
        "_source": {
          "storeId": "s1",
          "storeName": "店铺名称s1",
          "store_spu_sku_join": "store"
        }
      }
    ]
  }
}

 

五、小结

通过以上实战演示,相信大家对 ES 父子文档有了一定初步的了解。继而在项目实践中,将一对多、一对多对多的关系按实际搜索场景应用并设计出合理的 ES 索引结构,以满足业务需求。

# 精彩推荐 #

 自研 Starter 组件/中间件开发

 分布式系统「全链路日志追踪」实战之 RestTemplate & Feign

  小白都能看得懂的服务调用链路追踪设计与实现

  [三步法] 可视化分析定位线上 JVM 问题

  从 Java 代码如何运行聊到 JVM 和对象的创建-分配-定位-布局-垃圾回收

 "在看"吗,赶快分享和收藏吧

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐