SQL Tuning summary No.1
간단 소개
- 이 md는 조시형 저자의 친절한 SQL 튜닝을 요약한 것이다.
- 친절하다고 하는데… 음.. 처음 보면 걍 엄청나게 헤맨다.
- 아예 모른다면 그냥 SQL level up부터 읽고 오자.
sql 파싱
- sql 파싱은 optimizer가 SQL을 보고 실행계획을 만드는 행위다.
- 처음에 SQL이 파싱되면 저수준까지 모두 만들어야 한다.
- 첫 파싱이 끝나면 SQL 구문을 key로 실행계획 등이 SGA의 library cache에 저장된다.
- 라이브러리 캐시에 있는 걸 가져오면 소프트 파싱이다.
- 저수준까지 만들어야 하면 하드 파싱이다.
- 아래 sql문은 모두 서로 다른 sql문으로 라이브러리 캐시에 저장된다.
- 그런데 캐시가 부족하면 바로 버려진다.
SELECT * FROM emp WHERE empno = 7900; SELECT * FROM emp WHERE EMPNO = 7900; SELECT * FROM emp where empno = 7900 ; SELECT * FROM EMP WHERE empno = 7900; SELECT * FROM emp WHERE empno = 7900 ; - 그러한 이유로 WAS단에서 sql을 만들 때는 아래와 같이 만들어선 안 된다.
- 실제로 대부분의 Java에서 사용되는 DB library들은 실제로 아래와 같은 쿼리문을 만들지 않는다.
String sql = "SELECT * FROM CUSTOMER WHERE LOGIN_ID= '" + login_id " '"; Statement st = con.createStatement(); - 로그인을 위와 같은 식으로 만들었다고 해보자.
- 그런데 고객이 500만명이다. 내일 10시에 이벤트가 있어 100만명이 몰린다.
- 라이브러리 캐시에서 이미 버려진 고객들이 많다.
- 따라서 대부분 고객의 이름마다 하드파싱이 일어난다.
- CPU 사용량이 치솟아 버리고, 하드파싱끼리 서로 누가 먼저 할지 경합하게 된다.
- 이런 비효율적인 상황을 초래하지 않으려면 아래와 바인드 변수를 써줘야 한다.
- 보통 JAVA에서는 PreparedStatement와 같이 엮여서 쓰인다.
- 대부분의 PreparedStatement는 bind variable로 구현한다.
String sql = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?"; PreparedStatement st = con.prepareStatement(); st.setString(1,login_id); . . .
- 실제 내부 프로시저는 아래와 같이 형성된다.
- 그럼 jaewon을 넣든, jinsoo를 넣든 아래에 이미 만들어진 프로시저를 재사용하게 된다.
- 첫 고객의 로그인에서만 하드파싱이 되고, 나머지 100만명의 고객은 모두 파싱된 공통 SQL을 재사용한다.
- 그에 따라서 CPU연산이 부족할 일이 없다. 모두가 행복해진다. 트래픽만 관리하면 되는 것이다.
create procedure LOGIN (login_id in varchar2)
디스크 I/O
- 디스크 I/O는 블록킹이라 그동안 프로세스가 일을 하지 않고 CPU를 반환한다.
- I/O가 일어나면 프로세스가 대기 큐에서 잠을 자버린다.
- I/O Call은 Single Block I/O 기준 평균 10ms, SAN 스토리지는 4-8ms, SSD 스토리지는 1ms다.
- Single Block I/O 기준 아무리 빨라도 1초에 1000블록이 최대다.
- I/O call을 통해 데이터 블록을 가져온다고 했다.
- 한 번에 한 블록씩 가져오는 것은 single block I/O라고 부른다. index를 읽을 때 쓴다.
- 소량을 읽을 때는 효율적이다.
- 한번에 여러개를 가져오는 것은 multi block I/O라고 부른다. table을 읽을 때 쓴다.
- 대량으로 읽을 떄는 효율적이다.
- 그 중에서도 multiple block 단위가 클수록 좋다.
- I/O call이 줄어 process가 대기상태에 빠지는 횟수가 줄어들기 때문이다.
- OS단에서 최대 단위가 보통 1MB이기 떄문에 DB에서도 1MB 이상 설정할 필요가 없다.
- 아래와 같이 multiblock 단위를 확인할 수 있다.
- 한 번에 한 블록씩 가져오는 것은 single block I/O라고 부른다. index를 읽을 때 쓴다.
show parameter db_file_multiblock_read_count
- 그럼 아래와 같이 multiblock 기준이 나온다.
NAME TYPE VALUE db_file_multiblock_read_count integer 16 - 프로세스가 잠드는 disk I/O call을 최대한 줄이려면 block 크기를 늘려야 한다.
- 8KB block 기준 128로 바꾸면 1MB로 최대 크기를 설정할 수 있다.
- 8Kb x 128이면 딱 1MB다.
- 물론 8KB가 아니라 16KB 기준으로는 64로 설정하는 게 최대일 것이다.
- 만약 OS가 2MB라면 8KB 기준 256으로 설정해도 된다.
alter session set db_file_multiblock_read_count = 128;
table scan
- table scan의 경우에는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다.
- 익스텐트 맵을 통해 각 익스텐트의 첫 번쨰 데이터 블록 DBA(데이터블록주소)를 알 수 있다.
- 익스텐트는 연속된 데이터 블록 집합이다. 따라서 첫 번째 데이터 블록 뒤에 연속해서 저장된 블록을 읽으면 된다.
- 각 인전합 익스텐트끼리는 보통 서로 다른 테이블일 확률이 높다. DB가 파일 경합을 줄이기 위해 데이터를 여러 파일에 분산 저장하기 때문이다.
SEGMENT |TABLESPACE_NAME |EXTENT_ID |FILE_ID |BLOCK_ID |BLOCKS TABLE USERS |0 |1 |1 |4 TABLE USERS |1 |1 |9 |4 TABLE USERS |2 |2 |1 |4 TABLE USERS |3 |2 |5 |4 TABLE USERS |4 |2 |13 |4 TABLE USERS |5 |3 |1 |4 TABLE USERS |6 |4 |9 |4 TABLE USERS |7 |5 |5 |4 TABLE USERS |8 |5 |17 |4 - 데이터 블록이 DBMS가 데이터를 읽고 쓰는 단위다.
- 특정 레코드 하나를 읽는 건 불가능하다. 반드시 블록 단위로 읽게 된다.
- index 또한 마찬가지로 index only scan을 제외하면 데이터 블록을 읽게 된다.
- 아래는 table scan시의 예시다.
TABLE 세그먼트
EMP 테이블 Salary 테이블 ........
EMP 테이블 내 익스텐트
익스텐트1
데이터 블록1
EMPNO ENAME MGR JOB SAL DEPTNO
7369 SMITH 7902 CLERK 800 20
7499 ALLEN 7968 SALESMAN 1600 30
7521 WARD 7698 SALESMAN 800 30
7566 JONES 7839 MANAGER 800 20
7654 MARTIN 7698 SALESMAN 800 30
데이터 블록2
EMPNO ENAME MGR JOB SAL DEPTNO
7698 BLAKE 7839 MANAGER 800 30
7782 CLARK 7839 MANAGER 1600 10
7788 SCOTT 7566 ANALYST 800 20
7839 KING PRESIDENT 800 10
7844 MARTIN 7698 SALESMAN 800 30
데이터 블록3
EMPNO ENAME MGR JOB SAL DEPTNO
7876 ADAMS 7788 CLERK 1100 20
7900 JAMES 7698 CLEKR 950 30
7902 FORD 7566 ANAYLST 3000 20
7934 MILLER 7782 CLERK 1300 10
데이터 블록4
- 예를 들어 1byte짜리 컬럼인 JOB(varhcar2)을 읽고 싶다고 해보자.
- column 단위로는 당연히 읽을 수 없다. 그런데 문제는 record 단위로도 읽을 수 없다는 점이다.
- 무조건 block 단위로 읽어야 한다. 보통 block은 8KB다(VALUE가 8192).
- 아래를 통해 확인 가능한데 보통 8KB다. 2,4,16,32KB 중 선택가능하다.
show parameter block_size - index scan을 해도 1byte짜리 column 값을 가져오려고 8KB 블록을 읽어야 하는 건 마찬가지다.
- 어떤 scan을 하든 data block을 읽어야 하는 건 동일하다.
- 다만 table scan과 달리 index scan은 rowid를 통해 다른 데이터 block을 순차로 읽지 않고 곧바로 접근할 수 있을 뿐이다.
- table은 순차적으로 모든 데이터블록을 읽는 점에서 sequential access라고 한다.
- 반면에 index는 직접 필요한 데이터 블록에 곧바로 접근할 수 있기 때문에 random(임의) access라고 한다.
버퍼캐시
- I/O call을 통해 가져온 data는 매우 값비싸다.
- 따라서 메모리에 저장하는데, 그 공간을 데이터 캐시라고 한다.
- 서버 프로세스와 데이터파일 사이에 데이터 캐시가 있다.
- 데이터가 메모리에 있으면 전기적 접근이 가능하여 물리적 접근(disk I/O)에 보통 10,000배 빠르다.
- 데이터 캐시의 용량은 아래와 같이 확인 가능하다. 요새는 데이터 캐시의 용량을 늘리는 추세다.
show sga
- 캐시에서 블록을 찾으면 프로세스가 I/O call을 할 필요가 없다.
- process가 멈추지 않기 때문에 속도가 그만큼 빨라진다.
- 버퍼캐시는 공유메모리이므로, 버퍼캐시에 놓으면 해당 블록을 원래는 disk I/O로 읽어야하는 다른 process도 이득을 본다.
- 그래서 direct path I/O (배치용도)를 제외한 모든 blcok I/O는 메모리 버퍼캐시를 경유한다.
- index range scan이든 table full scan이든 메모리 버퍼캐시가 필요하다는 의미다.
- 버퍼캐시는 공유자원이라 누구나 접근할 수 있다.
- 다만 동시접근이 일어나는 경우, 순차적으로 접근하게끔 해주어야 한다.
- 이렇게 순차로 접근하게 하는 걸 직렬화라고 한다. 줄세우는 것이다.
- 줄 세우기 메커니즘은 래치로 구현한다.
table data block과 index leaf block
- index leaf block은 data block과 완전히 다르다.
- index leaf block은 end까지 entire read가 필요하지 않다.
- condition을 만족하지 않는 index record의 key를 발견하는 순간 scan을 멈춘다.
- index record는 key-value pair다. key - value pair는 index leaf block만 가지고 있다.
- index root, branch block은 idnex leaf block에 쉽게 접근할 수 있게끔 트리형태로 정보를 제공한다.
- index record는 db에서 사용자에게 별도로 제공해주지 않는다. 해당 command가 없다.
- index range scan은 실제로 index key scan이다.
- root -> branch -> leaf로 내려가면서 key를 scan하고 컨디션을 만족하는 첫 index record를 발견한다.
- 이렇게 starting point를 결정하는 걸 index의 수직적 탐색이라고 한다.
- 그럼 index는 모두 정렬되어 있기 때문에 인접한 key-value pair, 즉 index record를 읽어나간다.
- 그러다 condition을 만족하지 못하는 index record를 발견하면 scan을 멈춘다.
- 이렇게 end point를 정하는 과정을 수평적탐색이다.
- root -> branch -> leaf로 내려가면서 key를 scan하고 컨디션을 만족하는 첫 index record를 발견한다.
- 그럼 index range scan을 통해 필요한 key를 모두 찾게 된다.
- 해당 key에서 value를 찾고, value에 있는 rowid를 찾는다.
- rowid를 통해 다시 table의 data block을 읽으면 table record를 가져올 수 있다.
- 다만 table data block은 entire read가 수반된다. 이러한 과정을 table access라고 한다.
-
이러한 총 과정을 index range scan이라고 통칭한다.
- 만약 select와 order by, group by, where 조건절을 모두 포함해 전부 index안에 존재한다면, table access가 필요없다.
- b-tree는 실제 해당 column에 기반하여 key형태로 보관하고 있기에, 실제 value값에 어느정도 formatting만 해주는 것이다.
- 따라서 해당 formatting을 db에서 역으로 실행하면 select에 필요한 값을 알 수 있다. 이를 covered index, index only scan이라 한다.
- 이 경우 table access가 없기 때문에 엄청나게 빠르다.
index scan
- index는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.
- index를 이용한 tuning에서 중요한 것은 두 가지가 있다.
- 인덱스 스캔 효율화 튜닝이다.
- 정렬을 처음부터 잘해야한다는 의미다.
- index 선두 컬럼을 무엇으로 설정할 지에 관한 것이다.
- 또한 starting point와 end point를 어디로 정할지에 관한 것이다.
- random acces 최소화다.
- 테이블에 access하는 횟수를 최대한 줄여야 한다.
- 사실 이게 훨씬 중요하다.
- index에 어떤 컬럼을 포함시킬 지에 관한 것이다.
- 인덱스 스캔 효율화 튜닝이다.
- 예를 들어 이름, 시력, 학년-반-번호로 구성된 앨범이 있다고 해보자. 여기선 이름이 선두에 있는 정렬 필드다.
- 홍길동을 찾고자 한다면 이름이 선두 정렬 필드라 쉽게 찾을 수 있다.
이름 |시력 |학년-반-번호 강수지 |1.5 |4학년 3반 37번 김철수 |0.5 |3학년 2반 13번 이영희 |1.2 |... 홍길동 |1.0 |2학년 6반 24번 홍길동 |1.5 |5학년 1반 16번 홍길동 |2.0 |1학년 5반 15번 .... - 그런데 만약에 이름이 아닌 시력이 선두라면?
- 홍길동을 찾으려면 앨범에서 이름이 ‘홍’인 것을 찾아가는 게 아니라 전체를 찾아가야 한다.
- 매우 비효율적인 scan이 될 것이다.
|시력 이름 |학년-반-번호 |0.5 김철수 |3학년 2반 13번 |1.0 홍길동 |2학년 6반 24번 |1.2 이영희 |... |1.5 강수지 |4학년 3반 37번 |1.5 홍길동 |5학년 1반 16번 |1.6 김신후 |3학년 1반 24번 |1.7 허재원 |2학년 1반 11번 ... |2.0 홍길동 |1학년 5반 15번 ... - index tuning의 두번째는 random access 최소화인데, 즉 table access 횟수를 줄이는 것이다.
- 인덱스 스캔이 비효율적이어도 학생명부를 뒤지면 된다.
- 그러나 학생명부가 없다면 직접 교실을 찾아가며 방문해야 한다.
-
random access는 학생명부(index leaf block)가 없어 직접 교실(table data block)을 찾아다니는 상태인 것이다.
- index 탐색에는 아래와 같은 세 종류가 있다.
- 수직 탐색: starting point를 정하기
- 수평 탐색: end point를 정하기
- index 탐색이 끝나면 이제 index 필터링을 시작한다.
- range scan으로 얻은 index record(rowid) 중 어떤 것만 random access를 할 지 정한다
- 그렇게 가져온 table record 중 table filtering을 시작한다.
- 실제 resultSet에 담을 걸 정하는 과정이다.
- index를 수직 탐색한다는 의미는 조건을 만족하는 레코드를 찾는다는 의미가 아니다.
- 조건을 만족하는 ‘첫’ 레코드를 찾는다는 의미다. 즉 시작점을 찾는 것이다.
- 아래와 같은 sql이 있다고 해보자.
create index 고객 on 고객(성별, 고객명);
select from 고객
where 성별 = '남'
and 고객명 = '이재희'
- 그럼 index root block -> index branch block -> index leaf block으로 나아가면서 시작점을 찾아간다.
- 남 & 이재희가 발견된 해당 leaf block이 곧 시작점이다.
- 해당 리프 블록에서 scan을 더 수행해서 만족하는 지점이 없으면 scan을 멈춘다. 이렇게 되면 수직 탐색만 존재하는 셈이다.
- 만약 계속 만족하는 조건이 나오면 다음 리프블록도 읽어간다. 이게 수평 탐색이다.
- 수평탐색을 더 자세하게 살펴보자.
- index 수평 탐색은 index range scan의 end point를 찾아가는 과정이다.
- index를 수평탐색하는 이유는 두 가지다.
- 조건절을 만족하는 모든 index record 찾기
- index block leaf는 서로 양방향 연결 리스트라 좌 - 우, 우 - 좌로 모두 이동이 가능하다.
- 따라서 내림차순으로 정렬하면 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직 탐색을 하다 좌측으로 수평탐색을 한다.
- 따라서 오름차순으로 정렬하면 조건을 만족하는 가장 큰 값을 찾아 좌측으로 수직 탐색을 하다 우측으로 수평탐색을 한다.
- 조건절을 만족하는 모든 index record 찾기
- index는 range를 scan하는 것이라는 말을 이제는 이해했을 것이다.
- index range scan양이 똑같기 때문이다. block I/O call할 양이 똑같다는 의미다. 성능차이가 없다는 의미다.
- index column이 모두 조건문에 있고, 해당 조건문이 모두 =인 경우에는 인덱스 구성의 순서를 바꾼다고 해도 성능 변화가 없다.
- 똑같이 index column의 구성 요소면서 모두 등치 조건이라면 where조건문에서의 순서는 index scan에 아무런 영향이 없다.
- 따라서 인덱스가 (고객명, 성별)이든 (성별, 고객명)이든 등치조건이라면 일하는 양은 다르지 않다.
- 구체적 수치로 index scan의 효율성을 따지는 방법은 sql trace를 보면 된다.
- 아래와 같은 trace에서 읽은 블록은 7463개지만, 얻은 record는 10개밖에 안된다.
- 한 블록당 평균 500개가 담긴가도 하면, 5463 x 500 총 3,731,500개 record를 읽어 10개 record를 얻은 것이다.
- 엄청나게 비효율적이라고 볼 수 있다.
Rows Row Source Operation 10 TABLES ACCESS BY INDEX ROWID BIG_TABLE(cr=7471 pr=1466 pw=0 time=22137) 10 INDEX RANGE SCAN BIG_TABLE_IDX (cr=7463 pr=1466 pw=0 time=22328)
index scan의 종류
(1) index range scan
- 선두 컬럼을 가공하지 않은 상태로 조건절에 써야한다.
- index range scan이라고 무조건 효율적 scan을 의미하지 않는다.
- index scan 범위를 줄이고, table access도 줄여야 한다.
SELECT * FROM emp where deptno = 20;
(2) index full scan
- index로 갖고 있는 column이 하나라도 있기 때문에 table full scan이 아닌 index full scan이 가능하다.
- 물론 cardinality가 높아야, 즉 선택률이 낮아야 이렇게 index full scan 활용이 의미가 있다.
create index emp_ename_sal_idx on emp (ename, sal);
SELECT * FROM emp
WHERE sal > 2000
order by ename;
- index full scan이나 index range scan 모두 order by 연산이 생략될 수도 있다.
- index column 순으로 정렬되기 때문이다. 물론 아래와 같은 경우는 order by가 있어야 할 것이다.
- (ename, sal)로 만들었기 때문이다. (sal, ename)으로 만들었고 index scan이 이뤄지면 order by를 안써도 될 수도 있다.
- 다만 oracle 12c부터는 order by를 반드시 명시해주는 게 좋다. batch I/O가 발동하면 index 정렬 순서대로 늘 record가 뽑히진 않기 때문이다.
SELECT /*+ first_rows */ from emp where sal > 1000 order by ename;
(3) index unique scan
- unique index를 = 조건으로 탐색하는 경우, 수직탐색만 한다.
- 다만 결합 index의 경우, 모든 조건을 전부 =으로 써야 index unique scan이 된다.
- (주문일자, 고객ID, 상품ID) PK에서 where 조건문에 주문일자와 고객ID만 있다면 그것은 index range scan이 된다.
create unique index pk_emp on emp(empno);
select empno, ename from emp where empno = 7788;
(4) index skip scan
- 인덱스 선두 컬럼이 조건절에 없어도 index를 활용하는 방식 중 하나다.
- index full scan과 달리 조건에 맞지 않는 경우 index scan을 skip하여 효율적으로 scan한다.
- cardinality가 극단적으로 높고 낮은 조합의 index에 보통 이용된다.
- 성별과 PK의 조합이 대표적이다. 선두컬럼은 중복값이 많고, 후행은 중복값이 적을 때 유용하다.
- 인덱스 선두가 있고 중간 컬럼이 없는 조건절의 경우에도 활용할 수 있다.
- 해당 scan은 조건절에 맞는 index record를 포함할 거 ‘같은’ index leaf block을 scan하는 방식이다.
- index skip scan의 간단한 예시를 보여주면 아래와 같다.
SELECT * FROM 사원 WHERE 연봉 between 2000 and 4000; - 1번, 3번, 6번, 7번, 10번 블록을 읽는다.
- 핵심은 1번 블록과 6번 블록, 10번 블록이다.
- optimizer는 성별이 남과 여만 있다는 사실을 모르기 때문에 남자가 연봉이 800이하여도 1번 블록을 access한다.
- optimizer 기준에서는 ‘남’보다 작은 성별이 존재할 수 있기 때문이다.
- 6번 블록을 access하는 이유는 여성 중 연봉이 2000이상 3000이하인 index record가 저장되기 때문이다.
- 그 외에도 optimizer 기준에서는 남 과 여만 있는 걸 모르기 때문에 남 과 여 사이의 다른 성별이 6번 블록에 저장되므로 access한다.
-
10번 블록을 access하는 이유는 ‘여성’보다 큰 성별이 존재할 수 있기 때문이다.
- 중간 컬럼이 비는 경우의 예시를 살펴보자.
- PK는 업종유형코드 + 업종코드 + 기준일자로 구성되어있다고 해보자.
- 아래는 업종코드라는 중간 컬럼이 비어있다. 이럴 때 index skip scan을 쓸 수 있다.
- 중간컬럼이 비기 때문에 index range scan 기준으로는 기준일자는 index filter 조건이 된다.
- 따라서 업종유형코드가 01인 모든 record를 읽어야만 한다.
- 그러나 index skip scan을 하게 되면 기준일자가 아래에 해당할 거 ‘같은’ index leaf block만 골라서 access한다.
SELECT /*+ INDEX_SS(A 일병업종별거래_PK) */ 기준일자, 업종코드, 체결건수, 체결수량, 거래대금 FROM 일병업종별거래 A WHERE 업종유형코드 = '01' AND 기준일자 BETWEEN '20080501' AND '20080531' - 선두와 중간 모두 비어있고 마지막 컬럼만 있어도 쓸 수 있다.
SELECT /*+ INDEX_SS(A 일병업종별거래_PK) */ 기준일자, 업종코드, 체결건수, 체결수량, 거래대금 FROM 일병업종별거래 A AND 기준일자 BETWEEN '20080501' AND '20080531' - 선두 컬럼이 부등호, BETWEEN, LIKE같은 범위 검색 조건일 때도 index skip scan이 가능하다.
- 기준일자 + 업종유형코드로 구성된 index가 있다고 해보자.
- 만약 index range scan을 하게 되면 기준일자 BETWEEN조건을 만족하는 모든 index 구간을 scan해야 한다.
- 하지만 index skip scan을 하게 되면 기준일자 BETWEEN 구간을 만족하는 구간 중 업종유형코드가 01인 레코드를 포함할 리프 블록만 골라 scan한다.
SELECT /*+ INDEX_SS(A 일병업종별거래_PK) */ 기준일자, 업종코드, 체결건수, 체결수량, 거래대금 FROM 일병업종별거래 A AND 기준일자 BETWEEN '20080501' AND '20080531' AND 업종유형코드 = '01'
(5) index fast full scan
- 논리순서에 따른 scan이 아니라 물리 순서에 따른 scan을 실행한다.
- 따라서 한꺼번에 많이 읽는 게 중요하며, multiblock I/O 방식으로 진행된다.
- 다만 논리순서가 아니기 때문에 제대로 정렬되지 않을 가능성이 높다.
- 아래가 논리적 순서로 배치한 블록이다.
- 루트 ->브랜치 1 ->1->2->3->4->5->6->7->8->9->10번 순으로 블록을 읽는다.
루트 1번 브랜치 2번 브랜치 1번 2번 3번 4번 5번 6번 7번 8번 9번 10번리프 - 물리순서에 따르면 아래와 같다.
- 1->2->10->3->9->8->7->4->5->6 순으로 읽는다.
- index full scan은 leaf block만 필요하기 때문에 root와 branch는 읽어도 버린다.
1번 익스텐트 | 2번 익스텐트 루트 | 루트 1번브랜치 | 2번 브랜치 1번 2번 10번 3번 9번 | 8번 7번 4번 5번 6번
index range scan- LIKE 대신 BETWEEN
- LIKE보단 BETWEEN이 더 나은 경우가 많다.
- 아래와 같이 두 sql이 있다고 해보자.
- 인덱스가 (판매월,판매구분)으로 구성되었다.
- 판매구분은 A와 B가 있고 A가 90%, B가 10%다.
- BETWEEN 201901 and 201912라면 201912에서 A를 만나게 되면 멈춘다.
- LIKE는 그게 안된다. 201913이 존재할 수도 있기 때문에 2020이 나올때까지 전부 읽는다.
- scan량을 줄이는 데 도움이 안되는 것이다.
SELECT *
FROM 월별고객별판매집계
WHERE 판매월 LIKE '2019%'
AND 판매구분 = 'B'
SELECT *
FROM 월별고객별판매집계
WHERE 판매월 BETWEEN '201901' and '201912'
AND 판매구분 = 'B'
- LIKE가 편하다고 남용하면 sql 성능에서 댓가를 치른다.
- 특히 LIKE를 index column에 쓰면 안된다.
- 아래와 같은 쿼리가 있는데, 인덱스가 (회사코드,지역코드,상품명)으로 구성됐다고 해보자.
- 그 경우 아래와 같이 두 개로 나눠서 처리하면 적어도 지역코드가 존재할 떈 range scan량을 줄일 수 있다.
SELECT * 고객ID, 상품명, 지역코드, .... FROM 가입상품 WHERE 회사코드 = :com AND 지역코드 = :reg AND 상품명 LIKE :prod || '%'
SELECT 고객ID, 상품명, 지역코드, ...
FROM 가입상품
WHERE 회사코드 = :com
AND 상품명 LIKE :prod || '%'
- 그런데 sql을 하나로 쓰려고 통합했다고 해보자.
- 그럼 지역코드를 입력해도 입력하지 않은 경우와 range scan양이 크게 다르지 않게 된다.
- access조건이 filter조건으로 변해버렸기 때문에 그만큼 scan양이 많아진 것이다.
SELECT 고객ID, 상품명, 지역코드, ... FROM 가입상품 WHERE 회사코드 = :com AND 지역코드 LIKE :reg || '%' AND 상품명 LIKE :prod || '%' - BETWEEN으로 처리하는 것도 LIKE처럼 처리하는 효과를 낼 떄도 있다. 조심해야 한다.
- 종목코드를 입력하면 종목1과 종목2가 모두 같은 종목코드로 처리되어 = 조건과 동일해진다.
- 종목코드를 입력하지 않으면 왼쪽은 빈칸6개에서 오른쪽은 ZZZZZZ로 처리되어 모든 걸 검색하는 조건과 동일해진다.
- 즉 BETWEEN을 사실상 LIKE처럼 쓴 셈이다.
SELECT 거래일자, 종목코드, 투자자유형코드 FROM 일별종목거래 WHERE 거래일자 BETWEEN :시작일자 AND :종료일자 /**필수조건 */ AND 종목코드 BETWEEN :종목1 AND :종목2 /** 옵션 조건 */ AND 투자자유형코드 BETWEEN :투자자유형 AND :투자자유형2 /** 옵션 조건 */ AND 주문매체구분코드 BETWEEN :주문매체구분1 AND :주문매체구분2 /** 옵션 조건 */
index range scan- BETWEEN vs IN
- in 조건을 활용하면 index 구성을 바꾸지 않고도 index range scan을 효율적으로 진행할 수 있다.
- 만약 아래가 인터넷매물 betwenn 1 and 3이었다면, 1부터 3까지 전부 index record를 scan했을 것이다.
- 그럼 그 아래 조건 아파트시세코드, 평형, 평형타입은 모두 index filter조건으로 기능하게 된다. 비효율적 scan이 일어나게 되는 것이다.
- 대신 아래와 같이 쓰면 수직적탐색을 3번 거치지만, 수평탐색이 전혀 없게 된다.
- 이른바 IN-LIST 변환이다.
SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 인터넷매물 in ('1','2','3')
AND 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
ORDER BY 입력일 DESC
- 위의 sql은 실제로 아래와 같이 변환할 수 있다.
SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = 1
AND 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
ORDER BY 입력일 DESC
UNION ALL
SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = 2
AND 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
ORDER BY 입력일 DESC
UNION ALL
SELECT 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
FROM 매물아파트매매
WHERE 인터넷매물 = 3
AND 아파트시세코드 = 'A01011350900056'
AND 평형 = '59'
AND 평형타입 = 'A'
ORDER BY 입력일 DESC
- 다만 문제는 in에 들어갈 수가 많다면 between 보다 더 비효율적일 수 있다.
- 수직적 탐색이 많아지는데, 그렇게 되면 branch block을 그만큼 반복 탐색하는 횟수가 늘어난다.
- 그게 수평 탐색이 많아지는 것보다 더 비효율을 초래할 수도 있다. 특히 depth가 깊다면 말이다.
- 수평 탐색이 실제로 별로 크지 않았다면 in으로 변환해도 큰 효과를 누리지 못할 수 있다.
- in으로 바꾼다는 것은 결국 수직탐색을 늘리고, 수평탐색을 줄인다는 의미기 때문이다.
- 실제로 아래와 같은 경우, 왼쪽은 in-list로 바꾸는 게 좋지만, 오른쪽은 그닥 효과가 없을 것이다.
- 그럴 떄 만약 코드를 table로 따로 관리한다면, join이 괜찮은 대체재가 될 수 있다.
SELECT b.해당층, b.평당가, b.입력일, b.해당동, b.매물구분, b.연사용일수, b.중개업소코드 FROM 통합코드 a, 매물아파트매매 b WHERE a.코드구분 = 'CD064' AND a.코드 BETWEEN '1' and '3' AND b.인터넷매물 = a.코드 AND b.아파트시세코드 = 'A01011350900056' AND b.평형 = '59' AND b.평형타입 = 'A' ORDER BY b.입력일 DESC - 또는 index skip scan을 사용하는 것도 좋은 선택이다.
- 선두컬럼이 betwenn으로 범위검색을 하는 경우 특히 in-list로 바꾸는 것보다 유용하다.
SELECT /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ count(*) FROM 월별고객별판매집계 t WHERE 판매구분 = 'A' AND 판매월 BETWEEN '201801' AND '201812' - in 조건이 = 조건으로(index access) 작동하려면 in-list 방식으로 풀려야한다.
- union all을 썼을 때와 동일하게 변환될 수 있어야한다는 의미다.
- 즉 BETWENN으로 풀리면 안된다는 의미다.
- 만약 그렇지 않다면 in 조건은 index filter 조건으로 작동한다.
- 그러나 in 조건이 index access 조건으로 풀려야만 좋은 것은 아니다.
- 때로는 filter 조건으로 가는 게 훨씬 나은 판단일 수도 있다.
- in 조건절을 access 혹은 filter로 바꾸는 걸 자의로 조절하려면 hint를 사용하면 된다.
- 아래 방식은 인덱스 선두컬럼만 access 조건으로 사용한다는 의미다.
SELECT /*+ num_index_keys(a 고객별가입상품_X1 1) */ * FROM 고객별가입상품 a WHERE 고객번호 = :cust_no AND 상품ID in ('NH00037', 'NH00041','NH00050') - 아니면 상품ID를 가공해버려서 access조건으로 쓰지 못하게 할 수도 있다.
- 그러면 index filter조건으로만 활용이 가능하다.
SELECT *
FROM 고객별가입상품
WHERE 고객번호 =:cust_no
AND RTRIM(상품ID) in ('NH00037', 'NH00041','NH00050')
SELECT *
FROM 고객별가입상품
WHERE 고객번호 = :cust_no
AND 상품ID || '' in ('NH00037', 'NH00041','NH00050')
- 상품ID를 access 조건으로 활용하려면 아래와 같이 2번째까지 index access 조건으로 사용한다고 하면 된다.
- 그럼 in-list 방식으로 풀려 index access 조건으로 활용할 수 있다.
SELECT /*+ num_index_keys(a 고객별가입상품_X1 2) */ * FROM 고객별가입상품 a WHERE 고객번호 = :cust_no AND 상품ID in ('NH00037', 'NH00041','NH00050')
index를 비효율적으로 타는 경우 1 -OR
- or 조건의 경우, 아래와 같은 조건이 아니면 쓰지 않는 게 효율적이다.
- 인덱스 액세스 조건으로 사용 불가
- 인덱스 필터 조건으로도 사용 불가
- 테이블 필터 조건으로만 사용 가능
- 다만 or expansion으로 활용할 수 있다면 상관없다.
- 예를 들어, (고객ID, 타입, 거래일자)를 index로 묶어보자.
- 고객ID라는 선두컬럼만 범위조건이 아니면 or expansion이 발동할 수 있다.
- 아래와 같은 쿼리는 or expansion이 가능하다.
SELECT *
FROM 거래
WHERE 고객 ID = :cust_id
AND (
(:dt_type = 'A' AND 거래일자 BETWEEN :dt1 and :dt2)
or
(:dt_type = 'B' AND 결제일자 BETWEEN :dt1 and :dt2)
)
- 확실하게 하려면 아래와 같이 hint를 주면 된다.
- or expansion은 IN-LIST처럼 UNION ALL로 바뀌는 것을 의미한다.
SELECT /*+ OR_EXPAND */ *
FROM 거래
WHERE 고객 ID = :cust_id
AND (
(:dt_type = 'A' AND 거래일자 BETWEEN :dt1 and :dt2)
or
(:dt_type = 'B' AND 결제일자 BETWEEN :dt1 and :dt2)
)
- 반면 아래와 같이는 or expansion이 발동하지 않는다.
- 인덱스 선두컬럼이 or조건으로 갔기 때문이다.
- 다시 말해 인덱스 선두컬럼은 OR조건에 쓰이면 안 된다.
SELECT * FROM 거래 WHERE (고객ID is null or 고객ID = :cust_id) AND 거래일자 BETWEEN :dt1 and :dt2
index를 비효율적으로 타는 경우 2 -LIKE/BETWEEN
- LIKE - BETWEEN을 쓸 떄는 아래 같은 조건에 활용하지 않는게 좋다.
인덱스 선두 컬럼 NULL 허용 컬럼 숫자형 컬럼 가변 길이 컬럼 - index 선두컬럼에 LIKE/BETWEEN을 쓰면 그 아래 조건들은 전부 index filter로 처리된다는 걸 알것이다.
- 이번에는 그런문제보다 더 심각하다. 인덱스 선두컬럼이 입력되지 않는 경우는 range를 모르므로 index full scan이 일어난다.
SELECT * FROM 거래 WHERE 고객ID LIKE :cust_id || '%' AND 거래일자 BETWEEN :dt1 AND :dt2 - NULL 허용컬럼에 LIKE를 허용하는 것은 더 문제다.
- 성능이 문제가 아니라 결과 집합에 오류가 생긴다.
- LIKE/BETWEEN NULL같은 건 없다. IS NULL로만 NULL 값을 가져올 수 있기 때문이다.
SELECT * FROM 거래 WHERE 고객ID LIKE '%' AND 거래일자 BETWEEN :dt1 AND :dt2
- 숫자형 컬럼에도 LIKE는 안된다.
- LIKE는 문자열 연산자다. 숫자형 컬럼이 묵시적 형변환이 일어나게 된다.
- 그럼 숫자형 컬럼은 index access가 아닌 index filter조건으로 변환된다.
- 의도치 않게 index scan양이 늘어날 수 있다는 의미다.
- 묵시적 형변환으로 인해 (고객ID, 거래일자) index는 아예 무력화된다는 것도 문제다.
/**고객ID는 int */ SELECT * FROM 거래 WHERE 거래일자 = :trd_dt AND 고객ID/**실제론 TO_CHAR(고객ID)로 변환됨 */ LIKE :cust_id || '%'
- 옵션 조건은 union all을 통해 효율적인 index scan을 할 수도 있다.
- 단, 이경우 (cust_id, 거래일자)와 (거래일자)라는 index가 모두 존재해야 한다.
- 그럼 cust_id가 있든 없든 index range scan이 일어난다.
- 특히 NULL 허용 컬럼이여도 쓸 수 있다는 점에서 매우 좋은 해결방안이다.
SELECT *
FROM :cust_id is null
AND 거래일자 BETWEEN :dt1 and :dt2
UNION ALL
SELECT *
FROM 거래
WHERE :cus_id is not null
AND 고객ID = :cust_id
AND 거래일자 BETWEEN :dt1 and :dt2
- WAS의 경우 옵션조건을 대부분 dynamic sql로 처리한다.
- 좋은 방법이지만, hint를 사용하는 데 있어 신중할 필요가 있다.
- 또한 하드파싱 문제1가 없게 바인드 변수를 잘 사용해야 한다.
index를 비효율적으로 타는 경우 3 -가공된 index column
- 그러나 가장 중요한 것은 인덱스의 선두 컬럼이 조건절에 쓰이는 경우, 조건절의 가장 처음에 가공하지 않은 채로 나와야 한다는 것이다.
- 인덱스가 (소속팀, 사원명, 연령) 순으로 구성했다고 해보자.
- 이는 아래와 같은 의미다.
데이터를 소속팀 순으로 정렬하고, 소속팀이 같으면 사원명 순으로 정렬하고, 사원명까지 같으면 연령 순으로 정렬한다. - 다시말해 이름이 같더라도 소속팀이 다르면 서로 멀리 떨어지게 된다는 의미다.
- 소속팀 정렬이 이뤄지지 않았다면 말이다. 아래 sql은 index range scan의 역할을 못하게 된다는 의미다.
- 그럼 index full scan을 수행하게 된다.
SELECT 사원번호, 소속팀, 연령, 입사일자, 전화번호 from 사원 where 사원명 = '홍길동' - index의 두번째 구성요소는 가공하더라도 index range scan을 탈 수 있다.
- 인덱스가 (기준연도, 과세구분코드, 보고회차, 실명확인번호)라고 해보자.
- 그럼 아래는 index range scan을 수행할 수 있다.
- 기준연도라는 최선두 컬럼이 조건절에 첫번째로 가공되지 않은 채 쓰였기 때문이다.
SELECT * FROM TXA1234 WHERE 기준연도 = :stdr_year and substr(과세구분코드,1,4) = :txtn_dcd and 보고회차 = :rpt_tmrd and 실명확인번호 = :rnm_cnfm_no - 그러나 위의 sql은 성능이 좋기는 어려울 것이다.
- 과세구분코드부터는 조건절로서의 의미를 잃어버렸기 때문이다.
- 그럼 그 뒤 보고회차, 실명확인번호는 모두 index range scan에 아무런 역할을 하지 못한다.
- 아래의 sql도 마찬가지다. index가 (주문일자, 상품번호)라고 했을 때 주문일자만이 range를 좁히는 데 역할을 한다.
- 상품번호는 range를 좁혀 scan량을 줄이는 데 아무런 도움이 되지 못한다.
select * from 주문상품 where 주문일자 = :ord_dt and 상품번호 like '%PING%'
index를 비효율적으로 타는 경우 4 -묵시적 형변환
- 묵시적 형변환도 조심해야 한다.
- 생년월일이 char라면, 아래와 같은 쿼리는 인덱스를 가공하게 된다.
SELECT * FROM 고객 WHERE 생년월일 = 19821225 - 그래서 실제로는 아래와 같은 쿼리를 수행하게 된다.
- 숫자형과 문자형이 같다고 하면, 숫자형이 더 세서 문자가 숫자로 형변환된다.
SELECT * FROM 고객 WHERE TO_NUMBER(생년월일) = 19821225 - 날짜형과 문자형이 만나면 날짜형이 이긴다.
- 따라서 아래와 같은 쿼리는 index 문제는 없다.
SELECT * FROM 고객 WHERE 가입일자 = '01-JAN-2018' - 다만 날짜 포맷을 정확히 지정하는 편이 훨씬 좋다.
- NLS_DATE_FORMAT 파라미터가 다른 환경에서는 결과집합이 다를 수 있기 때문이다.
- 따라서 아래와 같이 date type으로 바꿔주자.
SELECT * FROM 고객 WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY') - 숫자형과 문자형이 만나서 이기는 경우는, LIKE 연산자가 쓰였을 때다.
- LIKE는 그 자체로 문자열 비교 연산자이기 때문이다.
SELECT * FROM 고객 WHERE 고객번호 LIKE '9410%' - 만약 고객번호가 int 형이면 아래와 같이 변환된다.
SELECT * FROM 고객 WHERE TO_CHAR(고객번호) LIKE '9410%' - LIKE 패턴과 관련된 중요한 안티 패턴 중 하나가 옵션 조건을 걸 떄 자주 일어난다.
- 계좌번호가 입력되지 않으면 NULL값이 입력되어 모든 계좌번호가 조회된다.
- 즉 계좌번호는 optional 조건이다.
SELECT * FROM 거래 WHERE 계좌번호 LIKE :acnt_no || '%' AND 거래일자 BETWEEN :trd_dt1 and :trd_dt2 - 위와 같이 사용하게 되면, 계좌번호 컬럼이 숫자형일 때, 자동 형변환이 발생하기 때문에 계좌번호가 아예 index access 조건으로 사용될 수가 없다.
- 그렇다고 (계좌번호, 거래일자)가 아니라 (거래일자, 계좌번호)로 바꾸면 full scan은 아니지만, range scan량이 압도적으로 늘어나게 된다.
- 거래일자 조회 범위에 속한 거래 데이터를 먼저 모두 읽으면서 계좌번호를 필터링하기 때문이다.
- 저 경우에는 dynamic sql을 WAS에서 사용해주는 게 좋다.
- Spring의 경우 Mybatis나 JPA 모두 dynamic sql을 지원한다.
Mybatis의 dynamic SQL
- mybatis의 경우 아래와 같이 if test로 만들 수 있다.
<mapper namespace="com.example.EmployeeMapper">
<select id="getEmployeesByCriteria" resultType="Employee">
SELECT * FROM employees
<where>
<if test="firstName != null">
AND first_name = #{firstName}
</if>
<if test="lastName != null">
AND last_name = #{lastName}
</if>
<if test="departmentId != null">
AND department_id = #{departmentId}
</if>
</where>
</select>
</mapper>
- choose when으로도 구현 가능하다.
<mapper namespace="com.example.EmployeeMapper">
<select id="getEmployeesByJobAndSalary" resultType="Employee">
SELECT * FROM employees
<where>
<choose>
<when test="jobTitle != null">
AND job_title = #{jobTitle}
</when>
<otherwise>
<!-- default condition if no job title is provided -->
AND 1=1
</otherwise>
</choose>
<choose>
<when test="minSalary != null and maxSalary != null">
AND salary BETWEEN #{minSalary} AND #{maxSalary}
</when>
<otherwise>
<!-- default condition if no salary range is provided -->
AND 1=1
</otherwise>
</choose>
</where>
</select>
</mapper>
JPA의 dynamic SQL
- Mybatis가 xml에 직접 query를 썼던 것과 달리 JPA는 모두 Java 소스코드로 query를 만들어낸다.
- JPA의 경우 JPQL은 아래와 같다.
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
public class EmployeeRepositoryImpl {
@PersistenceContext
private EntityManager entityManager;
public List<Employee> findByCriteria(String firstName, String lastName, Long departmentId) {
String jpql = "SELECT e FROM Employee e WHERE 1=1 ";
if (firstName != null) {
jpql += "AND e.firstName = :firstName ";
}
if (lastName != null) {
jpql += "AND e.lastName = :lastName ";
}
if (departmentId != null) {
jpql += "AND e.department.id = :departmentId ";
}
javax.persistence.Query query = entityManager.createQuery(jpql, Employee.class);
if (firstName != null) {
query.setParameter("firstName", firstName);
}
if (lastName != null) {
query.setParameter("lastName", lastName);
}
if (departmentId != null) {
query.setParameter("departmentId", departmentId);
}
return query.getResultList();
}
}
- Specification은 아래와 같이 만들어준다.
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Repository;
import com.example.Employee;
import javax.persistence.criteria.*;
@Repository
public class EmployeeRepositoryImpl {
public List<Employee> findByCriteria(String firstName, String lastName, Long departmentId) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
Root<Employee> root = query.from(Employee.class);
Predicate predicate = cb.conjunction();
if (firstName != null) {
predicate = cb.and(predicate, cb.equal(root.get("firstName"), firstName));
}
if (lastName != null) {
predicate = cb.and(predicate, cb.equal(root.get("lastName"), lastName));
}
if (departmentId != null) {
Join<Employee, Department> departmentJoin = root.join("department");
predicate = cb.and(predicate, cb.equal(departmentJoin.get("id"), departmentId));
}
query.select(root).where(predicate);
return entityManager.createQuery(query).getResultList();
}
}
- QueryDSL로는 아래와 같다.
import com.example.Employee;
import com.example.QEmployee;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQuery;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
@Repository
public class EmployeeRepositoryImpl {
@PersistenceContext
private EntityManager entityManager;
public List<Employee> findByCriteria(String firstName, String lastName, Long departmentId) {
JPAQuery<Employee> query = new JPAQuery<>(entityManager);
QEmployee employee = QEmployee.employee;
BooleanExpression predicate = employee.isNotNull(); // Start with true
if (firstName != null) {
predicate = predicate.and(employee.firstName.eq(firstName));
}
if (lastName != null) {
predicate = predicate.and(employee.lastName.eq(lastName));
}
if (departmentId != null) {
predicate = predicate.and(employee.department.id.eq(departmentId));
}
return query.select(employee)
.from(employee)
.where(predicate)
.fetch();
}
}