如何为复杂SQL查询选择合适索引以优化性能

第一步：清理冗余过滤条件 #

先梳理你的WHERE子句，里面存在大量重复、无效的过滤条件，直接删除这些冗余项能减少数据库的解析负担：

• 重复出现的ci.fc_id IN (1)、ci.brand_id IN (21)只保留一次即可
• ci.salesman_id != 0完全被ci.salesman_id = 16552覆盖，直接删除前者
• 子查询里的重复过滤条件同步清理

第二步：优化子查询 #

你的查询包含三类性能较低的子查询，全部可以转换为JOIN来提升效率：

1. 相关聚合子查询转批量聚合JOIN：
   原查询中(SELECT COALESCE(SUM(amount),0) from payments WHERE collection_invoice_id = ci.id)是逐行执行的相关子查询，改为LEFT JOIN+GROUP BY的方式，只执行一次聚合操作：

   LEFT JOIN (SELECT collection_invoice_id, COALESCE(SUM(amount),0) AS collected_amount FROM payments GROUP BY collection_invoice_id) p ON ci.id = p.collection_invoice_id
   

2. 重复查询销售员工信息转单次JOIN：
   原查询两次查询Salesmen表获取旧销售的姓名和手机号，改为一次LEFT JOIN：

   LEFT JOIN Salesmen AS old_S ON ci.handover_old_salesman_id = old_S.id
   

   后续SELECT直接使用old_S.name和old_S.mobile即可
3. IN子查询转JOIN派生表：
   原查询ci.id IN (SELECT max(id)...)的IN子查询性能瓶颈明显，改为JOIN派生表的方式：

   JOIN (
       SELECT max(id) AS max_id
       FROM collection_invoices
       WHERE fc_id = 1 
           AND brand_id = 21 
           AND salesman_id = 16552 
           AND collection_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -3 DAY) AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
           AND DAYOFWEEK(collection_date) = 4
       GROUP BY invoice_no, fc_id, brand_id
   ) ci_max ON ci.id = ci_max.max_id
   

第三步：调整JOIN类型 #

原查询中LEFT JOIN Orders AS Orde和LEFT JOIN Allocations AS alcn，但WHERE子句添加了Orde.status IN ('DL','PD') AND alcn.return_status = 'Complete'，这会强制左连接变为内连接（NULL值无法满足这些条件），直接改为INNER JOIN，减少数据库对NULL值的判断开销。

第四步：针对性创建索引 #

核心表索引（collection_invoices） #

创建复合覆盖索引，同时满足过滤、分组、排序的需求，避免回表查询：

CREATE INDEX idx_ci_fc_brand_salesman_date_inv ON collection_invoices (fc_id, brand_id, salesman_id, collection_date, invoice_no, id);

• 前四列是等值/范围过滤条件（等值列优先，范围列后置）
• invoice_no满足GROUP BY需求
• id满足子查询取max(id)的需求，实现索引覆盖

其他表索引 #

• payments表：CREATE INDEX idx_payments_ciid_amount ON payments (collection_invoice_id, amount);（覆盖SUM(amount)的聚合查询）
• ChampOutstandingInvoices表：CREATE INDEX idx_co_inv_fc_beat ON ChampOutstandingInvoices (invoice_no, fc_id, beat_name);（覆盖JOIN条件和beat_name字段查询）
• Orders表：CREATE INDEX idx_orders_id_status_alloc ON Orders (id, status, allocation_id);（覆盖JOIN和status过滤）
• Allocations表：CREATE INDEX idx_alloc_id_return ON Allocations (id, return_status);（覆盖JOIN和return_status过滤）

优化后的完整SQL #

SELECT
    S.id AS salesperson_id,
    S.name AS salesperson_name,
    S.mobile AS salesperson_phone_number,
    B.name AS brand_name,
    ci.id AS collection_invoice_id,
    ci.invoice_no AS invoice_number,
    ci.status as status,
    ci.invoice_date AS invoice_date,
    DATE_FORMAT(ci.collection_date,'%d-%m-%Y') AS collection_date,
    DAYOFWEEK(ci.collection_date) AS collection_weekday_number,
    ci.invoice_amount AS invoice_value,
    ci.invoice_assigned_by AS invoice_assigned_by,
    ci.invoice_updated_by AS invoice_updated_by,
    ci.verified_by_cashier_id AS verified_by_cashier_id,
    ci.verified_by_segregator_id AS verified_by_segregator_id,
    ci.invoice_verification_status AS invoice_verification_status,
    DATEDIFF(CURDATE(), DATE(ci.invoice_date)) AS invoice_age,
    ci.invoice_amount AS invoice_value,
    COALESCE(p.collected_amount,0) AS collected_amount,
    ci.initial_outstanding_amount AS outstanding_amount,
    ci.current_outstanding_amount AS new_outstanding,
    ci.fc_id AS collection_fc_id,
    ci.brand_id AS collection_brand_id,
    B.id AS brand_id,
    B.name AS brand_name,
    B.code AS brand_code,
    ci.store_id AS collection_store_id,
    store.id AS store_id,
    store.name AS store_name,
    store.code AS store_code,
    ci.salesman_id AS collection_salesman_id,
    ci.handover_old_salesman_id AS old_collection_salesman_id,
    co.beat_name as beat_name,
    COALESCE(old_S.name, '') AS old_collection_salesperson_name,
    COALESCE(old_S.mobile, '') AS old_collection_salesperson_phone_number
FROM collection_invoices AS ci
JOIN Brands AS B ON ci.brand_id = B.id
JOIN Stores AS store ON ci.store_id = store.id
JOIN Salesmen AS S ON ci.salesman_id = S.id
INNER JOIN Orders AS Orde ON ci.order_id = Orde.id
INNER JOIN Allocations AS alcn ON Orde.allocation_id = alcn.id
JOIN ChampOutstandingInvoices co ON ci.invoice_no = co.invoice_no AND co.fc_id = ci.fc_id
JOIN (
    SELECT max(id) AS max_id
    FROM collection_invoices
    WHERE fc_id = 1 
        AND brand_id = 21 
        AND salesman_id = 16552 
        AND collection_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -3 DAY) AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
        AND DAYOFWEEK(collection_date) = 4
    GROUP BY invoice_no, fc_id, brand_id
) ci_max ON ci.id = ci_max.max_id
LEFT JOIN (
    SELECT collection_invoice_id, SUM(amount) AS collected_amount 
    FROM payments 
    GROUP BY collection_invoice_id
) p ON ci.id = p.collection_invoice_id
LEFT JOIN Salesmen AS old_S ON ci.handover_old_salesman_id = old_S.id
WHERE ci.fc_id = 1 
    AND ci.brand_id = 21 
    AND ci.salesman_id = 16552 
    AND ci.collection_date BETWEEN DATE_ADD(CURDATE(), INTERVAL -3 DAY) AND DATE_ADD(CURDATE(), INTERVAL 3 DAY)
    AND DAYOFWEEK(ci.collection_date) = 4
    AND ci.invoice_assigned_by IS NULL 
    AND ci.initial_outstanding_amount > 0 
    AND Orde.status IN ('DL','PD') 
    AND alcn.return_status = 'Complete'  
ORDER BY ci.invoice_no ASC
LIMIT 50 OFFSET 0

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