MySQL慢查询优化求助：千万级大表关联小表查询耗时过长

现有查询的核心问题 #

• 子查询逻辑不严谨：select sale_id from dailyreports.tmp_evs_MAX_3 group by date属于非法聚合，非聚合字段sale_id既没有出现在GROUP BY子句中，也没有被聚合函数包裹，执行时会返回每个date分组下随机的sale_id，结果本身不符合业务预期。
• 执行策略不合理：当前IN子查询的执行逻辑被解析为全表扫描1800万行的大表evs_sale去匹配小表，完全没有利用tmp_evs_MAX_3只有29条记录的优势。
• 缺少必要索引：关联字段没有索引的情况下，每次匹配都需要遍历全表，耗时自然很高。

优化步骤 #

1. 修正子查询逻辑 #

先确认业务需求调整子查询：如果是要取tmp_evs_MAX_3表中所有的sale_id做匹配，直接去掉无效的group by即可；如果确实需要按日期分组取对应sale_id，要补充聚合逻辑比如select max(sale_id) from dailyreports.tmp_evs_MAX_3 group by date，保证返回结果确定。

2. 替换IN为JOIN语法，用小表驱动大表 #

用INNER JOIN代替IN子查询，数据库优化器更容易识别到小表驱动的执行计划，避免全表扫描evs_sale，优化后的参考SQL如下：

create table tmp_e1 as 
select 
    a.sale_record_id,
    a.transaction_id,
    date(a.sale_date_time) as date,
    a.sale_amount,
    a.float_account_id,
    a.float_account_balance_after as close_Balance,
    a.sale_status_id,
    'max3' as type 
from dailyreports.evs_sale a
inner join (
    -- 此处根据实际业务需求调整为修正后的子查询逻辑
    select distinct sale_id from dailyreports.tmp_evs_MAX_3
) b on a.sale_record_id = b.sale_id;

3. 添加索引加速匹配 #

给两个表的关联字段加索引，可大幅降低匹配耗时：

• 给小表tmp_evs_MAX_3的sale_id字段加普通索引，如果是按日期聚合的场景可以加联合索引(date, sale_id)
• 给大表evs_sale的sale_record_id字段加普通索引（如果该字段是唯一值可以加唯一索引），避免全表扫描
• 如果该查询频率很高，还可以给evs_sale加覆盖索引：(sale_record_id, transaction_id, sale_date_time, sale_amount, float_account_id, float_account_balance_after, sale_status_id)，直接从索引中取数不需要回表查询。

4. 大数据引擎可选优化 #

如果使用的是Hive/Spark SQL这类大数据计算引擎，可以开启小表广播配置，把只有29条记录的tmp_evs_MAX_3广播到所有计算节点，避免shuffle操作，执行速度会进一步提升。

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