'DataBase/DataBase Diary' 카테고리의 글 목록

DataBase/DataBase Diary

index란 2019.05.27

index란

2019. 5. 27. 10:51

Index란?

테이블에 저장된 데이터를 빠르게 조회하기 위한 데이터베이스 객체
인덱스는 말 그대로 책의 맨 처음 또는 맨 마지막에 있는 색인이라고 할 수 있다. 이 비유를 그대로 가져와서 인덱스를 살펴본다면 데이터는 책의 내용이고 데이터가 저장된 레코드의 주소는 인덱스 목록에 있는 페이지 번호가 될 것이다. DBMS 도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져 오려면 시간이 오래 걸린다. 그래서 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 두는 것이다.
DBMS 의 인덱스는 항상 정렬된 상태를 유지하기 때문에 원하는 값을 탐색하는데는 빠르지만새로운 값을 추가하거나 삭제, 수정하는 경우에는 쿼리문 실행 속도가 느려진다. 결론적으로 DBMS 에서 인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과만 불러올 수 있
다.

대개 B-Tree, B+Tree구조를 가짐
Index는 논리적/물리적으로 테이블과 독립적임

Index 자료구조

그렇다면 DBMS 는 인덱스를 어떻게 관리하고 있는가

B+-Tree 인덱스 알고리즘
일반적으로 사용되는 인덱스 알고리즘은 B+-Tree 알고리즘이다. B+-Tree 인덱스는 칼럼의 값을 변형하지 않고(사실 값의 앞부분만 잘라서 관리한다.), 원래의 값을 이용해 인덱싱하는 알고리즘이다.
Hash 인덱스 알고리즘
칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로, 특정 문자로 시작하는 값으로 검색을 하는 등 전방 일치와 같이 값의 일부만으로 검색하고자 할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
왜 index 를 생성하는데 b-tree 를 사용하는가?
데이터에 접근하는 시간복잡도가 O(1)인 hash table 이 더 효율적일 것 같은데? SELECT 질의의 조건에는 부등호(<>) 연산도 포함이 된다. hash table 을 사용하게 된다면 등호(=) 연산이 아닌 부등호 연산의 경우에 문제가 발생한다. 동등 연산(=)에 특화된 hashtable 은 데이터베이스의 자료구조로 적합하지 않다.

Index를 왜 사용할까?

SQL서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제 할 때 데이터의 레코드는 내부적으로 아무런 순서없이 저장된다. 이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다.이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다.

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

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

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

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

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

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

인덱스를 사용해야 하는 경우 
데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양한 값을 가지는 경우

인덱스의 사용

기존의 테이블에 인덱스를 추가하기

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

mysql> ALTER TABLE temp ADD INDEX(keyword(20));

Query OK, 554604 rows affected (1.31 sec)

Records: 554604  Duplicates: 0  Warnings: 0

테이블 생성시 인덱스 추가하기

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

mysql> CREATE TABLE test (
    -> keyword varchar(20),
    -> INDEX(keyword(20)))
    -> ENGINE MyISAM;
Query OK, 0 rows affected (0.11 sec)

FULLTEXT 인덱스 만들기
일반적인 인덱스와는 달리 MySQL의 FULLTEXT는 매우 빠르게 모든 텍스트 열을 검색한다. 검색 엔진과 유사한 방법으로 자연 언어를 이용해 검색할 수 있는 특별한 인덱스로 모든 데이터 문자열의 단어를 저장하기 때문이다.

참고사항

FULLTEXT 인덱스는 MySQL의 기본 저장 엔진 타입인 MyISAM 테이블에만 사용된다.

만약 테이블을 MyISAM 으로 변경해야 된다면

ALTER TABLE 테이블명 ENGINE = MyISAM; 
FULLTEXT 인데스는 CHAR 와 VARCHAR, TEXT 열로만 생성 가능하다.

ALTER TABLE 테이블명 ADD FULLTEXT(필드명)

mysql> ALTER TABLE temp ADD FULLTEXT(keyword);
Query OK, 554604 rows affected (1.49 sec)
Records: 554604  Duplicates: 0  Warnings: 0

인덱스의 효과

인덱스를 걸게 되면, 테이블 생성시에 인덱스의 정보도 만들게되므로 생성속도가 느려진다.

비교를 위해서 두개의 테이블을 생성하였다. 하나는 인덱스가 걸려있는 테이블 (test_index) 와 걸려 있지 않은 테이블 (test)이다. 그림을 보면 생성 속도에서 차이가 꽤 나는 것을볼 수 있다.

이런데도 인덱스를 거는 이유는 바로 조건문 등에 대한 SELECT 속도 등에서 차이가 나기 때문이다.
위 테이블의 데이터는 약 55만개의 데이터가 저장되어있다. 그 중에서 '가'로 시작되는 데이터들을 뽑아 오자

SELECT * FROM test WHERE keyword LIKE '가%' ; 
5056 rows in set (0.08 sec) 

SELECT * FROM test_index WHERE keyword LIKE '가%' ; 
5056 rows in set (0.01 sec)

Index 생성 시, 고려할 점

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼중 사용 빈도가 높고 키 값의 선별도가 좋은 칼럼에 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다. 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다. 선택한 키의 검색 일치율이 10%미만일 경우는 Index를 사용하는 것이 좋으나, 그 이상일 경우는 풀 스캔이 더 나을 수 있다.

SELECT 쿼리의 성능을 월등히 향상시키는 INDEX 항상 좋은 것일까? 쿼리문의 성능을 향상시킨다는데, 모든 컬럼에 INDEX 를 생성해두면 빨라지지 않을까?
결론부터 말하자면 그렇지 않다 .
우선, 첫번째 이유는 INDEX 를 생성하게 되면 INSERT, DELETE, UPDATE 쿼리문을 실행할 때 별도의 과정이 추가적으로 발생한다. INSERT 의 경우 INDEX 에 대한 데이터도 추가해야 하므로 그만큼 성능에 손실이 따른다. DELETE 의 경우 INDEX 에 존재하는 값은 삭제하지 않고 사용 안한다는 표시로 남게 된다. 즉 row 의 수는 그대로인 것이다. 이 작업이 반복되면 어떻게될까?
실제 데이터는 10 만건인데 데이터가 100 만건 있는 결과를 낳을 수도 있는 것이다. 이렇게되면 인덱스는 더 이상 제 역할을 못하게 되는 것이다. UPDATE 의 경우는 INSERT 의 경우,DELETE 의 경우의 문제점을 동시에 수반한다. 이전 데이터가 삭제되고 그 자리에 새 데이터가 들어오는 개념이기 때문이다. 즉 변경 전 데이터는 삭제되지 않고 insert 로 인한 split 도 발생하게 된다.
하지만 더 중요한 것은 컬럼을 이루고 있는 데이터의 형식에 따라서 인덱스의 성능이 악영향을 미칠 수 있다는 것이다. 즉, 데이터의 형식에 따라 인덱스를 만들면 효율적이고 만들면 비효율적은 데이터의 형식이 존재한다는 것이다. 어떤 경우에 그럴까?이름 , 나이 , 성별 세 가지의 필드를 갖고 있는 테이블을 생각해보자. 이름은 온갖 경우의 수가 존재할 것이며 나이는 INT 타입을 갖을 것이고, 성별은 남, 녀 두 가지 경우에 대해서만 데이터가 존재할 것임을 쉽게 예측할 수 있다. 이 경우 어떤 컬럼에 대해서 인덱스를 생성하는것이 효율적일까? 결론부터 말하자면 이름에 대해서만 인덱스를 생성하면 효율적이다.왜 성별이나 나이는 인덱스를 생성하면 비효율적일까? 10000 레코드에 해당하는 테이블에 대해서 2000 단위로 성별에 인덱스를 생성했다고 가정하자. 값의 range 가 적은 성별은 인덱스를 읽고 다시 한 번 디스크 I/O 가 발생하기 때문에 그 만큼 비효율적인 것이다.

예) 테이블 내 100개 데이터 중 pk=1을 검색했을 때, 1개의 데이터가 나올 경우 1/100 = 0.01 즉 1%의일치율을 보인다.

책에 비유를 할 때, 클러스터 인덱스는 찾고자 하는 페이지를 바로 찾는 것이고 논 클러스터 인덱스는 목차를먼저 확인한 다음, 목차에서 찾고자 하는 페이지를 찾아가는 것입니다. 또한 테이블 스캔은 처음부터 한 장씩넘기면서 찾는 방식입니다.

select * from test where name like'이%'
select * from test where name like'%이%' -- 처음 %가 붙으면 table scan이 되어 속도가 느리게 됩니다.

팁

아래의 내용을 주의, 명시해서 index를 생성하도록 합니다.

where절에서 자주 사용하는 컬럼
like '%~'는 조심 (table scan이여서 성능 감소) %는 뒤에만 사용하도록 해야함
between A and B (클러스터 인덱스 유리) - 범위 쿼리문에서는 클러스터 인덱스가 유리하지만 클러스터 인덱스는 테이블 당 1개만 가질 수 있다는 단점 존재
order by에 항상 또는 자주 사용되는 컬럼
join으로 자주 사용되는 컬럼
Foreign key (1:1 매핑)이 많을 때 -> 클러스터, 논클러스터 인덱스 둘 다 상관 없음 (상황에 따라 클러스터인덱스 사용)
Foreign key (1:N 매핑)이 많을 때 -> 클러스터 인덱스 사용
100만건의 데이터 중 10건의 데이터 조회 -> 찾는 건이 적은 컬럼에 인덱스를 걸어주는 것이 상책중복이 많은 컬럼 (예를 들어, 성별)에는 인덱스를 거는 것이 아님조회되는 건 수가 많으면 인덱스를 걸지 않고 table scan이 더 나은편
not 연산자는 긍정문으로 변경
insert, delete 등 데이터의 변경이 많은 컬럼은 인덱스를 걸지 않은 편이 좋음

인덱스를 만드는데 시간과 저장공간이 소비되고 만들고 난 후에도 추가적인 공간이 필요. 데이터를 변경 (insert, update, delete)를 하면 (특히 insert) 인덱스를 다시 조정해야하기 때문에 자원이 많이 소모됨

Index 생성

인덱스에는 크게 clustered와 nonclustered 인덱스로 나눌 수 있다. clustered 인덱스는 물리적 정렬로 DB에데이터를 입력 시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table을열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다. nonclustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다. 이 외에 unique에 대해 알아보겠다. unique는 말 그대로 중복을 허용하지 않는 값을 보호 할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다. 우리가 자주 사용하는 primary key의 경우에는 clustered와 unique 특성을 갖게 하는 제약키이다. Index를 생성할 때 clustered가 되어있을 때, index scan이 유리하다.

저작자표시 비영리 변경금지

PREV 1 NEXT

가치관제작소