做项目过程中遇到了一些查询很慢的SQL语句,本次记录一下处理问题的过程及方法
如何定位到返回数据慢的问题?
1、使用谷歌浏览器查看接口请求时间的组成
查看Watting(TTFB)的时间,如果时间过程则是定位到后端服务处理问题时间较长!
Watting(TTFB)解析参考
2、定位到执行的sql语句
3、进行SQL语句分析,使用explain关键字
explain
SELECT co.orderId,
co.type,
co.way,
co.search,
co.dept,
co.orderProcess,
co.pharmacyId,
ud.name as doctorName,
up.name as launchName,
co.name,
co.sex,
co.age,
co.phone,
co.allergy,
co.guardianName,
co.guardianPhone,
co.guardianIdCard,
co.downloadUrl,
co.consultProcess,
co.recipelProcess,
co.dispenseProcess,
co.processContent,
(SELECT us.name
FROM user_staff us
WHERE us.userId = co.applyUserId) as applyName,
co.applyDate,
(SELECT us.name
FROM user_staff us
WHERE us.userId = co.auditUserId) as auditName,
co.auditDate,
(SELECT us.name
FROM user_staff us
WHERE us.userId = co.dispenseUserId) as dispenseName,
co.dispenseDate,
co.printNum
FROM consult_order co
LEFT JOIN user_doctor ud
ON ud.userId = co.targetEasemob
LEFT JOIN user_pharmacy up
ON up.userId = co.pharmacyId
WHERE co.consultProcess = 4
AND co.recipelProcess = 0
AND DATE_FORMAT(co.applyDate, '%Y-%m-%d') >= '2021-05-31'
AND DATE_FORMAT(co.applyDate, '%Y-%m-%d') <= '2021-05-31'
AND co.way <> 0
ORDER BY co.applyDate DESC;
分析结果
type为sql执行过程中使用索引的重要指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
4、建立索引
建立索引可以使用Navicat或者DataGrip等数据库工具!
建立索引的SQL参考
create index consult_order_orderId_index
on consult_order (orderId);