ElasticSearch 查询优化之skipped shards

问题

PUT test_01
{
  "settings": {
    "number_of_shards": 50
  }, 
  "mappings": {
    "properties": {
      "createTimeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      },
      "timeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      }
    }
  },
  "aliases": {
    "test": {}
  }
}
  
PUT test_02
{
  "settings": {
    "number_of_shards": 50
  }, 
  "mappings": {
    "properties": {
      "createTimeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      },
      "timeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      }
    }
  },
  "aliases": {
    "test": {}
  }
}


PUT test_03
{
  "settings": {
    "number_of_shards": 50
  }, 
  "mappings": {
    "properties": {
      "createTimeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      },
      "timeDate": {
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        "type": "date"
      }
    }
  },
  "aliases": {
    "test": {}
  }
}

DELETE test_03

GET test_03


GET /test/_search


POST /test_01/_doc
{
  "createTimeDate": "2024-01-01 09:00:00",
  "timeDate" : "2024-01-01 09:00:00"
}


POST /test_02/_doc
{
  "createTimeDate": "2024-01-01 10:00:00",
  "timeDate" : "2024-01-02 09:00:00"
}

POST /test_03/_doc
{
  "createTimeDate": "2024-01-01 11:00:00",
  "timeDate" : "2024-01-03 09:00:00"
}


GET /test/_search
{
    "size": 10,
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "createTimeDate": {
                            "from": "2024-01-01 00:00:00",
                            "to": "2024-01-01 23:00:00",
                            "include_lower": true,
                            "include_upper": true,
                            "boost": 1
                        }
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1
        }
    }
}

• 根据createTimeDate可以跳过147个分片

通过timeDate查询

GET /test/_search
{
    "size": 10,
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "timeDate": {
                            "from": "2024-01-01 00:00:00",
                            "to": "2024-01-01 23:00:00",
                            "include_lower": true,
                            "include_upper": true,
                            "boost": 1
                        }
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1
        }
    }
}

通过timeDate可以跳过149个分片

生产环境中大数据量的查询可以通过skipped shards 时间来过滤分片来提高查询效率
待查询的分片数大于 128