优化含DISTINCT关键字与多子查询的SQL慢查询

Hey there, let's fix that sluggish query! The main issue with your current code is those correlated subqueries in the SELECT clause—each one runs once for every row in your result set (that's 40k times per subquery!). Multiply that by all your TERM fields, and you're looking at a ton of redundant table scans that kill performance. Here's how to rewrite and optimize it:

Step 1: Replace Correlated Subqueries with JOIN + Conditional Aggregation #

Instead of hitting the XYZ table once per TERM field, we can pull all the data in one go using a LEFT JOIN and calculate all your MAX dates with conditional CASE statements. We'll also replace the boss_id subquery with a straightforward LEFT JOIN to the EMPLOYEE table.

Here's the optimized query:

SELECT
    a.ID,
    a.NAME,
    a.DIV,
    a.UID,
    e.NAME AS boss_id,
    MAX(CASE WHEN x.XYZ_ID = 1 THEN DATE(x.create_time) END) AS TERM1,
    MAX(CASE WHEN x.XYZ_ID = 2 THEN DATE(x.create_time) END) AS TERM2,
    MAX(CASE WHEN x.XYZ_ID = 3 THEN DATE(x.create_time) END) AS TERM3
    -- Add remaining TERM fields using the same CASE WHEN pattern
FROM
    YourMainTable a  -- Replace with the actual table name for alias 'a'
LEFT JOIN
    EMPLOYEE e 
    ON e.UID = a.UID 
    AND e.UID <> ''
LEFT JOIN
    XYZ x 
    ON x.id = a.ID
GROUP BY
    a.ID, a.NAME, a.DIV, a.UID, e.NAME;

(Note: I removed DISTINCT because GROUP BY will already handle deduplication for us—no need for both!)

Step 2: Add Critical Indexes #

Indexes are make-or-break for query speed here. Create these to eliminate full table scans:

• For the XYZ table: This index lets the database quickly find matching rows and compute MAX(create_time) without reading the entire table:

  CREATE INDEX idx_xyz_id_xyzid_createtime ON XYZ(id, XYZ_ID, create_time);
  

• For the EMPLOYEE table: This index speeds up the JOIN on UID and pulls the NAME field directly from the index (no need to "jump back" to the main table):

  CREATE INDEX idx_employee_uid_name ON EMPLOYEE(UID, NAME);
  

• Ensure your main table (alias a) has an index on ID (ideally it's the primary key—most databases auto-index primary keys).

Step 3: Extra Tweaks for Even Better Performance #

• If your main table's ID is a unique identifier, you can simplify the GROUP BY to just a.ID (depending on your database version—MySQL 8.0+, PostgreSQL, etc. support functional dependency). This reduces the work the database does to group rows:

  GROUP BY a.ID;
  

• If you don't need rows where there's no matching data in XYZ, switch the LEFT JOIN to an INNER JOIN to reduce the number of rows processed early on.
• If create_time has a relevant date range (e.g., you only care about the last year), add a WHERE clause to filter XYZ rows before joining:

  LEFT JOIN
      XYZ x 
      ON x.id = a.ID
      AND x.create_time >= '2023-01-01'
  

These changes should cut your query time drastically—you'll go from 90 seconds to something more like a few seconds (or less, depending on your database hardware and data distribution).

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