데이터베이스 퍼포먼스 가이드

SQL이 탄생한 이후로 SQL 성능 문제는 계속 해서 발생해왔다. 이는 수 년간의 데이터베이스 경험이 있는 시니어 개발자 역시 피해갈 수 없다. 왜 이런 문제가 발생하는 걸까. 그 이유는 SQL의 선언적(Declarative)명령이라는 특징을 가지고 있기 때문이다. 이러한 특징 때문에 우리는 질의를 하기 위해서 단지 우리가 원하는 것이 “무엇" 인지만 고민하면 되었고, SQL이 지금 까지 살아남을 수 있는 이유가 바로 이러한 단순함 때문 이였다. 하지만 이러한 추상화는 성능과 관련하여 한계를 도달하게 만들었다. SQL 작성자는 데이터베이스가 명령문을 실행하는 방법을 신경 쓸 필요가 없다. 이는 작성자가 느린 실행(Slow Query)에 대한 책임을 지지 않는 다는 것을 의미한다. 하지만 데이터베이스를 사용한다면 분명하게 이에 대해 약간은 알고 있어야 한다.

이 때 성능을 위해서, 개발자가 배워야 하는 유일한 것은 “인덱싱” 이다. 데이터에비스 인덱싱은 분명히 개발자의 작업이다. 적절한 인덱싱을 위한 가장 중요한 정보들은 스토리지 시스템구성 이나 하드웨어 설정이 아니라, 도메인 지식이기 때문이다. 아무리 훌륭한 외부 데이터베이스 관리자(DBA) 혹은 컨설턴트가 오더라도 도메인에 대한 이해와 정보를 수집하기 위해서는 상당한 시간이 필요하다. 도메인 지식을 잘 알고 있는 내부 개발자 만이 적절한 인덱스를 가장 빠르게 생성 할 수 있다.

인덱스의 주요 목적은 바로 “정렬된 데이터를 제공”하는 것이다. 하지만 정렬된 데이터에서 insert 와 같은 명령은 새로운 항목을 만들기 위한 공간을 확보하기 위해 기존의 데이터들을 이동해야 하는 문제가 생긴다. 많은 양의 데이터를 이동하는 것은 시간이 많이 걸리기 때문에, insert 명령어는 느려질 수 밖에 없다. 이 문제에 대한 해결책은 메모리 내부의 “물리적 순서”와 독립적인 “논리적 순서”를 만드는 것이다.

이를 이해하기 위해서는 이중 연결 리스트(double linked list)에 대한 이해가 필요하다. 이 데이터 구조는 이전 노드와 다음 노드를 참조하는 포인터를 가지고 있다. 이러한 자료구조를 통해서 데이터베이스는 필요에 따라 앞뒤로 접근하는 것이 가능하다. 따라서 많은 양의 데이터를 이동하지 않고도 삽입(insert) 것이 가능해진다. 그저 삽입하려는 데이터의 이웃 노드의 “일부 포인터만 변경하면 되기 때문”이다.

일반적으로 데이터베이스의 논리적인 순서는 doubly linked list 로 만들어진다. 모든 노도는 마치 체인과 같은 두개의 인접항목과 연결되어 있다. 이중 연결 리스트를 통해서 새로운 노드를 삽입해야 할 시에는 두 개의 기존 노드 사이에 연결된 링크를 업데이트 하여 새 노드를 삽입하기만 하면 된다. 이 리스트는 논리적인 순서를 유지하기 때문에 물리적인 위치에 대해서는 신경 쓸 필요가 없다.

데이터베이스는 이중 연결 리스트를 이용해서 각각의 인덱스 리프 노드(Index Leaf Node)들을 연결한다. 리프 노드들은 데이터베이스의 가장 작은 단위인 block 혹은 page 에 저장되어 진다. 모든 인덱스 블럭들은 수 킬로바이트로 모두 같은 크기를 가진다. 데이터베이스는 블럭 안에 인덱스 리프 노드들을 저장하고, 해당 블럭 마다 가능한 많은 인덱스 항목을 저장하려 합니다. 이러한 것들은 인덱스가 “각각의 리프 노드 안의 인덱스 요소들” 과 “이중 연결 리스트를 사용하는 다른 노드들 사이의 리프 노드” 로 구분되어 관리 되고 있음을 알 수 있다.

위 그림은 인덱스 리프 노드(논리적 순서)와 테이블 데이터(물리적 데이터)간의 연결을 보여 줍니다. 각가의 인덱스 항목들은 인덱싱 된 컬럼(column2 혹은 인덱스 키)과 이에 상응하는 물리적인 테이블을 참조하는 테이블 열에 대한 아이디로 구성되어 있습니다. 이 때, 물리적인 테이블은 힙(Heap) 구조로 저장되며, 이는 정렬되지 않음을 의미한다. 그림에서 처럼 테이블은 블럭 간에 어떠한 관계나 연결도 없음을 확인 할 수 있다.

인덱스 리프 노드는 임의의 순서대로 저장된다. 디스크의 위치 즉 물리적인 위치는 인덱스 정렬을 따르는 논리적 위치와 일치하지 않는다. 이것은 마치 페이지가 뒤섞인 전화번호부와 같은데, 가령 “김디비”를 검색했지만 처음으로 펼친 디렉토리가 “박코딩"과 같은 이후의 디렉토리라면 “김디비”의 접근은 어려워진다. 데이터베이스는 뒤섞인 페이지 중에서 원하는 항목을 빠르게 찾기 위해서 “균형 검색 트리(a balanced search tree)” 즉, B-tree가 필요하다.

위의 그림은 30개의 항목이 있는(우측 축소 그림) 예제 인덱스를 보여준다. 이중 연결 리스트는 리프 노드들 사이의 논리적 순서를 만들어낸다. 이러한 구조는 빠른 검색을 지원할 수 있다.

확대된 그림은 분기 노드(Branch Node) 와 리프 노드들을 보여준다. 각각의 분기 노드들은 블럭에 들어 있는 리프노드들 중에 가장 큰 값에 해당된다. 예를 들어 첫번째 블럭에서는 40, 43, 46을 가지고 있고 이 중 가장 큰 리프노드는 46이기 때문에 분기 노드에 46이 포함된다. 다른 리프노드 블럭 역시 마찬가지이므로 46, 53, 57, 83이 분기 노드로 구성된다. 이러한 원리가 모든 리프노드들에 적용이 된다. 그리고 이렇게 만들어진 분기노드 역시 리프노드처럼 최대값으로 구성되는 블럭들을 계속해서 가지고, 이는 최상위의 루트 노드가 만들어질때까지 해당 절차가 반복된다. 이 때, 트리의 깊이는 모든 위치에서 동일하기 때무에 균형 탐색 트리라고 불린다. 루프 노드와 리프 노드 사이의 거리는 모든 곳에서 동일하다.

인덱스가 생기면 이 균형 트리 구조를 유지해야 하므로 insert , delete 및 update 와 같은 쓰기 작업시에 유지관리를 위한 오버헤드가 발생한다.

위 그림은 키 “57”을 검색하기 위한 과정을 나타낸다. 트리 탐색은 왼쪽 루트 노드 부터 시작한다. 각 항목은 값이 검색어(57)보다 크거나 같을 때까지 오름차순으로 처리된다. 가령 57에서 발견 된 후 83을 만날 시에 탐색을 종료한다. 트리탐색은 이러한 절차를 반복하여 리프 노드에 도달한다.

이러한 트리탐색은 정말로 효율적이고, 인덱싱이 빠를 수 있는 가장 첫번째 요소가 바로 트리탐색이다. 이는 아무리 방대한 데이터라도 거의 즉시 검색할 수 있게 만들어 준다. 뿐만 아니라 균형 트리이기 때문에 어느 위치에서도 동일한 수의 단계로 일관적인 퍼포먼스를 보장 받을 수 있다. 또한 균형 트리는 깊이의 대수가 리프노드의 수에 비해 매우 느리게 증가한다. 가령 수백만 개의 레코드가 있는 데이터라도 실제 인덱스의 깊이는 4또는 5정도일 뿐이다. 6 이상의 깊이는 거의 찾아볼 수 없다. 이러한 이유는 “로그 확장성 (Logarithmic Scalability)”이라는 원리 때문이다.

트리 탐색의 효율성에도 불구하고, 인덱스 조회가 예상한 것만큼 빠르게 작동하지 않는 경우는 여전히 있다. 이러한 모순 때문에 “인덱스 비생성”과 같은 미신을 부채질하곤 했었다. 잘못된 이해로 발생하는 이슈는 있을 수 있지만 대부분의 이러한 미신은 오해 이다. 인덱스를 사용함에도 불구하고 탐색이 느릴지 모르는 사소한 진짜 이유는 이전 섹션을 기반으로 충분히 설명할 수 있다.

느린 인덱스의 첫번째 이유는 리프 노드의 체인(Leaf Node Chain)이다. 위의 예제에서 찾고 있는 “57”은 하나가 아니다. 더 정확하게는 두개이다. 이러한 유일성을 보장받지 못하는 컬럼때문에 데이터베이스는 일치하는 항목이 더 있는지 확인하기 위해서 다음 리프 노드를 읽어야한 만다. 즉, 인덱스 조회는 트리 탐색을 수행할 뿐만 아니라, 리프 노드 체인도 따라야만 한다.

느린 인덱스의 두번째 이유는 테이블 엑세스이다. 단일 리프 노드임에도 불구하고 수백개의 히트가 발생할 수 있다. 테이블 데이터(그림 2 참고)는 일반적으로 많은 블록에 흩어져 있다. 이는 각각의 히트에 대해서 추가적인 테이블 엑세스가 있을 수 있음을 보여준다.

인덱스 조회에는 세 가지 단계가 필요한데, 이는 (1) 트리 탐색; (2) 필요 시에 리프 노드 체인 처리; (3) 테이블 데이터를 가져오기; 순이다. 트리 탐색은 접근된 블록 수에 대한 상한이 있는 유일한 단계이다. 나머지 두 단계는 많은 블록에 접근을 해야할 수 있으며, 이러한 이유로 인덱스 조회가 느려지게 된다.

“느린 인덱스” 신화의 기원은 인덱스 조회가 트리 탐색 뿐이라는 잘못된 이해에 있다. 진실을 알기 위해서는 데이터베이스에 인덱스의 사용방법을 직접 물어보는 방법이 있다. 예를 들어 Oracle 데이터베이스는 ( 다른 데이터베이스도 표현의 차이는 있으나 마찬가지이다. ) 인덱스를 조회하는데 가장 일반적인 세가지 구별되는 작업이 있다.

핵심은 INDEX RANGE SCAN 발생 시에는 잠재적으로 많은 부분을 읽을 수 있다는 것이다. 각 행에 대해 하나 이상의 테이블 접근이 필요하다면, 인덱스를 사용하더라도 쿼리가 느려질 가능성이 있다.

지금까지 인덱스의 구조를 설명하고 인덱스 성능이 떨어지는 원인을 살펴보았다. 지금 부터는 이러한 문제를 발견하고 피하는 방법에 대해 이야기할 예정이다. 첫번째로 가장 흔한 where 절을 살펴보는 것으로 시작할 것이다.

where 는 SQL문의 검색 조건을 정의하고, 이는 인덱스의 핵심 영역과 매우 밀접해 있다. 독립적인 where 자체로는 성능에 크게 영향을 미치지 않지만 데이터베이스가 인덱스의 많은 부분을 스캔해야 하기 때문에 부주의해서는 안된다. 분명 잘못 작성된 where 절은 Slow Query의 첫번째 이유가 될 수 있다. 이제부터 다양한 연산자가 인덱스 사용에 미치는 영향과 가능한 만은 연산을 포함할 수 있는 인덱스, 그리고 일반적인 안티 패턴과 더 나은 성능을 제공하는 대안을 살펴 보도록 하자.

같음(=) 연산자는 가장 사소하고 가장 자주 사용되는 SQL 이다. 성능에 영향을 미치는 인덱싱 실수는 매우 일반적이며 where 절이 하나가 아닌 여러 조건이라면 특히 이러한 실수를 발생하기 쉽다.

가장 단순하지만 가장 일반적인 where절인 기본 키 조회부터 시작해보자. 이 장의 전체 예제는 다음과 같이 정의된 테이블을 사용합니다.

데이터베이스는 테이블 생성 시에 기본 키에 대한 인덱스를 자동으로 생성한다. 즉 CREATE INDEX 문이 없더라도 EMPLOYEE_ID열에 인덱스가 있다.

이제 다음 쿼리를 통해 기본 키를 사용하여 직원을 검색해보자.

EMPLOYEE_ID 의 기본 키 제약 조건은 값 의 where 고유성(unique)을 보장 하므로 절이 여러 행과 일치하지 않는다.  데이터베이스는 인덱스 리프 노드를 따를 필요가 없으며 인덱스 트리를 순회하는 것으로 충분하다. 검증을 위해 EXPLAIN (실행 계획)문을 사용할 수 있다.

위 실행 계획은 Oracle 을 기준으로 보여주나 MySQL PostgreSQL 도 표현의 차이만 있을 뿐 다르지 않다. 집중해야 할 것은  INDEX UNIQUE SCAN인덱스 트리만 순회한다는 점이다. 인덱스의 로그 확장성을 최대한 활용하여 테이블 크기와 거의 무관하게 항목을 매우 빠르게 찾을 수 있다.

인덱스에 접근한 후 데이터베이스는 질의한 데이터의 속성 즉 (FIRST_NAME, LAST_NAME)를 물리적인 테이블 저장소에서 가져오기 위해 작업을 한 단계 더 수행해야 한다. 이 작업이 바로 TABLE ACCESS BY INDEX ROWID 로써 위의 C. 느린 인덱스 에 설명된 것처럼 성능 병목 현상이 될 수 있지만 현재 쿼리와는 무관한 일이다. INDEX UNIQUE SCAN 는 둘 이상의 항목을 가지지 않기 때문에 둘 이상의 테이블 접근을 트리거 하지 않는다. 

즉, INDEX UNIQUE SCAN 는 느린 쿼리에 영향을 미치지 않는다.

비록 데이터베이스가 자동으로 primary key를 생성하더라도, 만약 조건의 키가 여러개의 컬럼에 속한다면 여전히 수동으로 동작 할 수 있다. 이 경우에는 모든 기본 키에 대한 인덱스, 즉 Concatenated Indexes ( 순차 인덱스 ) 를 만들어야 한다. 이 순차 인덱스는 생성 시에 열의 순서가 큰 영향을 미칠 수 있기 때문에 주의해야 한다.

설명을 위해, 테이블을 추가해 보자. 다른 회사의 직원이 위 EMPLOYEE 테이블에 추가되어서 테이블의 크기가 10배로 늘어남을 가정한다. 이 때, 하나의 문제가 있는데 다른 회사의 EMPLOYEE_ID 는 기존의 테이블과 중복 된다. 즉 EMPLOYEE_ID 컬럼은 더 이상 유니크 하지 않다. 때문에, 추가 ID를 통해서 primary key 를 확장 할 필요가 있다. 이를 위해 subsidary_id 라는 컬럼명을 추가했다.

새로운 primary key 를 위해서 다음과 같은 인덱스를 정의한다.

특정 직원 한명에 대한 쿼리는 다음 처럼 employed_id 뿐만 아니라 subsidiary_id 도 고려 되어야 한다.

쿼리의 조건이 완벽한 primary key 에 맞게 사용된다면, 데이터베이스는 열 수에 언제나 INDEX UNIQUE SCAN 을 사용할 수 있다. 그러나 만약 아래와 같이 특정 직원을 검색 할 때, key 중에 하나만 사용하면 어떻게 될까?

실행 계획은 데이터베이스가 인덱스를 사용하지 않고 TABLE ACCESS FULL 을 반환한다. 결과적으로 데이터베이스는 전체 테이블을 읽어 WHERE 을 평가한다는 것을 의미한다. 이 뜻은 실행 시간은 선형적으로 늘어난다는 것이고, 데이터가 적은 소규모 개발 환경에서는 충분히 빠를지 몰라도 프로덕션에서는 심각한 성능 문제를 야기할 수 있다.

Concatenated Indexes 는 다른 인덱스와 마찬가지로 B-tree 구조로 정렬된 데이터 목록이다. 이 때, 순차 인덱스의 경우에는 첫 번째 열은 기본 정렬 기준이고, 두 번째 열은 첫 번째 열의 값이 같은 경우에만 순서를 추가적으로 정렬한다.

위 그림에서도 마찬가지로 SUBSIDARY_ID=20 은 나란하지 않음을 보여준다. 심지어 Index-Tree 에는 항목조차 찾아볼 수 없다. 따라서, 위 트리를 이용해서 쿼리를 하는 것은 불가능하다.

물론 SUBSIDARY_ID 인덱스를 독립적으로 하나 만들어줄 수도 있지만, 더 나은 방법이 있다. 이는 인덱스 열 순서를 반대로 하여 SUBSIDARY_ID 가 첫번째 위치에 있도록 하는 것이다.

두 열 모두 여전히 유니크 하며 INDEX UNIQUE SCAN 를 사용할 수 있음은 변함이 없지만, 인덱스 항목의 순서는 완전히 달라졌다. 이제 정렬 기준은 SUBSIDIARY_ID 가 되었다.

이는 데이터베이스가 SUBSIDIARY_ID 로 WHERE 문을 작성 시에 B-tree 를 사용할 수 있음을 의미한다.

이 때, SUBSIDIARY_ID 하나로는 더 이상 유니크하지 않으므로, 데이터베이스는 모든 항목을 찾기 위해 INDEX RANGE SCAN 을 사용해야만 한다.

INDEX 를 여러 개 만드는 것은 매우 우수한 성능을 제공하지만, 이러한 식으로 순서가 잘 적용된 Concatenated Indexes 는 저장공간을 절약할 뿐만 아니라, 두번 째 인덱스에 대한 유지 관리 오버헤드도 절약할 수 있다. 즉 insert, delete 및 update 성능이 향상 됨을 의미한다.

이처럼 최적의 인덱스를 정의하려면 인덱스가 작동하는 방법을 아는 것도 중요하지만 무엇보다도 어플리케이션 도메인에 대한 이해가 중요하다. 어떻게 테이블 구조를 만들고, 어떤 컬럼을 이용하는 것이 가장 바람직 한지 알 수 있는 유일한 사람은 바로 어플리케이션 개발자이다. 그들은 많은 노력 없이도 전체 응용프로그램에 대한 최적의 인덱스를 생성 할 수 있을 것이다.

이전 섹션에서는 열 순서를 변경하여 추가적인 이점을 얻는 방법을 알아보았다. 하지만 이와 같은 변경은 전체 테이블 쿼리에 사이드 이펙트를 발생 시킬수 있다. 이번 파트에서는 인덱스 변경 시 발생하는 부작용에 대해서 살펴보자.

EMPLOYEES_PK 인덱스는 오직 SUBSIDIARY_ID 와 함께 사용할 경우에만 쿼리의 퍼포먼스를 향상 시킬 수 있다. 하지만 SUBSIDIARY_ID 를 통해 검색하는 모든 쿼리는 추가적인 검색 기준 없이도 사용 가능하다. 즉, SUBSIDIARY_ID인덱스는 수 많은 where 문을 위해 적용될 수 있다는 것을 의미한다.

인덱스를 변경함으로써 사이드 이펙트가 발생한 예제를 보자. 다음 예제에서는 변경 이후 매우 느려진 전화번호 어플리케이션이다. 첫 번째 발견된 slow query는 다음과 같다.

그 실행 결과는 다음과 같다.

실행 계획은 인덱스를 사용했고, 전체 비용은 30밖에 되지 않는다. 너무 훌륭한 결과이다. 그러나 우리가 방금 변경한 인덱스를 사용하고 있는지 의심스럽다. 실제 실행 결과는 실행계획과 다르게 충분히 빠르지 않았다. 우리는 최근에 인덱스를 변경했고, 이는 문제를 일으켰다고 의심하기에 충분한 이유이다.

추가적인 분석을 위해서 실행 계획의 before & after 를 비교해보자. 이전 실행 계획을 가져오기 위해서는 인덱스 정의를 다시 수정해야 하겠지만, 대부분의 데이터베이스는 특정 쿼리에 인덱스를 사용하지 못하도록 하는 더 간단한 방법을 제공할 수 있다. 아래는 Oracle DB 힌트 예시이다.

인덱스 사용 전에는 subsidiary_id 로 인덱싱을 하지 않았을 것이기 때문에 애초에 EMPLOYEES_PK 인덱스를 사용하지 않게 쿼리 힌트로 NO_INDEX 를 주었다.

결과는 Cost 477 이 발생했고, TABLE ACCESS FULL 을 통한 전체 테이블을 읽고 처리해야 하지만 오히려 더 빨리 처리되었다. 이는 분명 이례적인 일이다. 쿼리에 해당 하는 Row 가 단 하나이긴 하지만 분명 인덱스로 조회하는 것이 더 빨라야 할 것만 같다.

이를 이해하기 위해서는 실행 계획을 각 단계별로 조금 더 살펴 봐야만 한다.

가장 처음 실행 되는 것은 INDEX RANGE SCAN 에 대한 것이다. employ.id 에 대한 조건이 포함되어 있지 않기 때문이다. 그 이후에 LAST_NAME 을 블록의 ROWID 를 통해 찾을 수 있다.

INDEX RANGE SCAN 는 subsidiary_id= 2 인 인덱스를 리프 노드 체이닝을 하면서 계속해서 모든 다른 항목을 찾게 된다. 이 때, 일치하는 행의 크기는 몇 개일수도, 수백 수천 개 일수도 있다.

다음 단계는 이 결과를 가지고 TABLE ACCESS BY INDEX ROWID 를 진행하여 모든 행을 가져 온다. 이후에 LAST_NAME 에 대한 조건을 평가할 수 있게 된다. 이 때, 평가는 subsidiary_id= 2 의 크기에 따라 성능이 천치만별이 된다. 일치하는 행이 적다면 INDEX RANGE SCAN 가 빠르지만 만약 그렇지 않다면 BLOCK으로 되어 있는 ROWID로 파일에 접근하는 것보다 다이렉트로 파일에 접근하는 TABLE ACCESS FULL 가 더 빠를 수 있다.

즉, 넓은 행을 포함하는 INDEX RANGE SCAN 는 SLOW QUERY를 발생시키는 치명적인 조회이다.

인덱스를 이용한 쿼리의 Cost는 “30”, 사용하지 않는 쿼리는 “477”이다. 하지만 Cost와는 다르게 인덱스를 사용하지 않았을 때, 더 좋은 퍼포먼스가 발생했는데 이는 어째서 일까?

일반적으로 사용 가능한 통계가 없는 경우에는 옵티마이저는 기본값을 사용하게 된다. 기본 값은 분포의 중간 값을 가지는 컬럼을 기준으로 인덱스를 했을 때, 일치하는 열을 기준으로 추정치를 결정한다. 즉 평균적으로는 INDEX RNGE SCAN 시에 40명의 일치하는 결과가 나오지만, subsidiary_id= 2 인 경우에는 1000명의 직원이 근무하고 있었고, 이는 분명히 과소평가된 것이다.

정확한 통계값을 제공함으로써, 옵티마이저는 더 정확한 결과를 수행할 수 있게 된다. 다음 결과는 인덱스를 사용했을 때의 새로운 추정치를 보여준다.

인덱스를 사용 했을 때 680 만큼의 Cost가 발생 했고 이는 FULL TABLE SCAN 비용 값인 477 보다 크다. 따라서 옵티마이저는 자동으로 FULL TABLE SCAN 를 선택하게 될 것이다.

하지만 적절한 인덱싱을 추가하는 것이 최상의 솔루션이다. LAST_NAME 에 관한 인덱스를 추가하자.

그 후 결과는 다음과 같다.

옵티마이저의 추정치에 따라 제공되는 인덱스 접근을 통해서 일치하는 오직 하나의 행을 찾을 수 있다. 그로므로 데이터베이스는 단 하나의 값에 대해서만 가져오면 된다.

즉 같은 INDEX RANGE SCAN 이지만 일치하는 행에 개수에 따라서 성능은 광범위하게 달라질 수 있다는 것이 중요하다. 또한 인덱스를 사용한다고 해서 자동으로 쿼리문이 항상 최선의 방법으로 동작하는 것은 아니라는 것을 명심하자.

LAST_NAME 의 인덱스를 통해 성능이 향상되었다. 그런데 추가적인 요청사항이 발생했다. 대소문자의 구분 없이 LAST_NAME 의 검색 결과를 얻어야 하는 상황이 발생한 것이다.

대소문자를 구분하지 않고 UPPER혹은 LOWER를 구분하는 방법은 간단하다. 아래의 예시처럼 양쪽의 문자열을 대문자 혹은 소문자로 변경하면 된다.

UPPER함수를 통해 모든 문자를 대문자로 변경하게 되면 대소문자를 구분하지 않게 검색할 수 있게 된다. 이 쿼리의 논리는 완벽하지만 실행 계획은 전혀 합리적이지 않다.

LAST_NAME 에 대한 인덱스가 있음에도 불구하고 이를 사용할 수 없다. 왜냐하면 데이터베이스 관점에서는 UPPER(LAST_NAME) 과 LAST_NAME 은 전혀 다른 것이기 때문이다.

우리 모두가 쉽게 빠질 수 있는 함정이다. 우리는 UPPER안의 LAST_NAME을 옵티마이저가 확인 할 것이라 생각한다. 하지만 옵티마이저 관점에서는 위의 쿼리는 다음처럼 본다.

UPPER 이하는 그저 블랙박스이다. 함수와 매개변수는 그것이 어떻게 연관되어 있는지 옵티마이저는 전혀 알수 없다.

해당 쿼리에 대한 인덱스를 지원하려면 실제 검색결과에 대한 인덱싱이 필요하다. 즉 UPPER(LAST_NAME) 와 같이 함수를 포함한 인덱스를 생성한다.

이러한 인덱스를 FBI(함수 기반 인덱스)라고 부른다. 열 데이터를 바로 복사하는 대신, 함수 기반의 인덱스는 먼저 함수를 실행한 후에 결과를 인덱스에 저장한다. 결과적으로 인덱스는 모두 대문자로 저장되게 된다.

결과는 아래와 같다.

이는 지금까지 설명한 것과 같이 B-Tree를 횡단하고, 리프 노드 체인을 따른다. 함수 기반 인덱스라고 특별한 작업이나 키워드는 따로 존재하지 않는다.

자 그런데, 이전 섹션에서는 일치하는 Rows 수가 1 Cost가 3이였는데 그 때와 같은 조건임에도 불구하고 실행 계획이 달리 너무 높게 나온다. 더 이상한 점은 INDEX RANGE SCAN 에서 40개의 행에 대한 필터링을 예상했는데 그 이후에 100개로 갑자기 늘어났다는 점이다. 이런게 가능한걸까?

결과적으로는 불가능한게 맞다. 이처럼 불가능한 수치를 통해 종종 통계에 문제가 있음을 확인 할 수도 있다. 이번 경우에는 데이터베이스가 새 인덱스를 생성할 때 테이블의 통계를 업데이트 하지 않으므로 인해 발생한 이슈이다. ( 오라클 기준으로는 DBMS_STATS를 통해 통계를 업데이트 할 수 있다. )

통계를 업데이트 한 이후에는 정확한 수치를 확인 할 수 있다.

이번 경우에는 어쨌든 인덱스가 적절하게 사용되었기 때문에, 통계 업데이트가 실행 성능을 향상시키진 않았지만 항상 옵티마이저의 추정치를 확인하고 필요할 때 갱신하는 것이 중요하다.

MYSQL 이나 SQL SERVER 같은 경우에는 함수 기반 인덱스를 지원하지 않지만 컬럼 자체를 계산해서 입력할 수 있게 변경함으로써 인덱스 적용이 가능하다.

가령 UPPER(last_name) 컬럼 자체를 추가해서 해당 컬럼에 인덱스를 적용하므로써 우회해서 동작가능하게 할 수 있다.

함수 기반 인덱싱은 꽤 일반적인 접근이다. 게다가 UPPER 함수처럼, A + B 와 같은 완전히 함수로 만들법한 표현식들 또한 정의할 수 있다.

이 때, 하나 중요한 예외가 있는데, 인덱스에 “현재시간”을 참조하는 것은 직, 간접적으로 인덱스 정의가 불가능하다. 예를 들면 아래와 같다.

함수 get_age 는 계산을 위해서 생일에 현재 시간 SYSDATE 을 이용하고 있다. 이를 통해 다음과 같은 SELECT 조회가 가능하다.

위 쿼리는 42세 직원들의 리스트를 반환한다. 함수 기반의 인덱스는 쿼리를 개선하기 위한 방법이지만, 위 함수 get_age 는 불가능하다. 그 이유는 바로 해당 함수의 정의가 deterministic 하지 않기 떄문이다.

이러한 이유는 생각해보면 간단하다. 새로운 행이 입력될 때마다 데이터베이스는 함수를 호출 한 후에 저장한다. 이 때, birth_of_date 의 업데이트는 전혀 이루어지지 않았는데, SYSDATE 의 결과로 매번 현재 날짜로 다시 계산 되어야 정확한 결과를 얻을 수 있다. 가령 매년 새해마다 매번 업데이트 해주어야 한다. 그런데 다음해가 되자 말자, birth_of_date 는 업데이트를 해야 한다. 하지만 인덱스를 업데이트 시키려면... birth_of_date 를 변경 해야만 한다. 인덱스만 업데이트 하는 어떠한 명령도 없다. 오로지 행의 값이 변경되어야만 한다.

함수 기반 인덱싱이 처음이라면 모든 항목을 인덱싱 하고 싶은 생각이 들수 있지만, 사실 이것은 가장 최후의 선택이 되어야만 한다. 그 이유는 모든 인덱스가 지속적인 유지 관리가 필요하기 때문이다. 특히 함수 기반 인덱스는 중복 인덱스를 생성 하기가 매우 쉬워서 특히 골치덩어리가 될 수 있다.

대 소문자를 구분하지 않는 검색은 LOWER 를 통해서도 구현 할 수 있다.

당연하게도 UPPER 로 적용한 인덱스는 적용 되지 않는다. 이 때문에 LOWER 에 대한 인덱스를 생성할 수도 있지만 유지 관리해야 할 인덱스가 늘어 날 뿐더러 기능의 중복이 발생한다. 어플리케이션 개발 시에는 항상 동일한 기능을 일관되게 사용해야 한다. 이 때문에, 특히 ORM을 사용할 때는 일관성 있는 개발이 중요하다.

Bind parameters(바인드 매개변수) 혹은 dynamic parameters(동적 매개변수) 란 데이터베이스에 데이터를 전달하는 대체 방법이다. 값을 SQL에 직접 입력하는 대신 ? 혹은 :name , @name 등의 placeholder(표시자)를 사용하여 실제 값을 제공한다.

SQL를 직접 입력하는게 나쁜 것은 아니지만, 프로그램에서 바인드 매개변수를 사용하는데는 두가지 이유가 있다.

이 때, 값이 데이터의 용량에 의존적인 경우에는 당연히 예외로 적용해야 된다.

아래 예제를 확인 해보자

위 쿼리는 소규모 자회사에 대해서는 인덱스를 통해 좋은 성능을 제공하지만, 대규모 자회사인 경우에는 인덱스 대신 TABLE ACCESS FULL 이 더 나은 퍼포먼스를 보일 수 있다.

옵티마이저는 SUBSIDIARY_ID 에 대한 히스토그램을 통해 어떤 계획을 이용할 지 결정한다. 결과적으로 행의 크기에 따라 두 가지 다른 실행 계획을 세운다.

후속 비용일 TABLE ACCESS BY INDEX ROWID 는 예상 행 수에 매우 민감하다. 때문에 많은 행을 선택하는 것은 그 만큼 비용이 높아지고 따라서 인덱스를 사용하는 것이 전체 테이블 스캔보다 훨씬 높아진다.

바인드 매개변수를 사용할 때 옵티마이저는 이러한 빈도를 결정하는 데 사용할 수 있는 구체적인 값이 없다. 구체적인 값 대신 ? 혹은 :name 등을 이용하기 때문이다. 그렇기 때문에 동일한 분포를가정하고 항상 동일한 행 수 추정치 및 비용 값을 얻는다. 결국, 옵티마이저는 볼륨에 대한 최적화 쿼리 대신 항상 동일한 실행 계획을 선택한다.

최상의 실행 계획을 얻기 위해 각 실행에 대해 모든 계획을 평가하거나, 최적화 실행 계획을 저장하고 캐시된 실행 계획을 이용하여 이를 처리하는 것은 온전히 개발자가 선택해야 한다.

개발자는 바인드 매개변수를 의도적으로 사용하여 이 딜레마를 없앨 수 있다.

다음 코드는 자바를 이용하여 바인드 매개변수를 사용하는 방법이다.

물음표(?)는 SQL표준에서 정의하는 유일한 placeholder 이다. 물음표는 왼쪽에서 오른쪽으로 순서대로 번호가 매겨진다. 하지만 값을 바인딩 하기 위해서 순서에 맞춰서 값을 입력해야 하기 때문에 추가하거나 제거할 경우에 매겨진 번호가 변경되어야 하므로, 이는 실용적이지 않다. 때문에 많은 데이터베이스에서는 바인드 매개변수의 확장 표현을 제공한다. ex) @name 혹은 :name 등

< , > , 혹은 between 와 같이 = 비교가 아닌 명령어 역시 인덱스를 사용할 수 있다. 심지어 Like 필터조차도 특정 상황에서는 범위 조건처럼 인덱스를 사용할 수 있다.

이러한 명령들을 사용하는 것은 멀티 컬럼 인덱스 내에서 컬럼의 정렬의 선택을 제한 하곤 한다. 잘못된 인덱싱으로 인해 모든 최적화가 제외될 수도 있다. 우리가 간단히 정의할 수 없는 “옳은” 컬럼 순서를 가진 쿼리 들이 존재한다.

INDEX RANGE SCAN 의 가장 큰 퍼포먼스 위험 부담은 leaf node traveral ( 리프 노드 순회 ) 이다. 그러므로 인덱스의 절대 규칙은 가능한만큼 가장 적은 인덱스 범위를 스캔하는 것을 유지하는 것이다. 우리는 스스로 인덱스가 어디서 시작되어 어디서 끝나는 지 확인하는 것으로 이러한 규칙을 유지할 수 있다.

만약 SQL문이 시작과 끝을 명시한다면 그 질문을 대답하기는 쉬울 것이다.

DATE_OF_BIRTH 인덱스는 특정한 범위를 스캔한다. 이 범위는 시작일자를 첫번째로 종료일자를 두번째로 스캔한다. 우리는 어떤 방법으로도 이 스캔 범위를 좁힐 수 는 없다.

여기에 두번째 컬럼에 대한 조건이 추가되면, 시작과 종료 조건은 어떻게 동작해야 할지 더 모호해 진다.

물론 이상적인 인덱스라면 두 컬럼을 모두 인덱싱 해야겠지만, 문제는 어떤 순서대로 해야 하는가 이다.

다음 그림은 범위 인덱스 스캔시 컬럼 순서의 영향력에 대해서 보여준다. 이 그림은 1971년 1월 1일부터 1월 9일 사이에 태어난 subsidiary = 27 에 해당하는 모든 직원들을 찾기 위해 그려졌다.

만약 위의 그림과는 다르게 인덱스 컬럼 순서를 뒤집는다면 전혀 다른 그림이 그려진다.

아래 그림은 SUBSIDIARY_ID 로 인덱스를 스캔하는 그림이다.

실제 성능 차이는 데이터와 검색 기준에 의존한다. 그 차이는 필터인 DATE_OF_BIRTH가 매우 작은 범위를 선택했다면 무시해도 될 정도이다. 하지만 데이터가 점점 범위를 넓혀간다면, 큰 퍼포먼스의 차이를 만들 것이다.

이 예제에서, 우리는 가장 많이 선택되는 컬럼이 인덱스의 가장 왼쪽에 위치해야한다는 잘못된 생각을 가질수도 있다. 가령 우리가 첫번째 컬럼만을 고려했다면, 두 조건 모두가 13개의 레코드와 일치하는 것을 볼 수 있다. 이러한 케이스에서는 오직 DATE_OF_BIRTH 또는 SUBSIDIARY_ID 만을 필터로 가지더라도 아무 문제 없다.

퍼포먼스 최적화를 위해, 가장 중요한 것은 스캔될 인덱스 범위를 아는 것이다. 대부분의 데이터베이스와 함께, 우리는 실행 계획을 볼 수 있고, 우리는 무엇을 찾아야 하는지 알아야만 한다. 아래 실행 계획은 정확하게 EMP_TEST 인덱스가 DATE_OF_BIRTH 컬럼으로 시작한다는것을 명백하게 보여준다.

INDEX RANGE SCAN 로 예측된 정보는 중요한 힌트를 준다. 이것은 where 절의 조건을 access 혹은 filter 예측으로 식별한다. 이것은 어떻게 데이터베이스가 우리에게 이 조건을 사용할 것인지에 대해 알려 준다.

DATE_OF_BIRTH 컬럼의 조건들은 오직 access 예측을 나열했다; SUBSIDIARY_ID 컬럼은 오직 필터로써만 사용되었다.

우리가 인덱스의 정의를 바꾸는 것으로 데이터베이스가 모든 조건들을 access로 예측하게 변경할 수 있다.

마침내, between 명령어를 사용할 때이다. 이것은 하나의 조건에 대해 이상, 이하를 명시할수 있게 해준다.

between 은 항상 특정한 값을 포함하고, >=혹은 <= 를 사용하는 것과 거희 유사하다.

SQL의 LIKE 명령어는 자주 예상하지 못한 퍼포먼스 장애에 원인이 되곤 하는데, 몇몇 검색 조건들이 효과적인 인덱스를 방해하기 때문이다. 특히, 검색 조건들은 잘 인덱싱 할 수 있지만 아닌 경우도 아주 많다. wild card 문자 위치는 모든 차이를 만들기도 한다. 아래는 문자 가운데 % 를 사용하는 예제이다.

LIKE 필터에서 트리 순회는 첫번째 와일드카드(%) 이전에만 사용할 수 있다. 남아있는 문자는 스캔해야 할 인덱스 범위를 좁힐 수 없는 단순한 필터일 뿐이다. 위 Like 표현식은 그러므로 두가지 의미를 가지는데: (1) 첫번째 와일드 카드 문자 이전의 접근(access) 조건; (2) 필터(filter) 조건으로써의 나머지 문자.

첫번째 와일드 카드 이전의 문자가 더 선택범위가 넓을 수록, 스캔 해야할 범위는 더 작아진다. 그것은 더 빠른 인덱스 검색으로 이어진다.

위 이미지는 LIKE 표현식에 따른 세가지 다른 결과를 보여준다. 세가지 모두 같은 열을 선택하지만, 스캔 해야할 범위는 다르고, 이는 퍼포먼스의 차이로 이어진다. 첫번째 표현식은 와일드 카드 이전에 두가지 문자를 가진다. 이 표현식은 18개의 열을 인덱스 스캔한다. 오직 이들 중 하나만 매치 되고 나머지 17개는 버리게 된다. 두번째 표현식은 더 긴 prefix 를 가지는데, 이는 스캔 해야 할 범위를 두개까지 좁힌다. 이 표현식을 이용하면 데이터베이스는 결과와 관계없는 열을 단 하나 더 조회한다. 마지막 표현식은 어떤 필터(filter) 조건도 필요로 하지 않는다. 데이터베이스는 LIKE 표현식의 접근(access) 결과를 모두 일치한 요소로써 가진다.

와일드 카드가 가장 먼저 시작하는 Like 표현식과 같은 반대인 상황 역시 발생할 수 있다. 이런 경우 LIKE 와 같은 표현식은 access 술어로써 사용 불가능하다. 만약 access 조건을 제공할 수 있는 다른 조건들이 없다면, 데이터베이스는 모든 테이블을 스캔해야 한다.

적어도 이 이론에서는 와일드 카드의 위치는 인덱스 사용에 영향을 준다. bind parameters 를 통해 검색 조건이 제공되어지는 경우에는 실제로 옵티마이저는 일반적인 실행 계획을 생성한다. 이 경우에서, 옵티마이저는 대부분의 실행이 선행 와일드카드를 가지는지의 여부에 대해 추측해야만 한다.

대부분의 데이터베이스는 bind parameter 와 함께 Like 조건을 최적화 할 때, 선행 와일드카드가 없는지를 추정한다. 하지만 이 추측은 LIKE 가 전문 검색(full-text search)을 작업할 때는 틀린 추측이다. 불행히도 전문 검색을 하기 위해 LIKE 조건을 사용할 수 있는 직접적인 방법이 없다.

위 조건은 access 로써 작동하지 않는다. bind parameter 없이 검색 조건을 명시하는 것은 가장 명백한 솔루션이지만, 최적화 오버헤드를 증가시키고 SQL injection 취약점에 노출될 수 있다. 효과적이면서 여전히 안전하고 편리한 해결책은 LIKE 조건을 의도적으로 난독화하는 것이다.