看到這個題目你敢相信自己的眼睛嗎？居然有人敢動祖傳代碼？沒錯，那個人就是我，而且這次不僅要動而且要調優（心中一萬個無奈，實在是沒辦法）。不過這次調優其實也挺經典的，于是整理了一下發出來給各位品鑒一下，希望對各位有用。

本次調優的難點：

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

首先，先看看再“非登錄”狀態下接口的響應時間，如下圖：

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

言歸正傳，當登錄后再查詢時性能急劇下降，如下圖：

問了最后一位修改的高人得知，他已經在Java層面優化過了，若不重構的情況下已經沒有可以繼續優化的地方了。所以這次調優主要將集中精力優化SQL查詢，先看看登錄后的查詢語句。執行的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行腳本...通過拆解分析，發現腳本可以通過UNION關鍵字拆解成兩部分，在此之前先在客戶端直接運行看看執行效率，如下圖：

分頁返回10條數據，總耗時為2.29秒。

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

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

“Range checked for each record”在以前其他調優分享里也說過，當前表的連接字段雖然有一個possibile_key的字段，但是MySQL的執行分析器在執行期間由于“某種”原因沒有使用到該索引（從上圖也看到了，雖然pn，pd兩表都有possibile_key但是key和key_len都是null的，證明他們都沒有走索引）因此出現了Range checked的提示，表示連接中的每一條記錄都需要進行檢查。因此這個報錯也是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字段進行連接的，procurement_id字段是p這張表的主鍵，而pn、pd兩張表procurement_id字段是他們的數據外鍵，本應該是不存在問題的。但是通過對比p、pn、pd這三張表得知，p表中procurement_id字段是bigint的數據類型，而pn、pd表中procurement_id數據類型是varchar類型，因此explain中不走索引的原因極有可能是因為數據類型不一致導致的**（又是數據類型不一致導致的性能問題）** 。

因為字段數據類型不一致，所以在on的時候需要將外表中的字段先隱式轉型成內表字段對應的數據類型后再做關聯，在這個過程中其實跟下面的語句是等價的：

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作為外聯表放在=的前面，而外表字段又要使用CAST函數對字段進行類型轉換，因此該字段不走索引。

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

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

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

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

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

如上圖所示暫時沒有發現其他特殊的情況，接下來就直接運行看看查詢效果，如下圖：

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

在賬號登錄的狀態下接口從5.42秒提升到0.82秒，執行效率提升了81.5%。