如何优化MySQL慢查询？大表关联查询性能优化咨询

运行10年的应用中MySQL查询变慢，原因是cus_pro_price表数据增长至48万行，当前使用MySQL 3.23版本。查询语句及表结构如下：

查询语句 #

select v.product_id, v.product_name, c.price,c.customer_id, 
            n.customer_name, date_start, date_end 
            from vol_product v 
            left outer join cus_pro_price c on v.product_id=c.product_id 
            left outer join ntt_customer n on n.customer_id=c.customer_id 
            where v.status!='110' and  date_end >='2022-04-01' 
            and date_end between '2022-04-01' and '2022-10-31'

表结构 #

vol_product表 #

Field          Type          Null    Key Default Extra
product_name   varchar(50)   NO          
product_unit_id varchar(4)  NO          
status         int(11)       NO      0   
product_id     int(11)       NO  PRI NULL    auto_increment
sort_order     int(11)       NO      0   
quo_price      double(16,4)  NO      0.0000  

ntt_customer表 #

Field            Type                    Null    Key Default Extra
customer_id      int(11) unsigned        NO  PRI NULL    auto_increment
customer_name    varchar(50)             NO          
country_id       char(2)                 NO      h   
address          varchar(80)             YES     NULL    
tel              varchar(20)             NO          
fax              varchar(20)             YES     NULL    
credit_limit     double(16,4) unsigned   YES     NULL    
credit_balance   double(16,4) unsigned   YES     NULL    
day_allowance    int(11) unsigned        NO      30  
status_id        int(11)                 NO      0   
official_name    varchar(50)             NO          
customer_no      varchar(20)             NO          
line_no          int(11)                 NO      80  

cus_pro_price表 #

Field          Type          Null    Key Default Extra
id             int(10)       NO  PRI NULL    auto_increment
customer_id    int(11)       NO  MUL 0   
product_id     int(11)       NO      0   
price          double(16,2)  NO      0.00    
date_start     date          NO      0000-00-00  
date_end       date          YES     0000-00-00  
date_add       datetime      YES     0000-00-00 00:00:00 
autostatus     enum('0','1') NO      0   

当前仅cus_pro_price表有customer_id的MUL索引，其余无额外索引。该查询仅需cus_pro_price表中约1.6万符合date_end范围的数据，怀疑当前查询是关联后再过滤日期，询问提前过滤日期再关联是否能大幅提升性能，以及如何实现。

核心结论 #

提前过滤cus_pro_price表的日期条件再关联，确实能大幅提升性能。原查询中left join后再过滤date_end，会先把vol_product和cus_pro_price全量关联，再过滤出符合日期的1.6万行，中间产生的临时数据集远大于目标数据，导致性能低下。提前过滤cus_pro_price的日期条件，能直接缩小关联的数据集规模，减少后续关联和计算的开销。

实现方式 #

方式1：子查询提前过滤 #

将cus_pro_price的日期过滤逻辑放在子查询中，先得到符合条件的1.6万行数据，再与其他表关联：

select v.product_id, v.product_name, c.price, c.customer_id, 
       n.customer_name, c.date_start, c.date_end 
from vol_product v 
left outer join (
    select * from cus_pro_price 
    where date_end between '2022-04-01' and '2022-10-31'
) c on v.product_id = c.product_id 
left outer join ntt_customer n on n.customer_id = c.customer_id 
where v.status != 110

注：原查询中date_end >= '2022-04-01'和between重复，保留between即可；v.status是int类型，直接用数字110避免类型转换开销。

方式2：调整关联顺序（内联替代左联优化） #

如果业务上允许（即只需要存在符合日期的cus_pro_price记录的产品），可以将left join改为inner join并调整关联顺序，让MySQL先过滤cus_pro_price的日期条件：

select v.product_id, v.product_name, c.price, c.customer_id, 
       n.customer_name, c.date_start, c.date_end 
from cus_pro_price c
inner join vol_product v on v.product_id = c.product_id 
left outer join ntt_customer n on n.customer_id = c.customer_id 
where v.status != 110 
  and c.date_end between '2022-04-01' and '2022-10-31'

这种方式MySQL的查询优化器更倾向于先执行cus_pro_price的日期过滤，再关联其他表，性能提升更明显，但注意业务逻辑是否允许舍弃无对应cus_pro_price记录的产品。

额外索引优化（关键） #

MySQL 3.23虽然版本老旧，但合理的索引能进一步放大性能提升：

• 给cus_pro_price表创建复合索引：(date_end, product_id, customer_id, price, date_start)。这个索引覆盖了过滤条件date_end和关联需要的product_id、customer_id，以及查询返回的字段，能实现覆盖索引扫描，避免回表查询，大幅减少IO开销。
• 给vol_product表创建索引：(status, product_id, product_name)。覆盖where条件status != 110和查询返回的字段，加速该表的过滤和数据读取。
• ntt_customer表的customer_id是主键，已经是索引，无需额外创建，但确保关联时使用主键关联即可。

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