mysql limit两个参数_MySQL全文检索性能测试及问题总结

最新推荐文章于 2023-05-27 09:32:09 发布
最新推荐文章于 2023-05-27 09:32:09 发布
阅读量459 收藏
点赞数
文章标签： mysql limit两个参数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_39871378/article/details/111691587
版权
本文探讨了MySQL的全文检索性能，特别是在使用ngram语法解析器时遇到的问题。测试发现，当结果集超过innodb_ft_result_cache_limit时会出现Bug，且输入目标文本越长，性能越低。此外，符合条件的记录数越多，查询性能下降明显。MySQL全文检索有两种模式，自然语言模式和BOOLEAN模式，各有优缺点。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

阅读使人充实，讨论使人敏捷，写作使人精确。

a7dfd7bc7399f926f49909b043366a03.png 关于MySQL全文语法解析器

ngram是MySQL支持中文，日语和朝鲜语(CJK)的全文语法分析器，支持InnoDB和MyISAM引擎。关于ngram，这里不做过多介绍，详细可参考官档：

https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html

最近发现线上环境有如下模糊查询场景，因此翻了下MySQL在全文检索的特性，顺便就测试发现的问题记录一下。总体来说，目前MySQL实现全文检索还不太友好。

a7dfd7bc7399f926f49909b043366a03.png 全文检索相关参数配置

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]>

a7dfd7bc7399f926f49909b043366a03.png 测试数据准备

测试表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;

a7dfd7bc7399f926f49909b043366a03.png 问题发现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)

a7dfd7bc7399f926f49909b043366a03.png 问题发现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>

a7dfd7bc7399f926f49909b043366a03.png 性能问题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>

a7dfd7bc7399f926f49909b043366a03.png 问题发现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-用最简单实用的方法将SQL性能提升100倍

2.MySQL日常运营重要事项存在问题总结

                         ------让学习成为一种习惯-Aken

MySQL高频基本面试问题整理
张彦峰的博客
04-07 174万+
总结大厂MySQL高频基本面试问题整理，用于快速查缺补漏
mysql分页的limit参数简单示例
01-21
Mysql的分页的两个参数 select * from user limit 1,2 1表示从第几条数据开始查(默认索引是0,如果写1,从第二条开始查) 2,表示这页显示几条数据 到此这篇关于mysql分页的limit参数的文章就介绍到这了,更多相关mysql分页limit参数内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！ 您可能感兴趣的文章:详解MySQL的limit用法和分页查询语句的性能分析MYSQL分页limit速度太慢的优化方法mysql limit分页优化方法分享Mysql limit
Mysql中limit的用法详解
liuliufa的专栏
03-22 1263
 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offsetLIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个
MySQL的limit参数
netpirate的专栏
12-15 1791
<br />posted on 2007-09-01 02:14 Xu Jianxiang<br /> <br />翻页在各个数据库中一般都有各自的特性参数完成，oracle用range（rownum＋alias），mssql用top，而mysql使用了limit。<br /><br />limit参数在数据小时效率非常高，随着数据量的增大，速度呈线性下降，这时候就可以显示出range的优势了。<br /><br />比如有张表有35w条数据，不算大，一次query需要不少的内存，如果要分批/翻页查询的话，
mysql limit两个参数_产品操作MySQL第6篇 – 数据过滤-LIMIT子句
weixin_39633089的博客
12-17 2480
MYSQL本资料为产品岗位作为日常工作参考，语言口语化At 2019/4/26 By David.Yang介绍下重量级子句LIMIT我们在逛马蜂窝的时候，多少会看看游记攻略什么的，你会发现马蜂窝一页给你10条左右的游记内容，你要继续看的话点分页，会给你下20条数据。想象如果没有分页呢？已下载给你百万千万篇游记，你会不会疯了？你不疯马蜂窝的数据库也得疯。那一页一页的数据返回固定的游记数量，是怎么实现...
Java面试相关文章及Github学习资料
m0_57081368的博客
06-26 290
前言 人人都想进大厂，当然我也不例外。早在春招的时候我就有向某某某大厂投岗了不少简历，可惜了，疫情期间都是远程面试，加上那时自身也有问题，导致屡投屡败。突然也意识到自己肚子里没啥货，问个啥都是卡卡卡卡，后期下定决心好好弥补我的知识与智商，天天扎在书堆里，再加上实操，自我感觉还是不错的，有进步。 尤其是这我啃了足足58天的[Java进阶架构核心知识集]（文末有介绍，可分享），还是当初朋友面试进大厂后分享给我的。摸熟里边近30个分类的Java知识后，7月下旬鼓足勇气向抖音后端进击，123面（视频面）下来就像开挂
mysql limit两个参数的四种用法
02-27
在MySQL中，`LIMIT`可以接受一个或两个参数，用于限制返回的记录数或指定起始位置。 一、基本语法 `LIMIT`子句的基本语法如下： ```sql LIMIT [offset,] rows ``` - `offset`：指定返回结果集的起始位置，也就是...
详解MySQL的limit用法和分页查询语句的性能分析
09-09
MySQL的`LIMIT`子句是用于限制SQL查询返回结果的数量，它在数据检索中尤其有用，特别是在进行分页查询时。`LIMIT`的基本语法有两种形式： 1. `LIMIT rows`：这种形式将返回最多`rows`数量的记录。 2. `LIMIT offset...
mysql limit查询优化分析
10-30
在MySQL中，LIMIT子句可以接收一个或两个数字参数，这两个参数都必须是整数常量。当提供两个参数时，第一个参数表示返回记录行的起始偏移量，第二个参数指定返回记录行的最大数目。重要的是，初始记录行的偏移量是从...
mysql_proc_page_order.zip_page_proc_page_排序MYSQL
09-21
然而，当数据量非常大时，直接使用`OFFSET`可能会导致性能问题，因为MySQL需要遍历到指定的偏移量。为了优化这个过程，我们可以使用存储过程来实现一种更高效的方法，称为“ROW_NUMBER()”或者“ROW_NUMBER over()”...
c mysql limit 参数_详解Mysql中limit的用法
weixin_35797963的博客
02-15 711
Mysql中limit的用法：在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢?不用担心，mysql已经为我们提供了这样一个功能。希望对大家学习Mysql有所帮助。SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offsetLIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受...
mysql limit 第二个参数_mysql的limit参数
weixin_28947253的博客
01-18 2284
mysql> select * from tuser;+---------+-----------+----------+| user_id | user_name | group_id |+---------+-----------+----------+| 1 | NULL | 1 || 2 | 小任 | 1 || ...
SQL学习笔记——limit用法（limit使用一个参数，limit使用两个参数）
热门推荐
liu_feng_zi_的专栏
06-19 13万+
Product表： limit语法： select <列名>,<列名>,...from <表名> limit <参数值>; select * from product limit 3; +------------+--------------+--------------+------------+----------------+-------------+ | product_id | product_name | product_type
优化 MySQL 查询的 Limit 参数
魔幻之翼的坚持
11-14 876
我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。那么在 MySQL 中有那些方式是可以避免全表扫面的呢？除了我们大家很熟悉的通过使用索引列或分区等方式来进行查询的优化之外还有那些呢？ 前些天看了一个老外写的程序，在 MySQL 查询中使用了很多 Limit 关键字，这就让我很感兴趣了，因为在我印象中， Limit 关键字似
mysql limit优化_程序员，你知道MySQL的Limit有性能问题吗？
weixin_39565390的博客
12-03 181
专注于Java领域优质技术号，欢迎关注作者：RebeccaZhongMySQL的分页查询通常通过limit来实现。MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。为了与PostgreSQL兼容，limit也支持limit # offset #。问题：对于小的偏移量，直...
SQL中limit的用法（一个参数，两个参数），分页查询
weixin_52293201的博客
05-27 5836
一个参数时，参数值为多少就查询表中前多少条数据 两个参数时，因为limit默认下标从0开始，第一个参数是下标，查数据的话是从第一条查，第二个参数限定了几条数据
MySql存储过程中limit不能动态参数问题的解决
hellolongbin[一个人]的专栏
12-28 1万+
先说两句体外话：好久没在优快云的blog写东西了，一个原因是自己最近工作比较忙，最主要的原因还是优快云的blog反映太迟钝，偶尔有想法想记录下来，打开博客主页http://blog.youkuaiyun.com/hellolongbin都打不开，要么进度条在下面缓慢的加载，要么干脆就报 已取消到该网页的导航
使用Limit参数优化MySQL查询
celebration1982的专栏
06-23 427
我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。那么在 MySQL 中有那些方式是可以避免全表扫面的呢？除了我们大家很熟悉的通过使用索引列或分区等方式来进行查询的优化之外还有那些呢? 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。 SE
mysql 动态sql limit_mysql limit后的变量问题，sql语句的prepare
weixin_35785014的博客
01-19 584
如果你的MySQL 版本是 5.0.7 或者更高的，你还可以在 LIMIT 子句中使用它，示例如下：mysql> SET @a=1;mysql>PREPARE STMT FROM "SELECT * FROM tbl LIMIT ?";mysql>EXECUTE STMT USING @a;mysql>SET @skip=1; SET @numrows=5;mysql&gt...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值