'SQL/[책] 친절한 SQL 튜닝' 카테고리의 글 목록

SQL/[책] 친절한 SQL 튜닝

[친절한 SQL 튜닝] 6장. DML 튜닝 2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 2021.06.15
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 2021.06.13
[친절한 SQL 튜닝] 2장. 인덱스 기본 2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O 2021.06.12

[친절한 SQL 튜닝] 6장. DML 튜닝

2021. 6. 26. 17:19

6.0 DML 성능 영향 요소

기본적인 DML 튜닝을 설명하기 전 DML 성능에 영향을 미치는 요소에 대해 설명한다.
인덱스
- 인덱스를 위한 데이터를 생성하거나 삭제하는 작업은 DML 성능에 영향을 준다.
- INSERT, DELETE는 인덱스 조작을 한 번만 수행한다.
- UPDATE는 인덱스 조작을 두 번 수행한다. (DELETE, INSERT)
- 시스템마다 다르지만
  - 인덱스 1개에 100만 건 데이터를 넣을 때는 5초
  - 인덱스 3개에 100만 건 데이터를 넣을 때는 40초

무결성 제약
- PK, FK, Check, Not Null 같은 제약도 DML 성능에 영향을 미친다.
- 시스템마다 다르지만
  - PK가 없으면 100만건 데이터를 넣을 때는 1.3초
  - PK가 있으면 100만건 데이터를 넣을 때는 4.95초

조건절
- 조건절을 확인하기 위하여 SELECT를 한다.
- 따라서 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 한다.

서브 쿼리
- 조건절과 마찬가지로 SELECT 할 때와 동일하게 인덱스를 잘 활용해서 DML을 수행해야 한다.

Redo 로깅
- Redo : 모든 변경 사항을 기록하는 로그이다.
  1. 물리적으로 디스크가 깨질 경우, 데이터베이스를 복구하기 위해 사용
  2. 정전 등으로 버퍼 캐시가 사라질 경우, 캐시를 복구하기 위해 사용
  3. 커밋은 느리므로 혹시 모를 상황을 대비해 트랜잭션에 의한 변경사항을 저장해놓는다.
- DML을 수행할 때마다 로그를 쌓으므로 성능에 영향을 미친다.

Undo 로깅
- Undo : 오라클 9i부터 Rollback을 Undo로 표현
- 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅해야 하므로 성능에 영향을 미친다.
Lock
- Lock은 DML 성능에 아주 큰 영향을 주는 요소이다.
- 길게 잡으면 DML 성능이 안 좋아지고, 짧게 잡으면 데이터 품질이 안 좋아진다.
- 6.4장에서 자세히 설명한다.
커밋
- Lock과 함께 커밋도 DML 성능에 간접적으로 영향을 미친다.
- 커밋의 내부 메커니즘은 다음과 같다.
  1. DML문을 수행하면 버퍼 캐시가 정전 등으로 사라질 수 있으므로 Redo 로깅을 먼저 해놓는다.
  2. Redo 로그 파일에 기록하기 전에 파일 자체도 I/O이므로 느리다. 따라서 로그 버퍼에 변경사항을 먼저 기록한다.
  3. 버퍼 캐시에 변경된 블록을 기록한다.
  4. 커밋을 하게 되면
  5. LGWR 프로세스가 Redo 로그 버퍼 내용을 Redo 로그 파일에 일괄 저장한다.
  6. DBWR 프로세스가 버퍼 캐시에 변경된 블록을 데이터 파일에 일괄 저장한다.

6.1 기본 DML 튜닝

DB는 SQL을 수행하면 Parse, Execute, Fetch Call이 발생한다.
- Parse Call : 1.1장에서 설명한 파싱과 최적화를 수행하는 단계이다.
- Execute Call : 실제로 SQL을 실행하는 단계이다. DML일 경우 해당 단계에서 마무리된다.
- Fetch Call : SELECT문에서 사용자에게 결과를 전송하는 단계이다. 전송할 데이터가 많다면 여러 번 호출된다.

Call은 발생 위치에 따라 User, Recursive Call로 나뉜다.
- User Call : DBMS 외부로부터 인입되는 Call. WAS가 앞단에 있다면 WAS가 DBMS를 호출할 때 발생한다.
- Recursive Call : DBMS 내부에서 발생하는 Call. 함수/프러시저/트리거에 내장된 SQL을 실행할 때 발생한다.

Call은 성능에 영향을 주며, 네트워크를 경유하는 User Call이 성능에 미치는 영향이 크다.
- 100만 건 기준으로 Recurisive Call만 발생하는 for문의 insert문을 짠 PL/SQL은 30초 걸림
- User Call (자바로 짠 코드)는 220초 걸림
- 하나의 SQL인 Insert Into Select은 한번의 Call만 발생하므로 1.4초 걸린다. 따라서 ONE SQL을 최대한 사용하는 것이 좋다.

One SQL로 작성하기 힘들 때는 Array Processing을 활용
- 100만 건 기준으로 Recurisive Call만 발생하는 for문의 insert문을 짠 PL/SQL 4초
- User Call (자바로 짠 코드)는 12초
```
for(int i = 0 ; i < length; i++){ 
	... 
	st.addBatch(); 
} 

st.executeBatch();
```

인덱스 및 제약 해제를 통한 대량 DML 튜닝
- 6.0장에서 설명했듯이 인덱스와 무결성 제약은 DML 성능에 영향을 주는 요소이다.
- 1000만 건의 데이터를 입력하게 되면
  - PK 인덱스 + 일반 인덱스 존재 : 약 1분 19초
  - 인덱스 및 제약사항이 없을 경우 : 약 5.8초
- 애플리케이션이 실행 중에 자주 발생하는 테이블에 대한 인덱스 및 제약을 잠시 동안 해제하기는 어렵다.
- 대량 데이터를 적재하기 위한 배치 프로그램에서는 이들 기능을 해제함으로써 DML 성능을 크게 높일 수 있다.
- 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천한다.
```
-- PK 제약사항 및 PK 인덱스 드랍
ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 DISABLE DROP INDEX;

-- 일반 인덱스 비활성화
ALTER INDEX 인덱스명 UNUSABLE;

-- PK 제약사항 및 PK 인덱스 재생성
ALTER TABLE 테이블 MODIFY CONSTRAINT PK명 ENABLE NOVALIDATE;

-- 일반 인덱스 활성화
ALTER INDEX 인덱스명 REBUILD;
```

뷰 : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체. 실제 데이터는 뷰를 구성하는 테이블에 담겨 있지만 마치 테이블처럼 사용할 수 있다.

조인 뷰 : FROM 절에 두 개 이상의 테이블을 가진 뷰

CREATE OR REPLACE VIEW EMP_DEPT_VIEW AS
	SELECT 
		E.ROWID AS EMP_RID
		,E.*
		,D.ROW_ID AS DEPT_RID
		,D.DNAME
		,D.LOC
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO;

수정 가능 조인 뷰 : 입력, 수정, 삭제가 허용되는 조인 뷰이다. 키 보존 테이블 설정을 해야만 가능하다.(이해가 되지 않는다.)

-- 연봉이 1500 이하인 직원의 휴대폰번호를 '1234'로 변경
-- 하지만 현 상태에서는 ORA-01779 라는 에러가 발생한다.
-- ORA-01779는 키-보존 테이블을 알 수 없어 발생하는 에러이다.
UPDATE EMP_DEPT_VIEW SET EMP_PHONE = '1234' WHERE SAL <= 1500;

6.2 Direct Path I/O 활용

버퍼 캐시의 장점
- 일반적으로 SQL문을 실행하면 1장에서 설명한 버퍼 캐시를 확인해보고 작업을 진행한다.
- 반복적으로 동일한 블록을 찾는 경우에 버퍼 캐시는 성능을 높여주는 아주 좋은 기능이다.

버퍼 캐시의 단점
- 버퍼 캐시를 탐색하는 것도 락에 의해서 느릴 수 있다.
- 반복적으로 동일한 블록을 찾을 경우가 없을 경우에는 오히려 버퍼 캐시를 한번 찾아보는 행위는 성능에 나쁜 영향을 미친다.

오라클은 버퍼 캐시를 사용할 필요가 없을 경우를 위해 버퍼 캐시를 경유하지 않고 곧바로 데이터 블록을 읽고 쓸 수 있는 Direct Path I/O 기능을 제공한다.
버퍼 캐시가 동작하도록 할 수 있는 방법에는 총 6가지가 존재한다.

1. Direct Path Insert를 수행할 때

일반적인 Insert와 Direct Path Insert의 순서는 다음과 같다.

순서	일반적인 Insert	Direct Path Insert
1	FreeList에서 데이터를 입력할 수 있는 블록을 찾음	FreeList를 찾아보지 않고, 맨 뒤에 순차적으로 쌓는다.
2	FreeList에서 할당받은 블록을 버퍼 캐시에서 찾음	블록을 버퍼 캐시에서 탐색하지 않는다.
3	버퍼 캐시에 없으면 데이터 파일에서 읽어 버퍼 캐시에 적재한다.	버퍼 캐시에 적재하지 않고, 데이터 파일에 직접 기록한다.
4	Undo 기록	Undo 기록하지 않음
5	Redo 기록	Redo를 하지 않도록 할 수 있음

버퍼 캐시를 참조하지 않고, Undo, Redo를 로깅하지 않을 수 있어 매우 빠르다.

Direct Path Insert를 유도할 수 있는 방법은 네 가지가 존재한다.
1. INSERT /*+ append */ INTO SELECT ... 처럼 append 힌트로 유도한 경우
2. INSERT /*+ parallel(C 4) */ INTO 고객 C 처럼 parallel 힌트로 유도한 경우
3. CREATE TABLE ... AS SELECT 문을 사용할 경우
4. 데이터를 적재할 수 있는 툴인 SQL*LOADER를 사용할 때 direct 옵션을 true로 준 경우

Direct Path Insert를 사용할 때 주의할 점은 두 가지가 존재한다.
1. 6.4장에서 설명할 Exclusive 모드 TM Lock이 걸려 다른 트랜잭션은 해당 테이블에 DML을 수행할 수 없다. 따라서 해당 테이블에 대해 DML 수행이 없을 때 작업을 진행해야 한다.
2. FreeList 참조하면 삭제된 여유공간을 재활용할 수 있지만, Direct Path Insert는 항상 맨 뒤에 Insert를 하므로 사이즈가 줄지 않고 계속 늘어난다.

2. 병렬 DML을 수행할 때

위에서 설명한 Direct Path Insert를 INSERT에서 사용하는 방법이므로 UPDATE와 DELETE는 사용할 수 없다.
다만 병렬 DML로 UPDATE와 DELETE를 수행할 경우 Direct Path Insert 방식을 사용할 수 있다.

병렬 DML을 사용하기 위해서는 아래와 같이 병렬 DML을 활성화해야 한다.
```
ALTER SESSION SET ENABLE PARALLEL DML;
```

parallel 힌트를 사용하여 병렬 DML을 사용하면 된다.

UPDATE /*+ full(c) parallel(c 4) */ 고객 c ㄴㄸㅆ 고객상태 = '휴먼'
WHERE 최종거래일시 < '20200101';

DELETE /*+ full(c) parallel(c 4) */ FROM 고객 c
WHERE 탈퇴일시 < '20200101';

병렬 DML을 수행할 경우 Direct Path Insert 단점과 동일하게 Exclusive 모드 TM Lock이 걸리므로 잘 사용해야 한다.

3. parallel 또는 parallel_index 힌트를 사용하여 병렬 쿼리로 Full Scan을 수행할 때

병렬 DML처럼 SELECT를 병렬 쿼리로 Full Scan 할 경우 발생
```
SELECT /*+ full(t) parallel(C 4) */ * FROM 고객 C;
```

4. Temp 세그먼트 블록들을 읽고 쓸 때

5. direct 옵션을 true로 활성화하고 export 할 때

6. nocache 옵션을 지정한 LOB 컬럼을 읽을 때

6.3 파티션을 활용한 DML 튜닝

파티셔닝 : 테이블 또는 인덱스 데이터를 특정 컬럼 값에 따라 별도 세그먼트에 나눠서 저장하는 것
- 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경 → 가용성 향상
- 성능적 측면 : 파티션 단위 조회 및 DML, 경합 또는 부하분산

파티션에는 Range, 리스트, 해시 방식이 있다.

Range 방식: 값의 범위에 따라 파티셔닝

create table ...

partition by range(주문일자) (
	partition P2017_Q1 values less than ('20170401')
	, partition P2017_Q2 values less than ('20170701')
	, partition P2017_Q3 values less than ('20170101')
	, partition P2017_Q4 values less than ('20180101')
	, partition P2018_Q1 values less than ('20180401')
	, partition P9999_MX values less than (MAXVALUE)
);

Hash 방식 : 파티션 키 값을 해시 함수에 입력. 파티션 개수를 사용자가 결정
```
create table ...

partition by hash(고객ID) partitions 4;
```

List 방식 : 그룹핑 기준에 따라 데이터를 분할 저장하는 방식

create table ...

partition by list(지역분류) (
	partition P_지역1 values ('서울')
	, partition P_지역2 values ('경기', '인천')
	, partition P_기타 values (DEFALUT)
);

테이블 파티션 : 테이블을 파티셔닝
- 비 파티션 테이블 : 파티셔닝 하지 않은 테이블로 일반적인 테이블이다.
- 파티션 테이블 : 파티셔닝 한 테이블

인덱스 파티션 : 인덱스를 파티셔닝. 테이블 파티션과 인덱스 파티션을 혼동하지 말자.
- 비 파티션 인덱스
  - 파티셔닝 하지 않은 인덱스로 일반적인 인덱스이다.
  - 만약 파티션 된 테이블의 파티션 구성을 변경하게 되면 비 파티션 인덱스는 재생성되어야 하며 그동안 서비스가 중단되므로 조심해야 한다.
  - 아래는 파티션 테이블일 때 비 파티션 인덱스의 예시이다.
- 로컬 파티션 인덱스
```
-- 인덱스 생성문 마지막에 LOCAL 옵션을 주면 로컬 인덱스로 생성된다.
create index ... LOCAL;
```
  - 테이블 파티션과 1:1로 매핑되는 인덱스 파티션. 즉 테이블 파티션 개수만큼 인덱스 파티션이 존재함.
  - 테이블 파티션의 키가 동일하게 로컬 인덱스에 상속된다. 따라서 테이블 파티션의 키와 인덱스 파티션의 키는 동일하다.
    - 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
  - 오라클에서 관리해주므로 테이블 파티션 구성을 변경하더라도 서비스에 영향을 거의 주지 않음
  - 아래는 파티션 테이블일 때 로컬 인덱스의 예시이다.
- 글로벌 파티션 인덱스
```
-- 인덱스 생성문 마지막에 GLOBAL 옵션을 준 후, 파티션을 생성 로직을 넣으면 글로벌 파티션 인덱스로 생성된다.
create index ... GLOBAL
partition by range(주문금액) (
	partition P_01 values less than (10000)
	, partition P_MX values less than (MAXVALUE)
);
```
  - 테이블 파티션과는 별개로 인덱스의 파티션을 다른 방식으로 만든다.
  - 테이블 파티션이 없어도 생성 가능
  - 테이블 파티션 구성을 변경하는 순간 글로벌 인덱스를 재생성해 줘야 하므로 서비스를 중단해야 함.
  - 아래는 파티션 테이블일 때 글로벌 파티션 인덱스의 예시이다.
Prefixed vs Nonprefixed

Prefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치할 경우
Nonprefixed : 인덱스 파티션 키 컬럼이 인덱스 컬럼 구성에서 왼쪽 선두에 위치하지 않거나 인덱스 컬럼에 아예 속하지 않는 경우

로컬 Prefixed
- 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
- 인덱스의 컬럼 구성을 '계절' + '온도'로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 '계절'이 인덱스 컬럼 구성 선두에 위치하므로 이를 로컬 Prefixed라고 한다.
로컬 Nonprefixed
- 테이블 파티션의 키가 '계절'이라면 인덱스 파티션의 키도 '계절'이다.
- 인덱스의 컬럼 구성을 '온도'로 한 로컬 파티션 인덱스를 만들었다면, 파티션 키인 '계절'이 인덱스 컬럼에 아예 속하지 않으므로 이를 로컬 Nonprefixed라고 한다.
글로벌 Prefixed
- 로컬 Prefixed와 설명 동일
~~글로벌 Nonprefixed~~
- 글로벌 파티션 인덱스는 Prefixed 파티션만 지원한다.

Unique 인덱스를 파티셔닝 하려면, 파티션 키(=테이블 파티션의 키 = 인덱스 파티션의 키)가 모두 인덱스 구성 컬럼이어야 한다. 이는 당연한 제약 사항이다.
- 예를 들면 Unique 인덱스가 '주문번호'이고 파티션의 키가 '주문일자'인 로컬 파티션이 존재한다고 가정하자.
- 인덱스 파티션의 키가 '주문일자'이므로 테이블 파티션도 '주문일자'를 기준으로 파티셔닝이 되어있다.
- '주문번호'가 1234인 데이터가 들어오게 되면 중복 값이 있는지 확인하기 위해 모든 인덱스의 파티션을 다 확인해야 한다. 왜냐하면 인덱스 파티션의 키는 '주문일자' 이므로 '주문번호'가 1234인 값이 어디에 있는지 알 수가 없기 때문이다.
- 이렇게 되면 데이터를 Insert 할 때마다 모든 인덱스 파티션을 다 찾아봐야 한다는 게 말이 안 되기 때문에 DBMS 자체에서 당연히 존재하는 제약사항이다.

파티션을 활용한 대량 UPDATE 튜닝

6.1장에서 설명한 '인덱스 및 제약 해제를 통한 대량 DML 튜닝'은 해당 DML이 테이블의 데이터를 5% 이상 수정할 경우 사용하는 것을 추천하지만, 테이블의 데이터가 엄청 많다면, 인덱스를 삭제하고 재생성하는 시간도 무시할 수 없으며 약간의 부담이 존재한다.

만약에 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션이라면 '인덱스 및 제약 해제를 통한 대량 DML 튜닝' 보다 더 좋은 방법이 있다. 특정 파티션에 대한 임시 세그먼트를 만들어 원본 파티션과 바꿔치기하는 방식이다.

-- 1. 임시 테이블을 만든다. nologging 모드로 생성하면 더 빠르다.
CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 1 = 2;

-- 2. 데이터를 수정하고자 하는 데이터를 insert
-- append 힌트로 Direct Path Insert 유도하여 빠르게 insert 하면 좋다.
INSERT /*+ append */ INTO 임시
SELECT 컬럼1, 컬럼2, 컬럼3, (CASE WHEN 데이터변경컬럼 = '1' THEN '2' ELSE '3' END) 데이터변경컬럼
FROM 실제테이블
WHERE 거래일자 < '20210101'; -- 특정 테이블 파티션의 조건으로

-- 3. 임시 테이블에 실제 테이블과 동일하게 인덱스 생성. nologging 모드로 생성하면 더 빠르다.
-- 인덱스를 나중에 만드는 이유는 6.0에서 설명했듯이 DML 성능에 영향을 주기 때문에 나중에 만든다.
CREATE INDEX ...


-- 4. 실제 테이블의 변경하려고 하는 파티션과 임시 테이블을 교체한다.
ALTER TABLE 실제테이블 EXCHANGE PARTITION P202012 WITH TABLE 임시
INCLUDING INDEXES WITHOUT VALIDATION;

-- 5. 임시 테이블 드랍
DROP TABLE 임시;

-- 6. nologging으로 인덱스를 만들었다면 logging으로 전환한다.

파티션을 활용한 대량 DELETE 튜닝

전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
파티션에 할당된 데이터를 모두 삭제할 때
```
ALTER TABLE 테이블명 DROP PARTITION p201412;
```

파티션에 할당된 데이터를 소수만 삭제할 때

DELETE FROM 테이블명
WHERE 거래일자 < '20210101' -- 파티션 조건
AND 상태 = '1' -- 이외 조건

파티션에 할당된 데이터를 대다수 삭제할 때

-- 1. 임시 테이블을 만드는데 삭제하지 않을 데이터만 남긴다. 
-- nologging 모드로 생성하면 더 빠르다.
CREATE TABLE 임시 nologging AS SELECT * FROM 실제테이블 WHERE 거래일자 < '20210101' AND 상태 = '1';

-- 2. 삭제 대상 테이블 파티션을 truncate 한다.
ALTER TABLE 실제테이블 TRUNCATE PARTITION p202012;

-- 3. 임시 테이블의 데이터를 실제 테이블에 Insert 한다.
INSERT INTO 실제테이블 SELECT * FROM 임시;

-- 4. 임시 테이블 드랍
DROP TABLE 임시;

파티션을 활용한 대량 INSERT 튜닝
- 전제조건 : 테이블이 파티셔닝이 돼 있고, 인덱스 파티션도 로컬 파티션
- '인덱스 및 제약 해제를 통한 대량 DML 튜닝'과 마찬가지로 파티션 단위로 인덱스 및 제약을 해제하면 된다.
```
-- 파티션의 인덱스 비활성화
ALTER INDEX 인덱스명 MODIFY PARTITION P202012 UNUSABLE;

-- 파티션의 인덱스 활성화
ALTER INDEX 인덱스명 REBUILD PARTITION P202012;
```

6.4 Lock과 트랜잭션 동시성 제어 (오라클 기준)

오라클은 DML, DDL, 래치, 버퍼, 라이브러리 캐시 등 다양한 종류의 Lock을 사용한다.
이 중 애플리케이션 개발 측면에서는 DML Lock이 중요하다.

DML Lock : 다중 트랜잭션이 동시에 액세스 하는 사용자 데이터의 무결성을 보호하기 위한 Lock
- DML 로우 Lock : 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지
  
  SELECT INSERT UPDATE DELETE
  
  로우 LOCK X 인덱스 존재 시 O O O
- DML 테이블 Lock (TM Lock) : 테이블 구조를 다른 트랜잭션이 변경하는 것을 방지
  - 테이블 Lock이라고 해서 테이블 전체에 Lock이 걸리는 의미가 아님.
  - 테이블 Lock에는 여러 가지 모드가 있고, 어떤 모드를 사용했는지에 따라 후행 트랜잭션이 수행할 수 있는 작업의 범위가 결정됨.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 (0)	2021.06.13
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

[친절한 SQL 튜닝] 5장. 소트 튜닝

2021. 6. 21. 23:31

5.1 소트 연산에 대한 이해

소트 수행 과정
- 소트는 PGA에 할당한 Sort Area에서 수행하며 PGA가 부족하면 Temp 테이블 스페이스를 사용
- 메모리 소트 : PGA 내에서 소트 완료할 경우
- 디스크 소트 : Temp 테이블 스페이스까지 사용하여 소트를 완료할 경우
- 소트는 최대한 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함

아래는 소트 오퍼레이션 종류에 대해 설명한다.

Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타난다.
```
-- Sort Area에 변수를 하나 만들어 데이터를 읽으면서 최대값 비교하며 최대값을 찾아 리턴 
SELECT MAX(SAL) FROM 직원;
```
- MAX, MIN, SUM, AVG 함수를 사용할 때 나타남
- 실제로 데이터를 정렬하진 않고 Sort Area 사용하는 정도

Sort Order By : 데이터를 정렬할 때 나타나는 기본적인 소트 오퍼레이션.
```
SELECT * FROM 직원 ORDER BY SAL DESC;
```

Sort Group By : 그룹별 집계에 나타나는 오퍼레이션
```
-- 부서번호를 기준으로 ORDER BY 하므로 SORT GROUP BY 오퍼레이션 사용
SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO ORDER BY DEPT_NO;

-- ORDER BY가 없으므로 HASH GROUP BY 오퍼레이션 사용
SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO;
```
- 각각의 그룹별로 위 Sort Aggregate에서 했던 방식대로 사용
- 각각의 그룹을 대표하는 값에 대해 ORDER BY가 없다면 Hash Group By라는 오퍼레이션을 사용함.
- ORDER BY가 없을 경우, 그룹을 대표하는 값에 따라 소트 하지 않는다는 것을 명심

Sort Unique : 중복 레코드를 제거하는 소트 오퍼레이션

-- 서브쿼리의 중복을 제거하기 위해 발생
SELECT /*+ ordered use_nl(dept) */
	*
FROM DEPT
WHERE
	DEPT_NO IN (SELECT /*+ unnest */ DEPT_NO FROM EMP WHERE JOB = '개발자');


-- UNION 하여 중복을 제거하기 위해 발생
SELECT JOB FROM EMP WHERE DEPT_NO = 10
UNION
SELECT JOB FROM EMP WHERE DEPT_NO = 20

만약 유일성이 보장된다면 (PK 또는 유니크 인덱스) 이 오퍼레이션은 생략됨.
Distinct 연산자에서도 사용됨.

Sort Join : 4.2장에서 배운 소트 머지 조인을 수행할 때 나타남

MERGE JOIN
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
			INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
			INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)

Window Sort : 윈도우 함수(분석 함수)를 수행할 때 나타남
```
SELECT MGR , ENAME , SAL , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;
```
- 윈도우 함수 : 행과 행간의 관계를 쉽게 정의 및 분석할 수 있는 함수
- RANK, SUM, MAX, MIN , AVG 등등

5.2 소트가 발생하지 않도록 SQL 작성

Union vs Union All
- Union은 중복을 제거하기 위해 소트 연산을 함
- Union All은 중복 제거를 하지 않으므로 소트를 하지 않음
- 중복일 수 없는 데이터들의 집합을 Union으로 처리하면 낭비이므로 데이터가 중복되지 않는다면 Union All을 사용해야 함
- 소트 연산이 일어나지 않도록 Union All을 사용하며 중복을 피하는 방법도 있음
```
SELECT
	..
WHERE 결제일자 = '20180316'
UNION ALL
SELECT
	..
WHERE 주문일자 = '20180316'
AND 결제일자 <> '20180316' -- 또는 LNNVL(결제일자 = '20180316')
```

Exists 활용

DISTINCT는 중복을 제거하기 위해 소트 연산을 함

-- 이 경우 B 데이터를 모두 읽고, 중복을 제거하는 작업이 필요
SELECT DISTINCT A.*
FROM A, B
WHERE A.x = :x
AND B.y = A.y
AND B.z between :dt1 and :dt2

-- 이 경우, B 데이터가 한건이라도 존재하는지만 확인하므로 효율적임
SELECT A.*
FROM A
WHERE A.x = :x
AND EXISTS(
	SELECT 'x' FROM B
	WHERE B.y = A.y
	AND B.z between :dt1 and :dt2
)

해시 조인은 소트가 발생하므로 NL 조인으로 변경해주는 것이 효과적일 수 있다.

5.3 인덱스를 이용한 소트 연산 생략

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로, 인덱스를 이용해서 소트 연산이 생략 가능

Top N 쿼리 : 전체 결과 집합 중 상위 N개 레코드만 선택하는 쿼리
- 인덱스를 탄다면, 부분 범위 처리로 딱 10개만 만나면 멈춘다.
- 실행 계획에는 COUNT(STOPKEY)라고 표현되며 이를 Top N Stopkey 알고리즘이라고 칭함.
- 페이징 처리를 할 때 Top N 쿼리를 사용하는데 반드시 아래에 ROWNUM을 사용해야 한다.

인덱스를 이용한 최대/최솟값 구하기
- 조건절 컬럼 + MIN/MAX 함수 인자의 컬럼이 모두 인덱스에 포함되어 있어야지 소트를 하지 않는다.
- 이런 경우를 First Row Stopkey 알고리즘이 사용되었다고 표현한다.

Top N 쿼리를 이용한 최대/최솟값 구하기
- 인덱스가 모두 포함되지 않을 경우는 Top N 쿼리로 사용하면 된다. ROWNUM ≤ 1;

이력 조회 : 다른 포스팅으로 대신합니다. [SQL] 변경 이력 테이블 생성 및 조회 방법

Sort Group By 생략
```
-- 인덱스 : REGION
SELECT REGION, AVG(AGE)
FROM 고객
GROUP BY REGION
```
- GROUP BY에 선두 컬럼 인덱스가 있다면, 인덱스를 통해, Sort Group By 연산을 생략 가능.
- Sort Group By Nosort라고 표현됨

5.4 Sort Area를 적게 사용하도록 SQL 작성

소트 연산이 불가피하다면 메모리 내에서 처리를 완료하도록 노력해야 함.

소트 데이터 줄이기 : Sort Area에 저장을 적게 해야 하므로, SELECT 절에 필요한 항목만 넣어야 한다.

-- 모든 컬럼을 SORT AREA에 담으므로 아래보다 느리다.
SELECT * FROM 직원 ORDER BY 나이;

-- 이름만 SORT AREA에 담으므로 위보다 빠르다.
SELECT 이름 FROM 직원 OERDER BY 나이;

TOP-N 쿼리가 인덱스를 사용하지 못해도, Sort Area를 적게 사용한다. 그러니 TOP-N은 좋다!
- Top N의 자세한 설명은 다음 포스팅을 참고. [SQL] Top N을 파헤쳐 보자.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 (0)	2021.06.13
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

[친절한 SQL 튜닝] 4장. 조인 튜닝

2021. 6. 15. 23:51

4.1 NL 조인

NL 조인 (Nested 조인) : 기본 조인이며 다중 for문처럼 조인하는 방식
3.4장에서 설명했던 것처럼 수행 빈도가 많은 것은 인덱스를 타야 하므로 Inner 쪽(이중 for문으로 설명하자면 안쪽 for문을 의미) 테이블은 인덱스를 반드시 사용해야 한다.

NL 조인 실행 계획 : 아래는 NL 조인 실행 계획 예시이다. 사원이 Outer에 해당하고 고객이 Inner에 해당한다.

NESTED LOOPS
	TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
		INDEX(RANGE SCAN) OF '사원_X1' (INDEX)
	TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
		INDEX(RANGE SCAN) OF '고객_X1' (INDEX)

힌트를 사용한 NL 조인 방법

-- ordered : FROM절에 기술한 순서대로 조인
-- use_nl(c) : NL 방식으로 조인하라는 의미
SELECT /*+ ordered use_nl(c) */
	*
FROM 사원 e, 고객 c
WHERE ...

-- leading : 조인 순서 정하기
-- use_hash : 해시 조인을 사용하라는 의미로 뒤에서 설명한다.
SELECT /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */
	*
FROM A, B, C, D
WHERE ...

-- 순서를 기술하지 않았다면, 옵티마이저가 알아서 순서를 정하여 NL 조인한다.
SELECT /*+ use_nl(A, B, C, D) */
	*
FROM A, B, C, D
WHERE ...

NL 조인 특징
- NL 조인은 인덱스를 거의 사용하기 때문에 랜덤 액세스 위주의 조인 방식이다. 당연하게도 대량 데이터를 조인하게 되면 비효율이 발생한다.
- 다중 for문처럼 NL 조인은 한 레코드씩 순차적으로 진행한다. 따라서 부분 범위 처리(3.2장에서 설명)를 하게 되면 Fetch Size만큼 읽어 들이면 바로 멈추기 때문에 성능이 좋다.
- 다른 조인보다 인덱스 구성 전략이 특히 중요하다.

NL 조인 튜닝
- 먼저 3장에서 배웠던 일반적인 인덱스 튜닝 방식대로 각각의 쿼리들을 최대한 튜닝한다.
- 조건절에 해당하는 Outer 로우 수가 많고 조인에 성공한 Inner 로우 수가 적다면, 조인 순서를 바꿔보는 것도 좋다.
- 만약 위처럼 하여도 불가능할 경우, 뒤에서 설명할 소트 머지 조인 또는 해시 조인을 사용해본다.

NL 조인 확장 메커니즘 : 오라클이 NL 조인 성능을 높이기 위해 개선한 알고리즘 방식들
- NL 조인 기본 실행계획
```
NESTED LOOPS
	TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
		INDEX(RANGE SCAN) OF '사원_X1' (INDEX)
	TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
		INDEX(RANGE SCAN) OF '고객_X1' (INDEX)
```
- 테이블 Prefetch : 디스크 I/O가 필요해지면 곧 읽게 될 블록까지 미리 읽어서 버퍼 캐시에 적재하는 기능이다. 아래는 Inner 쪽 테이블인 고객의 대한 디스크 I/O 과정에 테이블 PreFecth 기능이 작동된 실행계획이다.
```
TABLE ACCESS BY INDEX ROWID OF 고객
	NESTED LOOPS
		TABLE ACCESS BY INDEX ROWID OF 사원
			INDEX RANGE SCAN OF 사원_X1
		INDEX RANGE SCAN OF 고객_X1
```
- 배치 I/O : 디스크 I/O을 미뤘다가 일정량 쌓이면 한꺼번에 처리하는 기능이다. 아래는 Inner 쪽 테이블인 고객의 대한 디스크 I/O 과정에 배치 I/O 기능이 작동된 실행계획이다.
```
NESTED LOOPS
	NESTED LOOPS
		TABLE ACCESS BY INDEX ROWID OF 사원
			INDEX RANGE SCAN OF 사원_X1
		INDEX RANGE SCAN OF 고객_X1
	TABLE ACCESS BY INDEX ROWID OF 고객
```

NL 조인 자가 진단 : 쿼리와 인덱스가 아래와 같을 때, 인덱스를 튜닝해야 한다. 정답은 책에 기술되어있지 않음
```
-- 인덱스 PRA_HST_STC_N1 : SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT
select *
	FROM PRA_HST_STC a, ODM_TRMS b
WHERE 
	a.SALE_ORG_ID = :sale_org_id
	AND a.STRD_GR_ID = b.STRD_GRP_ID
	AND a.STRD_ID = b.STRD_ID
	AND b.TRMS_DT = :trms_dt
ORDER BY a.STC_DT desc
```
- 추측 1. Outer는 PRA_HST_STC 테이블이고, Inner는 ODM_TRMS이다.
- 추측 2. 따라서 조건절 중, 'AND a.STRD_GR_ID = b.STRD_GRP_ID AND a.STRD_ID = b.STRD_ID'은 INNER 쪽을 찾을 때 사용하는 조건절이다.
- 추측 3. 따라서 인덱스 컬럼 중 두 번째(STRD_GRP_ID)와 세 번째 (STRD_ID)는 인덱스에 의미가 없다.
- 추측 4. STC_DT는 ORDER BY를 위해 필요하므로 사용하는 것이 좋다.
- 즉, SALE_ORG_ID + STC_DT 만 존재하면 될 것으로 판단.

4.2 소트 머지 조인

조인 컬럼에 인덱스가 없을 때, 또는 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, NL조인 대신 소트 머지 조인이나 해시 조인을 사용한다. 소트 머지 조인은 PGA 공간을 사용하는데 SGA와 비교해서 설명하면 아래와 같다.

SGA(System Global Area) : 1.2장에서 설명했던 것처럼 캐싱된 데이터를 저장하는 메모리
- 여러 프로세스가 공유하여 사용한다.
- 단 공유를 하지만, 동시에 사용은 할 수 없으므로 락이 필요하므로 Latch라는 방식으로 락을 사용한다.

PGA(Process Global Area) : 각각의 오라클 서버 프로세스가 가지고 있는 메모리
- 프로세스에 종속적인 고유 데이터를 저장한다.
- 프로세스끼리 공유하지 않아 Lock도 필요 없고 속도가 빠르다.
- 단, 메모리 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스를 사용한다.

소트 머지 조인(Sort Merge Join) : 각각의 쿼리(Outer와 Inner)를 소트 후에 머지하여 NL 조인을 하는 방식이다.

소트 단계에서 얻은 데이터를 PGA에 저장한 후 NL 조인하기 때문에 대량 데이터를 스캔할 때 빠르다.

과정

소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬하여 PGA에 저장한다.

-- use_merge : 소트 머지 조인을 사용하라는 의미이다.
-- 조인 컬럼은 사원 테이블은 '사원번호', 고객 테이블은 '관리자사원번호'이다.
SELECT /* ordered use_merge(c) */
	...
FROM 사원 e, 고객 c
WHERE c.관리자사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 >= 'Z123'
AND c.최종주문금액 >= 20000

-- 양쪽 집합을 조인 컬럼 기준으로 정렬하면 다음과 같다.

-- 사원 데이터를 조인컬럼인 사원번호 기준으로 정렬
SELECT
	...
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 >= 'Z123'
ORDER BY 사원번호


-- 고객 데이터를 조인컬럼인 관리자사원번호 기준으로 정렬
SELECT
	...
FROM 고객
WHERE 최종주문금액 >= 20000
ORDER BY 관리자사원번호

머지 단계 : 정렬한 양쪽 집합을 NL 조인

소트 머지 조인 장점
- Inner를 스캔할 때 데이터가 정렬되어 있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출 수 있다. 즉 Inner를 Full Scan 하지 않아 빠르다.
- NL 조인은 인덱스를 이용하여 SGA를 사용하지만 소트 머지 조인은 PGA을 사용하여 Latch 획득 과정이 없어 빠르다.
- 소트 머지 조인은 소트 하는 과정에서 조인을 위한 인덱스를 실시간으로 만들어내는 과정이라고 생각하면 쉽다. 따라서 조인 컬럼에 대한 인덱스가 각각의 테이블에 없어도 빠르다.

소트 머지 조인 주용도
- 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인 (Cross Join, 카테시안 곱)

소트 머지 조인 실행 계획은 아래와 같다.

-- 소트 후, 위쪽 테이블 기준으로 아래쪽 테이블을 머지 조인
-- 소트할 때 인덱스를 사용해서 인덱스로 표현된 것 뿐이지, 인덱스 없이 스캔했다면 TABLE FULL SCAN으로 나올 수 도 있음
MERGE JOIN
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
			INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
	SORT (JOIN)
		TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
			INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)

4.3 해시 조인

소트 머지 조인은 양쪽 테이블을 정렬하는 부담이 있지만 해시 조인은 그런 부담이 없어 더 좋으며 해시 조인도 PGA를 사용한다.

해시 조인(Hash Join) : 조인 컬럼을 Key로 하는 해시 맵을 만들어 NL 조인을 하는 방식이다.

과정

Build 단계 : 작은 쪽 테이블(Build Input)을 기준으로 조인 컬럼을 Key로 하여 해시 맵을 생성 한다. 단, 아래는 ordered를 명시하였으므로 먼저 읽은 사원 테이블 기준으로 해시 맵을 생성 한다.

-- use_hash : 해시 조인을 사용하라는 의미이다.
-- ordered를 명시하였으므로 먼저 읽은 사원 테이블을 기준으로 해시 맵을 생성
-- swap_join_inputs(e) 힌트를 사용하면 명시한 테이블 기준으로 해시 맵을 생성
SELECT /* ordered use_hash(c) */
	...
FROM 사원 e, 고객 c
WHERE c.관리자사원번호 = e.사원번호
AND e.입사일자 >= '19960101'
AND e.부서코드 >= 'Z123'
AND c.최종주문금액 >= 20000

-- 사원 테이블을 읽어 해시 맵을 생성
-- 조인 컬럼인 사원번호를 해시 맵의 키 값으로 사용
SELECT
	...
FROM 사원
WHERE 입사일자 >= '19960101'
AND 부서코드 >= 'Z123'

Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 조인 컬럼을 가지고 해시 맵을 탐색 하면서 조인

-- 고객 테이블을 하나씩 읽어 앞서 생성한 해시 맵을 탐색
-- 관리자사원번호를 해시 함수에 입력
SELECT
	...
FROM 고객
WHERE 최종주문금액 >= 20000

해시 조인의 PL/SQL 코드로 표현하면 다음과 같다. 큰 쪽 테이블을 outer로 두고 생성한 해시 맵에서 Outer의 조인컬럼 기준으로 해시 맵을 탐색 한다.

begin
	for outer in (SELECT ... FROM 고객 WHERE 최종주문금액 >= 20000) -- Probe 쿼리
	loop -- outer 루프
		for inner in (SELECT ... FROM 해시맵 WHERE 사원번호 = outer.관리자사원번호)
		loop -- inner 루프
        	dbms_output.put_line(..);
		end loop;
	end loop;
end

대용량 Build Input 처리 : 해시 조인을 하려고 작은 테이블을 찾으려고 하였지만, 테이블 둘 다 대용량일 경우에는 DBMS는 Divide & Conquer 방식으로 해시를 만든다.
1. 파티션 단계 : 두 테이블을 쪼개서 각각 여러 개의 해시 맵을 만든다. 기존 해시 조인과 다르게 양쪽 테이블 모두 해시 맵을 만든다. 그러고 나서 서로 짝을 맺어준다.
2. 조인 단계 : 짝이 맺어진 서로의 맵끼리 해시 조인(Build단계, Prove단계)을 수행한다.

해시 조인 장점
- 해시 맵은 PGA 영역에 할당된 Hash Area에 저장한다. 소트 머지 조인과 마찬가지로 PGA를 사용하므로 대량 데이터를 스캔할 때 NL조인 보다 빠르다.
- 소트 머지 조인은 두 테이블을 정렬한 상태로 만든 데이터를 PGA에 저장해야 하지만(또 크기가 크면 Temp 테이블 스페이스를 사용) , 해시 맵은 하나의 테이블만 PGA(똑같이 Temp 테이블 스페이스를 사용하긴 함)를 사용하므로 더 빠르다.

해시 조인 실행 계획은 아래와 같다.

-- 위쪽 테이블 기준으로 해시 맵을 생성한 후, 아래쪽 테이블에서 읽은 조인 키값으로 해시 맵 탐색
-- 소트할 때 인덱스를 사용해서 인덱스로 표현된 것 뿐이지, 인덱스 없이 스캔했다면 TABLE FULL SCAN으로 나올 수 도 있음
HASH JOIN
  	TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE
  		INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)
  	TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
  		INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)

조인 메서드 선택 기준
- 일반적인 경우
  1. 소량 데이터 조인할 때 → NL 조인
  2. 대량 데이터 조인할 때 → 해시 조인
  3. 조건식이 등치가 아닐 경우 → 소트 머지 조인
- 빈도수가 높은 쿼리에 경우
  1. NL 조인과 해시 조인 성능이 같으면, NL 조인
  2. 해시 조인이 약간 더 빨라도 NL 조인
  3. NL 조인보다 해시 조인이 매우 빠른 경우, 해시 조인
- 이유는 해시 조인에 의해 생성된 해시 맵은 단 하나의 쿼리를 위해서만 사용하는 데이터이다. 따라서 같은 쿼리를 100개 프로세스가 동시에 수행하면, 100개 해시 맵이 생성된다.
- 해시 조인은 아래와 같은 경우에만 사용하자
  1. 수행 빈도가 낮고
  2. 쿼리 수행 시간이 오래 걸리는
  3. 대량 데이터를 조인할 때

4.4 서브 쿼리 조인

서브 쿼리 : SQL문 안에 괄호로 묶은 별도의 쿼리 블록
- 인라인 뷰 : FROM 절에 사용한 서브 쿼리
- 중첩된 : WHERE 절에 사용한 서브 쿼리
- 스칼라 서브 쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브 쿼리

쿼리 변환 : 옵티마이저가 SQL을 분석해 더 나은 성능을 위해 의미가 같은 SQL 문장으로 쿼리를 변환하는 작업
단일 쿼리를 옵티마이저가 쿼리 변환을 하여 더 나은 성능을 보여줄 수 있다.
일반적으로 서브 쿼리가 포함된 쿼리는 옵티마이저는 각각의 쿼리를 나눠서 쿼리 변환을 하여 각각에 대해 더 나은 성능을 내도록 한다.
- 더 나은 성능을 위해 각각의 쿼리를 변환하였어도 전체 쿼리에 대해서 최적화가 되었다고 할 수 없다.
- 나무가 아닌 숲 전체를 바라보는 관점에서 쿼리를 이해하려면 먼저 서브 쿼리를 풀어내야만 한다.

아래는 중첩된 서브 쿼리를 잘 사용하는 방법에 대해 설명한다.

필터 오퍼레이션 : 서브 쿼리를 필터 방식으로 처리하는 방법
```
-- no_unnest : 서브쿼리를 쿼리 하나로 판단하여 최적하지 말고 그대로 수행하라는 의미의 힌트. 중첩 해제를 하지 마라.
SELECT 
	...
FROM 고객 c
WHERE c.가입일시 >= '19960101'
AND EXISTS (
	SELECT /*+ no_unnest */ 
    	'x'
	FROM 거래
	WHERE 고객번호 = c.고객번호
	AND 거래일시 >= '20210621'  
)
```
- 필터 방식이란 마치 NL 조인처럼 동작하도록 하는 방식이다.
- 즉, 두 개의 쿼리를 각각 최적화하지 않고, NL 조인처럼 하나의 쿼리로 판단하여 최적화
- 실행계획은 아래와 같다.
```
-- 'FILTER'를 'NESTED LOOP'라고 생각하면 된다.
FILTER
  	TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
  		INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)
	INDEX (RAGNE SCAN) OF '거래_X1' (INDEX)
```
- NL 조인과 차이점
  - 서브 쿼리 조건절에 해당하는 값이 존재하면 조건절이 참이 true가 되었으므로 바로 break
  - 서브 쿼리 조건절에 따른 결과를 캐싱한다. 동일한 조건의 결과가 올 경우 바로 캐시에서 찾을 수 있으므로 빠름.
  - 항상 메인 쿼리가 Outer, 서브 쿼리가 Inner

Unnesting 방식 : 메인과 서브 쿼리 간의 계층구조를 없애고, 서로 같은 레벨로 쿼리를 처리하는 방식이다. 위에서 설명한 필터 방식과 비교해서 이해하면 쉽다.

	Filter 방식	Unnesting 방식
힌트	no_unnest	unnest
Outer와 Inner 쿼리	항상 서브 쿼리가 Inner	힌트를 이용해 선택 가능
조인 방식	NL 조인 방식	NL조인, 해시 조인 등 다양하게 가능

unnest 힌트로 유도 가능
서브 쿼리가 메인 쿼리 보다 먼저 처리될 수 도 있다.
다양한 최적화 기법을 줄 수 있어 필터 방식보다 더 좋은 실행 경로를 찾을 수 있다.
서브 쿼리에 ROWNUM을 사용하면 unnest 힌트를 사용한다고 하여도 서브 쿼리를 최적화하지 않으니 ROWNUM을 서브 쿼리에 쓸 때 조심하자.

서브 쿼리 Pushing : 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
- 조건절에 존재하는 서브 쿼리에 해당하는 데이터가 적다면 서브 쿼리가 먼저 수행되는 것이 좋다. 하지만 필터 방식의 서브 쿼리는 대게 맨 마지막에 처리되므로, 이를 가장 먼저 처리되도록 강제하는 기능이다.
- push_subq 힌트로 유도
- 필터 방식에서만 사용 가능하기 때문에 no_unnest 힌트와 같이 힌트를 사용한다.
- 반대로, 가장 나중에 처리하게 하려면 no_push_subq를 사용하면 된다.

인라인 뷰 서브 쿼리와 조인

이 장 처음에 말했듯이 일반적으로 서브 쿼리가 포함된 쿼리는 옵티마이저는 각각의 쿼리를 나눠서 쿼리 변환을 하여 각각에 대해 더 나은 성능을 내도록 한다.

인라인 뷰도 옵티마이저가 독립적으로 최적화하기 때문에 merge 힌트를 사용하여 다르게 처리할 수 있다.

-- T에 대한 인라인 뷰 안에서는 조건에 해당하는 모든 데이터를 읽어야 한다.
-- 모든 데이터를 읽고 결국엔 고객 테이블의 고객번호와 조인되는 값만 출력한다.
-- 따라서 불필요한 데이터를 조회해서 비효율적이다.
SELECT 
	...
FROM 고객 C
	,(SELECT ... FROM 거래 WHERE 거래일시 >= trunc(sysdate, 'mm') GROUP BY 고객번호) T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
	AND T.고객번호 = C.고객번호
    
    
-- merge : 뷰를 메인 쿼리와 머지하라는 힌트이다.
-- 머지를 하게되면 고객 테이블을 먼저 읽고 조인할 때는 해당 고객들에 대한 데이터만 읽는다.
SELECT 
	...
FROM 고객 C
	,(SELECT /*+ merge */ ... FROM 거래 WHERE 거래일시 >= trunc(sysdate, 'mm') GROUP BY 고객번호) T
WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
	AND T.고객번호 = C.고객번호

스칼라 서브 쿼리와 조인
- 스칼라 서브 쿼리는 NL 조인 방식으로 실행된다.
- 필터 서브 쿼리처럼 스칼라 서브 쿼리도 캐싱 기능을 사용하여 동일한 입력값에 대해서 캐싱해놓는다.
- 스칼라 서브 쿼리는 2개 이상의 항목을 조회할 수 없으므로 그럴 경우에는, 해당 쿼리를 인라인 뷰로 사용하는 편이 낫다.
- 스칼라 서브 쿼리도 unnest 사용이 가능하다.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 (0)	2021.06.13
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

[친절한 SQL 튜닝] 3장. 인덱스 튜닝

2021. 6. 13. 16:49

3.1 테이블 액세스 최소화

테이블 랜덤 액세스 절차
1. 쿼리문에 사용하는 컬럼을 인덱스가 모두 포함하는 경우가 아니라면, 인덱스를 스캔한 후에 ROWID 획득.
2. ROWID가 가리키는 테이블 블록을 버퍼 캐시에서 먼저 찾아봄.
3. 못 찾을 때만 ROWID를 가지고 디스크에서 찾아 블록을 읽는다.
1장에서 설명했지만, 디스크에서 읽는 방법은 엄청 느리지만, 버퍼 캐시를 탐색하는 것도 생각보다 고비용 구조이므로 테이블 랜덤 액세스 수 자체를 줄여야 함.

클러스터링 팩터(CF) : 같은 값을 갖는 데이터끼리 물리적으로 모여있는 정도
- 높을수록 데이터 찾는 속도가 빠르다
어차피 인덱스로 스캔한 수만큼 테이블을 액세스 할 텐데 CF가 좋다고 빠른 이유는 무엇일까?
- ROWID로 테이블을 액세스 할 때, 아주 어렵게 얻은 테이블 블록에 대한 포인터를 바로 해제하지 않고 가지고 있음. => 버퍼 Pinning이라고 함.
- 다음 데이터를 테이블 액세스 하려고 ROWID를 확인해보니, 이전 스캔한 ROWID와 같은 테이블 블록임.
- 그럼 테이블 블록 포인터를 얻기 위한 노력을 다시 할 필요가 없이, 이전 포인터를 그대로 사용하면 되기 때문에 CF가 좋으면 블록 I/O가 적게 발생하여 찾는 속도가 빠름.

인덱스 손익분기점 : Index Range Scan이 Table Full Scan 보다 느려지는 조회 건수 지점
- Table Full Scan으로 스캔할 경우, 어떤 쿼리든 동일한 성능을 가짐.
- Index Range Scan은 조회되는 건수가 많아질 경우 점점 느려짐.
- 이 값은 CF가 나쁘면 손익분기점은 최악의 경우 5% 정도가 되고 아주 좋으면 90% 수준으로 올라간다.
- 테이블의 로우수가 많을수록 인덱스 손익분기점은 더욱 낮아진다.
- 스캔하려는 건수가 1만 건만 돼도 Table Full Scan이 빠를 수 있다.

인덱스 컬럼 추가 : 해당하는 인덱스가 없다고 새로 만드는 것보다, 기존 인덱스에 추가하는 것도 효과적일 수 있음
- 부서번호 + 업무 인덱스에서 부서번호 + 연봉으로 검색하게 되면 부서번호에 해당하는 데이터가 많으면 랜덤 액세스가 많음
- 부서번호 + 업무 + 연봉 인덱스에서 부서번호 + 연봉으로 검색하게 되면 스캔량은 동일하여도 랜덤 액세스는 적음

조건절에 해당하는 컬럼이 모두 인덱스에 존재하지만 다른 컬럼이 필요해 테이블 액세스를 해야 한다면, 해당 컬럼을 인덱스에 추가하는 방법도 있다.

-- 인덱스 : 부서번호 일 경우, 부서번호로 테이블 스캔을 하지만, 수량을 위해서 테이블 액세스 필요
-- 인덱스 : 부서번호 + 수량일 경우, 수량이 인덱스 자체에 있으므로, 테이블 액세스 불필요
SELECT 부서번호, SUM(수량)
FROM 판매집계
WHERE 부서번호 LIKE '12%'
GROUP BY 부서번호;

인덱스 구조 테이블 : 랜덤 액세스가 절대 발생하지 않도록 인덱스 자체에 테이블 데이터를 갖는 방식
- Index-Organized Table(IOT)라고 하며 인덱스를 만들 때 organization index 키워드를 주면 됨
- IOT는 데이터를 정렬 상태를 유지하기 때문에 클러스터링 팩터가 좋음
- 따라서 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리
ex) 사원이 100명인 회사에 날마다 실적을 쌓는 테이블이 있다고 가정. PK인덱스는 사번 + 일자
- 날마다 사원 100명의 실적을 Insert 하기 때문에 한 사원에 대한 실적 데이터는 서로 멀리 떨어지게 됨. 즉 CF가 좋지 않음.
- SELECT * FROM 실적 WHERE 사번 = 'S1234' AND 일자 BETWEEN '20200101' AND '20201231'
- 위 쿼리를 수행할 경우 여러 개의 블록 I/O를 조회해야 하므로 효율적이지 않음.
- 인덱스 구조 테이블로 만들면 사번, 일자 순으로 정렬 상태를 유지하기 때문에 클러스터링 팩터가 좋음. 즉, 같은 데이터끼리 같은 블록에 있으므로 블록 I/O가 기존보다 적게 발생하여 효율적임.

인덱스 클러스터 테이블 : 클러스터 키(ex : 부서번호) 값이 같은 레코드를 한 블록에 모아서 저장하는 방식
- 인덱스를 스캔하면서 값을 찾을 때, 랜덤 액세스가 값 하나당 한 번씩 밖에 발생하지 않음
- 따라서 같은 클러스터 키 값의 데이터가 많을 경우 효율적임
```
-- 인덱스 클러스터 생성
create cluster c_dept# (deptno number(2) ) index; 

-- 클러스터 인덱스 생성
create index c_dept#_idx on cluster c_dept#; 

-- 클러스터 테이블 생성
create table dept (deptno number(2) not null , ... ) cluster c_dept#( deptno );  
```

해시 클러스터 테이블 : 인덱스를 사용하지 않고 특정 키를 가지고 해시 알고리즘을 사용해 클러스터를 찾음
- 인덱스 클러스터와 마찬가지로 값을 찾을 때, 랜덤 액세스가 해시 값 하나당 한 번씩 밖에 발생하지 않음
```
-- 해시 클러스터 생성
create cluster c_dept# (deptno number(2) ) hashkeys 4; 

-- 클러스터 테이블 생성
create table dept (deptno number(2) not null , ... ) cluster c_dept#( deptno );  
```

3.2 부분 범위 처리 활용

부분 범위 처리 : 클라이언트에게 데이터를 전송할 때 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것
인덱스로 액세스 할 대상 레코드가 아무리 많아도 아주 빠른 응답속도를 낼 수 있음.
자바에서는 Fetch Size를 조절할 수 있으며, 적절하게 사용해서 불필요한 레코드를 조회하지 않게 하는 것이 좋다. 그렇다고 너무 적게 잡으면 Fetch Call이 많아질 수 있다.
ORDER BY 대상이 INDEX 컬럼으로 잘 정의되어 있으면 부분 범위 처리가 가능
- 인덱스를 이용한다는 것은 이미 정렬되어 있기 때문에 SORT 연산이 필요하지 않음.
- 따라서 액세스 할 데이터가 많아도 SORT 연산을 하지 않고 바로 일부 데이터만 Fetch 할 수 있음

3.3 인덱스 스캔 효율화

인덱스 액세스 : 인덱스 스캔 범위를 결정하는 조건절
인덱스 필터 : 테이블로 액세스 할지를 결정하는 조건절
테이블 필터 : 쿼리 수행 다음 단계로 전달하거나 최종 결과 집합에 포함할지를 결정

ex) 인덱스 C1, C2, C3, C4 순으로 존재할 때

-- C1 ~ C4 인덱스 엑세스 조건
SELECT * FROM A WHERE C1='' AND C2='' AND C3='' AND C4=''

-- C1 ~ C3 인덱스 엑세스 조건, C4 인덱스 필터 조건
SELECT * FROM A WHERE C1='' AND C2='' AND C3 BETWEEN '' AND '' AND C4=''

-- C1 인덱스 엑세스 조건, C2 ~ C4 인덱스 필터 조건
SELECT * FROM A WHERE C1 BETWEEN '' AND '' AND C2='' AND C3='' AND C4=''

오라클은 보통 위와 같은 케이스의 실행계획을 보면 모두 인덱스 액세스에 표현함. 구별할 줄 알아야 함.
인덱스 뒤쪽 컬럼이 조건절에 없거나 등치 조건이 아니더라도, 비효율이 없다.
반면, 선행 컬럼이 조건절에 없거나 부등호, BETWEEN 같은 범위 검색 조건이면 비효율이 발생한다.

BETWEEN을 IN-List로 전환

만약 선행 컬럼을 BETWEEN을 해야 하고, 인덱스를 바꿀 수 없다면, BETWEEN을 IN으로 바꾸면 옵티마이저가 INLIST ITERATOR(UNION ALL)를 사용하여 큰 효과를 얻을 수 있다.

-- 인덱스 : 부서번호 + 연봉

-- 선행 컬럼인 부서번호가 BETWWEN으로 사용되어 효율적이지 않음
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 BETWEEN '1' AND '2'
AND 연봉 = '4000'


-- IN을 사용하면 옵티마이저는 IN-List로 변환하여 최적화
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 IN('1', '2')
AND 연봉 = '4000'

-- 위 쿼리는 마치 아래 쿼리처럼 동작한다.
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 = '1'
AND 연봉 = '4000'
UNION ALL
SELECT 부서번호, 연봉
FROM 사원
WHERE 부서번호 = '2'
AND 연봉 = '4000'

IN의 조건 개수가 많아지거나 계속 추가되면 관리하기가 힘들다. 따라서 NL 조인으로 IN 조건의 값을 다른 테이블에서 조인하여 가지고 오면 동일한 효과를 볼 수 있음.

-- 인덱스 : 부서번호 + 연봉

-- IN의 조건이 추가될 경우, 불편함.
SELECT 
	부서번호, 연봉
FROM
	사원
WHERE
부서번호 IN('1', '2', '3', '4', '5')
AND	연봉 = '4000'

-- 부서번호를 코드값으로 가지고 있는 테이블과 조인하여 사용하면 동일하게 In-List 방식으로 최적화 됨.
SELECT /*+ ordered use_nl(B) */
	B.부서번호, B.연봉
FROM 
	코드 A, 사원 B
WHERE
A.코드값 = '사원번호'
AND A.코드 BETWEEN '1' AND '5'
AND B.부서번호 = A.코드
AND B.연봉 = '4000'

In-List는 값 개수만큼 인덱스 스캔을 위한 수직적 탐색을 하므로 오히려 비효율이 생길 수 있으므로 잘 고려해서 사용해야 함.
In-List는 선택되는 레코드끼리 서로 멀리 떨어질 때 효율이 더 좋음. 바로 근처에 레코드가 있을 경우 오히려 IN-List로 조회하면 수직적 탐색 비용이 더 들 수 있음.
BETWEEN을 IN으로 수정하지 않고, INDEX_SS 힌트를 주어 Index Skip Scan을 사용하여도 비슷한 효율을 얻을 수 있다.

IN은 '='이 아니다. (이해가 잘 되지 않음)

IN이 '='로 처리되려면 IN-List 방식으로 풀려야 한다. 그렇지 않으면 필터 조건으로 사용됨.

만약 해당 필드를 IN-List로 풀고 싶거나 하는 방법은 아래와 같은 힌트를 사용

-- 인덱스가 고객번호 + 상품ID 일때

-- num 값을 1을 주었으므로 고객번호까지만 인덱스를 사용. 
-- 즉 IN-List로 풀지 않고 상품ID는 필터 조건으로 사용됨
select /*+ num_index_keys(TABLE 인덱스명 1) */ 
FROM TABLE
WHERE 고객번호 = :cust_no
AND 상품ID in ('1','2','3')

-- num 값을 2를 주었으므로 상품ID까지 인덱스를 사용하여 In-List로 풀게 됨
select /*+ num_index_keys(TABLE 인덱스명 2) */ 
FROM TABLE
WHERE 고객번호 = :cust_no
AND 상품ID in ('1','2','3')

LIKE 검색보다는 BETWEEN이 낫다.
- BETWEEN은 정확한 시작점과 끝을 알 수 있지만, LIKE는 혹시 모를 값을 위해 앞 뒤로 한 번씩 더 스캔함.

코딩을 쉽게 하려고 LIKE나 BETWEEN를 이용한 범위 검색 조건을 너무 남용하지 말자.
```
-- 인덱스 : 사원번호 + 이름 + 주소
SELECT 
	*
FROM
	사원
WHERE
사원번호 = :no
AND 이름 LIKE :name || '%'
AND 주소 LIKE :addr || '%'
```
- 이름과 주소는 각각 옵션이기 때문에 위와 같이 쿼리를 작성함.
- 이름을 LIKE로 검색하였기 때문에 이름을 입력하여도 INDEX 스캔 비효율이 발생함.
- 데이터가 많은 테이블에서는 귀찮지만 각각의 옵션 여부에 따라 쿼리를 여러 개 작성하여 '='로 하는 것을 고려해야 함.

선두 컬럼에 OR 조건이 있으면 인덱스를 타지 않는다.

-- 인덱스 : 사원ID + 이름
-- 사원ID가 선두컬럼이어도 OR를 사용하여서 인덱스를 사용하지 않음
SELECT *
FROM 사원
WHERE (:id is null or 사원ID = :id)
AND 이름 between :name1 and :name2

후행 컬럼의 LIKE/BETWEEN : 필수 조건이 변별력이 좋을 경우 (해당 조건에 해당하는 데이터가 적을 경우) 후행 컬럼이 LIKE/BETWEEN을 사용해도 괜찮다.

-- 인덱스 : 상품명 + 상품분류코드
-- 상품명의 변별력이 좋기 때문에 충분히 좋은 성능 발휘
SELECT * FROM 상품
WHERE 상품명 = :nm
AND 상품분류코드 LIKE :cd || '%'


-- 인덱스 : 상품대분류코드 + 상품코드
-- 상품대분류코드는 변별력이 좋지 않아 만약 상품코드를 입력하지 않으면 엑세스할 데이터가 많아 성능이 좋지 않음
SELECT * FROM 상품
WHERE 상품대분류코드 = :big_cd
AND 상품코드 LIKE :cd || '%'

LIKE/BETWEEN 튜닝 위치
- 인덱스 선두 컬럼
- NULL 허용 컬럼 : NULL 값이 실제 조건에 입력되면, 그 데이터는 결과 집합에 누락됨
- 숫자형 컬럼 : LIKE 검색을 하게 되면 문자열로 형 변환이 되어 인덱스 타지 않음
- 가변 길이 컬럼

3.4 인덱스 설계

인덱스가 많으면
- DML 성능 저하
- DB 사이즈 증가
- DB 관리 및 운영 비용 상승

인덱스 설계 기준 가장 기본
- 조건절에 항상 사용하거나, 자주 사용하는 컬럼을 선정한다.
- = 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.

이외 조건
- 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하
- 저장 공간
- 인덱스 관리 비용 등

수행 빈도가 많은 것은 인덱스를 타야 한다.
- NL 조인(4장에서 설명. 다중 포문이라고 생각하면 된다.)은 Inner 쪽의 쿼리가 여러 번 수행되기 때문에 Inner은 '=' 조건 컬럼을 선두에 두어 인덱스를 최대한 타도록 해야 한다.

인덱스를 공식에 맞춰 만들면 좋지만 현실적으로 불가능하기 때문에 전략적으로 작성해야 한다.
- 조건 컬럼이 = 로 조회하는 것이 있고, BETWEEN으로 일자를 검색하는 쿼리가 있다고 가정하면 일자를 선두 컬럼으로 두고, 나머지 조건 컬럼을 뒤에 후행 컬럼으루 만드는게 좋다.
- 가장 많이 사용되는 '=' 조건 컬럼이 있다면 그 조건컬럼은 선두 컬럼으로 두고, 일자를 그 뒤로 두는 인덱스가 하나 정도 있는것도 좋음.

소트 연산을 생략하기 위한 인덱스 설계 공식
1. '=' 연산자로 사용한 조건절 컬럼을 가장 앞에 선정
2. ORDER BY 절에 기술한 컬럼 추가
3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정

결합 인덱스 선택도 : 인덱스 컬럼을 모두 '=' 조회했을 때 전체 레코드 대비 선택되는 비율
- 선택도가 높으면 인덱스를 만들어봤자 테이블 액세스가 많이 발생하므로 비효율적이다.

중복 인덱스 제거
- 완전 중복 : 인덱스가 앞에서부터 동일하게 포함된 게 있다면 중복이므로 작은 인덱스를 제거해야 함.
- 불완전 중복 : 선두 컬럼은 같지만, 두 번째 컬럼이 모두 다른데, 선두 컬럼이 만약 카디널 리티가 5 이하정도 라면 그냥 다 합치는 게 나음.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

[친절한 SQL 튜닝] 2장. 인덱스 기본

2021. 6. 12. 23:17

2.0 미리 알아보는 DB에서 데이터를 찾는 방법

'2.1.1 미리 보는 인덱스 튜닝'에서 설명하는 내용을 따로 내 방식대로 설명

학교에서 이름이 '홍길동'이라는 학생의 교실을 찾는 방법
- 모든 교실을 돌며 찾는다.
  - Table Full Scan
- 이름으로 소팅된 명부를 보고 '홍길동'이 있는 교실을 알아낸다.
  - 이름을 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
  - 이름으로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음.

학교에서 이름이 '홍길동'이고 출생월이 '5월~6월'인 학생의 교실을 찾는 방법
- 모든 교실을 돌며 찾는다.
  - Table Full Scan
- 이름으로 소팅된 명부를 보고 '홍길동'이 있는 교실을 돌며 생월이 '5월~6월'인지 물어보며 찾는다.
  - 이름을 인덱스로 지정한 Index Scan후, 실제 테이블 액세스.
  - 테이블 액세스 후, 생월이 '5월~6월'인지 확인함.
  - 이 경우는 '5월~6월'이 아닌 '홍길동'이 있을 수 있으므로 테이블 액세스를 통해 생월이 확인해야 하므로 테이블 액세스가 발생. => 랜덤 액세스(테이블 액세스)가 발생하므로 '랜덤 액세스 최소화 튜닝'이 필요. 아래와 같이 인덱스를 튜닝하면 랜덤 액세스가 발생하지 않음.
- 생월, 이름 순으로 소팅된 명부를 보고 교실을 알아낸다.
  - 생월, 이름을 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
  - 생월, 이름으로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음.
  - 단, '5월~6월' 생이 100명이고 '홍길동'이 5명이라면 생월이 먼저 소트 되어 있기 때문에 불필요한 인덱스 스캔이 많아짐 => 인덱스 스캔의 효율이 적기 때문에 '인덱스 스캔 효율화 튜닝'이 필요. 아래와 같이 인덱스를 튜닝하면 스캔의 효율이 좋아진다.
- 이름, 생월 순으로 소팅된 명부를 보고 교실을 알아낸다.
  - 이름, 생월 인덱스로 지정한 Index Scan 하여 교실을 알아낸다.
  - 이름, 생월로 된 인덱스만으로 교실을 알 수 있기 때문에 이 경우는 테이블 액세스가 없음.
  - '5월~6월' 생이 100명이고 '홍길동'이 5명이라면, 이름이 먼저 소트 되어 있기 때문에 불필요한 인덱스 스캔이 많지는 않음

SQL 튜닝은 랜덤 액세스를 줄이기 위한 '랜덤 액세스 최소화 튜닝'과 인덱스 스캔을 줄이기 위한 '인덱스 스캔 효율화 튜닝'을 의미한다.

1장에서 말했듯이 SQL이 느린 이유는 거의 무조건 디스크 I/O 이기 때문에 '인덱스 스캔 효율화 튜닝'보다는 '랜덤 액세스 최소화 튜닝'이 중요하다. SQL 튜닝은 랜덤 I/O와의 전쟁이다.

2.1 인덱스 구조 및 탐색

인덱스는 B*Tree 구조로 되어있다.
- ~~대학교 때 배웠었는데 완전히 까먹었다..~~
- B Tree의 단점을 내가 좋아하는 도널드 커누스가 새롭게 제안한 트리
- 루트 노드가 아닌 노드는 최대 저장 공간의 2/3 이상의 자료가 저장되어야 함
  (B-트리는 ⌈m/2⌉ - 1 조건에 의해 최소 1/2 이상의 자료가 저장되어야 함)
- 노드에 저장되는 자료가 넘치는 경우 재분배
  (B-트리는 항상 중간값을 가지는 자료를 부모 노드로 올려 보내고 분할하였음)

최종적으로 하단에 있는 리프 블록에는 테이블 레코드를 가리키는 ROWID를 갖는다.
- ROWID = 데이터 블록 주소 (DBA) + 로우 번호
- DBA = 데이터 파일 번호 + 블록 번호
- 블록 번호 = 데이터 파일 내에서 부여한 상대적 순번
- 로우 번호 = 블록 내 순번

인덱스는 수직적 탐색으로 인덱스 스캔의 시작점을 찾고, 이후 수평적 탐색으로 본격적으로 데이터를 찾음
- 수직적 탐색 : B*Tree 루트에서 시작해 리프 블록까지 수직적 탐색을 통해 조건을 만족하는 첫 번째 레코드를 찾는 과정
- 수평적 탐색 : 리프 블록끼리는 양방향 링크드 리스트이다. 따라서 수직적 탐색을 통해 첫 번째 레코드인 리프에 도달한 후, 해당 조건을 만족하는 다른 리프 블록을 찾기 위해 수평적으로 탐색하는 과정

결합 인덱스 생성 시, 컬럼 순서에 따른 인덱스의 구조(즉 B*Tree의 구조)에는 영향이 없다.
- 이름 + 성별이나 성별 + 이름 순으로 인덱스를 구성해도 인덱스 구조는 동일하기 때문에 읽는 인덱스 블록 개수는 동일하다.
- 따라서 '결합 인덱스 생성 시 컬럼 배치 순서'에 따른 일 량에는 차이는 없다.
- 이 부분은 여기서까지만 읽으면 이해는 되지만, 이 책을 다 읽고 오면 좀 헷갈린다. 실제 인덱스 선두 컬럼에 따른 I/O 차이 때문에..
데이터를 Delete 하면 인덱스의 구조가 변경되는데, 이때 불균형이 일어난다고 말하는 사람들이 있지만, B Tree의 B는 'Balanced'를 의미하는 것처럼 B Tree는 데이터의 추가, 삭제가 될 경우 항상 Balanced 되게 트리의 구조를 수정한다.
- Index Skew : 불균형은 생길 수 없지만 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우침.
- Index Spares : 인덱스 블록에 밀도가 떨어지는 현상

2.2 인덱스 기본 사용법

인덱스를 정상적으로 사용(Index Range Scan) 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 함.
두 번째 조건은 인덱스의 선두 컬럼을 가공하지 않은 상태로 사용해야 함.
즉, 인덱스를 정상적으로 사용한다라는 의미는 조건절에 인덱스 선두 컬럼을 가공하지 않을 상태로 사용해야 함을 의미한다.

인덱스 컬럼을 가공해서 사용하면 인덱스 스캔 시작점을 찾을 수 없으므로 Range Scan이 불가능
- '이름' 컬럼을 인덱스 컬럼으로 가진 인덱스에서 이름이 '홍길동'이라는 사람을 찾는다면 수직적 탐색을 통해 '홍길동' 리프를 찾고 수평적 탐색을 통해 '홍길동'이 나올 때까지 스캔하면 종료.
- 동일한 조건에서 이름에 '길동'을 포함한 사람을 찾는다면 수직적 탐색을 통해 시작점을 알 수도 없고 스캔의 끝도 알 수 없음.

따라서 데이터를 가공하는 SUBSTR, NVL, LIKE을 사용하면 인덱스를 정상적으로 사용하지 않음.

OR도 일반적으로 인덱스를 정상적으로 사용하지는 않지만 use_concat 힌트를 사용하여 UNION ALL로 변환하여 Range Scan 가능
- OR 조건에 해당하는 값을 여러 문장으로 나눈 후 UNION ALL로 표현하면 각각 Rance Scan을 한다.

IN도 OR가 동일한 기능을 하는데, 마찬가지로 UNION ALL로 표현을 하면 된다.
- IN은 보통 옵티마이저가 알아서 IN-List Iterator 방식으로 Range Scan을 사용 가능하도록 함.
- IN에서 use_concat 힌트를 사용하면 어떻게 될까?
  - IN도 use_concat을 사용하면 UNION ALL로 변환함
- OR은 왜 IN-List Iterator로 안되나?
  - OR도 IN-List Iterator 방식으로 옵티마이저가 변환해줄 수 도 있다.

인덱스는 정렬된 상태로 존재하므로 Range Scan 인덱스를 사용한다는 것은 데이터를 정렬된 상태로 스캔을 한다는 것을 의미함. 따라서 수평적 탐색을 통해 언제 멈춰야 할지 알 수가 있는 것.
- 인덱스를 정상적으로 사용한다면 ORDER BY를 SQL 문장에 사용해도 실행계획에 SORT OREDER BY가 표현되지 않음. 즉, 소트 연산을 하지 않아 더 효율적임.
- 만약 인덱스가 ASC로 되어있는 상태에서 ORDER BY를 DESC로 해야 한다고 해도 옵티마이저가 인덱스를 디센딩 하여 스캔함. (단 제대로 안될 경우, 힌트를 주는 것이 좋음)
- 당연하겠지만 ORDEY BY에 들어간 인덱스 컬럼을 가공할 경우 문제 됨.
- -- 인덱스 : ID + 변경일자 + 변경순번 -- SORT ORDER BY 연산 생략 SELECT * FROM 변경이력 WHERE 장비번호 = '1' ORDER BY 변경일자, 변경순번 -- 가공하여 SORT ORDER BY 연산 필요 SELECT * FROM 변경이력 WHERE 장비번호 = '1' ORDER BY 변경일자 || 변경순번

SELECT 절에서 MAX나 MIN을 인덱스가 컬럼이 포함된 컬럼으로 조회하고 Range Scan을 탈 경우 정렬 연산을 수행하지 않고 바로 찾으러 감.
- 실행 계획에 FIRST ROW로 표현됨
- 마찬가지로 인덱스 컬럼을 가공할 경우 되지 않음
- 인덱스의 맨 마지막 것만 MAX/MIN 조회할 경우 해당되는 건가?
- 조건절 없이 하면 어떻게 될까?
```
-- 인덱스 : ID + 변경일자 + 변경순번

-- 정렬 연산 없이 FIRST ROW로 빠르게 찾음
SELECT MAX(변경순번)
FROM 변경이력
WHERE 장비번호 = '1'
AND 변경일자 = '20210612'


-- 가공할 경우 SORT ORDER BY 연산 필요
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
	* 
FROM 변경이력
WHERE 장비번호 = '1'
AND 변경일자 = '20210612'
```

컬럼의 타입과 다른 타입으로 검색할 경우, 자동형 변환에 의해서 인덱스를 타지 않음
- TO_CHAR, TO_DATE, TO_NUMBER를 사용하지 않아도 필요한 경우 옵티마이저가 자동으로 형 변환하므로 주의

2.3 인덱스 확장기능 사용법

Index Range Scan : 가장 일반적인 인덱스 스캔 방식. 수직적 탐색 + 수평적 탐색

Index Full Scan : 수직적 탐색 없이 리프 맨 처음부터 수평적 탐색하는 방식.
- 인덱스 선두 컬럼이 조건절에 존재하지 않을 경우 옵티마이저가 차선책으로 사용하는 방법
- 옵티마이저는 Table Full Scan을 고려하여 만약 좋지 않다면 Index Full Scan을 고려함
- 조건에 해당하는 로우가 극히 적으면 Index Full Scan이 효율적이므로 이를 사용
- 조건에 해당하는 로우가 많아도 극히 일부 데이터만 fetch 하여 소트 한 데이터를 보여줘야 하는 경우 first_row라는 힌트를 주어 Index Full Scan을 사용하는 방법도 있다.

Index Unique Scan : 유니크한 인덱스의 모든 인덱스 컬럼을 '='을 줄 경우 수직적 탐색만으로 바로 찾는 방식.

Index Skip Scan : 인덱스의 선두 컬럼이나 중간 컬럼이 조건절에 존재하지 않을 경우, 사용할 수 있는 방식
- 오라클 9i부터 Index Full Scan방식 보다 좀 더 나은 차선책
- 단, 존재하지 않는 인덱스 컬럼의 Disticnt 개수가 적고, 후자 컬럼은 많을 경우에 사용해야지 좋음.
- 수직적 탐색으로 시작점을 찾은 후, 조건절에 해당하는 가능성이 있는 블록만 스캔하는 방식
- index_ss 힌트를 주어서 사용 가능

Index Fast Full Scan : 논리적 구조로 스캔하는 방식이 아닌 디스크에 저장된 순서대로 스캔
- Multiblock I/O를 사용하고, 병렬 스캔이 가능하여 빠름
- 단, 순서 보장이 되지 않음
- 인덱스에 포함된 컬럼으로만 조회할 때 사용 가능?
- index_ffs 힌트를 주어서 사용 가능

Index Range Scan Descending : 현재 정렬 방식의 반대로 스캔하는 방식

추가 내용

인덱스를 정상적으로 사용한다고 해서(Index Range Scan) 쿼리가 빨리 수행되는 것은 아님.
수직적 탐색 이후 스캔할 데이터가 100만 건이라면? 인덱스를 타봤자 100만 건을 스캔해야 하므로 문제 됨.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 (0)	2021.06.13
[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O (0)	2021.06.12

[친절한 SQL 튜닝] 1장. SQL 처리 과정과 I/O

2021. 6. 12. 18:05

1.1 SQL 파싱과 최적화

SQL : Structured Query Language

SQL 최적화 : DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 과정
1. SQL 문장의 문법과 의미상 오류가 없는지 파싱함.
2. 미리 수집한 통계정보를 바탕으로 다양한 실행 경로를 생성해 가장 효율적인 길을 선택
3. 가장 효율적인 길을 이용해 실제 로우 소스를 생성

SQL 옵티마이저 : 위 두 번째 단계에서 가장 효율적인 길을 계산하는 DBMS의 핵심 엔진
- 네비게이션처럼 미리 이동 경로를 하나씩 확인하는 방식임.
- 그리고 결정한 이동 경로를 '실행계획'이라는 기능을 이용해서 확인할 수 있음
- SQL 문장으로 실행계획을 ON 시켜 확인할 수도 있고, 보통 DB 툴에서 바로 확인할 수 있음.
- 네비게이션도 항상 가장 빠른 길을 보여주는 것이 아닌 것처럼, SQL 옵티마이저가 정한 실행계획이 최선이 아닐 수 있음. 이럴 때는 힌트를 제공하여 실행계획을 변경해야 함.

힌트 : 옵티마이저에게 자신이 원하는 방향으로 실행계획을 세우도록 힌트를 주는 방법

  -- EMP 테이블에 대해서 해당 인덱스를 사용하라는 간단한 힌트
  -- 테이블을 명시할 때는 스키마를 명시하면 안 됨. 반드시 테이블명만 지정
  SELECT /*+ INDEX(EMP 인덱스명) */
  	*
  FROM ...

  -- 힌트를 여러 개 사용할 때는 콤마(,)를 사용하면 안 됨. 가장 앞에것만 적용돼버림
  SELECT /*+ INDEX(EMP 인덱스명) INDEX(DEPT 인덱스명) */
  	*
  FROM ...

  -- 테이블에 ALIAS를 지정했다면 힌트에도 반드시 지정한 ALIAS로 테이블을 표현해야 함
  SELECT /*+ INDEX(A 인덱스명) */
  	*
  FROM DEPT A ...

1.2 SQL 공유 및 재사용

SQL 문장을 한번 수행하면 최적화 후에 결정한 실행 계획을 재사용하기 위해 캐싱한다.
- SGA(System Global Area)라는 메모리 공간에 존재하는 Library Cache에 캐싱
- 캐싱을 하는 이유는 실행계획을 결정하기 위한 계산이 부담이 됨.
  - 테이블, 컬럼, 인덱스 구조도 파악해야 하고
  - 테이블과 인덱스, 그리고 컬럼 통계를 확인하고
  - Single Block I/O 나 MultiBlock I/O가 빠른지도 확인해야 하고 (밑에 설명)
  - 어떤 인덱스를 사용해야 할지 등등
- 그래서 캐시에 존재하지 않아 최적화의 모든 단계를 수행하는 것을 하드 파싱이라고 표현
- 반대로 Library Cache에서 발견해서 바로 실행단계 넘어가는 것을 소프트 파싱이라고 표현

Library Cache에서 해당 SQL을 캐싱했는지 찾으려면 Map처럼 Key가 필요
- SQL 문장은 따로 Key가 없으므로 SQL 문장 자체를 Key로 사용함.
- SQL 문장이 조금만 달라져도 하드 파싱을 수행. 이 말은 즉 같은 SQL 문장이지만 조건절의 변수 값이 달라도 하드 파싱
- 따라서 바인드 변수를 사용하여 SQL 문장을 작성해야 하는 이유임
```
SELECT * FROM DEPT WHERE DEPT_ID = :1
```

1.3 데이터 저장 구조 및 I/O 메커니즘

데이터베이스 저장 구조 (큰 순서대로 나열)
- 테이블스페이스 : 세그먼트를 담는 컨테이너
- 세그먼트 : 테이블, 인덱스, 파티션처럼 데이터 저장공간이 필요한 오브젝트. 세그먼트는 여러 익스텐트로 구성됨.
- 익스텐트 : 세그먼트(오브젝트)의 공간이 부족할 경우, 공간을 확장하는 단위. 만약 테이블에 데이터가 많이 쌓여서 공간이 부족할 경우 익스텐트를 추가로 할당받음.
- 블록 : 사용자가 입력한 레코드를 실제로 저장하는 공간. 읽고 쓰는 단위임
  - 특정 레코드를 하나만 가지고 오고 싶어도, 블록 단위로 읽기 때문에 블록을 통째로 읽음
  - 블록의 디폴트 크기는 8KB

블록을 읽는 방법에는 시퀀셜 액세스와 랜덤 액세스과 있음
- 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식. 즉 Full Scan
- 랜덤 액세스 : 특정한 랜덤 방식으로 블록을 읽는 방식

캐시
- 위에서 설명했지만, 실행계획은 SGA(System Global Area)라는 메모리 공간에 존재하는 Library Cache에 캐싱함. 이 외에도 함수/프로시저 등도 캐싱하기 때문에 '코드 캐시' 라고도 함.
- 실제 쿼리를 수행해서 디스크 I/O에서 가지고 온 데이터 자체도 캐시에 저장하는데, SGA라는 메모리 공간에 존재하는 DB 버퍼 캐시에 캐싱함. 데이터를 캐시 하므로 '데이터 캐시'라고도 함.

1.3.5 논리적 I/O와 물리적 I/O (세 번째 읽고 있지만, 여전히 이해가 안 됨..)
- 논리적 I/O : DB 버퍼 캐시에서 가지고 온 블록
- 물리적 I/O : 디스크에서 가지고 온 블록. 당연 디스크에서 가지고 오므로 1000배 정도 느림
- 이 다음 설명부터 이해되지 않음..
- SQL이 느린 이유는 거의 무조건 디스크 I/O 때문
- SQL 튜닝 : 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것

Single vs Multi Block I/O
- Single Block I/O : 한 번에 한 블록씩 요청해서 버퍼 캐시에 적재하는 방식
  - 디폴트로 인덱스와 테이블 블록 모두 이 방식을 사용
  - 또한 인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적
- Multi Block I/O : 한 번에 여러 개 블록을 버퍼 캐시에 적재하는 방식
  - 대용량 테이블을 Full Scan 해야 한다면 이 방식이 효율적
  - 일반적으로 1MB. DBMS에서는 더 크게 설정은 할 수 있어도 OS에서 최대 지원하는 I/O 단위가 1MB
  - 블록을 한 번에 가지고 올 때, 한 익스텐트에서만 동작함. 다른 익스텐트로 넘어가서까지 한 번에 가지고 오지는 않음

Scan 방식은 아주 크게 두 가지로 나눌 수 있음
- Table Full Scan : 테이블에 속한 블록 전체를 읽는 방식
  - 이 방식이 항상 나쁜 것은 아님
  - 집계용 SQL 이나 배치성 SQL처럼 많은 데이터를 가지고 와야 한다면 이 방식이 빠를 수 있음.
  - 왜냐하면 Multi Block 방식으로 여러 블록을 한 번에 가지고 오기 때문
- Index Range Scan : 인덱스를 이용하여 읽는 방식
  - 소량의 데이터를 읽을 때 좋은 방식

버퍼 캐시 탐색 방법
1. 해시함수를 사용해서 나온 결과로 링크드 리스트 헤더를 찾음
2. 찾으려고 하는 블록이 나올 때까지 리스트 탐색
3. 발견하면 끝
4. 없다면 디스크로부터 읽어서 리스트에 추가

버퍼는 동시에 사용할 수 없어 락이 필요해서 Latch라는 방식으로 각각의 링크드 리스트마다 락을 걸음

버퍼 캐시를 탐색하는 것도 락에 의해서 느릴 수 있으므로, 버퍼 캐시조차 접근하는 것을 줄여야 한다. 즉, 논리적 I/O를 줄여야 한다.

'SQL > [책] 친절한 SQL 튜닝' 카테고리의 다른 글

[친절한 SQL 튜닝] 6장. DML 튜닝 (0)	2021.06.26
[친절한 SQL 튜닝] 5장. 소트 튜닝 (0)	2021.06.21
[친절한 SQL 튜닝] 4장. 조인 튜닝 (0)	2021.06.15
[친절한 SQL 튜닝] 3장. 인덱스 튜닝 (0)	2021.06.13
[친절한 SQL 튜닝] 2장. 인덱스 기본 (0)	2021.06.12

PREV 1 NEXT

	SELECT	INSERT	UPDATE	DELETE
로우 LOCK	X	인덱스 존재 시 O	O	O