select 쿼리 실행 이후 테이블 먹통 문제

Question

azure 에서 mariadb database를 사용하고 있습니다SELECT u.idx AS user_idx, u.name_kr AS user_name, u.gender AS user_gender, TIMESTAMPDIFF(YEAR, STR_TO_DATE(u.birYMD, '%Y%m%d'), CURDATE()) AS user_age, SUM(ar.actually_loan_amount) AS total_loan_amount, SUM(ar.actually_loan_terms_months) AS total_loan_terms, COUNT(ar.idx) AS loan_count, ar.actually_loan_amount AS last_loan_amount, ar.actually_loan_terms_months AS last_loan_terms, ar.contract_date AS last_loan_date, IFNULL( (SELECT CASE WHEN rm.repayment_date IS NOT NULL THEN '완료' ELSE '미완료' END FROM repayment_month rm WHERE rm.approval_rate22idx = ar.idx ORDER BY rm.repayment_date DESC LIMIT 1), '미완료') AS last_repayment_status, fb.facebook_like_count, fb.facebook_img_count, fb.facebook_post_count, fb.facebook_comment_count, fb.facebook_friend_count, insta.instagram_post_count, insta.instagram_follower_count, insta.instagram_like_count, insta.instagram_comment_count, ks.kakao_story_post_count, ks.kakao_story_img_count, ks.kakao_story_like_count, ks.kakao_story_comment_count, nb.naver_blog_post_count FROM user u LEFT JOIN approval_rate ar ON ar.user22idx = u.idx AND ar.ast_grade22idx < 400000000 LEFT JOIN ( SELECT fb_info.user_id, SUM(fb_post.post_like) AS facebook_like_count, COUNT(fb_img.id) AS facebook_img_count, COUNT(fb_post.id) AS facebook_post_count, COUNT(fb_comment.id) AS facebook_comment_count, fb_info.friend_cnt AS facebook_friend_count FROM facebook_info fb_info LEFT JOIN facebook_post fb_post ON fb_post.info_id = fb_info.id LEFT JOIN facebook_img fb_img ON fb_img.info_id = fb_info.id LEFT JOIN facebook_comment fb_comment ON fb_comment.info_id = fb_info.id GROUP BY fb_info.user_id ) fb ON fb.user_id = u.idx LEFT JOIN ( SELECT insta_info.user_id, COUNT(insta_post.id) AS instagram_post_count, insta_info.follower_cnt AS instagram_follower_count, SUM(insta_post.post_like) AS instagram_like_count, COUNT(insta_comment.id) AS instagram_comment_count FROM instagram_info insta_info LEFT JOIN instagram_post insta_post ON insta_post.info_id = insta_info.id LEFT JOIN instagram_comment insta_comment ON insta_comment.info_id = insta_info.id GROUP BY insta_info.user_id ) insta ON insta.user_id = u.idx LEFT JOIN ( SELECT ks_info.user_id, COUNT(ks_post.id) AS kakao_story_post_count, ks_info.photo_cnt AS kakao_story_img_count, SUM(ks_post.post_like) AS kakao_story_like_count, COUNT(ks_comment.id) AS kakao_story_comment_count FROM kakaostory_info ks_info LEFT JOIN kakaostory_post ks_post ON ks_post.info_id = ks_info.id LEFT JOIN kakaostory_comment ks_comment ON ks_comment.info_id = ks_info.id GROUP BY ks_info.user_id ) ks ON ks.user_id = u.idx LEFT JOIN ( SELECT nb_info.user_id, COUNT(nb_post.id) AS naver_blog_post_count FROM naverblog_info nb_info LEFT JOIN naverblog_post nb_post ON nb_post.info_id = nb_info.id GROUP BY nb_info.user_id ) nb ON nb.user_id = u.idx GROUP BY u.idx HAVING ar.contract_date = MAX(ar.contract_date) ORDER BY u.idx;이 쿼리를 실행이후 관련된 테이블들 전부가 먹통이 됐습니다heidisql에서 그냥 테이블 클릭만 해도 프로그램 자체가 렉걸려버리고python에서 테이블 불러오려고해도 반응이 없고 계속 실행중인 상태로 멈춰있습니다check table 이나 repair table 실행해도 뭐 결과는 안나오고 계속 실행중인 상태입니다그래서 azure에서 하루 전이나 일주일 전으로 복원해도 똑같은 증상이 나타나네요해결방법이 있을까요?

오후 · Accepted Answer

단순 select 라도 데이터양이 얼마나 많은지(join, grouping 등)DB서버 사양이 어떻게되는지등에 따라 리소스 사용률이 천차만별입니다.explain 으로 실행계획확인해보세요

select 쿼리 실행 이후 테이블 먹통 문제

1개의 답변

Q&A 목록