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

[ccagml]

机器环境

• 腾讯云
• 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 中是否存在

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

[uiosun]

必须要一句 SQL 搞定吗？一句话搞定的话，感觉超难……

[lovelylain]

字符串也可以排序比较大小的，你这里是主键，也不用考虑重复和索引问题，按你那个 id > xxx limit 做就行。

[foolishcrab]

Sql server 试试 interset, mysql 全拿出来在内存里算吧还是

[ccagml]

@uiosun 不必须一句话呀,也可以写程序,按 2 楼的说话可以比较大小,我之前以为不行

[ccagml]

@jobmailcn 这样嘛,谢谢大佬

[ccagml]

@moqizhengz 没权限换数据库😂, MySQl 怎么做也不清楚,就是 io 满了,也没有加配置的空间

[xsonglive491]

你应该先 explain 下你的查询语句看是什么问题

[thinkershare]

没啥原因，MySQL 数据库过亿了就是慢。直接用子程序算了。先分别独立查询，看看独立查询费事多久。你这个过滤后大概会命中多少数据嘛？另外就像楼上说的，先 explain 一下，看看命中的索引信息。字符串肯定一样可以比较大小，为啥会不能比较大小呢？另外看看你字符串上使用的 collate 信息。

[LeegoYih]

求交集有过滤条件吗？如果有过滤条件且数量少直接 join 是没问题的。
如果是全量匹配，那只用代码一批一批查，没办法做到实时。

[liprais]

取出来之后要干啥?
要是只要其中一张表的数据可以用 exists

[opengps]

首先，硬盘慢。你用的云服务器，自带的硬盘，这个硬盘是虚拟化之后的，本身 io 就严重打折。
其次，索引大。你的 id 本身 30 字节，结合 1 亿，8000 万，这意味着这两个主键索引就得 54 亿字节（约 3GB ）

结论是：可以考虑下，分别把两个 id 拷贝到 2 个内存表，然后对两个内存表进行 INNER join 。看看效率提升多少

[opengps]

@opengps #11 少说了一句，注意下你的 16G 内存剩下多少空闲，需要至少大于 3G

[starrys]

观察一下两表的 id 的分布规律，分割成 10~50 次查询，再将结果 union 起来。

[weidaizi]

别用数据库来做这种偏计算类的工作，用个 python 查一下两张表的 id 字段回来，在程序里做对比就好了

[ccagml]

@xsonglive491 大佬 explain 加了
@thinkershare 以为字符串比较 会 出现 "2" > "11" 这种, id 长度不一样长

[ccagml]

@LeegoYih 全量匹配

[ccagml]

@liprais 主键冲突,需要捞出数据看哪条是对的,两个表除了主键,其他内容都不一样

[liprais]

@ccagml 忍着吧，mysql 就这性能

[ccagml]

@starrys id 没有规律,可长可短,自定义的

[ccagml]

@weidaizi 确实,本来觉得主键连主键,还只要查主键,应该会很快