本文介绍如何在多种查询场景下通过搜索索引查询Lindorm宽表数据,包括多维查询、排序翻页、分词查询、模糊查询和聚合分析。
前提条件
已通过Lindorm-cli连接宽表引擎。具体操作,请参见通过Lindorm-cli连接并使用宽表引擎。
已准备测试数据并创建搜索索引。具体内容,请参见管理搜索索引。
多维查询
执行以下语句实现多维查询。
示例一
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。同时,搜索索引也支持DISTINCT和GROUP 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函数实现分词查询。以下示例查询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=ik
,analyzer
分词器类型可根据实际使用场景选择。示例如下:
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 | 杭州 | +---------+--------+-----+--------+--------------+------------------+------+