如何解决多表关联查询用户最后修改记录的MySQL超时问题

我来帮你搞定这个查询问题！你的需求是获取每个符合条件的用户最后修改的Pin记录，同时解决查询超时的问题，对吧？咱们一步步拆解优化：

一、原查询的核心问题 #

• 结果不正确：直接用GROUP BY t1.user_id但没指定取最后修改的记录，MySQL在默认配置下会随机返回该用户的某一条Pin，完全不是你要的“最后修改”那条。
• 性能低下：直接关联30万条记录的Pin表，且没有合适的索引支撑，当用户有大量Pin时，全表扫描+分组操作会拖慢整个查询，甚至超时。

二、正确的查询逻辑：先锁定每个用户的最后修改Pin #

首先得确认Pin表中记录最后修改时间的字段（假设是modified_at，如果你的字段名是updated_at之类的，直接替换即可）。我们需要先通过子查询找到每个符合条件用户的最后修改Pin的时间/ID，再关联回主表获取完整数据。

原生SQL参考示例 #

SELECT 
    t1.id as pin_id, 
    t1.id, 
    t1.user_id, 
    t1.title, 
    t1.image, 
    t1.description, 
    t1.profession_tag, 
    t1.industry_tag, 
    t1.collaborators, 
    t2.title as board_title, 
    t3.email, 
    t3.username, 
    t3.firstname
FROM pin t1
LEFT JOIN board t2 ON t1.board_id = t2.id
LEFT JOIN user t3 ON t1.user_id = t3.id
-- 子查询：精准定位每个用户最后修改的Pin
INNER JOIN (
    SELECT user_id, MAX(modified_at) AS last_modified_time
    FROM pin
    WHERE status = 1
    GROUP BY user_id
) AS last_pins 
    ON t1.user_id = last_pins.user_id 
    AND t1.modified_at = last_pins.last_modified_time
WHERE 
    t3.status = 1 
    AND t3.hidden_profile = 1 
    AND t1.status = 1
GROUP BY t1.user_id -- 确保每个用户只返回一条结果
LIMIT {limit}, {start};

如果你的Pin表id是自增的，且修改时间越晚的Pin ID越大，也可以用MAX(id)代替MAX(modified_at)，这样查询效率可能更高。

三、性能优化关键：添加针对性索引 #

查询慢的核心原因是缺少合适的索引，给以下表创建联合索引：

1. Pin表：创建(user_id, status, modified_at, id)联合索引，子查询可以直接通过索引获取每个用户的最后修改时间，无需扫描全表：

CREATE INDEX idx_pin_user_status_modified ON pin(user_id, status, modified_at, id);

如果用ID判断最后修改，就建(user_id, status, id)索引。

2. Board表：创建(id, title)覆盖索引，关联时无需回表查询额外数据：

CREATE INDEX idx_board_id_title ON board(id, title);

3. User表：创建(id, status, hidden_profile, email, username, firstname)覆盖索引，过滤和查询用户信息时直接用索引完成：

CREATE INDEX idx_user_status_hidden_profile ON user(id, status, hidden_profile, email, username, firstname);

四、适配CodeIgniter的代码实现 #

把上面的逻辑转换成CodeIgniter查询构造器的写法：

// 先构建子查询：获取每个用户最后修改Pin的时间
$subquery = $this->db->select('user_id, MAX(modified_at) as last_modified_time')
                     ->from('pin')
                     ->where('status', 1)
                     ->group_by('user_id')
                     ->get_compiled_select();

// 主查询关联子查询
$this->db->select("t1.id as pin_id, t1.id, t1.user_id, `t1.title`, t1.image, t1.description, t1.profession_tag, t1.industry_tag, t1.collaborators, `t2.title` as board_title, t3.email, t3.username, t3.firstname");
$this->db->from("pin as t1");
$this->db->join('board as t2', 't1.board_id = t2.id', 'left');
$this->db->join('user as t3', 't1.user_id = t3.id', 'left');
// 关联子查询锁定最后修改的Pin
$this->db->join("($subquery) as last_pins", 't1.user_id = last_pins.user_id AND t1.modified_at = last_pins.last_modified_time', 'inner');
$this->db->where('t3.status', 1);
$this->db->where('t3.hidden_profile', 1);
$this->db->where('t1.status', 1);
$this->db->group_by('t1.user_id');
$this->db->limit($limit, $start);

$query = $this->db->get();
return $query->result_array();

额外提示 #

• 记得把代码中的modified_at替换成你Pin表实际的最后修改时间字段名。
• 执行索引创建后，可以用EXPLAIN分析查询语句，确认索引是否被正确使用。

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