求高效SQL：批量获取各房屋租赁有效条款及单条查询优化

Let's start by clarifying the core requirements we're working with:

• For each house_lease_id, first prioritize currently active terms: those where date_start <= NOW() and (date_end > NOW() OR date_end IS NULL)
• If no active terms exist for a house_lease_id, fall back to upcoming future terms: date_start > NOW()
• Sorting rules:
  • Active terms: sort by date_start DESC (most recently started active term first)
  • Future terms: sort by date_start ASC (earliest upcoming term first)
• We need to retrieve exactly one effective term per unique house_lease_id in a single batch query, no custom functions/stored procedures required.

The Problem with the Original Single-Record Query #

Your original query works for a single house_lease_id, but it has two major issues for batch processing:

1. It runs duplicate subqueries to check for active terms, which adds unnecessary overhead
2. It can't handle multiple house_lease_ids without looping, which is inefficient at scale

Optimized Batch Solution Using Window Functions #

MySQL 8.0+ supports window functions like ROW_NUMBER(), which is perfect for this scenario. We can group records by house_lease_id, rank them based on our priority rules, and pick the top-ranked record for each group.

Here's the optimized query:

WITH ranked_terms AS (
    SELECT
        *,
        -- Assign a priority type: 1 = active (higher priority), 2 = future (lower priority)
        CASE
            WHEN date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL) THEN 1
            WHEN date_start > NOW() THEN 2
            ELSE 3 -- Ignore expired terms (date_end <= NOW()) entirely
        END AS term_priority,
        -- Rank each term within its house_lease_id group
        ROW_NUMBER() OVER (
            PARTITION BY house_lease_id
            ORDER BY
                term_priority ASC, -- Active terms always come first
                -- Apply different sorting logic based on priority type
                CASE term_priority
                    WHEN 1 THEN UNIX_TIMESTAMP(date_start) * -1 -- Sort active terms DESC by date_start
                    WHEN 2 THEN UNIX_TIMESTAMP(date_start)      -- Sort future terms ASC by date_start
                END ASC
        ) AS rn
    FROM house_lease_terms
    -- Filter out invalid terms upfront to reduce dataset size
    WHERE (date_start <= NOW() AND (date_end > NOW() OR date_end IS NULL)) OR date_start > NOW()
)
SELECT id, house_lease_id, date_start, date_end
FROM ranked_terms
WHERE rn = 1;

How This Works #

1. CTE (ranked_terms): We first process all valid terms (active or future) and add two calculated fields:
   • term_priority: Marks each term as active (1) or future (2). Expired terms are excluded right away to minimize the data we need to process.
   • rn: Uses ROW_NUMBER() to assign a rank to each term within its house_lease_id group. The ranking logic ensures:
     • Active terms are always ranked higher than future terms
     • Active terms are sorted by date_start descending (most recent first)
     • Future terms are sorted by date_start ascending (earliest first)
2. Final Selection: We pick only the records where rn = 1, which gives us the highest-priority term for each house_lease_id.

Performance Optimization Tip #

To make this query run even faster, add a composite index on house_lease_terms to cover filtering, grouping, and sorting operations:

CREATE INDEX idx_lease_terms_priority ON house_lease_terms (house_lease_id, date_start, date_end);

This index allows MySQL to quickly filter valid terms, group them by house_lease_id, and sort them without doing a full table scan.

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