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 五百万以上的 join left 关联查询统计很慢 怎么操作

  •  
  •   edk24 ·
    edk24 · 2021-06-11 10:37:45 +08:00 · 5237 次点击
    这是一个创建于 1245 天前的主题,其中的信息可能已经有所发展或是发生改变。

    索引已经加了,a 表 people_id 关联 b 表 id, 然后条件是 b 表的某一个字段==1,然后 count(*)

    下面是 php 伪代码

    $people_ids = Db::name('people_check')->where('admin_id',$value['id'])->column('people_id');
    $people1 = Db::name("people")->where('id','in',$people_ids)->where('id_dailu',1)->count();  # 全部检测人数
                    
    

    admin_id 加了索引,id_dailu 加了索引。 没有全表扫描

    原先写法是 join left,这个是改过的, 这个更慢。 求大佬分析一波

    26 条回复    2021-06-11 21:45:32 +08:00
    jenlors
        1
    jenlors  
       2021-06-11 10:46:23 +08:00
    如果对准确的要求不高可以取模糊值,或者加缓存
    liprais
        2
    liprais  
       2021-06-11 10:47:27 +08:00
    不贴执行计划咋分析
    liuxingdeyu
        3
    liuxingdeyu  
       2021-06-11 11:20:18 +08:00
    你好歹给个 sql 语句和表信息啊,或者给个查询计划。还有就是,为啥 5m 的数据要一下全搞出来,而且还要 join 。话说如果有这样的需求,postgresql 是不性能会好点
    codespots
        4
    codespots  
       2021-06-11 13:15:40 +08:00   ❤️ 1
    这是哪个框架的 ORM 写法啊,好歹给个 SQL 语句啊,你这样提问很大程度上限制了回复你问题的人群范围
    yadgen
        5
    yadgen  
       2021-06-11 13:25:44 +08:00
    in 是全表扫描
    keepeye
        6
    keepeye  
       2021-06-11 13:28:04 +08:00
    第一步 people_ids 结果集多大?太大的话可能就不走索引了
    ebingtel
        7
    ebingtel  
       2021-06-11 14:02:46 +08:00
    这个要看 raw sql……调用 count(), 看看对应的 sql 是: "SELECT COUNT(*) FROM (子查询)".还是 SELECT COUNT(*) FROM 表 1 JOIN ...
    Aruforce
        8
    Aruforce  
       2021-06-11 14:08:51 +08:00
    不要 in... 会扫描所有的行
    ZhaoHuiLiu
        9
    ZhaoHuiLiu  
       2021-06-11 14:16:57 +08:00 via Android
    没做这种项目,能否给 mysql 弄两个从数据库。

    select people_id
    from people_check
    where admin_id=?

    上面得到了 people_ids 数组

    select count(*)
    from people
    where id=? and id_dailu=1

    遍历 people_ids 数组生成多个上面的 sql 语句,分别发送给两个从数据库查询,等两个从数据库返回结果,然后结果相加就可以了。

    数据库访问慢,优化 sql 语句成效不大,做好主从数据库,解决少于 1 亿条数据的表还是可行的,希望我说的能给你帮助。
    jk1030
        10
    jk1030  
       2021-06-11 14:19:55 +08:00
    @Aruforce 这种说法到底从哪里来的 正常而言 in 是会走索引的
    wangxin13g
        11
    wangxin13g  
       2021-06-11 14:42:14 +08:00   ❤️ 2
    @jk1030 某个版本之前的 in 查询是走全表的 然后一堆人看了以前的文章 总是信誓旦旦的说 In 走全表
    QiangZai
        12
    QiangZai  
       2021-06-11 16:05:01 +08:00
    可以想办法用 join 么
    Aruforce
        13
    Aruforce  
       2021-06-11 16:20:31 +08:00
    @jk1030 IN 会遍历链表的全部 。。而不是像大于小于那样会找某一段来遍历
    edk24
        14
    edk24  
    OP
       2021-06-11 16:35:06 +08:00
    @QiangZai 本来一开始是用 jioin 的 但是也很慢
    jk1030
        15
    jk1030  
       2021-06-11 16:46:22 +08:00
    @Aruforce 我的映像中 in 是区间把 虽然参数多可能会有影响 但是除非达到某些条件不然的话不会是全表的 你是什么版本的 mysql
    securityCoding
        16
    securityCoding  
       2021-06-11 16:50:41 +08:00
    我感觉我是个废人了,连表 sql 根本不会写了...
    ZhaoHuiLiu
        17
    ZhaoHuiLiu  
       2021-06-11 16:56:24 +08:00 via Android
    你们别讨论了,再讨论我也觉得好笑了,500 万数据,假如他 ids 是 10 个值,那么就是 5000 万次比较,然而他还有 id_dailu=1 这个比较就是 5500 万次比较,这只是理论最低值计算。又是单线程处理,又要读取硬盘数据,网络传输数据各种开销你觉得查询会少于 1 秒吗。多搞几台从 mysql 数据库服务器,然后多创建几个 mysql 连接发送到这两台服务器上查询结果,把两台数据库结果归总就可以了
    alansfinal
        18
    alansfinal  
       2021-06-11 17:46:10 +08:00
    1. 有没有先开启 mysql query log 确认一下实际执行的 raw query 是什么?可以把实际执行的 SELECT 语句贴上来
    2. 有没有调用 EXPLAIN 和 SHOW PROFILE 看看实际的 query plan 是什么?不能想当然认为是走索引还是全表扫描
    3. $people_ids 是否需要去重?一般有多少个值?如果有很多个的话(极端例子是所有 id 都需要返回)那么肯定是需要全表扫描,慢也正常。解决方法参考 1 楼
    y7E6IG8spV7TU8n7
        19
    y7E6IG8spV7TU8n7  
       2021-06-11 17:58:22 +08:00 via iPhone
    第一个表 admin_id 加索引
    第二个 people 表加联合索引 id,id_dailu 试试?
    ch2
        20
    ch2  
       2021-06-11 17:58:36 +08:00
    in 操作避雷
    young
        21
    young  
       2021-06-11 18:06:08 +08:00
    @codespots #4 99% thinkphp

    id_dailu 估计走全表了
    raaaaaar
        22
    raaaaaar  
       2021-06-11 18:08:30 +08:00 via Android
    SQL,explain 发出来看看
    ibx
        23
    ibx  
       2021-06-11 18:37:34 +08:00
    @codespots 伟大的 thinkphp 框架。
    zhuzhibin
        24
    zhuzhibin  
       2021-06-11 19:27:02 +08:00
    join 数据大 一定会慢吧
    JJstyle
        25
    JJstyle  
       2021-06-11 20:18:39 +08:00 via iPhone
    总的原因在于 in 的 id 太多了,加了索引也没用,提供一个思路:将 ids 排序后按照数量 chunk,比如 50 万一个 chunk,然后每个 chunk 一个异步查询,最终结果求和。
    xuanbg
        26
    xuanbg  
       2021-06-11 21:45:32 +08:00
    @wangxin13g 还有 null 不能索引也一样。。。都是以讹传讹,用旧地图轰炸新目标。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1068 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 19:32 · PVG 03:32 · LAX 11:32 · JFK 14:32
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.