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

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

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

[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 确实,本来觉得主键连主键,还只要查主键,应该会很快

[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 快多了