본문 바로가기
📁 Database/Concept

[ Database ] 인덱스(Index)란?

by 깸뽀 2022. 12. 25.
728x90

📌 인덱스(Index) 란?

책을 보면 맨 앞장 또는 맨 뒷장에 Index 페이지가 있다. 그와 같은 개념이다.
원하는 카테고리에서 찾고자 하는 내용이 담긴 페이지 번호를 찾아가듯 인덱스에서도 원하는 데이터를 먼저 찾고 저장되어 있는 물리적 주소로 찾아간다.

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조이다.

SQL select문의 속도가 너무 느린 경우 해결방법 중 하나가 인덱싱 하는것이다.

만약 인덱스를 사용하지 않은 컬럼을 조회해야 한다면 어마무시한 양의 데이터를 전체 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.

그 과정을 빠르게 하기 위해서는 index라는 자료구조를 거쳐 검색하는 것이다.

 

보통 DBMS에 데이터를 저장하면 ArrayList형태로 자료를 저장하는데(넣은 순서대로), 인덱스를 사용하면 컬럼의 값과 실제 해당 자료가 저장된 주소를 '키-값' 형태로 인덱스를 만들어 둠으로써 항상 '정렬된 상태'를 유지하는 SotedList 형태로 자료를 저장할 수 있다.

 

정렬을 제일 중요한 부분이다. 순서를 갖도록 정렬해두어 특정 데이터를 찾을 때 접근하는 속도를 굉장히 빠르게 할 수 있다. 뿐만 아니라 원하는 데이터가 더 이상 없다는 것을 알 수 있게 해 준다.

만약, 알파벡 순서로 정렬되어 있으면 'memory'라는 데이터를 찾을 때 알파벳 순서로 정렬되어 있으며 'm'으로 시작하는 용어를 모아둔 곳으로 바로 찾아가면 되기 때문이다. 

 

인덱스 자료구조에는 데이터와 그 데이터의 주소값(pointer)이 키,값으로 되어있고, 쿼리가 들어오면 이 index를 바탕으로 주소값을 통해 검색한다. 그렇게하면 검색속도가 훨씬 빨라진다.

 

특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 이렇게 인덱스를 생성하였다면 쿼리문에 "인덱스 생성 컬럼을 WHERE 조건으로 거는 등"의 작업을 하면 *옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고, 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져 올 수 있다.

*옵티마이저 : 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다. 컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다.

 

출처 - https://coding-factory.tistory.com/746

 

 

📌 인덱스(Index)의 동작 원리

일반적인 Select 쿼리 실행 시 먼저 메모리의 database buffer cache를 체크한다. buffer cache에는 자주 사용되는 테이블들이 캐싱되어 있어 여기에 데이터가 있을 경우 바로 찾아 출력하고 없을 경우 하드디스크에 있는 데이터 파일에서 데이터를 찾는다. 인덱스를 사용하면 이러한 과정을 거치지 않고 바로 주소를 통해 찾아간다.

  • 데이터 파일의 블록이 10만개가 있다고 가정할때 select문 실행시
  • server process가 구문분석과정을 마친후 database buffer cache에 조건에 부합하는 데이터가 있는지 확인
  • 해당 정보가 buffer cache에 없다면 디스크 파일에서 조건에 부합하는 블럭을 찾아 database buffer cache에 가져온 뒤 사용자에게 보여준다.
  • 이 경우 index가 없으면 10만개 전부 database buffer cache로 복사한뒤 풀스캔으로 찾게되는데
  • index가 있으면
    • where절의 조건의 컬럼이 index의 키로 생성되어있는지 확인한 뒤,
    • 인덱스에 먼저 가서 조건에 부합하는 정보가 어떤 ROWID를 가지고 있는지 확인 후
    • ROWID에 있는 블럭을 찾아가 해당 블럭만 buffer cache에 복사한다.

 

💡 인덱스의 장점

1. 조건 검색 WHERE 절의 효율성

테이블의 레코드는 내부적으로 순서 없이 뒤죽박죽 저장된다. 이렇게 되면 where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드를 처음부터 끝까지 다 릭어서 검색 조건과 맞는지 비교해야 한다.(Full Table Scan)

하지만 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건(where)에 맞는 데이터들을 빠르게 찾아낼 수 있다. 

 

2. 정렬 Order by 절의 효율성

인덱스를 사용하면 Order by에 의한 Sort과정을 피할 수 있다.

Order by는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생된다.

하지만 인덱스를 사용하면 이미 정렬이 되어있기 때문에 이러한 전반적인 자원 소모를 하지 않아도 된다. 

 

3. MIN, MAX의 효율적인 처리 가능

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점이다.

MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오기 때문에 Full Table Scan작업을 하는 것보다 훨씩 효율적으로 찾을 수 있다.

 

 

 

💡 인덱스의 단점

1. 인덱스의 관리

인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.

만약 INSERT, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 INDEX 테이블 내에 있는 값들을 다시 정렬을 해야한다. 즉, INDEX 테이블, 원본 테이블 이렇게 두 군데에 데이터 수정 작업해줘야 한다.

 

INSERT: 새로운 데이터에 대한 인덱스를 추가
DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행
UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가

 

2. 테이블의 전체 데이터 중에서 10~15% 이상의 데이터를 처리하는 경우

검색결과가 전체 데이터의 2~4% 일 때 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다.

 

3. 추가 저장공간이 필요

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다.

 

 

 

 

📌 인덱스(Index) 생성 전략

👉 사용해야 하는 경우

  • 테이블 행의 수가 많을 때
  • WHERE절, ORDER BY절에 자주 사용되는 컬럼
  • 중복도가 최소한인 컬럼 (분포도가 좋은 컬럼 ▶ PK)
  • 항상 = 으로 비교되는 컬럼
  • 조인조건으로 자주 사용되는 컬럼

👉 사용하지 말아야 하는 경우

  • 테이블 행의 수가 적을 때
  • true, false 값을 가지는 컬럼처럼 중복도가 높은 컬럼
  • 검색 결과가 전체 데이터의 10~15% 이상일 경우
  • INSERT, UPDATE, DELETE 등 변동작업이 많을 경우

 

📌 인덱스(Index) 종류

인덱스에는 클러스터형 인덱스(clustered index)와 비클러스터형 인덱스(non-clustered index, 보조 인덱스)가 있다.

두 인덱스를 책에 비유하면 클러스터형 인덱스는 ‘영어 사전’과 같고 비클러스터 인덱스는 ‘찾아보기가 있는 책’과 같다.

클러스터형 인덱스는 영어 사전처럼 책의 내용 자체가 순서대로 정렬되어 있어 인덱스가 책의 내용과 같다.

그리고 비클러스터 인덱스는 찾아보기(색인)가 별도로 있고, 찾아보기(색인)에서 먼저 단어를 찾은 후 그 옆에 표시된 페이지로 이동하여 원하는 내용을 찾는 것과 마찬가지다.

 

1. 클러스터형 인덱스(clustered index)

  • 테이블당 하나만 생성할 수 있다.
  • 행 데이터를 인덱스로 지정한 열에 맞춰서 자동으로 정렬한다.

2. 비클러스터형 인데스(non-clustered index)

  • 테이블당 여러 개를 생성할 수 있다.

 

 

 

📌 인덱스(Index)를 사용하지 못하는 경우

1. 부정형으로 조건을 사용한 경우 NOT 연산자

*   NOT IN  /  NOT LIKE  /  <>  /  != 

SELECT column_name FROM table_name WHERE column_name != 30;

🔻 개선 🔻

-- 1.
SELECT column_name FROM table_name WHERE column_name < 30 AND column_name > 30;

-- 2. 테이블을 한 번 더 NOT EXISTS를 사용
SELECT column_name FROM table_name 
WHERE NOT EXISTS
   (SELECT column_name FROM table_name WHERE column_name = 30);

 

 2. NOT NULL, NULL 연산자

SELECT column_name FROM table_name WHERE column_name IS [NOT] NULL;

🔻 개선 🔻

SELECT column_name FROM table_name WHERE column_name > '';
SELECT column_name FROM table_name WHERE column_name >= 0;
  • B-Tree 인덱스는 널값을 저장하지 않기 때문에, 갖지 않는 정보가 됨으로써 인덱스를 사용할 필요가 없어진다.
  • 따라서 NULL인 값이 많지 않아 인덱스를 통해 엑세스를 하고자 한다면 데이터 생성 시 디폴트로 0과 같이 데이터를 만들어주 는 것이 좋다.
  • 만약, NOT NULL이 분석 대상이라면 해당 컬럼을 NULL 허용 컬럼으로 두는 것이 좋다.

 

 3. 인덱스 컬럼 절을 변형한 경우

-- 1.
SELECT column_name FROM table_name WHERE TO_CHAR(column_name, 'YYYYMMDD') = '20130909';

-- 2.
SELECT column_name FROM table_name WHERE column_name * 100 > 10000;

🔻 개선 🔻

-- 1.
SELECT column_name FROM table_name WHERE column_name  = '20130909'; // DATE 타입의 column

-- 2.
SELECT column_name FROM table_name WHERE column_name  = 100; // 문자 타입의 column
  • 수식이나 함수 등으로 인덱스 컬럼 절을 변형하였을 경우 - 반드시 함수나 수식을 사용해야 하는 경우에는 인덱스 컬럼 부분에 적용하지 말고, 여기에 대입되는 컬럼이나 상수부분에 적용해야 한다.

 

4. 내부적으로 데이터 형 변환이 일어난 경우

-- 1.
SELECT column_name FROM table_name WHERE column_name  = '20130909'; // DATE 타입의 column

-- 2.
SELECT column_name FROM table_name WHERE column_name  = 100; // 문자 타입의 column

🔻 개선 🔻

-- 1.
SELECT column_name FROM table_name WHERE column_name = TO_DATE('20130909', 'YYYYMMDD');

-- 2.
SELECT column_name FROM table_name WHERE column_name = '100';
  • 서로 대입되는 항목끼리 데이터 타입이 다르면 내부적인 형 변환에 의해 컬럼이 함수를 사용한 효과를 나타낸다.

 

 

5. LIKE 연산자를 사용하였을 경우

SELECT column_name FROM table_name WHERE column_name LIKE '%S%';

🔻 개선 🔻

-- 1.
SELECT column_name FROM table_name WHERE column_name LIKE 'S%';

-- 2.
SELECT column_name  FROM table_name WHERE INSTR(column_name , 'cmp_value') > 0;
  •  LIKE 연산자를 이용하여 검색을 할 경우 %를 앞에 넣어 사용하게 되면 인덱스를 타지 않는다.
  • 가능하면 INSTR을 사용 INSTR('비교할 대상', '비교하고자하는 값', 비교를 시작할 위치, 검색된 결과의 순번);

 

 

5. OR 조건 사용

SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE (t1.column_name1 = t2.column_name1 OR t1.column_name2 = t2.column_name2)
AND t1.column_name3 = 'cmp_value';

🔻 개선 🔻

SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE t1.column_name1 = t2.column_name1 AND t1.column_name3 = 'cmp_value';
UNION ALL
SELECT column_name FROM table_name1 t1, table_name2 t2
WHERE t1.column_name2 = t2.column_name2 AND t1.column_name3 = 'cmp_value';

 

 

💡 INDEX Suppressing 현상
◽ External supperssing:인덱스를 구성하는 컬럼에 변형을 취함으로써, 해당 인덱스를 사용하지 못하게 하는 것을 의미한다.
◽ Internal Supperssing : 서로 다른 데이터 타입끼리 비교할때 해당 인덱스를 사용하지 못하게 되는 것을 의미한다.

 

 

 

 

📖 참고

 

 

 

 

728x90

'📁 Database > Concept' 카테고리의 다른 글

[ Database ] 정규화란?  (0) 2022.12.28
[ Database ] 결합(복합) 인덱스란? (feat. 원리)  (0) 2022.12.27

관련글

댓글

티스토리툴바