全文检索并没有你想象中的难，创建全文检索的核心思路是：初始化索引环境，为表字段创建索引，通过分词处理搜索文本，匹配索引中的词汇与记录行，最后根据匹配结果构建并返回相关数据。

肖哥弹架构 跟大家“弹弹” 数据库设计，需要代码关注

欢迎 点赞，点赞，点赞。

关注公号Solomon肖哥弹架构获取更多精彩内容

流程

数据库：全文索引实现技巧，架构师是这样实现的_搜索

整体思路

  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名、表名和列名。

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

  3. 词汇表 (WORDS)：存储索引中使用的所有唯一词汇及其对应的唯一ID。

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

  4. 行映射表 (ROWS)：存储每个索引项的哈希值、索引ID和主键条件。

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

  5. 词汇与行映射表 (MAP)：将词汇ID与对应的行ID关联起来，以便快速检索包含特定词汇的行。

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

  6. 忽略列表 (IGNORELIST)：存储在全文索引中应被忽略的词汇。

    sql
CREATE TABLE FT.IGNORELIST(
    LIST VARCHAR
);

  7. 数据存储：当创建全文索引时，数据库会扫描指定的列，将文本分解成词汇，并为每个词汇创建一个条目。然后，它会将这些词汇与包含它们的行的主键条件关联起来。

  8. 触发器更新：数据库使用触发器来自动更新全文索引。当对表进行插入、更新或删除操作时，触发器会调用FullText类的fire方法来相应地更新索引数据。

  9. 搜索查询：搜索操作通过构建一个查询来实现，该查询使用词汇表和映射表来找到包含搜索词汇的行。然后，可以使用这些行的主键条件来检索原始表中的数据。

业务全文索引数据存储

有一个电子商务网站，数据库中有一个名为 Products 的表，它包含产品的各种信息，比如产品ID、名称、描述和价格。我们想要对产品的名称和描述进行全文搜索，以便用户可以快速找到相关的产品。

首先，我们需要创建一个全文索引。以下是创建全文索引的 SQL 语句：

FT_CREATE_INDEX('PUBLIC', 'Products', 'Name, Description');
  • 1.

这条语句会在 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 表中找到对应的查询条件，最终返回满足条件的产品记录。

通过这种方式，数据库的全文索引提供了一种高效的方式来搜索和检索大型文本数据。