优化含子查询与左外连接的MySQL慢查询

Got it, let's tackle this slow query issue step by step. First, let's break down what your query is trying to do: fetch the latest entry for each Content, while pulling in the initial create/update timestamps from the first entry of that Content, plus related partner/role names and other joined data. The nested selects and excessive left joins are killing performance on large datasets—here are actionable optimizations:

1. Replace Nested Subqueries with Window Functions #

Multi-layered SELECT statements force the database to scan tables repeatedly, which is brutal on large datasets. Use ROW_NUMBER() to directly flag the latest entry per Content in a single pass:

SELECT 
    c.content_id,
    c.your_required_field_1,
    c.your_required_field_2,
    -- List ONLY the fields you need, avoid SELECT *
    initCMS.initcreatetime,
    initCMS.initupdatetime,
    u.name as partnerName,
    r.name as roleName
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY update_time DESC) AS rn
    FROM content_table
) c
JOIN (
    -- Grab initial timestamps with a single grouped scan
    SELECT 
        content_id,
        MIN(create_time) AS initcreatetime,
        -- If your first entry has the earliest update_time, MIN works here; adjust if needed
        MIN(update_time) AS initupdatetime
    FROM content_table
    GROUP BY content_id
) initCMS ON c.content_id = initCMS.content_id
-- Swap to INNER JOIN if every Content has a matching partner/role (cuts down data early)
LEFT JOIN user_table u ON c.partner_id = u.id
LEFT JOIN role_table r ON c.role_id = r.id
WHERE c.rn = 1 -- Filter to only the latest entry per Content

Window functions like ROW_NUMBER() let the database scan the content table once to mark latest rows, instead of nested subqueries that scan multiple times.

2. Add Targeted Indexes #

Slow joins and window function calculations usually stem from missing indexes. Add these to speed up filtering and joining:

• For the content table, a composite index to support the window function:

  CREATE INDEX idx_content_id_updatetime ON content_table(content_id, update_time DESC);
  

• Indexes on foreign key columns in the content table to speed up joins:

  CREATE INDEX idx_content_partner_id ON content_table(partner_id);
  CREATE INDEX idx_content_role_id ON content_table(role_id);
  

• Ensure your joined tables (user_table, role_table) have indexes on their primary keys (they should already, but double-check).

3. Ditch SELECT * to Cut Data Overhead #

Using SELECT * pulls every column from your tables, including large text/binary fields you might not need. Listing only the fields you require reduces memory usage and IO time—critical for large datasets.

4. Optimize Joins Where Possible #

If every Content entry has a matching partner and role (no NULL values expected), replace LEFT JOIN with INNER JOIN. This lets the database filter out unmatched rows early, reducing the total data it needs to process.

5. Validate with EXPLAIN #

After making these changes, run EXPLAIN before your query to check the execution plan. Look for:

• Full table scans (these mean indexes aren't being used)
• High "rows" values in the plan (indicates the database is processing more data than necessary)
  Adjust your indexes or query structure based on what EXPLAIN shows.

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