一、开门见山

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

官方文档链接介绍如下：

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

es 多张表查询 es 多表联合查询_elasticsearch

  • Nested object：嵌套对象
  • Parent child：父子关系

二、商铺SPU模型

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

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

es 多张表查询 es 多表联合查询_java_02

es 多张表查询 es 多表联合查询_可视化_03

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

es 多张表查询 es 多表联合查询_可视化_04

（2）然后双击 kibana.bat 文件，启动对应版本的 kibana 服务：

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

es 多张表查询 es 多表联合查询_es 多张表查询_05

（3）本地访问kibana：http://localhost:5601/。

es 多张表查询 es 多表联合查询_java_06

（4）点击右侧菜单栏【Dev Tools】，如下所示：

es 多张表查询 es 多表联合查询_elasticsearch_07

（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"
        }
      }
    }
  }
}

es 多张表查询 es 多表联合查询_可视化_08

// ②查询索引类型的结构
GET /store_spu_sku_index/store_spu_sku/_mapping
或
GET /store_spu_sku_index

es 多张表查询 es 多表联合查询_es 多张表查询_09

// ③删除索引
DELETE store_spu_sku_index

es 多张表查询 es 多表联合查询_elasticsearch_10

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


es 多张表查询 es 多表联合查询_可视化_11

（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"
  }
}


es 多张表查询 es 多表联合查询_elasticsearch_12

（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"
  }
}


es 多张表查询 es 多表联合查询_elasticsearch_13

注意：

  • 孙子文档 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 多表联合查询_可视化_14

 

五、小结

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