MySQL多表关联查询耗时约50秒，求排查与优化建议

Hey there, let's break down why your query might be dragging its feet and how to speed it up. First, here's your query formatted for readability:

SELECT 
    LEFT(t1.group, 3) AS `Group`, 
    MAX(t1.`Z-indexnummer`) AS 'ZI-Nummer', 
    COUNT(t1.`Z-indexnummer`) AS 'PacksDispensed', 
    t2.`Artikelomschrijving: inkoophoeveelheid (aantal)` AS 'Packsize', 
    t2.`Prijs: apotheekinkoop (ex. BTW)` AS 'Prijs EX BTW', 
    Afleverapotheek, 
    Zorgverzekeraar, 
    Basisproduct, 
    Etiketnaam, 
    t2.`Gm.: productnaam: PRK=prescriptie (code)`, 
    t2.`Naam: productverantwoordelijke`, 
    t2.`Inkoopkanaal (code)`, 
    t3.mnvddd 
FROM My_Data as t1 
INNER JOIN ZINummer as t2 ON t1.`Z-indexnummer` = t2.`Artikelnummer: ZI-nummer` 
INNER JOIN Idea as t3 ON t1.`Z-indexnummer` = t3.atkode 
WHERE t1.`VPV-Weekaflevering` = '0, Gewone Levering' 
GROUP BY t1.`Z-indexnummer` 
ORDER BY PacksDispensed DESC;

1. Start with the Execution Plan #

First step: Run EXPLAIN before your query (like EXPLAIN SELECT ...) to see exactly what your database is doing. Look for these red flags:

• Full table scans (type: ALL) on any of the three tables—this means the database is scanning every row instead of using shortcuts.
• High rows values, indicating it's processing way more data than needed.
• Using temporary or Using filesort labels—these mean extra overhead for grouping and sorting, which often slow things down.

2. Add Targeted Indexes #

Missing indexes on join and filter columns are the #1 cause of slow joins. Here's what you should add:

• On My_Data (t1): A composite index for your filter and join key. This lets the database quickly find rows matching your WHERE clause and join without scanning the whole table.

  CREATE INDEX idx_mydata_vpv_zindex ON My_Data (`VPV-Weekaflevering`, `Z-indexnummer`);
  

• On ZINummer (t2): An index on the join key with t1 to speed up lookups.

  CREATE INDEX idx_zinummer_artikel ON ZINummer (`Artikelnummer: ZI-nummer`);
  

• On Idea (t3): An index on its join key with t1 to eliminate full scans here too.

  CREATE INDEX idx_idea_atkode ON Idea (atkode);
  

3. Fix the GROUP BY Clause (If Needed) #

If you're using MySQL (or similar databases) with ONLY_FULL_GROUP_BY enabled, your current GROUP BY might be causing issues. You're selecting multiple columns that aren't in the GROUP BY or wrapped in aggregate functions—this can lead to unpredictable results and extra database work.

• Either add all non-aggregated selected columns to the GROUP BY (if those values are consistent per Z-indexnummer), or wrap them in aggregate functions like MAX() or MIN() if that makes sense for your use case.

4. Optimize the ORDER BY #

Sorting by PacksDispensed DESC (a calculated count) can trigger a slow filesort operation. Check the execution plan for this label. If you see it:

• If you're on MySQL 8.0+, try using a window function to pre-calculate the count before sorting, though this isn't always a silver bullet.
• The composite index on My_Data we suggested earlier might help the database avoid a filesort by leveraging indexed data for grouping and sorting.

5. Check Table Size & Data Quality #

• If any of these tables are massive (millions of rows), even with indexes, joins can take time. Consider partitioning tables (e.g., by VPV-Weekaflevering if you frequently filter on that value).
• Look for duplicate rows in join keys—if Z-indexnummer has lots of duplicates across tables, the join will create a huge intermediate dataset, slowing down grouping and sorting.

6. Isolate the Bottleneck #

To figure out exactly what's slowing you down:

• Remove the ORDER BY clause temporarily—if the query speeds up, sorting is the bottleneck.
• Remove one join at a time (first drop the Idea join, then ZINummer) to see which table is adding the most delay.

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