记录一次生产慢查询的完整解决方案

慢查询SQL

SELECT 
    a.dealer_code,
    a.dealer_name_fc AS dealer_name,
    a.view_address,
    a.address_lati AS addressLatitude,
    a.address_long AS addressLongitude,
    a.city_code,
    a.province_code,
    a.district_code,
    a.dealer_name_fc,
    a.address,
    a.is_coming_soon,
    a.busi_start_time,
    a.busi_end_time,
    a.dealer_phone_fc AS dealerPhone,
    a.dealer_type,
    a.dealer_img AS url,
    a.weekend_start_time,
    a.weekend_end_time,
    a.holiday_start_date,
    a.holiday_end_date,
    a.holiday_start_time,
    a.holiday_end_time,
    a.noa_tag,
    a.duty_tag,
    a.recommend_tag,
    a.is_recommend_dealer AS recommend_dealer_flag
FROM
    cb_dealer_info a
WHERE
    a.can_use_tag = 1
        AND a.is_recommend_dealer = TRUE
        AND a.show_status_fc = 10071001
        AND a.city_code = '310100'
        AND NOT EXISTS( SELECT 
            1
        FROM
            cb_dealer_type_relation cb
        WHERE
            a.dealer_code = cb.relation_dealer_code)
        AND a.dealer_type IN (10011001 , 10011002);

深度优化

索引问题描述

cb_dealer_info主表的索引现状UAT如下：

cb_dealer_type_relation关系表的索引现状UAT如下：

问题1：索引缺失

涉及查询条件的5个字段，索引缺失：can_use_tag, is_recommend_dealer, show_status_fc, city_code, dealer_type

问题2：查询条件只有city_code，索引里的city_code作为复合索引的第2项，无法触发索引

解决办法有2个：

给city_code字段单独添加一个独立的索引
在city_code查询条件之前，补充一个province_code

查询条件优先级问题

综合分析当前数据库的数据分布情况和各项查询条件的实际过滤效果，定制化如下where条件从左到右的过滤顺序：

cb_dealer_info主表字段：city_code > dealer_type > show_status_fc > is_recommend_dealer > can_use_tag

写法1：NOT IN 适用于cb_dealer_type_relation是小表的场景

SELECT 
    a.dealer_code,
    a.dealer_name_fc AS dealer_name,
    a.view_address,
    a.address_lati AS addressLatitude,
    a.address_long AS addressLongitude,
    a.city_code,
    a.province_code,
    a.district_code,
    a.dealer_name_fc,
    a.address,
    a.is_coming_soon,
    a.busi_start_time,
    a.busi_end_time,
    a.dealer_phone_fc AS dealerPhone,
    a.dealer_type,
    a.dealer_img AS url,
    a.weekend_start_time,
    a.weekend_end_time,
    a.holiday_start_date,
    a.holiday_end_date,
    a.holiday_start_time,
    a.holiday_end_time,
    a.noa_tag,
    a.duty_tag,
    a.recommend_tag,
    a.is_recommend_dealer AS recommend_dealer_flag
FROM
    cb_dealer_info a
WHERE
    a.city_code = '310100'
        AND a.dealer_type IN (10011001 , 10011002)
        AND a.show_status_fc = 10071001
        AND a.is_recommend_dealer = TRUE
        AND a.can_use_tag = 1
        AND a.dealer_code NOT IN (SELECT 
            relation_dealer_code
        FROM
            cb_dealer_type_relation cb);

写法2：LEFT JOIN 适用于cb_dealer_type_relation是大表的场景

SELECT 
    a.dealer_code,
    a.dealer_name_fc AS dealer_name,
    a.view_address,
    a.address_lati AS addressLatitude,
    a.address_long AS addressLongitude,
    a.city_code,
    a.province_code,
    a.district_code,
    a.dealer_name_fc,
    a.address,
    a.is_coming_soon,
    a.busi_start_time,
    a.busi_end_time,
    a.dealer_phone_fc AS dealerPhone,
    a.dealer_type,
    a.dealer_img AS url,
    a.weekend_start_time,
    a.weekend_end_time,
    a.holiday_start_date,
    a.holiday_end_date,
    a.holiday_start_time,
    a.holiday_end_time,
    a.noa_tag,
    a.duty_tag,
    a.recommend_tag,
    a.is_recommend_dealer AS recommend_dealer_flag
FROM
    cb_dealer_info a
        LEFT JOIN
    cb_dealer_type_relation cb ON a.dealer_code = cb.relation_dealer_code
WHERE
    a.city_code = '310100'
        AND a.dealer_type IN (10011001 , 10011002)
        AND a.show_status_fc = 10071001
        AND a.is_recommend_dealer = TRUE
        AND a.can_use_tag = 1
        AND cb.relation_dealer_code IS NULL;