MySQL索引优化：全文索引
发布时间
阅读量：
阅读量
在MySQL中，全文索引（Full-Text Index） 是一种专门为文本数据设计的索引类型，用于高效处理自然语言搜索（如文章内容、评论、关键词匹配等）。与传统的B-Tree索引相比，全文索引在处理大量文本数据时性能更优，且支持更复杂的搜索语法。
核心概念
适用场景 ：
* 文本字段（`CHAR`、`VARCHAR`、`TEXT`）的模糊搜索。
* 关键词匹配、短语搜索、语义搜索等。索引原理 ：
* 分词（Tokenizer）：将文本拆分为词元（Tokens）。
* 去重和排序：构建倒排索引（Inverted Index），记录每个词元出现的位置。支持的存储引擎 ：
* InnoDB（MySQL 5.6+）和 MyISAM。创建全文索引
单字段索引
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(255),
content TEXT,
FULLTEXT(title, content) -- 创建联合全文索引
);
sqlALTER TABLE添加索引
ALTER TABLE articles ADD FULLTEXT(title, content);
sql索引选项
- 最小/最大词长 ：通过
ft_min_word_len和ft_max_word_len配置（默认4和84字节）。 - 停用词（Stopwords） ：忽略常见词（如"the"、“and”），可自定义。
全文搜索语法
自然语言模式（Natural Language Mode）
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL索引优化' IN NATURAL LANGUAGE MODE);
sql- 匹配包含关键词的文档，按相关性排序。
布尔模式（Boolean Mode）
支持高级搜索操作符：
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST(
'+MySQL +"索引优化"' -- 必须包含"MySQL"和短语"索引优化"
IN BOOLEAN MODE
);
sql常用操作符：
+：必须包含。-：必须排除。*：前缀匹配（如MySQL*匹配"MySQL"、“MySQL优化”）。"：短语匹配（如"数据库设计"）。
全文索引与LIKE的对比
| 特性 | LIKE ‘%关键词%’ | 全文索引（MATCH AGAINST） |
|---|---|---|
| 性能 | 全表扫描，数据量大时极慢 | 索引扫描，速度快 |
| 语法灵活性 | 仅支持简单模糊匹配 | 支持布尔逻辑、短语搜索、权重 |
| 分词处理 | 不支持，需手动处理 | 自动分词（如中文需自定义分词） |
| 排序方式 | 无特定顺序 | 按相关性评分排序 |
| 适用场景 | 少量数据的简单模糊查询 | 大量文本的复杂搜索 |
中文全文索引优化
分词问题 ：
MySQL内置分词器对中文支持不佳（默认按空格分词），需使用第三方插件（如ngram分词器）：
-- 启用ngram分词器
ALTER TABLE articles ADD FULLTEXT(title, content) WITH PARSER ngram;
sql最小词长调整 ：
-- 修改配置文件my.cnf
ft_min_word_len = 1
-- 重建索引使配置生效
ALTER TABLE articles DROP INDEX idx_ft;
ALTER TABLE articles ADD FULLTEXT(title, content);
sql性能注意事项
索引更新开销 ：
全文索引的维护成本高于普通索引，写入操作可能变慢。
查询优化 ：
* 避免在`MATCH`字段上使用函数（如`UPPER()`），会导致索引失效。
* 复合查询中，全文索引条件应放在`WHERE`子句的前面。索引大小 ：
全文索引通常比原数据大，需预留足够磁盘空间。
EXPLAIN验证全文索引
EXPLAIN SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL');
sqltype列显示fulltext：表示使用了全文索引。Extra列显示Using where：表示通过全文索引过滤结果。
适用场景
- 全文索引不适用于 OLTP 场景。
全部评论 (0)
还没有任何评论哟~