개발하시는분들께 참고하시라고 팁을 간단하게나마 올립니다.
그냥 한번 쭉 읽어만 보셔도 조금은 도움 되실거에요~
쿼리짤 때 이것들만 지켜도 프로그램 속도는 향상됩니다.
1. 필요도 없는 아우터 조인을 남발하지 말기
2. 한번에 가져올 수 잇는 쿼리는 UNION ALL로 묶어서 가져온다음 로직단에서 처리
- 메모리와 Disk간 I/O가 속도에 영향을 크게 줌
3. Join SQL의 경우 드라이빙 순서를 맞춤
- NL-Join시 2개 테이블중 Size가 작은 것이 드라이빙는 것이 아니라.. 인덱스가 없는 테이블을 먼저 드라이빙
- 복잡한 조건문이 포함될 경우.. 처리량이 작은 순서
4. 인덱스 제대로 탈 수 있도록 조건 주기
- WHERE user_id > '' 같이 값이 안들어와도 인덱스를 타는 경우 속도가 더 잘 나올 수 있는 경우
위와 같이 트릭조건을 걸어 옵티마이저가 인덱스를 선택 할 수 있도록 도와 줄 수 있음
- 인덱스가 걸린 컬럼을 가공하지 않기
where Substr(User_id, 1, 3) = '123' ----> where User_id like '123%' 으로 처리
5. 데이터 타입은 필요한 만큼만 잡아주기
- 컬럼 길이의 편차가 심한 경우, NULL 로 입력되는 경우가 많은 경우 VARCHAR2 사용
- 무조건 넉넉하게 잡기보다는 들어갈 내용에 알맞게 잡아주는게 좋습니다.
varchar2(4000) 으로 잡아 놓았다가 텍스트 다운로드할 필요가 있을 때나 화면에 디스플레이 시켜야 할 필요가 있을 때
도대체 자리를 몇자리나 잡아야 하는지 고민될 수 있음
- null은 말 그대로 값이 없음을 의미하기 때문에 물리적으로도 공간을 따로 할당하진 않습니다.
하지만 null값에 접근하기 위해서는 4바이트 정도의 주소공간은 필요합니다.
6. char(5) 보다는 varchar2(5)를 쓰기
- Space가 추가되는 char 형이 Scan할 데이터가 더 많아져서 성능이 떨어짐
- 한바이트의 칼럼이라도 오라클에서는 char 보다는 varchar2 가 권장됨
- 지금은 varchar라고 선언하면 내부에서 varchar2로 변환하는 것으로 알고 있습니다.
tip)
1. indentity를 가진다면 Integer로 만들어야 하고, 순수 값의 의미라면 int를 쓰는 것이 맞다.
ex.)
PK, FK의 시퀀스값(ID) : Integer
나이, 점수 (int는 원시 자료형) : int
2. DB에서 null이 반드시 필요한 경우 Integer를 사용한다. int로는 null이 불가능하다.
7. ---> 혼동의 위험이 있어서 삭제합니다.
8. 부정형 조건 쓰지 말기
- is not null, not exists 등
- 애초에 null이 들어가는 경우가 문제가 있다면 null 안들어가게 default 값 잡아주기
9. select 시 * 쓰지말고 필요 컬럼만 적어 주기
- 블럭단위로 퍼올리는데 쓸데없이 I/O 엑세스만 더 늘리지 말기
- 내부적으로는 프로그램에서 사용안하는 컬럼들까지 I/O를 통해 통신됨
10. 테이블 설계할때 업무 조회시 자주 발생하는 경우와 조건 컬럼 분석해서 인덱스 잡기
- 시스템 구축 초기에 화면정의서를 보고 사용 패턴을 분석하여 인덱스를 설계
11. distinct 보다 group by 쓰기
- distinct 가 RDB체계에서는 temp space를 사용하며 그안에 임시로 저장하고 정렬하는 방식이라
과부하가 많이 걸리기 때문에 group by를 사용
- distinct 는 일단 원하는 select문들이 나타는데 중복이면 없애는 방식
group by 는 조인을 했을때 양 그룹중 같은 필드를 한대 묶은 방식
12. if else로 쿼리를 여러개 만들어 하나 실행하는 것 보다 SQL에 상수를 이용해 처리하는게 더 퍼포먼스가 좋음
SELECT ...
FROM ...
WHERE '1' = '상수'
Union All
SELECT ...
FROM ...
WHERE '2' = '상수'
========================================================
수정 :
7번에 대한 반론이 있네요..... DB 관점에서만 생각했을때는 date 형식만 사용하는것이 맞다고 저도 생각합니다.
혼동을 주게 될 소지가 있어서 7번 항목은 삭제하도록 하겠습니다.
요새 어떻게 하면 효과적이고 효율적으로 한줄의 쿼리문을 작성할까 고민 많이 하고 있는데 도움이 되네요^^!
6, 11 번은 반대로 알고있었네요 ㅜㅜ
출판된 지 20여년이 되어가는 책이지만 RDB계의 바이블이라고 해서 열심히 보고 있습니다.
일이 없어서 개발도 안 하는 4개월차 왕초짜 개발자라서 모르는 용어도 많이 튀어 나오지만 꾸역꾸역 보고 있습니다. ㅠ_ㅠ
모든 경우에 date 형이 좋습니다. 다만 사용이 불편하죠
http://ukja.tistory.com/131
3번도 이상합니다.
Join order 은 옵티마이져가 결정하는데 굳이 신경써서 SQL 문을 작성할 필요가 없습니다.
11번은 근거가 없는 이야기 같습니다.
distinct 와 group by 처리가 다르다???
join order 는 옵티마이져가 결정하긴 하지만 성능상 선행 드라이빙 테이블을 다르게 가져가면 nl join 의 데이터 io 를 줄여서 훨씬 좋은 결과를 가져올때가 있습니다. 이럴때 ordered 힌트로 순서를 조정하면 좋다는 얘기입니다.
그리고. 11번은 음.... distinct 와 group by 의 플랜 비교 한번 해보시고 말씀하시면 좋겠네요...처리가 같으면 플랜이 다를 이유가 없지요.
이 글을 참고하여 실제 업무에 활용하시는 분이 계실수도 있기 때문에 확인되지 않은 태끌은 자제해주시거나 쪽지로 부탁드리겠습니다.
작성자분도 성능은 당연히 DATE가 좋지만, 사용이 불편해서 VARCHAR2를 제약 조건과 함께
사용하는 방법도 고려해볼만하다 정도로 저는 이해했습니다.
3번은 옵티마이져가 만능은 아니기 때문에 기본적으로 신경 써서 작성할 필요가 있습니다.
과한 sql 튜닝의 스트레스를 지적하고 싶으신 것으로 이해합니다만,
대용량일수록, 테이블 수가 많아질수록 join order에 따른 성능 차가 기하급수적으로 커집니다.
옵티마이저가 결정을 잘못할 수 있는 부분에 대해서는 옵티마이저 힌트를 줘서 순서와 인덱스 사용
등에 대해 분명히 튜닝해 줄 필요가 있습니다.
실행계획을 확인해보는 습관이 필요하다고 생각합니다.)
더불어 7번은.. 시간 정보를 VARCHAR2(8)로 미리 정의해버리면
추후 운영에 있어서 불편할 경우가 더 많을거라 생각합니다.
꼭 쿼리 안에서 날짜의 형태를 바꿔야만 하는 것도 아니고...
개발 편의성 면에 있어서 Date 를 사용하는 것이 더 유용합니다.
11번은 원문 글이 맞습니다.
Join order 은 옵티마이져가 결정하는데 굳이 신경써서 SQL 문을 작성할 필요가 없습니다.
-> 옵티마이저도 길을 잃고 헤맬때가 생깁니다.
-> 굳이 신경써서 작성할 필요가 생겨서 그렇게 쓰고 있는 사람들도 있습니다.
11번은 근거가 없는 이야기 같습니다.
distinct 와 group by 처리가 다르다???
-> 10년 좀 넘게 쓰다보니, 대부분 distinct 보다 group by 를 많이들 쓰고, 쓰게 됩니다.
마냥 근거 없는 이야기라기보다는 확인이 필요할 듯 싶습니다.
체감상 group by 가 쪼끔 빠르다는 느낌은 자주 받습니다.
3. 오라클 정도라면...Join order는 꽤 알아서 해주고, 이걸 고치느니 인덱스를 걸거나 조인 알고리즘을 다른걸 사용할 수 있도록 옵티마이즈 하는게 좋겠죠.
4. 트릭으로 조건을 줘서 인덱스를 거느니 HINT를 줘서 직접 인덱스를 선택해주는 것이 바른 방법일 것 같고 (정말 옵티마이저보다 잘 할 수 있는 경우에만..), %를 사용하면 인덱스 안탄다고 생각하시면 되고, 차라리 위 경우라면 함수까지 넣어 인덱스를 사용하면 (오라클은 함수를 적용하여 인덱스를 생성할 수 있습니다.) 전자가 훨씬 좋은 성능을 낼 수 있습니다. 어자피 부분 문자열 매칭이 문제라면 별도의 search engine을 달거나 full text index를 고려하는 편이 좋지만요.
6. 아주 예외 적인 경우지만 전체 데이터가 동일한 크기(성별, 우편번호 등)라면 char가 varchar보다 공간 효율이 좋을 수 있구요. 그리고, 오라클의 경우 모든 숫자는 NUMBER 타입이지 않나요? integer나 int도 내부적으로 NULL인 것으로 알고 있는데.. 그리고, NULL을 못 받는 타입이 있나요? NOT NULL을 설정하지 않는한 NULL 되는 것으로 알고 있는데... (이 부분은 깊게 생각해본 적은 없어 혹시 제가 잘 못 알고 있는 것일 수도 있겠지만요.)
7. 햇살아이님 의견에 동의합니다. Date형을 사용하지 않을 이유가 없습니다. TIMEZONE 문제가 아주 복잡해서 EPOCH 정도를 쓴다면 모를까 문자열로 저장하는 것은 아니라고 봅니다.
11. DISTINCT나 GROUP BY나 JOIN 모두 오라클 정도 되면 님이 생각하시는 수준으로 단순하게 처리하지 않습니다. 데이터가 많으면 Hash join이나 External Sort를 활용하여 아름답게(?) 처리됩니다.
12. 도대체 어떤 상황인지는 모르겠지만, IF then과 유사한 처리가 필요했다면 오히려 CASE나 DECODE 같은 함수를 통해서 좀 더 나은 처리를 할 수 있을지도 모릅니다. 여러번 SQL문을 수행하는 것은 나쁘기는 하지만, UNION ALL도 가급적 피하는 것이 좋을 것 같네요.
4. 힌트로 직접 태우게 되어도 조건컬럼에 수식이 들어가는것은 결코 좋은 결과를 주지 못합니다. 그리고 % 라고 무조건 index 를 안타는게 아닙니다. 잘못알고 계시네요.... 제일 앞자리에 % 가 안오면 됩니다.
6. 패스할게요...
7. 위에 햇살아이님 댓글 참조부탁드립니다.
11. 제 생각이 아니라 이론적으로도 그렇고, 실제 업무에서 활용해보고 내놓은 결론입니다. 실제로 실행해보시면 알수 있습니다.
12. 통으로 드라이빙해서 계산하는것보다 부분 범위 처리로 끊어서 각 쿼리마다 다른 index를 태우고 쿼리후 union all 했을때 좋은 결과가 있는 경우가 있다는 설명입니다. 상세설명이 부족했군요....
4. 번은 힌트를 줘도 제대로 사용하지 못합니다. 작성자님 말씀이 맞습니다.
6. 데이터가 동일한 사이즈이고 not null이라면 char가 좋습니다(유연성은 떨어지지만). varchar2의 경우 유연성은 좋지만 자칫하면 row chain이 발생할 수 있기 때문에 잘 판단할 문제입니다.
7. 해묵은 논쟁입니다만, 그렇게 많이 차이나진 않습니다.
11. distinct의 경우 최근 버전은 자체적으로 가능하면 group by로 변환합니다. temp 영역은 sort area가 모자라면 사용하는 것이기때문에 distinct나 group by냐를 구분해서 사용되는 것은 아닙니다.
12. 쿼리를 여러개 만들어 사용하는 것을 피하라고 하는 것은 아마 과거에 SGA 사이즈와 soft(hard) parse로 인한 부하를 줄이고자 하는 것 같은데, 요즘 시대엔 좀 안 맞는거 같습니다.
전체적으로 oracle의 (최신버전에도 해당하는 내용이 있긴 하지만) 과거버전에 해당하는 내용 같습니다.;;
(수정)
12번의 경우 그런 의도라면 가능한 방법이라고 생각합니다. 다만 if else라면 서로 배타적인데...;; 예와 부연 설명이 조금 다른것 같습니다.
작성자분께서 부연 설명이 조금 부족해서 배경지식이 없는 경우 받아들이기 힘든 면은 있지만,
반론마저도 근거 없이 들이대는 것은 본인의 무식과 무례를 한 번에 보여주는 것과 다르지 않아
보입니다.
근거 없이 들이댄 댓글이 어떤건가요?
근거 없이 들이댄 댓글이 어떤건가요?
아래 글을 포함한 글들....
11번은 근거가 없는 이야기 같습니다.
distinct 와 group by 처리가 다르다???
저런 내용들은 보통 인터넷에 떠도는 내용들인데 잘못된 것이 있을 가능성이 많아서 그대로 적용하면 안됩니다. 반드시 테스트를 해보고 검증해야 됩니다. DB 종류에 따라 다른것도 있고....
상당히 이상한 조언이 많네요. 따라하지 않는 것이 좋을 것 같습니다. 아마도 오라클을 대상으로 한 것 같은데,
등등이
원글 작성자의 경험에 비추어 쓴 글에 비해
테스트없이 본인의 순간적인 느낌이나, 부분적인 경험으로 댓글을 작성한듯 보입니다.
원글 작성자의 글이 100% 맞지 않더라도, 최소한 자신의 경험한 부분만이 모든 진실을 아우를수는 없습니다.
위에 게시한 근거없이 들이대었다는 댓글들도 틀렸다는 말은 아닙니다.
원글이나 댓글들 모두 어떤 상황에서는 맞고, 다른 상황에서는 다를 수 있기에
어떤 주장의 첫 문장을 저 따위로 시작하는건 아니라고 봅니다.
oracle 9i 버젼까지는 distinct 가 sort temp 를 이용했네요
10g 부터 hash temp 를 이용하고요
hash temp 를 이용하면 느리지 않습니다.
http://rego.tistory.com/113
다만 몇가지 너무 단정짓는 항목들은 조금 위험해보입니다.
1. 필요도 없는 아우터 조인을 남발하지 말기
- 남발하는건 뭐든지 않좋겠지요 ^^... 근데 필요한데 성능을 위해 Join 을 안하고 별도의 SQL로 수행한다면 join 하는것만 못합니다. mysql이라면 몰라도 Oracle DBMS 에서 수십개의 Table join에 대해서도 적절한 연결고리가 있다면 기능적으로 문제가 될 것은 없습니다.
4. 인덱스 제대로 탈 수 있도록 조건 주기
- 인덱스를 타게 하기 위해서 불필요한 조건을 주는 건 특별한 경우를 제외하고는 바람직하지 않습니다. 적절한 컬럼이 있다면 옵티마이저가 판단해서 해당 인덱스를 타야하는게 맞는 것이고요. 조건 컬럼에 없는 인덱스를 불필요한 조건을 추가하여 강제로 태운다면 더 안 좋을겁니다. 적절한 조건이 있음에도 인덱스를 잘못 탈 때를 확인해서 힌트 등의 방법으로 조절해주는게 맞겠습니다.
조건절로 'TEXT%'를 쓴다고 인덱스가 효율이 없는건 아닙니다. '%TEXT' 는 생각해봐야겠지만요.
인덱스는 태운다고 무조건 좋은게 아니고 해당 업무에 적합한 인덱스를 타도록 하는게 중요합니다.
12. if else로 쿼리를 여러개 만들어 하나 실행하는 것 보다 SQL에 상수를 이용해 처리하는게 더 퍼포먼스가 좋음
- 하나의 SQL로 묶으려고 무리해서 Union all 로 처리하는 것도 무조건 좋다고는 할 수 없습니다. 사이즈가 큰 SQL 구문이 shared pool 에 올라가 있는게 더 안 좋은 경우도 많이 있습니다.
DBMS 라는 것도 케바케가 적용되는 것 같더군요. 옵티마이저가 아무리 진화해도 모든 것을 맞게 판단하지는 않으므로, 업무를 잘 이해하고 해당 업무SQL에 맞는 설계와 구문 작성이 가장 중요해 보입니다. ~.
다시 한번 좋은 글 감사합니다.
데이터만 잘나오면 되는거지뭐~라는 생각으로 쿼리문 작성 했는데
이번계기로 자아성찰 해봅니다.ㅎㅎ
- NL-Join시 2개 테이블중 Size가 작은 것이 드라이빙는 것이 아니라.. 인덱스가 없는 테이블을 먼저 드라이빙
인덱스가 없는 테이블의 경우 Full scan을 하는데 이 경우 NL조인을 하게되면 엄청난 비효율이 발생합니다. 기본적으로 NL조인은 최소한의 로우를 갖고 루핑을 돌리는거라 인덱스가 없는 테이블을 조인하는것 자체가 에러.
인덱스 설계를 해주는게 가장 베스트 하겠지만 불가피한경우 HASH 조인이 퍼포먼스 면에서 유리합니다.
참고로, 남들이 만들어놓은 수십, 수백개의 스키마를 쿼리 하나를 위해 리팩토링 할 수는 없습니다.
그러다 보면 쿼리는 길어지고, 원하는 결과를 위해 테이블 스캔을 여러번 해야하는 경우가 생기는 경우라고 보여집니다.
인덱스 설계를 해주는게 가장 베스트 하겠지만 불가피한경우 HASH 조인이 퍼포먼스 면에서 유리하다는건 잘 압니다.
현장에서는 어떤 이유로 꼬여있는 또는 그렇게 할 수가 없는 경우가 더 많기 때문에 나오는 이야기들 입니다.
#CLiOS
기본적으로 위와 같이 사용하셔도 되고요 DB의 특성이나 기타 버전에 따른
나미지 차이점은 는 전문 튜너에게나 DBA에게 맞기시면 됩니다
그분들이 돈받고 하는 일이 이런건데 포퍼먼스에 관한 건 맡겨야죠