背景:线上反映一个订单查询比较卡要差不多4~5秒才能出结果。
疑问:根据订单号查询,如果在订单号上加了唯一索引应该很快就能查出结果啊?
前提:线上的表的索引该加的都加了。
把线上的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
发现:在根据订单号查询的时候使用了like和or关键字,所以先把like和or关键字去掉了,再一次查询速度快了不少,但是还需要0.4S左右(订单表jc_order里面有30W数据),仔细查看分析结果发现订单表还是使用全表扫描。
回顾:当时做这个功能的时候主要目的是查看近7天的订单,所以在订单的创建时间字段上面加上了索引,但是有时候查询的时间跨度稍微长一点,mysql的查询分析器实际查询的时候没有使用该索引,导致查询过慢,所以才会使用FORCE INDEX (create_time),强制mysql使用该索引查询数据,没想到就是因为这个原因,导致该用主键索引的时候没有正确的使用。
解决:如果前端提交了时间,查询一个时间段的订单数据的时候才把FORCE INDEX (create_time)加上。
···
···