看到有人发 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 中是否存在

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

[thinkershare]

@ccagml 你这属于在 A 表上做全表扫描，怎么会块，慢是理所当然的。

[ccagml]

@opengps io 确实差，内存是够的，不好搞这种，等下告警又得挨批

[thinkershare]

A 表的主键还是 varchar(30)这种，主键一般不是用 char(30)这种就能搞定了吗，难道还要处理中文和可变长度。你这个 2 张表数据都差不多了，至少需要扫描 A 表 /或者 B 表一次，因为 a/b 表没用其它外键关联索引。

[Maboroshii]

写脚本， 循环从 a 查所有的 id ， 然后 select id from b where id in (...) 放在后台跑就行了

[dusu]

既然内存够 把 id 都存 redis 用 sinter

[ccagml]

@dusu 长见识了,看着 sinter 的实现是 取最小集合 然后 检查其他集合是否有相同的 key,感觉一个 8000W 的循环是避免不了的, 还涉及到搭建 redis 环境问题,感觉可能不如自己写脚本去重

[ccagml]

@Maboroshii 确实后台跑批可以解决

[ccagml]

@thinkershare 感觉就是 2 个表检查相同值,一个 O(n)是避免不了的

[encro]

你看的不是我这个吧： https://c4ys.com/archives/2349

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

你这个需求有点奇怪，没有 where ，所以没法缩小结果集，只能都跑全表。

[my3157]

试试 exsits

[encro]

多表关联查询重要的就两条：

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


优化也是三板斧：

1,show processlist
2,explain
3,slow query

[ccagml]

@encro 哈，不是看到那个，想要做的一开始已经写了，数据有问题，想看看有哪些数据错了，所以没有查询范围
也已经是主键关联了，你看 explain

[encro]

没看备注：

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

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

你试一下用 exist 是不是更快。

[xuelu520]

id 都取出来扔 redis ，然后一个个去查呗

[ccagml]

@xuelu520 这种一次性的小需求，感觉搭一套 redis 环境，加导出数据，感觉并不是很好的选择

[xsonglive491]

@ccagml 要是一次性需求你还是写脚本计算吧

[512357301]

本地跑个 clickhouse ，然后把关键的字段同步到本地，在本地分析，速度会快很多，clickhouse 是列式数据库，速度比 mysql 快多了