MYSQL INDEX 실무 활용기

우연히 INDEX를 실무에 (드디어)활용해 볼 기회가 생겨서 적어보는 글이다.

INDEX에 관한 원리 및 설명은 여기를 참고.

문제의 쿼리

SELECT SQL_NO_CACHE *
FROM USER
WHERE add_id = '2bf-ad7c-1b24'

쿼리는 위와 같이 심플했고 조건인 add_id 단 한개만 들어간 쿼리였는데 결과물 출력까지 13초나 소요되는 현상이 나타났다.

참고 - SQL_NO_CACHE는 DB 캐시 기능때문에 쿼리를 다시 돌릴 시에 캐시로 인한 속도 개선 문제때문에 캐시 기능을 막아놓고 쿼리를 돌렸다.

 

현재 인덱스 확인하기

show index from USER

 

예시

인덱스를 추가하기 전 현재 테이블에 걸린 인덱스를 조회해보면 위처럼 비슷한 결과가 나온다.

Column_name을 보면 컬럼 한개에 하나씩 인덱스를 걸어서 하나의 테이블에 총 5개의 인덱스가 걸린 것을 볼 수 있다.

key_name부분이 각 인덱스의 인덱스명을 말한다.

그리고 Mysql에서는 테이블 생성 시 기본키와 외래키에 대해서 인덱스를 자동 생성해준다. 

add_id 컬럼은 외래키도 아니고 기본키도 아니었기 때문에 만들어진 인덱스가 없었다.

 

인덱스 추가하기

ALTER TABLE USER
ADD INDEX USER_ADD_ID (add_id)

테이블에 add_id 컬럼에 해당하는 인덱스를 추가하기 위해 위의 쿼리를 이용했다.

처음 쿼리에서 조건문에 add_id를 이용했었다.

인덱스를 만드는 대상 컬럼은 WHERE 조건의 대상이 되는 컬럼을 지정해서 만들어줘야 한다. 그래야 해당 컬럼을 기준으로 인덱스를 정렬시키기 때문이다.

인덱스명은 임의로 지정해주면 되며 나는 USER_ADD_ID라는 이름으로 인덱스 이름을 지어줬다.

앞으로 이 인덱스명으로 인덱스를 추가하고 삭제하면 된다.

 

인덱스가 돌아가는지 확인하기

EXPLAIN
SELECT SQL_NO_CACHE *
FROM 테이블
WHERE add_id = 'test'

쿼리 상단에 EXPLAIN을 넣어서 아래와 같은 결과를 확인해보자.

예시

어떤 table에서 어떤 인덱스를 사용했는지 한 눈에 볼 수 있다.

employees 테이블에서는 type 컬럼이 ALL로 되어있기 때문에 조회 시 인덱스를 사용하지 않고 Full Scan을 사용했다는 뜻이다. 

titles와 salaries 테이블은 type이 ref로 되어있기 때문에 인덱스를 사용한 것이고 ref 컬럼에서 어떤 인덱스를 사용했는지 알려주고 있다.

 

인덱스를 만들었는데 인덱스를 참조하질 않아요

 

특정 테이블에 index를 걸었더라도 해당 index가 작동하지 않을 수도 있다. 이유는 여러가지겠지만 Mysql이 내부적으로 가장 빠르거나 합리적인 탐색법을 사용하기 때문이다. 그럴 경우엔 Hint 기능을 써서 USE INDEX를 사용해서 인덱스를 강제로 작동시키게 할 수 있다.

SELECT * 
FROM IMAGE USE INDEX(USER_ADD_ID)
WHERE add_id = '2bf-ad7c-1b24'

참고 - 힌트(Hint)기능을 쓰는 이유는 옵티마이저(Optimizer)가 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에 개발자가 최적의 경로를 지정해주는 것을 말한다. 옵티마이저 힌트와 인덱스 힌트로 나뉘며 무분별한 힌트의 사용은 성능의 저하를 초래한다.

어떤 컬럼을 기준으로 인덱스를 만들어야할까?

Cardinality가 높아야 좋다.

Cardinality란 해당 컬럼의 중복 수치를 나타낸 것이다.

어떠한 컬럼의 Cardinality 숫자가 높아야 인덱스 조회 시 더 효율적으로 돌아간다.

기본키 컬럼은 항상 중복이 불가능하므로 기본키에 대한 인덱스의 Cardinality는 테이블의 행의 갯수만큼 나오게된다. 

참고 - Mysql 내부에서 Cardinality의 숫자는 항상 동일하지 않다.
인덱스를 다시 만드는 과정에서 새롭게 연산이 들어가는데 이 때 Cardinality의 숫자는 매번 다를 수 있다.

당연히 기본키에 대한 Cardinality의 숫자는 높기 때문에 인덱스 조회 시 더 효율적으로 돌아간다.

Max cardinality: All values are unique
Min cardinality: All values are the same

 

Cardinality를 늘려보자

 

위에 설명했듯이 Cardinality의 숫자가 전체 행(count(*))을 계산했을 때 보다 더 적을 수도 있다.

ANALYZE TABLE 테이블

왜냐면 인덱스 테이블은 한번 만들고나면 해당 과거 이후로 테이블에 추가된 데이터들에 대해서는 인덱스 정렬이 최적화되어있지 않다.

위의 코드를 한번 써주면 해당 테이블에 대한 인덱스를 재생성하기 때문에 Show Index From 테이블명; 을 다시 입력해보면 특정 컬럼에 대한 Cardinality가 재계산되어 늘어난 것을 볼 수 있다.