INDEX의 의미?
RDBMS에서 검색속도를 높이기 사용하는 하나의 기술입니다.
INDEX는 색인입니다. 해당 TABLE의 컬럼을 색인화(따로 파일로 저장)하여 검색시 해당 TABLE의 레코드를 full scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 빠르게 합니다.
이런 INDEX는 TREE구조로 색인화합니다. RDBMS 에서 사용하는 INDEX는 Balance Search Tree 를 사용합니다.실제로는 RDBMS 에서 사용되는 B-Tree 는 B-Tree 에서 파생된 B+ Tree 를 사용한다고 합니다.
참고로 ORACLE이나 MSSQL에서는 여러종류의 TREE를 선택하여 사용가능하다.
INDEX의 원리?
INDEX를 해당 컬럼에 주게 되면 초기 TABLE생성시 만들어진 MYD,MYI,FRM 3개의 파일중에서 MYI에 해당 컬럼을 색인화 하여 저장합니다. 물론 INDEX를 사용안할시에는 MYI파일은 비어 있습니다. 그래서 INDEX를 해당컬럼에 만들게 되면 해당컬럼을 따로 인덱싱하여 MYI 파일에 입력합니다. 그래서 사용자가 SELECT쿼리로 INDEX가 사용하는 쿼리를 사용시 해당 TABLE을 검색하는것이 아니라 빠른 TREE로 정리해둔 MYI파일의 내용을 검색합니다.
만약 INDEX를 사용하지 않은 SEELCT쿼리라면 해당 TABLE full scan하여 모두 검색합니다.
이는 책의 뒷부분에 찾아보기와 같은 의미로 정리해둔 단어중에서 원하는 단어를 찾아서 페이지수를 보고 쉽게 찾을수 있는 개념과 같습니다. 만약 이 찾아보기 없다면 처음부터 끝까지 모든 페이지를 보고 찾아야 할것입니다.
INDEX의 장점?
보통 INDEX를 사용하지 않은 select쿼리와 INDEX를 사용한 쿼리의 검색속도는 6.5배가 차이납니다.이는 데이타양이 많아질수록 더욱더 차이납니다.
INDEX의 단점?
1.디스크용량 감소
인덱스를 사용하명 MYI파일에 추가 입력되기 때문에 디스크용량이 늘어납니다. 모든컬럼을 인덱스하면 데이타파일보다 인덱스파일이더 커질수도 있습니다. 적당하게 사용해야 합니다.
인덱스를 사용해도 디스크 용량은 염려할 정도로 많이 안먹으며 그에 비해 대부분의 경우는 퍼포먼스의 향상을 가져오게 되므로 좋은점이 더 많습니다.
2.INSERT,UPDATE속도 저하
해당 TABLE에 INDEX을 주게되면 INSERT,UPDATE가 조금 느려집니다. 왜냐하면 매번 해당 table과 table의 index를 검사해야 하기때문에 해당 table만 검사했을때보다 느리다.
INDEX의 목적?
RDBMS에는 INDEX가 있습니다. 인덱스의 목적은 해당 RDBMS의 검색 속도를 높이는데 있습니다.
SELECT 쿼리의 WHERE절이나 JOIN 예약어를 사용했을때만 인덱스를 사용되며 SELECT 쿼리의 검색 속도를 빠르게 하는데 목적을 두고 있습니다.
※ DELETE,INSERT,UPDATE쿼리에는 해당 사항없으며 INDEX사용시 좀 느려집니다.
INDEX 를 사용해야 하는 경우
데이터 양이 많고 검색이 변경보다 빈번한 경우
인덱스를 걸고자 하는 필드의 값이 다양할 값을 가질 경우
(성별과 같이 데이터의 값의 종류가 일정한 경우 인덱스 효과 없음)
INDEX 사용시 조심할점
Index가 가해지는 필드는 가능한 Null값이 없어야 한다.
한 테이블에 5개 이상의 인덱스 적용은 권장하지 않는다.
(이화식 씨의 대용량 데이터베이스 설계 중에서)
인덱스를 사용한 필드를 조건에서 연산,가공하여 사용하면 인덱스효과는 없다.
ex : Select * from 테이블 where 인덱스필드 * 10 > 100
--> Select * from 테이블 where 인덱스필드 > 100 / 10 으로 사용
INDEX가 동작하지 않는 경우
다음 연산자는 인덱스를 타지 않는다.
not,<> 는 인덱스 사용못함(= >= <= 는 사용가능)
like '%value' 와 like '%value%'는 인덱스 사용못함(like 'like%'는 사용가능)
조건 컬럼을 가공하거나 연산을 하면 인덱스를 사용 못합니다.
문자열 타입에 인덱스를 걸경우 150 바이트 이하까지만 인덱스가 적용됩니다.
INDEX의 적용?
TABLE 생성과 동시에 해당 컬럼에 INDEX 주기
CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),
INDEX index1 (name),
INDEX index2 (age)
);
CREATE TABLE member (
idx int not null auto_increment primary key,
name varchar(10),
age varchar(10),
INDEX index1 (name,age)
);
설명:맨뒤에 INDEX index (name) 라고 주며 INDEX는 index생성을 말하며 index1(name)은 index명을 index1이라고 하며 사용자가 임의로 결정해 줍니다. name컬럼에 준다는 애기입니다.
ex)
레코드가 100개 있을때 explain select * from member where name='13'; 을 하면 rows가 1이 나온다.만약 index을 사용안하면 100이 나온다. 이는 검색을 위해 100개의 레코드를 검사했다는 애기입니다 rows가 1인것은 레코드를 1개 검사했다는 애기입니다.
TABLE이 생성되어있는경우 컬럼에 INDEX 삽입하기
alter table member add index index1(name);
TABLE이 생성되어있는 경우 INDEX 삭제하기
show keys from member1;
--본 쿼리로 인덱스명 확인
drop table member1 index drop key_name;
--인덱스명으로 index삭제(컬럼명 아님)
desc member1;
--key컬럼을 보고 MUL(index지정) 삭제되었나 확인
INDEX의 주의점?
1.인덱스가 해당 컬럼에 줄때는 반드시 not null이어야 한다.
INDEX의 사용(varchar)?
CREATE TABLE member1(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name),
INDEX index2 (age)
);
insert into member1 (name,age) values ('11',11);
insert into member1 (name,age) values ('12',12);
insert into member1 (name,age) values ('13',13);
insert into member1 (name,age) values ('14',14);
CREATE TABLE member2(
idx int not null auto_increment primary key,
name varchar(10),
age int,
INDEX index1 (name,age)
);
insert into member2 (name,age) values ('11',11);
insert into member2 (name,age) values ('12',12);
insert into member2 (name,age) values ('13',13);
insert into member2 (name,age) values ('14',14);
CREATE TABLE member3(
idx int not null auto_increment primary key,
name varchar(10),
age int
);
insert into member3 (name,age) values ('11',11);
insert into member3 (name,age) values ('12',12);
insert into member3 (name,age) values ('13',13);
insert into member3 (name,age) values ('14',14);
explain select * from member1 where name ='13';
yes
explain select * from member2 where name ='13';
yes
explain select * from member3 where name ='13';
no
explain select * from member1 where age >10;
yes
explain select * from member2 where age >10;
no
explain select * from member3 where age >10;
no
설명 : 위와같이 age가 설정되면 index로 설정되면 age의 순서에 따라 정렬된 index를 가지기 때문에 레코드수의 증가와 거의 관계없이 query시간이 일정합니다. 그리고 당연히 order by age와 같이 정렬이 필요한 경우에도 따로 sort해줄 필요 없기 때문에 퀘리 시간이 짧아집니다.
explain select * from member1 where name ='13' and age =13;
yes
explain select * from member2 where name ='13' and age =13;
yes
explain select * from member3 where name ='13' and age =13;
no
explain select * from member1 where age =13;
yes
explain select * from member2 where age =13;
no
이유 : INDEX index1 (name,age)로 선언된 인덱스는 name,age의 값이 concate된 값이
index로 작용합니다.name이란 필드하나는 index의 역활을 하지 못합니다.
explain select * from member3 where age =13;
no
explain select * from member1 order by age;
no
explain select * from member2 order by age;
no
explain select * from member3 order by age;
no
explain select * from member1 where name='12' order by age;
yes
explain select * from member2 where name='12' order by age;
yes
explain select * from member3 where name='12' order by age;
no
explain select * from member1 where name like '12%' order by age;
yes
explain select * from member2 where name like '12%' order by age;
yes
explain select * from member3 where name like '12%' order by age;
no
explain select * from member1 where name like '%12' order by age;
explain select * from member1 where name like '%12%' order by age;
no
explain select * from member2 where name like '%12' order by age;
explain select * from member2 where name like '%12%' order by age;
no
explain select * from member3 where name like '%12' order by age;
explain select * from member3 where name like '%12%' order by age;
no
explain select * from member1 where name like '12%' or age =12 order by age;
no
explain select * from member2 where name like '12%' or age =12 order by age;
no
explain select * from member3 where name like '12%' or age =12 order by age;
no
INDEX의 사용(int,char,date)?
create table member(
idx int auto_increment primary key,
name char(20),
age int,
day date,
index index1(name),
index index2(age),
index index3(day)
);
insert into member values(null,'junsik1',11,date_add(now(),interval 1 day));
에서 차례대로
insert into member values(null,'junsik8',18,date_add(now(),interval 8 day));
까지 입력한다.
explain select * from member where day between '2005-07-19' and '2005-07-23';
index를 타지만 풀스캔을 한다.
explain select * from member where day = '2005/07/17'
yes
explain select * from member where name in('junsik1','junsik2');
yes
explain select * from member where age in('12','13','14');
no
explain select * from member where age between 12 and 14;
yes
explain select * from member where age < 14;
yes
