database.sarang.net
UserID
Passwd
Database
DBMS
ㆍMySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MySQL Q&A 31223 게시물 읽기
No. 31223
쿼리좀 빠르게 만들어 주세요 ㅠ.ㅠ
작성자
이기자(k3i2)
작성일
2019-03-20 08:13ⓒ
2019-03-20 08:17ⓜ
조회수
2,066

제 능력의 한계를 느낍니다.

제가 아무리 머리를 짜내어 쿼리를 만들어봐도 조회시간이 4분이 넘네요. ㅠ.ㅠ

조회 테이블과 설명은 아래와 같습니다.

이해가 가실지 모르겠습니다.

 

1.테이블명: member(회원테이블-10만건정도) 조회조건: id별로 phone값이 존재하고 '-'를 제외한

         번호길이가 10이나 11인 회원만 조회. (조회 컬럼: no(자동증가index값), id, name, phone, sms_yn)

 

2.테이블명: RECEIPT(결제테이블-12만건) 조회조건: 1.에서 조회된 회원중 id값이 같은 사람중
            chkeck값이 '승인'이고 totalc값이 0보다 큰 데이타의 주문번호(onumber)를 구한다

 

3.테이블명: hmember(헬스이용테이블-3백만건) 조회조건: 2.에서 조회된 값(onumber)과 같은

           주문번호(onumber) 데이타의 총갯수(tcnt)와 현재 이용갯수(hcnt)

           ( date_format(NOW(),'%Y%m%d') BETWEEN stime AND etime )를 구한다

 

4.테이블명: smember(수영이용테이블-2백만건) 조회조건: 2.에서 조회된 값(onumber)과 같은

           주문번호(onumber) 데이타의 총갯수(tcnt)와 현재 이용갯수(hcnt)

           ( date_format(NOW(),'%Y%m%d') BETWEEN stime AND etime )를 구한다


* 결과물: 조회된 id별로 3.4의 tcnt와 hcnt의 합을 표시한다.
------------------------------------------------------------------------------
no    id      name         phone         sms_yn    tcnt     hcnt
------------------------------------------------------------------------------

쿼리 잘만드시는분들은 데이타 수량에 상관없이 놀라운 조회속도로 조회되게 만드시던데 도움좀 요청 드립니다.

 

 

이 글에 대한 댓글이 총 5건 있습니다.

각 테이블의 조건에 대한 만족 건수를 보여주세요.
이걸 알아야 전략을 세울 수 잇을 듯 합니다.
조인 후에 집계할 것인지? / 집계 후에 조인할 것인지?
1,2번 조건에 의한 건수가 적다면? - 조인후에 집계하는게 좋을 듯 하고
1,2번 조건에 의한 건수가 많다면? - 집계후에 조인하는게 좋을 듯 하네요

마농(manon94)님이 2019-03-20 09:27에 작성한 댓글입니다.
이 댓글은 2019-03-20 09:29에 마지막으로 수정되었습니다.

 필수 조회조건

페이징 여부

정렬 여부

등 업무 요건이 주어져야

거기에 맞는 쿼리 및 튜닝이 가능합니다.

박인호(paerae)님이 2019-03-20 10:52에 작성한 댓글입니다.

마농(manon94)님

답변감사합니다 ^^

1의 결과값은 65,000건정도이고 회원가입시 더 늘어나고,

2의 결과값은 63,000건 정도입니다. 이또한 구매가 이루어 질수록 건수는 늘어나는 거고요. ^^

이기자(k3i2)님이 2019-03-20 22:45에 작성한 댓글입니다.

박인호(paerae)님

답변 감사합니다 ^^

1번의 필수조회조건은, 초기에 뭔가 잘못되어 폰번호가 없거나 중복되거나 이상한 번호가 많아서 그걸 걸런내기위헤 일단 이렇게 구현했습니다.


SELECT a.NO, a.id, a.NAME, a.phone, a.sms_susin, length(REPLACE(a.phone,'-',''))AS phone_len
 FROM wow_member a
 GROUP BY a.phone
 HAVING COUNT(a.NO) = 1
  AND phone_len >= 10
  And phone_len <= 11
 ORDER BY a.id

이 결과값에 조인을 걸어서 2,3,4번의 조회조건은 질문에 설명이 돼있고,
페이징은 안하고 한번에 전체 조회입니다.
정렬은 응용프로그램이라 일단 이름으로 하면 됩니다.

감사합니다 ^^

이기자(k3i2)님이 2019-03-20 22:54에 작성한 댓글입니다.

검색조건이 없고 페이징도 없다면

항상 Full Scan을 한다고 보고 쿼리를 짜야 하겠네요.

그러면 마농님 답변처럼 집계후 조인을 하시는 것이 좋습니다.

검증되지 않은 쿼리라 오류가 있을 수 있다는 점 감안하고

참고만 하세요.

- 필요없는 order by 절은 넣지 않은 것이 좋습니다. (응용프로그램에서 한다고 하셔서)

 

SELECT m.no, m.id, m.name, m.phone, m.sms_susin, s.tcnt, s.hcnt
FROM (
	SELECT a.NO, a.id, a.NAME, a.phone, a.sms_susin, length(REPLACE(a.phone,'-',''))AS phone_len
	FROM wow_member a
	GROUP BY a.phone
	HAVING COUNT(a.NO) = 1
	AND phone_len BETWEEN 10 AND 11
) m
LEFT JOIN (
	SELECT id, SUM(tcnt) tcnt, SUM(hcnt) hcnt
	FROM (
		SELECT id, onumber
		FROM receipt
		WHERE chkeck='승인'
		AND totalc > 0
	) r
	JOIN (
		SELECT onumber, COUNT(1) tcnt , SUM(CASE WHEN DATE_FORMAT(NOW(),'%Y%m%d') BETWEEN stime AND etime THEN 1 ELSE 0) hcnt
		FROM hmember
		GROUP BY onumber
		UNION ALL 
		SELECT onumber, COUNT(1) tcnt , SUM(CASE WHEN DATE_FORMAT(NOW(),'%Y%m%d') BETWEEN stime AND etime THEN 1 ELSE 0) hcnt
		FROM smember
		GROUP BY onumber
	) u ON u.onumber=r.onumber
	GROUP BY id
) s ON s.id=m.id 
박인호(paerae)님이 2019-03-21 10:13에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
31226쿼리가 index를 타지 않습니다.
최봉수
2019-04-02
1794
31225MySQL 클론 관련 문의 드립니다. [3]
지나가다
2019-03-22
2021
31224적립금(마일리지) 적립, 사용, 만료소멸 프로세스 도움 부탁드립니다. [1]
조현철
2019-03-21
1907
31223쿼리좀 빠르게 만들어 주세요 ㅠ.ㅠ [5]
이기자
2019-03-20
2066
31221Master-Slave를 사용한 복제시 특정Row만 복제 또는 특정 이벤트 발생 시 복제 [3]
정성철
2019-02-26
1958
31220서비스에 사용중인 DB database를 동기화 하는 방법 [5]
정성철
2019-02-26
1927
31219join 관련 질문입니다. [2]
조동건
2019-02-25
1736
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2022 DSN, All rights reserved.
작업시간: 0.058초, 이곳 서비스는
	PostgreSQL v14.2로 자료를 관리합니다