MySQL中关联查询结合全文索引时LIMIT与OFFSET的正确用法

核心思路 #

先单独对addresses表执行带分页的全文搜索，只提取该表的唯一标识字段（避免临时表过大或索引失效），再通过这些标识关联回完整的addresses数据以及users、working_hours表。这样分页仅限制addresses的结果数，同时保留全文索引的有效性，且所有关联结果都会被返回。

具体实现 #

情况1：addresses表有独立主键（如addr_id） #

假设addresses表存在主键addr_id（若你的表结构中没有，建议添加，这是最优方案），查询语句如下：

SELECT 
    u.uid, u.type_name,
    a.address, a.name, a.latitude,
    wh.day_of_week, wh.opens_at
FROM 
    -- 第一步：分页获取符合全文搜索条件的addresses主键
    (SELECT addr_id 
     FROM addresses 
     WHERE MATCH(name, address) AGAINST('你的搜索关键词' IN BOOLEAN MODE)
     LIMIT 10 OFFSET 20) AS paginated_addrs
-- 关联回完整的addresses数据
JOIN addresses a ON a.addr_id = paginated_addrs.addr_id
-- 关联users表
JOIN users u ON u.uid = a.user_id
-- 关联working_hours表（根据需求选择JOIN/LEFT JOIN）
LEFT JOIN working_hours wh ON wh.agent_id = u.uid;

情况2：addresses表无独立主键 #

若addresses表没有单独主键，可使用user_id + address的组合作为唯一标识（确保同一用户的地址不重复），查询语句如下：

SELECT 
    u.uid, u.type_name,
    a.address, a.name, a.latitude,
    wh.day_of_week, wh.opens_at
FROM 
    -- 第一步：分页获取符合全文搜索条件的addresses唯一组合标识
    (SELECT user_id, address 
     FROM addresses 
     WHERE MATCH(name, address) AGAINST('你的搜索关键词' IN BOOLEAN MODE)
     LIMIT 10 OFFSET 20) AS paginated_addrs
-- 通过组合标识关联回完整的addresses数据
JOIN addresses a ON a.user_id = paginated_addrs.user_id AND a.address = paginated_addrs.address
-- 关联users表
JOIN users u ON u.uid = a.user_id
-- 关联working_hours表（根据需求选择JOIN/LEFT JOIN）
LEFT JOIN working_hours wh ON wh.agent_id = u.uid;

方案优势 #

• 保留索引有效性：子查询直接对addresses表执行全文搜索，能正常使用name和address上的全文索引，不会因为临时表导致索引失效。
• 分页精准作用于addresses：LIMIT和OFFSET仅限制子查询中addresses的结果数，关联后的所有对应记录（比如一个地址对应多个工作时段）都会被完整返回。
• 性能优化：子查询仅提取标识字段，临时表数据量极小，不会带来额外性能开销。

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