mysql全文索引

'# MySQL 全文索引

一、背景与问题

在传统数据库系统中，全文搜索是一个长期存在的挑战。早期的MySQL通过LIKE模糊查询实现文本搜索，但这种方案存在严重局限性：

性能瓶颈：全表扫描导致查询效率极低
语义缺失：无法理解自然语言的语义关系
分词问题：中文等语言需要特殊处理

随着业务场景复杂度提升，越来越多的系统需要高效的文本搜索能力。MySQL在5.6版本引入了全文索引功能，通过倒排索引（Inverted Index）机制，为文本搜索提供了更专业的解决方案。

二、基本原理

MySQL全文索引的核心原理是构建倒排索引，其工作流程如下：

分词处理：将文本按规则拆分为词条（token）
建立映射：每个词条对应包含它的文档列表
查询匹配：通过词条查找文档列表

1. 倒排索引结构

{
  "词条1": [文档ID1, 文档ID2],
  "词条2": [文档ID3, 文档ID4],
  ...
}

2. MySQL的全文索引实现

MySQL支持两种全文索引类型：

类型	特点	适用场景
`Ngram`	基于分词的索引	中文等需要分词的文本
`Natural Language`	自然语言处理	英文等不需要分词的文本

三、环境准备

确保MySQL 5.6+版本支持全文索引，可使用以下SQL检查：

SHOW VARIABLES LIKE 'ft%';

需要配置ft_min_word_len参数（默认为4），控制最小分词长度：

SET GLOBAL ft_min_word_len = 2;

四、核心实现

1. 创建全文索引

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title TEXT,
    content TEXT,
    FULLTEXT INDEX idx_content (content)
) ENGINE=InnoDB;

关键点：

使用FULLTEXT INDEX语法创建
支持InnoDB和MyISAM引擎（InnoDB推荐）
自动使用Ngram分词器（需配置）

2. 插入数据

INSERT INTO articles (title, content) VALUES
('MySQL全文索引原理', 'MySQL的全文索引基于倒排索引机制实现'),
('Elasticsearch对比', 'Elasticsearch使用Lucene实现更高级的搜索功能');

3. 全文搜索查询

SELECT * FROM articles
WHERE MATCH(content) AGAINST('全文索引');

执行计划分析：

使用EXPLAIN可查看是否命中全文索引
默认使用Natural Language模式，匹配度由TF-IDF计算

五、完整案例：博客系统搜索功能

1. 业务场景

某博客系统需要支持按标题/内容搜索文章，要求：

支持中文分词
支持模糊匹配
支持分页查询

2. 表结构设计

CREATE TABLE blog_posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    create_time DATETIME,
    FULLTEXT INDEX idx_content (title, content)
) ENGINE=InnoDB;

3. 查询实现

SELECT 
    id, 
    title, 
    content, 
    MATCH(title, content) AGAINST('MySQL 分词' IN BOOLEAN MODE) AS score
FROM 
    blog_posts
WHERE 
    MATCH(title, content) AGAINST('MySQL 分词' IN BOOLEAN MODE)
ORDER BY 
    score DESC
LIMIT 10;

4. 分词优化配置

-- 设置ngram分词长度（建议设置为2）
SET GLOBAL ft_min_word_len = 2;

-- 创建ngram分词插件（需先安装）
CREATE PLUGIN ngram SONAME 'ngram.so';

六、源码解析（InnoDB实现）

MySQL的全文索引在InnoDB引擎中通过ft_index结构实现，关键代码包括：

// ft_index.h
struct ft_index {
    char *buffer;       // 索引缓冲区
    size_t size;        // 缓冲区大小
    int (*insert)(...); // 插入词条函数
    int (*search)(...);  // 查询词条函数
};

// ft_index.c
int ft_index_insert(const char *word, const char *doc_id) {
    // 实现ngram分词逻辑
    for (int i = 0; i < ft_min_word_len; i++) {
        char *token = extract_ngram(word, i, ft_min_word_len);
        add_to_inverted_index(token, doc_id);
    }
    return 0;
}

七、进阶使用

1. 复合查询

SELECT * FROM articles
WHERE MATCH(content) AGAINST(
    '全文索引' 
    WITH QUERY EXPANSION 
    IN NATURAL LANGUAGE MODE
);

2. 布尔模式查询

SELECT * FROM articles
WHERE MATCH(content) AGAINST(
    '+全文 -索引' 
    IN BOOLEAN MODE
);

3. 短语搜索

SELECT * FROM articles
WHERE MATCH(content) AGAINST(
    '"全文索引"' 
    IN BOOLEAN MODE
);

八、性能与工程实践

1. 性能优化策略

优化手段	说明
调整分词长度	增加`ft_min_word_len`提升查询速度
索引维护	定期执行`OPTIMIZE TABLE`
查询优化	避免使用`LIKE '%xxx%'`
缓存机制	使用Redis缓存热门搜索结果

2. 索引维护

-- 建立索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

-- 重建索引
OPTIMIZE TABLE articles;

-- 删除索引
ALTER TABLE articles DROP INDEX idx_content;

3. 安全考虑

禁用非必要权限：RELOAD、PROCESS等
设置ft_stopword_file防止敏感词暴露
使用mysql_secure_installation工具加固

九、常见问题与踩坑

1. 中文分词问题

错误示例：

SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

问题：默认分词器无法处理中文

解决：配置ngram插件并调整分词长度

2. 索引失效问题

错误场景：

SELECT * FROM articles WHERE content LIKE '%全文%';

问题：使用LIKE通配符导致索引失效

解决：改用全文搜索或使用MATCH查询

3. 分词冲突问题