如何结合UNION与JOIN优化MySQL 8.0+的4.4万条记录查询性能？

Got it, let's tackle this slow query issue you're facing—especially with that 4GB RAM Intel Core i3 setup, every bit of optimization counts. Your current query works, but the combination of type mismatches, missing indexes, and inefficient temporary table usage is killing performance on your hardware. Here's how to fix it step by step:

1. Fix the author_id Type Mismatch to Avoid Implicit Conversion #

The biggest red flag here is the author_id type difference (INT vs VARCHAR) between your two author tables. When you do the UNION and subsequent JOIN, MySQL has to do implicit type conversion, which invalidates any indexes on these fields—forcing full table scans.

Fix this by explicitly casting one side to match the other. Choose the type that matches the details_table's author_id (I'll assume it's INT for this example; adjust if it's VARCHAR):

SELECT a.author, a.title, a.author_id, b.pub_date 
FROM ( 
  SELECT author, title, author_id FROM authors_table 
  UNION 
  SELECT author, title, CAST(author_id AS UNSIGNED INT) AS author_id FROM old_authors_table 
) a 
LEFT JOIN ( 
  SELECT author_id, MAX(pub_date) AS pub_date FROM details_table GROUP BY author_id 
) b ON b.author_id = a.author_id;

2. Add Critical Indexes to Eliminate Full Scans #

Indexes are non-negotiable for large datasets, especially on low-memory hardware where disk I/O is a bottleneck:

• For authors_table and old_authors_table:

  -- For authors_table (INT author_id)
  CREATE INDEX idx_authors_author_id ON authors_table(author_id);
  -- For old_authors_table (VARCHAR author_id)
  CREATE INDEX idx_old_authors_author_id ON old_authors_table(author_id);
  

• For details_table, create a composite index that covers both the GROUP BY and MAX() operation—this lets MySQL compute the max pub_date without scanning the entire table:

  CREATE INDEX idx_details_author_pubdate ON details_table(author_id, pub_date DESC);
  

This index lets MySQL quickly group by author_id and grab the latest pub_date in one pass.

3. Replace UNION with UNION ALL (If No Duplicates Exist) #

UNION automatically removes duplicate rows, which requires sorting the combined dataset into a temporary table—huge overhead for 44k+ rows. If you’re sure there’s no overlap between authors_table and old_authors_table (no identical author + title + author_id entries), switch to UNION ALL:

SELECT a.author, a.title, a.author_id, b.pub_date 
FROM ( 
  SELECT author, title, author_id FROM authors_table 
  UNION ALL  -- Faster, no duplicate check
  SELECT author, title, CAST(author_id AS UNSIGNED INT) AS author_id FROM old_authors_table 
) a 
LEFT JOIN ( 
  SELECT author_id, MAX(pub_date) AS pub_date FROM details_table GROUP BY author_id 
) b ON b.author_id = a.author_id;

This cuts out the expensive duplicate removal step entirely.

4. Tune MySQL Config for Your Low-Memory Setup #

With only 4GB RAM on Windows 7 (which already uses 1-2GB for the OS), you need to adjust MySQL’s memory settings to prioritize critical caches:

• Open your MySQL my.ini/my.cnf file and update these values:

  # Give InnoDB enough buffer space to cache frequently used data (avoid disk hits)
  innodb_buffer_pool_size = 1G
  # Let temporary tables live in memory instead of writing to disk
  tmp_table_size = 256M
  max_heap_table_size = 256M
  # Reduce log buffer size if needed (since we're low on RAM)
  innodb_log_buffer_size = 64M
  

Restart MySQL after making these changes. This will keep more operations in memory, which is way faster than disk I/O on your setup.

5. Optimize PHP Side: Pagination Instead of Full Dataset Load #

Even if you fix the query, loading 44k rows into a single web page is bad for both performance and user experience. Implement pagination in your PHP code—fetch 20-50 rows at a time using LIMIT and OFFSET:

-- Example: Get page 3 (rows 41-60)
SELECT a.author, a.title, a.author_id, b.pub_date 
FROM ( 
  SELECT author, title, author_id FROM authors_table 
  UNION ALL 
  SELECT author, title, CAST(author_id AS UNSIGNED INT) AS author_id FROM old_authors_table 
) a 
LEFT JOIN ( 
  SELECT author_id, MAX(pub_date) AS pub_date FROM details_table GROUP BY author_id 
) b ON b.author_id = a.author_id
LIMIT 20 OFFSET 40;

If you really need to show all data, use lazy loading (load more rows as the user scrolls) or cache the full result set in PHP (e.g., using file caching or APCu) so you don’t re-run the slow query on every request.

6. Bonus: Pre-Merge Author Tables (If Data Doesn’t Change Often) #

If your authors_table and old_authors_table don’t get updated frequently, create a merged summary table with a cron job or scheduled task:

-- Create a merged table
CREATE TABLE merged_authors (
  author VARCHAR(255),
  title VARCHAR(255),
  author_id INT,
  PRIMARY KEY (author_id)
);

-- Populate it (run this periodically)
TRUNCATE TABLE merged_authors;
INSERT INTO merged_authors
SELECT author, title, author_id FROM authors_table
UNION ALL
SELECT author, title, CAST(author_id AS UNSIGNED INT) AS author_id FROM old_authors_table;

Then your query becomes a simple join with no subqueries:

SELECT ma.author, ma.title, ma.author_id, d.pub_date
FROM merged_authors ma
LEFT JOIN (
  SELECT author_id, MAX(pub_date) AS pub_date FROM details_table GROUP BY author_id
) d ON ma.author_id = d.author_id;

This eliminates the UNION overhead entirely and makes the query blazingly fast.

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