如题,业务上需要全文搜索,数据大约有两百万条,每次需要查询并统计条数,发现这样花的时间是两倍 ,第一条 SQL 花了两秒,COUNT 又花了两秒,这样效率太低了,请教如何优化?
因为项目原因,不让用 Redis ,是不是最好的方案就是再弄一张表单独记录 COUNT ,然后定期更新数据?
SELECT
*
FROM
bookclist
WHERE
btitle LIKE '%社会主义%'
SELECT
COUNT(*)
FROM
bookclist
WHERE
btitle LIKE '%社会主义%'
还有就是全文搜索准备用全文索引,然后用 MATCH AGAINST 查询,但是就是发现在查三个字的人名时速度还比不上 LIKE 查询,不知道各位 v 友知道怎么解决吗?
1
Jooooooooo 2022-05-09 22:35:13 +08:00
不小的数据量, 搜索匹配, 不是 mysql 的强项, 搞个 es 吧.
|
2
ration 2022-05-09 22:50:10 +08:00 via Android
第一条是需要分页吧。。只能全文索引了,单独记录 count 的话也没用,搜索条件是不确定的。不上 es 的话,试一下 mysql 的全文索引,缓存占用比较大才能发挥优势。
|
3
akira 2022-05-09 22:58:33 +08:00
不是 COUNT(*) 的问题,是全文检索的问题
|
4
lanlanye 2022-05-09 23:09:12 +08:00 via iPhone
1. 分成两个接口,这样相同条件只需要 count 1 次
2. 如果全文检索本身无法利用索引优化,可以用 OVER() 一次获取到结果和总数,少查一次 |
5
xiaopanzi 2022-05-09 23:19:27 +08:00
Never use COUNT(*) in production mode.
|
7
Juszoe 2022-05-09 23:22:01 +08:00 2
如果你的业务和搜索引擎一样只需要近似值,如“约 100000 条结果”,可以用 explain 命令
|
8
Euthpic 2022-05-09 23:34:07 +08:00
全文搜索用 ES 是最好的,用 MySQL 的 ngram 也行
|
9
anxn OP @lanlanye 感谢建议,不过第 2 条查了下是 MySQL 8 才支持 OVER()函数,公司项目还是 5.7 版的
再研究研究怎么弄合适吧 |
10
Sasasu 2022-05-09 23:52:37 +08:00 1
过会你就来问深翻页怎么办了
|
11
yesterdaysun 2022-05-10 00:57:37 +08:00
如果总条数不是特别重要, 可以考虑只精确查询特定数量以下的条数, 比如 10000 条, 10000 条之上统一显示 10000 或者提示超过 10000, 之下才精确计算
可以先用 select 1 from t where xxx limit 10000,1 确定是不是超过 1 万条, 超过则 total 直接写 10000 如果没超过再 select count(1) form t where xxx 1 万条可能大了点, 可以换成 1 千条之类的, 应该可以提高一点 count 的速度, 但是效果可能不会很好, 有条件还是上 ES 吧 |
12
sutra 2022-05-10 01:38:42 +08:00 2
`select found_rows();`
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count |
13
sutra 2022-05-10 01:42:16 +08:00
上面的 link 有点错。
后面的 anchor 改成 #function_row-count SELECT SQL_CALC_FOUND_ROWS * FROM bookclist WHERE btitle LIKE '%社会主义%' select found_rows(); |
14
sutra 2022-05-10 01:46:12 +08:00
我怎么老是贴错,anchor 应该是 #function_found-rows
|
18
encro 2022-05-10 13:48:42 +08:00
@sutra
@anxn 看文档意思是 found rows 已经被弃用(因为某些优化无效),建议用 count(*)会更好。 Mysql 应该是无解的,简单来说用全文索引会好过 like 很多。特别对于 tilte 这种,走索引后几百万数据还是挺快的。 Mysql 中文全文索引(含实例 5 分钟上手) https://c4ys.com/archives/2098 |
19
anxn OP @encro 全文索引也研究了,就是发现查 3 个字的人名还是比较慢,比如包含这个人名的记录有 5k 多条(记录不超过 1k 条得话可以达到毫秒级),查询还是得花 3 秒多,不过 COUNT(*)就非常快了,可以达到毫秒级
这是我的配置文件 innodb_ft_min_token_size = 1 innodb_ft_max_token_size = 84 ngram_token_size = 2 SQL 语句: SELECT * FROM bookclist WHERE MATCH ( btitle ) AGAINST ('邓小平' IN NATURAL LANGUAGE MODE ); 查"邓小平"或者"邓小"都是 3 秒多,研究了好久不知道咋优化了,不知道 MySQL 能不能对指定词库做索引,之前只查到了 stopword |
21
sutra 2022-05-10 14:10:43 +08:00
@anxn 你是说 select count(*) from blockclist; (注意不带任何 where 语句)很快是吧?这个 mysql 优化过,相比 postgresql 。
|
22
anxn OP @sutra 不是,是带上 WHERE MATCH ( btitle ) AGAINST 子句的,就是 MATCH ( btitle ) AGAINST 的记录数
|
23
mingl0280 2022-05-10 16:30:52 +08:00
第一个需求:不让你用 redis 你还不能自己写个缓存嘛……
第二个需求没搞过,不知道。 |
24
zmal 2022-05-10 16:54:32 +08:00
SQL 层面没什么好优化的。
1. 审视全文检索的需求到底是否必要,业务上是否可以规避,比如只允许查询预先缓存的 keyword 。 2. 用 mysql 的全文索引肯定没问题啊,你说的某个人名 5000 多条慢 90%是 IO 原因。 |
25
encro 2022-05-10 20:20:34 +08:00
没办法,记录集中确实慢。
ngram_token_size=1 试一下,中文简单的话都分字性能好像还会高一点 你 explain 的时候要看慢在哪里。 |
26
encro 2022-05-10 20:23:12 +08:00
理解 explain 每一列的意思,看没一列的输出,然后就知道有没有优化空间了。
|