使用 MySql 輕鬆五步驟實現全文索引

全文檢索並沒有你想象中的難，建立全文檢索的核心思路是：初始化索引環境，為表字段建立索引，透過分詞處理搜尋文字，匹配索引中的詞彙與記錄行，最後根據匹配結果構建並返回相關資料。

流程

整體思路

1. 初始化全文搜尋：首先，如果尚未初始化，需要透過呼叫FullText.init()方法來初始化全文搜尋功能。這會在資料庫中建立必要的schema和表。

2. 建立全文索引：使用FT_CREATE_INDEX函式為特定的表和列建立全文索引。這會觸發對錶中現有資料的索引構建過程。

3. 更新資料觸發器：當在索引的表上執行插入、更新或刪除操作時，相應的觸發器會被啟用，以確保全文索引與表資料保持同步。

4. 執行搜尋查詢：使用者執行全文搜尋，使用FT_SEARCH函式提交搜尋請求。搜尋請求包含搜尋文字、結果數量限制和偏移量。

5. 處理搜尋請求：資料庫接收到搜尋請求後，會進行以下操作：

• 分詞：將搜尋文字分解成單個詞彙。

• 查詢詞彙ID：在WORDS表中查詢每個詞彙對應的ID。

• 收集行ID：使用詞彙ID在MAP表中查詢所有相關聯的行ID。

6. 構建查詢結果：根據收集到的行ID，構建查詢結果。這可能包括：

• 直接使用行ID在ROWS表中檢索主鍵條件。

• 使用主鍵條件在原始表中檢索具體的資料行。

7. 返回結果集：資料庫將構建好的查詢結果作為結果集返回給使用者。結果集可能包含原始資料或者用於進一步查詢的SQL語句。

8. 結果展示：使用者根據返回的結果集獲取所需的資料，這可能涉及到在應用程式中展示搜索結果或進一步的資料操作。

全文索引結構設計

1. 索引表：資料庫使用一個專門的表來儲存全文索引資訊。這個表通常位於一個名為FT（FullText的縮寫）的schema中。

2. 索引資訊表 (INDEXES)：儲存每個全文索引的後設資料，包括索引ID、schema名、表名和列名。

        CREATE TABLE FT.INDEXES(
            ID INT AUTO_INCREMENT PRIMARY KEY,
            SCHEMA VARCHAR,
            TABLE VARCHAR,
            COLUMNS VARCHAR,
            UNIQUE(SCHEMA, TABLE)
        );

3. 詞彙表 (WORDS)：儲存索引中使用的所有唯一詞彙及其對應的唯一ID。

        CREATE TABLE FT.WORDS(
            ID INT AUTO_INCREMENT PRIMARY KEY,
            NAME VARCHAR,
            UNIQUE(NAME)
        );

4. 行對映表 (ROWS)：儲存每個索引項的雜湊值、索引ID和主鍵條件。

        CREATE TABLE FT.ROWS(
            ID IDENTITY,
            HASH INT,
            INDEXID INT,
            KEY VARCHAR,
            UNIQUE(HASH, INDEXID, KEY)
        );

5. 詞彙與行對映表 (MAP)：將詞彙ID與對應的行ID關聯起來，以便快速檢索包含特定詞彙的行。

        CREATE TABLE FT.MAP(
            ROWID INT,
            WORDID INT,
            PRIMARY KEY(WORDID, ROWID)
        );

6. 忽略列表 (IGNORELIST)：儲存在全文索引中應被忽略的詞彙。

        CREATE TABLE FT.IGNORELIST(
            LIST VARCHAR
        );

7. 資料儲存：當建立全文索引時，資料庫會掃描指定的列，將文字分解成詞彙，併爲每個詞彙建立一個條目。然後，它會將這些詞彙與包含它們的行的主鍵條件關聯起來。

8. 觸發器更新：資料庫使用觸發器來自動更新全文索引。當對錶進行插入、更新或刪除操作時，觸發器會呼叫FullText類的fire方法來相應地更新索引資料。

9. 搜尋查詢：搜尋操作透過構建一個查詢來實現，該查詢使用詞彙表和對映表來找到包含搜尋詞彙的行。然後，可以使用這些行的主鍵條件來檢索原始表中的資料。

業務全文索引資料儲存

有一個電子商務網站，資料庫中有一個名為 Products 的表，它包含產品的各種資訊，比如產品ID、名稱、描述和價格。我們想要對產品的名稱和描述進行全文搜尋，以便使用者可以快速找到相關的產品。

首先，我們需要建立一個全文索引。以下是建立全文索引的 SQL 語句：

FT_CREATE_INDEX('PUBLIC', 'Products', 'Name, Description');

這條語句會在 FT schema 中建立相關的索引資訊，並在 INDEXES 表中新增一條記錄。

INDEXES 表示例記錄：

ID | SCHEMA | TABLE  | COLUMNS
---|--------|--------|---------
1  | PUBLIC | Products | Name, Description

接下來， 會掃描 Products 表中的 Name 和 Description 列，將文字分解成詞彙，並在 WORDS 表中為每個唯一的詞彙建立記錄。

WORDS 表示例記錄：

ID | NAME
---|---------
1  | phone
2  | case
3  | leather
...

然後， 會為 Products 表中的每一行建立一個雜湊值，並在 ROWS 表中記錄這個雜湊值、索引ID和主鍵條件（在這個例子中是產品ID）。

ROWS 表示例記錄：

ID | HASH  | INDEXID | KEY
---|-------|---------|-----------------
1  | 12345 | 1       | ProductID = 1
2  | 67890 | 1       | ProductID = 2
...

同時，會在 MAP 表中為每個詞彙和對應的行ID建立對映關係。

MAP 表示例記錄：

ROWID | WORDID
------|--------
1     | 1
1     | 3
2     | 2
...

現在，假設使用者想要搜尋包含“leather case”的產品。會在 WORDS 表中查詢這兩個詞彙，然後在 MAP 表中找到包含這些詞彙的行ID，最後在 ROWS 表中使用這些行ID來構建查詢條件，找到對應的產品記錄。

搜尋：

FT_SEARCH('leather case', 10, 0);

這條語句會返回一個結果集，其中包含最多10條記錄，沒有偏移量。會根據 MAP 表找到所有包含“leather”和“case”的行ID，然後使用這些行ID在 ROWS 表中找到對應的查詢條件，最終返回滿足條件的產品記錄。

透過這種方式，資料庫的全文索引提供了一種高效的方式來搜尋和檢索大型文字資料。