MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
如题，业务上需要全文搜索，数据大约有两百万条，每次需要查询并统计条数，发现这样花的时间是两倍 ，第一条 SQL 花了两秒，COUNT 又花了两秒，这样效率太低了，请教如何优化？
因为项目原因，不让用 Redis ，是不是最好的方案就是再弄一张表单独记录 COUNT ，然后定期更新数据？
SELECT
*
FROM
bookclist
WHERE
btitle LIKE '%社会主义%'
SELECT
COUNT(*)
FROM
bookclist
WHERE
btitle LIKE '%社会主义%'
还有就是全文搜索准备用全文索引，然后用 MATCH AGAINST 查询，但是就是发现在查三个字的人名时速度还比不上 LIKE 查询，不知道各位 v 友知道怎么解决吗？
 |
1
Jooooooooo May 9, 2022
不小的数据量, 搜索匹配, 不是 mysql 的强项, 搞个 es 吧.
|
 |
2
ration May 9, 2022 via Android
第一条是需要分页吧。。只能全文索引了，单独记录 count 的话也没用，搜索条件是不确定的。不上 es 的话，试一下 mysql 的全文索引，缓存占用比较大才能发挥优势。
|
 |
3
akira May 9, 2022
不是 COUNT(*) 的问题，是全文检索的问题
|
 |
4
lanlanye May 9, 2022 via iPhone
1. 分成两个接口，这样相同条件只需要 count 1 次
2. 如果全文检索本身无法利用索引优化，可以用 OVER() 一次获取到结果和总数，少查一次 |
 |
5
xiaopanzi May 9, 2022
Never use COUNT(*) in production mode.
|
 |
7
Juszoe May 9, 2022  2
如果你的业务和搜索引擎一样只需要近似值，如“约 100000 条结果”，可以用 explain 命令
|
 |
8
Euthpic May 9, 2022
全文搜索用 ES 是最好的,用 MySQL 的 ngram 也行
|
 |
10
Sasasu May 9, 2022 1
过会你就来问深翻页怎么办了
|
 |
11
yesterdaysun May 10, 2022
如果总条数不是特别重要, 可以考虑只精确查询特定数量以下的条数, 比如 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 May 10, 2022 2
`select found_rows();`
https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count |
|
13
sutra May 10, 2022
上面的 link 有点错。
后面的 anchor 改成 #function_row-count SELECT SQL_CALC_FOUND_ROWS * FROM bookclist WHERE btitle LIKE '%社会主义%' select found_rows(); |
|
14
sutra May 10, 2022
我怎么老是贴错，anchor 应该是 #function_found-rows
|
 |
18
encro May 10, 2022
@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 May 10, 2022
@anxn 你是说 select count(*) from blockclist; (注意不带任何 where 语句）很快是吧？这个 mysql 优化过，相比 postgresql 。
|
|
22
anxn OP @sutra 不是，是带上 WHERE MATCH ( btitle ) AGAINST 子句的，就是 MATCH ( btitle ) AGAINST 的记录数
|
 |
23
mingl0280 May 10, 2022
第一个需求：不让你用 redis 你还不能自己写个缓存嘛……
第二个需求没搞过，不知道。 |
 |
24
zmal May 10, 2022
SQL 层面没什么好优化的。
1. 审视全文检索的需求到底是否必要，业务上是否可以规避，比如只允许查询预先缓存的 keyword 。 2. 用 mysql 的全文索引肯定没问题啊，你说的某个人名 5000 多条慢 90%是 IO 原因。 |
|
25
encro May 10, 2022
没办法，记录集中确实慢。
ngram_token_size=1 试一下，中文简单的话都分字性能好像还会高一点 你 explain 的时候要看慢在哪里。 |
|
26
encro May 10, 2022
理解 explain 每一列的意思，看没一列的输出，然后就知道有没有优化空间了。
|
Select Language