V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
cy1027
V2EX  ›  程序员

数据库查询优化问题,有没有老哥懂的?

  •  
  •   cy1027 · 2023-02-01 17:05:41 +08:00 · 2194 次点击
    这是一个创建于 421 天前的主题,其中的信息可能已经有所发展或是发生改变。

    SELECT id FROM table_name;--一秒钟出结果,我的表大概三十万数据 select count(id) from table_name;--超过二十秒,没等它跑完 select count() from table_name;--同上 id 是主键,数据库里有很多列,有大量字符串,大概占几个 g 的磁盘。 我比较不理解,为什么只查 id 可以很快,但是对 id 计数就会很慢,如果说 count ()要扫描全表,那 count ( id )不应该只比第一个稍微慢一丢丢么?

    41 条回复    2023-02-03 16:14:08 +08:00
    aw2350
        1
    aw2350  
       2023-02-01 17:09:25 +08:00
    count(*) 忽略 null
    count(id) 筛查 id<> null
    cstj0505
        2
    cstj0505  
       2023-02-01 17:11:32 +08:00
    SELECT id FROM table_name 只是查出了一部分,可能就几十条,不是全部查出,你的数据库工具也没有把 30 万 id 都放内存里吧。
    select count(id) from table_name 需要全部查出
    adoal
        3
    adoal  
       2023-02-01 17:12:43 +08:00
    如 #2 所说。

    你的 SELECT id 是在哪里运行的,怎样算是“出结果”?
    opengps
        4
    opengps  
       2023-02-01 17:20:11 +08:00
    id 列自身就是个索引。
    回归下实体案例理解下:你那本新华字典,真正内容厚 5cm (全文扫描),但你只看索引(执行 count(id) )只需要 0.5cm
    cy1027
        5
    cy1027  
    OP
       2023-02-01 17:23:38 +08:00
    @adoal
    @cstj0505 其实用什么工具查没影响的,我用的是 navicat ,在查询标签里面手打的 sql 语句,连远程数据库查的,直接查 id 本地查到了 40 万(刚刚数据库又增加了一些数据)数据,每个 id 都有,但是无序,我是拖动滚动条看的,右下角会显示“第 1 条”到“第 400000 条”的字样,由于数据库还在更新,现在查 id 大概在 3s 以内,查 count 还是远超 20 秒
    leahoop
        6
    leahoop  
       2023-02-01 17:29:19 +08:00
    navicat 默认给你查 1000 条所以很快,你试试 limit 400000 全查出来应该也慢。id 是主键应该不可 null,直接用 count(*)和 count(1)性能是最快的
    jixiangqd
        7
    jixiangqd  
       2023-02-01 18:10:58 +08:00   ❤️ 1
    什么数据库?什么版本?什么存储引擎?
    这些都没说怎么分析数据库里怎么跑的?
    encro
        8
    encro  
       2023-02-01 18:13:11 +08:00
    没有优化方法途径。count 就是慢。order by 也是慢
    qua
        9
    qua  
       2023-02-01 18:27:59 +08:00 via Android
    可能是 SELECT id FROM table_name 被 navicat 自动加了 limit
    LostPrayers
        10
    LostPrayers  
       2023-02-01 18:30:01 +08:00   ❤️ 1
    innodb 的大表 count 就是难搞。
    1. 如果只有一个 id 索引,然后表又很大, 解决方案是加一个非主键索引,然后查询用 count(*), 它会挑最小数据量的索引进行统计。
    2. 使用其他取巧方法:
    比如 id 是自增的,从 0 开始,那么最大的 id 就是总数。
    比如用 show table status, 虽然不准但勉强够用.
    比如上缓存, 程序启动时候查一次缓存起来, 业务更新缓存.
    3. 改需求,不要 count 全表。比如后台的管理列表的分页, 就可以只查询前 10W 条,其他情况下带精确的查询条件时再解除限制。
    seers
        11
    seers  
       2023-02-01 18:32:06 +08:00 via Android
    覆盖索引
    v2eb
        12
    v2eb  
       2023-02-01 18:32:42 +08:00 via Android
    看看表结构
    encro
        13
    encro  
       2023-02-01 18:33:15 +08:00
    navicat 的统计自动走了统计表,不是准确的。

    count 是必须准确的,而且是需要考虑脏数据。
    fengpan567
        14
    fengpan567  
       2023-02-01 18:34:19 +08:00
    explain select count(id) from table_name; 看看用的什么索引,索引长度是多少
    encro
        15
    encro  
       2023-02-01 18:41:55 +08:00
    关系数据库几个很多无法解决的问题:

    1,count 慢
    2,order by xxx limit 1000000,10 (取最后页的慢)
    3,like “%xxx”慢
    4,没有合理建立索引慢
    5,自动更新物理试图
    6,。。。

    以上等等限制才会有 es,mongo,hbase,redis,InfluxDB,GDB 等等的机会。以上导致关系数据库没有走主键索引的话也就千万级别数据量比较合适吧。如果都是走主键那随便放。
    PythonYXY
        16
    PythonYXY  
       2023-02-01 18:52:38 +08:00
    你把两个 sql 的执行过程贴出来
    luozic
        17
    luozic  
       2023-02-01 18:53:23 +08:00
    看执行计划,时间花在哪了。。。
    hhjswf
        18
    hhjswf  
       2023-02-01 18:58:40 +08:00 via Android
    我寻思 count 可不就必须全盘扫描了,还搁着索引呢。。有用吗
    hhjswf
        19
    hhjswf  
       2023-02-01 19:10:02 +08:00 via Android
    应该是 count 用了临时表
    shore123
        20
    shore123  
       2023-02-01 19:10:48 +08:00
    因为你 SELECT id FROM table_name; 这条查询的是第一页吧....
    netnr
        21
    netnr  
       2023-02-01 19:23:55 +08:00 via Android
    还有一种可能,都慢,第一种方式走缓存了
    akira
        22
    akira  
       2023-02-01 21:09:01 +08:00
    navicat 会自动加 limit ,所以你前面那个是秒出。
    littlewing
        23
    littlewing  
       2023-02-01 21:21:01 +08:00
    SELECT id FROM table_name;--一秒钟出结果,我的表大概三十万数据
    select count(id) from table_name;--超过二十秒

    不可能,不科学,SELECT id FROM table_name 也是把 30 万条数据每一条都查出来,怎么可能差别那么大
    cstj0505
        24
    cstj0505  
       2023-02-02 09:59:11 +08:00   ❤️ 1
    @encro 支持事务 mvcc 数据库 count 基本无解的,因为需要扫描每一行数据的更新状态,对当前查询是否可见,如果被别的事务修改了还要去查找历史版本
    cy1027
        25
    cy1027  
    OP
       2023-02-02 11:07:45 +08:00
    MySQL 8.0 ,innodb 的引擎,实在不好意思,本人确实对数据库不太了解,不知道检测需要从哪些指标切入,感谢指点
    cy1027
        26
    cy1027  
    OP
       2023-02-02 11:09:01 +08:00
    @leahoop 你怎么不看一看我的评论呢,navicat 查出来的结果是 40 万条,我不希望因为这些没有意义的讨论浪费时间,还请认真一点
    cy1027
        27
    cy1027  
    OP
       2023-02-02 11:11:32 +08:00
    @encro 我这种用法直接用命令行不也是一样的结果吗?跟 navicat 没关系吧
    cy1027
        28
    cy1027  
    OP
       2023-02-02 11:15:15 +08:00
    @littlewing 这就是我的疑问呀,我不明白的就是这个地方
    cy1027
        29
    cy1027  
    OP
       2023-02-02 11:18:44 +08:00
    @cstj0505 确实,因为查询的时候我的数据库还在更新,其实我只是需要一个大概的数值来检查更新进度就可以了,那这样我感觉直接查所有 id 然后在程序中对 id 计数会比较满足需求,直接用 count 可能就会因为更新问题导致查询变慢,应该这样理解吧
    wangxin3
        30
    wangxin3  
       2023-02-02 13:42:51 +08:00   ❤️ 1
    如果查询没有 where 条件的话,而且是大表,我建议自己在 db 中缓存 count 计数,insert 一条,count+1 ,insert 和 count+1 的 update 在同一事物中一并提交。
    如果有 where 条件的话,上覆盖索引。
    再者 count(*)是最快的
    cy1027
        31
    cy1027  
    OP
       2023-02-02 14:03:42 +08:00
    @wangxin3 没有 where 条件。你说的这个记录是在另外一个表中记录吗?
    cy1027
        32
    cy1027  
    OP
       2023-02-02 14:04:58 +08:00
    @wangxin3 还有,我这里实测就是 count (*)没有查所有 id 快啊,你直接再来给个结论是有什么依据吗?
    wangxin3
        33
    wangxin3  
       2023-02-02 16:52:46 +08:00
    @cy1027 #31 原文:“@wangxin3 没有 where 条件。你说的这个记录是在另外一个表中记录吗?”
    ======
    回复:是的
    wangxin3
        34
    wangxin3  
       2023-02-02 16:54:22 +08:00
    @cy1027 #32 原文:“@wangxin3 还有,我这里实测就是 count (*)没有查所有 id 快啊,你直接再来给个结论是有什么依据吗?”
    ======
    回复:count(主键 id),InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
    但是 count (*),InnoDB 引擎是作过特殊处理的,不取值,直接按行累加。相较于 count (主键 id )少了取每一行 id 的操作。
    wangxin3
        35
    wangxin3  
       2023-02-02 16:58:23 +08:00   ❤️ 1
    @cy1027 #32 原文:“@wangxin3 还有,我这里实测就是 count (*)没有查所有 id 快啊,你直接再来给个结论是有什么依据吗?”
    ======
    回复:可能我理解错了 select count(*) from table; 和 select id form table;不存在比较的意义。不是一个层级的东西。
    encro
        36
    encro  
       2023-02-03 13:22:26 +08:00
    @cy1027


    就是 navicat 自动给你加了 limit...
    然后他的统计是来自 infomation 表的大概统计,如果你仔细看前面有一个约等于符号,我没有记错的话。

    你直接用命令行 select * 看看,一片跑一边看 show full processlist ,几十万数据 send data 状态都要几秒吧。

    数据库的耗时有几个阶段:

    查询解析:分析关系,走哪个索引?
    取数据:内存(走了索引)或磁盘(没走索引)?
    预处理结果:排序或格式化等,走了内存或者临时表?
    发送数据:发数据到客户端,结果集大肯定慢了

    结合慢查询,processlist 和 explain 可以发现并解决问题,如果是我前面的问题,基本无解。

    如前面所说,我刚才试了下,在我的阿里云小 rds ( 1 核)上,900 多万数据无论是 select * 还是 id ,都需要一分多钟,两者走的是同样的索引,所以实际项目我都是加了条件限制的,默认只查当个月数据,用户加日期建立了组合索引,不让用户直接翻页到最后一页。
    encro
        37
    encro  
       2023-02-03 13:26:45 +08:00
    @wangxin3

    对不起,我用的 mysql8.17 没有这个功能。
    explain
    两个走的是一样的索引。
    同时走了一个 tinyint 的索引。
    你可以在线上试一下看。
    encro
        38
    encro  
       2023-02-03 13:28:35 +08:00
    @wangxin3

    这个特性好像是 20 年前的 myisam 引擎的,不是 innodb 引擎的。
    encro
        39
    encro  
       2023-02-03 13:36:18 +08:00
    pg 100 万数据,select count(*)和 select count(id)也都是一样的索引,一样的 200ms ,刚试过的。

    所以:

    不要相信关系性数据库 count 能快,是要实际排序计算的。

    不要相信数据库工程师很菜,不会自动分析走哪个索引,明明有更优化的索引不走,走个慢的。
    cy1027
        40
    cy1027  
    OP
       2023-02-03 13:52:33 +08:00
    @wangxin3 感谢回复
    wangxin3
        41
    wangxin3  
       2023-02-03 16:14:08 +08:00
    @encro #38 原文:“@wangxin3 这个特性好像是 20 年前的 myisam 引擎的,不是 innodb 引擎的。”
    ======
    回复:是 innodb 的。myisam 引擎是把 count 数目保存在磁盘上的,如果没有 where 条件,获取速度是非常快的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5394 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 40ms · UTC 09:11 · PVG 17:11 · LAX 02:11 · JFK 05:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.