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

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

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

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

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

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

問了最后一位修改的高人得知，他已經(jīng)在Java層面優(yōu)化過(guò)了，若不重構(gòu)的情況下已經(jīng)沒有可以繼續(xù)優(yōu)化的地方了。所以這次調(diào)優(yōu)主要將集中精力優(yōu)化SQL查詢，先看看登錄后的查詢語(yǔ)句。執(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), '暫無(wú)') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無(wú)') 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), '暫無(wú)') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無(wú)') 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行腳本...通過(guò)拆解分析，發(fā)現(xiàn)腳本可以通過(guò)UNION關(guān)鍵字拆解成兩部分，在此之前先在客戶端直接運(yùn)行看看執(zhí)行效率，如下圖：

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

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

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

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

既然沒有走索引那就要看看為什么沒有走索引。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

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

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

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ù)對(duì)字段進(jìn)行類型轉(zhuǎn)換，因此該字段不走索引。

因此，在不改變?cè)羞壿嫷那闆r下修改成如下：

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), '暫無(wú)') AS CONTACT_NAME,
            IFNULL(IF(p.display_type = 2, sui.CELLPHONE, fc.cell_phone), '暫無(wú)') 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連接，通過(guò)explain后得出執(zhí)行計(jì)劃如下：

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

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

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

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

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