背景:線上反映一個訂單查詢比較卡要差不多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ù)訂單號查詢的時候使用了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)時做這個功能的時候主要目的是查看近7天的訂單医舆,所以在訂單的創(chuàng)建時間字段上面加上了索引,但是有時候查詢的時間跨度稍微長一點象缀,mysql的查詢分析器實際查詢的時候沒有使用該索引蔬将,導(dǎo)致查詢過慢,所以才會使用FORCE INDEX (create_time)央星,強(qiáng)制mysql使用該索引查詢數(shù)據(jù)霞怀,沒想到就是因為這個原因,導(dǎo)致該用主鍵索引的時候沒有正確的使用莉给。
解決:如果前端提交了時間毙石,查詢一個時間段的訂單數(shù)據(jù)的時候才把FORCE INDEX (create_time)加上。
···
···