Lindorm：管理搜尋索引

前提條件

• 已開通搜尋索引。

  說明

  搜尋索引功能依賴搜尋引擎和LTS，因此開通搜尋索引時需同時開通搜尋引擎和LTS服務。

• 已通過Lindorm-cli串連並使用寬表引擎。

使用限制

資料準備

使用搜尋索引前，需要先建立目標表並寫入測試資料。

1. 執行以下語句建立目標表，表名為search_table。

   CREATE DATABASE searchindex_db;
   USE searchindex_db;
   CREATE TABLE IF NOT EXISTS search_table (user_id BIGINT, name VARCHAR, age SMALLINT, gender VARCHAR, address VARCHAR, email VARCHAR, city VARCHAR, PRIMARY KEY (user_id));

2. 執行以下語句在目標表中寫入四條資料。

   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (1, '張先生', 18, 'M', '北京市朝陽區', 'a***@example.net', '北京');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (6, '李先生', 32, 'M', '杭州市餘杭區', 'a***@example.net', '杭州');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (20, '王先生', 28, 'M', '杭州市濱江區', 'a***@example.net', '杭州');
   UPSERT INTO search_table (user_id,name,age,gender,address,email,city) VALUES (28, '陳女士', 36, 'F', '深圳市南山區', 'a***@example.net', '深圳');

3. 執行以下語句查詢目標表中的資料。

   SELECT * FROM search_table LIMIT 10;

   查詢結果如下：

   +---------+--------+-----+--------+--------------+------------------+------+
   | 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  | 深圳 |
   +---------+--------+-----+--------+--------------+------------------+------+

管理搜尋索引

以上述測試資料為例，建立搜尋索引可以滿足以下需求：

1. 建立搜尋索引。

   CREATE INDEX IF NOT EXISTS idx USING SEARCH ON search_table (
     name,
     age,
     gender,
     address(type=text, analyzer=ik),
     email,
     city
   ) WITH (numShards=4);

   說明

   • 以上樣本指定了numShards的值為4，表示建立的索引idx的分區數量為4。如果不指定numShards，則預設numShards的值為搜尋引擎節點數的兩倍。在建立用於生產環境的搜尋索引前，建議您提前規劃好分區數量，避免單分區資料量過大，進而影響系統穩定性。分區設計說明及索引使用建議，可參考numShards參數說明。

   • 建立搜尋索引語句中，address欄位按照ik分詞器進行分詞。

   • 建立搜尋索引時，系統預設在後台非同步構建搜尋索引，歷史資料越多，構建所需的時間越長。如果您希望同步構建搜尋索引，請在建立搜尋索引語句後添加SYNC關鍵字。

   • 執行建立搜尋索引語句時的報錯和解決方案請參考常見問題。

2. 查詢指定表的搜尋索引資訊。

   SHOW INDEX FROM search_table;

   查詢結果如下：

   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
   | TABLE_SCHEMA  |  DATA_TABLE  | INDEX_NAME | INDEX_STATE | INDEX_TYPE | INDEX_COVERED |            INDEX_COLUMN            | INDEX_TTL | INDEX_DESCRIPTION |
   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+
   | searchindex_db| search_table | idx        | BUILDING    | SEARCH     | NA            | address,city,age,gender,name,email | 0         |                   |
   +---------------+--------------+------------+-------------+------------+---------------+------------------------------------+-----------+-------------------+

   說明

   • 搜尋索引尚未構建完成時，索引狀態為BUILDING。搜尋索引構建完成後，索引狀態為ACTIVE。返回結果的詳細說明，請參見SHOW INDEX。

   • 您可以通過LTS控制台查看索引構建進度，具體操作請參見如何查看搜尋索引的全量構建進度。

3. 查詢資料，更多查詢情境的方法請參見通過搜尋索引查詢寬表資料。

   SELECT * FROM search_table WHERE name='王先生' AND age > 18 AND city='杭州';

   查詢結果如下：

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

4. 刪除索引。

   重要

   2.7.7以下版本的寬表引擎，在刪除索引前，您需要先執行ALTER INDEX IF EXISTS idx ON search_table DISABLED;禁用索引。索引禁用後狀態為DISABLED，如果想要再次啟用，需執行ALTER INDEX IF EXISTS idx ON search_table REBUILD;語句重新構建。

   DROP INDEX IF EXISTS idx ON search_table;

使用動態列

1. 開啟動態列功能。

   ALTER TABLE search_table SET 'DYNAMIC_COLUMNS' = 'true';

2. 添加動態列。動態列需要顯式添加至搜尋索引中。此處動態列的樣本為password，且password未在建表時定義。

   • 建立搜尋索引時指定動態列。

     CREATE INDEX idx USING SEARCH ON search_table(user_id,name,age,gender,password);

   • 已有搜尋索引，通過ALTER INDEX命令添加動態列。

     ALTER INDEX idx ON search_table ADD COLUMNS(password);

3. 寫入動態列。動態列password只能寫入HexString格式資料。

   UPSERT INTO search_table (user_id,name,age,gender,address,email,city,password) VALUES (30, '王女士', 38, 'F', '深圳市南山區', 'a***@example.net', '深圳', 'ef0011');

4. 查詢動態列。動態列password只能以HexString格式查詢。

   SELECT * FROM search_table WHERE password='ef0011' LIMIT 1;

   返回結果如下：

   +---------+--------+-----+--------+--------------+------------------+------+----------+
   | user_id |  name  | age | gender |   address    |      email       | city | password |
   +---------+--------+-----+--------+--------------+------------------+------+----------+
   | 30      | 王女士 | 38   | F      | 深圳市南山區  | a***@example.net | 深圳 | 0xef0011 |
   +---------+--------+-----+--------+--------------+------------------+------+----------+

使用萬用字元列

萬用字元列是指列名帶有萬用字元的特殊列。定義萬用字元列後，可以動態地寫入任何滿足萬用字元規則的列，而不需要預先定義每個具體的列。萬用字元列適用於寫入列數量較多，且列名具有特定規則的情境，詳細說明請參見萬用字元列。

1. 建立寬表並定義萬用字元列。樣本中定義了萬用字元列info_*和address_*。

   CREATE TABLE search_table (user_id BIGINT, name VARCHAR, age SMALLINT, `info_*` VARCHAR, `address_*` VARCHAR, PRIMARY KEY (user_id)) WITH (wildcard_column='info_*,address_*');

2. 將萬用字元列添加至搜尋索引。萬用字元列address_*設定為分詞列。

    CREATE INDEX idx USING SEARCH ON search_table (name,age,`info_*`,`address_*`(type=text, analyzer=ik));

3. 向萬用字元列寫入資料。info_gender、info_email和info_city屬於萬用字元列info_*，address_detail屬於萬用字元列address_*，這些列不需要預先定義，在寫入時可以自動匹配到對應的萬用字元列，並繼承相應的資料類型、分詞等屬性。

   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (1, '張先生', 18, 'M', '北京市朝陽區', 'a***@example.net', '北京');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (6, '李先生', 32, 'M', '杭州市餘杭區', 'a***@example.net', '杭州');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (20, '王先生', 28, 'M', '杭州市濱江區', 'a***@example.net', '杭州');
   UPSERT INTO search_table (user_id,name,age,info_gender,address_detail,info_email,info_city) VALUES (28, '陳女士', 36, 'F', '深圳市南山區', 'a***@example.net', '深圳');

4. 查詢萬用字元列。

   SELECT * FROM search_table WHERE name='王先生' AND age > 18 AND info_city='杭州' LIMIT 10;

   返回結果如下：

   +---------+--------+-----+----------------+-----------+------------------+-------------+
   | user_id | name   | age | address_detail | info_city | info_email       | info_gender |
   +---------+--------+-----+----------------+-----------+------------------+-------------+
   | 20      | 王先生  | 28  | 杭州市濱江區    | 杭州      | a***@example.net | M           |
   +---------+--------+-----+----------------+-----------+------------------+-------------+

   說明

   如需驗證查詢是否使用了搜尋索引，可執行以下EXPLAIN命令：EXPLAIN SELECT * FROM search_table WHERE name='王先生' AND age > 18 AND info_city='杭州' LIMIT 10;。

常見問題