1. 개요
인덱스는 데이터베이스 쿼리 성능을 논의할 때 중요한 부분이다. 데이터베이스 성능 튜닝의 핵심은 디스크 I/O를 얼마나 줄이는가에 있다. 디스크 읽기 방식에는 랜덤 I/O와 순차 I/O가 있다. 랜덤 I/O와 순차 I/O는 모두 하드디스크 드라이브의 플래터를 회전시켜 데이터가 저장된 위치로 디스크 헤더를 이동시켜 데이터를 읽는 방식이다. 순차 I/O는 한 번의 시스템 콜로 여러 페이지를 기록하지만, 랜덤 I/O는 각 페이지마다 시스템 콜을 요구한다. 이러한 차이로 인해 랜덤 I/O의 작업 부하가 훨씬 크다.
인덱스 레인지 스캔은 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 큰 테이블에서 대부분의 레코드를 읽는 경우 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하는 것이 더 효율적일 수 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빠르게 많은 레코드를 읽어올 수 있기 때문이며, 이러한 접근 방식은 일반적인 OLTP 환경보다는 데이터 웨어하우스나 통계 작업에서 더 자주 사용된다.
2. 인덱스
DBMS에서 인덱스는 데이터베이스 테이블의 검색 효율성을 높이기 위해 사용된다. 인덱스는 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 저장하여, 데이터의 검색 속도를 향상시킨다. 인덱스의 구조는 SortedList와 유사하게, 저장되는 컬럼의 값을 기준으로 항상 정렬된 상태를 유지한다. 반면, 데이터 파일 자체는 ArrayList처럼 저장된 순서대로 정렬 없이 저장된다.
인덱스는 데이터 저장(CUD: Create, Update, Delete) 성능을 일정 부분 희생하면서도 읽기(Read) 속도를 크게 향상시킨다. 인덱스를 역할별로 구분하면, 프라이머리 키(Primary Key) 인덱스와 보조키(Secondary Key) 인덱스로 나뉜다. 프라이머리 키는 레코드를 고유하게 식별하는 칼럼의 값으로 구성된 인덱스이며, 프라이머리 키를 제외한 모든 인덱스는 세컨더리 인덱스로 분류된다.
데이터 저장 알고리즘에 따라 인덱스는 B-Tree 인덱스와 해시 인덱스로 구분된다. B-Tree 인덱스는 칼럼의 원래 값을 변형하지 않고 인덱싱하는 가장 일반적인 인덱스 알고리즘이다. 반면, 해시 인덱스는 칼럼의 값으로부터 해시 값을 계산하여 인덱싱하는 알고리즘으로, 특히 빠른 검색을 지원한다.
데이터 중복 허용 여부에 따라 인덱스는 유니크 인덱스(Unique Index)와 유니크하지 않은 인덱스로 나뉜다. 유니크 인덱스는 중복된 값을 허용하지 않으며, 각 값은 고유해야 한다.
인덱스를 구축하는 이유는 빠른 데이터 검색을 위함이다. 특히 InnoDB에서는 인덱스 없이는 많은 레코드에 잠금이 걸리기 때문에 인덱스 설계의 중요성이 강조된다. 올바른 인덱스 설계는 데이터 검색 속도를 향상시키고, 전체 데이터베이스 시스템의 성능에 긍정적인 영향을 미친다.
3. B-Tree
B-Tree는 데이터베이스 인덱스 구조의 핵심이며, 칼럼의 원래 값을 변형하지 않고 항상 정렬된 상태로 유지한다. 이 구조는 최상위에 위치한 루트 노드, 가장 하위에 있는 리프 노드, 그리고 중간에 위치한 브랜치 노드로 구성된다. B-Tree의 리프 노드는 실제 데이터 레코드의 위치를 가리키는 주소 정보를 담고 있어 데이터 검색에 핵심적인 역할을 한다.
B-Tree에 데이터를 저장할 때는 키 값을 기반으로 적절한 위치를 찾아야 한다. 적절한 위치를 찾은 후에는 해당 키 값과 레코드의 주소 정보를 리프 노드에 저장한다. 리프 노드가 꽉 차게 되면 분리 작업이 필요하며, 이는 상위 노드로의 추가 연산을 필요로 해 쓰기 비용이 증가한다.
데이터 삭제 시에는 해당 키 값이 저장된 리프 노드를 찾아 삭제 마크만 하면 된다. 삭제 마킹된 공간은 방치하거나 재활용할 수 있다. 키 값 변경 작업은 기존 키 값을 삭제한 후 새로운 키 값을 다시 추가하는 과정으로 이루어진다.
InnoDB와 MyISAM 테이블은 인덱스 처리 방식에서 차이를 보인다. MyISAM 테이블의 세컨더리 인덱스는 물리적 주소를 사용하는 반면, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하여 논리적 주소를 활용한다.
4. B-Tree 인덱스 사용에 영향을 미치는 요소
MySQL에서 B-Tree 인덱스는 페이지 단위로 관리되며, 그 구조는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. 인덱스 키 값의 크기가 커질수록, 인덱스 페이지가 담을 수 있는 키 값의 개수가 줄어들고, 이는 디스크 읽기 작업의 증가로 이어진다. 결과적으로, 인덱스 키 값의 크기가 크면 검색 성능이 저하되고, 전체적인 인덱스 크기의 증가로 메모리 효율성이 떨어진다.
B-Tree 인덱스의 깊이는 성능에 중요한 요소지만, 직접적으로 제어할 수는 없다. 인덱스 키 값이 크면 B-Tree의 깊이가 깊어지고, 동일한 레코드 건수라도 더 많은 디스크 읽기가 필요하게 된다.
선택도(기수성)는 인덱스에서 중요한 개념으로, 전체 인덱스 키 값 중 유니크한 값의 수를 의미한다. 예를 들어, 전체 인덱스 키 값 100개 중 유니크한 값이 10개라면 기수성은 10이 된다. 인덱스 키 값 중 중복된 값이 많아질수록 기수성은 낮아지고, 선택도 역시 떨어진다. 인덱스의 효율성은 선택도가 높을수록 증가한다. 즉, 유니크한 값의 비율이 높을수록 검색 대상이 줄어들어 처리 속도가 빨라진다.
인덱스를 활용하는 쿼리에서 읽어야 할 레코드의 건수가 전체 테이블 레코드의 약 20~25%를 넘어서면, 인덱스를 이용하는 것보다 테이블 전체를 직접 읽고 필요한 레코드만 필터링하는 방식이 더 효율적일 수 있다. 이는 인덱스를 통한 검색이 전체 데이터의 상당 부분을 포함할 때, 인덱스를 거치는 오버헤드가 오히려 성능 저하를 가져올 수 있기 때문이다. 따라서 쿼리 최적화를 위해서는 인덱스의 구조뿐만 아니라 데이터의 분포와 쿼리의 특성을 고려해야 한다.
5. 스캔 종류
데이터베이스에서 인덱스를 활용하는 방식은 여러 가지가 있으며, 각각의 방식은 데이터를 읽는 방법과 효율성에 차이를 보인다. 대표적인 방식으로는 인덱스 레인지 스캔, 인덱스 풀 스캔, 루스 인덱스 스캔, 그리고 인덱스 스킵 스캔이 있다.
1. 인덱스 레인지 스캔
인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정되었을 때 사용되는 방식이다. 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져오며, 이는 인덱스 자체의 정렬 특성 때문이다. 인덱스의 리프 노드에서 조건에 맞는 데이터를 찾으면 해당 레코드를 데이터 파일에서 읽어와야 한다. 이 때, 레코드 한 건 당 랜덤 IO가 발생하기 때문에, 데이터 레코드를 읽는 데 비용이 많이 든다. 인덱스를 통해 읽어야 할 데이터 레코드가 전체의 20~25%를 넘으면 인덱스를 통한 읽기보다는 테이블을 직접 읽는 것이 더 효율적이다.
2. 인덱스 풀 스캔
인덱스 풀 스캔은 인덱스의 처음부터 끝까지 모두 읽는 방식이다. 예를 들어, 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우에 사용된다. 인덱스의 전체 크기는 테이블 자체의 크기보다 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다 적은 디스크 IO로 쿼리를 처리할 수 있다.
3. 루스 인덱스 스캔
루스 인덱스 스캔은 인덱스를 듬성듬성하게 읽는 방식이다. 루스 인덱스 스캔은 인덱스 레인지 스캔과 유사하지만, 중간에 필요치 않은 인덱스 키 값을 무시하고 다음 레코드로 넘어가는 형태로 처리한다. 이 방식은 인덱스에서 WHERE 조건을 만족하는 범위 전체를 스캔할 필요가 없다는 점에서 효율적이다.
4. 인덱스 스킵 스캔
인덱스 스킵 스캔은 MySQL 8.0 버전부터 도입된 기능으로, (A, B)와 같이 컬럼 조합으로 이루어진 인덱스에서 B 조건만 사용해도 최적화를 할 수 있다. 이 방식의 단점은 WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 한다는 것이다. 만약 유니크한 값의 개수가 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 찾는 데 많은 작업이 필요하며, 성능이 오히려 느려질 수 있다. 또한, 쿼
리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야 한다(커버링인덱스) 그렇지 않으면 풀 테이블 스캔을 하게 된다.
이렇게 각각의 인덱스 활용 방식은 데이터베이스 성능과 쿼리 최적화에 중요한 역할을 한다. 올바른 인덱스 전략을 통해 데이터베이스의 효율성을 높이고 비용을 절감할 수 있다.
6. 다중 컬럼 인덱스
두 개 이상의 칼럼으로 구성된 다중 칼럼 인덱스(multi-column index)는 데이터베이스에서 효율적인 데이터 검색과 정렬에 매우 중요한 역할을 한다. 이러한 인덱스에서 각 칼럼은 서로 의존적인 관계에 있으며, 이로 인해 인덱스 내에서 각 칼럼의 위치가 매우 중요해진다.
예를 들어, 첫 번째 칼럼과 두 번째 칼럼으로 구성된 인덱스가 있다고 가정할 때, 두 번째 칼럼의 정렬은 첫 번째 칼럼의 값이 같은 레코드 내에서만 의미를 가진다. 즉, 첫 번째 칼럼의 값이 변하면 두 번째 칼럼의 정렬 순서도 다시 시작된다. 이러한 특성 때문에 다중 칼럼 인덱스를 설계할 때는 각 칼럼의 순서를 신중하게 결정해야 한다.
다중 칼럼 인덱스의 효율성은 쿼리에서 사용되는 칼럼의 순서와 밀접하게 관련되어 있다. 쿼리가 인덱스의 첫 번째 칼럼을 사용할 때 가장 효율적이다. 하지만 인덱스의 중간 칼럼이나 마지막 칼럼만을 사용하는 쿼리는 인덱스의 이점을 충분히 활용하지 못할 수 있다.
따라서, 다중 칼럼 인덱스를 만들 때는 가장 자주 사용되고 중요한 칼럼을 인덱스의 앞부분에 배치하는 것이 좋다. 이렇게 하면 데이터베이스는 쿼리 실행 시 인덱스를 더 효율적으로 활용하여 빠른 검색 속도와 정확한 결과를 제공할 수 있다.
다중 칼럼 인덱스는 데이터베이스 성능 최적화에 있어 필수적인 요소이며, 인덱스 설계는 데이터베이스의 전반적인 성능에 큰 영향을 미친다. 따라서, 다중 칼럼 인덱스를 설계할 때는 해당 인덱스가 사용될 쿼리들을 면밀히 분석하고, 각 칼럼의 순서와 조합을 신중하게 결정하는 것이 중요하다.
7. B-Tree 인덱스의 정렬 및 스캔 방향
인덱스를 생성할 때 각 칼럼의 정렬 순서를 오름차순 또는 내림차순으로 설정하는 것은 데이터베이스 성능 최적화에 중요한 요소이다. 인덱스 생성 시 정렬 순서는 쿼리가 해당 인덱스를 사용할 때 읽는 방향과 연관되어, 오름차순 또는 내림차순 정렬 효과를 얻는 데 기여한다. MySQL 옵티마이저는 이러한 인덱스의 읽기 방향을 전환하여 쿼리의 실행 계획을 최적화한다.
특히, InnoDB에서는 인덱스 역순 스캔이 정순 스캔에 비해 느리다는 점을 고려해야 한다. 이는 페이지 잠금이 인덱스 정순 스캔에 보다 적합한 구조로 설계되어 있기 때문이다. 페이지 내에서 인덱스 레코드는 단방향으로만 연결된 구조로 되어 있으며, 이는 역순 스캔 시 추가적인 처리가 필요함을 의미한다.
따라서 쿼리에서 자주 사용되는 정렬 순서에 맞추어 인덱스를 생성하는 것이 중요하다. 이는 잠금 병목 현상을 줄이는 데 도움이 될 뿐만 아니라, 전반적인 쿼리 성능 향상에도 기여한다. 즉, 자주 사용되는 데이터의 액세스 패턴과 정렬 순서를 분석하여, 이에 맞는 인덱스 구조를 설계하는 것이 데이터베이스 성능 최적화의 핵심 전략 중 하나이다.
인덱스 설계는 데이터베이스 성능에 직접적인 영향을 미치므로, 쿼리 패턴과 데이터의 특성을 면밀히 분석하고 이에 기반한 인덱스 전략을 수립하는 것이 필수적이다. 이를 통해 데이터베이스의 응답 시간 단축 및 처리 능력 향상을 기대할 수 있다.
8. B-Tree 인덱스의 가용성과 효율성
다중 칼럼 B-Tree 인덱스의 활용은 칼럼의 순서와 각 칼럼에 사용된 조건의 유형에 따라 크게 달라진다. 인덱스에서 칼럼의 순서가 중요한 이유는 B-Tree 인덱스의 특성상 왼쪽 값에 기준해서 오른쪽 값이 정렬되어 있기 때문이다. 이는 인덱스의 활용성과 직결되며, 효율적인 쿼리 작성에 핵심적인 요소가 된다.
조건의 유형에 따라서도 인덱스의 활용도가 달라진다. 동등 비교(=)를 사용하는 경우와 범위 조건(>, <, BETWEEN 등)을 사용하는 경우, 인덱스를 활용하는 방식이 달라지며, 이는 쿼리의 성능에 직접적인 영향을 미친다. 일반적으로 작업 범위를 결정하는 조건이 많으면 많을수록 쿼리 처리 성능이 높아진다. 그러나 단순히 체크 조건의 수가 많다고 해서 성능이 항상 향상되는 것은 아니다.
특히, B-Tree 인덱스에서는 일부 조건들이 작업 범위 결정에 활용될 수 없다. 이러한 조건들은 다음과 같다:
NOT-EQUAL(<>)로 비교된 경우LIKE '%??'과 같이 앞부분이 아닌 뒷부분이 일치하는 형태로 문자열 패턴이 비교된 경우- 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
NOT-DETERMINISTIC속성의 스토어드 함수가 비교 조건에 사용된 경우- 데이터 타입이 서로 다른 경우의 비교
- 문자열 데이터 타입의 콜레이션이 다른 경우
이러한 조건들은 인덱스를 효과적으로 활용하는 데 제한을 주며, 쿼리 최적화를 위해서는 이러한 인덱스의 제약사항을 이해하고 적절히 대응하는 것이 중요하다. 따라서 인덱스를 활용한 쿼리를 작성할 때 이러한 사항들을 고려하여 설계하면, 보다 효율적인 데이터 검색과 처리가 가능하다.
9. 클러스터링 인덱스
클러스터링 인덱스는 테이블 내 레코드를 프라이머리 키의 값에 따라 물리적으로 정렬하여 저장하는 방식이다. 이 방식에서 프라이머리 키의 값은 레코드가 저장되는 위치를 결정하며, 이러한 특성으로 인해 프라이머리 키의 선택은 매우 중요하다. 프라이머리 키 값에 의존하는 클러스터링 인덱스는 사실상 인덱스 알고리즘이라기보다는 테이블 레코드의 저장 방식에 가깝다. 그래서 클러스터링 인덱스와 클러스터링 테이블은 동의어로 사용되기도 한다.
프라이머리 키가 없는 경우, InnoDB 스토리지 엔진은 프라이머리 키를 대체할 수 있는 칼럼을 자동으로 선택한다. 프라이머리 키나 유니크 인덱스가 없는 테이블은 의미 없는 값으로 클러스터링되며, 이는 특별한 이점을 제공하지 않는다. 클러스터링 인덱스는 테이블당 하나만 존재할 수 있는 중요한 혜택이므로, 프라이머리 키를 적절히 설정하는 것이 필요하다.
클러스터링 인덱스의 영향은 세컨더리 인덱스에도 미친다. InnoDB 테이블의 모든 세컨더리 인덱스는 레코드가 저장된 주소 대신 프라이머리 키 값을 저장한다. 이는 프라이머리 키의 장점이 세컨더리 인덱스에도 효과적으로 전달되는 구조이며, 성능 저하에 대한 우려는 크게 하지 않아도 된다.
클러스터링 인덱스의 장점은 빠른 읽기 속도에 있으며, 반면 쓰기 속도는 다소 느려질 수 있다. 일반적으로 온라인 트랜잭션 환경에서는 읽기 작업의 비율이 높기 때문에, 읽기 성능을 최적화하는 것이 중요하다.
