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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 927 天前的主题，其中的信息可能已经有所发展或是发生改变。

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

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

第一条 SQL

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

第二条 SQL

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

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

26 条回复 • 2022-05-10 20:23:12 +08:00

Jooooooooo

2022-05-09 22:35:13 +08:00

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

ration

2022-05-09 22:50:10 +08:00 via Android

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

akira

2022-05-09 22:58:33 +08:00

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

lanlanye

2022-05-09 23:09:12 +08:00 via iPhone

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

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

xiaopanzi

2022-05-09 23:19:27 +08:00

Never use COUNT(*) in production mode.

anxn

2022-05-09 23:20:12 +08:00

@ration 对第一条就是有分页

Juszoe

2022-05-09 23:22:01 +08:00

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

Euthpic

2022-05-09 23:34:07 +08:00

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

anxn

2022-05-09 23:41:15 +08:00

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

Sasasu

2022-05-09 23:52:37 +08:00

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

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 吧

sutra

2022-05-10 01:38:42 +08:00

`select found_rows();`

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

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();

sutra

2022-05-10 01:46:12 +08:00

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

akira

2022-05-10 03:51:15 +08:00

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

anxn

2022-05-10 09:56:07 +08:00

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

sutra

2022-05-10 11:32:37 +08:00

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

encro

2022-05-10 13:48:42 +08:00

@sutra
@anxn

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

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

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

https://c4ys.com/archives/2098

anxn

2022-05-10 14:05:03 +08:00

@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

2022-05-10 14:09:36 +08:00

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

sutra

2022-05-10 14:10:43 +08:00

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

anxn

2022-05-10 14:15:34 +08:00

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

mingl0280

2022-05-10 16:30:52 +08:00

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

zmal

2022-05-10 16:54:32 +08:00

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

encro

2022-05-10 20:20:34 +08:00

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

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

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

encro

2022-05-10 20:23:12 +08:00

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