MySQL的fulltext索引为什么比like慢这么多?

表字段:id,content(text类型),content字段已添加fulltext索引,10万条数据,内容是用navicat生成的随机文本。
查询SQL如下:

select * from tb_text_100w where content like "%simply double-click it in the pane%";
select * from tb_text_100w as a where match(a.content) against('"simply double-click it in the pane"');

第一条SQL:耗时13秒
第二条SQL:耗时189秒

我搞不懂为啥差距这么大,那fulltext索引的意义在哪里?
补充1:SQL执行计划

补充2:

阅读 4.3k
2 个回答

会不会是机器配置有点问题,在本地测试了一下,感觉速度提升还是非常多的。

# 创建了两个测试表
CREATE TABLE `test_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `msg` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_msg` (`msg`)
) ENGINE=InnoDB AUTO_INCREMENT=582145 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `test_table2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `msg` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=582145 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# 网上下了几本免费的英文电子书,写入到了数据库里面,两个表数据一样,都是1163200条数据。
# 测试数据是这里下的:https://www.gutenberg.org/  ,txt格式的电子书,一行数据对应数据表里面的一条msg记录。
select count(*) from test_table;
select count(*) from test_table2;

# 这两个 like 查询时间基本在 800ms 左右。是扫描了全表,查询的时候cpu占用很高,基本接近100%。
select * from test_table where msg like '%corruption of Kotzebue%'; 大概 850ms 左右
select * from test_table2 where msg like '%corruption of Kotzebue%'; 大概 750ms 左右


select * from test_table where id = 348332;

explain 
SELECT *
FROM test_table
WHERE MATCH (msg) AGAINST ('"corruption of Kotzebue"' IN BOOLEAN MODE);


# 使用fulltext索引查询,查询时间能降低到 10ms 以下
SELECT *
FROM test_table
WHERE MATCH (msg) AGAINST ('"corruption of Kotzebue"');

like在这里是全表扫描匹配但不至于有多慢。
而fulltext来弄索引的话,因为空格的关系,不一定按照自然语言的顺序来查找,理论上来说,against('"simply double-click it in the pane"') 是能搜出来 "double-click simply pane in the it" 这种乱序的句子的。根据你的需要,你可以试试指定自然语言查找模式

select * 
from tb_text_100w as a 
where match(a.content) 
against('"simply double-click it in the pane"' IN NATURAL LANGUAGE MODE);

希望可以帮助到你。

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题