MySQL多表关联性能问题：关联ref_practice_invoice_payment时耗时过长

Hey there, let's break down why your query is dragging its feet when joining ref_practice_invoice_payment (RPIP) and walk through actionable optimizations to speed it up.

First, Let's Check the Execution Plan #

Before jumping into fixes, run your query with EXPLAIN to see exactly where MySQL is spending time. This will reveal if you're hitting full table scans, missing indexes, or inefficient join logic:

EXPLAIN SELECT * FROM practice_invoice_detail PID 
LEFT JOIN ref_practice_invoice_payment RPIP ON RPIP.timestamp = PID.timestamp AND RPIP.practice_id = PID.practice_id AND RPIP.refunded <> 1 AND RPIP.other_bill = 0 
JOIN practice_invoice_header PIH ON PIH.timestamp = PID.timestamp AND PIH.practice_id = PID.practice_id AND PIH.is_active = 1 
WHERE PIH.source = 'E' AND PID.practice_id = 28618 
AND ( 
  (RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200) 
  OR 
  (PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%') 
)

Look for these red flags:

• type: ALL in any row (means a full table scan, which is slow)
• key: NULL (no index being used for that table)
• High numbers in the rows column (MySQL estimates it needs to scan thousands/millions of rows)

Likely Bottlenecks #

1. Missing Indexes on ref_practice_invoice_payment #

You didn't share RPIP's schema, but based on your query, it's almost certainly missing a targeted composite index. Your query filters RPIP by:

• practice_id (linked to PID's fixed value: 28618)
• timestamp (joined to PID's timestamp)
• refunded <> 1 and other_bill = 0 (filter conditions)
• pay_cal_id (range condition)

MySQL needs an index that lets it quickly narrow down rows by these criteria without scanning the entire table.

2. OR Condition Killing Index Efficiency #

The OR in your WHERE clause crosses two tables (RPIP and PIH), which MySQL struggles to optimize with indexes. It can't use a single index to cover both branches of the OR, so it falls back to slower scans.

3. Suboptimal Indexes on PID and PIH #

While your existing indexes cover some columns, they don't perfectly align with your filter and join conditions:

• PIH's indexes don't cover all your WHERE clauses (source='E', is_active=1, cal_id range, total_invoice=0) in one go.
• PID's indexes don't prioritize your filters (practice_id, item_comission_type) before the join column timestamp.

Actionable Optimization Steps #

1. Add a Targeted Index to RPIP #

Create a composite index that matches the order of filtering and joining:

CREATE INDEX idx_rpip_practice_ts_refund_other_paycal 
ON ref_practice_invoice_payment (practice_id, timestamp, refunded, other_bill, pay_cal_id);

This lets MySQL first narrow down rows by practice_id, then timestamp, apply the refunded/other_bill filters, and finally use pay_cal_id for the range check—all without scanning the entire table.

2. Optimize Indexes for PID and PIH #

For PIH, create an index that covers all your WHERE conditions:

CREATE INDEX idx_pih_practice_source_active_cal_total 
ON practice_invoice_header (practice_id, source, is_active, cal_id, total_invoice);

This index lets MySQL quickly find rows matching practice_id=28618, source='E', is_active=1, then apply the cal_id range and total_invoice=0 filter directly from the index (no need to jump back to the table data).

For PID, create an index that prioritizes your filters first, then the join column:

CREATE INDEX idx_pid_practice_comission_ts 
ON practice_invoice_detail (practice_id, item_comission_type, timestamp);

This lets MySQL filter by practice_id=28618 and item_comission_type <> '%' before joining on timestamp.

3. Rewrite the Query with UNION ALL #

Split the OR into two separate queries combined with UNION ALL (avoids the overhead of UNION's deduplication):

-- First branch: RPIP pay_cal_id range
SELECT * FROM practice_invoice_detail PID 
LEFT JOIN ref_practice_invoice_payment RPIP ON RPIP.timestamp = PID.timestamp AND RPIP.practice_id = PID.practice_id AND RPIP.refunded <> 1 AND RPIP.other_bill = 0 
JOIN practice_invoice_header PIH ON PIH.timestamp = PID.timestamp AND PIH.practice_id = PID.practice_id AND PIH.is_active = 1 
WHERE PIH.source = 'E' AND PID.practice_id = 28618 
AND RPIP.pay_cal_id >= 201805130 AND RPIP.pay_cal_id <= 201805200

UNION ALL

-- Second branch: PIH cal_id range with additional filters
SELECT * FROM practice_invoice_detail PID 
LEFT JOIN ref_practice_invoice_payment RPIP ON RPIP.timestamp = PID.timestamp AND RPIP.practice_id = PID.practice_id AND RPIP.refunded <> 1 AND RPIP.other_bill = 0 
JOIN practice_invoice_header PIH ON PIH.timestamp = PID.timestamp AND PIH.practice_id = PID.practice_id AND PIH.is_active = 1 
WHERE PIH.source = 'E' AND PID.practice_id = 28618 
AND PIH.cal_id >= 201805130 AND PIH.cal_id <= 201805200 
AND PIH.total_invoice = 0 AND PID.item_comission_type <> '%'

Each subquery can now use the indexes we created, making the overall query much faster.

4. Avoid SELECT * #

Instead of fetching all columns, only select the ones you actually need. This reduces data transfer, cuts down on IO, and lets MySQL use "covering indexes" (indexes that contain all the data needed for the query, so it doesn't have to access the table at all).

Verify the Fixes #

After implementing these changes, run EXPLAIN again on the rewritten query. You should see:

• No type: ALL rows (look for ref or range instead)
• The indexes you created listed in the key column
• A big drop in the estimated rows scanned

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