MySQL Index(인덱스) 조회, 생성, 수정, 삭제 및 사용 최적화 방법

MySQL Index(인덱스)

테이블의 데이터를 조회하는 성능을 향상시키기 위해 생성된 데이터

테이블과는 별도로 존재

테이블의 데이터를 생성/변경/삭제하면 관련된 Index도 변경

*장점:

테이블의 데이터를 빠르게 조회

Unique등의 제약 조건을 부여할 수도 있다.

정렬을빠르게할수있으며,GroupBy를빠르게할수있다

*단점:

테이블에 저장된 데이터가 차지하는 공간 이외에 저장 공간이 더 필요

테이블에 저장된 데이터가 자주 변경될 경우 관련 Index도 함께 변경해야 하는 오버헤 드로 인하여 성능이 나빠질 수 도 있다.

*특징

Primary Key, Unique Key, Key로 구분하기도 한다.

InnoDB Storage에 저장되는 테이블은 Primary Key에 따라 클러스터화 된다.

보조 Index(Secondary Key)는 내부적으로 Primary Key를 포함하여 생성된다

 

mysql 인덱스 확인하는 방법

show index from employees;

위 쿼리를 이용해서 mysql index를 확인 할 수 있다.

 

show index from employee; ----1
select * from information_schema.statistics
where table_name = 'employee'; ---2
select * from mysql.innodb_index_stats
where table_name = 'employee'; ---3

show index 명령어로 인덱스가 확인 되지 않을 경우, innodb_index_stat로 확인할 수 있는 방법도 있다.

(내부적으로 clust index를 만들어서 사용한다.)

 

alter table my_table
add primary key(id);

PK를 지정한 후, 인덱스를 확인하면,

show index from my_table;

 

mysql.innodb_index_stats를 이용하여 조회하게 되면,

select * from mysql.innodb_index_stats
where table_name = 'my_table';

PK 지정 후 mysql.innodb_index_stats 이용하여 조회해보면 Clust에서 Primary로 변경됨을 확인 할 수 있다.

 

*Primary key를 지정하면 Index는 자동 생성된다. 

FK의 경우, MySQL에서는 외부키(Foreign Key)를 지정하면 Index는 자동 생성(오라클은 아님!)

 

mysql index 인덱스 생성 쿼리:

CREATE [UNIQUE| FULLTEXT | SPATIAL] INDEX index_name [index_type] on table_name(column_names)[index_option]
[algorithm_option | lock_option]
column_names : col_name[(length)] [ASC | DESC]

위 인덱스 쿼리를 실제 테이블에 적용해보면,

# salary index 설정
create index emp_salary_ix
on employees(salary);

 

mysql index 인덱스 삭제 쿼리

drop index index_name on table_name;
->drop index emp_salary_ix on employees;

mysql Index를 삭제하는 것이 바람직한 경우

-대용량의 데이터를 테이블에 넣는 경우(Index를 삭제하고 데이터를 넣는 것이 바람직)

-사용하지 않는 Index가 있는 경우