文档首页/ 云搜索服务 CSS/ 最佳实践/ 使用Elasticsearch加速关系型数据库的查询分析
更新时间：2024-11-18 GMT+08:00
查看PDF
分享

使用Elasticsearch加速关系型数据库的查询分析

介绍如何将MySQL数据库中的数据同步到云搜索服务的Elasticsearch集群，通过Elasticsearch实现数据库的全文检索、Ad Hoc查询和统计分析能力。

应用场景

使用Elasticsearch加速关系型数据库，可以解决关系型数据库在某些方面的局限性，实现更加高效和智能的数据处理和分析。常用于以下应用场景：
  • 电子商务平台：快速搜索商品，提供个性化推荐，实时监控用户行为和交易数据。
  • 内容管理系统：对大量文档和内容进行高效检索，支持复杂的查询和数据分析。
  • 金融服务：实时监控交易数据，进行风险分析和欺诈检测。
  • 社交媒体分析：对用户生成的内容进行情感分析，趋势和影响力评估。
  • 客户关系管理：快速检索客户信息，分析客户行为，提供定制化服务。
  • 日志和事件监控：收集和分析大量日志数据，实时监控系统状态和安全事件。
  • 医疗健康记录：快速检索和分析患者记录，支持临床决策和研究。

方案架构

图1 Elasticsearch加速关系型数据库的方案架构
  1. 用户业务数据存储到MySQL。
  2. 通过数据复制服务DRS将MySQL中的数据实时同步到CSS服务的Elasticsearch集群。
  3. 在Elasticsearch集群中进行全文检索、Ad Hoc查询和统计分析。

方案优势

以下是使用Elasticsearch加速关系型数据库的方案优势：
  • 全文检索能力提升：Elasticsearch是一个提供了强大的全文检索功能的搜索引擎。关系型数据库通常不擅长进行全文检索，而Elasticsearch可以有效地解决这一问题。
  • 高并发Ad Hoc查询：Elasticsearch设计用于处理大量的并发查询请求，特别是在Ad Hoc查询场景下，它能够提供快速的响应时间，从而满足高并发环境下的查询需求。
  • 实时数据同步：通过华为云数据复制服务DRS，可以实现MySQL数据库中的数据实时同步到Elasticsearch，确保数据的一致性和实时性。
  • 简化的数据迁移和索引创建：在Elasticsearch中，可以创建与MySQL数据库表结构相对应的索引，简化了数据迁移和索引管理的复杂性。
  • 灵活的查询语言：Elasticsearch提供了灵活的查询语言，支持复杂查询的构建，如范围查询、模糊查询、聚合查询等，这在关系型数据库中可能需要更复杂的SQL语句来实现。
  • 统计分析能力：Elasticsearch的聚合功能可以快速进行数据统计和分析，如年龄分布统计等，这在关系型数据库中可能需要更多的计算资源和时间。
  • 安全性和稳定性：通过配置安全模式的Elasticsearch集群和MySQL数据库，以及使用SSL安全连接，可以保证数据传输的安全性和系统的稳定性。
  • 易于监控和维护：Elasticsearch提供了丰富的监控工具和API，使得系统维护和性能监控变得更加容易。
  • 扩展性：Elasticsearch集群可以根据业务需求进行水平扩展，增加更多的节点来处理更大的数据量和查询负载。

这些优势使得Elasticsearch集群成为关系型数据库在处理全文检索和高并发Ad Hoc查询时的有效补充。

前提条件

  • 已具备安全模式的Elasticsearch集群和MySQL数据库，且两者在同一个VPC与安全组内。
  • MySQL数据库中已经有待同步的数据。

    本文以如下表结构和初始数据举例。

    1. MySQL中创建一个学生信息表： 
      CREATE TABLE `student` (
  `dsc` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` smallint unsigned DEFAULT NULL,
  `name` varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    2. MySQL中插入3个学生的初始数据： 
      INSERT INTO student (id,name,age,dsc)
VALUES 
('1','Jack Ma Yun','50','Jack Ma Yun is a business magnate, investor and philanthropist.'),
('2','will smith','22','also known by his stage name the Fresh Prince, is an actor, rapper, and producer.'),
('3','James Francis Cameron','68','the director of avatar');
  • Elasticsearch集群中已完成索引创建，且与MySQL中表相对应。
    本文执行如下命令创建Elasticsearch集群的索引。 
    PUT student
{
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 3
	},
  "mappings": {
    "properties": {
      "id": {
        "type": "keyword"
		},
      "name": {
        "type": "short"
		},
      "age": {
        "type": "short"
		},
      "desc": {
        "type": "text"
		}
    }
  }
}

    其中的“number_of_shards”与“number_of_replicas”需根据具体业务场景进行配置。

操作步骤

  1. 通过DRS将MySQL数据实时同步到CSS。具体操作步骤请参见将MySQL同步到CSS/ES。

    在本章案例中，表1中的同步任务配置参数需要按建议填写。
    表1 同步任务参数说明

    配置模块

    参数名称

    填写建议

    同步实例 > 同步实例信息

    网络类型

    选择“VPC网络”。

    源数据库实例

    选择需要同步的RDS for MySQL实例，即存储用户业务数据的MySQL。

    同步实例所在子网

    选择同步实例所在的子网，建议跟数据库实例以及Elasticsearch集群所在子网保持一致。

    源库及目标库 > 目标库信息

    VPC

    选择和Elasticsearch集群一致的VPC。

    子网

    选择和Elasticsearch集群一致的子网。

    IP地址或域名

    填写Elasticsearch集群的IP地址，获取方式请参见获取CSS集群的IP地址。

    数据库用户名

    填写Elasticsearch集群的管理员账户名（admin）。

    数据库密码

    填写Elasticsearch集群的管理员密码。

    加密证书

    选择Elasticsearch集群的安全证书，如果未启用“SSL安全连接”，则不用选择。获取方式请参见获取CSS集群的安全证书。

    设置同步

    流速模式

    选择“不限速”。

    同步对象类型

    不勾选“同步表结构”，因为已经预先在Elasticsearch集群中创建了与MySQL中表相对应的索引。

    同步对象

    选择“表级同步”，选择与Elasticsearch对应的数据库以及表名。

    说明：

    配置项中type名称需要与索引名称一样，都是“_doc”，如果不一致请修改。

    数据加工

    -

    直接“下一步”。

    启动同步任务后，等待任务“状态”从“全量同步”变成“增量同步”，表示数据进入实时同步状态。

  2. 验证数据库的同步状态。

    1. 全量数据同步验证。

      在Elasticsearch集群的Kibana中执行如下命令，确认全量数据是否同步到CSS。

      GET student/_search
    2. 源端插入新数据，验证数据是否会同步到Elasticsearch。
      例如，源端插入“id”为“4”的新数据。 
      INSERT INTO student (id,name,age,dsc)
VALUES 
('4','Bill Gates','50','Gates III is a business magnate, software developer, investor, author, and philanthropist.')

      在Elasticsearch集群的Kibana中执行如下命令，确认新数据是否同步到CSS。

      GET student/_search
    3. 源端更新数据，验证数据是否会同步更新到Elasticsearch。
      例如，更新“id”为“4”这条数据的“age”字段，从“50”改成“55”。 
      UPDATE student set age='55' WHERE id=4;

      在Elasticsearch集群的Kibana中执行如下命令，确认数据是否同步更新到CSS。

      GET student/_search
    4. 源端删除数据，验证Elasticsearch的数据是否同步删除。
      例如，删除“id”为“4”的数据。 
      DELETE FROM student WHERE id=4;

      在Elasticsearch集群的Kibana中执行如下命令，确认CSS里的数据是否被同步删除。

      GET student/_search

  3. 验证数据库的全文检索能力。

    例如，在Elasticsearch集群查询“dsc”中包含“avatar”的数据。

    GET student/_search
{
  "query": {
    "match": {
      "dsc": "avatar"
    }
  }
}

  4. 验证数据库的Ad Hoc查询能力。

    例如，在Elasticsearch集群查询年龄大于40的philanthropist。

    GET student/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "dsc": "philanthropist"
          }
        },
        {
          "range": {
            "age": {
              "gte": 40
            }
          }
        }
      ]
    }
  }
}

  5. 验证数据库的统计分析能力。

    例如，在Elasticsearch集群统计所有人的年龄分布。

    GET student/_search
{
  "size": 0, 
  "query": {
    "match_all": {}
  },
  "aggs": {
    "age_count": {
      "terms": {
        "field": "age",
        "size": 10
      }
    }
  }
}

其他操作

  • 获取CSS集群的IP地址
    1. 在云搜索服务管理控制台，单击左侧导航栏的“集群管理”。
    2. 在集群管理列表页面，选择需要访问的集群，在“内网访问地址”列获取CSS集群的IP地址，一般是“<host>:<port>”或“<host>:<port>,<host>:<port>”样式。

      如果集群只有一个节点，此处仅显示1个节点的IP地址和端口号，例如“10.62.179.32:9200”；如果集群有多个节点，此处显示所有节点的IP地址和端口号，例如“10.62.179.32:9200,10.62.179.33:9200”。

  • 获取CSS集群的安全证书
    1. 登录云搜索服务控制台。
    2. 选择“集群管理”进入集群列表。
    3. 单击对应集群的名称，进入集群基本信息页面。
    4. 在“基本信息”页面，单击“HTTPS访问”后面的“下载证书”。
      图2 下载证书

相关文档