通过搜索索引查询宽表数据

本文介绍如何在多种查询场景下通过搜索索引查询Lindorm宽表数据,包括多维查询、排序翻页、分词查询、模糊查询和聚合分析。

前提条件

多维查询

执行以下语句实现多维查询。

  • 示例一

    SELECT * FROM search_table WHERE gender='M' AND city='杭州' OR city='北京';

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id | name   | age | gender | address      | email            | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 6       | 李先生 | 32  | M      | 杭州市余杭区 | a***@example.net | 杭州 |
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    | 20      | 王先生 | 28  | M      | 杭州市滨江区 | a***@example.net | 杭州 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 示例二

    SELECT * FROM search_table WHERE age > 30 AND city != '杭州';

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 28      | 陈女士 | 36  | F      | 深圳市南山区 | a***@example.net | 深圳 |
    +---------+--------+-----+--------+--------------+------------------+------+

排序翻页

  • 搜索索引支持任意列的排序,执行以下语句实现数据排序查询。

    SELECT * FROM search_table  WHERE gender='M' ORDER BY age DESC;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 6       | 李先生 | 32  | M      | 杭州市余杭区 | a***@example.net | 杭州 |
    | 20      | 王先生 | 28  | M      | 杭州市滨江区 | a***@example.net | 杭州 |
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 搜索索引支持通过limit或者offset方式进行数据翻页,执行以下语句实现数据翻页查询。

    SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC LIMIT 1,10;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 20      | 王先生 | 28  | M      | 杭州市滨江区 | a***@example.net | 杭州 |
    | 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
    +---------+--------+-----+--------+--------------+------------------+------+

模糊查询

执行以下语句查询email列中包含example的数据。

SELECT * FROM search_table WHERE email LIKE '%example%' ORDER BY user_id;

查询结果如下:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 1       | 张先生 | 18  | M      | 北京市朝阳区 | a***@example.net | 北京 |
| 6       | 李先生 | 32  | M      | 杭州市余杭区 | a***@example.net | 杭州 |
| 20      | 王先生 | 28  | M      | 杭州市滨江区 | a***@example.net | 杭州 |
| 28      | 陈女士 | 36  | F      | 深圳市南山区 | a***@example.net | 深圳 |
+---------+--------+-----+--------+--------------+------------------+------+
说明

模糊查询的性能受限于表的数据量,推荐使用分词查询

聚合分析

搜索索引支持常用的聚合函数,例如COUNT、SUM、AVG、MIN、MAX。同时,搜索索引也支持DISTINCTGROUP BY功能。

  • 执行以下语句,查询city杭州的数据的总数。

    SELECT COUNT(*) FROM search_table WHERE city='杭州';

    查询结果如下:

    +----------+
    | EXPR$0   |
    +----------+
    | 2        |
    +----------+
  • 执行以下语句,实现数据统计去重查询。

    SELECT distinct(age) FROM search_table WHERE gender='M';

    查询结果如下:

    +---------------+
    | DISTINCT(age) |
    +---------------+
    | 18            |
    | 28            |
    | 32            |
    +---------------+
  • 执行以下语句,实现分组查询。

    SELECT city,count(*) AS cnt FROM search_table WHERE gender='M' GROUP BY city ORDER BY cnt DESC ;

    查询结果如下:

    +------+-----+
    | city | cnt |
    +------+-----+
    | 杭州 | 2   |
    | 北京 | 1   |
    +------+-----+
说明

聚合查询时,如果未指定WHERE条件,将无法命中搜索索引。此时可以在WHERE关键字前添加HINT参数:_l_force_index_,强制使用搜索索引。_l_force_index_参数的详细介绍及使用示例,请参见hintOption参数说明示例四:使用_l_force_index_强制选择索引

短语匹配查询

执行以下语句,实现短语匹配查询。

SELECT name,age,city,address FROM search_table WHERE age>1 AND age<100 AND address='"杭州"' ORDER BY user_id;

查询结果如下:

+--------+-----+------+--------------+
|  name  | age | city |   address    |
+--------+-----+------+--------------+
| 李先生 | 32  | 杭州 | 杭州市余杭区 |
| 王先生 | 28  | 杭州 | 杭州市滨江区 |
+--------+-----+------+--------------+

分词查询

重要

分词查询依赖MATCH函数实现,仅宽表引擎2.7.2及以上版本支持MATCH函数。如何查看或升级当前版本,请参见宽表引擎版本说明升级小版本

对于分词列,需要通过MATCH函数实现分词查询。以下示例查询age的范围在(10,30]并且address匹配杭州的数据,其中address列是分词列。

SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND MATCH (address) AGAINST ('杭州') ORDER BY user_id;

查询结果如下:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | 王先生 | 28  | M      | 杭州市滨江区  | a***@example.net | 杭州 |
+---------+--------+-----+--------+--------------+------------------+------+

使用分词子字段功能查询

重要

分词子字段功能需要宽表引擎为2.8.0及以上版本,且搜索引擎为Elasticsearch兼容版本。如果您的版本不满足以上条件,请先升级小版本

背景介绍

在创建搜索索引时,如果宽表的VARCHAR字段设置为分词类型(即type=text),系统会在等值查询、模糊查询等场景中使用分词模式进行匹配,这可能会产生非预期的检索结果。

例如,将address设置为分词列:address(type=text,analyzer=ik),则系统在创建索引时会按照分词器策略将词汇拆分为多个独立词项,以杭州市滨江区为例,创建索引时会被拆分为杭州市滨江区。因此当使用LIKE进行模糊查询时,可能因为目标词汇被拆分而无法匹配到。

如下示例,由于滨江被拆分至两个词汇中导致无法命中,因此返回结果为空。

SELECT * FROM search_table WHERE city LIKE '%市滨江%' LIMIT 10;

启用方法

若需要让VARCHAR字段同时支持分词查询、模糊查询和等值查询场景,可启用分词子字段功能。

开启该功能,需要在创建索引时就设置对应字段的属性为type=string,textSubField=true,analyzer=ikanalyzer分词器类型可根据实际使用场景选择。示例如下:

CREATE INDEX idx USING SEARCH ON search_table(user_id,address(type=string, textSubField=true, analyzer=ik));
说明

每张宽表仅支持创建一个搜索索引。若已存在搜索索引且需要启用分词子字段功能,请先删除原有搜索索引,然后重新创建新索引启用分词子字段功能。

查询方式

在查询时,等值查询和模糊查询默认使用原始的完整字符串匹配,分词查询通过MATCH函数匹配过滤条件。以下是三种常见查询方式的示例及其查询结果。

  • 等值查询

    示例1:

    SELECT * FROM search_table WHERE address='杭州' LIMIT 10;

    返回结果为空,因为address不存在完全匹配杭州整个字符串的记录。

    示例2:

    SELECT * FROM search_table WHERE address='杭州市滨江区' LIMIT 10;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 20      | 王先生  | 28  | M      | 杭州市滨江区  | a***@example.net | 杭州 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 模糊查询

    示例:

    SELECT * FROM search_table WHERE address like '%市滨江%' LIMIT 10;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 20      | 王先生 | 28   | M      | 杭州市滨江区  | a***@example.net | 杭州 |
    +---------+--------+-----+--------+--------------+------------------+------+
  • 分词查询

    示例:

    SELECT * FROM search_table WHERE MATCH (address) AGAINST ('杭州') LIMIT 10;

    查询结果如下:

    +---------+--------+-----+--------+--------------+------------------+------+
    | user_id |  name  | age | gender |   address    |      email       | city |
    +---------+--------+-----+--------+--------------+------------------+------+
    | 6       | 李先生 | 32   | M      | 杭州市余杭区  | a***@example.net | 杭州 |
    | 20      | 王先生 | 28   | M      | 杭州市滨江区  | a***@example.net | 杭州 |
    +---------+--------+-----+--------+--------------+------------------+------+