인덱스 (데이터베이스)
인덱스(영어: index)는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다. 인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다. 고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다. 인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작다. (왜냐하면 보통 인덱스는 키-필드만 갖고 있고, 테이블의 다른 세부 항목들은 갖고 있지 않기 때문이다.) 관계형 데이터베이스에서는 인덱스는 테이블 부분에 대한 하나의 사본이다.
인덱스는 고유 제약 조건을 실현하기 위해서도 사용된다. 고유 인덱스는 중복된 항목이 등록되는 것을 금지하기 때문에 인덱스의 대상인 테이블에서 고유성이 보장된다.
+ 인덱스는 특정 칼럼 값을 가지고 있는 열 혹은 값을 빠르게 찾기 위해서 사용된다.
+ MySQL은 첫 번째 열부터 전체 테이블에 걸쳐서 연관된 열을 검색하기 때문에 테이블이 크면 클 수록 비용이 엄청나게 늘어난다.
+ 만약 테이블이 쿼리에 있는 컬럼에 대한 인텍스를 가지고 있다면,
+ MySQL은 모든 데이터를 조사하지 않고도 데이터 파일의 중간에서 검색위치를 빠르게 잡아낼 수 있다.
+ 인덱스 위주 검색 후, 관련된 혹은 원하는 데이터들을 가져올 수 있다고 생각할 수도 있다.
인덱스를 사용하는 이유
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)
출처: http://ra2kstar.tistory.com/96 [초보개발자 이야기.]
++++
프로시져 내부에서 인덱스를 사용하여 속도 향상을 시켜 보자.
프로시져를 이용하여 2년치의 데이터를 가져올 때, 30초의 대기 시간을 부여한 결과 TimeOut의 결과를 가져왔다.
따라서 프로시져 내부에 인덱스를 부여한 결과 놀라운 속도의 차이를 가져왔다.
프로시져 내용은
create temporary table tmptbl (cons char(10), custm varchar(60), phone char(50), group char(2), use char(4),
inspe char(8), cd char(8), consl char(5),
status char(1), no char(12), reque char(8), INDEX `index1` (consl_no));
프로시져 내에서 update 구문이 있어서, 더 오래 걸린 이유도 되었습니다.
하지만 인덱스를 추가한 이후 연관된 데이터의 업데이트 및 select 문이 빠르게 이루어지기 때문에,
타임아웃에도 안걸리고 속도는 향상되는 결과를 얻을 수 있었습니다.
update tmptbl a, something b set a.reque = b.reque,
where a.consl_no=b.consl_no;
이처럼 Index는 조건절에 따른 구분이 들어가 있는 것을 선택할 수록 효과는 더 좋습니다.
---
직접 느껴 본 것만 적다 보니깐, 이론적으로 안 맞는 경우도 있는데,
제가 놓친 정확한 정보와 지식은 댓글로 남겨주시면 감사하겠습니다.
