MySQL同表多条件匹配查询room_id，CodeIgniter多join性能差如何优化

问题原因 #

你原来的写法逻辑错误，多个where条件是作用在同一行数据上的，不可能存在一行数据的user_id同时等于23、45、56，自然查不到结果。多次JOIN的方案在匹配用户数多的时候性能指数级下降，不适合大表场景。

高效实现方案 #

用GROUP BY + HAVING的聚合查询方案，仅需单次表扫描即可完成需求，匹配10个甚至更多user_id时逻辑也无需调整，性能远高于多JOIN方案。
原生SQL逻辑如下：

SELECT room_id
FROM 你的表名
WHERE del = 0 
  AND user_id IN (23,45,56)
GROUP BY room_id
HAVING COUNT(DISTINCT user_id) = 3;

这里的3就是你需要匹配的用户数量，对应IN列表里的用户总数，如果你的业务里同一个room_id不会有重复的user_id记录，可以把COUNT(DISTINCT user_id)简化成COUNT(*)性能更好。

对应CodeIgniter代码实现 #

// 要匹配的用户列表
$matchUserIds = [23,45,56];
$userCount = count($matchUserIds);

$result = $this->db->select('room_id')
    ->from('你的表名')
    ->where('del', 0)
    ->where_in('user_id', $matchUserIds)
    ->group_by('room_id')
    ->having('COUNT(DISTINCT user_id) =', $userCount)
    // 如果无重复user_id可以改成下面的写法性能更好
    // ->having('COUNT(*) =', $userCount)
    ->get()
    ->result_array();

性能优化建议 #

给表添加联合索引 idx_room_user_del(room_id, user_id, del)，该查询可以直接命中索引，百万级大表也可以毫秒级返回结果。

内容的提问来源于stack exchange，提问作者Charas