SQL 튜닝

1장. SQL 처리 과정과 I/O

SQL 파싱과 최적화

1. 구조적, 집합적, 선언적 질의 언어
   • 사용자 - SQL - 옵티마이저(DBMS 내부 엔진) - 실행계획(Execution Plan) - 프로시저
2. SQL 최적화
   1. DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정
   2. SQL 실행 전 최적화 과정
      1. SQL 파싱
      2. SQL 최적화
         • 다양한 실행경로를 생성해서 가장 효율적인 하나를 선택
      3. 로우 소스 생성
         • 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
3. SQL 옵티마이저
   1. 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진
   2. 옵티마이저의 최적화 단계
      1. 후보 실행계획 찾기
      2. 데이터 딕셔너리에 미리 수집해 둔 정보를 이용해 각 실행계획의 예상비용을 산정
      3. 최저 비용을 나타내는 실행계획을 선택
4. 실행계획과 비용
   • SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리 구조로 표현한 것
5. 옵티마이저 힌트
   • 옵티마이저는 네비게이션 - 네비 경로가 마음에 들지 않을 때 옵티마이저 힌트로 수동으로 데이터 액세스 경로 선택 가능

SQL 공유 및 재사용

1. 소프트 파싱 vs 하드 파싱
   1. 라이브러리 캐시 : SQL 파싱, 최적화, 로우 소스 생성과정을 거쳐 생성한 내부 프로시저를 재사용할 수 있도록 캐싱해 두는 메모리 공간(p.29 그림 1-4 참조)
   2. 소프트 파싱 : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
   3. 하드 파싱 : SQL을 캐시에서 찾지 못해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것
2. 바인드 변수의 중요성(p.32)
   1. SQL에는 이름이 없다
   2. 라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 ‘SQL문 그 자체’이므로
   3. 대소문자, 공백등이 조금씩 다른 SQL문은 라이브러리 캐시에서 별도 공간을 사용한다.
   4. 이 문제를 해결하기 위해 파라미터 Driven 방식으로 SQL을 작성하는 바인드 변수를 사용해야한다.
   5. 자바에서 Statement 대신 PreparedStatement를 사용하는 이유.

-- 다음은 서로 다른 라이브러리 캐시 공간을 사용한다.
SELECT * FROM emp WEHRE empno = 7900;
select * from EMP where EMPNO = 7900;
select * from emp where empno = 7900;
select * from emp where empno = 7900 ;

// 하드 파싱
public void login(String login_id) throws Exception {
	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = '" + login_id + "'";
	Statement st = con.createStatement();
	ResultSet rs = st.executeQuery(SQLStmt);
	if(rs.next()){
		// do
	}
	rs.close();
	st.close();
}

// 소프트 파싱
public void login(String login_id) throws Exception {
	String SQLStmt = "SELECT * FROM CUSTOMER WHERE LOGIN_ID = ?";
	PreparedStatement st = con.prepareStatement();
	st.setString(1, login_id);
	ResultSet rs = st.executeQuery();
	if(rs.next()){
		// do
	}
	rs.close();
	st.close();
}

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

1. SQL이 느린 이유
   1. 디스크 I/O 때문. 디스크 I/O가 SQL 성능을 결정한다.
   2. SQL 튜닝 : 디스크 I/O 횟수를 줄이는 것
2. 데이터베이스 저장 구조 (p.39 참고)
   1. 테이블스페이스, 세그먼트(테이블, 인덱스, 파티션, LOB), 익스텐트, 블록, 로우
   2. 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. (페이지라고도 불림)
   3. 한 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.
   4. 익스텐트끼리는 연속된 공간이 아니다.
   5. 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 가능성이 더 높다.
   6. 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터파일로 분산해서 저장하기 때문.
3. 블록 단위 I/O
   1. 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
   2. 오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해 8KB를 읽는 셈.
   3. show parameter block_size
4. 시퀀셜 액세스 vs 랜덤 액세스
   1. 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식.
   2. 테이블 블록 간 연결고리 - 오라클의 경우 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리. 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면 Full Table Scan
   3. 랜덤 액세스 : 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식.
5. 논리적 I/O vs 물리적 I/O
   • DB 버퍼캐시.
     1. 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다.
     2. 라이브러리 캐시 : SQL, 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 코드캐시
     3. DB 버퍼캐시 : 데이터 캐시 -> 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.
     4. 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색
     5. show sga명령어로 용량 확인가능 default는 1.3GB정도
   • 논리적 I/O vs 물리적 I/O
     1. 논리적 블록 I/O : SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O.
     2. 물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O. SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스.
     3. 메모리 I/O는 전시적 신호인 데 반해, 디스크 I/O는 물리적 작용이 일어나므로 메모리 I/O에 비해 상당히 느리다. (보통 10000배쯤)
   • 왜 논리적 I/O인가?
6. Single Block I/O vs Multiblock I/O
   • 메모리 캐시가 클수록 좋지만, 비용적, 기술적 한계 때문에 데이터를 모두 캐시에 적재할 수는 없다.
   • Single Block I/O가 효율적일 때
     1. 인덱스와 관련된 블록을 읽을 때
     2. 소량 데이터를 읽을 때
   • Multiblock I/O가 효율적일 때
     1. 많은 데이터 블록을 읽을 때 (프로세스가 잠자는 횟수를 줄여주니까)
   • show parameter db_file_multiblock_read_count 명령어로 Multiblock I/O 단위 확인 가능
   • 일반적으로 OS 레벨 I/O 단위가 1MB. 오라클 레벨 I/O 단위가 8KB이므로 이 파라미터를 128로 설정하면 담을 수 있는 만큼 최대한 담게 된다(8KB x 128 = 1MB)
   • alter session set db_file_multiblock_read_count = 128;
7. Table Full Scan vs Index Range Scan
   1. 테이블에 저장된 데이터를 읽는 방식
      1. 테이블 전체 스캔(Table Full Scan) : Sequencial Access + Multiblock I/O
      2. 인덱스를 이용한 스캔(Index Range Scan) : Random Access + Single Block I/O
   2. 많은 쿼리 툴이 실행계획에 Table Full Scan을 빨간색으로 표시하는데, 무조건 이 빨간색을 없애야한다고 생각하지 말자. 인덱스가 SQL 성능을 떨어뜨리는 경우도 상당히 많다. 한 번에 많은 데이터를 처리하는 배치 프로그램이 대표적.
   3. 인덱스는 중요하다. 하지만 인덱스를 맹신하지 말자. 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다. 대부분 SQL 개선 포인트가 힌트를 이용한 Table Full Scan 유도인 경우도 있었다.
   4. 필자가 쿼리 툴을 개발한다면 예상 카디널리티가 일정량을 넘어서는데도 인덱스로 테이블을 액세스하는 부분에도 빨간색으로 표시해 주고 싶다.
8. 캐시 탐색 메커니즘
   1. Table Full Scan에서 일부만 탐색할 때
   2. 읽어야할 블록 목록을 익스텐트 맵에서 얻는다. (20번 블록, 5번블록에서 데이터를 가져오자)
   3. 버퍼캐시에서 20번 블록을 찾고자 할 때 이 블록이 캐싱돼 있다면 해시 체인에 연결돼 있으므로 해당 해시 체인만 탐색하면 된다. (해시 함수 + 해시 체인) 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스.

2장. 인덱스 기본

인덱스 구조 및 탐색

1. 미리보는 인덱스 튜닝
   1. 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용. OLTP(Online Transaction Processing) 시스템에서는 소량 데이터를 주로 검색하므로 인덱스 튜닝이 더욱 중요.
   2. 익덱스 스캔 효율화 튜닝 (인덱스 스캔 과정 비효율 줄이기). (p.71 설명 너무 좋음. 학생명부 정렬방법에 따른 효율성 차이)
   3. SQL 튜닝은 랜덤 I/O와의 전쟁이다.
2. 인덱스 구조
   • 범위 스캔이 가능한 이유는 인덱스가 정렬돼 있기 때문
   • 일반적으로 B*Tree 형태
3. 인덱스 수직적 탐색
   • 인덱스 스캔 시작지점 찾는 과정
4. 인텍스 수평적 탐색
   • 인덱스에서 본격적으로 데이터를 찾는 과정
5. 결합 인덱스 구조와 탐색(p.79 정독)

인덱스 기본 사용법

1. 인덱스를 사용한다는 것
   1. 인덱스를 정상적으로 사용하기 위해서는 (Index Range Scan) 인덱스 컬럼(선두 컬럼)을 가공하지 않아야 한다.
   2. 인덱스 컬럼을 가공하면 스캔 시작, 종료점을 찾을 수 없어 Index Full Scan 방식으로 작동한다.
2. 인덱스를 Range Scan 할 수 없는 경우
   1. 가공된 값을 기준으로 검색하는 경우 where substr(생년월일, 5, 2) = '05'
   2. 함수 사용 where nvl(주문수량, 0) < 100
      1. 예외 : MIN, MAX함수 (수직적 탐색을 통해 가장 왼쪽, 가장 오른쪽 레코드를 읽으면 된다)
   3. 문자열을 포함하는 값을 검색 where 업체명 like '%대한%'
   4. or 조건으로 검색 where (전화번호 = :tel_no OR 고객명 = :cust_nm)
      • OR Expansion쓰면 해결 가능(p.88)
   5. in 조건으로 검색 where 전화번호 in ( :tel_no1, :tel_no2 )
      • SQL 옵티마이저가 IN-List Iterator 방식을 사용해서 Index Range Scan으로 처리
3. 더 중요한 인덱스 사용 조건
   1. Index Range Scan 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다. 가공하지 않은 상태로.
   2. 인덱스 잘 타니까 튜닝 끝이 아니다. 인덱스 리프 블록에서 스캔하는 양을 따져봐야 한다.
4. 인덱스를 이용한 소트 연산 생략 (p.94)
   1. 인덱스는 정렬되어 있다.
   2. 정렬 연산을 생략할 수 있게 인덱스가 구성돼 있다면, SQL에 ORDER BY문이 있더라도 정렬 연산을 따로 수행하지 않는다. (p.95)
   3. 5장 3절에서 소트 연산 생략을 이용해 성능을 높이는 튜닝 기법에 대해 다룬다.
5. ORDER BY 절에서 컬럼 가공
6. SELECT-LIST에서 컬럼 가공
7. 자동 형변환
   1. 각 조건절에서 양쪽 값의 데이터 타입이 서로 다르면 값을 비교할 수 없다.
   2. 컴파일이 나거나 자동으로 형변환 해야하는데 오라클은 자동으로 형변환이 된다.
   3. 오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다. 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다.
   4. SELECT * FROM 고객 WHERE 생년월일 = 19821225 -> SELECT * FROM 고객 WHERE TO_NUMBER(생년월일) = 19821225로 형변환 된다. 이 경우 인덱스 컬럼이 가공되었기 때문에 Range Scan이 불가능하므로
   5. SELECT * FROM 고객 WHERE 생년월일 = TO_CHAR(19821225) 이런식으로 명시해주는 것이 낫다.
   6. 결론 : 자동 형변환 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다.

인덱스 확장기능 사용법

1. Index Range Scan : 일반적 수직적 탐색 + 필요한 범위(Range)만 수평적 탐색
2. Index Full Scan : 수직적 탐색 없이 인덱스 리프 블록을 전부(Full) 수평적으로 탐색
3. Index Unique Scan : 수직적 탐색만. Unique 인덱스를 ’=‘조건으로 탐색하는 경우
4. Index Skip Scan : 인덱스를 읽다가 중간에 Skip하고 계속 읽어나감
5. Index Fast Full Scan : 논리적 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔. 대신 순서 보장 X
6. Index Range Scan Descending : 내림차순 정렬하는 경우 인덱스를 거꾸로 읽음

3장. 인덱스 튜닝

테이블 엑세스 최소화

1. 테이블 랜덤 엑세스
   1. 파티션 Pruning은 조건절에 해당하는 파티션만 읽는 기능. 왜 필요?
   2. 인덱스 ROWID는 논리적 주소에 가깝다.
   3. 주소정보로 테이블 레코드를 찾아가는 비용이 만만치 않다.
   4. (오해 ㄴㄴ) 인덱스는 ROWID는 포인터가 아니다. 테이블 레코드와 물리적으로 직접 연결된 구조는 더더욱 아니다.
   5. 오라클은 테이블 블록이 수시로 버퍼캐시에서 밀려났다가 다시 캐싱되며, 그때마다 다른 공간에 캐싱되기 때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조다.
   6. 메모리 주소 정보(포인터)가 아닌 디스크 주소 정보(DBA, Data Block Address)를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.
   7. 이것이 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 액세스가 생각만큼 빠르지 않는 이유다.
2. 인덱스 클러스터링 팩터(CF)
   1. 분가한 열 명의 자녀가 모두 한 동네에 모여 살면 CF가 좋기 때문에 부모가 자녀들 집을 모두 방문하는데 하루면 충분하다.
3. 인덱스 손익분기점
   1. p.138 그림 3-7
   2. Full Table Scan은 대용량 데이터를 읽는 데 유리(Sequencial + Multiblock)
   3. Index Scan은 소규모 데이터를 읽는 데 유리(Random + Single block)
   4. 이를 나누는 기준이 인덱스 손익분기점
   5. 보통 전체 데이터의 5~20% 정도에 손익분기점이 생긴다.
   6. CF에 따라 크게 달라진다
   7. 1000만 건 수준의 큰 테이블에선 버퍼캐시 히트율이 낮아지기 때문에 손익분기점이 더 낮아진다.
   8. 온라인 프로그램 튜닝 vs 배치 프로그램 튜닝 (p.142)
4. 인덱스 컬럼 추가 (p.146)
   1. 인덱스 구성을 변경하기 어려울 때는 인덱스에 컬럼을 추가해서 테이블 랜덤 액세스 횟수를 줄일 수 있다.
5. 인덱스만 읽고 처리
6. 인덱스 구조 테이블
   1. 인덱스를 이용한 테이블 액세스는 고비용
   2. 랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하면?
   3. 오라클 - IOT(Index-Organized Table), MS-SQL - 클러스터형 인덱스
   4. 인덱스 리프 블록 = 데이터 블록
7. 클러스터 테이블

부분범위 처리 활용(p.158)

1. 부분범위 처리
2. 부분범위 처리 구현
3. OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

인덱스 스캔 효율화

1. 인덱스 탐색
2. 인덱스 스캔 효율성
3. 액세스 조건과 필터 조건
4. 비교 연산자 종류와 컬럼 순서에 따른 군집성
5. 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율
6. BETWEEN을 IN-List로 전환
7. Index Skip Scan 활용
8. IN 조건은 ’=‘인가
9. BETWEEN과 LIKE 스캔 범위 비교
10. 범위검색 조건을 남용할 때 생기는 비효율
11. 다양한 옵션 조건 처리 방식의 장단점 비교
12. 함수호출부하 해소를 위한 인덱스 구성

인덱스 설계

1. 인덱스 설계가 어려운 이유
2. 가장 중요한 두 가지 선택 기준
3. 스캔 효율성 이외의 판단 기준
4. 공식을 초월한 전략적 설계
5. 소트 연산을 생략하기 위한 컬럼 추가
6. 결합 인덱스 선택도
7. 중복 인덱스 제거
8. 인덱스 설계도 작성

참고자료

• 친절한 SQL 튜닝, 조시형, 디비안
• 남궁성 강사님 강의
• 실행계획을 보는 방법, 읽는 순서, 그리고 간단한 튜닝 - SQL전문가 정미나 유튜브
• SSD를 쓰면 DBMS가 빨라질까? - Naver D2
• 나는 프로그래머다! - DB 튜닝의 모든 것 1부