MySQL内连接比拆分查询慢50-500倍问题排查求助

First, let's break down why you're seeing Using temporary; Using filesort on tbl_account only in the join query, and how to fix the performance issue:

Root Cause #

The extra Using temporary; Using filesort on tbl_account is a quirk of MySQL 5.6's optimizer. When handling a join combined with an ORDER BY on a column from the large table (tbl_event), the optimizer incorrectly decides it needs to sort the small tbl_account result set first. This is completely unnecessary here—your index_fkAccount_creationDate is already structured to group events by fkAccount and sort them by creationDate, so sorting tbl_account adds zero value and just wastes time on redundant temp table and sorting operations.

Worse, the join query's optimizer isn't leveraging the index_fkAccount_creationDate's sorted order efficiently. Instead of using the index to pull the top 500 creationDate DESC rows directly (like your split query does), it's likely building a full join result set first, then sorting it—hence the massive slowdown.

Fixes to Optimize the Inner Join #

1. Block Unnecessary Sorting on tbl_account #

Add ORDER BY NULL to a subquery fetching account IDs, which tells the optimizer explicitly not to sort the small result set:

SELECT e.* 
FROM tbl_event e 
INNER JOIN (
  SELECT accountID 
  FROM tbl_account 
  WHERE accountKey = 'abcdefghij' 
  ORDER BY NULL  -- Prevent useless sorting
) a ON e.fkAccount = a.accountID 
WHERE e.creationDate >= '2019-02-01 00:00:00' 
ORDER BY e.creationDate DESC 
LIMIT 500;

Run EXPLAIN after this change—you should see Using temporary; Using filesort disappear from the tbl_account subquery.

2. Force Join Order with STRAIGHT_JOIN #

Tell the optimizer to strictly use tbl_account as the driving table first, then join to tbl_event. This ensures it uses the small table to filter the large one efficiently, and pairs well with your composite index:

SELECT STRAIGHT_JOIN e.* 
FROM tbl_account a 
INNER JOIN tbl_event e 
  ON e.fkAccount = a.accountID 
WHERE a.accountKey = 'abcdefghij' 
  AND e.creationDate >= '2019-02-01 00:00:00' 
ORDER BY e.creationDate DESC 
LIMIT 500;

STRAIGHT_JOIN overrides the optimizer's sometimes flawed join order logic, keeping the execution path aligned with your fast split query.

3. Explicitly Specify the Composite Index #

Guide the optimizer to use index_fkAccount_creationDate directly, avoiding any chance it picks a less efficient index:

SELECT e.* 
FROM tbl_event e USE INDEX (index_fkAccount_creationDate)
INNER JOIN tbl_account a 
  ON e.fkAccount = a.accountID 
WHERE a.accountKey = 'abcdefghij' 
  AND e.creationDate >= '2019-02-01 00:00:00' 
ORDER BY e.creationDate DESC 
LIMIT 500;

This ensures the query leverages the index's sorted creationDate values to grab the top 500 rows without extra sorting.

4. Upgrade MySQL (Long-Term Solution) #

MySQL 5.6's optimizer has known limitations with join + ORDER BY scenarios. Versions 5.7 and 8.0 include significant improvements to query planning, especially for cases like this, where the optimizer can better recognize when sorting is unnecessary and how to use indexes to avoid full result set sorting. If your infrastructure allows it, upgrading is the most reliable way to prevent this kind of issue from recurring.

Verify Improvements #

After trying each fix, run EXPLAIN to confirm:

• tbl_account no longer shows Using temporary; Using filesort
• tbl_event uses index_fkAccount_creationDate and doesn't have Using filesort (since the index already provides the required order)

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