优化INNER JOIN SQL查询：MySQL查询超时问题求助

问题描述 #

我有如下SQL查询语句：

SELECT DISTINCTROW  
    customers.id,
    customers.customer_name  
FROM customers  
    INNER JOIN rentals ON customers.id = rentals.id_customer  
    INNER JOIN rentals_billing ON rentals.id = rentals_billing.id_rental  
    INNER JOIN vehicles ON vehicles.id = rentals.id_vehicle  
WHERE  
    (rentals_billing.customer_id_facsimile IS NULL AND rentals_billing.rental_payable = 1) 
    OR (
       (rentals_billing.missing_rent_id_doc IS NULL OR missing_rent_id_doc <= 0) 
        AND missing_rent_fee > 0
     ) 
    AND customers.delete_date IS NULL
    AND rentals.delete_date IS NULL
    AND vehicles.delete_date IS NULL
    AND (rentals.mode = 'MODEA' OR rentals.mode = 'MODEB' OR rentals.mode = 'MODEC')

该查询运行在MySQL 5.6.13服务器（基于Windows Server 2008 R2 SP1系统），虚拟服务器配置为4GB内存、2.1GHz处理器（疑似单核）。当前各表数据量如下：

• customers表约3000条记录
• rentals表约150000条记录
• rentals_billing表约150000条记录
• vehicles表约8000条记录

该查询频繁超时（耗时超过10-20秒），我已尝试为涉及的表和键创建索引但效果不佳，请问是否有优化该查询的方法？

优化方案 #

1. 修正WHERE子句逻辑优先级

原查询中OR与AND的优先级会导致逻辑模糊，同时限制优化器的索引利用率。用括号明确分组条件，先过滤掉无效数据再处理分支：

WHERE  
    customers.delete_date IS NULL
    AND rentals.delete_date IS NULL
    AND vehicles.delete_date IS NULL
    AND rentals.mode IN ('MODEA', 'MODEB', 'MODEC')
    AND (
        (rentals_billing.customer_id_facsimile IS NULL AND rentals_billing.rental_payable = 1) 
        OR 
        ((rentals_billing.missing_rent_id_doc IS NULL OR rentals_billing.missing_rent_id_doc <= 0) AND rentals_billing.missing_rent_fee > 0)
    )

2. 优化去重逻辑，减少数据处理量

将DISTINCTROW替换为DISTINCT，并通过子查询先筛选出符合条件的customer_id，再关联customers表取名称，缩小去重范围：

SELECT 
    c.id,
    c.customer_name  
FROM customers c
INNER JOIN (
    SELECT DISTINCT r.id_customer
    FROM rentals r
    INNER JOIN rentals_billing rb ON r.id = rb.id_rental  
    INNER JOIN vehicles v ON v.id = r.id_vehicle  
    WHERE  
        r.delete_date IS NULL
        AND v.delete_date IS NULL
        AND r.mode IN ('MODEA', 'MODEB', 'MODEC')
        AND (
            (rb.customer_id_facsimile IS NULL AND rb.rental_payable = 1) 
            OR 
            ((rb.missing_rent_id_doc IS NULL OR rb.missing_rent_id_doc <= 0) AND rb.missing_rent_fee > 0)
        )
) AS valid_customers ON c.id = valid_customers.id_customer
WHERE c.delete_date IS NULL;

3. 创建针对性复合索引

索引需匹配过滤、关联的顺序，建议创建以下索引：

• rentals表：(delete_date, mode, id_customer, id, id_vehicle)
  先过滤delete_date和mode，再覆盖关联所需字段
• rentals_billing表：(id_rental, customer_id_facsimile, rental_payable, missing_rent_id_doc, missing_rent_fee)
  先关联rentals，再覆盖WHERE条件中的字段，避免回表
• vehicles表：(id, delete_date)
  快速校验车辆是否未删除
• customers表：(id, delete_date, customer_name)
  覆盖查询输出字段，无需额外查找原表

4. 拆分OR条件为UNION ALL

OR是索引失效的常见诱因，可拆分为两个独立查询用UNION ALL合并（添加去重逻辑避免重复数据）：

SELECT c.id, c.customer_name
FROM customers c
INNER JOIN rentals r ON c.id = r.id_customer
INNER JOIN rentals_billing rb ON r.id = rb.id_rental
INNER JOIN vehicles v ON r.id_vehicle = v.id
WHERE
    c.delete_date IS NULL
    AND r.delete_date IS NULL
    AND v.delete_date IS NULL
    AND r.mode IN ('MODEA', 'MODEB', 'MODEC')
    AND rb.customer_id_facsimile IS NULL 
    AND rb.rental_payable = 1

UNION ALL

SELECT c.id, c.customer_name
FROM customers c
INNER JOIN rentals r ON c.id = r.id_customer
INNER JOIN rentals_billing rb ON r.id = rb.id_rental
INNER JOIN vehicles v ON r.id_vehicle = v.id
WHERE
    c.delete_date IS NULL
    AND r.delete_date IS NULL
    AND v.delete_date IS NULL
    AND r.mode IN ('MODEA', 'MODEB', 'MODEC')
    AND (rb.missing_rent_id_doc IS NULL OR rb.missing_rent_id_doc <= 0)
    AND rb.missing_rent_fee > 0
    AND NOT (rb.customer_id_facsimile IS NULL AND rb.rental_payable = 1)

拆分后每个子查询都能高效利用索引，降低优化器的计算负担。

5. 调整MySQL内存配置

针对4GB内存的服务器，修改my.ini中的核心参数：

• innodb_buffer_pool_size = 2G（分配一半内存给InnoDB缓存）
• query_cache_size = 64M（开启查询缓存，适配重复执行的场景）
• sort_buffer_size = 2M
• read_rnd_buffer_size = 2M
• join_buffer_size = 2M
  提升内存使用率，减少磁盘IO开销。

6. 清理表碎片

确保所有表使用InnoDB引擎，定期执行碎片整理：

OPTIMIZE TABLE customers;
OPTIMIZE TABLE rentals;
OPTIMIZE TABLE rentals_billing;
OPTIMIZE TABLE vehicles;

过多碎片会导致索引查找效率下降。

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