안녕하세요 박성원입니다.

multi inex에 대한 문의군요!! 이거참 초반에는 매우 힘든 개념이지만 한번 집고 넘어가면 그리 어렵지 않습니다. ^^

자 우선 질문에 줄바꿈이없으므로 임의로 보면

1. index

KEY `ixs` (`f1`,`f2`,`f3`,`f4`) USING BTREE

2. index

KEY `ix_f1` (`f1`) USING BTREE
KEY `ix_f2` (`f2`) USING BTREE
KEY `ix_f3` (`f3`) USING BTREE
KEY `ix_f4` (`f4`) USING BTREE
 

이 두가지의 차이를 말씀하시는게 맞죠 ^^?

우선 멀티키에 대한 이야기 부터 진행할께요

멀티키는 특정쿼리의 where절 구문이 항상 동일한 루틴으로 접근할때 단순히 여러개의 컬럼을 묶어서 사용하는 방법입니다.

즉,

select a, b from table where a=1 and b=2

select a, b, c from table where a=1 and c=3

select c from table where a=1

이런식으로 말이죠 ^^

즉, unique한 특정 컬럼을 반드시 포함하고 몇개의 컬럼이 where절에 포함된다면 이는 항상 비슷한 패턴으로 들어오기 때문에 multikey를 생성하여 index를 타게 할 수 있습니다.

 (* 기준 컬럼이 항상 Unique 할 필요는 없습니다. 타 컬럼보다 구분되는 값이 많으면 좋겠죠 ^^)

단일 key의 경우에는

select a, b from table where a=1 and b=2

select b, c from table where b=2 and c=3

즉, 들어오는 query에 특정 패턴이 없으며 쿼리가 다양할 경우입니다.

이때는 단일컬럼으로 쿼리를 잡기 때문에 어떠한 경우던지 인덱스를 타게 되는것이지요 ^^

흠.. 어렵나요?

정리를 하면

multikey의 경우 묶어둔 index는 최상위 key(상기에서는 unique한 컬럼을 말합니다.)를 반듯이 포함해야

index를 거칩니다.  물런, 이것이 아니라해도 index scan의 형태로 검색은 합니다만, 속도의 차이가 많이 나겟죠?

single index의 경우 각개별의 index를 만들기 때문에 select나 where절 어디에서든지 index를 매우 효율적으로 사용할 수 있습니다.

흠.. 그럼 여기서 한가지 문의가 생기죵? 왜 굳이 multikey를 사용해서 index를 구성할까? 입니다.

네.. 조금 생각해보면 답이 있겠지요 ^^

index라는것은 data가 insert 될때마다 증가하도록 설계되어있습니다. 당연하죠? 데이터가 있으면 당연히 그에 해당하는 index가 늘어날테니까요 ^^

단일키의 경우 몇개의 index를 동시에 만들기때문에 multikey에 비해서 index file 자체보다 큰 용량을 필요로 하게 됩니다.

실제로 무분별한 index의 남용은 효율적이지 못하고 disk의 큰 i/o만 발생시키기도 합니다.

그럼 이정도로 정리를 해볼까요 ^^

multikey는 실무에서 많이 사용되는 개념입니다. 이 기회에 꼭 정리하시길 바랍니다.

그럼 건승하시길....

성원님의 정성 넘치는 답변이군요 ^^ 요새 좀 불타오르시는 듯~

추가로 말씀드리면, 멀티 컬럼 인덱스를 쓰는 다른 이유는 MySQL에서 하나의 대상에 대한 WHERE 구문 처리시, 하나의 인덱스만을 쓸 수 있기 때문입니다. 일반적으로 용량에서는 컬럼별로 나누는 것이 더 적게 나옵니다. ( f1의 Cardinality가 100, f2가 100 일때, 별도로 잡으면 100+100=200 이지만, 멀티키로 잡으면 100x100 = 10,000이 됩니다. 정확하게 이런 크기라는 얘기가 아니라 대략적으로 그렇습니다. )  -> 성원님의 제보로 확인해보았는데, 싱글키를 각각 따로 만드는 것의 용량이 더 크게 나옵니다. 감사합니다.

WHERE f1='...' AND f2='...' 과 같은 조건이 들어 왔을 때, 제시해주신 인덱스들 중 일반적인 경우라면 ixs를 통해 가장 빠르게 답을 찾을 수 있습니다. ix_f1과 ix_f2가 별도로 있다면 두 인덱스 중 Cardinality가 높은 값을 기준으로 하나의 인덱스만 사용하게 됩니다.