Effective SQL과의 대화

Effective SQL(SQL 코딩의 기술 발췌: 개인적인 comment 추가)

 

Better way 11 인덱스와 데이터 스캔을 최소화하도록 인덱스는 신중히 만들자

흔히 성능 문제는 인덱스가 부족하거나 올바르지 않은 인덱스를 만들어서 발생하기에 데이터베이스 엔진은 쿼리 조건을 만족하는 레코드를 찾는 데 필요 이상으로 많은 데이터를 처리할 가능성이 크다. 이런 프로세스를 인덱스 스캔 or 테이블 스캔으로 나눌 수 있다

데이터베이스 엔진이 적합한 레코드를 찾으려고 인덱스와 데이터 페이지를 스캔하며 데이터가 많을수록 인덱스 스캔을 완료하는 데 걸리는 시간은 길다.

à 데이터베이스 엔진 즉 옵티마이저는 데이터를 스캔하는 녀석이며 처리 건수에 성능 영향을 받는다.

인덱스는 데이터의 추출 문제를 해결해 주는 만능 해결사라고 여기는 것은 위험한 생각이다. 많은 인덱스가 데이터를 빠르게 가져오지도 않고 데이터 갱신 속도만 느리게 한다. 인덱스를 갱신하는 작업은 종종 테이블을 갱신하는 작업보다 비용이 많이 든다.

à 처음에 인덱스 만들 때 막 만들지 말아라

데이블 스캔보다 비클러스터 인덱스 스캔이 더 나은 성능을 발휘하는지 여부는 테이블 크기, 로우의 저장패턴, 로우의 길이, 쿼리가 반환하는 로우의 비율에 따라 다르다 흔히 전체 로우 중 최소 10%이상의 로우가 반환 될 때 테이블 스캔이 비클러스터 인덱스보다 나은 성능을 보이기 시작한다. 클러스터 인덱스는 보통 반환되는 로우의 비율이 높을수록 테이블 스캔보다 나은 성능을 발휘 한다.

à 검증이 필요하다 특히 반환되는 로우의 비율이라는 말은 잘 공감이 안 된다. 인덱스는 기본적으로 범위검색에 강하다 그렇다면 반환되는 비율이 높다는 것은 걸러지는 행이 별로 없다는 말로 이해된다… (경험으로 극복필요)

컬러의 카디널리티가 낮으면(인덱스 값의 다수가 같은 값일 때)인덱스의 효과가 미미하다. 인덱스를 사용했지만 테이블에서 최소 비율 이하의 데이터만 읽게 된다면 데이터베이스 엔진은 인덱스를 사용하지 않을 것이다. 게다가 인덱스는 테이블이 클 때만 사용하는 것이 좋다. 데이터베이스 엔진은 대부분 테이블이 작으면 그 데이터를 메모리에 올려놓는다. 데이터가 일단 메모리에 올라오면 무슨 작업을 하든 빠르게 데이터를 탐색한다. 여기서 “작다”는 기준은 로우의 개수, 개별 로우의 크기, 페이지에 로드되는 방식과 데이터베이스 서버의 가용 메모리 용량에 의존함을 의미한다.

à 인덱스로 사용하는 컬럼은 중복이 없으면 좋다. 그리고 메모리에 올리기 위해서는 가능한 작은 집합적으로 연산이 되야 한다.

 

Better way 12 인덱스를 단순 필터링 이상의 목적으로 사용하자

SQ문에서 검색 조건은 Where 절에 기술하는데 여기서 데이터를 빠르게 찾는다는 인덱스의 핵심 목적이 진가를 발휘하다. 느린 쿼리의 첫 번째 범인은 제대로 작성되지 않은 Where 절이다.

à 조건 검색에 사용할 데이터가 뭔지 미리 알자

컬럼을 인덱스로 만들었는지 여부는 테이블 간 조인이 얼마나 효율적으로 수행되는지에 영향을 미친다. 요컨대 조인을 이용하면 정규화된 모델의 데이터를 특정 처리에 적합한 역정규화된 형태로 변환할 수 있다.(비즈니스 모델 조인은 여러 테이블에 분산된 데이터를 결합하므로 여러 페이지에 있는 데이터를 더 많이 읽느라 디스크 탐색 대기 시간에 민감하다. 따라서 적절하게 인덱스를 만드는 것은 조인이 응답하는 시간에 큰 영향을 준다.

쿼리를 수행할 때 사용하는 조인은 한번에 두 테이블만 접근한다. SQL이 더 많은 테이블을 조인하는 경우 더욱더 많은 단계가 필요하다 먼저 두 테이블을 조인해 중간 결과 집합을 만든 후 이 결과 집합과 다음 테이블을 조인하는 식으로 처리한다.

à 조인의 경우 인덱스에 영향을 받고 조인으로 비즈니스 모델을 만들 수 있고 여러 테이블을 조인하는 경우 한번에 두 테이블만 처리가능 하므로 조인 순서에 영향을 받는다.

중접 루프 조인은 가장 기본적인 조인 알고리즘이다. 선행 쿼리가 한 테이블에서 결과 집합을 가져오고 두번째 쿼리는 선행 쿼리 결과집합의 각 로우에 대응하는 데이터를 다른 테이블에서 가져온다. 따라서 중첩 루프조인은 조인 조건에 참여하는 컬럼을 인덱스로 만들었을 때 가장 효과가 좋다. 선행 쿼리가 작은 결과 집합을 반환할 때 중첩 루프 조인은 좋은 성능을 보인다. 그렇지 않으면 옵티마이저는 다른 조인 알고리즘을 선택한다.

à NL 조인은 조인조건에 참여(ON, Select까지) 하는 컬럼을 인덱스로 만들고 선행테이블은 작은 결과 집합이 나오는 것으로 하자 결과집합이기 때문에 단순히 작은 테이블이 아니라 조건에 의해 걸리진 작은 결과 집합이다.

해시 조인은 참여하는 한쪽 테이블데이터를 해시 테이블로 만든 후 다른 쪽 테이블의 각 로우를 매우 빠르게 탐색할 수 있다. 해시 조인은 해시 테이블을 사용하므로 조인되는 컬럼을 인덱스로 만들 필요가 없다 해시 조인의 성능을 향상할 수 있는 인덱스는 Where와 ON 절에 사용되는 컬럼에 대한 인덱스다 현실적으로 해시 조인의 성능은 수평적(좀 더 적은 로우) 수직적(좀 더 적은 컬럼)으로 해시 테이블을 줄이면 성능이 좋다.

à 해시조인의 인덱스는 Select 절 컬럼보다는 On이나 Where절에 우선해서 만들고 선행 테이블의 결과 집합이 적으면 메모리에서 처리가 가능해 성능이 좋고 대량의 데이터를 처리할 때 알고리즘 복잡도가 O(1)이기 때문에 선호된다.

소트머지 조인은 조인 조건에 따라 두 테이블을 각각 정렬한 후 지퍼처럼 정렬된 두 항목을 결합하는 식으로 수행된다. 소트머지 조인은 테이블의 조인 순서가 별 의미가 없고 성능에 미치는 영향이 작다 동시에 두 테이블에 접근한다고 보면 된다. 일단 데이터가 정렬되면 소트머지 조인은 좋은 성능을 발휘하지만 양쪽 데이터를 정렬하는 비용이 크기 때문에 잘 사용하지 않는다. 반면에 해시조인은 한쪽만 미리 처리하면 되므로 양이 많으면 해시조인이 훨씬 낫다.

à 정렬된 상태와 범위 검색을 할 수 있는 컬럼의 인덱스가 필요하다 다른 조인과 가장 큰 차이는 조인 순서가 크게 영향이 없다는 부분이다. 해시조인이 부담이 될 때 고려해 볼 수 있겠다.

 

Better way 46 실행 계획의 작동 원리를 이해하자

SQL 데이터베이스를 사용하는 사람이라면 누구나 SQL 쿼리를 최적화하거나 특히 인덱스나 모델 설계에서 필요한 스키마 변경을 수행 할 수 있도록 실행 계획을 읽는 방법과 실행 계획의 의미를 이해해야 한다.

데이터를 효율적인 방식으로 가져오려고 물리적인 단계를 기술하는 단조로운 작업에서 개발자를 해방하는 것이 SQL의 목표라는 점을 일깨우고 싶다. 즉 얻으려는 데이터를 선언적으로 기술하고 데이터를 가져오는 최상의 방법은 옵티마이저에게 일임하는 것이다. 실행 계획과 물리적인 구현 내용을 설명할 때 SQL이 제공하는 추상화는 배제할 것이다.  

à 어렵게 말한 것 같다 내가 이해하기로는 모델링과 같은 추상화 적인 부분과 DBMS가 해석하는 영역은 구분되어 있다는 말인 것 같다

컴퓨터이기 때문에 작업을 수행하는 방식이 완전히 다를 것이라고 가정하지만 그렇지 않다 동일한 작업을 처리할 때 거치는 물리적인 단계는 사람이 수행하는 것과 다르지 않다 결론적으로 실행 계획을 읽을 때 쿼리에 대해 데이터베이스 엔진이 수행하는 물리적인 단계를 파악하는 것이 좋다

à 물리적인 처리 단계를 이해하면 실행계획과 쿼리를 보고 내부적으로 어떻게 처리하는 지 알 수 있다는 말   즉 디버깅 같은 과정을 하지 않아도 쿼리가 어떻게 실행되는지 해석하는데 도움이 되는 것

인덱스 시스템은 여러분이 작성할 쿼리의 종류에 매우 크게 의존한다는 점이다. (관련 예제 설명은 인덱스를 생성할 때 컬럼의 순서나 INCLUDE 절로 Lookup을 제거할 수 있다는 것을 설명한다)

à 좋은 인덱스 생성은 좋은 모델 혹은 좋은 설계부터 시작된다. (책의 첫 인덱스는 클러스터 인덱스로 볼 수 있고 뒷장의 인덱스는 비클러스터드 인덱스로 볼 수 있고 룩업이라는 것은 비클러스터드 인덱스에서 페이지 번호를 보고 실제 책에서 내용을 찾는 것을 말한다 이런 DBMS의 물리적인 동작을 머릿속에서 그릴 수 있다

데이블을 스캔하는 실행 계획을 보는데 인덱스가 있음에도 실행 계획에 사용되지 않는다면 어떤 이유인지 찾고 분석해서 개선하거나 수정할 수 있어야 한다. (인덱스는 만능도 아니고 비용이다)

à 실행 계획을 보고 인덱스 사용여부를 파악하라는 말

데이터베이스 엔진은 쿼리를 수행하는 더 나은 방법을 찾고 이에 맞게 실행 계획을 다시 세울 정도로 똑똑하게 처리했다. 이 특성은 데이터베이스 엔진 자체가 사용자가 질의한 쿼리로 제한된다는 점을 강조하는 것이다. 잘 작성되지 않은 쿼리를 보낸다면 데이터베이스 엔진도 어쩔 수 없이 나쁜 실행 계획을 생성할 것이다.

à 쿼리작성과 모델링을 잘하자

물리적인 동작을 처리하는 순서가 꽤 많다. 하지만 어떤 순서로 처리해야 좀 더 효율적인지는 데이터 분포에 의존한다. 따라서 매개변수화 된 쿼리(저장 프로시저)는 특정 값에서 월등한 성능으로 수행되지만 다른 값에서는 현격히 느리게 수행될 수 있다. 데이터베이스 엔진은 매개변수화 된 쿼리에 대응하는 실행 계획을 저장해 놓는다.

à Ad-Hoc 쿼리와 차이점 저장 프로시저는 실행 계획을 캐싱한다 라는 의미로 보면 될 것 같다 즉 저장 프로시저는 캐싱된 실행계획을 사용하지만 그 데이터 분포가 변하는 것에 캐싱된 정보로 인해 잘 못 판단할 수 있다는 말이다. 즉 노후화된 시스템은 인덱스 통계정보를 갱신하거나 인덱스를 수정해서 DBMS에서 다시 실행계획을 캐싱하도록 검토하고 확인할 필요가 있다. (유지보수하면서 나올 문제를 말하는 것 같은데.. 다만 SP를 만들면서 이와 같은 경우를 예측할 수 있을까?? 일단 경험을 쌓자)

실행계획의 단계를 보고 효율적인지 판단한다. 효율성은 데이터 분포의 영향을 받는다 결론적으로 나쁜 연산은 없다. 다만 쿼리에 적합한 연산이 무엇인지 분석한다.

à 실행계획을 볼 줄 알자

좋은 실행 계획을 얻으려면 한 쿼리에 국한되지 않는 인덱스를 추가한다. 데이터베이스의 전반적인 가용성을 고려해 추가한 인덱스가 가능한 많은 쿼리에 적용되는지 확인하고 저장프로시저의 경우 시간이 흘러도 최적화를 잘하는지 확인하자 (DBMS에서 재컴파일 옵션이 어떻게 동작하는지도 확인해 보자)

à 인덱스 통계(분포와 관련)를 주기적으로 갱신해 주고 필요하면 기존 캐싱을 지우고 실행계획을 다시 생성하도록 하자