背景：線上反映一個(gè)訂單查詢比較卡要差不多4~5秒才能出結(jié)果。

360截圖1786060773106106.png

疑問：根據(jù)訂單號查詢，如果在訂單號上加了唯一索引應(yīng)該很快就能查出結(jié)果??？
前提：線上的表的索引該加的都加了。
把線上的sql弄下來分析：

SELECT
    count(1)
FROM
    (
        SELECT
            count(1) AS s
        FROM
            jc_order a FORCE INDEX (create_time)
        LEFT JOIN jc_order_ticket b ON a.order_code = b.order_code
        LEFT JOIN sys_user_line c1 ON a.line_id = c1.line_id
        WHERE
            1 = 1
        AND (
            a.order_code like '%JC17101000334970%'
            OR b.ticket_code like 'JC17101000334970%'
        
        )
        AND c1.user_id ='bf9a7695-d4c9-11e6-aa66-6c92bf2c'
        AND a. STATUS != 9
        GROUP BY
            a.order_code
    ) t

SELECT
    d1.detail_name AS status_name,
    d2.detail_name AS order_from_name,
    d3.detail_name AS pay_mode_name,
    a.order_code,
    a.trade_no,
    a.customer_id,
    AES_DECRYPT (
        unhex(a.mobile),
        'jIcEtWiBcAkCeQtA'
    ) AS mobile,
    a.ride_date,
    a.company_id,
    a.line_id,
    a.line_code,
    a.shift_id,
    a.journey_id,
    a.shift_code,
    a.start_station_id,
    a.start_station_name,
    a.end_station_id,
    a.end_station_name,
    a.start_city_id,
    a.start_city_name,
    a.end_city_id,
    a.end_city_name,
    a.start_time,
    a.end_time,
    a.price,
    a.quantity,
    a.yh_quantity,
    a.yh_price,
    a.children_quantity,
    a.children_price,
    a.coupon_code,
    a.coupon_price,
    a.total_price,
    a.actual_pay_price,
    a.order_type,
    IFNULL(a.quantity, 0) + IFNULL(a.yh_quantity, 0) + IFNULL(a.children_quantity, 0) + IFNULL(a.st_quantity, 0) AS total_count,
    a.mileage,
    a.contact_name,
    AES_DECRYPT(
        unhex(contact_mobile),
        'jIcEtWiBcAkCeQtA'
    ) AS contact_mobile,
    AES_DECRYPT(
        unhex(contact_id_card),
        'jIcEtWiBcAkCeQtA'
    ) AS contact_id_card,
    a.order_from,
    a.pay_mode,
    a.evaluate_flag,
    a.`status`,
    a.create_time,
    a.pay_time,
    a.remark,
    a.isnormal
FROM
    jc_order AS a FORCE INDEX (create_time)
LEFT JOIN jc_order_ticket b ON a.order_code = b.order_code
LEFT JOIN sys_dictionary_detail d1 ON d1.detail_value = a.`status`
AND d1.dictionary_code = 'order_status'
LEFT JOIN sys_dictionary_detail d2 ON d2.detail_value = a.order_from
AND d2.dictionary_code = 'order_from'
LEFT JOIN sys_dictionary_detail d3 ON d3.detail_value = a.pay_mode
AND d3.dictionary_code = 'pay_mode'
LEFT JOIN sys_user_line c1 ON a.line_id = c1.line_id
WHERE
    1 = 1
AND (
    a.order_code LIKE '%JC17101000334970%'
    OR b.ticket_code LIKE '%JC17101000334970%'
)
AND c1.user_id ='bf9a7695-d4c9-11e6-aa66-6c92bf2c'
AND a. STATUS != 9
GROUP BY
    a.order_code
ORDER BY
    create_time DESC
LIMIT 0 ,15

發(fā)現(xiàn)：在根據(jù)訂單號查詢的時(shí)候使用了like和or關(guān)鍵字，所以先把like和or關(guān)鍵字去掉了，再一次查詢速度快了不少，但是還需要0.4S左右（訂單表jc_order里面有30W數(shù)據(jù)）,仔細(xì)查看分析結(jié)果發(fā)現(xiàn)訂單表還是使用全表掃描。

360截圖17400126637664.png

回顧：當(dāng)時(shí)做這個(gè)功能的時(shí)候主要目的是查看近7天的訂單，所以在訂單的創(chuàng)建時(shí)間字段上面加上了索引，但是有時(shí)候查詢的時(shí)間跨度稍微長一點(diǎn)，mysql的查詢分析器實(shí)際查詢的時(shí)候沒有使用該索引，導(dǎo)致查詢過慢，所以才會使用FORCE INDEX (create_time)，強(qiáng)制mysql使用該索引查詢數(shù)據(jù)，沒想到就是因?yàn)檫@個(gè)原因，導(dǎo)致該用主鍵索引的時(shí)候沒有正確的使用。

解決：如果前端提交了時(shí)間，查詢一個(gè)時(shí)間段的訂單數(shù)據(jù)的時(shí)候才把FORCE INDEX (create_time)加上。
···

···