MySQL SQL查询优化咨询：50万记录表慢查询排查与优化

Hey there! Let's break down why your query is taking 10-20 seconds even with indexes in place. Since you mentioned you have the actual query and EXPLAIN output, I'll start with common issues and fixes that plague multi-table joins on large datasets like yours:

1. Verify Indexes Are Actually Being Used #

Just creating indexes doesn't guarantee the query planner will use them. Pop open your EXPLAIN output and look for these red flags:

• type: ALL (full table scan) on any of the three tables—this means the database isn't leveraging your indexes. Common culprits:
  • You're using functions on indexed columns (e.g., UPPER(rg_emisor.nit) = '12345') which makes the index unsargable.
  • The index doesn't include columns used in JOIN or WHERE clauses as leading columns.
• For PostgreSQL/MySQL, use EXPLAIN ANALYZE instead of plain EXPLAIN—it gives actual execution times and row counts, which often expose mismatches between the planner's estimates and real data.

2. Tune Your Join Strategy #

Three-table joins can go south if the planner picks a bad join order or type:

• Ensure your JOIN conditions are precise (no vague matches that could create Cartesian products). Double-check that you're joining on primary/foreign key pairs where possible.
• If you don't need all rows from the left table, swap LEFT JOIN for INNER JOIN—this cuts down on the number of rows being processed early in the pipeline.
• If the planner is choosing a nested loop join for large datasets, it might be better to force a hash join (but use hints only as a last resort—they can break as data changes).

3. Use Covering Indexes #

You said all required fields are indexed, but are they covering indexes? A covering index includes every column your query needs (for SELECT, WHERE, JOIN, and ORDER BY). This lets the database grab all data directly from the index without having to jump back to the main table (a costly "bookmark lookup").

• Example: If your query selects rg_egresos.monto and joins on rg_egresos.emisor_id = rg_emisor.id, create an index on rg_egresos (emisor_id, monto)—this covers both the join and the selected column.

4. Eliminate Unnecessary Rows Early #

Filter data as close to the source tables as possible:

• Move WHERE clauses to the table they apply to instead of filtering after joining. For example, if you're filtering rg_egresos.fecha > '2023-01-01', make sure this filter is applied to rg_egresos before joining to rg_detallexml or rg_emisor.
• Check for accidental row duplication from one-to-many joins. If your result set has way more rows than expected, add DISTINCT (carefully—it adds sorting overhead) or adjust your join conditions to limit duplicates.

5. Refresh Table Statistics #

Outdated statistics can make the query planner choose terrible execution plans. If your tables have had lots of inserts, updates, or deletes lately:

• Run ANALYZE rg_detallexml, rg_egresos, rg_emisor; (PostgreSQL/MySQL) or UPDATE STATISTICS rg_detallexml, rg_egresos, rg_emisor; (SQL Server) to refresh stats. This helps the planner make better decisions about join order and index usage.

6. Check for Locking or Contention #

If other processes are writing to these tables while your query runs, it might be waiting for locks. Look for long-running transactions or frequent writes that could be blocking your read. For read-heavy workloads:

• Consider using read replicas if your database supports them.
• Enable snapshot isolation (e.g., SET TRANSACTION ISOLATION LEVEL READ COMMITTED;) to avoid waiting for write locks.

If you can share the actual query text and full EXPLAIN output, I can help you pinpoint the exact bottleneck and suggest tailored fixes.

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