The Long Room Library at Trinity College in Dublin, Ireland [1080x1335]
100만여개가 넘는 서적이 보관되어 있는 도서관에서 10분안에 특정 서적을 찾아야 하는 상황에 있다고 가정해보자. 10분안에 모든 책을 뒤져서 찾을 수 있을까? 어렵다. 책을 찾는 가장 빠른 방법은 책이 위치한 분류 번호, 섹터 번호 뭐 그런 것을 먼저 알고 찾아 가는게 가장 빠를 것이다.
Database Index 데이터베이스 인덱스는 그것에 대한 이야기이다.
출처: [DB] 데이터베이스 인덱스(Index) 란 무엇인가? (tistory.com)
정렬되지 않은 테이블에서 특정 값을 가진 데이터를 검색해야 한다고 생각해보자.
예를 들어,
와 같은 임의의 테이블이 있다고 가정하자.
COMPANY_ID = 18인 행을 가져오고 싶을 때 아래의 쿼리를 실행할 수 있다.
SELECT
company_id,
units,
unit_cost
FROM
index_test
WHERE
company_id = 18이 경우 데이터베이스는 companay_id = 18인 행을 찾기 위해 테이블에 나타나는 순서대로 모든 행을 한번에 하나씩 위에서 아래로 조회할 것이다.
출처 How Does Indexing Work | Tutorial by Chartio
예시에서는 17개의 행이지만, 테이블 크기가 늘어날수록 조회에 오랜 시간이 걸릴 것이다.
인덱싱을 활용해 검색 조건이 있는 열을 순서대로 정렬하면 쿼리 성능을 최적화하는 데 도움이 될 것이다.
이제 데이터베이스는 COMPANY_ID에 대해 18을 검색하고 해당 행에 요청된 모든 열을 반환 한 다음 다음행으로 이동하게 된다. 다음 행에서 COMPANY_ID의 값이 18이면 행을 반환할 것이고, 20이면 검색과 쿼리가 중지할 것이다.
Database Index
이제 데이터베이스 인덱스 Database Index 이야기를 해보자.
인덱스는 데이터베이스에서 조회 성능 향상을 위해 사용하는 자료구조이다. 인덱스는 요청한 쿼리를 빠르게 조회하여 데이터를 제공한다.
인덱스는 테이블에서 인덱스의 포인터 역할을 하는 것이다.
인덱스는 말 그대로 인덱스(index 색인) 역할을 한다. 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸린다. 이때 책의 목차, 색인을 찾아보듯이 인덱스는 그런 역할을 하는 것이다.
출처: How Does Indexing Work | Tutorial by Chartio
인덱스를 사용하면 쿼리는 값이 18인 행만 검색할 수 있으며, 포인터를 사용하여 특정 행을 찾을 수 있다. 그 다음 쿼리는 테이블로 이동하여 해당 행과 필드값을 조회할 수 있는 것이다.
인덱스를 활용하면 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. 수정, 삭제를 위해서 당연히 조회가 필요하다.
UPDATE USER SET NAME = 'JAY JANG' WHERE NAME = 'JANG';만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체 탐색 Full Scan을 해야한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.
인덱스 관리
인덱스 생성
CREATE INDEX <index_name>
ON <table_name> (column1, column2, ...)테이블에서 전화 번호를 인덱싱하는 경우 다음 쿼리를 사용할 수 있다.
CREATE INDEX customers_by_phone
ON customers (phone_number)인덱스 삭제
CREATE INDEX <index_name>전화 번호 인덱스를 삭제하는 경우 다음 쿼리를 삭제할 수 있다.
DROP INDX customer_by_phone인덱스 장점과 단점
그렇다면 인덱스는 항상 효율적일까? 그렇지는 않다. 아래와 같은 장단점이 존재한다.
장점
테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
전반적인 시스템의 부하를 줄일 수 있다.
단점
인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다
인덱스를 관리하기 위해 추가 작업이 필요하다
인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
인덱스의 자료구조
인덱스 구현을 위해 다양한 자료구조를 사용할 수 있다. 가장 대표적인 것은 해시 테이블과 B+ 트리이다.
B+ 트리
REFERENCE
Database index - Wikipedia
How Does Indexing Work | Tutorial by Chartio
What is a Database Index? | Codecademy
How to use Indexing to Improve Database Queries (dataschool.com)
[Database] 인덱스(index)란? - MangKyu's Diary (tistory.com)
