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

このトピックでは、検索インデックスを使用して、多次元クエリ、ページングクエリ、結果がソートされたクエリ、トークン化されたテキストクエリ、あいまい検索、および集計クエリで Lindorm ワイドテーブルのデータをクエリする方法について説明します。

前提条件

Lindorm-cli を使用して LindormTable に接続しています。詳細については、「Lindorm-cli を使用して LindormTable に接続して使用する」をご参照ください。
テストデータが生成され、検索インデックスが作成されます。詳細については、「検索インデックスの管理」をご参照ください。

多次元クエリの実行

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

例 1

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

次の結果が返されます。

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 6 | Li | 32 | M | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 1 | Zhang | 18 | M | Chaoyang District, Beijing | a***@example.net | Beijing |
| 20 | Wang | 28 | M | Binjiang District, 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%' ORDER BY user_id;

次の結果が返されます。

+---------+--------+-----+--------+--------------+------------------+------+
| 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 句もサポートしています。

次の文を実行して、Hangzhou が city 列に含まれるデータ行の数をクエリします。
```
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 条件を指定しない場合、対応する検索インデックスはヒットしません。この場合、_l_force_index パラメーターを WHERE キーワードの前に追加することで、クエリが指定されたインデックスにヒットするように強制できます。詳細については、HINT トピックの「Parameters of hintOptions」セクションと「Example 4: Specify _l_force_index_ヒント to forcibly use an index in the query」セクションをご参照ください。

フレーズベースのクエリの実行

次の文を実行して、フレーズを条件として指定したクエリを実行します。

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

次の結果が返されます。

+--------+-----+------+--------------+
|  name  | age | city |   address    |
+--------+-----+------+--------------+
| Li | 32 | Hangzhou | Yuhang District, Hangzhou |
| Wang | 28 | Hangzhou | Binjiang District, Hangzhou |
+--------+-----+------+--------------+

トークン化クエリの実行

重要

トークン化クエリは、LindormTable 2.7.2 以降のバージョンでのみ使用可能な MATCH 関数に依存しています。 LindormTable のバージョンを表示またはスペックアップするには、「LindormTable のリリースノート」と「Lindorm インスタンスのマイナーエンジンバージョンのスペックアップ」をご参照ください。

トークン化された列の場合、MATCH 関数を使用してトークン化クエリを実装する必要があります。次の例では、age が 10 ～ 30 の範囲で、address が Hangzhou と一致するデータ行をクエリします。 address 列はトークン化された列です。

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

次の結果が返されます。

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

トークン化テキストクエリの実行

重要

トークン化テキストクエリ機能を使用するには、LindormTable のバージョンが 2.8.0 以後で、LindormSearch のバージョンが ElasticSearch と互換性がある必要があります。バージョンが上記の要件を満たしていない場合は、マイナーバージョンを更新してください。

背景情報

検索インデックスを作成するときに、ワイドテーブルの VARCHAR 列に type=text 属性を設定すると、システムは列内のテキストをトークンに分割し、同等クエリやあいまいクエリなどのクエリを実行するときに検索インデックス内のトークンを使用します。これにより、予期しない検索結果が生じる可能性があります。

たとえば、address(type=text, analyzer=ik)address(type=text,analyzer=ik) 属性を設定して address 列をトークン化列として構成すると、インデックスを作成するときに、システムはトークン化ポリシーに基づいて列内のテキストを個々のトークンに分割します。たとえば、テキスト Binjiang District, Hangzhou は、個々のトークン Hangzhou と Binjiang District に分割されます。この場合、LIKE 操作を使用すると、あいまい検索は失敗します。

この例では、District と Hangzhou が分離されているため、結果は返されません。

SELECT * FROM search_table WHERE city LIKE '%District, Hangzhou%' 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));

説明

各ワイドテーブルに対して作成できる検索インデックスは 1 つだけです。検索インデックスが既に存在する場合にトークン化テキストクエリを有効にするには、最初に現在の検索インデックスを削除する必要があります。その後、新しいインデックスを作成するときにトークン化テキストクエリを有効にすることができます。

クエリメソッド

デフォルトでは、同等クエリとあいまい検索は完全一致に文字列全体を使用しますが、トークン化クエリは一致に 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 |
+---------+--------+-----+--------+--------------+------------------+------+