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

SELECT COUNT(*)超级慢,讨论一下解决方案

  •  
  •   Aluhao · 2018-05-14 13:35:02 +08:00 · 19419 次点击
    这是一个创建于 2146 天前的主题,其中的信息可能已经有所发展或是发生改变。

    使用场景: 1、回复表 1 亿多数据,每天增长 1 万+; 2、发回复的时候得统计回复用户回复数量;

    使用的语句:SELECT COUNT(*) FROM answer WHERE uid='10' 查询结果:6963911 使用时间:101.618 秒 其中 uid 已经索引,也用过其它 COUNT(其它列字段) 查询一样很慢; 如果用 aid 已经索引,aid='10' 查出结果数量少,查询很快;

    还能通过优化 SQL 语句来优化吗,如果不行,只能通过 insert +1 及 delete -1 来解决了。

    67 条回复    2018-05-16 09:47:55 +08:00
    3dwelcome
        1
    3dwelcome  
       2018-05-14 13:44:54 +08:00
    用 MyISAM 表示速度奇快。而且用 mysql_list_tables 之类的结构查询,就能直接看一共有多少记录。
    linpf
        3
    linpf  
       2018-05-14 13:47:07 +08:00
    单表数据上亿,早就该拆表了吧。
    Aluhao
        4
    Aluhao  
    OP
       2018-05-14 13:47:15 +08:00
    @3dwelcome 现在就是用 MyISAM,数据量小很快的。
    kran
        5
    kran  
       2018-05-14 13:47:33 +08:00 via iPhone
    explain 一下
    yiqiao
        6
    yiqiao  
       2018-05-14 13:47:54 +08:00   ❤️ 1
    回复数量不少应该记录在类似文章表里面吗。。。
    RorschachZZZ
        7
    RorschachZZZ  
       2018-05-14 13:49:02 +08:00
    按照用户分组全量统计一次,保存起来。以后关于用户回复数的增删改查都来操作这次保存的数据。而且你这个表太大了是个隐患,最好拆。
    zhaishunqi
        8
    zhaishunqi  
       2018-05-14 13:51:46 +08:00
    我印象中,select count(*) 和 select count(1) 的效率,在数据量大的情况下,差别还是很大的.
    只是前者后者用法有略微的差别,会用就能避坑。
    q397064399
        9
    q397064399  
       2018-05-14 13:54:41 +08:00   ❤️ 1


    如果并发量不是很大的话,+1 -1 应该是个不错的选择
    glues
        10
    glues  
       2018-05-14 13:55:57 +08:00
    这个问题快成日经贴了,不要用 MySQL 不就好了
    doubleflower
        11
    doubleflower  
       2018-05-14 13:57:52 +08:00
    这种东西明显是要保存每个分组的 count,以后发贴+1
    mchl
        12
    mchl  
       2018-05-14 14:01:16 +08:00
    试一下
    SELECT COUNT(uid) FROM answer WHERE uid='10';
    FrailLove
        13
    FrailLove  
       2018-05-14 14:07:29 +08:00
    物化视图查询重写 了解一下
    ourzhang
        14
    ourzhang  
       2018-05-14 14:07:54 +08:00
    COUNT ( 1 ) 试试。
    xi4oh4o
        15
    xi4oh4o  
       2018-05-14 14:08:30 +08:00   ❤️ 1
    如果需求不用很精准的话,可以尝试用 explain select count(*) from table 取
    af463419014
        16
    af463419014  
       2018-05-14 14:17:24 +08:00
    分区表,了解一下

    比如: PARTITION BY HASH (uid) PARTITIONS 1000
    sagaxu
        17
    sagaxu  
       2018-05-14 14:18:03 +08:00 via Android
    主流关系数据库 count 需要遍历,时间复杂度是 O(n)
    akstrom
        18
    akstrom  
       2018-05-14 15:27:31 +08:00
    SELECT COUNT(uid) FROM answer WHERE uid=10;
    zqguo
        19
    zqguo  
       2018-05-14 15:27:38 +08:00
    回答 count(1)的认真看过题主的问题吗?
    tianzx
        20
    tianzx  
       2018-05-14 16:08:19 +08:00 via Android
    m
    VoidChen
        21
    VoidChen  
       2018-05-14 16:12:29 +08:00
    count 个字段,不要 count * 。然后就是搞分区。还不行就上 mpp,我用起来感觉 mpp 比 oracle 要快一丢丢,千万级数据的表,没实测,有时间打算试一下
    puritania
        22
    puritania  
       2018-05-14 16:13:15 +08:00   ❤️ 2
    这种东西用 redis 做个计数器不就完了吗,动态更新计数器。
    VoidChen
        23
    VoidChen  
       2018-05-14 16:15:36 +08:00
    没看到下面还有详细的不好意思。。。我觉得这种回复用 count 有点反人类了,给个字段 update +1 -1 吧。。。。
    linbiaye
        24
    linbiaye  
       2018-05-14 16:16:21 +08:00
    说 count(*)不如 count(1), count 字段的都是瞎说,加了索引还慢就考虑其他方案把。看看有没有可能自己维护计数器,估计跑不了拆分了。
    kkeiko
        25
    kkeiko  
       2018-05-14 16:22:06 +08:00
    说不要 count(*) 的 估计不知道查询优化器这个东西吧,至于楼主这个问题,拆表吧。
    Aluhao
        26
    Aluhao  
    OP
       2018-05-14 16:59:07 +08:00
    @mchl 这样也是很慢的,试过了,估计是内容太多的问题。
    dante3imin
        27
    dante3imin  
       2018-05-14 18:01:50 +08:00
    我看好多人说 count (*)和 count (字段)的区别,之前看到一篇[博文]( https://blog.ti-node.com/blog/6343811157316337664)
    jelinet
        28
    jelinet  
       2018-05-14 20:41:13 +08:00   ❤️ 1
    我也维护了一个计数的,但因为计数前业务复杂,写了很多逻辑代码,后来又加了锁,有时候真想 count 了之,但理智告诉我绝对不行,继续维护吧。
    lihongming
        29
    lihongming  
       2018-05-14 20:52:43 +08:00 via Android
    记得 discuz 的做法是+-1,且定时重算
    defclass
        30
    defclass  
       2018-05-14 21:01:54 +08:00 via Android
    uid 是 string 还是 integer ? 遇到过一个类似的坑
    elarity
        31
    elarity  
       2018-05-14 22:25:32 +08:00
    @VoidChen 说反了,对于 innodb 引擎,毫无疑问用 count(*),count ( col )弄不好会更慢
    Raymon111111
        32
    Raymon111111  
       2018-05-14 22:26:09 +08:00   ❤️ 2
    讨论 count(*) 和 count(1) 根本没找到重点

    没有使用场景?

    如果仅仅希望有一个数量, 两个方法

    简单一点直接 redis

    复杂一点, 把索引带上时间, 今天以前的回复数量是固定的, 每天零点的时候用离线任务把今天以前的回复数量全统计一遍扔到一个地方. 然后再实时计算今天的回复数量, 索引踩上 (uid,ctime) uid=#{uid} and ctime >#{betweenTime} and ctime< #{endTime}

    另外一个上亿每天增长千万的表应该要分库分表或者归档
    kn007
        33
    kn007  
       2018-05-14 22:29:36 +08:00   ❤️ 1
    建议读取一次后,存入 noSQL,做 incr 和 decr。
    aa6563679
        34
    aa6563679  
       2018-05-14 22:33:51 +08:00 via iPhone
    只有当天的数据实时统计,以前的数据专门换个表归档
    scnace
        35
    scnace  
       2018-05-14 22:36:17 +08:00 via Android
    鸟书上推荐的是维护字段+- 1 只是这样就要引入锁机制(
    yangqi
        36
    yangqi  
       2018-05-14 22:38:49 +08:00
    慢说明索引已经不优化了,先分析优化下索引。
    lgh
        37
    lgh  
       2018-05-14 23:23:24 +08:00 via iPhone
    @defclass +1,这个 uid 字段的类型到底是数字还是字符串真的要搞清楚先
    rahuahua
        38
    rahuahua  
       2018-05-14 23:43:06 +08:00   ❤️ 1
    uid=10 的数量已经近 700W,(如果数据库服务器内存不是足够大 + 要检索的索引文件不在内存里)索引本身的检索就会造成大量的 I/O,从数据库层面已经无法再优化了。只能从应用层优化了
    is99zsq
        39
    is99zsq  
       2018-05-14 23:54:18 +08:00   ❤️ 1
    添加一个表,记录累计值

    然后每日做个批量,

    查询变成 历史上的累计值+本日实时统计值
    vus520
        40
    vus520  
       2018-05-14 23:56:40 +08:00   ❤️ 2
    我的老哥,ES 了解一下
    kavana
        41
    kavana  
       2018-05-15 08:38:03 +08:00
    收藏 count*处理办法
    agostop
        42
    agostop  
       2018-05-15 08:54:42 +08:00   ❤️ 1
    最懒的办法,就是你先手动 count,然后再做个触发器,incr 和 decr
    VoidChen
        43
    VoidChen  
       2018-05-15 09:19:59 +08:00
    @elarity 好的,我去学习下
    Aluhao
        44
    Aluhao  
    OP
       2018-05-15 09:49:44 +08:00
    Aluhao
        45
    Aluhao  
    OP
       2018-05-15 09:55:04 +08:00
    @defclass 这个 UID 肯定是 int 了,存放用户 ID 的不可能用 string,默认都不为 null
    defclass
        46
    defclass  
       2018-05-15 10:04:21 +08:00
    @Aluhao 可以看 explain 先看看. 我之前遇到的一个坑是, 类型写错了, 无法使用到索引. 如果是 Int , sql 应该是 `uid= 10` ?
    checgg
        47
    checgg  
       2018-05-15 10:20:41 +08:00   ❤️ 1
    目前怀疑是 myisam 表锁的问题。
    建议把数据库 copy 到本地,在没有写和更新的情况下查询一下。
    Reign
        48
    Reign  
       2018-05-15 10:56:01 +08:00
    话说, https://www.v2ex.com/t/433836 ,每天回复增长 1 万+的理财论坛,居然还没钱只能用 SELECT COUNT(*), 你咋不专门雇个员工一行一行的去数呢?
    eslizn
        49
    eslizn  
       2018-05-15 10:59:43 +08:00   ❤️ 1
    @af463419014 实际经验,少用分区表。线上 ddl 是噩梦
    reus
        50
    reus  
       2018-05-15 11:00:18 +08:00
    内存不够。
    Aluhao
        51
    Aluhao  
    OP
       2018-05-15 11:49:03 +08:00
    @defclass
    没法上传图片;
    WHERE `uid`='10' 和 WHERE uid='10' 差别不大;
    defclass
        52
    defclass  
       2018-05-15 12:21:35 +08:00 via Android
    我的意思是 10 不要引号
    plko345
        53
    plko345  
       2018-05-15 12:29:40 +08:00 via Android
    @VoidChen 请问测试时用什么工具,数据是模拟的吗?
    IceBay
        54
    IceBay  
       2018-05-15 12:38:07 +08:00
    @scnace #35 请问书名是什么。
    darklowly
        55
    darklowly  
       2018-05-15 12:42:11 +08:00   ❤️ 1
    这类表一般是插入后不怎么修改,

    1 分表
    2 新建统计表

    可以用通过触发器,在插入的时候触发一下更新统计表
    crist
        56
    crist  
       2018-05-15 14:07:19 +08:00
    SELECT COUNT (. 人 .)
    colincat
        57
    colincat  
       2018-05-15 14:18:59 +08:00   ❤️ 1
    个人经验是需要单独创建一个表存数量,然后累加即可,这个也不要特别精准
    shiny
        58
    shiny  
       2018-05-15 14:24:47 +08:00
    MySQL 是跑在 SSD 上吗
    Wysten
        59
    Wysten  
       2018-05-15 14:31:42 +08:00   ❤️ 1
    前几天刚遇到类似的问题,单表 500W 数据,count(*) 和 count(id) 都很慢。最后用了 Redis Incr,先 count 一次,存入 redis,以后就直接在 redis 加 1 了 。
    scnace
        60
    scnace  
       2018-05-15 16:21:31 +08:00 via Android   ❤️ 1
    @IceBay 高性能 MySQL
    sleshep
        61
    sleshep  
       2018-05-15 16:49:51 +08:00 via Android
    触发器解决问题
    randyzhao
        62
    randyzhao  
       2018-05-15 17:18:41 +08:00
    10 号用户评论量有近 700W。。。
    dobelee
        63
    dobelee  
       2018-05-15 19:53:24 +08:00 via Android   ❤️ 1
    InnoDB 的 count 是无解的,如果对精准度没有要求的话可定时统计存储,否则需要维护一个计数器。
    lgh
        64
    lgh  
       2018-05-15 22:44:22 +08:00 via iPhone
    @Aluhao 你没理解 @defclass 的意思,应该写成 WHERE uid=10,否则 uid 的索引是不会起作用的,很多人叫你 explain 一下,你也没做吗?如果做了也能看出没走索引的。
    fox0001
        65
    fox0001  
       2018-05-16 06:39:44 +08:00 via Android   ❤️ 1
    我很懒,超过 99 条后,就显示 99+
    Aluhao
        66
    Aluhao  
    OP
       2018-05-16 09:28:13 +08:00
    @lgh 这个试过了,加了和没加效果一样呢;
    sagaxu
        67
    sagaxu  
       2018-05-16 09:47:55 +08:00 via Android
    @lgh 用不用索引,看用索引这个字段过滤后,剩下的条数占的比例,如果不够稀疏,比如说大于 10%,索引的质量就很低,走不走索引就不一定了。

    最常见的毫无意义的索引就是性别
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5470 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 39ms · UTC 07:41 · PVG 15:41 · LAX 00:41 · JFK 03:41
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.