关于 mysql count 太慢的问题

我刚刚想了一个估计的方法

假如 where 为 A

添加两条 sql, 1. select * from table A order by id limit 5, 取到 min_id

2. select * from table A order by id desc limit 5, 取到 max_id

当然要考虑可能 table 为空的情况

通过 delta=max_id-min_id,如果 detlta < 10000,再直接查 count, 如果> 10000,直接返回 10000,不知道这个样子可不可以

第 2 条附言 · 2022-10-07 11:03:48 +08:00

我刚刚想了一个估计的方法

假如 where 为 A

添加两条 sql, 1. select id from table A order by id limit 5, 取到 min_id

2. select id from table A order by id desc limit 5, 取到 max_id

当然要考虑可能 table 为空的情况

通过 delta=max_id-min_id,如果 detlta < 10000,再直接查 count, 如果> 10000,直接返回 10000,不知道这个样子可不可以

count

MySQL

Cache

Where

36 条回复 • 2022-10-08 22:04:38 +08:00

xupefei

2022-10-06 18:57:07 +08:00 via iPhone

用 where 的列做 partition

mejee

2022-10-06 19:09:34 +08:00

没有索引吗

lmshl

2022-10-06 19:28:37 +08:00

做不到的，实际上现代数据库也没有在精确 count 上做很多努力，你看各大网站的搜索结果，基本上都是给你个估算值（ 10000+），因为精确 count 是需要遍历所有 where 命中行并聚合计数的。

我在我公司应用上实现的 count 是这么个逻辑：
先用索引概率分布估算一个值，如果这个值小于 10k ，那么执行精确 select count(*) where ... 返回给前端。
如果这个值大于 10k ，那么将此估算值抹去末尾 N 位，返回给前端，前端显示为“约 53200+ 符合结果”

参考资料：
https://wiki.postgresql.org/wiki/Count_estimate
https://www.datastax.com/blog/counting-keys-cassandra

chenqh

2022-10-06 19:51:06 +08:00

@lmshl 只有学刚刚下面那个 5KW 那个了,只展示一个日期范围内的数据,不让他选全部,不然太卡了

网上搜了一下,pg 的 count 比 mysql 还慢

chenqh

2022-10-06 19:52:00 +08:00

但是如果两个月的数据还是太大,到了 200W 怎么办?

edis0n0

2022-10-06 19:52:37 +08:00

35 单位是毫秒吗，毫秒我业务能接受，因为只有后台需要 count 大量数据

chenqh

2022-10-06 19:54:43 +08:00

@edis0n0 3S 是 3 秒的意思,35MS 我就不怕了咯

edis0n0

2022-10-06 19:56:32 +08:00

@chenqh #7 s 我看成 5 了

mazyi

2022-10-06 20:18:45 +08:00 via iPhone

百万的级别，大概率是 where 条件的问题和索引的问题吧，查真实数据也会很慢吧

signalas1

2022-10-06 20:21:39 +08:00

换 PG ，要不就是分析加索引

chenqh

2022-10-06 20:22:06 +08:00

@signalas1 网上搜的,pg 的 count 比 mysql 要慢

olaloong

2022-10-06 20:23:27 +08:00 via Android

同坑，mysql 算是快的了，mongodb 更是慢得炸裂。最后放弃精确计数了

xy90321

2022-10-06 20:46:23 +08:00 via iPhone

对实时性要求有多高？不高的话另外维护一个件数的值，后台定期更新就好了。

chenqh

2022-10-06 20:56:45 +08:00

@xy90321 管理后台,table 的那个 count

makelove

2022-10-06 21:06:21 +08:00

这么大数据量谁会实时计算 count ，编程是省心了，可是理论上做不到不遍历
要精确值又要速度只能手动维护一堆计数器，在记录增加时给相关计数器也加一

dzdh

2022-10-06 22:10:47 +08:00

count 目的是分页吗。

如果是，前端固定写死 100 页。

kiwi95

2022-10-06 22:11:27 +08:00 via Android

用户量大的系统一般会有一个单独的 counter 服务来处理各种计数

oceanthe1h

2022-10-06 22:23:09 +08:00

MyISAM

signalas1

2022-10-06 22:33:37 +08:00

@chenqh Planned Count

To avoid the shortcomings of exact count, PostgREST can leverage PostgreSQL statistics and get a fairly accurate and fast count.

https://postgrest.org/en/v8.0/api.html#planned-count

kenvix

2022-10-06 22:36:33 +08:00

如果不要求精确的话可以用 explain select * from table

cp19890714

2022-10-06 23:35:37 +08:00

首先需求是否合理？百万数据，精确 count 意义是什么？
我从来没有找到有哪种场景真的有这个需求。

如果是为了分页，那么可以做个假 total ，只要 currentPageSize 大于或等于 pageSize ，就认为可能有下一页，returnTotal = pageNo * pageSize + 1. 用户就可以一直点下一页，直到最后一页。

如果是为了看大概的数据量，那么可以定时任务跑，根据条件(例如时间段)分多次查询，最后相加，放缓存。

用户需要知道精确 count 时，数据量一定是小的。
数据量大的时候，用户一定不需要知道精确 count 。

mythabc

2022-10-06 23:37:55 +08:00

用 flink 做实时 count ，结果实时写入 kafka

zoharSoul

2022-10-07 01:26:16 +08:00

@lmshl #3 索引概率分布估算一个值
大佬可以讲下 mysql 怎么估算这个值吗?

dobelee

2022-10-07 02:09:12 +08:00

无解的。可以做个简单的 cache ，一般很少人翻到最后一页，很多产品最后几页都不准，比如豆瓣。

minsheng

2022-10-07 03:47:08 +08:00 via iPhone

@lmshl 也不是完全不行，我之前看 CouchDB 的设计，它们的核心亮点就是可以对每个文档（这是个 NoSQL 的文档数据库）做持久化的 flat map + reduce ，就类似建立 index 一样把 map reduce 的结果存在硬盘上。用的是 B+树，把每个树的子节点的 reduce 结果都会存在 B+树的分支节点上，这样可以快速的（指数级复杂度）对连续区间的数据进行 reduce ，这就包括了题主的问题。

minsheng

2022-10-07 03:57:59 +08:00 via iPhone

我又想了一下，如果不需要 filter 的话，即每次只支持查询时间 a 到 b 之间的全部数据，数据的时间戳是单调递增的话，或许也不是不能用我说的这个树这样的想法？

手机打字，我就边打边算了。假设两个月生成两百万数据，那么一天在三万左右，每分钟 24 个。那可不可以每分钟跑个程序，统计一下那个分钟有多少数据，然后每三十分钟请求一下，刚刚三十分钟分钟有多少数据。注意你这个时候就可以直接加过去三十分钟已经算好的三十行了。然后依次类推，九百分钟一个单元，两万七千分钟一个单元（十八点七五天），这样这颗树不会很高。

查询的时候，你就把时间切成这样的区间，比如说查询 3min 到 180min ，你就把 30 、60 、90 、120 、150 这五行取出来加一下，再把 3 到 29 这二十多行加一下，最后累加就好了。