文档首页> 云搜索服务 CSS> 最佳实践> 实践案例> 使用CSS加速数据库的查询分析
更新时间:2024-04-19 GMT+08:00

使用CSS加速数据库的查询分析

方案架构

关系型数据库(例如MySQL、GaussDB for MySQL等)受限于全文检索和Ad Hoc查询能力,因此会将Elasticsearch作为关系型数据库的补充,以此提升数据库的全文检索能力和高并发的Ad Hoc查询能力。

本章主要介绍,如何将MySQL数据库中的数据同步到云搜索服务CSS,通过CSS实现数据库的全文检索与Ad Hoc查询分析加速。方案架构图如图1所示。

图1 CSS加速数据库的查询分析方案
  1. 用户业务数据存储到MySQL。
  2. 通过数据复制服务DRS将MySQL中的数据实时同步到CSS。
  3. 通过CSS进行全文检索与数据查询分析。

前提条件

  • 已创建好安全模式的CSS集群和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 Chinese business magnate, investor and philanthropist.'),
      ('2','will smith','22','also known by his stage name the Fresh Prince, is an American actor, rapper, and producer.'),
      ('3','James Francis Cameron','68','the director of avatar');
  • CSS集群中已完成索引创建,与MySQL中表相对应。
    本章集群的索引示例如下:
    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。

    “同步实例所在子网”

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

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

    “VPC”“子网”

    选择和CSS集群一致的VPC与子网。

    “IP地址或域名”

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

    “数据库用户名”“数据库密码”

    填写CSS集群的管理员账户名(admin)和密码。

    “加密证书”

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

    设置同步

    “流速模式”

    选择“不限速”。

    “同步对象类型”

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

    “同步对象”

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

    说明:

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

    数据加工

    -

    直接“下一步”。

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

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

    1. 全量数据同步验证。

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

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

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

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

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

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

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

      GET student/_search

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

    例如,在CSS查询“dsc”中包含“avatar”的数据。

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

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

    例如,在CSS查询年龄大于40的philanthropist。

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

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

    例如,在CSS统计所有人的年龄分布。

    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 下载证书