- 论坛徽章:
- 9
|
本帖最后由 stupid_lee 于 2015-12-03 20:10 编辑
有两张表
a表 t_char_basic 字段c_cid 为主键 10万条数据
b表 t_uid_sid_map 字段c_uid,c_cid 为联合主键 10万条数据
现在需要找出在a表中出现,但不在b表中出现的cid,分别使用如下sql语句- [root@localhost] (ProjectM) 19:58> explain select c_uid,c_cid from t_char_basic where c_cid not in (select distinct c_cid from t_uid_sid_map);
- +----+-------------+---------------+-------+---------------+-------+---------+------+--------+--------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+---------------+-------+---------------+-------+---------+------+--------+--------------------------+
- | 1 | PRIMARY | t_char_basic | index | NULL | c_uid | 4 | NULL | 126173 | Using where; Using index |
- | 2 | SUBQUERY | t_uid_sid_map | ALL | NULL | NULL | NULL | NULL | 108049 | NULL |
- +----+-------------+---------------+-------+---------------+-------+---------+------+--------+--------------------------+
- 2 rows in set (0.00 sec)
- [root@localhost] (ProjectM) 19:59> explain select a.c_uid,a.c_cid from t_char_basic a left join t_uid_sid_map b on a.c_cid=b.c_cid where b.c_cid is null;
- +----+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------------------+
- | 1 | SIMPLE | a | index | NULL | c_uid | 4 | NULL | 126173 | Using index |
- | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 108049 | Using where; Not exists; Using join buffer (Block Nested Loop) |
- +----+-------------+-------+-------+---------------+-------+---------+------+--------+----------------------------------------------------------------+
- 2 rows in set (0.00 sec)
复制代码 看起来扫描的行都差不多,但是我执行第一条sql语句0.23秒就出来结果了
第二条 left join 卡了很久,几乎5分钟才出来结果
show processlist 显示 一直在sending data ,不知道为什么left join 比 not in 效率低这么多,求解 |
|