如何用 SQL 求出「都关注了某100人」和「都收藏了某100帖子」的所有用户？ - 数据库

140

无名啊

@Ta / 2022-08-12 / 样 / 源

@老虎会游泳，SQLAlchemy里一大堆为了优化，而提前预取关联对象的方法（就是各种 JOIN），我头都大了。。
141

无名啊

@Ta / 2022-08-12 / 样 / 源
@老虎会游泳，感觉我理解的链式获取，和你的不一样。。我理解的是：
```
select xxx_id from table_a where id=?;
select yyy_id from table_xxx where id in (...xxx_ids);
select * from table_zzz where id in (...yyy_ids);
```
142

老虎会游泳

@Ta / 2022-08-12 / 样 / 源
@无名啊，嗯，你这种，我在虎绿林里确实是写循环搞定的啊，比如获取每个回复者的用户名，因为
```
$uinfo = new UserInfo;
foreach ($repies as $reply) {
    $uinfo->uid($reply['uid']);
    echo $uinfo->name;
}
```
非常方便，我对构造一个JOIN查询一点兴趣也没有。而且现在只是获取用户名，未来想获取头像呢？你还要往JOIN里加字段，而我只需要在用到头像的地方写$uinfo->avatar()就可以了，岂不是更方便。
143

老虎会游泳

@Ta / 2022-08-12 / 样 / 源

@无名啊，再看看虎绿林API这个用户信息扩展功能，如果不用循环而用JOIN，就需要把每一个可能出现的字段加入每一个可能出现的SQL里，烦不烦就看你自己了。
144

老虎会游泳

@Ta / 2022-08-12 / 样 / 源

@无名啊，不过这也不代表我排斥使用JOIN。如果用起来又快又方便，我当然会用。但现实是往往有比它更方便的解决方法，可笑的是还比它快。所以，虎绿林代码中的JOIN含量很少。

当然，含量不是0，因为有时候确实用JOIN又快又方便，所以我就用。

在觉得JOIN不够方便不够快的时候，我选择使用链式查询、循环、SQL拼接（备注，这不代表我不使用预处理，拼接AND xxx=?也是SQL拼接）。
145

老虎会游泳

@Ta / 2022-08-12 / 样 / 源

@无名啊，当然，这都只是我的个人习惯，是我在开发虎绿林时做出的我认为正确的选择。它不一定适用于其他项目或其他人。
146

无名啊

@Ta / 2022-08-12 / 样 / 源

@老虎会游泳，随机插入好慢啊。。

顺序插入两亿行，只需4分钟

随机插入，我关了日志和必须同步至磁盘，开了 1G 缓存，都非常慢

以后我绝对少用随机插入的东西，比如：UUID

无名啊

@Ta / 2022-08-12 / 样 / 源

@老虎会游泳，哦，我最后还是用 Python 了，因为加上不能重复的功能后，tcc很慢了，范围改大一点儿，更是没眼看

不如直接python：

from datetime import datetime
from random import seed, randint

seed(int(datetime.fromisoformat('2022-08-11 20:00:00').timestamp()))

nums = set()
for uid in range(1, 10000000 + 1):

    nums.clear()
    size = randint(15, 25)

    while len(nums) < size:
        nums.add(randint(1, 10000))

    print('\n'.join(f'{num},{uid}' for num in sorted(nums)))

148

无名啊

@Ta / 2022-08-13 / 样 / 源

@老虎会游泳，先测试了一次SQLite的

测试硬件

移动硬盘：4K 读写 130 IOPS

测试流程

SQLite 建表a、表b，结构都是(num INT, uid INT, PRIMARY KEY(num, uid)) WITHOUT ROWID
Python 为每个表生成并导入约两亿行数据（一千万个uid， num范围[0, 255]）
给每个表喂 1, 2, ..., 10 个（之前未用过）数据，每个方法测试两次（第一次没有缓存，第二次可利用缓存）。如：
- 方法一，a表需要匹配16，b表需匹配88
- 方法一，a表需要匹配16，b表需匹配88（这次会利用操作系统缓存）
- 方法二，a表34，b表127
- 方法二，a表34，b表127（利用缓存）
- 方法一，a表251, 93，b表19, 29
- 方法一，a表251, 93，b表19, 29（缓存）
- ……

方法一

SQL模板：

SELECT count(*)
  FROM (SELECT uid FROM a WHERE num = 10)
  JOIN (SELECT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT uid FROM b WHERE num = 70) USING(uid)

方法二

（见下面代码）

方法三

关系除法。但我测试了一下，3分钟都没结束，直接放弃。。

第一次结果（无缓存）

每个表参数个数	方法一结果个数	方法二结果个数	方法一用时/秒	方法二用时/秒
1	61215	60618	50.37	33.373
2	364	344	78.277	65.338
3	3	1	80.83	98.5
4	0	0	76.423	132.935
5	0	0	76.119	161.369
6	0	0	80.988	195.302
7	0	0	78.959	228.609
8	0	0	80.601	258.55
9	0	0	76.376	143.318
10	0	0	77.137	158.763

第二次结果（操作系统缓存）

每个表参数个数	方法一结果个数	方法二结果个数	方法一用时/s	方法二用时/s
1	61215	60618	0.454	2.753
2	364	344	0.539	3.485
3	3	1	0.521	4.954
4	0	0	0.513	6.599
5	0	0	0.489	8.049
6	0	0	0.497	9.688
7	0	0	0.512	11.084
8	0	0	0.517	12.429
9	0	0	0.502	6.906
10	0	0	0.497	7.516

测试用脚本

DB_FILE='test.db'

seq 0 255 | shuf | for count in {1..10}; do
	
	readarray -n $((count*2)) -t arr
	
	IFS=$'\n'
	for times in {1..2}; do
		echo "Index: $count, Times: $times, Method: 1"
		time sed \
			-e '2,$s/^.*$/JOIN (#&) USING(uid)/' \
			-e '1s/^.*$/SELECT count(*) FROM (#&)/' \
			-e 's/#/SELECT uid FROM # WHERE num = /' \
			-e '1~2s/#/a/; 2~2s/#/b/' <<<"${arr[*]}" |
		sqlite3 -echo "$DB_FILE"
	done
	
	readarray -n "$count" -t arr1
	readarray -n "$count" -t arr2
	
	IFS=','
	for times in {1..2}; do
		echo "Index: $count, Times: $times, Method: 2"
		time sqlite3 -echo "$DB_FILE" <<-EOF
			WITH
				query_a(num) AS (
					SELECT value
					FROM json_each('[${arr1[*]}]')
				),
				
				query_b(num) AS (
					SELECT value
					FROM json_each('[${arr2[*]}]')
				),

				find_a(uid) AS (
					SELECT a.uid
					FROM a
					JOIN query_a USING(num)
					GROUP BY a.uid
					HAVING count(*) = (SELECT count(*) FROM query_a)
				),
				
				find_b(uid) AS (
					SELECT b.uid
					FROM b
					JOIN query_b USING(num)
					GROUP BY b.uid
					HAVING count(*) = (SELECT count(*) FROM query_b)
				)

			SELECT count(*)
			FROM find_a
			JOIN find_b USING(uid)
		EOF
	done
done

提取结果用的正则

re.findall(
    r'^Count: (?P<index>\d+), Times: (?P<times>\d+), SQL: (?P<method>\d+)$.*?'
    r'^(?P<result>\d+)$\s*?'
    r'^real\s*?(?P<min>\d+)m(?P<sec>[\d.]+)s$',
    data,
    re.S | re.M,
)

149

无名啊

@Ta / 2022-08-13 / 样 / 源
@老虎会游泳，反思中。。
- 我索引没建对？（uid, num就全表扫描了啊。。）
- 是SQLite查询计划太垃圾了吗？
- 还是那帮搞学术的，就喜欢搞花里胡哨的东西。。
150

无名啊

@Ta / 2022-08-15 / 样 / 源
@老虎会游泳，MySQL建多值索引，为啥不落盘呢。。
```
CREATE INDEX idx_user_a ON user ((CAST(a AS UNSIGNED ARRAY)));
```
然后MySQL就读了几分钟表，占了5GB内存。。
151

老虎会游泳

@Ta / 2022-08-15 / 样 / 源

@无名啊，方法2是什么，代码折行太多手机看不清。
152

无名啊

@Ta / 2022-08-15 / 样 / 源

@老虎会游泳，方法二类似 96楼

153

老虎会游泳

@Ta / 2022-08-15 / 样 / 源

@无名啊，哦，那你没有测我这个方案呗：

SELECT * FROM 员工信息表 WHERE
        员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能1')
    AND 员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能2')
    AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机1')
    AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机2');

它和

SELECT count(*)
  FROM (SELECT uid FROM a WHERE num = 10)
  JOIN (SELECT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT uid FROM b WHERE num = 70) USING(uid)

不一样，你没有“员工信息表”，而且JOIN和AND IN也不等价。

154

无名啊

@Ta / 2022-08-15 / 样 / 源

@老虎会游泳，对，我只查了uid（个数）
155

无名啊

@Ta / 2022-08-15 / 样 / 源

@老虎会游泳，我没有user表

要不你改改SQL，只查uid（个数）的（按理说，少连一次表，应该更快？）

156

老虎会游泳

@Ta / 2022-08-15 / 样 / 源

@无名啊，为什么我的方案可能会更快？
因为按照「都关注了某100人」和「都收藏了某100帖子」这种数量级，表a和表b的行数应该远多于那个主键唯一的“员工信息表”。所以在“员工信息表”里不断排除范围可能比让其他表互相做JOIN更快。

而且

SELECT * FROM 员工信息表 WHERE
        员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能1')
    AND 员工ID IN (SELECT 员工ID FROM 员工技能表 WHERE 技能 = '技能2')
    AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机1')
    AND 员工ID IN (SELECT 员工ID FROM 员工手机表 WHERE 手机 = '手机2');

和

SELECT * FROM 员工信息表 WHERE
        员工ID IN (SELECT DISTINCT 员工ID FROM 员工技能表 WHERE 技能 = '技能1')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工技能表 WHERE 技能 = '技能2')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工手机表 WHERE 手机 = '手机1')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工手机表 WHERE 手机 = '手机2');

以及

SELECT count(*)
  FROM (SELECT uid FROM a WHERE num = 10)
  JOIN (SELECT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT uid FROM b WHERE num = 70) USING(uid)

和

SELECT count(*)
  FROM (SELECT DISTINCT uid FROM a WHERE num = 10)
  JOIN (SELECT DISTINCT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT DISTINCT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT DISTINCT uid FROM b WHERE num = 70) USING(uid)

之间，可能存在性能差异（取决于优化器聪不聪明）。

157

无名啊

@Ta / 2022-08-15 / 样 / 源

@老虎会游泳，这个SQL是没有必要的？

因为表结构是a(num INT, uid INT, PRIMARY KEY(num, uid))，不会出现 num = 10 时，有多个重复的 uid？自然也没必要DISTINCT？

SELECT count(*)
  FROM (SELECT DISTINCT uid FROM a WHERE num = 10)
  JOIN (SELECT DISTINCT uid FROM b WHERE num = 60) USING(uid)
  JOIN (SELECT DISTINCT uid FROM a WHERE num = 20) USING(uid)
  JOIN (SELECT DISTINCT uid FROM b WHERE num = 70) USING(uid)

补充：

这个SQL应该也是同理

SELECT * FROM 员工信息表 WHERE
        员工ID IN (SELECT DISTINCT 员工ID FROM 员工技能表 WHERE 技能 = '技能1')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工技能表 WHERE 技能 = '技能2')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工手机表 WHERE 手机 = '手机1')
    AND 员工ID IN (SELECT DISTINCT 员工ID FROM 员工手机表 WHERE 手机 = '手机2');

158

老虎会游泳

@Ta / 2022-08-15 / 样 / 源
@无名啊，从当前的数据生成一个user表很简单：
```
CREATE TABLE u(uid int primary key);
INSERT INTO u(uid) SELECT DISTINCT uid FROM a UNION SELECT DISTINCT uid FROM b;
```
159

老虎会游泳

@Ta / 2022-08-15 / 样 / 源

@无名啊，我建议你进行测试，不要做猜想。进行测试并不需要等待多少时间。