MySQL全文检索性能测试及问题总结
阅读使人充实，讨论使人敏捷，写作使人精确。
关于MySQL全文语法解析器
ngram是MySQL支持中文，日语和朝鲜语（CJK）的全文语法分析器，支持InnoDB和MyISAM引擎。关于ngram，这里不做过多介绍，详细可参考官档：
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html
最近发现线上环境有如下模糊查询场景，因此翻了下MySQL在全文检索的特性，顺便就测试发现的问题记录一下。总体来说，目前MySQL实现全文检索还不太友好。
全文检索相关参数配置
l ngram分词单位，考虑到中文通常2个词以上，这里将参数设置为2：:
MySQL [testdb]> show variables like '%ngram_token_size%';+------------------+-------+| Variable_name | Value |+------------------+-------+| ngram_token_size | 2 |+------------------+-------+1 row in set (0.01 sec)MySQL [testdb]> (0.01 sec)
l 其他fulltext相关参数配置，按需配置即可。
MySQL [testdb]> show variables like '%ft%';+---------------------------------+----------------+| Variable_name | Value |+---------------------------------+----------------+| ft_boolean_syntax | + -><()~*:""&| || ft_max_word_len | 84 || ft_min_word_len | 2 || ft_query_expansion_limit | 20 || ft_stopword_file | (built-in) || innodb_ft_aux_table | || innodb_ft_cache_size | 8000000 || innodb_ft_enable_diag_print | OFF || innodb_ft_enable_stopword | ON || innodb_ft_max_token_size | 84 || innodb_ft_min_token_size | 3 || innodb_ft_num_word_optimize | 2000 || innodb_ft_result_cache_limit | 2000000000 || innodb_ft_server_stopword_table | || innodb_ft_sort_pll_degree | 2 || innodb_ft_total_cache_size | 640000000 || innodb_ft_user_stopword_table | |+---------------------------------+----------------+17 rows in set (0.01 sec)MySQL [testdb]>
测试数据准备
测试表800w左右，文本字段中英文混合
MySQL [test]> set names utf8;Query OK, 0 rows affected (0.00 sec)MySQL [test]> select id,name from tab_test limit 3;+------+--------------------------------------+| id | name |+------+--------------------------------------+| 7306 | [N][网易云音乐] || 7307 | [N][全民K歌] || 7308 | [N]客户端接入][登陆心跳] |+------+--------------------------------------+3 rows in set (0.00 sec)MySQL [test]>mysql> select count(*) from tab_test;+----------+| count(*) |+----------+| 8814854 |+----------+1 row in set (4.81 sec)mysql>
插入特定目标记录，便于测试搜索性能：
insert into test.tab_test(id,name) values(666666,'全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子');如下总共插入6行目标数据
MySQL [test]> select id,name from tab_test_txt where id =666666;+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 666666 | MySQL? || 666666 | ? || 666666 | ? || 666666 | 全文检索 || 666666 | 全文检索世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 || 666666 | 全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 |+--------+-------------------------------------------------------------------------------------------------------+6 rows in set (0.00 sec)MySQL [test]>
指定WITH PARSER ngram，创建使用NGRAM解析器的FULLTEXT索引：
alter table tab_test add fulltext index idx_fulltext(name) with parser ngram; 问题发现1：Bug。
当结果集超出innodb_ft_result_cache_limit参数限制，会触发Bug #93168：
1.如下查询“上海”的记录较少，执行正常：
SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('安全' IN BOOLEAN MODE);+--------+--------------+| id | name |+--------+--------------+| 10788 | [安全打击两地_上海][逻辑SPP] |。。。。。。。| 10860 | [DEVOPS预发布_上海][安全中心web] |+-------+------------------------------------------------------+32 rows in set (0.85 sec)
2.如下查询“深圳”的结果集较大，执行异常：需调高相关内存参数配置。
mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('深圳' IN BOOLEAN MODE);ERROR 188 (HY000): FTS query exceeds result cache limitmysql> show variables like '%innodb_ft_result_cache_limit%';+------------------------------+------------+| Variable_name | Value |+------------------------------+------------+| innodb_ft_result_cache_limit | 2000000000 |+------------------------------+------------+1 row in set (0.00 sec)
问题发现2：输入目标文本越长，性能越低
1.只使用4个中文字符搜索：性能较高
mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('全文检索' IN BOOLEAN MODE);+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 666666 | 全文检索世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 || 666666 | 全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 || 666666 | 全文检索 |+--------+-------------------------------------------------------------------------------------------------------+3 rows in set (0.03 sec)
2.使用长字符搜索，性能下降比较严重
mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试' IN BOOLEAN MODE);+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 666666 | 全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 |+--------+-------------------------------------------------------------------------------------------------------+1 row in set (2.27 sec)mysql>
性能问题3：记录数和性能问题
符合条件记录数越多，性能越低，甚至比like模糊查询还差。
1.只有一行记录符合查询条件：
mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试' IN BOOLEAN MODE);+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 666666 | 全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 |+--------+-------------------------------------------------------------------------------------------------------+1 row in set (2.19 sec)
2.较多记录符合查询条件，如下在800w左右数据量中有5w多行符合搜索条件：
mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('测试环境' IN BOOLEAN MODE); ---在800w中有5w多行符合，耗时39.57秒+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 11291 | [TDSQL学习测试环境_上海][其它] || 11632 | [体验测试环境分配_深圳][离线] |。。。。。。。。。。。| 11291 | [TDSQL学习测试环境_上海][其它] || 12615 | [管理平台测试环境_深圳][WEB] |+-------+-----------------------------------------------+50176 rows in set (39.57 sec)mysql>
问题发现4：两种检索模式性能及结果偏差
MySQL全文检索模式主要有两种：
1）、自然语言模式(NATURAL LANGUAGE MODE) ，
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
2）、BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
1.按布尔全文搜索模式查询
SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('全文检索' IN BOOLEAN MODE);mysql> SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('全文检索' IN BOOLEAN MODE);+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 666666 | 全文检索世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 || 666666 | 全文检索需求场景世界上最流行的开源数据库产品MYSQL全文检索性能测试例子 || 666666 | 全文检索 |+--------+-------------------------------------------------------------------------------------------------------+3 rows in set (0.04 sec)mysql>
2.按自然语言搜索模式查询
SELECT id,name FROM tab_test WHERE MATCH (name) AGAINST ('全文检索' IN NATURAL LANGUAGE MODE);+--------+-------------------------------------------------------------------------------------------------------+| id | name |+--------+-------------------------------------------------------------------------------------------------------+| 12192 | [推荐向量检索searcher_深圳][逻辑SPP] || 12412 | [检索系统_深圳][逻辑SPP] |。。。。。。。| 12565 | [长音频向量检索_深圳][逻辑SPP] |+--------+-------------------------------------------------------------------------------------------------------+37635 rows in set (0.34 sec)mysql>
参考资料
1.https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
2.https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html
往期推荐
1.MySQL-
2.
------让学习成为一种习惯-Aken