MySQL DB 인덱싱을 통한 SELECT문 속도 향상

 

 

 

 

 

데이터베이스 설계하고 그를 바탕으로 만들어낸 어떤 서비스가 배포되어

 

사용자들의 이용이 시작되면 지속적으로 많은 데이터가 쌓이게 될텐데

 

그로인해 DB와의 통신간 SELECT문 속도가 느려질 수 있다.

 

SELECT문의 속도가 느려질 수 있는 이유에는 여러가지 원인이 있을 수 있지만

 

이 글에서는 조회할 테이블들의 데이터의 양(row)이 많은 것이 느린 속도의 원인이라고 하고

 

단적인 예로 아래와 같은 SELECT문이 있다.

 

SELECT * FROM koreans WHERE region = 'Seoul';

 

'koreans' 테이블에는 한국인의 이름,나이,살고 있는 지역의 정보가  51,740,000 개가 저장되어 있다.

 

그리고 비교적 최근에 생성된 row들에 'region' 의 값이 'Seoul' 인 데이터가 몰려있는데

 

위 SELECT문을 실행하게 되면 51,740,000 개나 되는 데이터를 일일이 뒤져가며

 

'region' 이라는 컬럼의 값이 'Seoul' 인 row들을 찾게 되는데 어느 일정 생성 시점을 넘어가면

 

조건에 해당되는 데이터가 없음에도 모든 row의 'region' 값을 체크하게 된다.

 

그럼 모든 데이터를 일일이 뒤져보지 않고 좀 더 효율적으로 빨리 찾을 수 있는 방법이 무엇일까?

 

이럴 때 바로 인덱스가 필요하다.

 

 

INDEX 인덱스란 무엇인가?

인덱스는 데이터베이스에서 검색 속도를 높이기 위해 사용하는 기술
* 특정 컬럼의 값을 미리 정렬해두고 검색할 때 이 정렬된 값을 바탕으로 검색하는 것
* 이를 통해 검색 속도를 빠르게 할 수 있으며, 특히 대용량 데이터를 다룰 때 유용함

 

 

위 예시에 'koreans' 테이블의 'region' 컬럼에 인덱스를 적용한다고 하면 다음과 같다.

 

'koreans' 테이블의 행(row)이 51,740,000 개 지만 각 row들의 'region'의 값은 한정적일 것 이다.

 

왜냐하면 51,740,000 명의 사람들이 51,740,000 곳의 각기 다른 지역에 살고 있지는 않을테니까.

 

어쨌든 설정상 'region' 컬럼의 값은 다음과 같이 7개 중 하나의 값이 저장되어 있다고 가정한다.

 

Seoul,Gyeonggi,Gangwon,Chungcheong,Gyeongsang,Jeolla,Jeju

 

이 'region' 컬럼에 인덱스를 생성하게 되면 컬럼의 값을 기준으로

 

'Seoul'부터 'Jeju'까지의 값들을 알파벳 순서로 정렬해두고

 

이후 SELECT 문에서 WHERE 절에서 'region' 값을 조건으로 사용하면

 

데이터베이스는 이 인덱스를 사용하여 값을 검색하게 된다.

 

즉, 'region' 값이 'Seoul'인 데이터를 검색하려면, 인덱스에서 'Seoul' 값을 가진 인덱스 키를 찾고

 

그 키에 해당하는 row를 가져온다 이때, 인덱스를 사용하기 때문에 일치하는 row를 찾는 속도가 빨라지는 것 이다.

 

그럼 무조건 인덱스를 생성하는 게 좋은 것 일까?

 

물론 인덱스에 장점만 있는 것은 아니다, 단점도 있다

 

위 예시에 바로 적용하자면, 만약 WHERE 절에서 'region' 값이 'Busan'과 같이 인덱스에 없는 값을 사용하면

 

데이터베이스는 인덱스를 사용할 수 없기 때문에 모든 row를 검색해야 한다

 

이런 경우에는 인덱스를 사용하지 않는 것이 더 빠를 수 있다.

 

그리고 다양한 조건의 SELECT문을 사용하기 위해

 

다수의 컬럼에 인덱스를 생성할 수도 있는데 남용하지 않도록 주의 해야 한다.

 

인덱스를 남용하면 안되는 이유

* 인덱스를 생성하면 추가적인 디스크 공간이 필요한데, 디스크 공간이 많이 필요한 경우
인덱스 생성으로 인해 디스크 사용량이 증가하여 전체 시스템 성능이 감소할 수 있음

* 인덱스를 생성하면 INSERT(삽입), UPDATE(갱신), DELETE(삭제) 등의 작업이 느려질 수 있는데
그 이유는 인덱스를 생성한 컬럼에 데이터가 추가, 삭제 또는 수정될 때마다 인덱스도 함께 (UPDATE)갱신되기 때문

 

 

 

인덱스를 조회,생성 및 제거하는 방법

 

/* 테이블의 인덱스를 조회 */

SHOW INDEX FROM 테이블명;

 

/* 테이블을 생성함과 동시에 인덱스를 생성 */

CREATE TABLE 테이블명 (
    컬럼명1 datatype,
    컬럼명2 datatype,
    컬럼명3 datatype,
    ...
    PRIMARY KEY (컬럼명1),
    INDEX 인덱스명 (컬럼명2, 컬럼명3) /* 컬럼2와 컬럼3을 하나로 묶어 복합인덱스 생성 */
);

 

/* 이미 생성되어 있는 테이블의 컬럼에 인덱스를 생성 */

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2); /* 컬럼1과 컬럼2를 하나로 묶어 복합인덱스 생성 */
/* CREATE INDEX 문에서는 인덱스를 생성하는 컬럼이 16개를 초과하면 안 됨 */

또는

ALTER TABLE 테이블명 ADD KEY 인덱스명 (컬럼명);

 

/* 생성된 인덱스를 제거 */

DROP INDEX 인덱스명 ON 테이블명;

또는

ALTER TABLE 테이블명 DROP KEY 인덱스명;