全文索引是什么
全文索引按「词」而不是按「前缀」检索：MySQL 用 FULLTEXT 索引配 MATCH ... AGAINST，InnoDB 自 5.6 起支持；PostgreSQL 用 tsvector 列加 GIN 索引，用 @@ 搭配 to_tsquery / plainto_tsquery 查询。它能避开 LIKE '%词%' 那种前置通配造成的全表扫描，但命中效果完全由分词规则决定。
基本信息
| MySQL 语法 | ALTER TABLE ... ADD FULLTEXT INDEX；查询用 MATCH(列) AGAINST('串' [模式]) |
|---|---|
| PostgreSQL 语法 | tsvector 生成列 + GIN 索引 + @@ 匹配 |
| 分词方式 | MySQL 默认按空格与标点切分，中文可配 ngram 解析器；PG 用文本搜索配置 |
| 最小词长 | MySQL InnoDB 默认 innodb_ft_min_token_size=3，更短的词不入索引 |
| 检索模式 | MySQL 自然语言模式按相关度排序，布尔模式支持 + - * 等操作符 |
典型使用场景
- 文章、商品标题与正文的关键词检索
- 后台搜索框替代 LIKE '%词%' 的全表扫描
- 布尔模式做包含与排除，例如「必须含 A 且不含 B」
- 按相关度取结果 Top N，替代手工排序规则
- 多语言内容按对应文本搜索配置分词
MySQL 建索引与查询
MySQL 建索引与查询
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content); -- 建全文索引
-- 布尔模式：+ 表示必须包含，- 表示必须排除
SELECT id, title FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);
-- 自然语言模式：返回值就是相关度分数，可用来排序
SELECT id, title, MATCH(title, content) AGAINST('mysql 索引') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('mysql 索引')
ORDER BY score DESC;PostgreSQL 建索引与查询
PostgreSQL 建索引与查询
-- 生成列保存分词结果，写入时自动更新
ALTER TABLE articles ADD COLUMN tsv tsvector
GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || content)) STORED;
CREATE INDEX idx_articles_tsv ON articles USING GIN (tsv);
-- plainto_tsquery 把普通文本转成查询，不会把符号当运算符
SELECT id, title FROM articles
WHERE tsv @@ plainto_tsquery('simple', 'mysql 索引')
ORDER BY id;两库对照
| 维度 | MySQL FULLTEXT | PostgreSQL |
|---|---|---|
| 索引对象 | 建在表上的 FULLTEXT 索引 | GIN 索引建在 tsvector 列上 |
| 查询写法 | MATCH(列) AGAINST('串' 模式) | tsvector @@ tsquery |
| 中文支持 | 需配置 ngram 解析器 | 需安装对应分词扩展 |
| 相关度 | AGAINST 的返回值即分数 | ts_rank / ts_rank_cd |
| 短词限制 | 低于 innodb_ft_min_token_size 不入索引 | 无同类硬限制，按配置分词 |
最小词长与停用词会让某些词根本查不到：搜不到时先确认 innodb_ft_min_token_size 与停用词列表，而不是先怀疑索引没建成功。
全文索引的写入门槛高于普通索引：每次写入都要重新分词。另外 InnoDB 全文索引里，未提交事务插入的行对全文检索不可见，刚写入马上搜索可能查不到。