品鑒一下祖?zhèn)鱏QL腳本調(diào)優(yōu)方法

看到這個題目你敢相信自己的眼睛嗎？居然有人敢動祖?zhèn)鞔a？沒錯，那個人就是我，而且這次不僅要動而且要調(diào)優(yōu)（心中一萬個無奈，實在是沒辦法）。不過這次調(diào)優(yōu)其實也挺經(jīng)典的，于是整理了一下發(fā)出來給各位品鑒一下，希望對各位有用。

本次調(diào)優(yōu)的難點(diǎn)：

1. 本次腳本太過雍長，不知道之前那位高人幾乎將所有業(yè)務(wù)邏輯都寫到SQL里面了；
2. 據(jù)了解本次腳本已經(jīng)經(jīng)過3位高人之手調(diào)整過3次，只不過一直沒有調(diào)好。后來得知腳本在“登錄”和“非登錄”時會出現(xiàn)兩個分支處理，這是不恰當(dāng)使用Mybatis動態(tài)腳本特性出來的鍋；

首先，先看看再“非登錄”狀態(tài)下接口的響應(yīng)時間，如下圖：

如上圖所示接口在“非登錄”狀態(tài)下耗時1.76秒。 需要說明一下的是，圖片顯示的是7.83秒是整個事務(wù)操作的響應(yīng)結(jié)果（里面存在大量的實時統(tǒng)計與運(yùn)算，當(dāng)時并沒有針對運(yùn)算和代碼邏輯的優(yōu)化...其實說白了也不敢優(yōu)化，因此整個事務(wù)耗時比較長），圖片上說的接口與本次文章中說的接口并不是同一個接口，而有問題的接口經(jīng)排查耗時為1.76秒，因此本文中的圖片是為了直觀看出性能結(jié)果截取的并不是對應(yīng)接口真實的執(zhí)行時間（其實就是一句“懶”，不想寫log展示數(shù)據(jù)庫執(zhí)行時間了......） 。

言歸正傳，當(dāng)?shù)卿浐笤俨樵儠r性能急劇下降，如下圖：

問了最后一位修改的高人得知，他已經(jīng)在Java層面優(yōu)化過了，若不重構(gòu)的情況下已經(jīng)沒有可以繼續(xù)優(yōu)化的地方了。所以這次調(diào)優(yōu)主要將集中精力優(yōu)化SQL查詢，先看看登錄后的查詢語句。執(zhí)行的SQL腳本如下：

SELECT *
FROM
    (SELECT 
        p.procurement_id,
            p.display_type,
            p.publish_type,
            p.valid_time,
            p.pay_type,
            p.cust_id,
            p.add_user,
            t.trade_name,
            p.add_time,
            p.oper_user,
            p.oper_time,
            p.platform_audit_status,
            p.platform_back_reason,
            p.platform_audit_user,
            p.platform_audit_time,
            p.status,
            p.procurement_title,
            p.alive_flag,
            c.is_gsp,
            c.is_gmp,
            c.customer_service_user,
            IFNULL(IF(p.display_type = 2, sui.CONTACT_NAME, fc.CONTACT_NAME), '暫無') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無') AS cellphone,
            IF(fc.SEX = 1, '先生', '女士') AS sex,
            IF(INSTR(GROUP_CONCAT(t.TRADE_PUBLISH_STATE), '0') > 0, 0, 1) AS TRADE_PUBLISH_STATE,
            IF(p.display_type = 2, '*******', c.CUST_NAME) AS CUST_NAME,
            SUM(IF((SELECT 
                    COUNT(0)
                FROM
                    spot_procurement_details spd
                WHERE
                    FIND_IN_SET(spd.trade_name_id, '35,65,124,1145,1168,255,288,81,')
                        AND spd.procurement_detail_id = pd.procurement_detail_id) > 0, 1, 0)) AS flag,
            pn.status AS inviteStatus,
            pn.invitation_id,
            pn.send_time,
            (SELECT IF(p.valid_time >= DATE_FORMAT(NOW(), '%Y-%m-%d'), 1, 2)) AS info_status,
            IF(p.status = 1, 1, IF(p.status = 6, 1.5, 2)) AS proc_status,
            p.top_type AS topType,
            p.top_time AS topTime
    FROM spot_procurement p
    LEFT JOIN spot_procurement_invitation pn ON pn.procurement_id = p.procurement_id
    LEFT JOIN spot_procurement_details pd ON pd.procurement_id = p.procurement_id
    LEFT JOIN spot_trade_name t ON t.trade_name_id = pd.trade_name_id
    LEFT JOIN spot_frequent_contacts fc ON p.cust_id = fc.CUST_ID AND fc.ALIVE_FLAG = 1 AND fc.IS_FREQUENT = 1
    LEFT JOIN spot_company c ON c.cust_id = p.cust_id
    LEFT JOIN spot_user_info sui ON c.CUSTOMER_SERVICE_USER = sui.USER_ID
    WHERE p.platform_audit_status = 1 AND p.alive_flag = 1 AND p.status >= 1
            AND (pd.is_split IS NULL OR pd.is_split != 'Y')
            AND (pn.receive_cust_id = '100000000000365' OR p.publish_type = 2)
            AND p.top_Type IN (1 , '3')
    GROUP BY p.procurement_id UNION (SELECT 
        p.procurement_id,
            p.display_type,
            p.publish_type,
            p.valid_time,
            p.pay_type,
            p.cust_id,
            p.add_user,
            t.trade_name,
            p.add_time,
            p.oper_user,
            p.oper_time,
            p.platform_audit_status,
            p.platform_back_reason,
            p.platform_audit_user,
            p.platform_audit_time,
            p.status,
            p.procurement_title,
            p.alive_flag,
            c.is_gsp,
            c.is_gmp,
            c.customer_service_user,
            IFNULL(IF(p.display_type = 2, sui.CONTACT_NAME, fc.CONTACT_NAME), '暫無') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無') AS cellphone,
            IF(fc.SEX = 1, '先生', '女士') AS sex,
            IF(INSTR(GROUP_CONCAT(t.TRADE_PUBLISH_STATE), '0') > 0, 0, 1) AS TRADE_PUBLISH_STATE,
            IF(p.display_type = 2, '*******', c.CUST_NAME) AS CUST_NAME,
            SUM(IF((SELECT COUNT(0)
                FROM spot_procurement_details spd
                WHERE FIND_IN_SET(spd.trade_name_id, '35,65,124,1145,1168,255,288,81,')
                        AND spd.procurement_detail_id = pd.procurement_detail_id) > 0, 1, 0)) AS flag,
            pn.status AS inviteStatus,
            pn.invitation_id,
            pn.send_time,
            (SELECT IF(p.valid_time >= DATE_FORMAT(NOW(), '%Y-%m-%d'), 1, 2)) AS info_status,
            IF(p.status = 1, 1, IF(p.status = 6, 1.5, 2)) AS proc_status,
            p.top_type AS topType,
            p.top_time AS topTime
    FROM spot_procurement p
    LEFT JOIN spot_procurement_invitation pn ON pn.procurement_id = p.procurement_id
    LEFT JOIN spot_procurement_details pd ON pd.procurement_id = p.procurement_id
    LEFT JOIN spot_trade_name t ON t.trade_name_id = pd.trade_name_id
    LEFT JOIN spot_frequent_contacts fc ON p.cust_id = fc.CUST_ID AND fc.ALIVE_FLAG = 1 AND fc.IS_FREQUENT = 1
    LEFT JOIN spot_company c ON c.cust_id = p.cust_id
    LEFT JOIN spot_user_info sui ON c.CUSTOMER_SERVICE_USER = sui.USER_ID
    WHERE
        p.platform_audit_status = 1 AND p.alive_flag = 1 AND p.status >= 1
            AND (pd.is_split IS NULL OR pd.is_split != 'Y')
            AND (pn.receive_cust_id = '100000000000365' OR p.publish_type = 2)
    GROUP BY p.procurement_id)) sss
WHERE sss.TRADE_PUBLISH_STATE = 1
ORDER BY sss.info_status ASC , sss.add_time DESC
LIMIT 0 , 10

這淺淺的107行腳本...通過拆解分析，發(fā)現(xiàn)腳本可以通過UNION關(guān)鍵字拆解成兩部分，在此之前先在客戶端直接運(yùn)行看看執(zhí)行效率，如下圖：

分頁返回10條數(shù)據(jù)，總耗時為2.29秒。

之后將嵌套查詢的內(nèi)部腳本拆解成兩部分，每部分都通過explain分析執(zhí)行結(jié)果，先看第一部分，如下圖：

從上圖中可以看出，除pn和pd兩表的連接出現(xiàn)異常外，其他表的連接都比較正常，最起碼它們都能夠走到索引了（key和key_len說明了索引的名稱和索引長度）。之后就看看pn和pd對應(yīng)的Extra列提示什么，返回的內(nèi)容是“Range checked for each record (index map: 0x2)”。

“Range checked for each record”在以前其他調(diào)優(yōu)分享里也說過，當(dāng)前表的連接字段雖然有一個possibile_key的字段，但是MySQL的執(zhí)行分析器在執(zhí)行期間由于“某種”原因沒有使用到該索引（從上圖也看到了，雖然pn，pd兩表都有possibile_key但是key和key_len都是null的，證明他們都沒有走索引）因此出現(xiàn)了Range checked的提示，表示連接中的每一條記錄都需要進(jìn)行檢查。因此這個報錯也是MySQL里面最慢的錯誤提示之一。

既然沒有走索引那就要看看為什么沒有走索引。pn、pd表的連接如下所示：

FROM spot_procurement p
LEFT JOIN spot_procurement_invitation pn ON pn.procurement_id = p.procurement_id
LEFT JOIN spot_procurement_details pd ON pd.procurement_id = p.procurement_id

其實兩個表都是p這張表的右連接，而且都是通過procurement_id字段進(jìn)行連接的，procurement_id字段是p這張表的主鍵，而pn、pd兩張表procurement_id字段是他們的數(shù)據(jù)外鍵，本應(yīng)該是不存在問題的。但是通過對比p、pn、pd這三張表得知，p表中procurement_id字段是bigint的數(shù)據(jù)類型，而pn、pd表中procurement_id數(shù)據(jù)類型是varchar類型，因此explain中不走索引的原因極有可能是因為數(shù)據(jù)類型不一致導(dǎo)致的**（又是數(shù)據(jù)類型不一致導(dǎo)致的性能問題）** 。

因為字段數(shù)據(jù)類型不一致，所以在on的時候需要將外表中的字段先隱式轉(zhuǎn)型成內(nèi)表字段對應(yīng)的數(shù)據(jù)類型后再做關(guān)聯(lián)，在這個過程中其實跟下面的語句是等價的：

FROM spot_procurement p
LEFT JOIN spot_procurement_invitation pn ON CAST(pn.procurement_id AS UNSIGNED integer) = p.procurement_id
LEFT JOIN spot_procurement_details pd ON CAST(pd.procurement_id AS UNSIGNED integer) = p.procurement_id

在這里看出了其他問題，pn、pd作為外聯(lián)表放在=的前面，而外表字段又要使用CAST函數(shù)對字段進(jìn)行類型轉(zhuǎn)換，因此該字段不走索引。

因此，在不改變原有邏輯的情況下修改成如下：

SELECT 
        p.procurement_id,
            p.display_type,
            p.publish_type,
            p.valid_time,
            p.pay_type,
            p.cust_id,
            p.add_user,
            t.trade_name,
            p.add_time,
            p.oper_user,
            p.oper_time,
            p.platform_audit_status,
            p.platform_back_reason,
            p.platform_audit_user,
            p.platform_audit_time,
            p.status,
            p.procurement_title,
            p.alive_flag,
            c.is_gsp,
            c.is_gmp,
            c.customer_service_user,
            IFNULL(IF(p.display_type = 2, sui.CONTACT_NAME, fc.CONTACT_NAME), '暫無') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無') AS cellphone,
            IF(fc.SEX = 1, '先生', '女士') AS sex,
            IF(INSTR(GROUP_CONCAT(t.TRADE_PUBLISH_STATE), '0') > 0, 0, 1) AS TRADE_PUBLISH_STATE,
            IF(p.display_type = 2, '*******', c.CUST_NAME) AS CUST_NAME,
            SUM(IF((SELECT COUNT(0)
                FROM spot_procurement_details spd
                WHERE FIND_IN_SET(spd.trade_name_id, '35,65,124,1145,1168,255,288,81')
                        AND spd.procurement_detail_id = pd.procurement_detail_id) > 0, 1, 0)) AS flag,
            pn.status AS inviteStatus,
            pn.invitation_id,
            pn.send_time,
            (SELECT IF(p.valid_time >= DATE_FORMAT(NOW(), '%Y-%m-%d'), 1, 2)) AS info_status,
            IF(p.status = 1, 1, IF(p.status = 6, 1.5, 2)) AS proc_status,
            p.top_type AS topType,
            p.top_time AS topTime
    FROM spot_procurement p
    LEFT JOIN 
    (select a.receive_cust_id,a.status,a.invitation_id,a.send_time, CAST(a.procurement_id AS UNSIGNED integer) as procurement_id from spot_procurement_invitation a) pn ON pn.procurement_id = p.procurement_id
    LEFT JOIN 
    (select b.procurement_detail_id,CAST(b.procurement_id AS UNSIGNED integer) as procurement_id,b.trade_name_id,b.is_split from spot_procurement_details b ) pd ON pd.procurement_id = p.procurement_id
    LEFT JOIN spot_trade_name t ON t.trade_name_id = pd.trade_name_id
    LEFT JOIN spot_frequent_contacts fc ON p.cust_id = fc.CUST_ID AND fc.ALIVE_FLAG = 1 AND fc.IS_FREQUENT = 1
    LEFT JOIN spot_company c ON c.cust_id = p.cust_id
    LEFT JOIN spot_user_info sui ON c.CUSTOMER_SERVICE_USER = sui.USER_ID
    WHERE p.platform_audit_status = 1 AND (pd.is_split IS NULL OR pd.is_split != 'Y')
            AND p.alive_flag = 1 AND p.status >= 1
            AND (pn.receive_cust_id = '100000000000365' OR p.publish_type = 2)
            AND p.top_Type IN (1 , '3')
    GROUP BY p.procurement_id

這里先將需要轉(zhuǎn)類型的字段做顯式轉(zhuǎn)換，然后再做join連接，通過explain后得出執(zhí)行計劃如下：

在外聯(lián)的時候使用了auto_key1帶代替了原來的null了，而a和b兩個表由于只是轉(zhuǎn)義用因此是全表掃描的。但是留意Extra列中已經(jīng)不存在Range checked的提示了。

接下來再看看第二部分的語句，經(jīng)過對比與第一部分的語句基本相似，因此可以使用同樣的優(yōu)化手段進(jìn)行sql的優(yōu)化，優(yōu)化后的整體explain執(zhí)行計劃如下圖：

如上圖所示暫時沒有發(fā)現(xiàn)其他特殊的情況，接下來就直接運(yùn)行看看查詢效果，如下圖：

在修改了sql之后再去驗證一下接口的加載速度，如下圖：

在賬號登錄的狀態(tài)下接口從5.42秒提升到0.82秒，執(zhí)行效率提升了81.5%。