MySQL 인덱스

데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.

실무에서는 현실적으로 인덱스 없이 데이터베이스의 운영이 불가능하다.

 

인덱스의 개념

책의 색인 또는 찾아보기와 유사한 개념이다.

ex) 책의 내용 중 찾아보고 싶은 내용이 있다면 책의 제일 뒤에 수록되어 있는 색인을 열어보고 원하는 단어를 찾아서 해당 페이지를 빠르게 확인할 수 있다. 만약 색인이 없는 책이라면 책을 첫 페이지부터 넘겨가며 확인하는 수 밖에 없다.

 

MySQL 블로그 정리에 사용한 테이블은 인덱스를 고려하지 않았는데, 이는 색인이 없는 책과 마찬가지로 테이블을 사용한 것이다.

인덱스가 없었음에도 문제가 되지 않았던 이유는 데이터의 양이 적었기 때문이다.

하지만 실무에서 운영하는 테이블은 인덱스의 사용 여부에 따라 성능 차이가 날 수 있고, 대용량의 테이블일 경우에는 그 차이가 더욱 커진다.

 

인덱스 사용 여부에 따른 결과값의 차이는 없지만 시간이 오래걸리므로 인덱스를 사용하는 것이 좋다.

 

인덱스의 문제점

필요없는 인덱스를 남용하면 데이터베이스의 용량만 늘어나고 속도가 더 느려질 수 있다.

데이터베이스에 인덱스를 생성해두더라도 MySQL이 인덱스를 사용하는 것과 전체 테이블을 검색하는 것 중에서 더 빠른 방법을 판단해서 사용한다.

따라서 사용하지 않는 인덱스를 만들면 쓸데없이 공간낭비만 하게된다.

 

인덱스의 장점

기존보다 아주 빠른 응답속도를 얻을 수 있다.

컴퓨터는 작은 처리량으로 요청한 결과를 빨리 얻을 수 있으니 여유가 생기고 추가로 더 많은 일을 할 수 있게된다.

결과적으로 전체 시스템에 성능이 향샹되는 결과를 볼 수 있다.

 

인덱스의 단점

인덱스도 공간을 차지하는 데이터이기 때문에 데이터베이스 안에 추가적인 공간이 필요하다.

처음 인덱스를 만드는 데  시간이 오래 걸릴 수 있다.

select가 아닌 데이터의 변경 작업이 자주 일어나면 오히려 성능이 나빠질 수 있다.

 

자동으로 생성되는 인덱스

인덱스는 테이블 컬럼단위에 생성되며, 하나의 열에는 하나의 인덱스만 생성할 수 있다.

하나의 열에 여러개의 인덱스를 생성하거나 여러 개의 열을 묶어서 하나의 인덱스를 생성하는 것은 가능하지만 극히 드물다.

테이블에서 기본키를 지정하면 자동으로 클러스터형 인덱스가 생성된다.

클러스터형 인덱스(Clostered Index)

영어사전처럼 책의 내용이 이미 정렬되어 있는 것이다.

기본키는 테이블에 하나만 지정할 수 있으므로 클러스터형 인덱스는 테이블에 한 개만 만들 수 있다.

 

테이블 생성하기

create table table1(
	col1 int primary key,
	col2 int,
	col3 int
);

테이블의 인덱스 확인하기

show index from table1;

• Key_name : 'PRIMARY'는 '기본키로 설정해서 자동으로 생성된 인덱스' 라는 의미이다(클러스터형 인덱스)
• column_name : 해당 컬럼에 인덱스가 만들어져 있다는 뜻이다.
• non_unique : '중복이 혀용된다' 라는 뜻으로 0이면 중복이 허용되지않고 1이면 중복이 허용된다.

 

고유 인덱스(Unique Index)

인덱스의 값이 중복되지 않는다는 의미이며 반대 의미로는 단순 인덱스(Non_unique Index)가 있다.

기본키(Primary key)나 고유키(unique)로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다.

고유키도 인덱스가 자동으로 생성되며, 고유키로 생성되는인덱스는 보조 인덱스이다.

보조 인덱스(Secondary Index)

책의 색인과 같이 단어를 찾고 단어의 주소를 찾아야 실제 내용이 있는 형태이다.

 

create table table2(
	cal1 int primary key,
	cal2 int unique,
	cal3 int unique
);

show index from table2;

Key_name에 열 이름이 쓰여진 것은 보조 인덱스이다.

고유키도 중복을 허용하지 않기 때문에 Nun_unique가 0이고 고유키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러개를 만들 수 있다.

 

클러스터형 인덱스의 특징

• 기본키로 지정하면 자동으로 생성된다.
• 테이블에 1개만 생성된다.
• 어떤 열을 기본키로 지정해서 클러스터형 인덱스가 생성되면 그 열을 기준으로 자동 정렬된다.

 

보조인덱스의 특징

• 테이블에 여러개를 설정할 수 있다.
• 보조 인덱스를 만든다고해서 데이터의 순서나 내용이 바뀌지는 않는다.

보조 인덱스는 여러 개 만들 수 있지만, 보조 인덱스를 만들 때 마다 데이터베이스의 공간을 차지하게 되고 전반적으로 시스템에 오히려 나쁜 영향을 준다.

그러므로 꼭 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다.

 

 

인덱스 생성

인덱스 생성 방식

create [unique] index 인덱스_이름

    on 테이블_이름 (열_이름) [asc|desc];

 

create index로 생성되는 인덱스는 보조 인덱스이며 unique는 중복이 안되는 고유 인덱스를 생성하는 것이다. 생략하면 중복을 허용한다.

unique 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안되고 인덱스를 생성한 이후에 입력되는 데이터와도 중복이 될 수 없다.

asc 또는 desc로 인덱스의 오름차순 또는 내림차순으로 정렬하고 기본은 asc로 정렬된다. 굳이 desc로 만드는 경우는 거의 없다.

 

인덱스 제거

인덱스 제거 방식

drop index 인덱스_이름 on 테이블_이름;

 

기본키, 고유키로 자동생성된 인덱스는 drop index로 제거할 수 없다.

자동 생성된 인덱스는 alter table로 기본키나 고유키가 제거되면 함께 제거된다.

 

하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우에는 인덱스를 제거할 때 보조 인덱스부터 제거하는것이 더 좋다.

클러스터형 인덱스가 제거되면 내부적으로 데이터가 재구성되기 때문이다.

 

인덱스가 많이 생성되어 있는 테이블은 사용하지 않는 인덱스를 제거해주는 것이 성능 향상에 도움이 된다.