大佬们这个sql怎么优化啊，索引加了不管用啊

[Jkcc]

大佬们有啥优化建议吗，优化不动了我

 

sql脚本：
EXPLAIN SELECT
    t1.couname,
    t1.uname,
    t1.thirdPartyMemberId,
    t1.receivePhone,
    t1.status,
    t1.receiveTime,
    t1.verification_time,
    t1.verificationName,
    t1.verificationPhone,
    t2.NAME couPackageName,
    t1.oneBranchName,
    t1.twoBranchName,
    t1.oneSubbranchName,
    t1.twoSubbranchName,
    t1.staffid,
    t1.copartnername,
    t1.send_type,
    t1.isTestCoupon,
    t1.code_md,
    t1.activity_name,
    t1.countryname,
    t1.provincename,
    t1.cityname,
    t1.areaname,
    t1.userAffiliation,
    t1.receive_user_id,
    t1.receive_member_level_name 
FROM
    (
    SELECT
        cdkey.receive_user_affiliation userAffiliation,
        bi.one_branch_name AS oneBranchName,
        bi.two_branch_name AS twoBranchName,
        bi.one_subbranch_name AS oneSubbranchName,
        bi.two_subbranch_name AS twoSubbranchName,
        goods_id,
        info.couname,
        cdkey.receive_user_id,
        "user".uname,
        "user".third_party_member_id AS thirdPartyMemberId,
        cdkey.receive_user_phone receivePhone,
        cdkey.code_verification_status status,
        cdkey.receive_time receiveTime,
        cdkey.verification_time,
        rec.hxshopname verificationName,
        rec.username verificationPhone,
        cop.staffid,
        cop.copartnername,
    CASE
            
            WHEN cdkey.send_type IS NULL THEN
            4 ELSE cdkey.send_type 
        END AS send_type,
    CASE
            
            WHEN info.is_test_coupon IS NULL THEN
            0 ELSE info.is_test_coupon 
        END isTestCoupon,
    cdkey.code_md,
    orel.activity_name,
    "user".countryname,
    "user".provincename,
    "user".cityname,
    "user".areaname,
    cdkey.receive_member_level_name 
FROM
    t_jc_cdkey_coupon_code_info cdkey
    LEFT JOIN t_jc_copartner_info cop ON cdkey.copartnerid = cop.copartnerid
    LEFT JOIN t_jc_promote_bank_info AS bi ON bi.bankid = cop.bankid
    LEFT JOIN t_mini_ticket_coupon_info info ON cdkey.goods_id = info.
    ID LEFT JOIN t_jc_app_user_info "user" ON cdkey.receive_user_id = "user".
    ID LEFT JOIN t_mini_coupon_userecord rec ON cdkey.code_md = rec.couusecode
    LEFT JOIN t_mini_ticket_coupon_order_rel orel ON cdkey.code_md = orel.couusecode 
WHERE
    cdkey.del_status = 0 
    AND cdkey.audit_status = 2 
    AND cdkey.tisid IN ( 86 ) 
    AND ( code_receiving_status = 2 OR code_receiving_status = 5 ) 
GROUP BY
    cdkey.receive_user_affiliation,
    oneBranchName,
    twoBranchName,
    oneSubbranchName,
    twoSubbranchName,
    goods_id,
    info.couname,
    cdkey.receive_user_id,
    "user".tisid,
    "user".uname,
    "user".third_party_member_id,
    receivePhone,
    status,
    receiveTime,
    cdkey.verification_time,
    verificationName,
    verificationPhone,
    cop.staffid,
    cop.copartnername,
    cdkey.send_type,
    isTestCoupon,
    cdkey.code_md,
    orel.activity_name,
    "user".countryname,
    "user".provincename,
    "user".cityname,
    "user".areaname,
    cdkey.receive_member_level_name 
    ) AS t1
    LEFT JOIN t_jc_coupons_package_rel t2 ON t1.goods_id = t2.source_coupon_id 
GROUP BY
    couname,
    uname,
    thirdPartyMemberId,
    receivePhone,
    status,
    receiveTime,
    verification_time,
    verificationName,
    verificationPhone,
    couPackageName,
    oneBranchName,
    twoBranchName,
    oneSubbranchName,
    twoSubbranchName,
    staffid,
    copartnername,
    send_type,
    isTestCoupon,
    code_md,
    activity_name,
    countryname,
    provincename,
    cityname,
    areaname,
    t1.userAffiliation,
    t1.receive_user_id,
    t1.receive_member_level_name 
ORDER BY
    receiveTime DESC,
    verification_time DESC 
    LIMIT 10 OFFSET 0;
explain执行

 

[qq_50916410]

要不你单独建一个新的表，把需要特殊读取的据保存到那个表？

[太空漫步11]

请问一下为什么group by 后面跟这么多字段。

[djdjdowi]

把所有关联表都单独写个子查询，把where条件塞进子查询，之后再左右连接。例如

select * from (select * from test_left where a=1 and b=2) t_l left join (select * from test_right)  t_r on t_l.c=t_r.c