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
0clickjacking0
V2EX  ›  MySQL

关于 mysql 两表查询,三列字段相等的一个问题

  •  
  •   0clickjacking0 · 2020-10-02 22:33:23 +08:00 · 3465 次点击
    这是一个创建于 1502 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前情况是这样的,我有两张表,分别为 t1 和 t2,t1 中有字段( id,a,b,c ),t2 中有字段(name,a,b,c),想让这两表中的 a,b,c 字段相等时,查询出 id 和 name,我的查询语句如下select id,name from t1,t2 where(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c),但是一直查不出来,也尝试过先把 a 字段相等的查到一张表上,但是数据量实在太大了,表直接满了,报错了,各位大师傅有啥办法吗

    16 条回复    2020-10-04 20:16:57 +08:00
    xyjincan
        1
    xyjincan  
       2020-10-02 23:09:46 +08:00 via Android
    a,b,c where 顺序按表内重复数据量从小到大来,建立索引,这两个表还是合并吧
    clf
        2
    clf  
       2020-10-02 23:34:02 +08:00
    试试连接?
    select t1.id,t2.name from t1 inner join t2 on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c;

    不过其实最好还是弄个 t3 表,数据结构是 id,name,a,b,c 把两个表数据合并了最方便。
    0clickjacking0
        3
    0clickjacking0  
    OP
       2020-10-02 23:38:38 +08:00
    @lychs1998 我的思路是这样的,先把 a 相等的做一张临时表,然后在这张临时表中去查询 b 相等的,然后去查询 c 相等的,但是这样好像有问题,查不出来
    0clickjacking0
        4
    0clickjacking0  
    OP
       2020-10-02 23:38:48 +08:00
    @xyjincan 我的思路是这样的,先把 a 相等的做一张临时表,然后在这张临时表中去查询 b 相等的,然后去查询 c 相等的,但是这样好像有问题,查不出来
    bowser1701
        5
    bowser1701  
       2020-10-02 23:42:24 +08:00 via iPhone
    @0clickjacking0 用连接不行吗?感觉你这种思路也不会比连接高效。
    KomiSans
        6
    KomiSans  
       2020-10-03 08:40:05 +08:00
    select b01.id as id , b02.name as name from b1 b01 INNER JOIN b2 b02 ON b01.a = b02.a and b01.b = b02.b and b01.c = b02.c; ???
    lpts007
        7
    lpts007  
       2020-10-03 09:24:36 +08:00 via Android
    想求楼主个事
    能把表数据量,数据分布描述一下吗
    abc 重的多了肯定查不出来,你自己打算怎么办
    0clickjacking0
        8
    0clickjacking0  
    OP
       2020-10-03 09:49:38 +08:00
    @lpts007 表 1 大概 15 万条,表 2 大约 4900 条,就目前万想要的结果是 t1.a=t2.a and t1.b=t2.b and t1.c=t2.c 同时满足,但是查出来的貌似是笛卡尔积,有些数据有问题的,我去试试自然连接行不行,如果不行的话,考虑用 py 了,因为数据量也没有达到上百万的级别,用 py 也是无奈之举
    wangritian
        9
    wangritian  
       2020-10-03 10:08:32 +08:00
    t2 作主表(总行数少的),left join t1,t1 创建联合索引 a,b,c
    也可以增加字段计算 a,b,c 的哈希,对哈希字段创建索引,查询时不用管 a,b,c 了
    0clickjacking0
        10
    0clickjacking0  
    OP
       2020-10-03 10:26:53 +08:00
    @wangritian 那我是不是可以对 t1 和 t2 的 a,b,c 字段都计算哈希,然后直接去比较他们的哈希就行了,这样就变成了比较一个字段
    lpts007
        11
    lpts007  
       2020-10-03 12:40:53 +08:00
    @0clickjacking0
    “但是查出来的貌似是笛卡尔积” 那就对了,说明存在大量 abc 相等的数据
    假设
    表 t1
    |id | a| b| c|
    |1| a1|b1|c1|
    |2| a1|b1|c1|
    |3| a3|b3|c3|

    表 t2
    |name | a| b| c|
    |n1| a1|b1|c1|
    |n2| a1|b1|c1|
    |n3| a1|b1|c1|
    |n4| a3|b3|c3|

    结果会有 2 x 3 +1 x 1 = 7 条。那你的预期是不是查出 7 条呢?
    abc 重复记录越多,数据条数越多。
    你对俩表数据分布没有大体概念吗.



    我半瓶子水。
    lpts007
        12
    lpts007  
       2020-10-03 12:47:20 +08:00
    至于 hash 成一个字段再原样查——如果结果跟之前不一样的话,要么 hash 碰撞,要么 mysql 的 bug 。
    zhangysh1995
        13
    zhangysh1995  
       2020-10-03 14:00:38 +08:00
    九楼靠谱 @wangritian
    楼主可能要给出表结构会更好一些。另外,如果 a,b,c 是组合外键好说,不是的话,为啥 name 没有也放在 t1 里面?感觉有些许奇怪。从查询来说,需要给 t2 加组合索引 (a,b,c) ,inner join 和 left join 区别不大,都是 index scan
    vZexc0m
        14
    vZexc0m  
       2020-10-04 16:41:46 +08:00 via Android
    试试先求和,然后对和做索引。然后缩小范围再查
    PopRain
        15
    PopRain  
       2020-10-04 20:12:54 +08:00
    select distinct id,name from t1,t2 where(t1.a=t2.a and t1.b=t2.b and t1.c=t2.c)

    你这个数据量一点都不大,查不出来说明满足条件的记录太多太多了。。。。 试试消重
    wangritian
        16
    wangritian  
       2020-10-04 20:16:57 +08:00
    @0clickjacking0 是的,这是哈希的作用之一,但不排除有哈希碰撞概率,在超大数据量时,哈希相等,还需要对应的 n 个字段也分别相等
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1456 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 23:51 · PVG 07:51 · LAX 15:51 · JFK 18:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.