MySQL Index 관리편

요새 MySQL을 다루고 있어서 검색하는 중에 괜찮은 글이 있어 정리해둔다.

 

테이블 설계 시 유의 사항

 

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

로그 성 테이블에도기본적으로 PK 생성을 원칙으로 함

InnoDB에서 PK는인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

 

2. 테이블 Primary Key는 auto_increment를 사용한다.

InnoDB에서는 기본키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능

InnoDB의 PK는절대 갱신되지 않도록 유지

(갱신 시 갱신된 행이후 데이터를 하나씩 새 위치로 옮겨야 함)

 

3. 데이터 타입은 최대한 작게 설계한다.

시간정보는MySQL데이터 타입 date/datetime/timestamp 활용

IP는INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용

정수 타입으로 저장가능한 문자열 패턴은 최대한 정수 타입으로 저장

 

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

NULL을 유지를 위한추가 비용 발생

(NULL 허용 칼럼을인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

 

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

데이터 및 인덱스파일이 커질수록 성능이 저하되므로Partitioning 유도

PK 존재 시 PK내부에 반드시 Partitioning 조건이 포함되어야 함

 

인덱스 설계 시 유의 사항

 

1. 인덱스 개수를 최소화 한다.

현재 인덱스로Range Scan이 가능한지 여부를 사전에 체크

인덱스도 서버 자원을소모하는 자료구조이므로 성능에 영향을 줌

 

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

인덱스 칼럼 데이터의중복이 줄어들수록 인덱스는 최대의 효과를 가짐

하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함

 

SELECT count(distinctINDEX_COLUMN)/count(*)

FROM TABLE;

 

3. 커버링 인덱스(Covering Index)를 활용한다.

쿼리 조건이 인덱스안에 포함된 경우 인덱스에서만 연산 유도

인덱스는 일반적으로 행전체보다 작으므로 불필요한 Disk I/O 회피 가능

“MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!” 편 참고

 

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

InnoDB에서데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐

MyISAM은 PK와일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용

(MyISAM 엔진에서ORDER BY 시 DESC는 가급적 지양)

 

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

긴 문자열 경우Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용

 

CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))

 

Prifix Size는앞 글자 분포도에 따라 적절하게 설정

(하단 결과가 1에가까울 수록 최적의 성능 유지, 0.9이상 권고)

 

SELECT count(distinct LEFT(INDEX_COLUMN,3))/count(*)

FROM TABLE;

 

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

URL/Email같이문자 길이기 긴 경우 유용

INSERT/UPDATE발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱

CRC32 결과값을저장할 칼럼 추가 및 인덱스 생성

 

alter table user_tbl add email_crc intunsigned not null;

create index idx01_email_crc on user_tbl (email_crc);

 

InsertTrigger 생성

 

create trigger trg_user_tbl_insert

beforeinsert on user_tbl

for each row

begin

set new.email_crc = crc32(lower(trim(new.email)));

end$$

 

UpdateTrigger 생성

 

create trigger trg_user_tbl_update

before update on user_tbl

for each row

begin

if old.email<>new.email then

setnew.email_crc = crc32(lower(trim(new.email)));

end if;

end$$

 

검색 쿼리

 

select *

from user_tbl

where email_crc = crc32(lower(trim('mail@domain.com')))

and email='mail@domain.com'

 

CRC32 결과가중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

 

7. 중복 인덱스 생성 회피

MySQL은 동일한인덱스를 중복 생성해도 에러를 발생하지 않음

Primary Key로구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의

출처 : http://dev.kthcorp.com/2012/05/15/mysql-table-index-design-tip/