摘要：深夜的报警短信突然亮起："商品搜索接口响应时间突破5秒！"查看日志，满屏的LIKE '%爆款%'查询正在疯狂吞噬CPU资源。这熟悉的场景是否也曾让您辗转难眠？作为数据库工程师，我们深知模糊查询是业务刚需，但当数据量突破百万级时，LIKE操作就像在高速公路上骑自
深夜的报警短信突然亮起："商品搜索接口响应时间突破5秒！"查看日志，满屏的LIKE '%爆款%'查询正在疯狂吞噬CPU资源。这熟悉的场景是否也曾让您辗转难眠？作为数据库工程师，我们深知模糊查询是业务刚需，但当数据量突破百万级时，LIKE操作就像在高速公路上骑自行车——费力又低效。
今天给大家分享MySQL全文索引的魔法，让模糊查询性能提升100倍不再是难题！
全文索引（Full-Text Index）是一种特殊类型的索引，允许在文本数据中进行复杂的查询操作。它主要用于加速对大文本字段的搜索速度，特别适合处理包含大量文本的数据列，如文章、评论等。
优点:
提高搜索效率: 对于大型文本字段的搜索，全文索引可以显著加快查询速度。
自然语言搜索: 支持基于自然语言的搜索模式，能够根据文档的相关性返回结果。
布尔模式: 提供布尔模式搜索，允许通过特定的操作符来精确控制搜索条件。
缺点:
额外的存储需求: 全文索引会占用更多的磁盘空间。
更新成本: 在数据插入、更新或删除时，维护全文索引需要额外的开销。
适用范围有限: 并非所有字符集和排序规则都支持全文索引；此外，某些存储引擎可能不完全支持全文搜索功能。
文本搜索: 当你需要在一个大的文本字段中查找特定的单词或短语时，比如博客文章、新闻报道或产品描述。
搜索引擎: 构建网站内部的搜索引擎或应用内的搜索功能时非常适合。
需要快速响应: 对于那些需要快速返回搜索结果的应用程序来说，使用全文索引可以极大地提高性能。
说明：MySQL 5.6及以上版本对中文支持较好，并且从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
创建测试表结构
CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL COMMENT '商品名称', description TEXT COMMENT '商品描述', price DECIMAL(10,2) COMMENT '价格', category_id INT COMMENT '分类ID', created_at DATETIME DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;数据初始化脚本
DELIMITER $$CREATE PROCEDURE GenerateTestDataBEGIN DECLARE i INT DEFAULT 0; -- 如果你确实需要控制事务，可以在这里开始一个事务 -- START TRANSACTION; WHILE i 0.5 THEN '防水设计' ELSE '超长续航' END, CASE WHEN RAND > 0.3 THEN '支持蓝牙连接' ELSE 'WiFi直连' END, CASE WHEN RAND > 0.7 THEN '爆款推荐' ELSE '新品上市' END ), ROUND(100 + RAND * 2000, 2), FLOOR(1 + RAND * 10) ); SET i = i + 1; -- 这里是否需要手动 COMMIT 取决于你的具体需求以及数据库配置 IF MOD(i, 1000) = 0 THEN COMMIT; END IF; END WHILE; -- 确保所有更改都被提交 COMMIT;END$$DELIMITER ;CALL GenerateTestData;注意：需要准备50W条测试数据，使用该存储过程，大概过程需要几分钟。大家如果本地测试的话请耐心等候数据创建完成。
数据准备效果如下：
通配符的诅咒
前置百分号让like查询的索引彻底失效，百万级数据下查询时间呈指数级增长
语义缺失的困境
无法区分"数码相机"和"相机配件"的相关性，智能搜索成空谈
-- 创建支持中文的全文索引（MySQL 5.7+）ALTER TABLE products ADD FULLTEXT ft_search(name, description)WITH PARSER ngram;代码逻辑：
WITH PARSER ngram 启用中文分词插件，MySQL默认采用空格分词，对中文需使用ngram二元分词ft_search 索引名称需体现业务场景，建议使用ft_+字段名的命名规范联合索引的字段顺序不影响查询性能，但影响存储结构适用场景：
商品描述、文章内容等长文本字段日均搜索量>1000次的高频查询包含中文/日文等无空格分隔的语言-- 基础版：相关性搜索SELECT *, MATCH(name, description) AGAINST('运动相机') AS scoreFROM productsWHERE MATCH(name, description) AGAINST('运动相机')ORDER BY score DESC;-- 进阶版：布尔模式精准控制SELECT * FROM productsWHERE MATCH(name, description) AGAINST('+防水 -手机' IN BOOLEAN MODE);进阶版符号解释：
+ 必须包含（AND逻辑）- 必须排除（NOT逻辑）* 通配符匹配（仅词尾有效）"短语搜索" 精确匹配词组典型场景：
排除干扰词：-山寨 过滤劣质商品强制精确匹配：+"真无线" 保证核心特征组合搜索：+运动 +相机 -手机 精准定位运动相机实际开发当中选择业务中那个最"痛"的LIKE查询，尝试用全文索引改造它。大家是否遇到过全文索引的"奇葩"问题？欢迎留言讨论，让我们共同攻克性能难关！
来源：免费高清壁纸大全
