› 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
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
这是一个创建于 638 天前的主题，其中的信息可能已经有所发展或是发生改变。
机器环境
- 腾讯云
- 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
+----+-------------+-----------+--------+---------------------------------+---------+---------+------------------------+----------+-------------+
| 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
 |
1
uiosun 2023-02-09 13:09:14 +08:00  1
必须要一句 SQL 搞定吗？一句话搞定的话，感觉超难……
|
 |
2
lovelylain 2023-02-09 13:09:47 +08:00 via Android 1
字符串也可以排序比较大小的，你这里是主键，也不用考虑重复和索引问题，按你那个 id > xxx limit 做就行。
|
 |
3
foolishcrab 2023-02-09 13:10:23 +08:00 via iPhone 1
Sql server 试试 interset, mysql 全拿出来在内存里算吧还是
|
 |
6
ccagml OP @moqizhengz 没权限换数据库😂, MySQl 怎么做也不清楚,就是 io 满了,也没有加配置的空间
|
 |
7
xsonglive491 2023-02-09 13:58:45 +08:00 1
你应该先 explain 下你的查询语句看是什么问题
|
 |
8
thinkershare 2023-02-09 14:05:08 +08:00 1
没啥原因，MySQL 数据库过亿了就是慢。直接用子程序算了。先分别独立查询，看看独立查询费事多久。你这个过滤后大概会命中多少数据嘛？另外就像楼上说的，先 explain 一下，看看命中的索引信息。字符串肯定一样可以比较大小，为啥会不能比较大小呢？另外看看你字符串上使用的 collate 信息。
|
 |
9
LeegoYih 2023-02-09 14:15:37 +08:00 1
求交集有过滤条件吗？如果有过滤条件且数量少直接 join 是没问题的。
如果是全量匹配，那只用代码一批一批查，没办法做到实时。 |
 |
10
liprais 2023-02-09 14:18:14 +08:00
取出来之后要干啥?
要是只要其中一张表的数据可以用 exists |
 |
11
opengps 2023-02-09 14:40:38 +08:00
首先，硬盘慢。你用的云服务器，自带的硬盘，这个硬盘是虚拟化之后的，本身 io 就严重打折。
其次，索引大。你的 id 本身 30 字节，结合 1 亿，8000 万，这意味着这两个主键索引就得 54 亿字节（约 3GB ） 结论是：可以考虑下，分别把两个 id 拷贝到 2 个内存表，然后对两个内存表进行 INNER join 。看看效率提升多少 |
 |
13
starrys 2023-02-09 15:23:24 +08:00 via Android 1
观察一下两表的 id 的分布规律，分割成 10~50 次查询，再将结果 union 起来。
|
 |
14
weidaizi 2023-02-09 16:30:17 +08:00 1
别用数据库来做这种偏计算类的工作，用个 python 查一下两张表的 id 字段回来，在程序里做对比就好了
|
|
15
ccagml OP |
|
21
thinkershare 2023-02-09 17:09:09 +08:00
@ccagml 你这属于在 A 表上做全表扫描，怎么会块，慢是理所当然的。
|
|
23
thinkershare 2023-02-09 17:12:05 +08:00 1
A 表的主键还是 varchar(30)这种，主键一般不是用 char(30)这种就能搞定了吗，难道还要处理中文和可变长度。你这个 2 张表数据都差不多了，至少需要扫描 A 表 /或者 B 表一次，因为 a/b 表没用其它外键关联索引。
|
 |
24
Maboroshii 2023-02-09 17:19:49 +08:00 1
写脚本， 循环从 a 查所有的 id ， 然后 select id from b where id in (...) 放在后台跑就行了
|
 |
25
dusu 2023-02-09 19:00:02 +08:00 via iPhone 1
既然内存够 把 id 都存 redis 用 sinter
|
|
26
ccagml OP @dusu 长见识了,看着 sinter 的实现是 取最小集合 然后 检查其他集合是否有相同的 key,感觉一个 8000W 的循环是避免不了的, 还涉及到搭建 redis 环境问题,感觉可能不如自己写脚本去重
|
|
27
ccagml OP @Maboroshii 确实后台跑批可以解决
|
|
28
ccagml OP @thinkershare 感觉就是 2 个表检查相同值,一个 O(n)是避免不了的
|
 |
29
encro 2023-02-09 20:06:02 +08:00 1
你看的不是我这个吧： https://c4ys.com/archives/2349
你这个必须临时表，而且查询集是 n*m 而不是 n+m. 你这个需求有点奇怪，没有 where ，所以没法缩小结果集，只能都跑全表。 |
 |
30
my3157 2023-02-09 20:07:09 +08:00
试试 exsits
|
|
31
encro 2023-02-09 20:11:22 +08:00
多表关联查询重要的就两条：
1, 先查结果集少的表，再查大的；（通常先查索引稀疏且分布均匀的） 2 , 索引必须用上，且尽量用主键关联； 优化也是三板斧： 1,show processlist 2,explain 3,slow query |
|
32
ccagml OP @encro 哈，不是看到那个，想要做的一开始已经写了，数据有问题，想看看有哪些数据错了，所以没有查询范围
也已经是主键关联了，你看 explain |
|
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 是不是更快。 |
 |
34
xuelu520 2023-02-10 10:09:39 +08:00 1
id 都取出来扔 redis ，然后一个个去查呗
|
|
35
ccagml OP @xuelu520 这种一次性的小需求，感觉搭一套 redis 环境，加导出数据，感觉并不是很好的选择
|
|
36
xsonglive491 2023-02-10 12:51:01 +08:00
@ccagml 要是一次性需求你还是写脚本计算吧
|
 |
37
512357301 2023-02-12 13:40:35 +08:00 via Android
本地跑个 clickhouse ，然后把关键的字段同步到本地，在本地分析，速度会快很多，clickhouse 是列式数据库，速度比 mysql 快多了
|
Select Language