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

mysql 四百万左右数据 count(*) 49 秒才响应,求助大佬怎么优化?

  •  1
     
  •   edk24 ·
    edk24 · 2021-07-30 11:35:10 +08:00 · 9579 次点击
    这是一个创建于 973 天前的主题,其中的信息可能已经有所发展或是发生改变。
    EXPLAIN SELECT COUNT(*) AS tp_count FROM `fa_people_check` `people_check` LEFT JOIN `fa_people` `people` ON `people_check`.`people_id`=`people`.`id` LIMIT 1;
    

    大佬们,像这种怎么优化字段提升查询速度。 执行一次 49s 就很离谱

    55 条回复    2021-08-02 17:26:10 +08:00
    xixihaha1
        1
    xixihaha1  
       2021-07-30 11:41:08 +08:00
    people_id 有索引吗
    luckyrayyy
        2
    luckyrayyy  
       2021-07-30 11:41:18 +08:00   ❤️ 1
    没看太懂,你这怎么两个表 left join 两个表?另外左关联的 count(*) 不就是左表的 count(*) 嘛?
    xixihaha1
        3
    xixihaha1  
       2021-07-30 11:44:15 +08:00   ❤️ 1
    @luckyrayyy 什么两个表,那是表的别名
    ipwx
        4
    ipwx  
       2021-07-30 11:46:32 +08:00
    @luckyrayyy AS 省略,SQL 的奇葩语法
    0o0o0o0
        5
    0o0o0o0  
       2021-07-30 11:48:08 +08:00
    people_check.people_id
    people.id
    需要加索引
    edk24
        6
    edk24  
    OP
       2021-07-30 11:49:07 +08:00
    @0o0o0o0 加了的
    @xixihaha1

    加了索引的, 因为是必须常用的字段
    edk24
        7
    edk24  
    OP
       2021-07-30 11:49:31 +08:00
    @ipwx 哈哈 直接在慢日志里 copy 的。 这个 as 是 thinkphp 框架加上的
    iyaozhen
        8
    iyaozhen  
       2021-07-30 11:51:38 +08:00
    表结构呢? people_check 还用了表分区,是依据哪个字段分的?

    还有不加条件的话 count * 貌似是比较慢,count id 啥的看看?感觉这个 sql 想干什么不明确
    ledkk
        9
    ledkk  
       2021-07-30 11:54:08 +08:00
    people_check 是一个分区表? 分区的方式是什么样子的? 分区表不带分区字段,查询肯定会非常慢了。

    people 表只有一条记录么? 如果是的话,或者记录条数很少的话,就没必要做 join 了,可以考虑用 in 替换一下
    edk24
        10
    edk24  
    OP
       2021-07-30 11:54:37 +08:00
    @iyaozhen 分区是原先想的替代方案,结果太麻烦 而且并没有什么好处没用了, 现在只想好好优化这个 sql 。

    这个 sql 是查询数据 limit 前后,要返回数据总数, 用来分页
    SjwNo1
        11
    SjwNo1  
       2021-07-30 11:55:49 +08:00
    上面的索引明显加了啊。。
    查看下 join_buffer_size 值吧,适当调整看看,不要 count(*),先试试看
    iyaozhen
        12
    iyaozhen  
       2021-07-30 11:56:50 +08:00
    @edk24 那你得把分区去掉呀,不然会有影响的
    iyaozhen
        13
    iyaozhen  
       2021-07-30 12:05:51 +08:00
    @edk24 总数的话和 count people_check 这个表没啥区别?
    myd
        14
    myd  
       2021-07-30 12:17:17 +08:00
    1. 给 people_check 加一个 people_id 普通索引
    2. 给 fa_people 的 id 字段加一个唯一索引(已有主键索引的情况下,再加一个唯一索引)
    myd
        15
    myd  
       2021-07-30 12:17:37 +08:00
    SQL 不用改
    Jooooooooo
        16
    Jooooooooo  
       2021-07-30 12:42:46 +08:00
    最好是增量的 count
    klesh
        17
    klesh  
       2021-07-30 12:55:26 +08:00 via Android
    索引对 where 和 order 有帮助,对 count 没有直接影响,因原理上需要遍历所有结果集里面的行。
    可用 trigger 维护一个变量或定时 count 的方式绕过。
    liuxu
        18
    liuxu  
       2021-07-30 13:00:15 +08:00
    这是索引的事吗,这是 join 出 temp 表做 count 的事啊。。
    wellsc
        19
    wellsc  
       2021-07-30 13:00:38 +08:00
    单独搞个字段增量去记录 count
    JJsty1e
        20
    JJsty1e  
       2021-07-30 13:07:19 +08:00 via iPhone
    你左表一条数据会对应右表多条数据吗?如果没有的话,就直接 count 左表吧没必要 left join 了
    JJsty1e
        21
    JJsty1e  
       2021-07-30 13:08:35 +08:00 via iPhone
    晕,右表是主键啊,那可以去掉 left join
    zoharSoul
        22
    zoharSoul  
       2021-07-30 13:09:33 +08:00   ❤️ 3
    不 count 返回个 99999 拉倒
    est
        23
    est  
       2021-07-30 13:18:54 +08:00
    百万级的表我觉得几十秒返回是正常的。。mysql 就这个水平。。。
    micean
        24
    micean  
       2021-07-30 13:20:47 +08:00
    如果 fa_people 记录数小,可以分几步算出来
    如果 fa_people 记录数大,fa_people_check 直接加字段吧
    otakustay
        25
    otakustay  
       2021-07-30 13:22:20 +08:00
    不用 InnoDB 就好了
    zhaokun
        26
    zhaokun  
       2021-07-30 13:29:02 +08:00
    数据量小的表在前 join 其他表,可以稍微快一丢丢,join 能不要就不要了,查 count 不需要 json 吧
    oueryini
        27
    oueryini  
       2021-07-30 13:29:53 +08:00
    换 MyISAM
    bootvue
        28
    bootvue  
       2021-07-30 13:30:06 +08:00
    删掉 399 万
    SjwNo1
        29
    SjwNo1  
       2021-07-30 13:30:31 +08:00   ❤️ 2
    上面的某几位仁兄简直在误导别人啊。。。
    ruke
        30
    ruke  
       2021-07-30 13:37:45 +08:00
    count 和索引没啥关系吧,索引是辅助查询的,count 是要对结果进行统计的
    ruke
        31
    ruke  
       2021-07-30 13:38:45 +08:00
    innodb 的话 count 本身就是扫描, 就算不加条件 count 一样慢
    0o0o0o0
        32
    0o0o0o0  
       2021-07-30 13:43:32 +08:00
    @edk24 去掉分区之后有索引也这么慢吗?
    robinlovemaggie
        33
    robinlovemaggie  
       2021-07-30 13:46:39 +08:00
    @est #23 如你所说,那 mysql 就是 shit 级数据库了
    Valid
        34
    Valid  
       2021-07-30 13:56:46 +08:00
    我 250 万的数据 count(*)用时 (0.876 秒)
    0o0o0o0
        35
    0o0o0o0  
       2021-07-30 13:58:04 +08:00
    @ruke 好像如果是辅助索引的话就不需要全表扫描了,不过这里有 left join 用索引可以加速的吧。。。
    sunjiayao
        36
    sunjiayao  
       2021-07-30 14:12:06 +08:00
    做几个测试
    1. 去掉 join
    2. 去掉分区
    3. left join 改成 right
    然后抛下测试结果
    nuistzhou
        37
    nuistzhou  
       2021-07-30 14:14:51 +08:00 via iPhone
    看看两个表的 id 有没有加索引吧
    chenyi
        38
    chenyi  
       2021-07-30 14:29:52 +08:00
    people 表就一行,还是用的 left join,这个 left join 可以去掉

    还有那些问有没有索引的,自己看看 explain 结果呀
    ifconfig
        39
    ifconfig  
       2021-07-30 14:39:16 +08:00
    很疑惑,既然没有加 where 条件,为什么要 join ?
    realpg
        40
    realpg  
       2021-07-30 14:56:46 +08:00
    抛开 49 秒的问题,你这 SQL 应该回炉了
    maxiaofeng
        41
    maxiaofeng  
       2021-07-30 15:03:00 +08:00
    @ifconfig 盲生,你发现了华点
    atom234
        42
    atom234  
       2021-07-30 15:11:21 +08:00
    数据库是 mysql,使用 count 的话 people_check 必须要有主键,排查下 这 9 个分区的分区方式是啥,这个数据量好像也没必要分区啊。。。
    还有 换 MyISAM 的话并不建议 会有事务问题。
    Aruforce
        43
    Aruforce  
       2021-07-30 15:27:54 +08:00
    有 where 过滤么? 不过滤 左联干啥啊
    ybleak
        44
    ybleak  
       2021-07-30 15:28:04 +08:00
    这个 JOIN 我怕
    rekulas
        45
    rekulas  
       2021-07-30 16:21:38 +08:00
    count(主键)会稍微快一些,当然想彻底解决只能缓存起来,count 本身很难优化,互联网巨头都没办法
    hhjswf
        46
    hhjswf  
       2021-07-30 17:51:13 +08:00
    几百万的数据分页好像无解啊,页数越往后越慢
    Loku
        47
    Loku  
       2021-07-30 18:20:12 +08:00
    有 9 个分区,然后 count(*)原因吧。count(`people_check`.id)吧
    encro
        48
    encro  
       2021-07-30 22:23:16 +08:00   ❤️ 1
    写得什么鬼 SQL,
    我大几千万数据 mysql 阿里云 2C4G RDS count 也就几秒啊,没分表分库。
    不过对于我来说,超过 0.2 秒的 sql 是不允许存在的,所以 Count 的时候都是加了条件,保证 explain rows 不超过几千条记录的。对于统计是走聚合的。

    那些还在鼓吹 myisam 的,也是一群无知,麻烦先试下是不是 Count 是否也慢?
    还有说 innodb Count 慢的,能说出那个引擎 Count 不慢(包括收费的),
    建议以上两类都先了解下 Count 慢的原因(原理)再开口?
    采用 innodb 引擎的 mysql 工程师们都是一个傻子?丢下原来好用的 myisam 不用去全面切换为 innodb ?
    商业数据库 count 快?他们是如何解决 like,in , or 等复杂条件 count 的?原理上可行吗?
    rekulas
        49
    rekulas  
       2021-07-30 22:53:57 +08:00
    @encro 我们也用阿里数据库 rds polar 之类的也在用自建数据库,阿里的是做了优化感觉比原生 mysql 性能要高些,然后云数据库估计硬件配置也好所以快
    abcbuzhiming
        50
    abcbuzhiming  
       2021-07-31 11:07:18 +08:00
    @encro 阿里云的 RDS 比很多人自建的快,楼上这些说性能的能把自己硬件软件配置报一下吗?楼主最好也把配置报一下,我见过有些人不懂,直接自己在云 ECS 上建 MySQL 的,他那个 ECS 还不是高性能 IO 的,那不就这个性能。要知道很多 Mysql 看起来测试性能很高,那人家那硬件动不动就是在 64 核,123G 内存,SSD 阵列,参数还优化到极致。不比你自己用 MySQL 社区版默认装的强到哪里去了
    Maxwe11
        51
    Maxwe11  
       2021-07-31 20:02:32 +08:00   ❤️ 1
    如果不是要求实时统计,其实还有个野路子,就是为分析之类的做准备,离线一份儿 clickhouse,快到你高潮。
    lizuoqiang
        52
    lizuoqiang  
       2021-08-02 10:08:15 +08:00
    @myd 逗比
    KickAssTonight
        53
    KickAssTonight  
       2021-08-02 11:28:10 +08:00
    innodb 的 count 就是会慢
    edk24
        54
    edk24  
    OP
       2021-08-02 17:25:43 +08:00
    @KickAssTonight 正解,确实拿这个无法,因为是 innodb 关系,去除 join 也好不在哪儿去
    ivanMeng
        55
    ivanMeng  
       2021-08-02 17:26:10 +08:00
    innodb 没有计数器。count 是遍历获得数据总数。myisam 会有计数器的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2939 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 60ms · UTC 14:01 · PVG 22:01 · LAX 07:01 · JFK 10:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.