mysql - 更有效的 sql 查找不在连接表中的记录？

Question

我有:

_sms_users_
id

_join_smsuser_campaigns_
sms_user_id

我想取出在 join_smsuser_campaigns 表中没有记录的 sms_users(通过 join_smsuser_campaigns.sms_user_id = sms_users.id 关系)

我有 sql:

select * from sms_users where id not in (select sms_user_id from join_smsuser_campaigns);

编辑:

这里是解释选择结果:

mysql> explain select u.* from sms_users u left join join_smsuser_campaigns c on u.id = c.sms_user_id  where c.sms_user_id is null;
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+
| id | select_type | table | type  | possible_keys | key                                                         | key_len | ref  | rows  | Extra                                |
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+
|  1 | SIMPLE      | u     | ALL   | NULL          | NULL                                                        | NULL    | NULL | 42303 |                                      |
|  1 | SIMPLE      | c     | index | NULL          | index_join_smsuser_campaigns_on_campaign_id_and_sms_user_id | 8       | NULL | 30722 | Using where; Using index; Not exists |
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+

mysql> describe sms_users;
+------------------------+------------+------+-----+---------+-------+
| Field                  | Type       | Null | Key | Default | Extra |
+------------------------+------------+------+-----+---------+-------+
| id                     | int(11)    | NO   | PRI | NULL    |       |

mysql> describe join_smsuser_campaigns;
+---------------------+------------------+------+-----+---------+-------+
| Field               | Type             | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| sms_user_id         | int(11)          | NO   |     | NULL    |       |

看起来问题是我在连接时没有 sms_user_id 的索引？

运行大约需要 5 分钟，我看到有一条记录。有没有更有效的方法通过连接来做到这一点？我的 sql 技能很基础。

Answer 1

当 IN 子句中有很多项时，查询会变慢。使用 left join 代替

select u.*
from sms_users u
left join join_smsuser_campaigns c on u.id = c.sms_user_id 
where c.sms_user_id is null

参见 this great join explanation