데이터베이스에서 사용하는 용어 index(인덱스)에 대해 알아보자
1. 인덱스(index)
인덱스란 DBMS(데이터베이스 관리 시스템)에서 테이블의 검색 속도를 향상시키기 위한 데이터이다. DB에는 여러 개의 테이블이 존재하고, 테이블 안에는 무수히 많은 데이터가 저장된다. 많은 데이터를 관리하는 DB에서 특정 조건에 부합하는 데이터를 조회하려면 검색 속도는 아주 느릴 것이다. 이 때 인덱스를 이용하면 검색 속도를 향상시킬 수 있다.
책에서 원하는 내용을 찾을 때, 목차와 색인을 확인해 원하는 내용의 페이지를 쉽게 찾을 수 있는 것처럼 데이터베이스에서는 인덱스가 그런 역할을 하는 것이다.
테이블의 특정 컬럼(Column)에 인덱스를 설정하면 별도의 메모리 공간에 컬럼의 값과 물리적 주소를 저장한다. 인덱스는 오름차 순으로 정렬되어 저장된다.
우리가 테이블을 생성한다면 기본적으로 PK(Primary Key)가 인덱스로 생성된다. PK를 이용해 쿼리를 작성한 경우, 인덱스를 통해 PK를 찾고 PK를 통해 레코드를 찾는다. *옵티마이저 입장에서는 PK를 찾고, 레코드를 찾으니 그만큼 비용이 더 들지만 테이블 전체를 탐색하지 않아도 되기 때문에 검색 속도가 빨라진다.
인덱스는 테이블에서 하나 혹은 여러 컬럼에 대해 설정할 수 있는데, 인덱스를 무조건 많이 설정한다고 검색 속도가 향상되는 것은 아니다. 인덱스는 별도의 메모리에 테이블 형태로 저장되므로 많이 저장될수록 그만큼 공간을 차지하게 된다.
*옵티마이저(Optimizer)?
SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 핵심 엔진이다. 내가 SQL문을 작성하고 실행시키면 DBMS에 내장된 옵티마이저가 쿼리를 어떻게 최적으로 실행시킬지 결정한다.
옵티마이저는 다음과 같이 동작한다
- 사용자의 쿼리를 수행하기 위해 후보가 될 만한 실행 계획을 찾는다.
- 미리 수집해놓은 통계 정보를 이용해 각 실행 계획의 예상 비용을 산정한다.
- 실행 계획을 비교해 최적의 실행을 선택한다.
2. 인덱스 특징
인덱스 관리
DBMS는 인덱스를 항상 최신의 정렬 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 추가적인 연산이 발생한다.
- INSERT : 새로운 데이터에 대한 인덱스 추가 (인덱스는 항상 정렬된 상태를 유지해야 하므로 적절한 위치를 탐색한 후에 저장)
- UPDATE : 기존의 인덱스를 사용하지 않겠다는 처리, 갱신된 데이터에 대해 인덱스 추가
- DELETE : 해당 인덱스를 사용하지 않겠다는 처리
만약 데이터의 수정이 잦은 데이터로 인덱스를 생성하면 이런 추가적인 연산때문에 오히려 성능이 낮아질 것이다. 또 데이터 수정과 삭제에서는 인덱스를 제거하는 것이 아니라 사용하지 않는다는 처리를 하고 남겨두기 때문에 인덱스 저장 공간이 과도하게 커지는 문제가 발생할 수 있다.
인덱스 사용의 장단점
장점
- 검색 속도를 향상시킬 수 있다.
- 시스템의 부하를 줄일 수 있다.
단점
- 추가 저장공간이 필요하다.
- INSERT, UPDATE, DELETE 시 추가 작업이 필요하다.
- 인덱스를 잘못 사용할 경우 오히려 성능이 저하된다.
그럼에도 불구하고 DBMS에서 인덱스는 필수다. 일반적인 시스템에서 데이터 조회 업무가 90% 이상이기 때문에 조회 속도의 향상이 시스템 성능 향상의 중요한 역할을 하게 되는 것이다.
3. 인덱스 생성
기본적으로 규모가 큰 테이블에서 인덱스를 생성하는 경우 검색의 이득이 있다. 인덱스로 생성하는 컬럼들의 기준은 다음과 같다.
- *카디날리티 (Cardinality)
- 카디날리티가 높은 컬럼
- 카디날리티가 높으면 한 컬럼이 갖고 있는 값의 중복도가 낮다.
- 선택도 (Selectivity)
- 선택도가 낮은 컬럼
- 선택도가 낮으면 한 컬럼이 갖고 있는 값 하나로 적은 데이터가 찾아진다.
- 조회 활용도
- 조회 활용도가 높은 컬럼
- JOIN, WHERE, ORDER BY 같은 곳에서 자주 사용되는 컬럼
- 수정 빈도
- 수정 빈도가 낮은 컬럼
- 인덱스로 설정된 컬럼이 자주 수정된다면 인덱스 테이블에도 추가작업을 해줘야 하기 때문
그 외 분포도가 좁은 범위의 컬럼, 기본키 등
*카디날리티(Cardinality)?
특정 컬럼에 존재하는 데이터의 고유성을 의미한다. 카디날리티가 높을수록 중복도가 낮아지며, 유니크한 값이 많다는 것이다. 일반적으로 유니크한 값이 많을수록 검색 대상이 줄어들어 조회 속도가 빠르다.
만약 이름과 성별 컬럼이 존재하는 테이블에 1000개의 데이터가 존재한다면
Case A. 이름을 인덱스로 둔 경우 : (동명이인으로 인해 대략) 70가지. 70개 인덱스에 1000개의 데이터가 나뉘어 들어간다.
Case B. 성별을 인덱스로 둔 경우 : (남/여) 2가지. 2개 인덱스에 1000개의 데이터가 나뉘어 들어간다.
⇒ 특정 사람을 찾고싶을 때 Case B에서 검색했을 때보다 Case A에서 검색할 때가 더 효율적으로 찾을 수 있을 것이다.
4. 인덱스의 자료구조
- Hash Table
해시 테이블은 (key, value)쌍으로 데이터를 저장하는 자료구조로 빠른 데이터 검색이 필요할 때 유용하다. key값에 해시함수를 적용해 고유한 index를 생성하고, 그 index를 이용해 값을 저장하고 검색한다.
해시 테이블을 이용해 인덱스를 생성하면 (key, value) = (컬럼의 값, 데이터의 위치)가 저장되기 때문에 등호(=) 연산에 최적화되어 있다. 하지만 해시 테이블은 실제로 인덱스에서 잘 사용되지 않는데, 그 이유는 해시 테이블 내의 데이터들은 정렬되어 있지 않으므로 부등호(<, >) 연산으로 검색하기에는 부적합하기 때문이다.
- B-Tree
B-Tree는 탐색 성능을 높이기 위해 균형 있게 높이를 유지하는 Balanced Tree의 일종이다. 모든 leaf node가 같은 level로 유지되도록 자동으로 밸런스를 맞춘다. 자식 node의 개수가 2개 이상이 될 수 있고, 하나의 node 안에 key가 1개 이상일 수 있다. node안에서 key들은 정렬된 상태이며, key를 기준으로 자식 node들이 나뉘게 된다. 부모 node의 key가 k개라면, 자식 node의 수는 k+1개이다.
정렬된 트리이므로 조회할 때 빠르다. 하지만 INSERT, UPDATE, DELETE 작업은 느리다.
- B+Tree
B-Tree를 개선시킨 자료구조로, 실제로 가장 많은 DBMS에서 사용되는 자료구조이다. 기존 B-Tree는 어느 한 데이터의 검색은 효율적이지만 모든 데이터를 순회할 때는 트리의 모든 노드를 방문해야 하므로 비효율적이다. 이러한 B-Tree의 단점을 개선시킨 자료구조가 B+Tree이다.
오직 leaf node에만 데이터를 저장하고 leaf node가 아닌 node에서는 자식 포인터만 저장한다. leaf node에 모든 key들이 존재해야 하기 때문에 중간 node에서 중복된 key가 존재할 수 있다. leaf node를 제외하고 데이터를 저장하지 않기 때문에 메모리를 더 확보할 수 있다. 따라서 하나의 node에 더 많은 key를 보관할 수 있게 되고, 트리의 높이가 더 낮아지므로 검색 속도를 높일 수 있다.
leaf node끼리는 연결리스트(linked list)로 연결되어있다. 따라서 부등호 연산을 이용해 데이터를 검색한다면 순차 검색을 효율적으로 할 수 있다. Full Scan을 하는 경우 leaf node끼리 연결리스트로 연결되어 있기 때문에 선형 시간이 소모된다. (B-Tree는 모든 node를 트리 순회해서 확인해야 함)
5. 마무리
특정 컬럼에 인덱스를 생성해서 조회 비용을 확인해보자
MySQL에서 진행. 약 5000개의 데이터를 저장하는 테이블에서 특정 컬럼을 인덱스 `test_index` 로 생성하고 WHERE절에서 해당 인덱스를 사용해 쿼리를 작성했다. 설정하기 전에는 Full Table Scan으로 5000개의 데이터를 모두 확인하고 있고, 설정한 후에는 Non-Unique Key Lookup으로 `test_index`를 타고 데이터를 찾는다.
쿼리 비용이 훨씬 낮아진다. 저장하는 데이터가 많고 인덱스로 생성한 컬럼을 이용해 조회가 많이 이루어진다면, 적절한 인덱스를 설정하는 것이 조회 성능을 올리는데 도움이 될 것이다.
인덱스를 생성하고 사용하는 것만큼 인덱스를 잘 관리해주는 것도 중요한 것 같다. 그리고 인덱스를 잘 활용할 수 있도록 쿼리를 짜는 것도 중요해 보인다.
참고자료 😃
https://coding-factory.tistory.com/746
https://choicode.tistory.com/27
https://mangkyu.tistory.com/96
https://rebro.kr/167
https://rebro.kr/169
https://hudi.blog/db-index-and-indexing-algorithms/
https://d2.naver.com/helloworld/1155
https://code-lab1.tistory.com/137
'DB' 카테고리의 다른 글
| [DB] B-Tree, B+Tree - DB Index (0) | 2024.04.18 |
|---|---|
| [DB] NoSQL (NotOnly SQL) (1) | 2024.03.28 |
| [DB] 데이터베이스 정규화 (Normalization) (1) | 2024.03.26 |
| [DB] 트랜잭션 격리수준 (Transaction Isolation) (0) | 2024.03.15 |
| [DB] 트랜잭션 (Transaction) (0) | 2024.03.10 |
데이터베이스에서 사용하는 용어 index(인덱스)에 대해 알아보자
1. 인덱스(index)
인덱스란 DBMS(데이터베이스 관리 시스템)에서 테이블의 검색 속도를 향상시키기 위한 데이터이다. DB에는 여러 개의 테이블이 존재하고, 테이블 안에는 무수히 많은 데이터가 저장된다. 많은 데이터를 관리하는 DB에서 특정 조건에 부합하는 데이터를 조회하려면 검색 속도는 아주 느릴 것이다. 이 때 인덱스를 이용하면 검색 속도를 향상시킬 수 있다.
책에서 원하는 내용을 찾을 때, 목차와 색인을 확인해 원하는 내용의 페이지를 쉽게 찾을 수 있는 것처럼 데이터베이스에서는 인덱스가 그런 역할을 하는 것이다.
테이블의 특정 컬럼(Column)에 인덱스를 설정하면 별도의 메모리 공간에 컬럼의 값과 물리적 주소를 저장한다. 인덱스는 오름차 순으로 정렬되어 저장된다.
우리가 테이블을 생성한다면 기본적으로 PK(Primary Key)가 인덱스로 생성된다. PK를 이용해 쿼리를 작성한 경우, 인덱스를 통해 PK를 찾고 PK를 통해 레코드를 찾는다. *옵티마이저 입장에서는 PK를 찾고, 레코드를 찾으니 그만큼 비용이 더 들지만 테이블 전체를 탐색하지 않아도 되기 때문에 검색 속도가 빨라진다.
인덱스는 테이블에서 하나 혹은 여러 컬럼에 대해 설정할 수 있는데, 인덱스를 무조건 많이 설정한다고 검색 속도가 향상되는 것은 아니다. 인덱스는 별도의 메모리에 테이블 형태로 저장되므로 많이 저장될수록 그만큼 공간을 차지하게 된다.
*옵티마이저(Optimizer)?
SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 DBMS 핵심 엔진이다. 내가 SQL문을 작성하고 실행시키면 DBMS에 내장된 옵티마이저가 쿼리를 어떻게 최적으로 실행시킬지 결정한다.
옵티마이저는 다음과 같이 동작한다
- 사용자의 쿼리를 수행하기 위해 후보가 될 만한 실행 계획을 찾는다.
- 미리 수집해놓은 통계 정보를 이용해 각 실행 계획의 예상 비용을 산정한다.
- 실행 계획을 비교해 최적의 실행을 선택한다.
2. 인덱스 특징
인덱스 관리
DBMS는 인덱스를 항상 최신의 정렬 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 따라서 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 추가적인 연산이 발생한다.
- INSERT : 새로운 데이터에 대한 인덱스 추가 (인덱스는 항상 정렬된 상태를 유지해야 하므로 적절한 위치를 탐색한 후에 저장)
- UPDATE : 기존의 인덱스를 사용하지 않겠다는 처리, 갱신된 데이터에 대해 인덱스 추가
- DELETE : 해당 인덱스를 사용하지 않겠다는 처리
만약 데이터의 수정이 잦은 데이터로 인덱스를 생성하면 이런 추가적인 연산때문에 오히려 성능이 낮아질 것이다. 또 데이터 수정과 삭제에서는 인덱스를 제거하는 것이 아니라 사용하지 않는다는 처리를 하고 남겨두기 때문에 인덱스 저장 공간이 과도하게 커지는 문제가 발생할 수 있다.
인덱스 사용의 장단점
장점
- 검색 속도를 향상시킬 수 있다.
- 시스템의 부하를 줄일 수 있다.
단점
- 추가 저장공간이 필요하다.
- INSERT, UPDATE, DELETE 시 추가 작업이 필요하다.
- 인덱스를 잘못 사용할 경우 오히려 성능이 저하된다.
그럼에도 불구하고 DBMS에서 인덱스는 필수다. 일반적인 시스템에서 데이터 조회 업무가 90% 이상이기 때문에 조회 속도의 향상이 시스템 성능 향상의 중요한 역할을 하게 되는 것이다.
3. 인덱스 생성
기본적으로 규모가 큰 테이블에서 인덱스를 생성하는 경우 검색의 이득이 있다. 인덱스로 생성하는 컬럼들의 기준은 다음과 같다.
- *카디날리티 (Cardinality)
- 카디날리티가 높은 컬럼
- 카디날리티가 높으면 한 컬럼이 갖고 있는 값의 중복도가 낮다.
- 선택도 (Selectivity)
- 선택도가 낮은 컬럼
- 선택도가 낮으면 한 컬럼이 갖고 있는 값 하나로 적은 데이터가 찾아진다.
- 조회 활용도
- 조회 활용도가 높은 컬럼
- JOIN, WHERE, ORDER BY 같은 곳에서 자주 사용되는 컬럼
- 수정 빈도
- 수정 빈도가 낮은 컬럼
- 인덱스로 설정된 컬럼이 자주 수정된다면 인덱스 테이블에도 추가작업을 해줘야 하기 때문
그 외 분포도가 좁은 범위의 컬럼, 기본키 등
*카디날리티(Cardinality)?
특정 컬럼에 존재하는 데이터의 고유성을 의미한다. 카디날리티가 높을수록 중복도가 낮아지며, 유니크한 값이 많다는 것이다. 일반적으로 유니크한 값이 많을수록 검색 대상이 줄어들어 조회 속도가 빠르다.
만약 이름과 성별 컬럼이 존재하는 테이블에 1000개의 데이터가 존재한다면
Case A. 이름을 인덱스로 둔 경우 : (동명이인으로 인해 대략) 70가지. 70개 인덱스에 1000개의 데이터가 나뉘어 들어간다.
Case B. 성별을 인덱스로 둔 경우 : (남/여) 2가지. 2개 인덱스에 1000개의 데이터가 나뉘어 들어간다.
⇒ 특정 사람을 찾고싶을 때 Case B에서 검색했을 때보다 Case A에서 검색할 때가 더 효율적으로 찾을 수 있을 것이다.
4. 인덱스의 자료구조
- Hash Table
해시 테이블은 (key, value)쌍으로 데이터를 저장하는 자료구조로 빠른 데이터 검색이 필요할 때 유용하다. key값에 해시함수를 적용해 고유한 index를 생성하고, 그 index를 이용해 값을 저장하고 검색한다.
해시 테이블을 이용해 인덱스를 생성하면 (key, value) = (컬럼의 값, 데이터의 위치)가 저장되기 때문에 등호(=) 연산에 최적화되어 있다. 하지만 해시 테이블은 실제로 인덱스에서 잘 사용되지 않는데, 그 이유는 해시 테이블 내의 데이터들은 정렬되어 있지 않으므로 부등호(<, >) 연산으로 검색하기에는 부적합하기 때문이다.
- B-Tree
B-Tree는 탐색 성능을 높이기 위해 균형 있게 높이를 유지하는 Balanced Tree의 일종이다. 모든 leaf node가 같은 level로 유지되도록 자동으로 밸런스를 맞춘다. 자식 node의 개수가 2개 이상이 될 수 있고, 하나의 node 안에 key가 1개 이상일 수 있다. node안에서 key들은 정렬된 상태이며, key를 기준으로 자식 node들이 나뉘게 된다. 부모 node의 key가 k개라면, 자식 node의 수는 k+1개이다.
정렬된 트리이므로 조회할 때 빠르다. 하지만 INSERT, UPDATE, DELETE 작업은 느리다.
- B+Tree
B-Tree를 개선시킨 자료구조로, 실제로 가장 많은 DBMS에서 사용되는 자료구조이다. 기존 B-Tree는 어느 한 데이터의 검색은 효율적이지만 모든 데이터를 순회할 때는 트리의 모든 노드를 방문해야 하므로 비효율적이다. 이러한 B-Tree의 단점을 개선시킨 자료구조가 B+Tree이다.
오직 leaf node에만 데이터를 저장하고 leaf node가 아닌 node에서는 자식 포인터만 저장한다. leaf node에 모든 key들이 존재해야 하기 때문에 중간 node에서 중복된 key가 존재할 수 있다. leaf node를 제외하고 데이터를 저장하지 않기 때문에 메모리를 더 확보할 수 있다. 따라서 하나의 node에 더 많은 key를 보관할 수 있게 되고, 트리의 높이가 더 낮아지므로 검색 속도를 높일 수 있다.
leaf node끼리는 연결리스트(linked list)로 연결되어있다. 따라서 부등호 연산을 이용해 데이터를 검색한다면 순차 검색을 효율적으로 할 수 있다. Full Scan을 하는 경우 leaf node끼리 연결리스트로 연결되어 있기 때문에 선형 시간이 소모된다. (B-Tree는 모든 node를 트리 순회해서 확인해야 함)
5. 마무리
특정 컬럼에 인덱스를 생성해서 조회 비용을 확인해보자
MySQL에서 진행. 약 5000개의 데이터를 저장하는 테이블에서 특정 컬럼을 인덱스 test_index 로 생성하고 WHERE절에서 해당 인덱스를 사용해 쿼리를 작성했다. 설정하기 전에는 Full Table Scan으로 5000개의 데이터를 모두 확인하고 있고, 설정한 후에는 Non-Unique Key Lookup으로 test_index를 타고 데이터를 찾는다.
쿼리 비용이 훨씬 낮아진다. 저장하는 데이터가 많고 인덱스로 생성한 컬럼을 이용해 조회가 많이 이루어진다면, 적절한 인덱스를 설정하는 것이 조회 성능을 올리는데 도움이 될 것이다.
인덱스를 생성하고 사용하는 것만큼 인덱스를 잘 관리해주는 것도 중요한 것 같다. 그리고 인덱스를 잘 활용할 수 있도록 쿼리를 짜는 것도 중요해 보인다.
참고자료 😃
https://coding-factory.tistory.com/746
https://choicode.tistory.com/27
https://mangkyu.tistory.com/96
https://rebro.kr/167
https://rebro.kr/169
https://hudi.blog/db-index-and-indexing-algorithms/
https://d2.naver.com/helloworld/1155
https://code-lab1.tistory.com/137
'DB' 카테고리의 다른 글
| [DB] B-Tree, B+Tree - DB Index (0) | 2024.04.18 |
|---|---|
| [DB] NoSQL (NotOnly SQL) (1) | 2024.03.28 |
| [DB] 데이터베이스 정규화 (Normalization) (1) | 2024.03.26 |
| [DB] 트랜잭션 격리수준 (Transaction Isolation) (0) | 2024.03.15 |
| [DB] 트랜잭션 (Transaction) (0) | 2024.03.10 |
