[SQL] Index(인덱스)

GilLog·2020년 10월 27일
Indexsql
32

DataBase

목록 보기
2/25

Index

Index는 RDBMS에서 검색 속도를 높이기 위한 기술이다.

TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 Full Scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 한다.

RDBMS에서 사용하는 INDEX는 B-Tree 에서 파생된 B+ Tree 를 사용해서 색인화한다.

보통 SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때 인덱스가 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있다.

DELETE, INSERT, UPDATE 쿼리에는 해당 사항이없으며 INDEX 사용시 오히려 느려진다

조금더 자세히 알아보면, SQL서버에서 데이터의 레코드는 내부적으로 아무런 순서없이 저장된다.

이때 데이터 저장영역을 Heap이라고 한다.

Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 모두 조회하여 검색조건과 비교하게 된다.

이러한 데이터 검색방법을 테이블 스캔(Table Scan) 또는 풀 스캔(Full Scan)이라고 한다.

이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다.

즉 인덱스는 데이터를 SELECT 할 때 빨리 찾기 위해 사용된다.


Index 사용 이유

  • WHERE 구문과 일치하는 열을 빨리 찾기 위해.

  • 특정 열을 고려 대상에서 빨리 없애 버리기 위해.

  • 조인 (join)을 실행할 때 다른 테이블에서 열을 추출하기 위해.

  • 특정하게 인덱스된 컬럼을 위한 MIN() 또는 MAX() 값을 찾기 위해.

  • 사용할 수 있는 키의 최 좌측 접두사(leftmost prefix)를 가지고 정렬 및 그룹화를 하기 위해.

  • 데이터 열을 참조하지 않는 상태로 값을 추출하기 위해서 쿼리를 최적화 하는 경우.


B-Tree 알고리즘 사용 이유

  • B+ Tree
    Index에 일반적으로 사용되는 알고리즘은 B+ Tree 알고리즘이다.
    B+ Tree 인덱스는 컬럼의 값을 변형하지 않고(값의 앞부분만 잘라서 관리), 원래의 값을 이용해 인덱싱하는 알고리즘이다.

  • Hash
    컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다.
    하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

데이터 접근에 시간 복잡도가 O(1)인 Hash Table말고 B-Tree를 사용하는 이유는 SELECT 절의 조건에 부등호 연산(>, <)이 포함될 경우 문제가 발생한다.
HashTable은 동등 연산(=)에 특화되어있어 데이터베이스의 자료구조에 적합하지 않다.


Index 구조와 작동 원리

Index 구조

Index는 논리적/물리적으로 테이블과 독립적이다.

테이블은 컬럼에 데이터가 정렬되지 않고 입력된 순서대로 들어가지만, Index는 KEY 컬럼과 ROWID 컬럼 두개로 이루어져 있고 오름차순, 내림차순으로 정렬이 가능하다.

Key : 인덱스를 생성하라고 지정한 컬럼의 값

MySQL에서 테이블 생성 시, 아래와 같은 3가지 파일이 생성된다.

  • FRM : 테이블 구조 저장 파일
  • MYD : 실제 데이터 파일
  • MYI : Index 정보 파일 (Index 사용 시 생성)

사용자가 쿼리를 통해 Index를 사용하는 칼럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용한다.

디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다.

왜냐하면 보통 인덱스는 KEY-ROWID만 가지고 있고, 테이블의 세부항목들은 갖고 있지 않기 때문이다.

Index 작동 원리

SELECT *
FROM EMP
WHERE empno=7902;

데이터 파일의 블록이 10만개 일 때, 위 SQL문을 수행시에

  1. 서버 프로세스가 파싱 과정을 마친 후 DB buffer cache에 empno 가 7902인 정보가 있는지 확인한다.

  2. 정보가 없으면 하드 디스크 파일에서 7902정보를 가진 블록을 복사해서 DB buffer cache로 가져온 후 7900 정보만 골라내서 사용자에게 보여줌

이 때 두 가지 경우로 나눌 수 있는데,

  • Index 없는 경우 : 7902정보가 어떤 블록에 들어 있는지 모르므로 10만개 전부 db buffer cache로 복사한 후 하나하나 찾는다.

  • Index 있는 경우 : where 절의 컬럼이 index가 만들어져 있는지 확인 후, 인덱스에 먼저 가서 7902정보가 어떤 ROWID를 가지고 있는지 확인한 후 해당 ROWID에 있는 블록만 찾아가서 db buffer cache에 복사함.

DML이 일어났을 때의 상황

  • INSERT

기존 Block에 여유가 없을 때, 새로운 Data가 입력된다.

→ 새로운 Block을 할당 받은 후, Key를 옮기는 작업을 수행한다.

→ Index split 작업 동안, 해당 Block의 Key 값에 대해서 DML이 블로킹 된다.
대기 이벤트 발생


  • DELETE

<Table과 Index 상황 비교>

Table에서 data가 delete 되는 경우
Data가 지워지고, 다른 Data가 그 공간을 사용 가능하다.

Index에서 Data가 delete 되는 경우
Data가 지워지지 않고, 사용 안 됨 표시만 해둔다.
Table의 Data 수와 Index의 Data 수가 다를 수 있다.


  • UPDATE

Table에서 update가 발생하면 → Index는 Update 할 수 없다.
Index에서는 Delete가 발생한 후, 새로운 작업의 Insert 작업 / 2배의 작업이 소요되어 힘들다.


Index 종류

인덱스에는 크게 Clustered와 NonClustered 인덱스로 나눌 수 있다.

Clustered 인덱스

Clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 Clustered 인덱스를 기준으로 입력이 된다.

따라서 한 테이블에 오직 하나만 존재 할 수 있으며 Table을열었을 때 Order By를 사용하지 않아도 데이터가 Clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수있다.

물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다.

NonClustered 인덱스

NonClustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다.

자동 정렬되지 않고, Index를 생성할 때는 Clustered가 되어있을 때, Index Scan이 유리하다.


Index 장점

  • 키 값을 기초로 하여 테이블에서 검색과 정렬 속도를 향상시킨다.
  • 질의나 보고서에서 그룹화 작업의 속도를 향상시킨다.
  • 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.
  • 테이블의 기본 키는 자동으로 인덱스가 된다.

Index 단점

  • Index 생성시 .mdb 파일 크기가 증가한다.
  • 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
  • Index 된 Field에서 Data를 업데이트하거나, Record를 추가 또는 삭제시 성능이 떨어진다.
  • 데이터 변경 작업이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.
  • Index를 생성하는데 시간이 많이 소요될 수 있다.
  • Index가 데이터베이스 공간을 차지해 추가적인 공간이 필요해진다.
    DB의 10퍼센트 내외의 공간이 추가로 필요

Index를 남발하지 말아야 하는 이유

데이터베이스 서버에 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다.
문제가 발생할때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래한다.

조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 Insert, Delete, Update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하한다.

그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야한다.
인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.


Index 사용 전 명시 사항

  • where절에서 자주 사용하는 컬럼에 사용한다.

  • like '%~'는 조심해야 한다. %는 뒤에만 사용하도록 해야한다.
    (table scan이여서 성능 감소)

  • between A and B (Clustered Index가 유리)
    범위 쿼리문에서는 클러스터 인덱스가 유리하지만 클러스터 인덱스는 테이블 당 1개만 가질 수 있다는 단점 존재

  • order by에 항상 또는 자주 사용되는 컬럼에 사용한다.

  • join으로 자주 사용되는 컬럼에 사용한다.

  • Foreign key (1:1 매핑)이 많을 때 -> Clustered, NonClustered Index 둘 다 상관 없다.
    상황에 따라 Clustered Index사용

  • Foreign key (1:N 매핑)이 많을 때 -> Clustered Index 사용한다.

  • 100만건의 데이터 중 10건의 데이터 조회 -> 찾는 건이 적은 컬럼에 Index를 사용한다.
    상책중복이 많은 컬럼 (EX:성별)에는 Index를 거는 것이 아니다.
    조회되는 건 수가 많으면 인덱스를 걸지 않고 Table Scan이 더 나은편이다.

  • not 연산자는 긍정문으로 변경

  • Insert, Delete 등 데이터의 변경(DML)이 많은 컬럼은 인덱스를 걸지 않은 편이 좋다.

  • 인덱스를 만드는데 시간과 저장공간이 소비되고 만들고 난 후에도 추가적인 공간이 필요하다..

  • 데이터를 변경(Insert, Update, Delete)를 하면 인덱스를 다시 조정해야하기 때문에 자원이 많이 소모된다.
    특히나 Insert 연산


Index 사용 예제

  • 인덱스 생성
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......);
EX> CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 
// UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻
EX> CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); 

ALTER TABLE  테이블명 ADD INDEX(필드명(크기));

CREATE TABLE 테이블 명 ( 필드명 데이터타입(데이터크기), INDEX(필드명(크기)) ENGINE MyISAM;

필드 중에는 데이터 형식 때문에 인덱스가 될 수 없는 필드도 있다.

여러 필드로 이루어진(다중 필드) 인덱스를 사용하면 첫 필드 값이 같은 레코드도 구분할 수 있다.

참고로 액세스에서 다중 필드 인덱스는 최대 10개의 필드를 포함할 수 있다.

  • 인덱스 삭제
DROP INDEX [인덱스 명]
  • 인덱스 확인
SHOW INDEX FROM 테이블이름

Index Rebuild

인덱스를 리빌드하는 이유

인덱스 파일은 생성 후 Insert, Update, Delete등을 반복하다보면 성능이 저하된다.
생성된 인덱스는 트리구조를 가지는데, 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어지기 때문이다.
이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋다.

  • Index 트리의 깊이가 4이상인 Index를 조회하는 쿼리
SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
       DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM   USER_INDEXES I
WHERE   I.BLEVEL > 4
ORDER BY I.BLEVEL DESC

해당 쿼리문을 실행하여 검색되는 Index는 리빌딩을 하는것이 좋다.

  • 인덱스 리빌드
ALTER INDEX [인덱스명] REBUILD;
  • 전체 인덱스 리빌드 쿼리문 만들기
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;

🙆‍♂️ 참고사이트 🙇‍♂️

DB Index 란?[lalwr님]

[database] DB 인덱스(INDEX)란?

[Oracle] 오라클 인덱스(Index) 사용법 총정리(생성, 조회, 삭제, 리빌드)[코딩팩토리]

index란[가치관제작소]

profile
GilLog
🚀 기록보단 길록을 20.10 ~ 22.02 ⭐ Move To : https://gil-log.github.io/
이전 포스트

MySQL 설치 하기

다음 포스트

[SQL] 튜닝

5개의 댓글

comment-user-thumbnail
Min-Ho Lee
2021년 6월 23일

좋은 글 감사합니다!

1개의 답글
comment-user-thumbnail
sir.YOO_HWAN
2022년 4월 20일

좋은글 감사합니다 :)

답글 달기
comment-user-thumbnail
clcell1646
2022년 10월 13일

좋은 글 감사합니다!

답글 달기