left join 与 not in 语句 调优
本帖最后由 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语句 (ProjectM) 19:58> explainselect 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)
(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 效率低这么多，求解 not in 或者not exist 只需要判断数据在或者不在 返回结果是true or false，但使用left join，需要实际关联每条数据，实际消耗资源更多，而且还会临时表。（通过show profile可以看到）
页:
[1]