꿈꾸는 개발자, DBA 커뮤니티 구루비

mysql 쿼리 질문

by 미무미 [2021.12.24 13:53:05]

쿼리 수행속도가 매우 느린데요

아래의 쿼리에서 쿼리최적화를 할 건덕지가 있는지 궁금합니다.

그 쿼리는 다음과 같습니다.

select sum(cnt) as cnt from (

select * from ( select count(*) as cnt FROM `g5_write_apply` as a left outer join customer_list as b on a.mb_id = b.customer_id where (1) and a.wr_9 in ('미처리','처리중','') and left(a.wr_datetime, 10) >='2021-10-24' and left(a.wr_datetime, 10) <='2021-12-24' and a.delDate = '' and b.alevel >= '3' and mb_id<>'admin' order by a.wr_datetime desc ) as x1

union all

select * from (SELECT count(*) as cnt FROM `addNewEmployee_tbl` as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' and a.customer_id <>'admin' order by a.regdate desc ) as x2

union all

select * from(select count(*) as cnt from employeeLeave as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' and a.customer_id <>'admin' order by a.regdate desc) as x3

union all

select * from (select count(*) as cnt FROM `g5_write_workRequest` as a left outer join customer_list as b on a.mb_id = b.customer_id where (1) and a.wr_9 in ('미처리','처리중','') and left(a.wr_datetime, 10) >='2021-10-24' and left(a.wr_datetime, 10) <='2021-12-24' and a.delDate = '' and b.alevel >= '3' and mb_id<>'admin' order by a.wr_datetime desc ) as x4

union all

select * from (select count(*) as cnt FROM `confirm_message_info` as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' order by a.regdate desc ) as x6

union all

select * from (select count(*) as cnt FROM `confirm_00booking_info` as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' order by a.regdate desc ) as x10

union all

select * from (select count(*) as cnt FROM `confirm_bank_info` as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' order by a.regdate desc ) as x11

union all

select * from (select count(*) as cnt FROM `inOut_book_end` as a left outer join customer_list as b on a.customer_id = b.customer_id where (1) and a.status in ('미처리','처리중','') and left(a.regdate, 10) >='2021-10-24' and left(a.regdate, 10) <='2021-12-24' and a.delDate = '' and a.delDate = '' and b.alevel >= '3' order by a.regdate desc ) as x7

) as x;

by 마농 [2021.12.27 09:29:24]

8 개의 쿼리가 UNION ALL 되고 있는데.
1. 각 쿼리의 LEFT JOIN 은 잘못 입니다. Inner Join 으로 변경.
2. 각 쿼리의 ORDER BY 는 불필요 합니다. 불필요한 정렬 제거.
2. 각 쿼리의 SELECT * FROM () 는 불필요 합니다. 불필요한 서브쿼리 제거.
4. 날짜 조건을 조절할 필요가 있습니다.
- 기존 조건이 컬럼을 가공하여 조건을 주고 있는데.
- 컬럼을 가공하지 않고 그대로 조건을 주는 방식으로 변경이 되어야 할 것입니다.
- 변경전 : AND LEFT(a.regdate, 10) >= '2021-10-24' AND LEFT(a.regdate, 10) <='2021-12-24'
- 변경후 : AND a.regdate >= '2021-10-24' AND a.regdate < '2021-12-25'
5. 8개 쿼리를 각각 따로 수행해서 느린 쿼리가 있는지 확인하세요.
6. 조건이 통일성이 없네요.
- admin 이 아닌 id 만 가져오는 조건이 있는 쿼리도 있고 없는 쿼리도 있네요.
- 조건이 동일해야 할 것 같습니다.

SELECT SUM(cnt) cnt
  FROM (
          SELECT COUNT(*) cnt FROM g5_write_apply         a INNER JOIN customer_list b ON a.mb_id       = b.customer_id WHERE a.wr_9   IN ('미처리', '처리중', '') AND a.wr_datetime >= '2021-10-24' AND a.wr_datetime < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3' AND a.mb_id         <> 'admin'
UNION ALL SELECT COUNT(*) cnt FROM addNewEmployee_tbl     a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3' AND a.a.customer_id <> 'admin'
UNION ALL SELECT COUNT(*) cnt FROM employeeLeave          a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3' AND a.a.customer_id <> 'admin'
UNION ALL SELECT COUNT(*) cnt FROM g5_write_workRequest   a INNER JOIN customer_list b ON a.mb_id       = b.customer_id WHERE a.wr_9   IN ('미처리', '처리중', '') AND a.wr_datetime >= '2021-10-24' AND a.wr_datetime < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3' AND a.mb_id         <> 'admin'
UNION ALL SELECT COUNT(*) cnt FROM confirm_message_info   a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3'                               
UNION ALL SELECT COUNT(*) cnt FROM confirm_00booking_info a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3'                               
UNION ALL SELECT COUNT(*) cnt FROM confirm_bank_info      a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3'                               
UNION ALL SELECT COUNT(*) cnt FROM inOut_book_end         a INNER JOIN customer_list b ON a.customer_id = b.customer_id WHERE a.status IN ('미처리', '처리중', '') AND a.regdate     >= '2021-10-24' AND a.regdate     < '2021-12-25' AND a.delDate = '' AND b.alevel >= '3'                               
) x
;

by 미무미 [2021.12.27 15:06:18]

안녕하세요 마농님

다 적용해봐도 14초에서 13.5초정도로 0.5초정도 밖에 줄지 않더라구요

그래서 말씀해주신대로 각각 따로 수행해봤는데

맨첫번째 쿼리 혼자만 10초가 걸리더라구요

(select sql_no_cache count(wr_id) as cnt FROM `g5_write_apply` as a inner join customer_list as b on a.mb_id = b.customer_id where (1) and a.wr_9 in ('미처리','처리중','') and a.wr_datetime >='2021-10-24' and a.wr_datetime <='2021-12-24' and a.delDate = '' and b.alevel >= '3' and mb_id <> 'admin';)

그래서 뭐가 문제일까 계속 고민해보다가

customer_list테이블과

g5_write_apply테이블의 구조를 캡쳐해서 살펴보니까

a.mb_id = b.customer_id로 매칭하는 각각의 필드가 pk도 아니고 int형도 아니어서

느린게 아닐까라는 생각이 들더라구요 이게 속도 저하의 원인이 될 수 있을까요?

맞다면 어떻게 해결할 수 있을까요?

테이블구조

by 마농 [2021.12.27 15:53:29]

1. 다른 테이블은 왜 빠를까요?
- 건수가 적어서?
- 적절한 인덱스가 있어서?
2. 필요 인덱스
- a 테이블에는 (처리상태 + 등록일자) 인덱스가 있으면 좋습니다. (검색용)
- b 테이블에는 (customer_id + alevel) 인덱스가 있으면 좋습니다. (조인용)
3. 댓글 쿼리의 날짜 조건은 오류입니다.
- 오류 : AND a.wr_datetime >= '2021-10-24' AND a.wr_datetime <= '2021-12-24'
- 수정 : AND a.wr_datetime >= '2021-10-24' AND a.wr_datetime < '2021-12-25'

by 미무미 [2021.12.27 16:29:40]

해결되었습니다!!

2번은 어떻게 해야할 지 잘 모르겠어서 검색해보다가 straight_join을 알게되었는데요

결론은 아래 코드로 해결인데 좋은 코드인지는 모르겠네요 ㅜㅜ

아래 코드가 이제 0.016초 걸립니다

select sql_no_cache
   straight_join
   count(wr_id) as cnt
FROM
   `g5_write_apply` as a
inner join customer_list as b
on a.mb_id = b.customer_id
where
   a.wr_9 in ('미처리','처리중','') and
a.wr_datetime >='2021-10-24' and
a.wr_datetime < '2021-12-25' and
a.delDate = '' and
a.mb_id <> 'admin' and
b.alevel >= '3';

by 마농 [2021.12.27 18:32:25]

straight_join 은 form 절의 테이블 순서대로 조인을 하라는 의미입니다.
그렇다면 기존에 b 를 먼저 읽고 a 를 읽어서 느렸다는 거네요.
인덱스 정보 확인이나 실행계획 보는 법 등을 익히셔야 할 것 같습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변