百万级Users表与2-3百万级UserDocuments表关联查询性能优化请求

看起来你的查询因为嵌套子查询、重复过滤条件和不必要的排序导致了性能瓶颈，结合百万级的表数据量，咱们一步步来优化：

1. 重构查询结构，去掉冗余的嵌套子查询 #

原查询里子查询用了user_id IN (SELECT user_id FROM Users ...)，然后主查询又重复过滤了一遍Users的条件，完全可以把Users的过滤条件提前，直接和UserDocuments做关联，这样能减少子查询带来的开销，让数据库更好地优化执行计划。

2. 移除不必要的排序操作 #

子查询里的ORDER BY user_id ASC完全是多余的——子查询的结果是用来和主表JOIN的，排序在这里不会影响最终结果，反而会额外消耗CPU和内存，直接删掉就行。

3. 合并重复的过滤条件 #

主查询里重复写了Users的过滤条件，其实在关联Users表的时候就可以一次性过滤掉不符合条件的用户，不用在JOIN之后再过滤一次。

4. 优化索引（关键！） #

针对你的查询场景，创建合适的复合索引能大幅提升性能：

• Users表：创建覆盖过滤和查询字段的复合索引：

  -- PostgreSQL语法
  CREATE INDEX idx_users_filter_cover ON Users (region_id, city_id, user_type, user_suspended, is_enabled, verification_status) INCLUDE (user_id, name, date_registered, phone_no);
  
  -- MySQL语法（无INCLUDE，直接追加字段）
  CREATE INDEX idx_users_filter_cover ON Users (region_id, city_id, user_type, user_suspended, is_enabled, verification_status, user_id, name, date_registered, phone_no);
  

  这个索引能让数据库直接从索引里获取需要的字段，不需要回表查询原数据。

• UserDocuments表：创建覆盖关联、过滤和聚合的复合索引：

  -- PostgreSQL语法
  CREATE INDEX idx_userdocs_user_doc_status ON UserDocuments (user_id, document_id, status) INCLUDE (updated_at);
  
  -- MySQL语法
  CREATE INDEX idx_userdocs_user_doc_status ON UserDocuments (user_id, document_id, status, updated_at);
  

  索引包含了关联、过滤和聚合需要的所有字段，彻底避免回表开销。

优化后的查询语句 #

SELECT 
  u.user_id, 
  u.name, 
  u.date_registered, 
  u.phone_no, 
  t1.docs_count, 
  t1.last_uploaded_on 
FROM Users u 
JOIN (
  SELECT 
    user_id, 
    MAX(updated_at) AS last_uploaded_on, 
    SUM(CASE WHEN status != 2 THEN 1 ELSE 0 END) AS docs_count 
  FROM UserDocuments 
  WHERE document_id IN ('1', '2', '3', '4', '10', '11')
  GROUP BY user_id
) t1 ON u.user_id = t1.user_id 
WHERE 
  u.region_id = 1 
  AND u.city_id = 8 
  AND u.user_type = 1 
  AND u.user_suspended = 0 
  AND u.is_enabled = 1 
  AND u.verification_status = -1
  AND t1.docs_count < 6
ORDER BY u.user_id ASC
LIMIT 1000, 100;

额外优化建议：优化分页逻辑 #

如果这是分页查询，LIMIT 1000, 100的大偏移量会导致数据库先扫描前1100条数据再丢弃前1000条，效率很低。可以改成基于user_id的keyset分页：

-- 假设上一页最后一条的user_id是XXX
SELECT 
  u.user_id, 
  u.name, 
  u.date_registered, 
  u.phone_no, 
  t1.docs_count, 
  t1.last_uploaded_on 
FROM Users u 
JOIN (
  SELECT 
    user_id, 
    MAX(updated_at) AS last_uploaded_on, 
    SUM(CASE WHEN status != 2 THEN 1 ELSE 0 END) AS docs_count 
  FROM UserDocuments 
  WHERE document_id IN ('1', '2', '3', '4', '10', '11')
  GROUP BY user_id
) t1 ON u.user_id = t1.user_id 
WHERE 
  u.region_id = 1 
  AND u.city_id = 8 
  AND u.user_type = 1 
  AND u.user_suspended = 0 
  AND u.is_enabled = 1 
  AND u.verification_status = -1
  AND t1.docs_count < 6
  AND u.user_id > XXX -- 传入上一页的最大user_id
ORDER BY u.user_id ASC
LIMIT 100;

这种方式利用索引直接定位到起始位置，避免了大偏移量的扫描开销。

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