MySQL升级至5.7后多关联SELECT查询耗时激增求助

Hey there, let’s troubleshoot why your 4-way INNER JOIN query has gone from a manageable 3 seconds to timing out after upgrading to MySQL 5.7—especially that frustratingly long 'Send Data' phase. Here’s a structured approach to get to the bottom of it:

MySQL 5.7 rolled out significant optimizer improvements (like enhanced cost-based optimization) that might have shifted the query’s join order or index usage away from what worked before.

• Run EXPLAIN ANALYZE (or just EXPLAIN if your 5.7 build doesn’t support ANALYZE) on the problematic query.
• If you still have access to your old MySQL version, generate the same plan there and compare the two. Look for red flags like:
  • Full table scans (ALL in the type column) where indexes were used previously
  • A different join order that’s hitting large tables first without filters
  • Missing Using index flags for covering indexes that were leveraged before

The 5.7 optimizer relies heavily on up-to-date table statistics to make good decisions. Stale or incorrect stats can lead to terrible query plans:

• Run ANALYZE TABLE on every table involved in the join to refresh statistics. This is quick and non-disruptive (for InnoDB, it doesn’t lock tables).
• Verify that all critical indexes are still present with SHOW INDEX FROM your_table_name;—sometimes upgrades or migrations can accidentally drop indexes.
• Check for index fragmentation with SHOW TABLE STATUS LIKE 'your_table_name'; (look at the Data_free column). If fragmentation is high, run OPTIMIZE TABLE (note: this locks tables, so schedule it during low traffic).

Don’t be fooled—MySQL’s "Send Data" status isn’t just about sending results to the client. It includes all post-retrieval processing: filtering, joining, sorting, and assembling rows. Common culprits here are:

• Unintended large result sets: If your WHERE clause is filtering fewer rows than expected (maybe a condition that worked before is now being evaluated differently), the server has to process and send way more data. Check the row count with a simplified SELECT COUNT(*) with the same filters.
• Disk-based filesorts or temporary tables: If EXPLAIN shows Using filesort or Using temporary, MySQL is using disk instead of memory for sorting/table creation. This kills performance. Try adding covering indexes to avoid sorting, or adjust sort_buffer_size (start small—don’t overallocate) if needed.
• Lock contention: If other queries are writing to the joined tables, your read query might be waiting for locks. Run SHOW FULL PROCESSLIST; to see if your query is blocked, or check INFORMATION_SCHEMA.INNODB_LOCKS for lock details.

Upgrades often reset or tweak config parameters that directly impact join performance:

• Join buffer size: join_buffer_size controls memory for joins that can’t use indexes. If the optimizer is doing nested-loop joins without indexes, a too-small buffer will force disk swapping. Compare this value to your pre-upgrade config.
• InnoDB buffer pool: innodb_buffer_pool_size is the most critical setting for InnoDB. If it’s too small, MySQL will constantly hit disk instead of using memory. For dedicated DB servers, aim for 50-70% of available RAM.
• Optimizer switches: 5.7 added new optimizer flags like derived_merge or condition_fanout_filter. Temporarily disable suspect flags with SET SESSION optimizer_switch='flag_name=off'; to see if performance improves. For example, if derived table merging is causing issues, try SET SESSION optimizer_switch='derived_merge=off';.

Narrow down which part of the join is causing the slowdown:

• Run each table’s SELECT with the same WHERE conditions to check if any single table is slow on its own.
• Remove one join at a time and re-run the query—this will tell you exactly which join pair is introducing the latency.
• Check for implicit type conversions in join conditions (e.g., joining a VARCHAR column to an INT). These break index usage and force full scans. Use DESCRIBE your_table; to confirm column types match across joins.

While the query is still running:

• Run SHOW FULL PROCESSLIST; to get the exact state of the query and see if it’s waiting on anything else.
• Check the MySQL error log for warnings related to the query or upgrade—sometimes silent issues here can explain performance drops.

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