GitHub Copilot - Elasticsearch 和 MySQL 单表查询耗时比对

技术文章 > 性能 > GitHub Copilot - Elasticsearch 和 MySQL 单表查询耗时比对

当单表数据库超过百万后，数据库 like '%xxx%' 查询明显变慢，为了对比 Elasticsearch 的效果，将百万级的测试数据导入到 Elasticsearch 中对比看看效果。导入和查询 Elasticsearch 的过程完全通过 GitHub Copilot Chat 辅助编码。

Elasticsearch 环境

使用 Docker Compose 快速搭建环境，compose.yaml 配置如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
version: "2"
services:
  elasticsearch:
    image: docker.io/bitnami/elasticsearch:8
    ports:
      - '9200:9200'
      - '9300:9300'
    volumes:
      - "elasticsearch_data:/bitnami/elasticsearch"
  kibana:
    image: docker.io/bitnami/kibana:8
    ports:
      - "5601:5601"
    volumes:
      - "kibana_data:/bitnami/kibana"
    depends_on:
      - elasticsearch
volumes:
  elasticsearch_data:
    driver: local
  kibana_data:
    driver: local

参考：

https://github.com/bitnami/containers/tree/main/bitnami/kibana
https://github.com/bitnami/containers/tree/main/bitnami/elasticsearch

数据库 VS Elasticsearch

数据库查询

SQL：

1
2
3
4
SELECT * FROM itsm_base_enterprisemanagement 
WHERE cname like '%1876%' 
and ctaxcode like '%767%' 
and (ctypeid_name = '6' or ctypeid_name = '16');`。

查询结果数：860
查询耗时：3.593s
查询截图：

Elasticsearch 查询

HTTP：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
GET /itsm_base_enterprisemanagement_index/_search
{
  "size": 10000,
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "cname": "*1876*"
          }
        },
        {
          "wildcard": {
            "ctaxcode": "*767*"
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "ctypeid_name": "6"
                }
              },
              {
                "term": {
                  "ctypeid_name": "16"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

部分查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
{
  "took": 78,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 860,
      "relation": "eq"
    },
    "max_score": 5.401209,
    "hits": [
      {
        "_index": "itsm_base_enterprisemanagement_index",
        "_id": "1xY-SIwBGWXLItGpKRZG",
        "_score": 5.401209,
        "_source": {
          "csynflag": "0",
          "cname": "[email protected]",
          "isvalid": "0",
          "cTimeStamp": "2023-12-06 14:53:41",
          "cCreatorId": "[email protected]",
          "cCreateDate": "2023-12-06 14:53:41",
          "cGuid": "[email protected]",
          "cmaintainerid": "[email protected]",
          "ctypeid_name": "6",
          "ctaxcode": "[email protected]",
          "ccrminsertsyn": "0",
          "ccrmupdatesyn": "1",
          "cremark": "[email protected]"
        }
      },

查询结果数：860
查询耗时：155ms
查询截图：

对比结果： MySQL 比 ES 慢 23 倍

GitHub Copilot 对话过程

GitHub Copilot:

Hi @abel533, how can I help you? I’m powered by AI, so surprises and mistakes are possible. Make sure to verify any generated code or suggestions, and share feedback so that we can learn and improve.

ASK:

JDBC 连接数据库代码。

GitHub Copilot:

要使用 JDBC 连接数据库，您需要以下步骤：

  1. 加载并注册 JDBC 驱动程序
  2. 创建数据库连接
  3. 创建 Statement 对象
  4. 执行 SQL 查询
  5. 处理结果集
  6. 关闭连接

以下是一个简单的示例，该示例使用 JDBC 连接到 MySQL 数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ESInsert {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "your_username";
        String password = "your_password";

        try {
            // 1. Load and register driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. Create connection
            Connection conn = DriverManager.getConnection(url, user, password);

            // 3. Create statement
            Statement stmt = conn.createStatement();

            // 4. Execute SQL query
            // TODO: Add your SQL query here

            // 5. Process the result set
            // TODO: Process your result set here

            // 6. Close connection
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

请注意，您需要将 your_database，your_username 和 your_password 替换为您的数据库名称，用户名和密码。此外，您可能需要根据您的实际需求添加 SQL 查询和处理结果集的代码。

ASK:

使用 stmt 查询 itsm_base_enterprisemanagement 表的全部数据，根据表的字段在 ES 创建索引，将所有数据插入到 ES 中。

GitHub Copilot:

要将 itsm_base_enterprisemanagement 表的所有数据查询出来，并根据表的字段在 ES 创建索引，然后将所有数据插入到 ES 中，我们可以按照以下步骤进行：

  1. 使用 stmt 查询 itsm_base_enterprisemanagement 表的所有数据。
  2. 获取查询结果的元数据，以获取列名，这些列名将用作 ES 索引的字段。
  3. 创建 ES 索引。
  4. 将查询结果插入到 ES 索引中。

以下是实现这些步骤的 Java 代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
import org.elasticsearch.client.RestHighLevelClient;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.client.indices.CreateIndexRequest;
import org.elasticsearch.client.indices.CreateIndexResponse;
import org.elasticsearch.common.xcontent.XContentBuilder;
import org.elasticsearch.common.xcontent.XContentFactory;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

// 1. Query all data from itsm_base_enterprisemanagement
String query = "SELECT * FROM itsm_base_enterprisemanagement";
ResultSet rs = stmt.executeQuery(query);

// 2. Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// 3. Create ES index
String indexName = "itsm_base_enterprisemanagement_index";
CreateIndexRequest request = new CreateIndexRequest(indexName);

XContentBuilder builder = XContentFactory.jsonBuilder();
builder.startObject();
{
    builder.startObject("properties");
    {
        for (int i = 1; i <= columnCount; i++) {
            builder.startObject(rsmd.getColumnName(i));
            {
                builder.field("type", "text");
            }
            builder.endObject();
        }
    }
    builder.endObject();
}
builder.endObject();
request.source(builder);

RestHighLevelClient client = new RestHighLevelClient(
    RestClient.builder(
        new HttpHost("localhost", 9200, "http")
    )
);
CreateIndexResponse createIndexResponse = client.indices().create(request, RequestOptions.DEFAULT);

// 4. Insert all data into ES index
while (rs.next()) {
    Map<String, Object> jsonMap = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        jsonMap.put(rsmd.getColumnName(i), rs.getObject(i));
    }

    IndexRequest indexRequest = new IndexRequest(indexName)
        .source(jsonMap);
    IndexResponse indexResponse = client.index(indexRequest, RequestOptions.DEFAULT);
}

// Don't forget to close the connections
rs.close();
stmt.close();
conn.close();
client.close();

请注意，这段代码假设您已经在项目中添加了 Elasticsearch 的 Java 客户端依赖，并且 Elasticsearch 服务器正在本地的 9200 端口上运行。如果不是，请根据实际情况修改代码。

ASK:

针对 ES 的操作不要使用 elasticsearch-rest-high-level-client sdk，使用 elasticsearch-rest-client 8.11 版本的 sdk

GitHub Copilot:

要使用 elasticsearch-rest-client 8.11 版本的 SDK 进行 ES 操作，我们需要对上述代码进行一些修改。以下是修改后的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
import org.apache.http.HttpHost;
import org.elasticsearch.client.Request;
import org.elasticsearch.client.Response;
import org.elasticsearch.client.RestClient;
import org.elasticsearch.client.RestClientBuilder;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

// 1. Query all data from itsm_base_enterprisemanagement
String query = "SELECT * FROM itsm_base_enterprisemanagement";
ResultSet rs = stmt.executeQuery(query);

// 2. Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// 3. Create ES index
String indexName = "itsm_base_enterprisemanagement_index";

RestClientBuilder builder = RestClient.builder(new HttpHost("localhost", 9200, "http"));
RestClient restClient = builder.build();

// Create index request
Request createIndexRequest = new Request("PUT", "/" + indexName);
createIndexRequest.setJsonEntity(
    "{\n" +
    "  \"mappings\": {\n" +
    "    \"properties\": {\n" +
    // Add fields to the index
    "    }\n" +
    "  }\n" +
    "}"
);
Response createIndexResponse = restClient.performRequest(createIndexRequest);

// 4. Insert all data into ES index
while (rs.next()) {
    Map<String, Object> jsonMap = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        jsonMap.put(rsmd.getColumnName(i), rs.getObject(i));
    }

    // Create index request
    Request indexRequest = new Request("POST", "/" + indexName + "/_doc");
    indexRequest.setJsonEntity(jsonMap.toString());
    Response indexResponse = restClient.performRequest(indexRequest);
}

// Don't forget to close the connections
rs.close();
stmt.close();
conn.close();
restClient.close();

请注意，这段代码假设您的 Elasticsearch 服务器正在本地的 9200 端口上运行。如果不是，请根据实际情况修改代码。此外，这段代码没有处理异常，您可能需要根据实际需求添加异常处理代码。

ASK:

// Add fields to the index，此处注释的属性根据数据库字段自动生成

GitHub Copilot:

要根据数据库字段自动生成索引属性，我们需要在查询结果集的元数据中获取列名，并将这些列名作为索引的字段。以下是修改后的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
// 2. Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// 3. Create ES index
String indexName = "itsm_base_enterprisemanagement_index";

RestClientBuilder builder = RestClient.builder(new HttpHost("localhost", 9200, "http"));
RestClient restClient = builder.build();

StringBuilder mappings = new StringBuilder();
mappings.append("{\n");
mappings.append("  \"mappings\": {\n");
mappings.append("    \"properties\": {\n");

// Add fields to the index
for (int i = 1; i <= columnCount; i++) {
    mappings.append("      \"").append(rsmd.getColumnName(i)).append("\": {\n");
    mappings.append("        \"type\": \"text\"\n");
    mappings.append("      },\n");
}

// Remove the last comma and close the JSON object
mappings.setLength(mappings.length() - 2);
mappings.append("\n    }\n  }\n}");

// Create index request
Request createIndexRequest = new Request("PUT", "/" + indexName);
createIndexRequest.setJsonEntity(mappings.toString());
Response createIndexResponse = restClient.performRequest(createIndexRequest);

这段代码将遍历结果集的元数据，获取每个列的名称，并将这些名称作为 ES 索引的字段。然后，它将创建一个包含这些字段的 ES 索引。

ASK:

type 类型不要固定为 text，根据数据库字段类型进行转换。

GitHub Copilot:

要根据数据库字段类型进行转换，我们需要获取结果集元数据中的每个列的类型，并将这些类型用作 ES 索引的字段类型。以下是修改后的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
// 2. Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// 3. Create ES index
String indexName = "itsm_base_enterprisemanagement_index";

RestClientBuilder builder = RestClient.builder(new HttpHost("localhost", 9200, "http"));
RestClient restClient = builder.build();

StringBuilder mappings = new StringBuilder();
mappings.append("{\n");
mappings.append("  \"mappings\": {\n");
mappings.append("    \"properties\": {\n");

// Add fields to the index
for (int i = 1; i <= columnCount; i++) {
    mappings.append("      \"").append(rsmd.getColumnName(i)).append("\": {\n");
    mappings.append("        \"type\": \"").append(convertSqlTypeToESType(rsmd.getColumnType(i))).append("\"\n");
    mappings.append("      },\n");
}

// Remove the last comma and close the JSON object
mappings.setLength(mappings.length() - 2);
mappings.append("\n    }\n  }\n}");

// Create index request
Request createIndexRequest = new Request("PUT", "/" + indexName);
createIndexRequest.setJsonEntity(mappings.toString());
Response createIndexResponse = restClient.performRequest(createIndexRequest);

这段代码将遍历结果集的元数据，获取每个列的名称和类型，并将这些名称和类型作为 ES 索引的字段和类型。然后，它将创建一个包含这些字段和类型的 ES 索引。
请注意，我添加了一个名为 convertSqlTypeToESType 的方法，该方法将 SQL 类型转换为 ES 类型。这是该方法的一个简单实现：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
private static String convertSqlTypeToESType(int sqlType) {
    switch (sqlType) {
        case Types.INTEGER:
        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.BIGINT:
            return "integer";
        case Types.FLOAT:
        case Types.REAL:
        case Types.DOUBLE:
        case Types.NUMERIC:
        case Types.DECIMAL:
            return "float";
        case Types.BOOLEAN:
            return "boolean";
        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
            return "date";
        default:
            return "text";
    }
}

这个方法根据 SQL 类型返回对应的 ES 类型。如果 SQL 类型不在这个方法处理的范围内，那么默认返回”text”。您可能需要根据实际需求修改这个方法。

ASK:

new Request("POST", "/" + indexName + "/\_doc") 这里能否改为批量插入？

GitHub Copilot:

是的，Elasticsearch REST Client 支持批量插入操作。我们可以使用”_bulk” API 来进行批量插入。以下是修改后的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 4. Insert all data into ES index
StringBuilder bulkRequestBody = new StringBuilder();
while (rs.next()) {
    Map<String, Object> jsonMap = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        jsonMap.put(rsmd.getColumnName(i), rs.getObject(i));
    }

    // Create index request
    bulkRequestBody.append("{\"index\":{\"_index\":\"").append(indexName).append("\"}}\n");
    bulkRequestBody.append(jsonMap.toString()).append("\n");
}

// Execute bulk request
Request bulkRequest = new Request("POST", "/_bulk");
bulkRequest.setJsonEntity(bulkRequestBody.toString());
Response bulkResponse = restClient.performRequest(bulkRequest);

这段代码将遍历结果集，为每一行数据创建一个索引请求，并将这些请求添加到批量请求的主体中。然后，它将执行批量请求，将所有数据一次性插入到 ES 索引中。

ASK:

批量插入改为每 100 个提交一次

GitHub Copilot:

要实现每 100 条数据提交一次的批量插入，我们可以在遍历结果集时，每当累计到 100 条数据时，就执行一次批量请求。以下是修改后的代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// 4. Insert all data into ES index
StringBuilder bulkRequestBody = new StringBuilder();
int count = 0;
while (rs.next()) {
    Map<String, Object> jsonMap = new HashMap<>();
    for (int i = 1; i <= columnCount; i++) {
        jsonMap.put(rsmd.getColumnName(i), rs.getObject(i));
    }

    // Create index request
    bulkRequestBody.append("{\"index\":{\"_index\":\"").append(indexName).append("\"}}\n");
    bulkRequestBody.append(jsonMap.toString()).append("\n");

    count++;

    // Execute bulk request every 100 records
    if (count % 100 == 0) {
        Request bulkRequest = new Request("POST", "/_bulk");
        bulkRequest.setJsonEntity(bulkRequestBody.toString());
        Response bulkResponse = restClient.performRequest(bulkRequest);

        // Clear the bulk request body
        bulkRequestBody.setLength(0);
    }
}

// Execute the remaining requests if there are less than 100
if (bulkRequestBody.length() > 0) {
    Request bulkRequest = new Request("POST", "/_bulk");
    bulkRequest.setJsonEntity(bulkRequestBody.toString());
    Response bulkResponse = restClient.performRequest(bulkRequest);
}

这段代码将遍历结果集，为每一行数据创建一个索引请求，并将这些请求添加到批量请求的主体中。每当累计到 100 条数据时，它就会执行一次批量请求。如果最后还剩下少于 100 条数据，它也会执行一次批量请求，以确保所有数据都被插入到 ES 索引中。

ASK:

itsm_base_enterprisemanagement 表有 100 多万数据，将查询改为流式查询，每次加载一部分，避免内存不足。

GitHub Copilot:

要将查询改为流式查询，我们可以使用 JDBC 的 Statement 对象的 setFetchSize 方法来设置每次从数据库中获取的行数。以下是修改后的代码：

1
2
3
4
5
6
7
// 1. Query all data from itsm_base_enterprisemanagement
String query = "SELECT * FROM itsm_base_enterprisemanagement";

// Set the fetch size to a reasonable number to avoid memory issues
stmt.setFetchSize(1000);

ResultSet rs = stmt.executeQuery(query);

在这段代码中，我们设置了每次从数据库中获取 1000 行数据。这样，即使 itsm_base_enterprisemanagement 表有 100 多万数据，我们也可以避免一次性加载所有数据导致的内存不足问题。

ASK:

在 mysql jdbc 中，设置 1000 有效吗？是否应该 stmt.setFetchSize(Integer.MIN_VALUE);？

GitHub Copilot:

在 MySQL JDBC 驱动中，setFetchSize(Integer.MIN_VALUE)被用来开启流式查询，这样可以避免一次性加载所有数据导致的内存不足问题。这是因为 MySQL JDBC 驱动对 setFetchSize 方法的实现与 JDBC 规范不同。在 JDBC 规范中，setFetchSize 方法的参数表示每次从数据库中获取的行数，但在 MySQL JDBC 驱动中，如果参数为 Integer.MIN_VALUE，则会开启流式查询。

如果你想每次从数据库中获取固定数量的行，例如 1000 行，你可以尝试设置 setFetchSize(1000)，但是需要注意的是，这在 MySQL JDBC 驱动中可能不会按预期工作，因为 MySQL JDBC 驱动的实现与 JDBC 规范不同。

所以，如果你正在使用 MySQL JDBC 驱动，并且想要开启流式查询以处理大量数据，你应该使用 stmt.setFetchSize(Integer.MIN_VALUE);。

ASK:

如何在 kibana 的 devtool 中查询 itsm_base_enterprisemanagement_index 的数量

GitHub Copilot:

要在 Kibana 的 DevTools 中查询 itsm_base_enterprisemanagement_index 的数量，您可以使用 Elasticsearch 的 count API。以下是查询语句：

1
GET /itsm_base_enterprisemanagement_index/_count

这个查询将返回 itsm_base_enterprisemanagement_index 索引中的文档数量。

ASK:

查询 itsm_base_enterprisemanagement_index 索引中 cname 字段包含 1876 的所有数据的 cguid

GitHub Copilot:

要查询 itsm_base_enterprisemanagement_index 索引中 cname 字段包含 1876 的所有数据的 cguid，您可以使用 Elasticsearch 的 match 查询。在 Kibana 的 DevTools 中，您可以使用以下查询：

1
2
3
4
5
6
7
8
9
GET /itsm_base_enterprisemanagement_index/_search
{
  "query": {
    "match": {
      "cname": "1876"
    }
  },
  "_source": ["cguid"]
}

这个查询将返回 cname 字段包含 1876 的所有文档的 cguid 字段。

ASK:

上面的查询结果为空，为了验证里面有没有数据，先写个查询，获取该索引 10 条数据。

GitHub Copilot:

要查询 itsm_base_enterprisemanagement_index 索引的前 10 条数据，您可以使用 Elasticsearch 的 size 参数来限制返回的文档数量。在 Kibana 的 DevTools 中，您可以使用以下查询：

1
2
3
4
GET /itsm_base_enterprisemanagement_index/_search
{
  "size": 10
}

这个查询将返回 itsm_base_enterprisemanagement_index 索引的前 10 条文档。

ASK:

索引中没有任何数据，请检查 ESInsert.java 中是否存在问题？

GitHub Copilot:

首先，我们需要确认以下几点：

  1. 数据库连接信息是否正确？请检查url、user和password是否正确。
  2. 数据库中的itsm_base_enterprisemanagement表是否存在，并且是否有数据？
  3. Elasticsearch服务器是否在运行，并且是否可以通过localhost:9200访问？
  4. 是否有足够的权限在Elasticsearch中创建索引和插入数据？

如果以上所有条件都满足，那么ESInsert.java应该能够正确地从数据库中获取数据并插入到Elasticsearch中。

然而，如果在运行ESInsert.java时抛出了异常，那么可能会在插入数据之前就终止了程序，导致Elasticsearch中没有数据。为了找出可能的问题，你可以检查是否有异常被抛出。在ESInsert.java的main方法中，所有的异常都被捕获并重新抛出为RuntimeException，你可以在重新抛出异常之前打印出异常的详细信息：

1
2
3
4
} catch (Exception e) {
    e.printStackTrace();
    throw new RuntimeException(e);
}

这样，如果有异常被抛出，你就可以在控制台看到异常的详细信息，这将有助于你找出问题所在。

ASK:

我发现 bug 了，在 jsonMap.toString() 这里，这里没有转换为 JSON，导致插入数据出错。

GitHub Copilot:

确实，jsonMap.toString()并不会将 Map 对象转换为 JSON 格式的字符串，而是将其转换为默认的字符串表示形式，这可能会导致插入数据时出错。为了解决这个问题，我们可以使用一个 JSON 库，如 Gson，来将 Map 对象转换为 JSON 格式的字符串。

首先，需要在项目的 pom.xml 文件中添加 Gson 的依赖：

1
2
3
4
5
<dependency>
    <groupId>com.google.code.gson</groupId>
    <artifactId>gson</artifactId>
    <version>2.8.9</version>
</dependency>

然后，我们可以在 ESInsert.java 文件中使用 Gson 来转换 Map 对象：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import com.google.gson.Gson;

// ...

public class ESInsert {
    // ...

    public static void main(String[] args) {
        // ...

        Gson gson = new Gson();

        // ...

        while (rs.next()) {
            Map<String, Object> jsonMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                jsonMap.put(rsmd.getColumnName(i), rs.getObject(i));
            }

            // Create index request
            bulkRequestBody.append("{\"index\":{\"_index\":\"").append(indexName).append("\"}}\n");
            bulkRequestBody.append(gson.toJson(jsonMap)).append("\n");

            // ...
        }

        // ...
    }

    // ...
}

在这段修改后的代码中，我们创建了一个 Gson 对象，并使用 gson.toJson(jsonMap)来将 Map 对象转换为 JSON 格式的字符串。

ASK:

为了测试插入索引一些数据，如何使用 HTTP 命令清除索引 itsm_base_enterprisemanagement_index 的所有数据？

GitHub Copilot:

要使用 HTTP 命令清除索引 itsm_base_enterprisemanagement_index 的所有数据，您可以使用 Elasticsearch 的 delete API。在 Kibana 的 DevTools 中，您可以使用以下命令：

1
DELETE /itsm_base_enterprisemanagement_index

这个命令将删除整个 itsm_base_enterprisemanagement_index 索引，包括索引中的所有数据。如果您想再次插入数据，您需要重新创建索引。

ASK:

如何使用 HTTP 请求，模糊查询 cName 包含 1867 的所有数据，查询结果只返回 cGuid？

GitHub Copilot:

要使用 HTTP 请求模糊查询 cName 包含 1867 的所有数据，并且只返回 cGuid，你可以使用 Elasticsearch 的 match 查询和_source 字段来限制返回的字段。在 Kibana 的 DevTools 中，你可以使用以下查询：

1
2
3
4
5
6
7
8
9
GET /itsm_base_enterprisemanagement_index/_search
{
  "query": {
    "match": {
      "cName": "1867"
    }
  },
  "_source": ["cGuid"]
}

这个查询将返回 cName 字段包含 1867 的所有文档的 cGuid 字段。

ASK:

match是模糊匹配吗？为什么查询不到结果？

GitHub Copilot:

Elasticsearch中的match查询实际上是全文本查询，它不仅可以进行模糊匹配，还会对查询的文本进行分析。这意味着，如果你的字段是被分析的（默认情况下，字符串字段会被分析），那么match查询会在执行查询之前，先将查询的文本分割成单个的词条（或称为tokens），然后对每个词条进行查询。 如果你的查询没有返回结果，可能有以下几个原因：

  1. 你查询的文本在字段中不存在。请检查你的查询文本是否正确。
  2. 你的字段是一个未分析的字段。未分析的字段不会被分割成词条，因此match查询可能无法找到匹配的结果。如果你的字段是未分析的，你可能需要使用term查询。
  3. 你的字段是一个数字或日期字段。match查询主要用于文本字段，对于数字或日期字段，你可能需要使用range查询。

如果你确定你的查询文本在字段中应该存在，但是match查询仍然返回空结果，你可能需要检查你的字段是否被正确地分析。你可以使用_analyze API来测试Elasticsearch如何分析你的字段。

ASK:

如何使用 1435 匹配 [email protected] 和 [email protected]？

GitHub Copilot:

要匹配包含”1435”的邮箱地址，如”[email protected]“和”[email protected]“，您可以使用 Elasticsearch 的 wildcard 查询。在 Kibana 的 DevTools 中，您可以使用以下查询：

1
2
3
4
5
6
7
8
GET /itsm_base_enterprisemanagement_index/_search
{
  "query": {
    "wildcard": {
      "email": "*1435*"
    }
  }
}

这个查询将返回 email 字段包含”1435”的所有文档。

请注意，wildcard 查询可能会比其他类型的查询更消耗资源，因为它需要检查每个字段值是否匹配给定的模式。因此，如果可能，最好使用其他类型的查询，如 match 查询或 term 查询。

ASK:

上述 GET 查询返回结果的 json 结构的各部分是什么含义？为什么没有返回全部匹配的数据？

GitHub Copilot:

Elasticsearch 的 GET 查询返回的 JSON 结构通常包含以下部分：

  • took：查询执行的时间（以毫秒为单位）。
  • timed_out：如果查询在规定的时间内没有完成，则此值为 true。
  • _shards：这部分包含了关于查询涉及的分片的信息，包括总分片数、成功查询的分片数、失败的分片数等。
  • hits：这部分包含了查询结果的详细信息，包括匹配的文档总数、最高得分以及匹配的文档列表。每个文档包含_index（文档所在的索引名）、_type（文档的类型）、_id（文档的 ID）、_score（文档的得分）以及_source（文档的源数据）。
    默认情况下，Elasticsearch 的查询只返回前 10 条匹配的文档，这是为了提高查询效率和响应速度。如果你想返回更多的匹配文档，你可以在查询中添加 size 参数来指定返回的文档数量。例如，以下查询将返回前 100 条匹配的文档：
1
2
3
4
5
6
7
8
9
10
GET /itsm_base_enterprisemanagement_index/_search
{
  "size": 100,
  "query": {
    "match": {
      "cName": "1867"
    }
  },
  "_source": ["cGuid"]
}

请注意，size 参数的最大值是 10000。如果你需要处理超过 10000 条的结果，你可能需要使用 scroll API 或者 search_after 参数。

ASK:

将 SQL 形式的查询转换为 kibana 中的 HTTP 形式：SELECT * FROM itsm_base_enterprisemanagement WHERE cname like '%1876%' and ctaxcode like '%767%' and (ctypeid_name = '6' or ctypeid_name = '16');

GitHub Copilot:

在 Kibana 的 DevTools 中，您可以使用 Elasticsearch 的查询 DSL（Domain Specific Language）来执行复杂的查询。以下是将您的 SQL 查询转换为 Elasticsearch 查询的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
GET /itsm_base_enterprisemanagement_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "cname": "*1876*"
          }
        },
        {
          "wildcard": {
            "ctaxcode": "*767*"
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "ctypeid_name": "6"
                }
              },
              {
                "term": {
                  "ctypeid_name": "16"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

这个查询将返回 cname 字段包含”1876”，ctaxcode 字段包含”767”，并且 ctypeid_name 字段为”6”或”16”的所有文档。

请注意，这个查询假设您的索引名为 itsm_base_enterprisemanagement_index。如果不是，请根据实际情况修改查询。

ASK:

“should”和”must”什么区别？还有那些命令？

GitHub Copilot:

在 Elasticsearch 中，bool 查询可以包含 must，should，must_not 和 filter 这四种类型的子查询：

  • must：这些查询必须匹配，以便文档被包含在结果中。这相当于逻辑”AND”。
  • should：这些查询至少有一个必须匹配，以便文档被包含在结果中。这相当于逻辑”OR”。如果没有 must 或 filter 查询，那么至少一个 should 查询必须匹配。
  • must_not：这些查询必须不匹配，以便文档被包含在结果中。这相当于逻辑”NOT”。
  • filter：这些查询必须匹配，但它们以不评分、缓存方式贡献于结果。这对于过滤和缓存常见的过滤条件非常有用。
    以下是一个包含所有这些子查询的 bool 查询的示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
GET /_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "title": "Search" } }
      ],
      "filter": [
        { "term": { "status": "published" } },
        { "range": { "publish_date": { "gte": "2015-01-01" }}}
      ],
      "must_not": [
        { "range": { "likes": { "gte": 1000 }}}
      ],
      "should": [
        { "match": { "content": "Elasticsearch" }}
      ]
    }
  }
}

这个查询将返回标题包含”Search”，状态为”published”，发布日期在”2015-01-01”之后，喜欢的人数少于 1000，以及内容包含”Elasticsearch”的所有文档。

技术文章 > 性能 > GitHub Copilot - Elasticsearch 和 MySQL 单表查询耗时比对
GitHub Copilot - Elasticsearch 和 MySQL 单表查询耗时比对
https://blog.mybatis.io/post/93583122.html
作者
Liuzh
发布于
2023年12月13日
许可协议