mysql Index 와 다중 컬럼 인덱스

Index 개요

 

Index는 MYI (MySQL Index)파일에 저장 됩니다. Index가 설정되지 않았다면 Table Full Scan이 일어 나 성능이 저하 되거나 치명적인 장애가 발생 합니다. Index는 검색 속도를 향상 시키는 장점이 있지만, update, insert, delete 속도는 저하 됩니다. 이는 Table의 Index 색인 정보를 갱신하는 비용이 추가 되기 때문입니다. Index 설정시 이런 부분들을 위의 하여 효율적인 컬럼 선정이 이루어 져야 합니다.

 

주 목적은 다음과 같습니다.

 

조건과 일치하는 열을 빨리 찾기 위해 사용.

JOIN시 다른 Table의 열을 추출 하기 위해 사용.

Max, Min값을 찾기 위해 사용.

 

 

다음과 같은 사항을 고려 하여 사용하면 좋습니다.

 

Index의 키의 크기는 되도록 작게 설계해야 성능에 유리 합니다.

분포도가 좋은 컬럼(좁은 범위), 기본 키, 조인의 연결 고리가 되는 컬럼을 Index로 구성합니다.

단일 인덱스 여러 개 보다 다중 컬럼 Index의 생성을 고려 합니다.

Update가 빈번하지 않은 컬럼으로 인덱스를 구성하는게 좋습니다.

Join시 자주 사용하는 컬럼은 Index로 등록하는게 유리 합니다.

되도록 동등 비교(=)를 사용합니다.

조건절에 자주 사용하는 컬럼은 Index 추가를 고려 합니다.

Index를 많이 생성하면 Insert/Update/Delete의 성능 저하의 원인이 될 수 있습니다.

Index scan이 Table 순차 sacn보다 항상 빠르지 않습니다. 보통 선택도(selectivity)가 5~10% 이내인 경우 Index scan이 우수합니다.

S (selectivity) = d/n

d = 서로 다른 값의 수 (# of distinct values)

n = 테이블의 전체 레코드 수

 

Index를 생성할때 가장 효율적인 자료 형은 정수형 자료 입니다. (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT등)

가변적인 크기와 정규화 할수 없는 데이터는 인덱스를 생성할때 비효율적으로 동작 합니다 (Text 데이터 등) 이를 위해 FULLTEXT INdex를 사용할 수 있습니다. FullText 는 TEXT, BLOB, VARCHAR등 가변적이고 일반적인 Index의 효율성이 떨어지는 부분에서 많은 효과를 가져올 수 있는 형태의 Index 입니다.

FULLTEXT Index는 텍스트 필드에 '%검색문자열%'와 비슷한 형태의 검색 결과를 얻을 수 있고 Text에 최적화된 Index 방식입니다. MySQL 5.6 버전부터는 InnoDB 에서도 지원 합니다.

 

Index를 사용한 필드의 조건 검색이  not, <, > 일 경우 Index를 사용하지 않습니다. ( =>  , <= , =, Between 은 사용이 가능합니다.)

Index를 사용한 필드의 조건 검색이 like '%값' 혹은 '%값%'인 경우 Index를 사용하지 않습니다. (like '값%'은 사용 가능 합니다.)

Index를 사용한 필드의 조건 검색이 다른 필드와 비교일 경우 Index를 사용하지 않습니다. (where 컬럼1=컬럼2)

 

order by 와 group by에 대한 Index

Index는 order by와 group by에도 영향을 끼칩니다. 다음과 같은 경우에는 Index를 타지 않습니다.

 

order by 인덱스 컬럼1,컬럼2  : 복수의 키에 대해서 order by를 사용한 경우

where 컬럼1='값' order by 인덱스 컬럼  : 연속하지 않은 컬럼에 대해 order by를 실행한 경우

order by 인덱스컬럼1 desc, 인덱스컬럼2 asc  : desc와 asc를 혼합하여 사용한 경우

group by 컬럼1 order by 컬럼2  : group by와 order by의 컬럼이 다른 경우

order by abs(컬럼)  : order by절에 다른 표현을 사용할 경우

 

order by 동작

1. where절에 일치하지 않는 열은 제외하고 키순서에 따라 모든 열을 읽어 들입니다.

2. 각각의 열에 대해 버퍼에 정렬을 위한 키와 열에 대한 포인터의 쌍을 저장합니다. (이때 사용되는 버퍼의 크기는 sort_buffer_size에 의해 결정 됩니다.)

3. 버퍼가 가득 차면, 퀵소트를 수행하고 그 결과를 임시 파일에 보관한 뒤 다시 반복 합니다. (sort_buffer_size의 크기에 다 담길 양이면 임시 파일을 만들지 않습니다.)

4. 다른임시 파일에 대해, 최대 MERGEBUFF(기봉밧:7)의 영역을 하나의 블록이 될 때까지 다중 병합작업을 수행합니다. 첫번째 파일의 모든 블럭이 두번째 파일과 같아질 때까지 반복합니다.

5. MERGEBUFF2(기본값:15)보다 작은 수의 블록이 남을 때 까지 반복합니다.

 

order by에 대한 index를 사용하지 못할 경우는 sort_buffer_size와 read_rnd_buffer_size의 크기를 증가 시킵니다.

 

추가 삭제.

인덱스 보기

show index from 테이블명

 

인덱스 추가
alter table 테이블명 add index 인덱스명 (칼럼명);

 

인덱스 삭제
alter table 테이블명 drop index 인덱스명;

 

다중 컬럼 인덱스 (Multiple-column Index)

다중 컬럼 인덱스는 두개 이상의 필드를 조합하여 생성한 Index 입니다. 첫번째 조건과 이를 만족하는 두번째 조건을 함께 Index 하여 검색 성능 향상을 위해 사용 됩니다. MySQL은 하나의 Index에 최대 15개의 컬럼으로 구성될 수 있습니다.

 

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 Index/Update/Delete를 수행하기 때문에 사용에 신중해야 합니다. 가급적 업데이트가 안되는 값을 선정하야 사용하는게 좋습니다.

 

테이블1

Create Table tb_name(
   uid int(11) not null auto_increment,
   id varchar(20) not null,
   name varchar(50) not null,
   address varchar(155) not null,
   primary key('uid'),
   key idx_name(name),
   key idx_address(address)
)

 

테이블2

 Create Table tb_name(
   uid int(11) not null auto_increment,
   id varchar(20) not null,
   name varchar(50) not null,
   address varchar(155) not null,
   primary key('uid'),
   key idx_index(name,address)
 )

 

단일 index와 다중 컬럼 인덱스의 경우를 차이점에 대해 알아 봅니다.

 

select * from tb_name where name='홍길동' and address='경기도'

 

테이블1의 경우 각각의 필드에 Index가 설정되어 있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 됩니다.

 

테이블2의 경우 바로 원하는 값을 찾습니다. 그 이유는 Index를 저장할때 name가 address를 같이 저장하기 때문입니다. 즉 name 과 address의 값을 함께 색인을 한 뒤 검색에서도 홍길동경기도 로 검색을 시도하게 됩니다. 이렇게 사용할 경우 테이블1의 경우 보다 테이블2의 경우가 더 빠른 검색을 할 수 있습니다.

 

하지만 다중 컬럼 인덱스를 다음과 같이 사용 한다면 Index를 타지 못합니다.

 

select * from tb_name where address='경기도'

 

이경우 name이 함께 검색이 되지 않으므로 Index의 효과를 볼 수 없습니다.

다중 컬럼 인덱스를 사용할때는 index의 제일 왼쪽 컬럼이 where 절에 사용 되어야 합니다.

 

출처 : code-factory.tistory.com/24?category=724516