[데이터베이스] MySQL 인덱스

1. InnoDB

Clustered index

MySQL은 InnoDB에서 작동하는 SQL이다. 그리고 이 InnoDB는 테이블의 기본 키(PK)를 인덱스로 하여 사용하며 동기화 되어 저장한다. 다시 말해 데이터가 변경, 삭제, 삽입이 발생하면 기본 키에 대하여 데이터 레코드를 정렬을 한다. 만약 기본키가 없는 테이블은 not null인 Unique Key를 사용하고 그것마저도 없으면 내부적으로 숨겨진 Row ID를 사용하여 사용하며 새로운 열에 대하여는 단계적으로 증가한 ID값을 넣어 사용한다. 

Secondary index 

InnoDB는 Clustered index를 제외한 나머지 인덱스를 보조 인덱스로 사용한다. 이론적으로 non-clustered index를 사용하면 해당 레코드의 RID(Row ID)로 데이터 레코드에 접근하는데 InnoDB에서는 RID 대신 기본 키 로 대체하여 사용한다. 따라서 기본 키의 크기가 매우 긴 경우에는 보조 인덱스의 크기가 비대해질수도 있으므로 기본키의 크기를 작게 사용하는 것이 좋다.

2. Index Query

index 생성

1. 기본 키 또는 Unique key 선언된 테이블 생성 시 자동으로 생성

create table [ table_name ] ( column_name 자료형(크기)....  

PRAMARY(or UNIQUE) KEY( [ column_name ] ));

2. 기본 키가 없는 테이블에 기본 키를 지정하는 경우

alter table [ table_name ] ADD PRIMARY KEY [ column_name ]

3. 직접 index 생성

create index index_name using [ BTREE | HASH ] on table_name(column_name);

Index Hint

DB가 보통 최적의 인덱스를 선택하는 경우도 있지만 인덱스의 선택이 옳지 못한 경우에는 사용자가 직접 인덱스 힌트를 줘서 사용을 강제로 하거나 사용을 못하게 막을 수 있다. 

select * from [ table_name ] ( use | force | ignore ) index [ index_name ] (이하 조건절 생략)

use : 인덱스를 써 볼 것을 권유... (최적화를 고려하므로 강제성이 없다.)

force : 강제로 인덱스 사용. (Table Scan이 더 효율적인데도 강제적으로 사용한다) 

ignore : 해당 인덱스를 사용을 금지한다.

Index List

해당 테이블에 어떤 인덱스가 있는지 확인할 수 있는 쿼리

show index from [ table_name ]