ai-saas / saas-search

常用查询 - ES.md 24.3 KB

Elasticsearch 排查流程

1. 集群健康状态

# 集群整体健康（green / yellow / red）
curl -s -u 'saas:4hOaLaf41y2VuI8y' 'http://127.0.0.1:9200/_cluster/health?pretty'

2. 索引概览

# 查看所有租户索引状态与体积
curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/_cat/indices/search_products_tenant_*?v'

# 或查看全部索引
curl -s -u 'saas:4hOaLaf41y2VuI8y' 'http://127.0.0.1:9200/_cat/indices?v'

3. 分片分布

# 查看分片在各节点的分布情况
curl -s -u 'saas:4hOaLaf41y2VuI8y' 'http://127.0.0.1:9200/_cat/shards?v'

4. 分配诊断（如有异常）

# 当 health 非 green 或 shards 状态异常时，定位具体原因
curl -s -u 'saas:4hOaLaf41y2VuI8y' -X POST 'http://127.0.0.1:9200/_cluster/allocation/explain?pretty' \
  -H 'Content-Type: application/json' \
  -d '{"index":"search_products_tenant_163","shard":0,"primary":true}'

典型结论示例：disk_threshold — 磁盘超过高水位，新分片禁止分配。

5. 系统层检查

# 服务状态
sudo systemctl status elasticsearch

# 磁盘空间
df -h

# ES 数据目录占用
du -sh /var/lib/elasticsearch/

6. 配置与日志

# 配置文件
cat /etc/elasticsearch/elasticsearch.yml

# 实时日志
journalctl -u elasticsearch -f

快速排查路径

_cluster/health          → 确认集群状态（green/yellow/red）
    ↓
_cat/indices             → 检查索引体积与状态
    ↓
_cat/shards              → 查看分片分布
    ↓
_cluster/allocation/explain  → 定位分配问题（如需要）
    ↓
systemctl / df / 日志     → 系统层验证

以下是将您提供的 Elasticsearch 查询整理为 Markdown 格式的文档：

Elasticsearch 查询集合

租户相关

说明：索引已按租户拆分为 search_products_tenant_{tenant_id}，一般情况下不需要在查询中再按 tenant_id 过滤（可选保留用于排查）。

1. 根据 tenant_id / spu_id 查询

查询指定 spu_id 的商品（返回 title）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 11,
    "_source": ["title"],
    "query": {
        "bool": {
            "filter": [
                { "term": {"spu_id" : 206150} }
            ]
        }
    }
}'

查询所有商品（返回 title）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 100,
    "_source": ["title"],
    "query": {
        "match_all": {}
    }
}'

查询指定 spu_id 的商品（返回 title、keywords、tags）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 5,
    "_source": ["title", "keywords", "tags"],
    "query": {
        "bool": {
            "filter": [
                { "term": { "spu_id": "223167" } }
            ]
        }
    }
}'

组合查询：匹配标题 + 过滤标签

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["title", "keywords", "tags"],
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "title.en": {
                            "query": "Floerns Women Gothic Graphic Ribbed Strapless Tube Top Asymmetrical Ruched Bandeau Tops"
                        }
                    }
                }
            ],
            "filter": [
                { "terms": { "tags": ["女装", "派对"] } }
            ]
        }
    }
}'

组合查询：匹配标题 + 过滤租户（冗余示例）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["title"],
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "title.en": {
                            "query": "Floerns Women Gothic Graphic Ribbed Strapless Tube Top Asymmetrical Ruched Bandeau Tops"
                        }
                    }
                }
            ],
            "filter": [
                { "term": { "tenant_id": "170" } }
            ]
        }
    }
}'

2. 分析器测试

测试 index_ik 分析器

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_analyze' -H 'Content-Type: application/json' -d '{
    "analyzer": "index_ik",
    "text": "14寸第4代-眼珠实身冰雪公仔带手动大推车,搪胶雪宝宝"
}'

测试 query_ik 分析器

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_analyze' -H 'Content-Type: application/json' -d '{
    "analyzer": "query_ik",
    "text": "14寸第4代-眼珠实身冰雪公仔带手动大推车,搪胶雪宝宝"
}'

3. 多字段搜索 + 聚合（综合分面示例）

多字段匹配 + 聚合（category1、color、size、material）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "from": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "multi_match": {
                        "_name": "base_query",
                        "fields": [
                            "title.zh^3.0",
                            "brief.zh^1.5",
                            "description.zh",
                            "vendor.zh^1.5",
                            "tags",
                            "category_path.zh^1.5",
                            "category_name_text.zh^1.5",
                            "option1_values^0.5"
                        ],
                        "minimum_should_match": "75%",
                        "operator": "AND",
                        "query": "裙",
                        "tie_breaker": 0.9
                    }
                }
            ],
            "filter": [
                { "match_all": {} }
            ]
        }
    },
    "aggs": {
        "category1_name_facet": {
            "terms": {
                "field": "category1_name",
                "size": 15,
                "order": { "_count": "desc" }
            }
        },
        "specifications_color_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filter_by_name": {
                    "filter": { "term": { "specifications.name": "color" } },
                    "aggs": {
                        "value_counts": {
                            "terms": {
                                "field": "specifications.value",
                                "size": 20,
                                "order": { "_count": "desc" }
                            }
                        }
                    }
                }
            }
        },
        "specifications_size_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filter_by_name": {
                    "filter": { "term": { "specifications.name": "size" } },
                    "aggs": {
                        "value_counts": {
                            "terms": {
                                "field": "specifications.value",
                                "size": 15,
                                "order": { "_count": "desc" }
                            }
                        }
                    }
                }
            }
        },
        "specifications_material_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filter_by_name": {
                    "filter": { "term": { "specifications.name": "material" } },
                    "aggs": {
                        "value_counts": {
                            "terms": {
                                "field": "specifications.value",
                                "size": 10,
                                "order": { "_count": "desc" }
                            }
                        }
                    }
                }
            }
        }
    }
}'

4. 通用查询（通用索引示例）

查询所有

GET /search_products_tenant_2/_search
{
    "query": {
        "match_all": {}
    }
}

按 spu_id 查询（通用索引）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 5,
    "query": {
        "bool": {
            "filter": [
                { "term": { "spu_id": "74123" } }
            ]
        }
    }
}'

5. 统计租户总文档数

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_170/_count?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "match_all": {}
    }
}'

分面数据诊断相关查询

1. 检查 ES 文档的分面字段数据

1.1 查询特定租户的商品，显示分面相关字段

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "term": { "tenant_id": "162" }
    },
    "size": 1,
    "_source": [
        "spu_id", "title", "category1_name", "category2_name",
        "category3_name", "specifications", "option1_name",
        "option2_name", "option3_name"
    ]
}'

1.2 验证 category1_name 字段是否有数据

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "term": { "tenant_id": "162" } },
                { "exists": { "field": "category1_name" } }
            ]
        }
    },
    "size": 0
}'

1.3 验证 specifications 字段是否有数据

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "term": { "tenant_id": "162" } },
                { "exists": { "field": "specifications" } }
            ]
        }
    },
    "size": 0
}'

2. 分面聚合查询（Facet Aggregations）

2.1 category1_name 分面聚合

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "match_all": {} },
    "size": 0,
    "aggs": {
        "category1_name_facet": {
            "terms": { "field": "category1_name", "size": 50 }
        }
    }
}'

2.2 specifications.color 分面聚合

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "match_all": {} },
    "size": 0,
    "aggs": {
        "specifications_color_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "color" } },
                    "aggs": {
                        "values": { "terms": { "field": "specifications.value", "size": 50 } }
                    }
                }
            }
        }
    }
}'

2.3 specifications.size 分面聚合

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "match_all": {} },
    "size": 0,
    "aggs": {
        "specifications_size_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "size" } },
                    "aggs": {
                        "values": { "terms": { "field": "specifications.value", "size": 50 } }
                    }
                }
            }
        }
    }
}'

2.4 specifications.material 分面聚合

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "match_all": {} },
    "size": 0,
    "aggs": {
        "specifications_material_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "material" } },
                    "aggs": {
                        "values": { "terms": { "field": "specifications.value", "size": 50 } }
                    }
                }
            }
        }
    }
}'

2.5 综合分面聚合（category + color + size + material）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "match_all": {} },
    "size": 0,
    "aggs": {
        "category1_name_facet": { "terms": { "field": "category1_name", "size": 50 } },
        "specifications_color_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "color" } },
                    "aggs": { "values": { "terms": { "field": "specifications.value", "size": 50 } } }
                }
            }
        },
        "specifications_size_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "size" } },
                    "aggs": { "values": { "terms": { "field": "specifications.value", "size": 50 } } }
                }
            }
        },
        "specifications_material_facet": {
            "nested": { "path": "specifications" },
            "aggs": {
                "filtered": {
                    "filter": { "term": { "specifications.name": "material" } },
                    "aggs": { "values": { "terms": { "field": "specifications.value", "size": 50 } } }
                }
            }
        }
    }
}'

3. 检查 specifications 嵌套字段的详细结构

3.1 查看 specifications 的 name 字段有哪些值

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": { "term": { "tenant_id": "162" } },
    "size": 0,
    "aggs": {
        "specifications_names": {
            "nested": { "path": "specifications" },
            "aggs": {
                "name_values": { "terms": { "field": "specifications.name", "size": 20 } }
            }
        }
    }
}'

3.2 查看某个商品的完整 specifications 数据

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "term": { "tenant_id": "162" } },
                { "exists": { "field": "specifications" } }
            ]
        }
    },
    "size": 1,
    "_source": ["spu_id", "title", "specifications"]
}'

3.3 enriched_attributes：.value.zh / .value.en 的 keyword 精确匹配与 text 全文匹配

enriched_attributes 为 nested，检索需包在 nested 里。.keyword 子字段带 lowercase normalizer，英文词建议用小写做 term。

keyword 精确匹配（示例词：中文 法式风格，英文 long skirt）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "enriched_attributes"],
    "query": {
        "nested": {
            "path": "enriched_attributes",
            "query": {
                "bool": {
                    "should": [
                        { "term": { "enriched_attributes.value.zh.keyword": "法式风格" } },
                        { "term": { "enriched_attributes.value.en.keyword": "long skirt" } }
                    ],
                    "minimum_should_match": 2
                }
            }
        }
    }
}'

text 全文匹配（经 index_ik / english 分词；可与上式对照）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "enriched_attributes"],
    "query": {
        "nested": {
            "path": "enriched_attributes",
            "query": {
                "bool": {
                    "should": [
                        { "match": { "enriched_attributes.value.zh": "法式风格" } },
                        { "match": { "enriched_attributes.value.en": "long skirt" } }
                    ],
                    "minimum_should_match": 2
                }
            }
        }
    }
}'

若需要 拼写容错，可在 match 上增加 "fuzziness": "AUTO"（对英文更常见）。

3.4 option1_values：keyword 与 text 分别查 蓝色 / blue

keyword 精确匹配

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "option1_values"],
    "query": {
        "bool": {
            "should": [
                { "term": { "option1_values.zh.keyword": "蓝色" } },
                { "term": { "option1_values.en.keyword": "blue" } }
            ],
            "minimum_should_match": 2
        }
    }
}'

text 全文匹配

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "option1_values"],
    "query": {
        "bool": {
            "should": [
                { "match": { "option1_values.zh": "蓝色" } },
                { "match": { "option1_values.en": "blue" } }
            ],
            "minimum_should_match": 2
        }
    }
}'

3.5 enriched_tags.zh / enriched_tags.en：keyword 与 text（高腰 / high waist）

keyword 精确匹配

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "enriched_tags"],
    "query": {
        "bool": {
            "should": [
                { "term": { "enriched_tags.zh.keyword": "高腰" } },
                { "term": { "enriched_tags.en.keyword": "high waist" } }
            ],
            "minimum_should_match": 2
        }
    }
}'

text 全文匹配

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "enriched_tags"],
    "query": {
        "bool": {
            "should": [
                { "match": { "enriched_tags.zh": "高腰" } },
                { "match": { "enriched_tags.en": "high waist" } }
            ],
            "minimum_should_match": 2
        }
    }
}'

3.6 specifications：value_keyword 与 value_text.zh / value_text.en（蓝色 / blue）

specifications 为 nested，value_keyword 为整词匹配；value_text.* 可同时 term 子字段或 match 主 text。

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "size": 1,
    "_source": ["spu_id", "title", "specifications"],
    "query": {
        "nested": {
            "path": "specifications",
            "query": {
                "bool": {
                    "should": [
                        { "term": { "specifications.value_keyword": "蓝色" } },
                        { "term": { "specifications.value_keyword": "blue" } },
                        { "term": { "specifications.value_text.zh.keyword": "蓝色" } },
                        { "term": { "specifications.value_text.en.keyword": "blue" } },
                        { "match": { "specifications.value_text.zh": "蓝色" } },
                        { "match": { "specifications.value_text.en": "blue" } }
                    ],
                    "minimum_should_match": 5
                }
            }
        }
    }
}'

仅查 keyword 类（value_keyword + value_text.*.keyword）时可从上面 should 里删掉两条 match；仅 全文 时可只保留两条 match。

4. 统计查询

4.1 统计有 category1_name 的文档数量

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_count?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "exists": { "field": "category1_name" } }
            ]
        }
    }
}'

4.2 统计有 specifications 的文档数量

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_count?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "exists": { "field": "specifications" } }
            ]
        }
    }
}'

5. 诊断问题场景

5.1 查找没有 category1_name 但有 category 的文档（MySQL 有数据但 ES 没有）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "term": { "tenant_id": "162" } }
            ],
            "must_not": [
                { "exists": { "field": "category1_name" } }
            ]
        }
    },
    "size": 10,
    "_source": ["spu_id", "title", "category_name_text", "category_path"]
}'

5.2 查找有 option 但没有 specifications 的文档（数据转换问题）

curl -u 'saas:4hOaLaf41y2VuI8y' -X GET 'http://localhost:9200/search_products_tenant_163/_search?pretty' -H 'Content-Type: application/json' -d '{
    "query": {
        "bool": {
            "filter": [
                { "term": { "tenant_id": "162" } },
                { "exists": { "field": "option1_name" } }
            ],
            "must_not": [
                { "exists": { "field": "specifications" } }
            ]
        }
    },
    "size": 10,
    "_source": ["spu_id", "title", "option1_name", "option2_name", "option3_name", "specifications"]
}'

重排序示例

GET /search_products_tenant_170/_search
{
    "query": {
        "match": {
            "title.en": {
                "query": "quick brown fox",
                "minimum_should_match": "90%"
            }
        }
    },
    "rescore": {
        "window_size": 50,
        "query": {
            "rescore_query": {
                "match_phrase": {
                    "title.en": {
                        "query": "quick brown fox",
                        "slop": 50
                    }
                }
            }
        }
    }
}

检查字段是否存在

GET search_products_tenant_163/mapping GET search_products_tenant163/_field_caps?fields=*

curl -u 'saas:4hOaLaf41y2VuI8y' -X POST \
'http://localhost:9200/search_products_tenant_163/_count' \
-H 'Content-Type: application/json' \
-d '{
    "query": {
        "bool": {
            "filter": [
                { "exists": { "field": "title_embedding" } }
            ]
        }
    }
}'

curl -u 'saas:4hOaLaf41y2VuI8y' -X POST \
'http://localhost:9200/search_products_tenant_163/_count' \
-H 'Content-Type: application/json' \
-d '{
"query": {
"nested": {
    "path": "image_embedding",
    "query": {
    "exists": {
        "field": "image_embedding.vector"
    }
    }
}
}
}'