【MySQL深入详解】第19篇：高级索引技巧——全文索引与索引合并

开篇引入

基础的等值查询和范围查询索引大家都会，真正的高手掌握的是全文索引、索引合并、表达式索引这些高级技巧。

《高性能MySQL》第7章虽然没详细讲，但MySQL 8.0还支持很多高级索引特性，这篇文章帮你扩展视野。

全文索引

什么是全文索引

-- 普通索引：精确匹配
SELECT * FROM articles WHERE title = 'MySQL优化';

-- 全文索引：模糊匹配关键词
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL 优化 技巧');

创建全文索引

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- 或者后添加
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

全文搜索语法

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

-- 布尔模式：更灵活
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -database +优化' IN BOOLEAN MODE);

-- 扩展模式
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL' WITH QUERY EXPANSION);

布尔模式操作符

-- + 必须包含该词
-- - 必须不包含该词
-- > 提高该词权重
-- < 降低该词权重
-- * 通配符（词的前缀）
-- "" 短语匹配

-- 示例：
-- '+MySQL +优化'：同时包含MySQL和优化
-- '+MySQL optimization'：包含MySQL或optimization（至少一个）
-- '+MySQL -database'：包含MySQL但不包含database
-- 'MySQL*'：以MySQL开头的词
-- '"MySQL优化"'：精确匹配短语

全文索引配置

-- 最小词长度
SHOW VARIABLES LIKE 'ft_min_word_len';
-- 默认4个字符

-- 停用词列表
SHOW VARIABLES LIKE 'ft_stopword_file';
-- 默认使用内置停用词列表

-- 自定义停用词（创建文件）
-- 编辑 stopwords.txt，每行一个词
SET GLOBAL ft_stopword_file = '/path/to/stopwords.txt';

全文索引性能

-- 全文索引适合的场景：
-- 1. 大文本字段（几百字符以上）
-- 2. 需要搜索多个词
-- 3. 相关性排序

-- 不适合的场景：
-- 1. 搜索词太短（<3字符）
-- 2. 搜索结果要求精确
-- 3. 需要多语言支持

-- 配合普通索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    category VARCHAR(50),
    
    FULLTEXT INDEX ft_search (name, description),
    INDEX idx_category (category)
);

-- 先用分类索引过滤，再用全文搜索
SELECT * FROM products 
WHERE category = '电子产品'
  AND MATCH(name, description) AGAINST('+手机 -三星' IN BOOLEAN MODE);

空间索引（R-Tree）

地理空间数据

-- MySQL支持空间数据类型和空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    coordinate POINT NOT NULL SRID 4326,
    SPATIAL INDEX (coordinate)
);

空间查询

-- 查找某点附近的位置
SELECT name, ST_Distance_Sphere(
    coordinate, 
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326)
) AS distance_meters
FROM locations
ORDER BY distance_meters;

-- 范围查询（某个矩形内）
SELECT * FROM locations
WHERE MBRContains(
    ST_GeomFromText('POLYGON((...))'),
    coordinate
);

表达式索引（函数索引）

MySQL 8.0+

-- 在表达式上建索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    created_at DATETIME,
    
    -- 在小写email上建索引
    INDEX idx_email_lower ((LOWER(email))),
    
    -- 在日期的月份上建索引
    INDEX idx_created_month ((DATE_FORMAT(created_at, '%Y-%m')))
);

表达式索引的优势

-- 查询可以直接使用索引
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- 使用 idx_email_lower 索引

SELECT * FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') = '2024-01';
-- 使用 idx_created_month 索引

-- 比普通写法快很多
-- 原来：LOWER(email)函数导致索引失效
-- 现在：表达式索引让查询直接走索引

多值索引（MySQL 8.0.17+）

JSON数组索引

CREATE TABLE customers (
    id INT PRIMARY KEY,
    tags JSON
);

-- 创建多值索引（JSON数组中的每个值）
ALTER TABLE customers 
ADD INDEX idx_tags ((CAST(tags->'$.[*]' AS UNSIGNED ARRAY)));

-- 查找包含某个标签的客户
SELECT * FROM customers 
WHERE JSON_CONTAINS(tags, '100');

索引合并策略

Index Merge是什么

-- 当查询条件涉及多个列，每个列有独立索引
SELECT * FROM orders 
WHERE user_id = 1 OR status = 'paid';

-- MySQL可以合并多个索引的结果
-- EXPLAIN显示：Using index merge

Index Merge的类型

-- 1. intersection（交集）
SELECT * FROM orders 
WHERE user_id = 1 AND status = 'paid';
-- 两个索引都命中的行

-- 2. union（并集）
SELECT * FROM orders 
WHERE user_id = 1 OR status = 'paid';
-- 两个索引任意一个命中的行

-- 3. sort_union（排序并集）
SELECT * FROM orders 
WHERE user_id < 10 OR user_id > 100;
-- 先按主键排序，再合并

Index Merge的问题

-- Index Merge有时反而更慢
-- 因为需要合并多个索引的结果集

-- 可以禁用
SET optimizer_switch = 'index_merge=off';

-- 或者用IGNORE INDEX
SELECT * FROM orders IGNORE INDEX (idx_user, idx_status)
WHERE user_id = 1 OR status = 'paid';

更好的方案：联合索引

-- 用OR连接 → 考虑联合索引
SELECT * FROM orders 
WHERE user_id = 1 OR status = 'paid';

-- 如果两个条件都频繁使用
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 如果查询经常OR，建议重写为UNION
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE status = 'paid' AND user_id != 1;

复合条件的索引设计

OR条件的处理

-- 原始查询（OR）
SELECT * FROM orders WHERE user_id = 1 OR status = 'paid';

-- 方案1：联合索引 + UNION
CREATE INDEX idx_user_status ON orders(user_id, status);
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE status = 'paid' AND user_id != 1;

-- 方案2：如果OR不常见，忽略优化
-- MySQL会选择全表扫描或Index Merge

IN条件的索引

-- IN条件可以使用索引
SELECT * FROM orders WHERE user_id IN (1, 2, 3, 4, 5);
-- 走user_id索引，5次索引查找

-- 优化：如果IN的值很多，考虑分批查询
-- 或者用临时表
CREATE TEMPORARY TABLE user_ids (id INT);
INSERT INTO user_ids VALUES (1), (2), (3), (4), (5);
SELECT o.* FROM orders o JOIN user_ids u ON o.user_id = u.id;

范围查询的优化

-- 范围查询 + 精确查询
SELECT * FROM orders 
WHERE user_id IN (1, 2, 3) 
  AND created_at > '2024-01-01';

-- 索引设计
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- user_id精确匹配，created_at范围查询

-- 注意：IN等价于多个OR，索引能处理
SELECT * FROM orders 
WHERE (user_id = 1 OR user_id = 2 OR user_id = 3) 
  AND created_at > '2024-01-01';

前缀匹配与通配符

LIKE的前缀匹配

-- 后缀通配符：可以使用索引
SELECT * FROM users WHERE name LIKE 'Alice%';

-- 前缀通配符：无法使用索引
SELECT * FROM users WHERE name LIKE '%lice';

-- 解决方案1：反转字符串
CREATE INDEX idx_name_reversed ON users((REVERSE(name)));
SELECT * FROM users WHERE REVERSE(name) LIKE REVERSE('%lice');

-- 解决方案2：使用全文索引
SELECT * FROM users WHERE MATCH(name) AGAINST('lice');

前缀索引的变通

-- 存储反转的数据
ALTER TABLE users ADD COLUMN name_reversed VARCHAR(50);
UPDATE users SET name_reversed = REVERSE(name);
CREATE INDEX idx_name_rev ON users(name_reversed);

-- 查询时反转
SELECT * FROM users WHERE name_reversed = REVERSE('lice');

索引 Hint

强制使用索引

-- FORCE INDEX（比USE INDEX更强）
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 1;

-- 场景：优化器选错索引时使用
-- 通常是因为统计信息不准

忽略索引

-- 忽略特定索引
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE user_id = 1;

Index Hint的合理使用

-- 不要滥用
-- 99%的情况下，优化器的选择是对的

-- 合理场景：
-- 1. 统计信息过时，ANALYZE TABLE也无法修复
-- 2. 临时跳过有问题的索引
-- 3. 测试对比不同索引的效果

-- 示例：对比索引效果
SELECT SQL_NO_CACHE * FROM orders USE INDEX (idx_user_id);
SELECT SQL_NO_CACHE * FROM orders USE INDEX (idx_status);

小结

1. 全文索引：适合大文本的关键词搜索，配合布尔模式使用
2. 表达式索引：MySQL 8.0+支持，在函数结果上建索引
3. 空间索引：R-Tree结构，适合地理空间查询
4. 多值索引：JSON数组的每个值都可以建索引
5. Index Merge：MySQL自动合并多个索引，但不一定最优
6. 联合索引优于Index Merge：设计好列顺序，一个索引顶多个
7. UNION重写OR查询：有时比OR效率更高
8. 前缀索引变通：反转字符串+全文索引解决前导通配符问题

高级索引技巧是MySQL优化的进阶内容，灵活运用能让查询效率再上一个台阶。

---

延伸阅读

• 《高性能MySQL》第7章 创建高性能的索引
• MySQL 8.0 Reference Manual: Full-Text Search Functions
• MySQL 8.0 Reference Manual: Spatial Data Types
• MySQL 8.0 Reference Manual: Expression Indexes