16. [SQL기초] 인덱스와 성능 튜닝

인덱스 기초

인덱스, 또는 색인이라고 하는 데이터베이스 객체는 테이블의 데이터를 빠르게 검색할 수 있도록 해주는 기술, 또는 기능을 말합니다. 데이터를 검색하는 속도를 비약적으로 향상해주는 방법이지만, 자동으로 인덱스가 생겨나고 검색 속도가 빨라지는 것은 아닙니다.

책으로 치면 색인과 같은 기능을 하는 것이 인덱스라고 생각하면 쉽습니다. 색인이 키워드가 위치한 페이지가 어디인지 알려주는 것처럼 인덱스도 찾고자 하는 조건에 맞는 행이 어느 행인지 알려주는 정보를 저장하고 있습니다.

인덱스는 쿼리문의 WHERE절에 사용하는 조건식과 밀접하게 연관되어 있습니다. WHERE절에 표시하는 조건문, 도는 제약조건이 빠르게 검색될 수 있도록 사용자가 인덱스를 구성해야 합니다.

만약 인덱스에 없는 WHERE절의 조건이 실행되면 인덱스는 제 역할을 하지 못하고, 테이블 검색 속도는 현저히 느려집니다.

테이블의 행 수가 많지 않으면 인덱스는 큰 효과를 보지 못합니다. 순차 검색을 하는 속도보다 미세하게 빠르기 때문에 인덱스가 별다른 효용성이 없게 됩니다. 반대로 몇 만행, 몇 십 만행의 데이터를 가진 테이블이라면 얘기가 달라집니다. 몇 십만행의 테이블 행을 가진 테이블을 순차 검색해서 원하는 조건에 맞는 행들을 찾는 시간은 최소 몇 초에서 몇 분이 걸리게 됩니다.

인덱스가 잘 구성된 몇십 만 행의 데이터를 가진 테이블은 몇 십 밀리세컨드, 또는 몇 백 밀리세컨드 안에 원하는 테이블 검색 결과를 얻을 수 있습니다.

데이터베이스의 성능 요소와 가장 밀접하게 연결된 데이터베이스 기능이기 때문에 테이블 생성 후 기본적인 인덱스 구성은 꼭 해야 합니다.

B트리와 B+트리

B트리는 “이진트리”라고도 합니다. 데이터를 빠르게 검색하도록 고안된 가장 널리 알려진 방식 중의 하나입니다.

크거나 작은 조건 비교 결과에 따라 나뭇가지가 퍼져 나가는 모양으로 비교 검색이 이루어지면서 뎁스(Depth)를 내려가기 때문에 이진트리라고 합니다.

테이블에 검색할 행이 10개가 있다면 평균 5번의 비교를 하면 원하는 행을 찾을 수 있습니다. 최선의 경우는 1번이겠지만, 최악의 경우는 10번일 될 것입니다.

데이터가 작기 때문에 10번의 비교를 해서 원하는 행을 찾는다고 해도 시간 차이는 크지 않습니다.

만약 테이블에 100만 개의 행이 있다면 순차적으로 비교하는 방법으로 원하는 행을 찾으려면 평균 50만 번을 비교해야 합니다. 데이터베이스의 성능이 좋다고 해도 시간이 꽤 걸릴 거라는 것 정도는 짐작이 될 것입니다.

100만 개의 행을 이진트리로 검색하면 1000번의 비교로 원하는 값을 찾을 수 있습니다. 테이블의 데이터가 작을 때는 순차비교와 이진 트리 비교는 별 차이가 없지만, 데이터가 늘어날수록 비교 속도는 기하급수적으로 차이가 납니다.

이진트리는 원하는 검색 결과를 얻기 위해 일정한 검색 회수를 보장하는 특징이 있습니다. 이진 트리는 다음과 같은 방식으로 데이터를 검색합니다. 이해를 돕기 위해 그림으로 표현되어 있지만, 이진 트리는 이런 방식으로 검색을 할 수 있도록 데이터 구조를 만들어서 빠른 검색이 가능하도록 합니다. 이것이 인덱스입니다.

B트리는 모든 모드에 테이블 행을 가리키는 포인터가 있습니다. 따라서 최선의 경우에는 1번에 원하는 행을 찾을 수 있는 장점이 있습니다.

이진 트리는 크거나 작은 비교 결과 2가지만 있습니다. 값이 같은 경우에 대한 비교는 없기 때문에 중복 값이 없는 기본키와 같은 경우에 사용이 적합합니다. 중복 데이터가 있는 열에는 이진트리로 된 인덱스를 생성할 수 없습니다.

중복 값이 있는 경우 이진트리처럼 같이 빠르고 일정한 검색 속도를 보장할 수 있는 B+트리 알고리즘으로 인덱스를 생성합니다. B+트리는 B트리의 문제점을 보완한 트리 형태 형태입니다.

B+트리는 부모 노드의 값이 자식 노드에 있을 수 있습니다. 즉, 중복이 허용됩니다.

이진트리는 작거나 큰지를 비교해서 둘 중 하나의 자식 노드로 내려가지만, B+트리는 작거나, 같거나, 크거나 3가지로 비교해서 해당되는 하위 노드로 내려갑니다. 속도 측면에서는 이진 트리에 비해 약간의 손해를 보지만 이진 트리에 뒤지지 않는 빠른 검색 속도를 냅니다.

B트리와 B+트리는 √검색 대상의 개수 (루트 값) 만큼의 검색 회수로 원하는 행을 찾습니다. 순차 검색과 비교를 하면 제곱에　반비례하는　검색시간 차이가　있습니다．

인덱스의 장단점

테이블 검색 속도를 획기적으로 올려주는 좋은 기능이지만 장점만 있는 것은 아닙니다.

인덱스는 읽기(Select), 쓰기(Insert), 수정(Update), 삭제(Delete) 속도를 향상시키지만, 지속적인 관리를 해주지 않으면 성능이 떨어지는 문제가 있어 관리적인 면에서 사람의 손을 꽤나 많이 타는 데이터베이스의 기능입니다.

장점

• 테이블의 평균 조회 속도가 비약적으로 향상됩니다.
• 빠른 처리 속도로 인해 다중 작업을 빠르게 처리할 수 있고, 시스템 리소스도 적게 먹습니다.

단점

• 인덱스의 생성과 관리를 위해 전체 데이터베이스 크기의 약 10% 만큼 추가 스토리지 공간을 필요로 합니다.
• 인덱스 생성, 수정과 같은 지속적인 유지관리 작업을 필요로 합니다.
• 인덱스를 잘못 생성하면 불필요한 스토리지만 차지하고 성능 향상이 전혀 없거나 오히려 떨어지기도 합니다.
• 수정, 삭제가 빈번한 테이블은 인덱스가 차지하는 공간히 급격하게 늘어나 스토리지 공간 부담이 생기고 속도가 저하되는 문제가 생길 수 있습니다.

인덱스가 최고의 효율을 내는 경우

• 레코드 수가 아주 많은 테이블을 쿼리할 때.
• Select 가 빈번하고 상대적으로 Insert, Update, Delete 는 적은 테이블을 쿼리할 때.
• 테이블 조인(Join)이나 조건절(Where)이 많은 쿼리를 할 때.
• 데이터 중복이 적은 컬럼(들)을 쿼리할 때.

기본키와 인덱스

테이블에 기본키를 지정하면 테이블 생성 시점에 기본키에 대한 인덱스가 자동으로 생성됩니다. 따라서 기본키를 위한 인덱스를 따로 만들 필요가 없습니다. 기본키용 인덱스는 임의로 삭제할 수도 없습니다.

여러 열을 묶어서 만든 기본키에 대한 인덱스도 자동으로 생성됩니다.

기본키는 아니지만 자동으로 인덱스가 만들어지는 열 속성으로 UNIQUE가 있습니다. 유니크로 속성이 설정된 컬럼은 빠른 중복 체크를 위해 테이블 생성 시점에 자동으로 인덱스가 만들어집니다.

인덱스의 유지와 갱신

인덱스는 빠른 Select 쿼리 처리 속도를 최우선으로 하는 기능입니다.

반대로 나머지 쿼리(Insert, Update, Delete)에 대해서는 약간의 추가 지연이 발생하게 됩니다.

약간의 추가 지연은 갱신된 테이블 컬럼 데이터에 대해 인덱스를 갱신하는 과정에서 발생하는 오버헤드입니다.

테이블의 컬럼 데이터가 갱신될 때마다 인덱스 트리를 재 생성하거나 재구성한다면 많은 부하가 발생하게 됩니다. 따라서 인덱스를 갱신할 때도 무조건 빠른 속도와 낮은 부하를 최우선으로 하는 방식을 취하게 됩니다.

새로운 데이터가 새 인덱스 노드를 생성해서 적당한 위치에 끼워넣으면 됩니다.

인덱스 트리를 한번 타면 넣을 위치를 바로 찾을 수 있으므로 과정도 단순합니다.

Update와 Delete는 조금 다른 과정을 거쳐야 합니다.

데이터가 변경되거나, 삭제되면 해당되는 위치의 인접 노드를 재구성해야 합니다. 삭제는 삭제된 위치의 인접 노드만 재구성하면 되지만, 갱신(Update)은 기존 데이터의 인덱스를 삭제하고, 변경된 데이터에 대한 인덱스를 새로 생성해야 합니다.

즉, 데이터 갱신이 이루어지면 기존 인덱스 삭제[삭제 -> 삭제된 노드 재구성] -> 새 인덱스 추가의 단계를 거쳐야 합니다.

이중 가장 많은 부하를 주는 작업은 노드를 재구성하는 작업입니다.

그래서 테이블 컬럼 데이터의 삭제, 갱신시에도 노드를 재구성하지 않는 방향으로 인덱스 유지가 되는 방법을 사용합니다.

노드의 재구성은 노드의 삭제가 원인이기 때문에 노드를 삭제하는 대신 노드에 사용하지 않는 노드라는 표시를 하고, 인덱스 트리를 탈 때 무시를 하고 통과를 시킵니다.

이렇게하면 인덱스 노드를 삭제할 필요가 없고, 재구성할 필요도 없게 됩니다.

데이터 갱신에 따른 인덱스 갱신을 할 때도 인덱스를 2번 타서 위치를 찾는 작업만 하면 됩니다.

• Insert - 갱신된 데이터에 대한 새 인덱스를 인덱스 트리에 추가
• Update - 기존 인덱스를 사용하지 않음으로 표시하고, 갱신된 데이터에 대한 인덱스를 추가.
• Delete - 삭제된 해당 데이터에 대한 인덱스를 사용하지 않음으로 표시.

인덱스의 유지와 갱신을 빠르게 할 수 있는 최고의 방법이지만 단점도 있습니다.

테이블의 데이터가 아주 빈번하게 추가, 수정, 삭제가 일어난다면, 사용하지 않는 노드들이 급격하게 늘어나게 되고, 인덱스 트리는 아주 복잡하게 엉키게 됩니다.

인덱스 트리를 검색하는 시간도 당연히 더 늘어나게 되고, 인덱스 검색 속도 저하가 발생하게 됩니다. 그리고 인덱스가 데이터베이스에서 차지하는 공간도 급격하게 늘어나게 됩니다.

이렇게 미사용 노드들이 누적될 때는 인덱스를 재구성, 또는 재생성해서 사용하지 않는 노드를 없애는 작업을 해야 합니다.

인덱스 생성

“Create index”문으로 새 인덱스를 생성할 수 있습니다. 기본키에 대한 인덱스는 자동으로 생성되기 때문에 따로 인덱스를 만들 필요가 없습니다.

기본 구문

Create index 인덱스명 on 테이블 이름(열1, 열2, 열3, …);

예제 테이블 중 제조사 테이블의 제조사 이름에 인덱스를 생성해보겠습니다.

Create index i_name on maker (name);

인덱스는 테이블 안에 객체로 저장되기 때문에 테이블에 의존적입니다. 따라서 테이블 안에서 인덱스의 이름은 고유해야 합니다. 다른 테이블에 “i_name”이라는 인덱스가 있어도 충돌이나 에러가 발생하지 않습니다.

데이터베이스에 따라서는 스키마 객체(데이터베이스 객체)로 인덱스가 관리되기도 합니다. 오라클은 전체 데이터베이스 객체로 인덱스가 생성되기 때문에 인덱스 이름이 데이터베이스 전체 안에서 중복되지 않도록 주의해야 합니다. SQL Server는 MySQL과 같이 테이블 단위로 인덱스를 관리합니다.

인덱스 삭제

사용하지 않는 인덱스는 삭제해서 불필요한 데이터베이스 공간을 해제해야 합니다. 테이블의 데이터가 큰 경우 인덱스가 차지하는 데이터베이스 공간도 그만큼 늘어납니다.

Drop index 인덱스명;

인덱스는 테이블에 연결되어 있기 때문에 테이블을 삭제하면 테이블에 연결된 인덱스는 자동으로 삭제됩니다. 일단 테이블을 삭제한 후 같은 구조의 테이블을 재 생성해도 인덱스는 남아있지 않기 때문에, 인덱스도 새로 만들어야 합니다. 앞서 만든 “i_name” 인덱스를 삭제해 보겠습니다.

Drop index i_name on maker;

오라클은 스키마 객체로 인덱스가 저장되기 때문에 인덱스 이름까지만 표시해서 인덱스를 삭제합니다.

Drop index i_name;

인덱스는 사용하는 쿼리문의 WHERE 조건식에 따라 적절한 인덱스를 생성해야 합니다. 사용하는 조건식이 달라지면 인덱스도 거기에 맞춰 변경을 해야 합니다. 인덱스는 한번 만들어 놓으면 끝나는 것이 아니라 쿼리문에 따라 그때그때 새로운 것을 만들고, 또 더 이상 사용하지 않는 인덱스는 삭제를 해서 최신 인덱스를 유지해야 합니다.

한번 생성한 인덱스는 구조를 수정할 수 없습니다. 테이블을 변경하듯이 “ALTER INDEX”와 같은 쿼리문은 존재하지 않습니다. 인덱스 구성이 변경되면 이전 인덱스를 삭제를 하고 새로 만들어야 합니다.

인덱스 검증

인덱스를 생성했으면 인덱스가 실제로 쿼리문 조건식에서 참조를 하는지 알아야 합니다. 가장 확실한 방법은 쿼리문을 실행해서 인덱스가 없을 때와 있을 때의 시간 차이를 재면 되겠지만, 테이블 행 수가 적으면 사실 인덱스가 있으나 없으나 별 차이가 나지 않습니다.

테이블에 테스트용 더미 데이터를 넣어서 검색 시간을 비교 것은 아무리 봐도 멍청한 방법입니다. 그래서 쿼리문이 실제로 인덱스를 참조하는지 확인하는 명령어가 있습니다. 쿼리문의 조건식이 인덱스를 참조하는 것을 “인덱스를 탄다”라고 합니다.

EXPLAIN 인덱스를 참조하는 쿼리문;

Explain 명령어로 쿼리문을 실행하면 참조 가능한 인덱스와 실제 참조한 인덱스를 결과표로 표시해줍니다. 참조 가능하거나 참조한 인덱스가 있으면 결과표에 인덱스의 이름이 표시가 되고, 참조한 인덱스가 없으면 NULL로 표시됩니다.

호환성

EXPLAIN은 MySQL 전용의 명령어입니다.

오라클에서는 “SET AUTOTRACE ON;” 명령 쿼리문을 실행한 후 실행 계획(Execution Plan)을 확인하고 싶은 쿼리문을 실행하면 쿼리문의 자세한 실행 정보와 어떤 인덱스를 타는지를 확인할 수 있습니다.

실행 계획은 쿼리문이 실제로 어떻게 컴파일되어 어떤 데이터베이스 객체에 접근하고, 어떤 순서로 실행하는지에 대한 정보를 말합니다.

SQL Server는 “SET showplan_all on;” 명령문을 실행한 후 실행 계획을 출력해보고 싶은 쿼리문을 실행하면 쿼리문 실행 결과 대신 실행 계획 내용이 출력됩니다. 실행 계획에서 인덱스를 타는지 확인할 수 있습니다.

MySQL 워크벤치로 인덱스 생성

MySQL 워크벤치의 왼쪽 객체 브라우저에서 컬럼의 인덱스를 생성할 수 있습니다. 커맨드라인으로 인덱스를 생성하는 것에 익숙하지 않으면 워크벤치를 사용하는 것을 추천합니다.