SELECT COUNT(*) 查询如何优化？

[anxn]

如题，业务上需要全文搜索，数据大约有两百万条，每次需要查询并统计条数，发现这样花的时间是两倍 ，第一条 SQL 花了两秒，COUNT 又花了两秒，这样效率太低了，请教如何优化？

因为项目原因，不让用 Redis ，是不是最好的方案就是再弄一张表单独记录 COUNT ，然后定期更新数据？

第一条 SQL

SELECT
	* 
FROM
	bookclist 
WHERE
	btitle LIKE '%社会主义%'

第二条 SQL

SELECT
	COUNT(*) 
FROM
	bookclist 
WHERE
	btitle LIKE '%社会主义%'

还有就是全文搜索准备用全文索引，然后用 MATCH AGAINST 查询，但是就是发现在查三个字的人名时速度还比不上 LIKE 查询，不知道各位 v 友知道怎么解决吗？

[Jooooooooo]

不小的数据量, 搜索匹配, 不是 mysql 的强项, 搞个 es 吧.

[ration]

第一条是需要分页吧。。只能全文索引了，单独记录 count 的话也没用，搜索条件是不确定的。不上 es 的话，试一下 mysql 的全文索引，缓存占用比较大才能发挥优势。

[akira]

不是 COUNT(*) 的问题，是全文检索的问题

[lanlanye]

1. 分成两个接口，这样相同条件只需要 count 1 次

2. 如果全文检索本身无法利用索引优化，可以用 OVER() 一次获取到结果和总数，少查一次

[xiaopanzi]

Never use COUNT(*) in production mode.

[anxn]

@ration 对 第一条就是有分页

[Juszoe]

如果你的业务和搜索引擎一样只需要近似值，如“约 100000 条结果”，可以用 explain 命令

[Euthpic]

全文搜索用 ES 是最好的,用 MySQL 的 ngram 也行

[anxn]

@lanlanye 感谢建议，不过第 2 条查了下是 MySQL 8 才支持 OVER()函数，公司项目还是 5.7 版的
再研究研究怎么弄合适吧

[Sasasu]

过会你就来问深翻页怎么办了

[yesterdaysun]

如果总条数不是特别重要, 可以考虑只精确查询特定数量以下的条数, 比如 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 吧

[sutra]

`select found_rows();`

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_row-count

[sutra]

上面的 link 有点错。

后面的 anchor 改成 #function_row-count


SELECT
SQL_CALC_FOUND_ROWS *
FROM
bookclist
WHERE
btitle LIKE '%社会主义%'

select found_rows();

[sutra]

我怎么老是贴错，anchor 应该是 #function_found-rows

[akira]

@sutra 看了下文档，他是建议还是继续用 select count(*) 作为第二个语句来获取总行数的呀

[anxn]

@sutra 这个方法测试可行，感谢！

[sutra]

@akira 我也没怎么用过这个，只是知道。

[encro]

@sutra
@anxn

看文档意思是 found rows 已经被弃用（因为某些优化无效），建议用 count(*)会更好。

Mysql 应该是无解的，简单来说用全文索引会好过 like 很多。特别对于 tilte 这种，走索引后几百万数据还是挺快的。


Mysql 中文全文索引（含实例 5 分钟上手）

https://c4ys.com/archives/2098

[anxn]

@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

[anxn]

@anxn 补充一下，全文搜索 LIMIT 1000;应该是个优化思路

[sutra]

@anxn 你是说 select count(*) from blockclist; (注意不带任何 where 语句）很快是吧？这个 mysql 优化过，相比 postgresql 。

[anxn]

@sutra 不是，是带上 WHERE MATCH ( btitle ) AGAINST 子句的，就是 MATCH ( btitle ) AGAINST 的记录数

[mingl0280]

第一个需求：不让你用 redis 你还不能自己写个缓存嘛……
第二个需求没搞过，不知道。

[zmal]

SQL 层面没什么好优化的。
1. 审视全文检索的需求到底是否必要，业务上是否可以规避，比如只允许查询预先缓存的 keyword 。
2. 用 mysql 的全文索引肯定没问题啊，你说的某个人名 5000 多条慢 90%是 IO 原因。

[encro]

没办法，记录集中确实慢。

ngram_token_size=1 试一下，中文简单的话都分字性能好像还会高一点

你 explain 的时候要看慢在哪里。

[encro]

理解 explain 每一列的意思，看没一列的输出，然后就知道有没有优化空间了。