🔍 MySQL的全文索引和空间索引：让你的数据库"上天入地"！

考察点： FULLTEXT全文索引、GIS空间数据、ElasticSearch替代方案

📚 引言：数据库里的"搜索引擎"和"地图导航"

嘿，小伙伴们！👋 今天我们要聊聊MySQL中两个"特殊技能"——全文索引和空间索引。

想象一下，你在图书馆找书🔍：

• 普通索引：就像书架上的分类标签，你知道书的编号，直接去找就行
• 全文索引：就像图书馆的搜索系统，你只记得书里有"勇敢"这个词，系统帮你找出所有相关的书
• 空间索引：就像地图导航📍，告诉你"找出附近3公里内的所有餐厅"

听起来是不是很酷？让我们深入探索一下！

---

第一部分：全文索引（FULLTEXT Index）🔎

1.1 什么是全文索引？

全文索引是一种特殊的索引类型，专门用于文本内容的快速搜索。它不同于普通的B+树索引，它能够对文本内容进行分词，然后建立倒排索引。

🌰 生活例子：

假设你在朋友圈发了一条状态："今天天气真好，和小明去爬山了，开心！😊"

• 普通索引：只能通过ID快速找到这条动态
• 全文索引：可以通过"天气"、"爬山"、"小明"、"开心"任意一个词找到这条动态

1.2 全文索引的使用场景

✅ 适合的场景：

1. 文章内容搜索：博客系统、新闻网站
2. 商品描述搜索：电商平台的商品详情检索
3. 日志内容查询：查找包含特定关键词的日志
4. 评论搜索：论坛、社交平台的评论检索

❌ 不适合的场景：

1. 数据量特别大（上千万级别）→ 建议用ElasticSearch
2. 需要复杂的分词和相关性排序
3. 需要实时更新且查询频繁

1.3 全文索引的创建和使用

📝 创建全文索引

-- 方式1：建表时创建
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(50),
    FULLTEXT INDEX ft_title_content (title, content)  -- 创建全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 方式2：后续添加
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 方式3：使用CREATE INDEX
CREATE FULLTEXT INDEX ft_title ON articles(title);

🔍 使用全文索引查询

-- 自然语言模式（默认）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL数据库优化');

-- 布尔模式（支持运算符）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- + 表示必须包含
-- - 表示必须不包含
-- * 表示通配符
-- "" 表示短语

-- 查询扩展模式（自动扩展搜索词）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库' WITH QUERY EXPANSION);

🎯 全文索引的三种模式对比

模式	说明	使用场景	示例
自然语言模式	默认模式，按相关性排序	普通搜索	AGAINST('MySQL')
布尔模式	支持+、-、*等运算符	精确控制搜索条件	AGAINST('+MySQL -Oracle' IN BOOLEAN MODE)
查询扩展模式	二次搜索，扩展结果	模糊搜索，扩大范围	AGAINST('数据库' WITH QUERY EXPANSION)

1.4 中文分词问题 🇨🇳

MySQL的全文索引默认使用空格和标点符号分词，对中文支持不太友好！

解决方案：

方案1：使用ngram分词器（MySQL 5.7+）

-- 设置ngram分词的token大小（默认为2）
SET GLOBAL ngram_token_size = 2;

-- 创建使用ngram的全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 查询
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('中文分词');

方案2：使用ElasticSearch（推荐大数据量场景）

优点：
✅ 专业的搜索引擎
✅ 支持中文分词（IK分词器）
✅ 相关性评分更准确
✅ 支持高亮显示
✅ 支持聚合分析

缺点：
❌ 需要额外部署ES集群
❌ 数据同步成本
❌ 学习曲线较陡

1.5 全文索引的注意事项 ⚠️

1. 只支持InnoDB和MyISAM引擎（MySQL 5.6+的InnoDB才支持）
2. 最小词长度限制：
   • InnoDB默认最小3个字符
   • 可通过innodb_ft_min_token_size参数调整
3. 停止词：太常见的词（如"的"、"是"、"在"）会被忽略
4. 性能考虑：全文索引会占用较多磁盘空间
5. 更新成本：INSERT、UPDATE操作会比普通表慢

---

第二部分：空间索引（Spatial Index）📍

2.1 什么是空间索引？

空间索引是用于**地理位置数据（GIS数据）**的特殊索引，可以快速查询空间对象之间的关系，比如：

• 找出附近的餐厅
• 计算两点之间的距离
• 判断点是否在某个区域内

🗺️ 生活例子：

打开美团/大众点评，搜索"附近的餐厅"：

1. 你的位置：经度116.4074，纬度39.9042（北京天安门）
2. 搜索范围：方圆3公里
3. 结果：显示所有3公里内的餐厅，按距离排序

这就是空间索引的典型应用！

2.2 空间索引的使用场景

✅ 适合的场景：

1. LBS应用：附近的人、附近的店
2. 地图服务：导航、路径规划
3. 物流系统：配送范围查询、司机调度
4. 房产系统：查找某区域的房源
5. O2O平台：商家覆盖范围、配送范围

2.3 MySQL的空间数据类型

-- 常用的空间数据类型
POINT           -- 点（经度，纬度）
LINESTRING      -- 线（多个点连接）
POLYGON         -- 多边形（封闭区域）
GEOMETRY        -- 任意几何类型

2.4 空间索引的创建和使用

📍 创建空间索引

-- 创建表
CREATE TABLE restaurants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    location POINT NOT NULL,  -- 存储经纬度
    SPATIAL INDEX idx_location (location)  -- 创建空间索引
) ENGINE=InnoDB;

-- 插入数据（经度在前，纬度在后）
INSERT INTO restaurants (name, location) VALUES
('老北京炸酱面', ST_GeomFromText('POINT(116.4074 39.9042)')),
('麻辣香锅', ST_GeomFromText('POINT(116.4080 39.9050)')),
('黄焖鸡米饭', ST_GeomFromText('POINT(116.4100 39.9100)'));

🔍 空间查询示例

1️⃣ 查找某个点附近的所有餐厅（最常用！）

-- 查找当前位置(116.4074, 39.9042)附近3公里的餐厅
SELECT 
    id,
    name,
    ST_Distance_Sphere(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)')
    ) / 1000 AS distance_km  -- 距离（公里）
FROM restaurants
WHERE ST_Distance_Sphere(
    location,
    ST_GeomFromText('POINT(116.4074 39.9042)')
) <= 3000  -- 3000米 = 3公里
ORDER BY distance_km;

2️⃣ 查找矩形区域内的餐厅

-- 定义一个矩形区域
SET @rect = ST_GeomFromText('POLYGON((
    116.4000 39.9000,
    116.4200 39.9000,
    116.4200 39.9200,
    116.4000 39.9200,
    116.4000 39.9000
))');

-- 查找区域内的餐厅
SELECT * FROM restaurants
WHERE ST_Contains(@rect, location);

3️⃣ 计算两点之间的距离

-- 计算两个餐厅之间的距离
SELECT 
    ST_Distance_Sphere(
        (SELECT location FROM restaurants WHERE id = 1),
        (SELECT location FROM restaurants WHERE id = 2)
    ) / 1000 AS distance_km;

2.5 常用空间函数速查表 📋

函数	说明	示例
ST_GeomFromText()	从文本创建几何对象	ST_GeomFromText('POINT(116.4 39.9)')
ST_Distance_Sphere()	计算球面距离（米）	ST_Distance_Sphere(point1, point2)
ST_Contains()	判断是否包含	ST_Contains(polygon, point)
ST_Within()	判断是否在内部	ST_Within(point, polygon)
ST_X() / ST_Y()	获取经度/纬度	ST_X(location), ST_Y(location)
ST_Buffer()	创建缓冲区	ST_Buffer(point, distance)

2.6 空间索引 vs Redis GEO

很多同学会问：为什么不用Redis的GEO功能？ 🤔

Redis GEO的优势：

✅ 查询速度极快（内存操作）
✅ 支持半径查询
✅ 支持按距离排序
✅ 简单易用

Redis GEO的劣势：

❌ 数据量受内存限制
❌ 持久化可能丢失数据
❌ 不支持复杂的空间查询（如多边形）

MySQL空间索引的优势：

✅ 支持复杂的空间查询
✅ 数据持久化可靠
✅ 适合大数据量
✅ 支持事务

MySQL空间索引的劣势：

❌ 查询速度相对较慢
❌ 占用磁盘空间较大

🎯 选择建议：

场景	推荐方案
高频查询，数据量小（百万级以下）	Redis GEO
数据量大，查询不太频繁	MySQL空间索引
复杂空间查询（多边形、路径）	MySQL空间索引 + PostGIS
最佳实践	Redis做缓存 + MySQL做持久化

---

第三部分：ElasticSearch vs MySQL全文索引 🥊

3.1 什么时候该"毕业"到ElasticSearch？

当你的MySQL全文索引出现以下问题时，就该考虑ES了：

问题	阈值	ES的解决方案
数据量太大	> 1000万条	分布式存储
查询响应慢	> 1秒	倒排索引优化
需要中文分词	必须	IK分词器
需要高亮显示	必须	内置支持
需要聚合分析	必须	强大的聚合功能
需要评分排序	必须	TF-IDF、BM25算法

3.2 MySQL全文索引 vs ElasticSearch

MySQL全文索引：🏠 小区的公告栏
- 简单直接
- 适合小规模
- 维护成本低
- 功能有限

ElasticSearch：🏢 城市级搜索引擎
- 功能强大
- 适合大规模
- 维护成本高
- 需要专业技能

---

第四部分：实战案例 💼

案例1：博客文章搜索系统

-- 建表
CREATE TABLE blog_posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(50),
    created_at DATETIME,
    FULLTEXT INDEX ft_search (title, content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO blog_posts (title, content, author, created_at) VALUES
('MySQL性能优化技巧', 'MySQL是最流行的开源数据库...', '张三', NOW()),
('深入理解Redis', 'Redis是高性能的内存数据库...', '李四', NOW()),
('Java并发编程实战', 'Java多线程是面试的重点...', '王五', NOW());

-- 搜索文章
SELECT 
    id,
    title,
    author,
    MATCH(title, content) AGAINST('MySQL数据库') AS relevance
FROM blog_posts
WHERE MATCH(title, content) AGAINST('MySQL数据库')
ORDER BY relevance DESC
LIMIT 10;

案例2：外卖平台附近餐厅查询

-- 建表
CREATE TABLE merchants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    address VARCHAR(200),
    location POINT NOT NULL,
    avg_rating DECIMAL(2,1),
    delivery_time INT COMMENT '配送时间（分钟）',
    SPATIAL INDEX idx_location (location)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO merchants (name, address, location, avg_rating, delivery_time) VALUES
('肯德基(王府井店)', '北京市东城区王府井大街', 
 ST_GeomFromText('POINT(116.4074 39.9169)'), 4.5, 30),
('麦当劳(天安门店)', '北京市东城区前门大街', 
 ST_GeomFromText('POINT(116.3975 39.9050)'), 4.3, 25);

-- 查询：用户在(116.4074, 39.9042)，查找3公里内的餐厅，按距离排序
SELECT 
    id,
    name,
    avg_rating,
    delivery_time,
    ROUND(ST_Distance_Sphere(
        location,
        ST_GeomFromText('POINT(116.4074 39.9042)')
    ) / 1000, 2) AS distance_km
FROM merchants
WHERE ST_Distance_Sphere(
    location,
    ST_GeomFromText('POINT(116.4074 39.9042)')
) <= 3000
ORDER BY distance_km
LIMIT 20;

---

第五部分：性能优化建议 ⚡

全文索引优化：

1. 合理设置分词大小

-- 调整ngram token大小
SET GLOBAL innodb_ft_min_token_size = 2;
SET GLOBAL ngram_token_size = 2;
-- 修改后需要重建索引

2. 使用覆盖索引

-- 只查询必要的字段
SELECT id, title FROM articles 
WHERE MATCH(title) AGAINST('MySQL');

3. 分表策略

• 按时间分表：热数据和冷数据分离
• 历史数据归档

空间索引优化：

1. 使用合适的空间参考系统（SRID）

-- 使用WGS84坐标系（GPS使用的标准）
CREATE TABLE locations (
    id INT PRIMARY KEY,
    position POINT SRID 4326 NOT NULL,
    SPATIAL INDEX idx_position (position)
);

2. 预先过滤

-- 先用矩形范围预过滤，再精确计算距离
SELECT * FROM (
    SELECT *, 
           ST_Distance_Sphere(location, @point) AS dist
    FROM merchants
    WHERE MBRContains(
        ST_Buffer(@point, 0.03),  -- 大约3公里的矩形
        location
    )
) t
WHERE dist <= 3000
ORDER BY dist;

---

🎓 总结：选择指南

全文索引选择流程图：

数据量 < 100万 && 查询不频繁
    ↓
使用MySQL FULLTEXT

数据量 > 100万 || 需要复杂搜索
    ↓
使用ElasticSearch

需要实时性 && 数据量适中
    ↓
MySQL + ES双写

空间索引选择流程图：

查询频率高 && 数据量 < 100万
    ↓
Redis GEO（做缓存）

数据量大 || 需要复杂查询
    ↓
MySQL空间索引（做持久化）

专业GIS需求
    ↓
PostGIS（PostgreSQL扩展）

---

🎯 面试要点提炼

1. 全文索引的三种模式：自然语言、布尔、查询扩展
2. 中文分词问题：ngram分词器
3. 全文索引局限性：数据量大时性能下降，推荐ES
4. 空间索引应用场景：LBS、地图、O2O
5. 常用空间函数：ST_Distance_Sphere、ST_Contains
6. MySQL vs Redis GEO：各自的优劣和选择依据

---

📚 参考资料

• MySQL官方文档：Full-Text Search Functions
• MySQL官方文档：Spatial Data Types
• 《高性能MySQL》第5章：索引
• 《MySQL技术内幕：InnoDB存储引擎》

---

最后的最后： 记住，没有银弹！🎯 选择技术方案要根据实际业务场景、数据量、查询频率、团队技术栈等因素综合考虑。不要为了用技术而用技术，实用才是王道！

加油，打工人！💪 下一题见~