由 MySQL 至 Elasticsearch 的字段映射

hello大家好，我是千羽。嘿哟，从那神奇的 MySQL 跨越到超厉害的 Elasticsearch 的字段映射来啦！！！最近业务上经常遇到MySQL 和 Elasticsearch 和查询，像列表搜索涉及四五张表的，基本上都重构为了es查询。同时在处理es同步数据的时候，它们之间的结构对比以及如何进行查询优化深有体会。

MySQL 与 Elasticsearch 结构对比

MySQL 是一种关系型数据库管理系统 (RDBMS)，使用结构化查询语言 (SQL) 进行数据管理和操作。

Elasticsearch 是一种基于文档的搜索和分析引擎，使用一种称为 Elasticsearch Query DSL 的查询语言。了解两者之间的结构对比有助于顺利进行数据迁移和优化。

数据库与索引

在 MySQL 中，数据库是数据的逻辑集合。每个数据库包含多个表，每个表又包含多行数据。在 Elasticsearch 中，索引 (index) 是数据的逻辑集合，类似于 MySQL 中的数据库。每个索引包含多个文档，每个文档是一个 JSON 对象。

表与类型

在 MySQL 中，表是数据库中的一个实体，包含了数据的结构定义。在 Elasticsearch 中，类型 (type) 曾经用来定义文档的结构，但从 Elasticsearch 7.0 开始，类型已被弃用。现在，每个索引只能包含一种类型的文档。

行与文档

在 MySQL 中，行 (row) 是表中的一条记录。在 Elasticsearch 中，文档 (document) 是索引中的一条记录。文档以 JSON 格式存储，结构可以灵活变化。

列与字段

在 MySQL 中，列 (column) 是表中的一个字段，每列有固定的数据类型。在 Elasticsearch 中，字段 (field) 是文档中的一个键值对，字段类型可以在映射 (mapping) 中定义。

常见数据类型对比

在 Elasticsearch 中，Mapping 用来定义索引中文档和字段的结构、类型、及其配置。Mapping 可以看作是 Elasticsearch 中的数据模式，类似于 MySQL 中的表结构。

下面是 Elasticsearch 的 Mapping 结构与 MySQL 字段对比的详细说明。

MySQL	Elasticsearch	说明
Database	Index	数据库
Table	Type (deprecated)	表
Column	Field	列
Row	Document	行
Data Type	Data Type	数据类型

以下是 MySQL 和 Elasticsearch 中常见数据类型的对比：

MySQL	Elasticsearch	说明
INT	integer	整数类型
BIGINT	long	大整数类型
FLOAT	float	浮点数类型
DOUBLE	double	双精度浮点数
DECIMAL	scaled_float	高精度小数
VARCHAR	text	文本字段，适用于全文搜索
CHAR	keyword	关键词字段，适用于精确匹配
TEXT	text	大文本字段
DATE	date	日期字段
DATETIME	date	日期时间字段
TIMESTAMP	date	时间戳字段
BOOLEAN	boolean	布尔字段
ENUM	keyword	枚举字段，适用于有限集合的字符串
JSON	object/nested	JSON 对象或嵌套类型字段

示例：从 MySQL 表结构到 Elasticsearch 映射

MySQL 表结构

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Elasticsearch 映射

PUT /users
{
  "mappings": {
    "properties": {
      "id": {
        "type": "integer"
      },
      "name": {
        "type": "text"
      },
      "age": {
        "type": "integer"
      },
      "email": {
        "type": "keyword"
      },
      "created_at": {
        "type": "date",
        "format": "yyyy-MM-dd'T'HH:mm:ssZ||epoch_millis"
      }
    }
  }
}

SQL 语句与 Elasticsearch 查询对比

在 MySQL 中，我们使用 SQL 语句来查询数据。在 Elasticsearch 中，我们使用 Elasticsearch Query DSL 来查询数据。下面我们将通过具体示例来对比 SQL 语句和 Elasticsearch 查询。

查询所有用户

MySQL

SELECT * FROM users;

Elasticsearch

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

查询特定用户

MySQL

SELECT * FROM users WHERE id = 1;

Elasticsearch

GET /users/_search
{
  "query": {
    "term": {
      "id": 1
    }
  }
}

查询优化

查询优化是确保数据库高效运行的关键。下面我们将介绍 MySQL 和 Elasticsearch 的查询优化技巧。

MySQL 查询优化

使用适当的索引：在频繁查询的列上创建索引可以显著提高查询速度。主键、唯一键和普通索引都可以根据需要使用。
避免使用 SELECT * ：只选择需要的列，避免不必要的数据传输。
使用 JOIN 而非子查询：在合适的场景下，使用 JOIN 代替子查询可以提高查询效率。
优化 WHERE 子句：在 WHERE 子句中使用索引列进行过滤，可以加速查询。

Elasticsearch 查询优化

使用合适的字段类型：在映射中定义合适的字段类型，以确保查询效率。
利用索引和过滤：使用索引和过滤条件，可以有效减少查询范围，提高查询速度。
避免全文搜索的左模糊匹配：全文搜索时避免使用左模糊匹配，以充分利用倒排索引的优势。
使用聚合而非脚本：在可能的情况下，使用聚合功能代替脚本计算，以提高查询性能。

示例：MySQL 和 Elasticsearch 查询优化

MySQL 查询优化示例

假设我们有一个包含数百万条记录的 orders 表，我们需要查询特定日期范围内的订单总数。

优化前

SELECT COUNT(*) FROM orders WHERE order_date >= '2023-01-01' AND order_date <= '2023-01-31';

优化后

-- 添加索引
CREATE INDEX idx_order_date ON orders(order_date);

-- 优化查询
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

通过为 order_date 列添加索引，可以显著提高查询性能。

Elasticsearch 查询优化示例

假设我们有一个包含数百万条文档的 logs 索引，我们需要查询特定日期范围内的日志记录。