MySQL大表COUNT+ORDER BY查询性能过慢，10秒查询优化咨询

Hey there, let’s dig into this 10-second query runtime problem—it’s clear joining two tables with 2M+ and 3M+ records needs some targeted tweaks. Let’s break down the fixes step by step:

1. Fix Indexes (The Most Impactful Change) #

Your query relies on filtering, joining, grouping, and counting—so we need indexes that cover all these operations to avoid full-table scans:

• For masters_genres: Create a composite index on (genre, master_id). This lets MySQL quickly filter rows where genre='Electronic' and immediately grab the master_id values needed for the JOIN, no need to scan the entire table.

  CREATE INDEX idx_genre_master ON masters_genres (genre, master_id);
  

• For masters_artists: Create a composite index on (master_id, artist_id). This index supports both the JOIN (matching master_id from masters_genres) and the GROUP BY on artist_id—it’s a covering index, meaning MySQL can get all needed data directly from the index without hitting the table itself.

  CREATE INDEX idx_master_artist ON masters_artists (master_id, artist_id);
  

2. Rewrite the Query to Reduce Data Volume #

Your original query might be joining more rows than necessary, especially if one master_id maps to multiple genres in masters_genres. Let’s narrow down the dataset first:

SELECT 
  ma.artist_id, 
  ma.master_id, -- Include other needed columns from masters_artists here
  COUNT(ma.master_id) AS quantity
FROM masters_artists ma
JOIN (
  -- Get unique master_ids for the target genre first
  SELECT DISTINCT master_id 
  FROM masters_genres 
  WHERE genre='Electronic'
) mg ON ma.master_id = mg.master_id
GROUP BY ma.artist_id
ORDER BY quantity DESC 
LIMIT 25;

The subquery filters and deduplicates master_ids upfront, so the JOIN only processes relevant rows instead of the entire 3M-row masters_artists table.

3. Analyze the Execution Plan #

Run EXPLAIN on your query to confirm if indexes are being used and spot bottlenecks:

EXPLAIN SELECT masters_genres.*, masters_artists.*, COUNT(masters_artists.master_id) as quantity FROM masters_genres JOIN masters_artists ON masters_genres.master_id = masters_artists.master_id WHERE masters_genres.genre='Electronic' GROUP BY masters_artists.artist_id ORDER BY quantity DESC LIMIT 25;

Look out for:

• type: ALL (full table scan) → means indexes aren’t being used
• Using temporary or Using filesort → these indicate MySQL is creating temp tables or sorting on disk, which slows things down. The indexes above should eliminate these if set up correctly.

4. Quick Checks for Table Structure #

• Ensure master_id and artist_id are integer types (not strings). Integer comparisons and joins are far faster than string operations.
• If you don’t need all columns from masters_genres and masters_artists, avoid using *—only select the columns you need to reduce data transfer and processing.

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