问题:
近日,线上MySQL查出一个慢sql,每次都要查询1000ms以上,严重影响用户体验
今得空去诊断一番,记录如下:
sql原句:
SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r
LEFT JOIN
( SELECT card_id FROM card_member WHERE user_id = #uid# and card_member.deleted=0
UNION
SELECT card_id FROM card_subscribed where user_id = #uid# and card_subscribed.deleted=0
) m ON r.object_id = m.card_id
WHERE r.object_type = #objectType# AND r.deleted = 0
GROUP BY r.object_id;
解决问题:
由于对数据库优化一知半解,完全无从下手,只能求助度娘和谷哥了,试验了各种方法,都不见效果
几番周折之后,最终把注意力集中到了临时表上,因为explain查看执行计划,可以看到Using temporary
MySQL在执行SQL查询时可能会用到临时表,一般情况下,用到临时表就意味着性能较低。
于是想办法修改sql语句,摒弃临时表,修改如下:
SELECT r.object_id AS cardId,