[MySql] 효과적인 인덱스 설계

인덱스(index)는 즉 색인이다

테이블의 동작속도(조회를) 높여주는 자료구조이다. 인덱스로 데이터의 위치를 색인 처럼 빠르게 찾아주는 역할이다.

select를 빠르게 하는 대신 update, delete, insert를 희생한다. 물론 update, delete 라고 다 희생되는게 아니라 

update, delete를 하기 위해 해당 데이터를 조회하는 인덱스가 있으면 빠르게 된다.

만약 인덱스를 사용하는 테이블에서는 update, delete를 하게 되면 가능한 인덱스로 지정된 컬럼을

기준으로 진행하는게 좋다

테이블의 특정 컬럼(Column)에 인덱스를 생성하면, 해당 컬럼의 데이터를 정렬한 후 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 컬럼의 값과 물리적 주소를 (key, value)의 한 쌍으로 저장한다. 

 

인덱스 컬럼 기준

만약 1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을

잡아야 한다는 점입니다.

카디널리티(Cardinality)란 해당 컬럼의 중복된 수치를 나타냅니다.
보통 성별, 학년 등은 중복도가 높고 카디널리티가 낮다고 얘기합니다.
반대로 주민등록번호, 계좌번호 등은 중복도가 낮고 카디널리티가 높다고 얘기합니다.

중복도가 낮은 컬럼으로 인덱스를 해야하는 이유는

인덱스로 최대한 많이 걸러내야 하기 때문에 그렇습니다.

 

인덱스 구성시 기준

CREATE TABLE table(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name, address) -- 다중 컬럼 인덱스
)

 

select * from table where name = '스프링' and address = '서울';

이런 식으로 사용한다.

꼭 다중 컬럼 인덱스라고 해서 컬럼을 다 사용할 필요는 없습니다.

 

select * from table where name = '스프링';

그러나 이 쿼리는 인덱스를 타지 않습니다.

조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야만 합니다.

 

select * from table where address = '서울';

이 쿼리는 인덱스를 사용하는 쿼리다.

 

인덱스 조회시 주의사항 및 설계 방법

• 무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 조회시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을 수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)
• between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않습니다.
  • 즉, group_no, from_date, is_bonus으로 인덱스가 잡혀있는데 조회 쿼리를 where group_no=XX and is_bonus=YY and from_date > ZZ등으로 잡으면 is_bonus는 인덱스가 사용되지 않습니다.
  • 범위조건으로 사용하면 안된다고 기억하시면 좀 더 쉽습니다.
• 반대로 =, in 은 다음 컬럼도 인덱스를 사용합니다.
  • in은 결국 =를 여러번 실행시킨 것이기 때문입니다.
  • 단, in은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생합니다.
  • in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고, in 은 체크조건으로 실행되기 때문입니다.
• AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.
  • WHERE 에서 OR을 사용할때는 주의가 필요합니다.
• 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용됩니다.
  • 인덱스는 가공된 데이터를 저장하고 있지 않습니다.
  • where salary * 10 > 150000;는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.
  • 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.
• null 값의 경우 is null 조건으로 인덱스 레인지 스캔 가능