총 게시물 12건, 최근 0 건 안내 RSS 글쓰기
이전글  다음글  목록 글쓰기
[TIP&TECH]

MySQL 인덱스(INDEX) 정리 (1)

글쓴이 : 앱개발재밌어 날짜 : 2013-09-30 (월) 21:15 조회 : 12915
글주소 : http://www.androidside.com/B19/8
 
  아마존 AWS 공식사이트
AWS 클라우드 데이터베이스, 저렴한 비용, 쉬운 관리, 지금 1년무료체험 신청!
aws.amazon.com/ko/
  SBS아카데미컴퓨터학원지관훈
방학특강40%할인, 취업포트폴리오, 수강료조회, 국비지원, MYSQL
sbsdaejeoni.co.kr
  MYSQL 컴스쿨닷컴
MYSQL 인강만으로 합격가능, 단기완성, 무한반복 전 강좌 스마트폰 학습가능
www.컴스쿨.com
신청하기


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만 검사했을때보다

느리다.

 

인덱스 삭제

 

drop index 컬럼명 on 테이블명;

 

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 table_name;
-- 현재 table_name 테이블의 인덱스상태 상태확인

ALTER TABLE table_name drop INDEX key_name;

-- 현재 table_name 테이블의 key_name 인덱스키를 삭제
-- 쿼리에서 사용하지 않는 인덱스는 삭제하는게 좋다.
-- ex : ALTER TABLE g4_write_dr_tv_ta drop INDEX wr_num_2;

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

인덱스는 where절이 아닌 order by절에도 줘야 한다.
select * from jun_table where wr_is_comment = 0  order by wr_num, wr_reply   limit 0, 30;
위의 쿼리가 느릴때는 wr_is_comment 컬럼만이 아닌 복합인덱스로 wr_is_comment, wr_num, wr_reply 컬럼을 주면 효과적이다.


출처 : http://zetswing.com/bbs/board.php?bo_table=MySQL_LEC&wr_id=12




앱개발재밌어 님의 데이터베이스 최신글 [더보기]


추천 버튼을 클릭하면 추천한 자와 추천받은 자 모두에게 포인트가 지급됩니다.

사가정만발 2015-01-09 (금) 08:33
오호~ 좋은정보 감사합니다. ^^
댓글주소
quki 2015-08-31 (월) 14:27
정말 유용하네요
댓글주소
풀스택호프 2017-01-30 (월) 20:18
감사합니다!!!정말유용하게 보고갑니다.
댓글주소
hi
이전글  다음글  목록 글쓰기

총 게시물 12건, 최근 0 건 안내 RSS
번호 분류 제목 글쓴이 날짜 추천 비추천 조회
12 TIP&TECH
char대신 varchar를 쓰는이유??http://okky.kr/article/217655 정리가 잘되있는거 같아서 링크를 가지고 와봤습니다. char…
이힝힝 08-08 1 0 2143
11 소스코드
아래는 java 파일에 sql 쿼리문을 작성한 부분입니다. 여기서 궁금한게 DATETIME 은 테이블내에 있는 DATE 형식의 컬럼…
아르페우스 11-16 1 0 2935
10 TIP&TECH
기능설명 :   A 서버에 A DB와 B 서버에 B DB가 있을경우   1. A서버의 ADB에 테이블에 데이…
카이로 11-26 4 0 8276
9 TIP&TECH
### 원격서버의 MYSQL데이테베이스 백업하기   1) 원격서버의 MYSQL데이테베이스 백업하기 (기본포트 사…
카이로 07-12 6 0 7452
8 TIP&TECH
MySQL 인덱스(INDEX) 사용 방법  인덱스는 특정 칼럼 값을 가지고 있는 열을 빠르게 찾기 위해서 사용된다.&n…
앱개발재밌어 09-30 2 0 9931
7 TIP&TECH
1. 인덱스를 검사하는 법. explain select * from Table_Name where A='a' and B='b' order by C,D,E ; 해당 쿼리문이 인덱스를 …
앱개발재밌어 09-30 0 1 8799
6 TIP&TECH
INDEX의 의미?   RDBMS에서 검색속도를 높이기 사용하는 하나의 기술이입니다. INDEX는 색인입니다. 해당 TABLE의 …
앱개발재밌어 09-30 4 0 12916
5 TIP&TECH
안녕하세요. 디비구조를 만만하게 생각하고, 엑셀로 끄적이며 설계해보려 했으나, 만만하지가 않더군요. …
앱개발재밌어 09-26 0 0 9377
4 소스코드
샘플긁어 온건데 테스트 함 해보세요
레오니아 08-08 14 0 10010
3 강좌
출처 : http://www.phpschool.com/bbs2/inc_view.html?id=9464&code=tnt2[MYSQL] LIKE vs INSTR()0. 배경1. 영문 검색어 테스트  1-1…
카이로 04-29 2 0 8887
2 강좌
아래 내용은 Ubuntu/GNU Linux 11.04 를 기반으로 한다. - MySQL 서버 중지 후 데이터 디렉토리의 내용을 새 디렉토리로 …
카이로 04-25 0 0 8830
1 강좌
ibdata1 파일은 MySQL의 innodb 로그 파일로 줄일 수 없다. 단, 다음 과정을 통해 삭제 후 재생성할 수 있다. 1. 전체 데…
카이로 04-25 0 0 13719
 


Copyright ⓒ www.androidside.com. All rights reserved.
채팅 권한: 글쓰기 1개
2레벨 이상만 대화 가능
공개 채팅: 평일 !(9시 ~ 17시),토,일
안사2 변경사항 보러가기 챗방이 잘 안보이면 크롬에서 접속해주세요
챗방 숨기기 |  챗방 보이기