一个sql语句查询的问题

[kingwkb]

表a

aid bid

100 59
100 47
100 22
100 32
200 59
200 22
200 25
300 26
300 36

就这2个字段

要查询出bid=47和22的aid

[alsotang]

@colincat SQL数据库按你这样的设计的话，那不是每次处理相关数据都要取出所有数据并且建立tree啊？

[colincat]

@alsotang 是的 需要这样，但是你可以用这个表构建一个数据结构，预先生成一个tree，只有在有新变动的时候需要重新生成tree，如果他说的是一种频道的话，可能变化应该不是很大吧

[ipconfiger]

（SELECT `aid` FROM `表a` WHERE `bid`=22） INTERSECT （SELECT `aid` FROM `表a` WHERE `bid`=47）

原因，用 `bid`=22 or `bid`=47 这个查询条件 或者 `bid` in (22,47),在mysql里都会丢索引，也就是会产生全表扫描，所以把每个条件单独用字查询取出后用INTERSECT取交集，即可解决

[alsotang]

@ipconfiger 问一下这位牛人..我13L的解答，效率上如何，跟您23楼这个比较的话呢...
(本人SQL处于入门水平)

[kingwkb]

@ipconfiger mysql是不支持INTERSECT的

[ipconfiger]

@kingwkb

可以用IN 子查询来代替，IN 子查询是可以match到索引的
SELECT `aid` FROM `表a` WHERE `bid`=47 AND `aid` IN (SELECT `aid` FROM `表a` WHERE `bid` = 22)

如果不确定数量，这个需求确实没有高效率的SQL可以解决，因为不管怎么优化都会产生临时表和filesort。

@alsotang 都group by了就别说效率了，小表无所谓，上百万的表这么写，访问一上去就等服务器冒烟吧

[kingwkb]

@ipconfiger 恩，看来in的效果还比较好，最起码都是using index, using where，虽然可能子查询多几个

感谢大家的回复

[alsotang]

@ipconfiger 是select aid from a as a1 inner join a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid，没有group by。你要表达的是inner join?

[ipconfiger]

@alsotang 你没跑过你的SQL吧，会报错的，亲。给你修改一下改成
select a1.aid from a as a1 inner join a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid;
这下不报错了，结果返回 Empty set (0.00 sec)
用我的sql实验，返回的是

+------+
| aid |
+------+
| 100 |
+------+
1 row in set (0.00 sec)

我建的测试数据库数据和LZ给出的一致。

explain分析一下你的语句就知道错误在那里了


mysql> EXPLAIN
-> select a1.aid from table_a as a1 inner join table_a as a2 where a1.bid = 47 and a1.bid = 22 and a1.aid = a2.aid;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+


Impossible WHERE

[alsotang]

@ipconfiger 写写指正。还请问，效率呢？对于index的使用呢？

[ipconfiger]

@alsotang 你那条错误的sql就不用纠结效率了，根本的不出正确结果效率是无用的。对于LZ这个命题的效率问题我之前说过了，很难有特别高效的写法，层层嵌套的IN 子查询或者 EXISTS 子查询的方式可以保证不会丢失索引，但是过多潜逃的子查询会产生大量的临时表，且排序的时候如果不是根据bid排序还会产生filesort消耗CPU和内存。修改一下设计，用冗余数据来空间换时间比较好

[napoleonu]

我造了点数据，测试了下。

mysql> show create table d\G
*************************** 1. row ***************************
Table: d
Create Table: CREATE TABLE `d` (
`aid` int(11) DEFAULT NULL,
`bid` int(11) DEFAULT NULL,
KEY `idx1` (`aid`,`bid`),
KEY `idx2` (`bid`,`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from d;
+----------+
| count(*) |
+----------+
| 465005 |
+----------+
1 row in set (0.36 sec)

mysql> select count(*) from (select count(*) from d group by aid,bid) t1;
+----------+
| count(*) |
+----------+
| 465005 |
+----------+
1 row in set (0.29 sec)

mysql> select aid,count(*) from d group by aid;
+------+----------+
| aid | count(*) |
+------+----------+
| 1 | 16640 |
| 2 | 16640 |
| 3 | 16640 |
| 4 | 16640 |
| 5 | 16640 |
| 6 | 33197 |
| 7 | 32448 |
| 11 | 16640 |
| 12 | 16640 |
| 13 | 16640 |
| 14 | 16640 |
| 15 | 16640 |
| 16 | 16640 |
| 17 | 16640 |
| 21 | 16640 |
| 22 | 16640 |
| 23 | 16640 |
| 24 | 16640 |
| 25 | 16640 |
| 26 | 16640 |
| 31 | 16640 |
| 32 | 16640 |
| 33 | 16640 |
| 34 | 16640 |
| 35 | 16640 |
| 36 | 16640 |
+------+----------+
26 rows in set (0.22 sec)

mysql> select count(*) from d where aid=7;
+----------+
| count(*) |
+----------+
| 32448 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from d where aid=6;
+----------+
| count(*) |
+----------+
| 33197 |
+----------+
1 row in set (0.03 sec)

mysql> select sql_no_cache bid from d where aid=6 and bid in (select bid from d where aid=7);
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.12 sec)

mysql> select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.08 sec)

mysql> select sql_no_cache t1.bid from d t1 ,d t2 where t1.aid=6 and t2.aid=7 and t1.bid=t2.bid;
| 331800 |
| 332000 |
| 332200 |
| 332400 |
| 332600 |
| 332800 |
+--------+
18221 rows in set (0.11 sec)

[napoleonu]

select sql_no_cache bid from d where aid=6 and bid in (select bid from d where aid=7);
select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;
select sql_no_cache t1.bid from d t1 ,d t2 where t1.aid=6 and t2.aid=7 and t1.bid=t2.bid;

三条SQL语句都经过反复执行，所消耗的时间也都稳定在如上的时间上。

就这个数据量来说，似乎

select sql_no_cache bid from d where aid in (6,7) group by bid having count(*)=2;

效率最高。

[kingwkb]

@napoleonu 你这个数据分布不好，虽然数据大，但aid, bid分布太少，group by aid和group by aid, bid至少也得达到上千个吧。

[napoleonu]

@kingwkb

group by aid,bid 上千万，也就是数据量在千万级别，我觉得效果没多大区别，如果你现在有上千万数据，跑下不就知道了。

aid 是你上文中说的场景中的 tag，group by aid 上千万，你有上千万的tag？

[kingwkb]

@napoleonu 您看错了吧，我说的是上千，没有“万”字，目前我还没有那么多数据，之后有的话一定跑跑试试

[napoleonu]

@kingwkb 嗷，那也没什么区别。