用 Elasticsearch 解决 MySQL/MariaDB 的 LIKE %Keyword% 中英文混合查询问题

关键字

Elasticsearch, MySQL, MariaDB, OR Query, LIKE Query, Keyword, analyzer, tokenizer, analyzers, tokenizers, character filters, token filters, token, term, terms, N-Gram, nGram, kaizen, cerebro

场景

例如有电子产品型号和参数等属性表,需要输入完整或不完整型号或参数,可以使用中文或部分英文,查询百万数量级电子器件。

CREATE TABLE `items_search` (
  `id` int(11) NOT NULL,
  `key_value_str` longtext CHARACTER SET utf8 COLLATE utf8_general_ci,
  PRIMARY KEY (`id`),
)

key_value_str 格式如下:

TI(德州仪器)|MSP430F1101AIPWR|MSP430F1101AIPWR 编带|TSSOP-20|ATT00618 MSP430F1101AIPW
发光二极管|HQG(授权代理)|HQ27-2101UYOC72|发光二极管0603白发橙 侧面|0603|LED0016 LED0016 led led灯珠 led灯

在 MySQL/MariaDB 中我们可使用 SQL LIKE %Keyword% 查询,如:

SELECT * FROM items_search WHERE key_value_str LIKE '%Keyword%';
SELECT COUNT(1) FROM items_search WHERE key_value_str LIKE '%Keyword%';
SELECT * FROM items_search WHERE LOCATE('Keyword', `key_value_str`)>0;

实际数值以阿里云主机为例,选择突发性能实例 t5 ecs.t5-lc1m2.smal

  • CPU:1 vCPU
  • 内存:2 G
  • 平均基准 CPU 计算性能:10%
  • 处理器型号:Intel Xeon CPU
  • 处理器主频/睿频:2.5 GHz
  • 内网带宽:0.2 Gbps
  • 内网收发包:6 万 PPS

数据库使用 MariaDB 10.3.17,数量量 80 万情况下,上述 count 查询结果耗时 2.3 秒。

这样查关系数据库的弊端是,当数据量到数百万级时,无论是使用全文检索,还是 LIKE 查询,或分字段 OR 或者 UNION,或者拆表等各种方案,查询耗时均超过 2 秒,用户体验上难以接受。

这时,我们排除 SQL Server/Oracle 等商业数据库,可替换更高性能的数据库 PostgreSQL,DB2 Express 缩短查询时间,SSD 硬盘实测查询耗时可以降低到 1 秒。

替换 MongoDB 勉强解决,查询耗时为 1 秒。

更佳方案是用 Elasticsearch 来实现,查询耗时为 0.05 秒。

概念

Elasticsearch 是基于 Apache Lucene 的支撑海量数据的全文检索引擎标准解决方案,典型的案例,其支撑了 Github 的 400 万用户 800 万仓库 2 亿文档和代码的查询。

analyzer 分析器

分析器,无论是内建还是自定义的,都是包含三个编译块:

  • character filters 字符过滤器
  • tokenizers 分词器
  • token filters 过滤器:

内建分析器预处理器预置包适合分析不同语言和类型的文本,Elasticsearch 也暴露了这些内建块用于自定义分析器。内置有:

  • Standard Analyzer:标准分析器把文本按单词边界拆开并删除标点符号,并支持转小写,去除停止词(the)。
  • Simple Analyzer:简单分析器遇到非字母时,即将文本分开,并转为小写。
  • Whitespace Analyzer:空白分析器,将文本以空白拆开。
  • Stop Analyzer:停止分析器就是简单分析器加上去除停止词。
  • Keyword Analyzer:关键字分析器,不分词,它直接把传入的关键词拆分为一个词。
  • Pattern Analyzer:正则分析器使用正则表达式把原文拆分为,支持转小写去停止词。
  • Language Analyzers:语言分析器,针对特定语言的分析器,比如中文分析器。
  • Fingerprint Analyzer:指纹分析器是一种专家分析器,它可以创建用来探测重复的指纹数据。

character filters字符过滤器

字符过滤器接受原始文本的字符流,可以增、删或改变字符。例如,可以去除 HTML 标记,或者转化(一、二、三、四、五)为(1、2、3、4、5),字符过滤器可以有多个顺序进行处理。

tokenizers:分词器

分词器接受字符流并将其打散(通常为单词)输出。例如,空白分词器将 “Quick brown fox!” 处理为 [Quick, brown, fox!] 三个词。分词器还会记录每个词在原文中出现的顺序和位置。一个分析器必须且只有一个分词器。内置有:

整词分词器

  • Standard Tokenizer:标准分词器,分词文本为单词,去除符号,是最常用的分词器。(中文很特殊,刚好不适合中文)
  • Letter Tokenizer:字母分词器,应该叫非字母分词器,把文本按非字母分词。
  • Lowercase Tokenizer:小写分词器和字母分词器类似,但会把所有分词转为小写。
  • Whitespace Tokenizer:空白分词器按空白分词。
  • UAX URL Email Tokenizer:类似标准分词器,但对 URL 和邮件地址作整体分词。
  • Classic Tokenizer:经典分词器按语法对英语分词。
  • Thai Tokenizer:泰语分词器

部分词分词器

  • N-Gram Tokenizer:步长分词器按步长、标点和空白分词。例如,按步长 2,quick 会分为 [qu, ui, ic, ck]
  • Edge N-Gram Tokenizer:边界步长分词器,按边界逐步分词,也包括标点和空白。例如,quick 被分为 [q, qu, qui, quic, quick]

结构文本分析器

  • Keyword Tokenizer:关键字分词器,按关键字返回关键字分词。可以和其他分词器组合,比如小写分词器。
  • Pattern Tokenizer:正则分词器用正则表达式分词。
  • Simple Pattern Tokenizer:简单正则分词器用正则表达捕获来分词,它使用一个简单的正则规则,所以比正则分词器快。
  • Char Group Tokenizer:字符组分词器用一组字符来分词,所以它通常比正则分词器快。
  • Simple Pattern Split Tokenizer:简单正则拆分分词器和简单正则分词器类似,但它使用正则的拆分而不是捕获。
  • Path Tokenizer:路径分词器,用于拆分文件路径。例如,/foo/bar/baz 分词为 [/foo, /foo/bar, /foo/bar/baz ]。

token filters:token 过滤器

token 可以翻译为令牌,但此处更适合不翻译。要理解 token,首先要理解 Term,Term 就是待查文本的一个词,表示搜索的最小单元,这些是 Lucene 的原始概念。token 是记录了 Term 在字段中开始位置和结束位置、类型的对象。按上面的例子,token 就是解析器分好的词 Quick, brown,有时也不一定是一个词,比如 fox! 。

token 过滤器接受 token 流,也可能增、删或改变其中的 token。例如,小写过滤器将 token 转为小写,停止过滤器将停止词(the)删除。

token 过滤器不允许改变每个 token 的位置偏移。

一个解析器可以没有 token 过滤器,也可以有多个按顺序的 token 过滤器。

解决

LIKE ‘%Keyword%’ 的实现

与通常的使用中文分词查询方式不同,这个场景我们需要使用 nGram 来自定义分词步长。

自定义分析器和解析器

{
  "settings": {
    "index": {
      "analysis": {
        "analyzer": {
          "ngram_tokenizer_analyzer": {
            "filter": [
              "lowercase"
            ],
            "type": "custom",
            "tokenizer": "ngram_tokenizer"
          }
        },
        "tokenizer": {
          "ngram_tokenizer": {
            "token_chars": [
              "letter",
              "digit"
            ],
            "min_gram": "2",
            "type": "nGram",
            "max_gram": "2"
          }
        }
      },
      "number_of_shards": "1",
      "number_of_replicas": "1"
    }
  },
  "mappings": {
    "_doc": {
      "properties": {
        "key_value_str": {
          "analyzer": "ngram_tokenizer_analyzer",
          "type": "text"
        }
      }
    }
  }
}

导入数据进行索引

将待查字段导入 Elasticsearch 进行索引,可以用客户端工具 kaizen、cerebro 等,也可用 Python、PHP 等自编脚本控制,提供一段 Python 脚本如下:

# MySQL to Elasticsearch
from elasticsearch import Elasticsearch
from elasticsearch import helpers
import MySQLdb
import time
db = MySQLdb.connect("127.0.0.1", "mysql", "password", "db", port=3306, charset='utf8')
cursor = db.cursor()
cursor.execute("SELECT id, key_value_str FROM items_search LIMIT 0, 10000000")
data = cursor.fetchall()
actions = []
for row in data:
    action = {
        "_id": row[0],
        "_index": "product",
        "_type": "_doc",
        "_source": {
            "key_value_str": row[1]
        }
    }
    actions.append(action)
es = Elasticsearch(['127.0.0.1:9200'])
s = time.time()
helpers.bulk(es, actions, chunk_size=2000)
print("Execution Time: {} sec".format(time.time() - s))
cursor.close()
db.close()

上述 80 万数据查询耗时,可以降低到 0.03 秒。与数据库方案相差 2 个量级,这就是 Elasticsearch 方案带来的差异。

总结

1、Elasticsearch 最新版中文文档版本为 2.x,最新的文档请查阅英文,与旧版本查询接口的 json 格式差异很大。

2、单节点的性能和成本是指数相关的,购买一台双倍性能节点是两台单倍性能节点成本的两倍以上,且性能越高,差异越大。所以分布式横向扩展的价值在于用更低成本的多节点解决更重的业务问题。

相关文章

在 CentOS 7 下安装 Elasticsearch 7.3

发表评论

电子邮件地址不会被公开。 必填项已用*标注