优化sql系列之Show Warnings

---

title: 优化sql系列之Show Warnings tags:

• mysql
• explain
• showWarnings categories: mysql date: 2017-10-13 16:14:55

---

我们平时使用explain来查看相关的执行计划

    SELECT
        sum(t.amount) + IFNULL(sum(t.vip_expense), 0) + IFNULL(
            sum(t.customer_level_favourable),
            0
        ) + IFNULL(sum(t.package_favourable), 0)
    FROM
        tf_payment t
    LEFT JOIN tb_user tu ON tu.pk_id = t.modifier
    LEFT JOIN tm_employee te ON te.pk_id = tu.id_employee
    LEFT JOIN tb_user tbu ON tbu.pk_id = t.id_settle_person
    LEFT JOIN tm_employee tbe ON tbe.pk_id = tbu.id_employee
    LEFT JOIN ts_maintain p ON p.pk_id = t.id_source_bill
    LEFT JOIN tm_car r ON p.id_car = r.pk_id
    LEFT JOIN tm_customer s ON p.id_customer = s.pk_id
    LEFT JOIN tm_employee pe ON p.id_employee = pe.pk_id
    LEFT JOIN ts_member_card tmc ON tmc.pk_id = t.id_source_bill
    LEFT JOIN ta_car_member tcm ON tmc.id_member = tcm.id_member
    LEFT JOIN tm_car tcr ON tcm.id_car = tcr.pk_id
    LEFT JOIN tm_customer tcs ON tcm.id_customer = tcs.pk_id
    WHERE
        t.id_own_org = CAST(
            '10545360219000381015' AS UNSIGNED
        )
    AND t.is_del = 0
    AND t.business_type = 'YSD'
    AND t.id_own_org IN (
        CAST(
            '10545360219000381015' AS UNSIGNED
        )
    );
复制代码

我们想来查看一下执行计划优化过原Sql执行的“实际”sql呢

    EXPLAIN SELECT
        sum(t.amount) + IFNULL(sum(t.vip_expense), 0) + IFNULL(
            sum(t.customer_level_favourable),
            0
        ) + IFNULL(sum(t.package_favourable), 0)
    FROM
        tf_payment t
    LEFT JOIN tb_user tu ON tu.pk_id = t.modifier
    LEFT JOIN tm_employee te ON te.pk_id = tu.id_employee
    LEFT JOIN tb_user tbu ON tbu.pk_id = t.id_settle_person
    LEFT JOIN tm_employee tbe ON tbe.pk_id = tbu.id_employee
    LEFT JOIN ts_maintain p ON p.pk_id = t.id_source_bill
    LEFT JOIN tm_car r ON p.id_car = r.pk_id
    LEFT JOIN tm_customer s ON p.id_customer = s.pk_id
    LEFT JOIN tm_employee pe ON p.id_employee = pe.pk_id
    LEFT JOIN ts_member_card tmc ON tmc.pk_id = t.id_source_bill
    LEFT JOIN ta_car_member tcm ON tmc.id_member = tcm.id_member
    LEFT JOIN tm_car tcr ON tcm.id_car = tcr.pk_id
    LEFT JOIN tm_customer tcs ON tcm.id_customer = tcs.pk_id
    WHERE
        t.id_own_org = CAST(
            '10545360219000381015' AS UNSIGNED
        )
    AND t.is_del = 0
    AND t.business_type = 'YSD'
    AND t.id_own_org IN (
        CAST(
            '10545360219000381015' AS UNSIGNED
        )
    );
    SHOW WARNINGS;
复制代码

执行完执行计划之后再执行

    SHOW WARNINGS;
复制代码

    /* select#1 */ select (((sum(`f6dms`.`t`.`amount`) + ifnull(sum(`f6dms`.`t`.`vip_expense`),0)) + ifnull(sum(`f6dms`.`t`.`customer_level_favourable`),0)) + ifnull(sum(`f6dms`.`t`.`package_favourable`),0)) AS `sum(t.amount) + IFNULL(sum(t.vip_expense), 0) + IFNULL(
            sum(t.customer_level_favourable),
            0
        ) + IFNULL(sum(t.package_favourable), 0)` from `f6dms`.`tf_payment` `t` left join `f6dms`.`tb_user` `tu` on((`f6dms`.`tu`.`pk_id` = `f6dms`.`t`.`modifier`)) left join `f6dms`.`tm_employee` `te` on((`f6dms`.`te`.`pk_id` = `f6dms`.`tu`.`id_employee`)) left join `f6dms`.`tb_user` `tbu` on((`f6dms`.`tbu`.`pk_id` = `f6dms`.`t`.`id_settle_person`)) left join `f6dms`.`tm_employee` `tbe` on((`f6dms`.`tbe`.`pk_id` = `f6dms`.`tbu`.`id_employee`)) left join `f6dms`.`ts_maintain` `p` on((`f6dms`.`p`.`pk_id` = `f6dms`.`t`.`id_source_bill`)) left join `f6dms`.`tm_car` `r` on((`f6dms`.`r`.`pk_id` = `f6dms`.`p`.`id_car`)) left join `f6dms`.`tm_customer` `s` on((`f6dms`.`s`.`pk_id` = `f6dms`.`p`.`id_customer`)) left join `f6dms`.`tm_employee` `pe` on((`f6dms`.`pe`.`pk_id` = `f6dms`.`p`.`id_employee`)) left join `f6dms`.`ts_member_card` `tmc` on((`f6dms`.`tmc`.`pk_id` = `f6dms`.`t`.`id_source_bill`)) left join `f6dms`.`ta_car_member` `tcm` on((`f6dms`.`tcm`.`id_member` = `f6dms`.`tmc`.`id_member`)) left join `f6dms`.`tm_car` `tcr` on((`f6dms`.`tcr`.`pk_id` = `f6dms`.`tcm`.`id_car`)) left join `f6dms`.`tm_customer` `tcs` on((`f6dms`.`tcs`.`pk_id` = `f6dms`.`tcm`.`id_customer`)) where ((`f6dms`.`t`.`is_del` = 0) and (`f6dms`.`t`.`id_own_org` = 10545360219000381015) and (`f6dms`.`t`.`business_type` = 'YSD'))

复制代码