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
chenqh
V2EX  ›  MySQL

关于 mysql count 太慢的问题

  •  
  •   chenqh · 2022-10-06 18:51:35 +08:00 · 4684 次点击
    这是一个创建于 760 天前的主题,其中的信息可能已经有所发展或是发生改变。

    200W 的数据量,count(*)就要 3S 多了

    有什么办法提升没有?

    使用 cache?但是 count(*)的时候 where 并不是固定的

    求指点

    第 1 条附言  ·  2022-10-07 11:02:49 +08:00
    我刚刚想了一个估计的方法

    假如 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,不知道这个样子可不可以
    36 条回复    2022-10-08 22:04:38 +08:00
    xupefei
        1
    xupefei  
       2022-10-06 18:57:07 +08:00 via iPhone
    用 where 的列做 partition
    mejee
        2
    mejee  
       2022-10-06 19:09:34 +08:00
    没有索引吗
    lmshl
        3
    lmshl  
       2022-10-06 19:28:37 +08:00   ❤️ 11
    做不到的,实际上现代数据库也没有在精确 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
        4
    chenqh  
    OP
       2022-10-06 19:51:06 +08:00
    @lmshl 只有学刚刚下面那个 5KW 那个了,只展示一个日期范围内的数据,不让他选全部,不然太卡了

    网上搜了一下,pg 的 count 比 mysql 还慢
    chenqh
        5
    chenqh  
    OP
       2022-10-06 19:52:00 +08:00
    但是如果两个月的数据还是太大,到了 200W 怎么办?
    edis0n0
        6
    edis0n0  
       2022-10-06 19:52:37 +08:00
    35 单位是毫秒吗,毫秒我业务能接受,因为只有后台需要 count 大量数据
    chenqh
        7
    chenqh  
    OP
       2022-10-06 19:54:43 +08:00
    @edis0n0 3S 是 3 秒的意思,35MS 我就不怕了咯
    edis0n0
        8
    edis0n0  
       2022-10-06 19:56:32 +08:00
    @chenqh #7 s 我看成 5 了
    mazyi
        9
    mazyi  
       2022-10-06 20:18:45 +08:00 via iPhone
    百万的级别,大概率是 where 条件的问题和索引的问题吧,查真实数据也会很慢吧
    signalas1
        10
    signalas1  
       2022-10-06 20:21:39 +08:00
    换 PG ,要不就是分析加索引
    chenqh
        11
    chenqh  
    OP
       2022-10-06 20:22:06 +08:00
    @signalas1 网上搜的,pg 的 count 比 mysql 要慢
    olaloong
        12
    olaloong  
       2022-10-06 20:23:27 +08:00 via Android
    同坑,mysql 算是快的了,mongodb 更是慢得炸裂。最后放弃精确计数了
    xy90321
        13
    xy90321  
       2022-10-06 20:46:23 +08:00 via iPhone
    对实时性要求有多高?不高的话另外维护一个件数的值,后台定期更新就好了。
    chenqh
        14
    chenqh  
    OP
       2022-10-06 20:56:45 +08:00
    @xy90321 管理后台,table 的那个 count
    makelove
        15
    makelove  
       2022-10-06 21:06:21 +08:00
    这么大数据量谁会实时计算 count ,编程是省心了,可是理论上做不到不遍历
    要精确值又要速度只能手动维护一堆计数器,在记录增加时给相关计数器也加一
    dzdh
        16
    dzdh  
       2022-10-06 22:10:47 +08:00   ❤️ 1
    count 目的是分页吗。

    如果是,前端固定写死 100 页。
    kiwi95
        17
    kiwi95  
       2022-10-06 22:11:27 +08:00 via Android
    用户量大的系统一般会有一个单独的 counter 服务来处理各种计数
    oceanthe1h
        18
    oceanthe1h  
       2022-10-06 22:23:09 +08:00   ❤️ 1
    MyISAM
    signalas1
        19
    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
        20
    kenvix  
       2022-10-06 22:36:33 +08:00
    如果不要求精确的话可以用 explain select * from table
    cp19890714
        21
    cp19890714  
       2022-10-06 23:35:37 +08:00   ❤️ 2
    首先需求是否合理?百万数据,精确 count 意义是什么?
    我从来没有找到有哪种场景真的有这个需求。

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

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

    用户需要知道精确 count 时,数据量一定是小的。
    数据量大的时候,用户一定不需要知道精确 count 。
    mythabc
        22
    mythabc  
       2022-10-06 23:37:55 +08:00
    用 flink 做实时 count ,结果实时写入 kafka
    zoharSoul
        23
    zoharSoul  
       2022-10-07 01:26:16 +08:00   ❤️ 1
    @lmshl #3 索引概率分布估算一个值
    大佬可以讲下 mysql 怎么估算这个值吗?
    dobelee
        24
    dobelee  
       2022-10-07 02:09:12 +08:00
    无解的。可以做个简单的 cache ,一般很少人翻到最后一页,很多产品最后几页都不准,比如豆瓣。
    minsheng
        25
    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
        26
    minsheng  
       2022-10-07 03:57:59 +08:00 via iPhone
    我又想了一下,如果不需要 filter 的话,即每次只支持查询时间 a 到 b 之间的全部数据,数据的时间戳是单调递增的话,或许也不是不能用我说的这个树这样的想法?

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

    查询的时候,你就把时间切成这样的区间,比如说查询 3min 到 180min ,你就把 30 、60 、90 、120 、150 这五行取出来加一下,再把 3 到 29 这二十多行加一下,最后累加就好了。
    xuanbg
        27
    xuanbg  
       2022-10-07 16:28:58 +08:00
    其实不精确的 count 也很难的。。。难就难在某个条件可能 count 很小,换个条件就很大。譬如订单状态,你查已完成订单,数量就大得不得了;待付款的总是很少。。。如果你查的是已完成,那查起来很慢,但没有出结果前你不能确定他慢啊。等你知道慢,结果也出来了,还优化个毛线啊。
    jhdxr
        28
    jhdxr  
       2022-10-07 17:45:11 +08:00
    虽然我也完全赞同大数据情况下还得精确统计这种需求的合理性在哪的问题,
    但 200W 的 count 要 3s+,我觉得可以看看用的是 SSD 还是机械硬盘,还有就是索引的利用情况。
    route
        29
    route  
       2022-10-07 18:00:16 +08:00
    要是是有限的几条语句,就每隔时间间隔去执行语句,再存到缓存中,下次用直接拿出来就好了
    nicoljiang
        30
    nicoljiang  
       2022-10-08 08:43:20 +08:00
    @chenqh 我 pg count 1000 万数据只需要 500ms ,没有什么索引。跟 pg 比起来,MySQL 都不算合格的数据库。
    chenqh
        31
    chenqh  
    OP
       2022-10-08 09:12:44 +08:00
    chenqh
        32
    chenqh  
    OP
       2022-10-08 09:16:55 +08:00
    @jhdxr 我虚拟机测的,200W COUNT 是没加条件的,我虚拟机是装在 SSD 上面的,但是问题是可能会加条件,这个页面就是管理后后的一个列表页面,上面一堆输入堆,下面一个 table
    Maxwe11
        33
    Maxwe11  
       2022-10-08 18:33:01 +08:00
    mysql 本身也不适合干这个活儿;

    如果你是生产库,那更不适合干这个,没事儿总 count 容易挂;

    看你的延迟需求,是不是说一定要实时 count ,还是准实时,对于这类聚合,如果不是要求绝对实时,踏踏实实另搭个 clickhouse ,你再加个 0 也是秒出
    nicoljiang
        34
    nicoljiang  
       2022-10-08 21:23:55 +08:00
    @chenqh pg 的 很多函数比 MySQL 快非常多,MySQL 都是残血版。
    chenqh
        35
    chenqh  
    OP
       2022-10-08 21:30:51 +08:00
    @nicoljiang 可惜我 pg 不会,现在也懒得去学了 pg 了,又不是自己当老板,学 PG 有什么用,老板叫用什么用什么咯
    ShuA1
        36
    ShuA1  
       2022-10-08 22:04:38 +08:00
    explain
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1227 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 17:54 · PVG 01:54 · LAX 09:54 · JFK 12:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.