Mysql Index 설정시 참고 사항

개발을 하다보면 DB의 속도로 인해 Index를 설정해야 할 경우가 많이 있다.
이때 아무 생각 없이 index를 설정하다보면 오히려 성능을 저하 시키는 경우가 있다
어떤 경우에 index를 설정해야 좋은 퍼포먼스를 보이게 되는지에 대해 살펴 보자

1. index란?

Index는 하나의 컬럼에서 특정한 값을 찾을때 즉 Select 를 할때 주로 사용이 된다.
Index는 색인이라고 생각하면 되는대 Table을 생성할때 만들어지는 MYI파일에 컬럼을 색인화 하여 저장한다.
그래서 사용자가 select 쿼리를 전송하면 Table을 검색하는게 아니라 정리해둔 MYI파일의 내용을 검색한다.
만약 Index를 설정하지 않는다면 Mysql은 Table Full Scan을 하여 검색한다.

참고로 Index에 사용되는 구조는 Tree 구조로 B-Tree B+ Tree 등이 있는대 Mssql과 Oracle에선 여러종류의
Tree를 선택하여 사용이 가능하다.


2. Index 사용시 단점

Index의 장점은 단연 Select의 속도가 빨라진다는것이다.
하지만 장점이 있으면 단점이 있는대 이는 바로 update와 Insert의 속도가 느려진다는 것이다.
왜냐 하면 Update나 Insert가 전송되면 Mysql은 이미 색인된 Index를 update해야 하기 때문에
Select는 빨라지지만 Update나 Insert는 느려지게 된다.
그래서 자주 Update나 Insert가 이뤄지는 컬럼에 Index가 있는 경우 Lock이 걸리거나
Mysql이 죽어 버리는 경우가 생기게 된다. 이는 서비스에서 치명적인 부분이 될 수 있으므로
Index를 설정시 유의 해야 한다.


3. Index 사용시 주의할 부분

Index는 null 이면 안된다.
Index를 사용할 필드의 값이 일정할 경우(성별/양력/음력 등 데이터가 일정한 경우는 효과가 크지 않음)
Index를 사용한 필드의 조건 검색에서 연산이 이뤄질 경우( where 컬럼*10>1000 )
Index를 사용한 필드의 조건검색이 not, <, > 일 경우(=>,<=,=,Between 은 사용이 가능하다.)
Index를 사용한 필드의 조건검색이 like '%값'이거나 like '%값%' 일 경우 (like '값%' 은 사용가능)
Index를 사용한 필드의 조건검색이 다른 필드와 비교일 경우 (where 컬럼1=컬럼2 ) 


4. Multiple-column Index 란

다음의 Table을 만들때 index를 거는 쿼리를 보자

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)
)

이렇게 만드는 경우와

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)
 )

이렇게 만드는 두가지의 경우가 있다 이중 Multiple-column Index는 밑의 경우를 말하는것이다.
그럼 다음 쿼리에 대해 두개의 index가 어떤식으로 동작할까?

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

첫번째의 경우 각각에 index가 설정되어 있기때문에 mysql은 name컬럼과 address컬럼을 보고
둘중에 어떤 컬럼의 수가 더 빠르게 검색이 되는지 판단후 빠른쪽을 먼저 검색을 하고
그 다음 다른 컬럼을 검색하게 된다.
즉 name에서 검색할 행수가 적다면 name을 검색한뒤 address 컬럼을 검색하게 된다.

두번째의 경우는 바로 원하는 값으로 찾아가게 되는대 그 이유는
index를 저장할때 name 과 address 를 같이 저장하기 때문이다.
즉 name과address의 값을 함께 색인을 한뒤 검색에서도 홍길동경기도 로 검색을 시도하게 되는것이다.
이렇게 사용하게 될경우 첫번째보다 빠른 속도의 검색을 할 수 있게 된다.

하지만 multiple-column index에서도 index를 타지 못하는 경우가 있는대 다음과 같은 경우이다.
select * from tb_name where address='경기도' // 이경우는 name이 함께 검색이 되지 않으므로 index의 효과를 볼수 없다.
즉 multiple-column으로 index를 걸때 제일 왼쪽의 컬럼이 where 절에 사용이 되야 한다는것이다.


5. 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의 크기를 증가 시킨다.