検索インデックスを使用したワイドテーブルでのデータクエリ - Lindorm

このトピックでは、検索インデックスを使用して、多次元クエリ、ソートとページネーション、トークン化クエリ、あいまい検索、集約と分析など、さまざまなシナリオで Lindorm ワイドテーブルのデータをクエリする方法について説明します。

前提条件

Lindorm-cli を使用してワイドテーブルエンジンに接続します。詳細については、「Lindorm-cli を使用したワイドテーブルエンジンへの接続と使用」をご参照ください。
テストデータを準備し、検索インデックスを作成済みであること。詳細については、「検索インデックスの管理」をご参照ください。

多次元クエリ

次の文を実行して、多次元クエリを実行します。

例 1

SELECT * FROM search_table WHERE gender='M' AND city='Hangzhou' OR city='Beijing';

次の結果が返されます。

+---------+----------+-----+--------+--------------------+------------------+----------+
| user_id | name     | age | gender | address            | email            | city     |
+---------+----------+-----+--------+--------------------+------------------+----------+
| 6       | Mr. Li   | 32  | M      | Yuhang, Hangzhou   | a***@example.net | Hangzhou |
| 1       | Mr. Zhang| 18  | M      | Chaoyang, Beijing  | a***@example.net | Beijing  |
| 20      | Mr. Wang | 28  | M      | Binjiang, Hangzhou | a***@example.net | Hangzhou |
+---------+----------+-----+--------+--------------------+------------------+----------+

例 2

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

次の結果が返されます。

+---------+--------+-----+--------+----------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                  |      email       | city     |
+---------+--------+-----+--------+----------------------------+------------------+----------+
| 28      | Chen   | 36  | F      | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+--------+-----+--------+----------------------------+------------------+----------+

データのソートとページネーション

検索インデックスは、任意の列によるソートをサポートしています。次の文を実行して、クエリ結果をソートします。

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

次の結果が返されます。

+---------+--------+-----+--------+---------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                 |      email       | city     |
+---------+--------+-----+--------+---------------------------+------------------+----------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou| a***@example.net | Hangzhou |
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing| a***@example.net | Beijing  |
+---------+--------+-----+--------+---------------------------+------------------+----------+

検索インデックスは、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      | Wang   | 28  | M      | Binjiang District, Hangzhou| a***@example.net | Hangzhou |
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing| a***@example.net | Beijing  |
+---------+--------+-----+--------+---------------------------+------------------+----------+

あいまい検索

次の文を実行して、email 列に "example" が含まれるデータをクエリします。

SELECT * FROM search_table WHERE email LIKE '%example%';

次の結果が返されます。

+---------+--------+-----+--------+----------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                  |      email       | city     |
+---------+--------+-----+--------+----------------------------+------------------+----------+
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing  |
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou  | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou| a***@example.net | Hangzhou |
| 28      | Chen   | 36  | F      | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+--------+-----+--------+----------------------------+------------------+----------+

説明

あいまい検索のパフォーマンスは、テーブルのデータ量の影響を受けます。代わりにトークン化クエリを使用することを推奨します。

集約と分析

検索インデックスは、COUNT、SUM、AVG、MIN、MAX などの一般的な集計関数をサポートしています。検索インデックスは、DISTINCT および GROUP BY 機能もサポートしています。

次の文を実行して、city が Hangzhou であるレコードの総数をクエリします。
```
SELECT COUNT(*) FROM search_table WHERE city='Hangzhou';
```
次の結果が返されます。
```
+----------+
| 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 |
+----------+-----+
| Hangzhou | 2   |
| Beijing  | 1   |
+----------+-----+

説明

集計クエリを WHERE 句なしで実行すると、検索インデックスがヒットしない場合があります。クエリに検索インデックスを強制的に使用させるには、WHERE キーワードの前に _l_force_index_ ヒントを追加します。 _l_force_index_ パラメーターとその使用方法の詳細については、「hintOption パラメーター」および「例 4: _l_force_index_ を使用したインデックス選択の強制」をご参照ください。

トークン化クエリ

トークン化された列の場合、MATCH 関数を使用してトークン化クエリを実行できます。

重要

MATCH 関数は LindormTable 2.7.2 以降でサポートされています。現在のバージョンを表示またはアップグレードするには、「LindormTable バージョンガイド」および「マイナーバージョンのアップグレード」をご参照ください。

次の例は、age が (10,30] の範囲にあり、address が Hangzhou に一致するデータのクエリを示しています。 address 列はトークン化された列です。

SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND MATCH (address) AGAINST ('Hangzhou');

次の結果が返されます。

+---------+--------+-----+--------+-----------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                   |      email       | city     |
+---------+--------+-----+--------+-----------------------------+------------------+----------+
| 20      | 田中   | 28  | M      | 杭州市濱江区                | a***@example.net | Hangzhou |
+---------+--------+-----+--------+-----------------------------+------------------+----------+

次の例は、age が (30,35] の範囲にあり、address が完全なフレーズ Yuhang District, Hangzhou City に一致するデータのクエリを示しています。 address 列はトークン化された列です。

SELECT * FROM search_table WHERE age > 30 AND age <= 35 AND MATCH (address) AGAINST ('"Yuhang District, Hangzhou City"');

次の結果が返されます。

+---------+--------+-----+--------+---------------------------+------------------+----------+
| user_id | name   | age | gender | address                   | email            | city     |
+---------+--------+-----+--------+---------------------------+------------------+----------+
| 6       | 佐藤   | 32  | M      | 杭州市余杭区              | a***@example.net | Hangzhou |
+---------+--------+-----+--------+---------------------------+------------------+----------+

LindormTable 2.7.2 より前のバージョンでは、等号 (=) 構文を使用してトークン化クエリを実行できます。
重要
- 等号構文を使用するトークン化クエリは推奨されなくなりました。できるだけ早く最新バージョンにアップグレードし、代わりに MATCH 関数を使用してください。現在のバージョンを表示またはアップグレードするには、「LindormTable バージョンガイド」および「マイナーバージョンのアップグレード」をご参照ください。
- LindormTable 2.8.2.3 以降、新しい検索インデックスでは、等号構文を使用するトークン化クエリはサポートされなくなりました。既存の検索インデックスでは、引き続きこの構文を使用できます。互換性やその他の理由で新しい検索インデックスでこのクエリ構文を使用する必要がある場合は、Lindorm のテクニカルサポート (DingTalk ID: s0s3eg3) にご連絡ください。
次の例では、age が (10,30] の範囲にあり、address が Hangzhou に一致するデータをクエリします。 address 列はトークン化された列です。
```
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND address = 'Hangzhou';
```
次の結果が返されます。
```
+---------+----------+-----+--------+-----------------------------+------------------+----------+
| user_id |  name    | age | gender |   address                   |      email       | city     |
+---------+----------+-----+--------+-----------------------------+------------------+----------+
| 20      | 田中     | 28  | M      | 杭州市濱江区                | a***@example.net | Hangzhou |
+---------+----------+-----+--------+-----------------------------+------------------+----------+
```

トークン化サブフィールド機能を使用したクエリ

重要

トークン化サブフィールド機能には、LindormTable 2.8.0 以降と、Elasticsearch と互換性のある検索エンジンが必要です。ご利用のバージョンがこれらの要件を満たしていない場合は、まずマイナーバージョンをアップグレードしてください。

背景情報

検索インデックスを作成する際に、ワイドテーブルの VARCHAR フィールドがトークン化タイプ (つまり、type=text) に設定されている場合、システムは完全一致クエリやあいまい検索などのシナリオで照合にトークン化パターンを使用します。これにより、予期しないクエリ結果が返されることがあります。

たとえば、address(type=text,analyzer=ik) を使用して address 列をトークン化列として設定した場合、システムはインデックス作成時にトークナイザーポリシーに基づいて文字列を個別のトークンに分割します。たとえば、Binjiang District, Hangzhou City は Hangzhou City と Binjiang District に分割されます。その結果、LIKE を使用するあいまい検索では、ターゲットの単語が分割されているため、一致が見つからない場合があります。

次の例では、City と Binjiang が 2 つの異なるトークンに分割されてヒットしないため、結果は返されません。

SELECT * FROM search_table WHERE city LIKE '%Binjiang%' 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 関数を使用してフィルター条件を照合します。次のセクションでは、これら 3 つの一般的なクエリメソッドとその結果の例を示します。

完全一致クエリ

例 1：

SELECT * FROM search_table WHERE address='Hangzhou' LIMIT 10;

address 列に文字列 Hangzhou 全体に完全に一致するレコードがないため、クエリは空の結果を返します。

例 2：

SELECT * FROM search_table WHERE address='Binjiang District, Hangzhou' LIMIT 10;

次の出力が返されます。

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

あいまい検索

例：

SELECT * FROM search_table WHERE address like '%Binjiang%' LIMIT 10;

次の結果が返されます。

+---------+--------+-----+--------+-----------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                   |      email       | city     |
+---------+--------+-----+--------+-----------------------------+------------------+----------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+-----------------------------+------------------+----------+

トークン化クエリ

例：

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

次の結果が返されます。

+---------+--------+-----+--------+---------------------------+------------------+----------+
| user_id |  name  | age | gender |   address                 |      email       | city     |
+---------+--------+-----+--------+---------------------------+------------------+----------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou| a***@example.net | Hangzhou |
+---------+--------+-----+--------+---------------------------+------------------+----------+