SQL查询执行耗时过长，求优化该查询以缩短结果返回时间的方法

Hey there! Let's dive into optimizing your query—it looks like you're trying to count distinct employee IDs grouped by status, but only for IDs that fall in a specific range tied to the largest ID in the table and a join date cutoff. Here are actionable steps to cut down execution time:

1. Fix the Date Filter to Use Indexes #

Your current query uses trunc(join_date) >= '01-Mar-2018', which applies a function to the join_date column. This prevents the database from using any indexes on join_date (a function-based index would work, but it's better to rewrite the filter to avoid the function entirely).

Replace that condition with a direct date comparison:

join_date >= DATE '2018-03-01'

This is equivalent to your original filter (since trunc(join_date) removes time components) but lets the database leverage standard indexes on join_date.

2. Remove Unnecessary DISTINCT (If Applicable) #

If id is a primary key or unique column (which it likely is, given the name), count(distinct id) is redundant—every id is already unique. Switching to count(id) will save the database from doing extra deduplication work:

COUNT(id) AS id_count

3. Simplify Subqueries (Optional but Helpful) #

Your nested subqueries work, but rewriting them with CTEs (Common Table Expressions) can make the query easier to read and might help the query optimizer generate a better execution plan. Here's a cleaner version:

WITH lower_bound AS (
    SELECT MAX(id) - 200000 AS min_id_threshold
    FROM emp
),
starting_id AS (
    SELECT MIN(id) AS first_eligible_id
    FROM emp
    CROSS JOIN lower_bound
    WHERE id >= lower_bound.min_id_threshold
      AND join_date >= DATE '2018-03-01'
)
SELECT status, COUNT(id) AS id_count
FROM emp
CROSS JOIN starting_id
WHERE id >= starting_id.first_eligible_id
GROUP BY status;

4. Add Targeted Indexes #

The biggest performance gain will come from adding indexes that match your query's filter conditions. Depending on your database system, create one of these composite indexes:

• If id is your primary key (already has an index), create an index on (join_date, id):

  -- Oracle example
  CREATE INDEX idx_emp_join_date_id ON emp(join_date, id);
  
  -- MySQL example
  CREATE INDEX idx_emp_join_date_id ON emp(join_date, id);
  

  This index lets the database quickly find rows where join_date meets your cutoff, and retrieve the corresponding id values without needing to scan the entire table.

• If id isn't unique, create an index on (id, join_date) instead:

  CREATE INDEX idx_emp_id_join_date ON emp(id, join_date);
  

  This helps the database quickly filter rows by id >= min_id_threshold and check the join_date condition without looking up the full table row.

5. Update Table Statistics #

Outdated table statistics can lead the query optimizer to choose inefficient execution plans. Refresh them to ensure the database has accurate data about your table's contents:

• Oracle:

  EXEC DBMS_STATS.GATHER_TABLE_STATS('your_schema_name', 'emp');
  

• MySQL:

  ANALYZE TABLE emp;
  

Final Optimized Query (Without CTE) #

If you prefer to stick with nested subqueries, here's the optimized version:

SELECT status, COUNT(id) AS id_count
FROM emp
WHERE id >= (
    SELECT MIN(id)
    FROM emp
    WHERE id >= (SELECT MAX(id) - 200000 FROM emp)
      AND join_date >= DATE '2018-03-01'
)
GROUP BY status;

Give these changes a try—you should see a noticeable drop in execution time, especially if your emp table is large!

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