一、全文索引和传统索引的基本概念
我们先来打个比方。传统索引就像是一本书的目录，它能帮你快速找到某个特定章节的位置。而全文索引更像是这本书的搜索引擎，不仅能找到章节，还能帮你找到包含特定词汇的所有页面。
在MySQL中，传统索引通常是B-Tree结构的，它对精确匹配查询特别有效。比如你要找用户表中ID为100的记录，B-Tree索引就能大显身手。而全文索引则是专门为文本搜索设计的，它会把文本内容拆分成词元(token)，然后建立倒排索引。
举个简单的例子，假设我们有个文章表：
-- 技术栈：MySQL 8.0
-- 创建带传统索引和全文索引的表
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
author VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- 传统B-Tree索引
INDEX idx_author (author),
-- 全文索引
FULLTEXT INDEX ft_idx_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
二、工作原理的深度对比
传统索引的工作原理相对简单直接。当你创建一个B-Tree索引后，MySQL会为索引列建立一个平衡树结构。查询时，MySQL可以快速定位到符合条件的记录。
全文索引的工作机制就复杂多了。以InnoDB的全文索引为例：
- 首先它会用分词器把文本拆分成词元
- 然后建立倒排索引，记录每个词元出现在哪些文档中
- 查询时，先分析查询词，然后在倒排索引中查找匹配
MySQL 5.7以后引入了ngram分词器，这对中文特别有用。比如"数据库"会被拆分成"数据"和"据库"两个词元(假设ngram_token_size=2)。
-- 技术栈：MySQL 8.0
-- 插入一些测试数据
INSERT INTO articles (title, content, author) VALUES
('MySQL性能优化', '本文详细介绍了MySQL数据库的性能优化技巧', '张三'),
('索引使用指南', '讲解如何正确使用数据库索引提高查询效率', '李四'),
('全文搜索实践', 'MySQL全文索引的实际应用案例分享', '王五');
-- 传统索引查询示例
SELECT * FROM articles WHERE author = '张三';
-- 这个查询会使用idx_author索引
-- 全文索引查询示例
SELECT * FROM articles WHERE MATCH(content) AGAINST('+优化 +技巧' IN BOOLEAN MODE);
-- 这个查询会使用ft_idx_content全文索引
三、性能表现的实际测试
为了更直观地感受两者的区别，我们来做个简单的性能对比。假设我们有一个包含10万条记录的文章表。
-- 技术栈：MySQL 8.0
-- 创建测试表
CREATE TABLE large_articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
-- 传统索引
INDEX idx_title (title),
-- 全文索引
FULLTEXT INDEX ft_idx_content (content)
) ENGINE=InnoDB;
-- 插入10万条测试数据(这里用存储过程模拟)
DELIMITER //
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 100000 DO
INSERT INTO large_articles (title, content)
VALUES (
CONCAT('文章标题', i),
CONCAT('这是第', i, '篇文章的内容，包含一些关键词如数据库、性能、优化等')
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_test_data();
现在我们来比较两种查询的性能：
-- 精确匹配查询(使用传统索引)
SELECT * FROM large_articles WHERE title = '文章标题99999';
-- 执行时间：约0.001秒
-- 模糊查询(不使用索引)
SELECT * FROM large_articles WHERE content LIKE '%优化%';
-- 执行时间：约0.5秒
-- 全文索引查询
SELECT * FROM large_articles WHERE MATCH(content) AGAINST('优化');
-- 执行时间：约0.01秒
可以看到，对于精确匹配，传统索引表现最好。而对于文本搜索，全文索引比LIKE模糊查询快得多。
四、适用场景分析
经过上面的对比，我们可以总结出它们各自的适用场景：
传统索引最适合：
- 精确匹配查询(WHERE column = value)
- 范围查询(WHERE column > value)
- 排序操作(ORDER BY column)
- 连接操作的关联字段
全文索引最适合：
- 自然语言搜索(如搜索文章内容)
- 布尔搜索(AND/OR/NOT等组合查询)
- 相关性排序(按匹配度排序)
- 中文等无空格分隔语言的分词搜索
举个实际例子，电商网站的商品搜索：
- 用传统索引：按商品ID精确查找、按分类筛选、按价格区间查询
- 用全文索引：搜索商品名称和描述中的关键词
-- 技术栈：MySQL 8.0
-- 电商商品表示例
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200),
description TEXT,
category_id INT,
price DECIMAL(10,2),
-- 传统索引
INDEX idx_category (category_id),
INDEX idx_price (price),
-- 全文索引
FULLTEXT INDEX ft_idx_name_desc (name, description)
);
-- 传统索引查询：按分类和价格筛选
SELECT * FROM products
WHERE category_id = 5 AND price BETWEEN 100 AND 500;
-- 全文索引查询：搜索名称和描述
SELECT * FROM products
WHERE MATCH(name, description) AGAINST('+手机 +防水' IN BOOLEAN MODE);
五、使用注意事项
使用全文索引时需要注意以下几点：
- 索引大小：全文索引通常会比原数据大很多，特别是对于长文本
- 分词配置：MySQL的ngram_token_size参数影响中文分词效果(建议设为2)
- 停用词：某些常见词可能被忽略，可以通过配置调整
- 最小词长：默认忽略太短的词(innodb_ft_min_token_size)
- 维护成本：数据修改时需要更新全文索引，会有额外开销
-- 技术栈：MySQL 8.0
-- 查看全文索引配置
SHOW VARIABLES LIKE 'innodb_ft%';
-- 重建全文索引(数据大量变更后建议执行)
ALTER TABLE articles DROP INDEX ft_idx_content;
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content);
六、总结与建议
经过全面对比，我们可以得出以下结论：
- 传统索引适合结构化数据的精确查询，全文索引适合非结构化文本的搜索
- 全文索引在文本搜索场景下性能远超LIKE模糊查询
- 两者可以互补使用，而不是相互替代
- 对于中文搜索，务必使用ngram分词器并合理配置参数
在实际应用中，建议：
- 为经常需要精确查询的字段建立传统索引
- 为需要文本搜索的字段建立全文索引
- 避免过度索引，定期检查索引使用情况
- 对于特别复杂的搜索需求，可以考虑Elasticsearch等专业搜索引擎
-- 技术栈：MySQL 8.0
-- 检查索引使用情况
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';
-- 优化表(定期维护)
ANALYZE TABLE articles;
OPTIMIZE TABLE articles;
评论