MySQL全文索引与空间索引的奇妙世界 🗺️

一、开篇故事：两种特殊的图书馆索引 📚

传统索引：按书名查找

读者："我要《Java编程思想》这本书"
管理员：查索引 → 找到书架位置 → 拿书 ✅

特点：
  → 精确匹配
  → 速度快
  → 适合确定查询

全文索引：按内容搜索

读者："我要找所有讲'设计模式'的书"
管理员（普通索引）："书名没有这个词啊..." ❌

管理员（全文索引）：
  → 搜索所有书的内容
  → 找到包含"设计模式"的章节
  → 列出所有相关的书 ✅

特点：
  → 模糊匹配
  → 搜索内容
  → 适合文本搜索

空间索引：按位置查找

读者："我要找离这里最近的咖啡店"
管理员（普通索引）："你要哪家咖啡店？" ❌

管理员（空间索引）：
  → 计算所有咖啡店的距离
  → 按距离排序
  → 找到最近的 ✅

特点：
  → 地理位置
  → 距离计算
  → 适合LBS应用

---

二、全文索引（FULLTEXT） 📝

2.1 什么是全文索引？

全文索引：用于在大文本字段中搜索关键词，类似搜索引擎。

-- 表结构
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    create_time DATETIME,
    
    -- 创建全文索引
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;

-- 插入数据
INSERT INTO articles (title, content) VALUES
('MySQL教程', 'MySQL是一个关系型数据库管理系统，支持全文索引功能...'),
('Redis教程', 'Redis是一个内存数据库，支持多种数据结构...'),
('ElasticSearch教程', 'ElasticSearch是一个强大的全文搜索引擎...');

-- 全文搜索（自然语言模式）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('全文索引' IN NATURAL LANGUAGE MODE);

-- 返回：包含"全文"或"索引"的文章
-- 结果1: MySQL教程（包含"全文索引"）
-- 结果2: ElasticSearch教程（包含"全文"和"搜索"）

---

2.2 全文索引的三种模式

模式1：自然语言模式（NATURAL LANGUAGE MODE）⭐⭐⭐⭐⭐

-- 最常用的模式
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 数据库' IN NATURAL LANGUAGE MODE);

-- 特点：
--   → 分词搜索（MySQL、数据库分别搜索）
--   → 相关性排序（包含更多关键词的排在前面）
--   → 自动过滤停用词（的、了、是等）
--   → 默认模式（可以省略IN NATURAL LANGUAGE MODE）

-- 相关性得分
SELECT 
    title,
    MATCH(title, content) AGAINST('MySQL 数据库') AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 数据库')
ORDER BY relevance DESC;

-- 结果：
-- title            | relevance
-- -----------------+----------
-- MySQL教程        | 1.5
-- ElasticSearch教程| 0.8

---

模式2：布尔模式（BOOLEAN MODE）⭐⭐⭐⭐

-- 支持布尔操作符
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Redis' IN BOOLEAN MODE);

-- 布尔操作符：
--   +   : 必须包含
--   -   : 不能包含
--   >   : 提高权重
--   <   : 降低权重
--   *   : 通配符
--   ""  : 精确匹配

-- 示例1：必须包含MySQL，不能包含Redis
WHERE MATCH(title, content) AGAINST('+MySQL -Redis' IN BOOLEAN MODE);

-- 示例2：必须包含MySQL，可选包含InnoDB
WHERE MATCH(title, content) AGAINST('+MySQL InnoDB' IN BOOLEAN MODE);

-- 示例3：通配符搜索（data开头的词）
WHERE MATCH(title, content) AGAINST('data*' IN BOOLEAN MODE);
-- 匹配：database、datastore、data等

-- 示例4：精确短语搜索
WHERE MATCH(title, content) AGAINST('"关系型数据库"' IN BOOLEAN MODE);
-- 只匹配完整短语"关系型数据库"

-- 示例5：权重调整
WHERE MATCH(title, content) AGAINST('>MySQL <Redis' IN BOOLEAN MODE);
-- MySQL权重高，Redis权重低

---

模式3：查询扩展模式（WITH QUERY EXPANSION）⭐⭐

-- 自动扩展查询关键词
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);

-- 工作原理：
--   1. 第一次搜索：搜索"MySQL"
--   2. 分析结果：找到相关词（如"数据库"、"InnoDB"）
--   3. 第二次搜索：搜索"MySQL 数据库 InnoDB"
--   4. 返回扩展结果

-- 优点：
--   ✅ 自动发现相关词
--   ✅ 结果更全面

-- 缺点：
--   ❌ 可能不够精确
--   ❌ 性能开销大（两次搜索）

---

2.3 中文全文索引

问题： MySQL默认的全文索引不支持中文分词

-- 创建全文索引
CREATE FULLTEXT INDEX ft_title ON articles(title);

-- 搜索中文
SELECT * FROM articles 
WHERE MATCH(title) AGAINST('数据库');

-- 问题：
--   → MySQL按空格分词
--   → 中文没有空格
--   → 无法正确分词
--   → 搜索结果不准确 ❌

解决方案： 使用ngram分词器（MySQL 5.7.6+）

-- 创建表时指定ngram分词器
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    
    FULLTEXT INDEX ft_title_content (title, content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 或者添加全文索引
ALTER TABLE articles 
ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram;

-- 配置ngram分词长度（默认2）
-- my.cnf配置文件
[mysqld]
ngram_token_size = 2

-- 搜索中文
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库');
-- ✅ 现在可以正确搜索中文了！

-- 原理：
--   ngram_token_size = 2（二元分词）
--   "数据库" → "数据"、"据库"
--   "关系型数据库" → "关系"、"系型"、"型数"、"数据"、"据库"

---

2.4 全文索引的限制

1. 最小词长度（ft_min_word_len）
   → 默认4（英文字符）
   → 少于4个字符的词不被索引
   → 修改：my.cnf中设置ft_min_word_len=1

2. 停用词（Stopwords）
   → "the"、"a"、"of"等常见词被忽略
   → 中文：的、了、是等
   → 可自定义停用词表

3. 50%阈值
   → 如果某个词出现在超过50%的行中
   → 该词被认为太常见，不被索引
   → 解决：使用BOOLEAN MODE（不受此限制）

4. 支持的字段类型
   ✅ CHAR、VARCHAR、TEXT
   ❌ 不支持BLOB

5. 支持的存储引擎
   ✅ InnoDB（MySQL 5.6+）
   ✅ MyISAM
   ❌ Memory、Archive等

---

2.5 全文索引 vs ElasticSearch

特性	MySQL全文索引	ElasticSearch
分词能力	⭐⭐⭐（基础）	⭐⭐⭐⭐⭐（强大）
中文支持	⭐⭐⭐（ngram）	⭐⭐⭐⭐⭐（ik分词器）
相关性排序	⭐⭐⭐	⭐⭐⭐⭐⭐
性能	⭐⭐⭐（小数据量）	⭐⭐⭐⭐⭐（大数据量）
功能	⭐⭐⭐（基础）	⭐⭐⭐⭐⭐（聚合、高亮等）
运维成本	⭐⭐⭐⭐⭐（低）	⭐⭐⭐（高）
适用场景	小型项目、简单搜索	大型项目、复杂搜索

选择建议：

✅ 使用MySQL全文索引：
   - 数据量小（< 100万）
   - 简单的关键词搜索
   - 不想引入额外组件

✅ 使用ElasticSearch：
   - 数据量大（> 100万）
   - 复杂搜索需求（高亮、聚合、分析）
   - 需要高性能和扩展性

---

三、空间索引（SPATIAL） 🗺️

3.1 什么是空间索引？

空间索引：用于地理位置数据，支持距离计算、范围查询等。

-- 表结构
CREATE TABLE places (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    location POINT NOT NULL,  -- 地理位置（经纬度）
    
    SPATIAL INDEX idx_location (location)
) ENGINE=InnoDB;

-- 插入数据（经度, 纬度）
INSERT INTO places (name, location) VALUES
('天安门',    ST_GeomFromText('POINT(116.397128 39.916527)')),
('故宫',      ST_GeomFromText('POINT(116.397026 39.918058)')),
('天坛',      ST_GeomFromText('POINT(116.407526 39.882699)')),
('颐和园',    ST_GeomFromText('POINT(116.273434 39.999538)'));

-- 查询：找到距离天安门1km内的地点
SET @center = ST_GeomFromText('POINT(116.397128 39.916527)');  -- 天安门坐标
SET @radius = 1000;  -- 1000米

SELECT 
    name,
    ST_Distance_Sphere(location, @center) AS distance
FROM places
WHERE ST_Distance_Sphere(location, @center) <= @radius
ORDER BY distance;

-- 结果：
-- name    | distance (米)
-- --------|-------------
-- 天安门   | 0
-- 故宫     | 170

---

3.2 空间数据类型

-- POINT（点）
POINT(116.397128 39.916527)  -- 经度116.397128，纬度39.916527

-- LINESTRING（线）
LINESTRING(
    116.397128 39.916527,  -- 起点
    116.407526 39.882699   -- 终点
)

-- POLYGON（多边形，如行政区域）
POLYGON((
    116.3 39.9,   -- 点1
    116.4 39.9,   -- 点2
    116.4 40.0,   -- 点3
    116.3 40.0,   -- 点4
    116.3 39.9    -- 回到点1（闭合）
))

-- MULTIPOINT（多点）
MULTIPOINT(
    116.397128 39.916527,
    116.407526 39.882699
)

-- GEOMETRYCOLLECTION（几何集合）
GEOMETRYCOLLECTION(
    POINT(116.397128 39.916527),
    LINESTRING(116.3 39.9, 116.4 39.9)
)

---

3.3 空间函数

创建空间对象

-- 从文本创建
ST_GeomFromText('POINT(116.397128 39.916527)')
ST_PointFromText('POINT(116.397128 39.916527)')
ST_LineStringFromText('LINESTRING(116.3 39.9, 116.4 39.9)')
ST_PolygonFromText('POLYGON((116.3 39.9, 116.4 39.9, 116.4 40.0, 116.3 40.0, 116.3 39.9))')

-- 从WKB（Well-Known Binary）创建
ST_GeomFromWKB(binary_data)

---

距离计算

-- ST_Distance（平面距离，单位：度）
SELECT ST_Distance(
    ST_GeomFromText('POINT(116.397128 39.916527)'),  -- 天安门
    ST_GeomFromText('POINT(116.407526 39.882699)')   -- 天坛
);
-- 返回：约0.04度

-- ST_Distance_Sphere（球面距离，单位：米）✅ 推荐
SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(116.397128 39.916527)'),  -- 天安门
    ST_GeomFromText('POINT(116.407526 39.882699)')   -- 天坛
);
-- 返回：约3800米

-- Haversine公式（手动计算）
SELECT 
    6371000 * 2 * ASIN(SQRT(
        POWER(SIN((RADIANS(39.882699) - RADIANS(39.916527)) / 2), 2) +
        COS(RADIANS(39.916527)) * COS(RADIANS(39.882699)) *
        POWER(SIN((RADIANS(116.407526) - RADIANS(116.397128)) / 2), 2)
    )) AS distance;
-- 6371000：地球半径（米）

---

空间关系判断

-- ST_Contains（包含）
SELECT ST_Contains(
    ST_GeomFromText('POLYGON((116.3 39.9, 116.5 39.9, 116.5 40.1, 116.3 40.1, 116.3 39.9))'),
    ST_GeomFromText('POINT(116.397128 39.916527)')
);
-- 返回：1（天安门在多边形内）

-- ST_Within（在内部）
SELECT ST_Within(
    ST_GeomFromText('POINT(116.397128 39.916527)'),
    ST_GeomFromText('POLYGON((116.3 39.9, 116.5 39.9, 116.5 40.1, 116.3 40.1, 116.3 39.9))')
);
-- 返回：1（天安门在多边形内）

-- ST_Intersects（相交）
SELECT ST_Intersects(geom1, geom2);

-- ST_Equals（相等）
SELECT ST_Equals(geom1, geom2);

---

范围查询（MBR - Minimum Bounding Rectangle）

-- MBRContains（使用空间索引，速度快）✅
SELECT name 
FROM places
WHERE MBRContains(
    ST_GeomFromText('POLYGON((116.3 39.9, 116.5 39.9, 116.5 40.1, 116.3 40.1, 116.3 39.9))'),
    location
);

-- 查询矩形范围内的地点（经度116.3-116.5，纬度39.9-40.1）
-- 使用空间索引，非常快 ✅

---

3.4 实战案例：附近的人

-- 需求：查找附近1km内的人

-- 表结构
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    location POINT NOT NULL,
    update_time DATETIME,
    
    SPATIAL INDEX idx_location (location)
) ENGINE=InnoDB;

-- 插入用户位置
INSERT INTO users (username, location, update_time) VALUES
('Alice', ST_GeomFromText('POINT(116.397128 39.916527)'), NOW()),
('Bob',   ST_GeomFromText('POINT(116.398000 39.917000)'), NOW()),
('Carol', ST_GeomFromText('POINT(116.407526 39.882699)'), NOW());

-- 查询：找到距离当前位置1km内的用户
SET @my_location = ST_GeomFromText('POINT(116.397128 39.916527)');
SET @radius = 1000;  -- 1000米

-- 方案1：精确计算（慢，全表扫描）
SELECT 
    username,
    ST_Distance_Sphere(location, @my_location) AS distance
FROM users
WHERE ST_Distance_Sphere(location, @my_location) <= @radius
ORDER BY distance
LIMIT 20;

-- 方案2：先用MBR过滤，再精确计算（快，使用索引）✅
-- 1度约等于111km
SET @degree = @radius / 111000;  -- 转换为度

SELECT 
    username,
    ST_Distance_Sphere(location, @my_location) AS distance
FROM users
WHERE MBRContains(
    ST_GeomFromText(CONCAT(
        'POLYGON((',
        116.397128 - @degree, ' ', 39.916527 - @degree, ',',
        116.397128 + @degree, ' ', 39.916527 - @degree, ',',
        116.397128 + @degree, ' ', 39.916527 + @degree, ',',
        116.397128 - @degree, ' ', 39.916527 + @degree, ',',
        116.397128 - @degree, ' ', 39.916527 - @degree,
        '))'
    )),
    location
)
AND ST_Distance_Sphere(location, @my_location) <= @radius  -- 精确过滤
ORDER BY distance
LIMIT 20;

---

3.5 空间索引 vs Redis GEO

特性	MySQL空间索引	Redis GEO
性能	⭐⭐⭐	⭐⭐⭐⭐⭐
功能	⭐⭐⭐⭐（多边形、线等）	⭐⭐⭐（只支持点）
数据持久化	⭐⭐⭐⭐⭐	⭐⭐⭐（需要配置）
复杂查询	⭐⭐⭐⭐	⭐⭐
内存占用	⭐⭐⭐⭐	⭐⭐⭐（全内存）
适用场景	复杂地理查询	简单附近查询

选择建议：

✅ 使用MySQL空间索引：
   - 需要持久化
   - 复杂地理查询（多边形、线等）
   - 数据量大（> 1000万）

✅ 使用Redis GEO：
   - 简单的附近查询（点）
   - 需要极高性能
   - 数据量小（< 1000万）
   - 实时位置更新频繁

---

四、面试高频问题 🎤

Q1: MySQL全文索引和LIKE '%keyword%'的区别？

答：

1. 性能：LIKE '%keyword%'无法使用索引，全表扫描；全文索引使用专门的索引结构，快很多
2. 功能：LIKE只能精确匹配；全文索引支持分词、相关性排序、布尔操作等
3. 适用场景：LIKE适合精确短语匹配；全文索引适合关键词搜索

Q2: MySQL全文索引如何支持中文？

答： 使用ngram分词器：

CREATE FULLTEXT INDEX ft_content (content) WITH PARSER ngram;

配置分词长度：

[mysqld]
ngram_token_size = 2

Q3: 空间索引如何计算两点之间的距离？

答： 使用ST_Distance_Sphere函数：

SELECT ST_Distance_Sphere(
    ST_GeomFromText('POINT(lng1 lat1)'),
    ST_GeomFromText('POINT(lng2 lat2)')
);

返回米为单位的距离，基于球面计算（考虑地球曲率）。

Q4: 如何优化附近的人查询性能？

答：

1. 先用MBR过滤（使用空间索引）
2. 再精确计算距离（ST_Distance_Sphere）
3. 限制结果数量（LIMIT）
4. 定期清理过期位置
5. 考虑使用Redis GEO（更快）

Q5: 全文索引适合什么场景？什么时候应该用ElasticSearch？

答： 全文索引适合：

• 数据量小（< 100万）
• 简单关键词搜索
• 不想引入额外组件

ElasticSearch适合：

• 数据量大（> 100万）
• 复杂搜索（高亮、聚合、分面）
• 需要高性能和扩展性

---

五、总结口诀 📝

全文索引很特殊，
关键词搜索能力强。
自然语言最常用，
布尔模式功能全。

中文分词用ngram，
分词长度要设好。
数据量大用ES，
小项目MySQL好。

空间索引地理位，
经纬度查询快。
距离计算Sphere函数，
MBR过滤用索引。

附近的人常见题，
先框后算性能高。
Redis GEO更快速，
场景选择很重要！

---

参考资料 📚

• MySQL官方文档 - Full-Text Search
• MySQL官方文档 - Spatial Data Types
• Redis GEO文档

---

下期预告： 156-Redis的数据结构和底层实现 🔧

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的搜索如光速，地理查询如神算！ 🗺️✨