거의 2년만에 새로운 글을 올리게 되어 기분이 남다르다. ^^;
나는 지난달 MySQL에 대한 튜닝 요청이 있어서 약 3주간 지원을 다녀온 적이 있다.
오라클이 아니라서 살짝 MySQL 책을 사서 맛만 좀 보고 지원을
하게되었다.
사실 직접 가서 확인해보니, MySQL로 2TB에 달하는 대용량 데이터를 운영하고 있는 것이 놀라울 정도였다. 전체
데이터는 약 2TB, 가장 큰 테이블 하나가 약 200GB 였다. MySQL의 다른 면을 보는 듯 했다.
튜닝이 필요한 SQL을 전달 받고서 점검을 수행하던 중에 매우 특이한 SQL을 발견했다.
SQL문장 하나가 4만라인... 헉~~
도저히 이해가 되지 않아 가만히 들어다 보니, 응용프로그램에서 SQL을 동적으로 생성하여 실행한 SQL처럼 보였다.
대략 아래와 같은 패턴이었다.
SELECT ...
FROM (
SELECT 1 AS CNT FROM DUAL
UNION ALL
SELECT 1 AS CNT FROM DUAL
UNION ALL
SELECT 1 AS CNT FROM DUAL
UNION ALL
SELECT 1 AS CNT FROM DUAL
.....
UNION ALL
SELECT 1 AS CNT FROM DUAL
)
위와 같은 SQL이 생성 되어진 이유는 다음과 같았다.
1. 다음과 같이 테이블A 가 존재하는데
2. 테이블에 데이터가 입력되는 형태는 아래와 같이 생성시간과 생성유형 컬럼에 버티컬바(‘|’)를 구분 값으로 하여 쌍으로 입력되며, 생성개수 컬럼에는 쌍의 개수가 입력되어 진다.
일련번호
|
생성시간
|
생성유형
|
생성개수
|
1
|
2015010100|2015010101|2015010102|…
|
080101|080101|090102|…
|
40
|
2
|
2015010200|2015010201|2015010202|…
|
090101|080101|090102|…
|
45
|
3
|
2015010300|2015010301|2015010302|…
|
080101|090101|090102|…
|
80
|
4
|
2015010400|2015010401|2015010402|…
|
080101|080101|080102|…
|
30
|
5
|
2015010500|2015010501|2015010502|…
|
070101|080101|090102|…
|
109
|
3. 이렇게 입력된 데이터를 생성시간의 일자, 생성유형별로 그룹핑하여 개수를 구해야 하는데 이를 구하기 위해서 프로그램을 작성하여 한 건씩 패치하여 SQL문장을 생성 및 실행하게 된다.
String SQL = “”;
while(rs.next())
{
String[] createTime = rs.getString(1).split("|");
String[] createType = rs.getString(2).split("|");
int createCnt = rs.getInt(3);
for(int i = 0; i < createCnt; i++)
{
SQL += “select ‘“ + createDate[I] + “_” + createType[I] + “’ as d, ‘1’ as cnt from dual union all”;
}
}
rs.close();
/* to-do */
SQL 문장 실행…
위와 같이 SQL 문장을 만들게 되니 4만 라인의 SQL이 생성된 것이다.
이러한 로직에는 어떤 문제점이 있을까?
우선 첫 번째로 불필요한 fetch를 수행하게 되고,
두 번째 불필요한 network 부하를 발생하게 된다. 그러므로 해당 업무의 성능이 저하될 수 밖에 없다.
그럼 이런 문제점들을 어떻게 해결할 수 있을까? 답은 간단하다. 그냥 SQL로 처리하는 것이다.
위의 데이터에서 우리는 각 레코드를 응용프로그램에서 fetch하지
않고 생성일자, 생성유형별 개수를 한방의 SQL로 추출할
수 있는 중요한 두 가지를 발견할 수 있다.
첫 번째 ‘|’로 구분되는 생성시간과 생성유형의 각 값의 길이가 고정되어
있다는 것과
두 번째 생성개수 컬럼에 의해 몇 개가 생성되어 있는지 알 수 있다는 것이다.
이렇게 두 가지만 알면 ‘|’로 구분된 각 컬럼의
데이터를 레코드로 변환할 수 있다.실제로 테스트를 수행해 보자
1. 먼저 테스트를 위한 테이블을 만들자.
CREATE TABLE 테스트A(
일련번호 NUMBER(10)
PRIMARY KEY,
생성시간
VARCHAR2(4000) NOT NULL,생성유형 VARCHAR2(4000) NOT NULL,
생성개수 NUMBER(5) NOT NULL
);
2. 데이터를 편하게 넣기 위해 SEQUENCE를 하나 생성하자. CREATE SEQUENCE SEQ1 START WITH 1 INCREMENT BY 1 MINVALUE 1 NOCYCLE;
3. 테스트 데이터를 입력하자
INSERT
INTO 테스트A VALUES (SEQ1.NEXTVAL,
'2015010100|2015010101|2015010102|2015010115', '080101|080101|090102|090102',
4);INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010200|2015010201|2015010202|2015010202|2015010203', '090101|080101|090102|080101|080101', 5);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010300|2015010301|2015010302|2015010302', '080101|090101|090102|080101', 4);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010400|2015010401|2015010402', '080101|080101|080102', 3);
INSERT INTO 테스트A VALUES (SEQ1.NEXTVAL, '2015010500|2015010501|2015010502|2015010510|2015010510|2015010510', '070101|080101|090102|090102|070101|080101', 6);
COMMIT;
4. ‘|’로 구분된 값을 레코드로 변환하기 위해서는 데이터 복제방법을
사용해야 한다. 오라클 이라면 CONNECT BY 절을 사용하여
쉽게 데이터 복제를 위한 데이터를 생성할 수 있겠지만, 다른 DBMS에서도
활용할 수 있게 하려면 고전적인 COPY_T 테이블을 생성해야 한다.
CREATE TABLE COPY_T(
NO NUMBER(10) NOT NULL PRIMARY KEY
);
INSERT INTO COPY_T
SELECT LEVEL FROM DUAL CONNECT BY LEVEL
<= 100;MySQL 이라면 아래와 같이 COPY_T 테이블에 데이터를 입력할 수 있다.
CREATE TABLE COPY_T
(
NO INT PRIMARY KEY
);
INSERT INTO COPY_T
SELECT @RN:=@RN+1 AS ROWNUM
FROM (SELECT 1 FROM <AnyTable>) AS T, (SELECT @RN:=0) AS R
LIMIT 500000;
5. 이제 준비가 완료 되었다.
SQL을 작성하여 컬럼을 레코드가 아닌 데이터 값을 레코드로 변환해보자. 원리는 간단하다. 먼저 이야기 했듯이 우리는 각 레코드의 값에 몇 개씩 생성되었는지 이미 개수를 알고 있으므로 COPY_T 테이블을 이용하여 그 개수만큼 레코드를 복제하면 된다.
SQL을 작성하여 컬럼을 레코드가 아닌 데이터 값을 레코드로 변환해보자. 원리는 간단하다. 먼저 이야기 했듯이 우리는 각 레코드의 값에 몇 개씩 생성되었는지 이미 개수를 알고 있으므로 COPY_T 테이블을 이용하여 그 개수만큼 레코드를 복제하면 된다.
SELECT
A.생성시간, A.생성유형, T.NO
FROM
테스트A A, COPY_T TWHERE T.NO <= A.생성개수
ORDER BY 생성시간, 생성시간, NO;
6. 레코드가 생성개수만큼 복제 되었다. 다음으로 우리가 알고 있는 정보를 사용해야 한다. 생성시간은 10자리마다 ‘|’로 구분되어 있으며, 생성유형은 6자리마다 ‘|’로 구분되어 있다. 이정보를 사용하여 각 레코드별로 해당되는 위치의 값을 잘라서 취해보자.
SELECT
SUBSTR(A.생성시간, (CASE WHEN T.NO = 1 THEN 1 ELSE
(T.NO * 10 + T.NO - 11 + 1) END), 10) AS 생성시간
,SUBSTR(A.생성유형, (CASE WHEN T.NO = 1 THEN 1 ELSE
(T.NO * 6 + T.NO - 7 + 1) END), 6) AS 생성유형, T.NOFROM 테스트A A, COPY_T T
WHERE T.NO <= A.생성개수
ORDER BY 생성시간, 생성시간, NO;
이렇게 하면 구분자로 분리된 값을 레코드로 변환하여 다른 테이블과 조인도 수행할 수 있다.
다음을 기약하며 이번 포스팅은 여기까지다.