多表查询符合条件的唯一academyID的SQL优化方案咨询

Hey there! Let's work through optimizing your query to fetch the unique academyID values across three tables, filtered by isActive = true and lastSeen within your specified date range, sorted by the lastSeen field.

Key Optimization Principles #

First, let's break down what makes this query efficient:

• Covering Indexes: Create indexes that let the database retrieve all needed data without accessing the main table (no costly "table lookups").
• Minimize Unnecessary Deduplication: Avoid early deduplication steps that add overhead, and handle it strategically later.
• Clear Sorting Logic: Define exactly which lastSeen value to use for sorting if an academyID appears in multiple tables.

Optimized Query Implementation #

Assuming each table (HeroAcademy, Hero, HeroMission) contains the academyID, isActive, and lastSeen fields, here's a streamlined approach:

-- Gather all valid records from each table first
WITH valid_records AS (
    SELECT academyID, lastSeen
    FROM HeroAcademy
    WHERE isActive = true
      AND lastSeen BETWEEN :start AND :end
    UNION ALL
    SELECT academyID, lastSeen
    FROM Hero
    WHERE isActive = true
      AND lastSeen BETWEEN :start AND :end
    UNION ALL
    SELECT academyID, lastSeen
    FROM HeroMission
    WHERE isActive = true
      AND lastSeen BETWEEN :start AND :end
)
-- Deduplicate and sort using the most recent lastSeen for each academyID
SELECT academyID
FROM valid_records
GROUP BY academyID
ORDER BY MAX(lastSeen) DESC; -- Swap to MIN(lastSeen) if you want the earliest timestamp instead

Why This Works Better #

1. UNION ALL Instead of UNION: UNION automatically deduplicates results, which adds unnecessary overhead. Using UNION ALL just combines records, and we handle deduplication later with GROUP BY—this is significantly faster for large datasets.
2. Covering Indexes: For each table, create this composite index to speed up filtering and data retrieval:

   -- For HeroAcademy
   CREATE INDEX idx_heroacademy_active_lastseen_academy ON HeroAcademy(isActive, lastSeen, academyID);
   -- Repeat for Hero and HeroMission
   CREATE INDEX idx_hero_active_lastseen_academy ON Hero(isActive, lastSeen, academyID);
   CREATE INDEX idx_heromission_active_lastseen_academy ON HeroMission(isActive, lastSeen, academyID);
   

   This index lets the database quickly find rows where isActive = true and lastSeen is in range, and it includes academyID so no need to pull data from the main table.
3. Controlled Sorting: By using MAX(lastSeen) (or MIN), we explicitly define which timestamp to use for sorting if an academyID exists in multiple tables. If you need to sort by the timestamp from the original table for each record (instead of per academyID), adjust the query like this:

   SELECT DISTINCT academyID, lastSeen
   FROM (
       SELECT academyID, lastSeen FROM HeroAcademy WHERE isActive = true AND lastSeen BETWEEN :start AND :end
       UNION ALL
       SELECT academyID, lastSeen FROM Hero WHERE isActive = true AND lastSeen BETWEEN :start AND :end
       UNION ALL
       SELECT academyID, lastSeen FROM HeroMission WHERE isActive = true AND lastSeen BETWEEN :start AND :end
   ) AS valid_records
   ORDER BY lastSeen;
   

Edge Cases to Consider #

• If an academyID appears multiple times in a single table (e.g., multiple Hero records for the same academy), the GROUP BY will still deduplicate it correctly.
• Ensure your date parameters (:start and :end) match the data type of the lastSeen column to avoid implicit conversions that break index usage.

内容的提问来源于stack exchange，提问作者Boo Yan Jiong