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

看到有人发 MySql 必知必会,想请教一个问题

  •  1
     
  •   ccagml · 2023-02-09 12:59:50 +08:00 · 3345 次点击
    这是一个创建于 413 天前的主题,其中的信息可能已经有所发展或是发生改变。

    机器环境

    • 腾讯云
    • centos7
    • 4 核 16G 内存

    表结构

    CREATE TABLE `a` (
    `id` varchar(30) NOT NULL,
    PRIMARY KEY (`id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    CREATE TABLE `b` (
    `id` varchar(30) NOT NULL,
    PRIMARY KEY (`id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    • a 表有 1 亿数据
    • b 表有 8000W 数据

    想要做的

    • 想要查询出两个表 id 有交集的数据(因为有遇到问题,确实有部分数据重复,数量应该不大,可能几百条)

    我尝试过

    SELECT a.id FROM a INNER join b on a.id = b.id;
    
    • 遇到的问题是 io 跑满了, 然后很久也没有出结果(可能有 20 分钟)

    id 是字符串好像也没办法写代码做类似以下操作?

    • a.id > xxx limit 1000;
    • 用上一步的结果 查询在 b 中是否存在

    请问还有什么办法可以查询出两个表有交集的部分的数据吗

    第 1 条附言  ·  2023-02-09 16:50:45 +08:00
    mysql> explain SELECT a.id FROM a INNER join b on a.id = b.id;
    +----+-------------+-----------+--------+---------------------------------+---------+---------+------------------------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-----------+--------+---------------------------------+---------+---------+------------------------+----------+-------------+
    | 1 | SIMPLE | b | index | PRIMARY,b_id_index | id2 | 4 | NULL | 88343401 | Using index |
    | 1 | SIMPLE | a | eq_ref | PRIMARY,a_id_index | PRIMARY | 92 | db.b.id | 1 | Using index |
    +----+-------------+-----------+--------+---------------------------------+---------+---------+------------------------+----------+-------------+
    2 rows in set (0.01 sec)

    mysql> SELECT VERSION();
    +------------+
    | VERSION() |
    +------------+
    | 5.1.73-log |
    +------------+
    1 row in set (0.00 sec)


    CREATE TABLE `a` (
    `id` varchar(30) NOT NULL,
    `id2` int(12) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id2` (`id2`),
    KEY `a_id_index` (`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8

    CREATE TABLE `b` (
    `id` varchar(30) NOT NULL,
    `id2` int(12) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id2` (`id2`),
    KEY `b_id_index` (`id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8

    机器空闲内存:8.6G
    37 条回复    2023-02-12 13:40:35 +08:00
    uiosun
        1
    uiosun  
       2023-02-09 13:09:14 +08:00   ❤️ 1
    必须要一句 SQL 搞定吗?一句话搞定的话,感觉超难……
    lovelylain
        2
    lovelylain  
       2023-02-09 13:09:47 +08:00 via Android   ❤️ 1
    字符串也可以排序比较大小的,你这里是主键,也不用考虑重复和索引问题,按你那个 id > xxx limit 做就行。
    foolishcrab
        3
    foolishcrab  
       2023-02-09 13:10:23 +08:00 via iPhone   ❤️ 1
    Sql server 试试 interset, mysql 全拿出来在内存里算吧还是
    ccagml
        4
    ccagml  
    OP
       2023-02-09 13:12:20 +08:00
    @uiosun 不必须一句话呀,也可以写程序,按 2 楼的说话可以比较大小,我之前以为不行
    ccagml
        5
    ccagml  
    OP
       2023-02-09 13:12:39 +08:00
    @jobmailcn 这样嘛,谢谢大佬
    ccagml
        6
    ccagml  
    OP
       2023-02-09 13:15:08 +08:00
    @moqizhengz 没权限换数据库😂, MySQl 怎么做也不清楚,就是 io 满了,也没有加配置的空间
    xsonglive491
        7
    xsonglive491  
       2023-02-09 13:58:45 +08:00   ❤️ 1
    你应该先 explain 下你的查询语句看是什么问题
    thinkershare
        8
    thinkershare  
       2023-02-09 14:05:08 +08:00   ❤️ 1
    没啥原因,MySQL 数据库过亿了就是慢。直接用子程序算了。先分别独立查询,看看独立查询费事多久。你这个过滤后大概会命中多少数据嘛?另外就像楼上说的,先 explain 一下,看看命中的索引信息。字符串肯定一样可以比较大小,为啥会不能比较大小呢?另外看看你字符串上使用的 collate 信息。
    LeegoYih
        9
    LeegoYih  
       2023-02-09 14:15:37 +08:00   ❤️ 1
    求交集有过滤条件吗?如果有过滤条件且数量少直接 join 是没问题的。
    如果是全量匹配,那只用代码一批一批查,没办法做到实时。
    liprais
        10
    liprais  
       2023-02-09 14:18:14 +08:00
    取出来之后要干啥?
    要是只要其中一张表的数据可以用 exists
    opengps
        11
    opengps  
       2023-02-09 14:40:38 +08:00
    首先,硬盘慢。你用的云服务器,自带的硬盘,这个硬盘是虚拟化之后的,本身 io 就严重打折。
    其次,索引大。你的 id 本身 30 字节,结合 1 亿,8000 万,这意味着这两个主键索引就得 54 亿字节(约 3GB )

    结论是:可以考虑下,分别把两个 id 拷贝到 2 个内存表,然后对两个内存表进行 INNER join 。看看效率提升多少
    opengps
        12
    opengps  
       2023-02-09 14:43:21 +08:00
    @opengps #11 少说了一句,注意下你的 16G 内存剩下多少空闲,需要至少大于 3G
    starrys
        13
    starrys  
       2023-02-09 15:23:24 +08:00 via Android   ❤️ 1
    观察一下两表的 id 的分布规律,分割成 10~50 次查询,再将结果 union 起来。
    weidaizi
        14
    weidaizi  
       2023-02-09 16:30:17 +08:00   ❤️ 1
    别用数据库来做这种偏计算类的工作,用个 python 查一下两张表的 id 字段回来,在程序里做对比就好了
    ccagml
        15
    ccagml  
    OP
       2023-02-09 16:52:22 +08:00
    @xsonglive491 大佬 explain 加了
    @thinkershare 以为字符串比较 会 出现 "2" > "11" 这种, id 长度不一样长
    ccagml
        16
    ccagml  
    OP
       2023-02-09 16:52:40 +08:00
    @LeegoYih 全量匹配
    ccagml
        17
    ccagml  
    OP
       2023-02-09 16:57:36 +08:00
    @liprais 主键冲突,需要捞出数据看哪条是对的,两个表除了主键,其他内容都不一样
    liprais
        18
    liprais  
       2023-02-09 16:58:14 +08:00
    @ccagml 忍着吧,mysql 就这性能
    ccagml
        19
    ccagml  
    OP
       2023-02-09 17:00:22 +08:00
    @starrys id 没有规律,可长可短,自定义的
    ccagml
        20
    ccagml  
    OP
       2023-02-09 17:03:42 +08:00
    @weidaizi 确实,本来觉得主键连主键,还只要查主键,应该会很快
    thinkershare
        21
    thinkershare  
       2023-02-09 17:09:09 +08:00
    @ccagml 你这属于在 A 表上做全表扫描,怎么会块,慢是理所当然的。
    ccagml
        22
    ccagml  
    OP
       2023-02-09 17:10:44 +08:00 via Android
    @opengps io 确实差,内存是够的,不好搞这种,等下告警又得挨批
    thinkershare
        23
    thinkershare  
       2023-02-09 17:12:05 +08:00   ❤️ 1
    A 表的主键还是 varchar(30)这种,主键一般不是用 char(30)这种就能搞定了吗,难道还要处理中文和可变长度。你这个 2 张表数据都差不多了,至少需要扫描 A 表 /或者 B 表一次,因为 a/b 表没用其它外键关联索引。
    Maboroshii
        24
    Maboroshii  
       2023-02-09 17:19:49 +08:00   ❤️ 1
    写脚本, 循环从 a 查所有的 id , 然后 select id from b where id in (...) 放在后台跑就行了
    dusu
        25
    dusu  
       2023-02-09 19:00:02 +08:00 via iPhone   ❤️ 1
    既然内存够 把 id 都存 redis 用 sinter
    ccagml
        26
    ccagml  
    OP
       2023-02-09 19:28:26 +08:00
    @dusu 长见识了,看着 sinter 的实现是 取最小集合 然后 检查其他集合是否有相同的 key,感觉一个 8000W 的循环是避免不了的, 还涉及到搭建 redis 环境问题,感觉可能不如自己写脚本去重
    ccagml
        27
    ccagml  
    OP
       2023-02-09 19:29:13 +08:00
    @Maboroshii 确实后台跑批可以解决
    ccagml
        28
    ccagml  
    OP
       2023-02-09 19:30:07 +08:00
    @thinkershare 感觉就是 2 个表检查相同值,一个 O(n)是避免不了的
    encro
        29
    encro  
       2023-02-09 20:06:02 +08:00   ❤️ 1
    你看的不是我这个吧: https://c4ys.com/archives/2349

    你这个必须临时表,而且查询集是 n*m 而不是 n+m.

    你这个需求有点奇怪,没有 where ,所以没法缩小结果集,只能都跑全表。
    my3157
        30
    my3157  
       2023-02-09 20:07:09 +08:00
    试试 exsits
    encro
        31
    encro  
       2023-02-09 20:11:22 +08:00
    多表关联查询重要的就两条:

    1, 先查结果集少的表,再查大的;(通常先查索引稀疏且分布均匀的)
    2 , 索引必须用上,且尽量用主键关联;


    优化也是三板斧:

    1,show processlist
    2,explain
    3,slow query
    ccagml
        32
    ccagml  
    OP
       2023-02-09 20:16:24 +08:00 via Android
    @encro 哈,不是看到那个,想要做的一开始已经写了,数据有问题,想看看有哪些数据错了,所以没有查询范围
    也已经是主键关联了,你看 explain
    encro
        33
    encro  
       2023-02-09 20:23:51 +08:00
    没看备注:

    | 1 | SIMPLE | b | index | PRIMARY,b_id_index | id2 | 4 | NULL | 88343401 | Using index |

    关键就在这里,用了 id2 全表扫描了。

    你试一下用 exist 是不是更快。
    xuelu520
        34
    xuelu520  
       2023-02-10 10:09:39 +08:00   ❤️ 1
    id 都取出来扔 redis ,然后一个个去查呗
    ccagml
        35
    ccagml  
    OP
       2023-02-10 12:20:12 +08:00 via Android
    @xuelu520 这种一次性的小需求,感觉搭一套 redis 环境,加导出数据,感觉并不是很好的选择
    xsonglive491
        36
    xsonglive491  
       2023-02-10 12:51:01 +08:00
    @ccagml 要是一次性需求你还是写脚本计算吧
    512357301
        37
    512357301  
       2023-02-12 13:40:35 +08:00 via Android
    本地跑个 clickhouse ,然后把关键的字段同步到本地,在本地分析,速度会快很多,clickhouse 是列式数据库,速度比 mysql 快多了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2930 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 00:31 · PVG 08:31 · LAX 17:31 · JFK 20:31
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.