MySQL数据量增大后慢查询问题求助（附EXPLAIN分析结果）

Hey there, let’s break down your slow query issue using the EXPLAIN output you provided. The key red flags here are driving the latency when data grows, so let’s tackle them step by step:

Key Issues Identified from EXPLAIN #

• Using temporary; Using filesort in multiple branches: These appear in the PQL table queries (derived/union parts 2,3,4) and the PID table query (union part 5). Temporary tables and disk-based sorting are massive performance bottlenecks as data volume increases—they force MySQL to write intermediate results to disk, which is way slower than in-memory operations.
• UNION overhead: By default, UNION performs deduplication, which requires creating temporary tables to compare and remove duplicates. This adds unnecessary processing for large datasets.
• Large intermediate result sets: With so many joined tables, even small per-table filters can balloon into huge intermediate datasets, making subsequent joins and sorting exponentially slower.
• Opportunities to optimize index usage: While some queries use indexes, there are spots where we can refine indexes to avoid partial lookups (Using index condition) and reduce table access.

Actionable Optimization Steps #

1. Eliminate Using temporary and Using filesort

   • For the PQL table queries that trigger these operations: Check if your ORDER BY or GROUP BY fields are included in a composite index alongside the for_comission filter. For example, a composite index like (for_comission, your_order_by_field) would let MySQL sort directly from the index, avoiding filesort and temporary tables.
   • For the PID table in union part 5: Analyze the GROUP BY/ORDER BY logic here and create a composite index covering the filter fields (practice_id, item_type?) and the sorted/grouped fields.

2. Replace UNION with UNION ALL (if possible)

   • If your business logic doesn’t require deduplicated results (or you can guarantee no overlaps between union branches), switch to UNION ALL. This skips the deduplication step entirely, eliminating the need for temporary tables to process duplicates—a huge win for large datasets.

3. Shrink intermediate result sets

   • Push filters down: Add early WHERE clauses to each subquery to eliminate unnecessary rows as soon as possible. For example, ensure is_active flags or date ranges are applied in the PIH and PID subqueries before joining to other tables.
   • Use temporary tables for complex joins: Extract the core joins (like PQL + EN + PIH) into a temporary table, add indexes to the temporary table’s key fields, then join this smaller, indexed dataset with the remaining tables. This reduces the number of rows processed in subsequent joins.

4. Refine indexes for better coverage

   • For queries showing Using index condition (like the PIH table joins): Create covering indexes that include all fields needed for filtering, joining, and selecting. For example, if PIH uses practice_id, EN.id, timestamp, and is_active, a composite index (practice_id, id, timestamp, is_active) would let MySQL retrieve all needed data directly from the index without accessing the main table.
   • Verify that all join fields are indexed and have matching data types (avoid implicit conversions that break index usage).

5. Split the query if necessary

   • With 10+ tables joined, consider breaking the query into smaller, focused queries and assembling the final result in your application layer. For example:
     1. Fetch the core records from PQL, EN, and PIH first.
     2. Use the IDs from this result to query related data from RPP, PID, RV, etc.
   • This can reduce the complexity of MySQL’s query planner and avoid the overhead of joining massive datasets all at once.

6. Tweak MySQL configuration

   • Check tmp_table_size and max_heap_table_size—if these values are too small, MySQL will write temporary tables to disk instead of keeping them in memory. Increase these to a reasonable size (e.g., 64M or 128M, depending on your server’s memory) to keep temporary operations in RAM.

If you can share more details about the ORDER BY/GROUP BY clauses in your query, or the specific business logic driving the UNION branches, we can refine these suggestions further!

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