정말 많이 들어봤습니다. 인덱스(Index). 하지만 실제 프로젝트에서 인덱스를 만들어본 적은 없습니다. 물론 PK를 설정하면 자동으로 인덱스가 들어가는 경우를 제외하고요. 실제로 인덱스를 이용해 데이터를 조회할 만큼 데이터가 많이 존재하지 않은 부분만 개발을 했었기 때문입니다. 그렇다고 중요한 사실을 모르고 지나가면 안될 것 같아서 공부 겸 내가 아는 지식이 맞는지 확일할 겸 인덱스(Index)에 관해서 작성해보도록 하겠습니다.
올바르지 못한 내용이 존재할 수 있습니다. 틀린 점이 보인다면 댓글로 작성 부탁드리겠습니다.
"인덱스(Index)란 무엇일까?"
인덱스(index)라는 것은 특정 컬럼(Column)의 값을 빠르게 검색할 수 있도록 도와주는 자료구조입니다. 즉, 책의 목차처럼 데이터가 저장된 위치를 미리 알려줌으로써 원하는 데이터를 빠르게 찾을 수 있게 도와줍니다.
"적용하는 방법"
데이터베이스를 공부하면 익히 볼 수 있는 salaries 테이블을 보도록 하겠습니다.
CREATE TABLE `salaries` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
`is_bonus` tinyint(1) unsigned zerofill DEFAULT NULL,
`group_no` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;위와 같이 작성된 테이블에 인덱스를 삽입해보겠습니다.
CREATE INDEX IDX_SALARIES_INCREASE ON salaries
(is_bonus, from_date, group_no);
CREATE INDEX IDX_SALARIES_DECREASE ON salaries
(group_no, from_date, is_bonus);이처럼 인덱스를 생성할 수 있습니다. is_bonus, from_date, group_no에 차례대로 인덱스를 설정했습니다. 인덱스를 다른 순서로 삽입해주었는데요? 이러한 이유는 아래에 나옵니다.
위 테이블과 인덱스 삽입 그리고 블로그에 작성된 정보는 해당 블로그를 참조했습니다. 더욱 자세히 알고 싶으신 분은 찾아가서 보시면 될 것입니다.여기까지는 인덱스 삽입하는 방법에 대해서 알아보았습니다. 하지만 인덱스를 아무 컬럼에 삽입해선 되지 않겠죠? 인덱스를 넣을 컬럼을 선택하는 것 또한 중요한 부분입니다.
"어떤 컬럼에 인덱스를 적용해야 할까?"
두가지 상황에 대해서 알아보도록 하겠습니다.
1. 오직 1개의 컬럼에 인덱스를 적용해야 한다면?
2. 2개 이상의 컬럼에 인덱스를 적용해야 한다면?
위 두가지 상황에 대해서 어떻게 해야하는지 알아보도록 하겠습니다.
"1개의 컬럼에 인덱스를 적용해야 한다면?"
카디널리티(Cardinality)가 가장 높은 컬럼을 대상으로 인덱스를 적용해야합니다.
위키피디아에서 설명한 카디널리티란?
SQL에서 카디널리티(Cardinality)는 데이터베이스 테이블의 특정 컬럼(Column)에 포함된 데이터 값의 고유성을 나타냅니다. 카디널리티가 낮을수록 컬럼에 중복된 요소가 많습니다. 따라서 가능한 가장 낮은 카디널리티를 가진 컬럼은 모든 행에 동일한 값을 갖을 수 있습니다. SQL 데이터베이스는 카디널리티를 사용해 주어진 쿼리에 대한 최적의 쿼리 계획을 결정하는 데 도움을 줍니다.
그렇습니다. 카디널리티는 해당 컬럼의 중복도에 관한 내용입니다. 즉, 카디널리티가 높다는 것은 중복도가 낮은 것을 의미합니다. 가장 독립적인 행이 많다는 의미로 해석될 수 있습니다. 반대로 카디널리티가 낮다는 것은 위에 적혀있는 그대로 행의 중복도가 높다는 것을 의미합니다. 가장 중복된 값이 많은 것을 의미합니다.
인덱스의 목적은 특정 컬럼의 값을 빠르게 검색하는 것입니다. 중복된 값이 많다면? 검색되는 데이터의 양이 많아지는 상황이 발생하는데 이는 인덱스의 목적과 부합하지 않습니다. 그렇기 때문에 우리는 카디널리티가 높은 컬럼에 인덱스를 적용해야함이 확실해졌습니다.
"만약에, 중복도가 높은 다시 말해서 카디널리티가 낮은 컬럼을 지정한다면 어떻게 될까요?"
중복도가 높은 컬럼에 인덱스를 설정하게 되면 인덱스는 많은 레코드를 동일한 키 값으로 참조하게 됩니다. 그리고 많은 레코드가 반환되어 이는 인덱스를 사용하는 것보다 전체 테이블을 스캔하는 (Full Table Scan)것이 더 효율적일 수 있습니다.
중복이 많은 컬럼을 인덱스로 설정하게 되면 발생하는 문제점들에 대해서 설명하겠습니다.
인덱스의 크기도 증가하게 됩니다. 중복도가 높은 컬럼에 인덱스를 설정하면 동일한 키 값이 여러번 저장되므로 인덱스의 크기가 불필요하게 커질 수 있습니다. 이는 메모리 사용량과 디스크 공간을 증가시키고 캐시 미스를 발생시킬 수 있습니다.
특히 Insert, Delete, Update와 같은 연산시 인덱스도 함께 업데이트해야합니다. 중복도가 높은 컬럼일 경우 동일한 키 값이 여러번 참조되므로 인덱스 업데이트에 필요한 작업이 더 많이 발생합니다.
이러한 이유가 발생하는 이유는 인덱스의 자료구조에도 있습니다. 인덱스의 자료구조는 B-Tree, B+ Tree를 사용하는데 모두 균형 잡힌 트리 구조로 모든 리프가 동일한 깊이를 가지게 됩니다. 중복된 키 값이 많아지면 트리의 크기와 탐색 시간이 증가하게 됩니다. 그리고 동일한 키 값이 여러 번 저장되어 트리의 높이가 증가하거나 불균형해질 수 있습니다. 이는 인덱스 탐색 속도를 저하시키는 요인이 됩니다.
"굳이 굳이 중복도가 높은 컬럼에 인덱스를 설정해야한다면?"
복합 인덱스를 사용합니다. 단일 컬럼 인덱스 대신, 여러 컬럼을 포함하는 복합 인덱스를 사용해 독립성을 유지해봅니다.
부분 인덱스를 사용합니다. 특정 조건을 만족하는 레코드만을 인덱스에 포함시켜 인덱스 크기를 줄이고 선택도를 높일 수 있습니다.
비클러스터드 인덱스를 사용합니다. 클러스터드 인덱스 외에 별도의 인덱스를 생성해 데이터 접근을 최적화합니다.
위 내용은 좀 더 조사를 해본 후에 다음 글로 작성해볼 예정입니다.
"2개 이상의 컬럼에 인덱스를 적용해야 한다면?"
카디널리티가 높은 순서대로 구성을 해야합니다.
"인덱스의 기본 구조"
키(key) : 인덱스가 참조하는 특정 컬럼의 값(type)입니다.
포인터(Pointer) : 실제 데이터가 저장된 위치를 가리키는 주소를 의미합니다.
인덱스 키의 역할은 아래와 같습니다.
| 검색 속도 향상 | 인덱스 키를 사용하면 전체 테이블을 스캔하지 않고도 원하는 데이터를 빠르게 찾을 수 있습니다. |
| 정렬 및 그룹화 최적화 | 인덱스를 사용하면 order by나 group by와 같은 쿼리의 성능을 개선할 수 있습니다. |
| 조인 최적화 | 여러 테이블 간의 조인 연산시 인덱스 키를 활용해 효율적인 데이터 매칭이 가능합니다. |
여기까지가 키의 역할이였습니다. 하지만 중요한 내용은 키의 크기입니다. 키의 크기가 중요한 이유에 대해서 설명하겠습니다. 아래의 글중 일부는 해당 블로그를 참조했습니다.
참조한 부분입니다.
InnoDB는 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 합니다. 인덱스 역시 페이지 단위로 관리되며 페이지는 16KB로 고정되어있습니다. 만약 본인이 설정한 인덱스 키의 크기가 16Byte라고 하고, 자식 노드의 주소가 담긴 크기가 12Byte로 잡게 된다면? 16 * 1024 / (16 + 12) = 585로 하나의 페이지는 585개가 저장될 수 있습니다.
인덱스의 키가 32byte로 커지게 되면 어떻게 될까요?
32 * 1024 / (32 + 12) = 372개만 한 페이지에 저장할 수 있습니다. 조회 결과로 500개의 row를 읽을 때 16byte라면 1개의 페이지에서 다 조회가 되지만, 32byte 일때는 2개의 페이지를 읽어야하므로 이는 성능 저하가 발생됩니다.
조금의 설명이 저는 필요해서 찾아보았습니다.
-- employees 테이블 생성
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT
);
-- employee_id 컬럼에 인덱스 생성 (기본 키로 이미 인덱스가 생성됨)
CREATE INDEX idx_employee_id ON employees(employee_id);
-> 4byte
-- last_name 컬럼에 인덱스 생성
CREATE INDEX idx_last_name ON employees(last_name);
-> 최대 50byteemployees에 저장된 키 값은 INT로 4byte의 크기를 차지하고 있습니다. 하지만, last_name에 저장된 키값은 INT보다 더 많은 디스크 공간을 차지합니다.
"위 두가지의 차이는 무엇일까요?"
위에서 설명한대로 키의 크기 차이가 가장 분명하게 보여집니다. 기본적으로 4byte와 최대 50byte이니까요. 키의 크기가 큰 것으로 발생할 수 있는 문제점이 무엇이 있을까요?
인덱스의 크기가 증가하게 됩니다. 어쨌든 인덱스도 데이터이기 때문에 디스크의 공간 소모가 큽니다. 그리고 메모리에 올라가는 크기도 덩달아 커지기에 사용량이 증가합니다. 그렇기에 캐시 미스가 많이 발생됩니다. 이유는 크기가 커져 메모리에 더 적은 인덱스 페이지를 적재하기 때문입니다. 이는 디스크 I/O를 증가시켜 성능을 저하시키게 됩니다.
검색 성능의 저하도 뒤따라 옵니다. 위에서 설명한대로 페이지를 두개를 불러오는 상황이 발생하겠습니다. 그리고 인덱스 자료구조는 키 값이 클수록 트리의 깊이가 증가하게 됩니다. 이는 검색시 더 많은 노드를 탐색해야하는 불편한 상황이 발생하게 됩니다.
또한, 쓰기(Write Ahead Logging) 작업의 오버헤드가 증가합니다. 당연히 Insert, Delete, Update 과정이 더 많은 데이터를 처리하므로 성능의 저하가 찾아올 것입니다.
그래서 우리는 키값은 최대한 작게, 그리고 인덱스를 적용하려는 컬럼도 작은 컬럼을 선택해야합니다.
이런 인덱스도 단점이 존재하기 마련입니다. 물론 적절히 상황에 맞게 사용한다면 단점을 커버할 수 있을 것입니다.
"인덱스의 단점은?"
인덱스를 사용하면, insert, update, delete의 성능이 떨어지게 됩니다. 대신 select의 성능을 향상시켜줍니다.
insert 연산시 새로운 데이터가 테이블에 추가될 뿐만 아니라 관련된 인덱스도 업데이트가 됩니다. 그래서 인덱스 업데이트시에 새로운 키 값이 인덱스 자료구조에 삽입됩니다. 그래서 인덱스는 일정한 검색 속도를 내기 위해 정렬상태를 유지하기 위해 추가적인 작업을 필요로 합니다.
위와 같은 이유로 delete도 update도 정렬상태 유지를 위해 트리의 일부를 재구성하는 과정이 들어가게 되어 다수의 인덱스가 존재할 경우 각 인덱스마다 별도의 업데이트가 필요하며 전체 삽입 기간이 늘어나게 되는 것입니다. 그래서 인덱스의 개수를 제한해야 되는 것이고 이는 단점으로 작용하게 됩니다.
또한, 동시성 제어를 해야하므로 오버헤드가 발생합니다. 다중 쓰기 작업이 동시에 인덱스를 업데이트할 경우 락 관리가 필요하고 이는 동시성 성능에 영향을 미치게 됩니다.
추가적인 메모리를 사용합니다. 인덱스가 많아질 수록 인덱스를 캐시하는 데 필요한 메모리 양이 증가합니다. 인덱스도 어쨌든 메모리이기 때문입니다. 위에서도 설명이 되어있죠? 이렇게 되므로 데이터 캐시의 크기를 줄이거나 전체 메모리 사용량을 증가시켜 성능에 영향을 미치게 됩니다.
여기까지 인덱스에 관한 설명이었습니다. 인덱스 인덱스 했지만 정작 어떻게 돌아가는지 어떻게 인덱스를 적용해야하는지 알 수 있는 부분이었던 것 같습니다. 이후에는 클러스터 인덱스 등 복잡한 인덱스에 대해서 공부해보겠습니다.
앞으로 주어진 프로젝트에서도 인덱스를 잘 활용해 조회 속도를 높여보도록 하겠습니다.
Ref.