[MySQL] MySQL 인덱스 적용하여 쿼리 성능 개선

슬로우쿼리 슬랙 알림을 적용한 후, 본격적으로 슬로우쿼리를 해결하고자 인덱스에 관해 공부했다.

처음에는 아무리 찾아봐도 인덱스가 도대체 뭐하는 애인지 몰랐는데 그냥 인덱스는 책 마지막에 있는 색인이라고 이해하기로 했다.

 

사실 인덱스를 적용하는 데에 인덱스의 종류가 뭐가 있고 그것들의 차이점은 뭐고는 크게 도움이 되지는 않았다.

감사하게도 CTO님께서 운영 DB를 복사해서 인덱스 테스트용 DB를 만들어 주셔서 이것저것 인덱스 적용해 보는 게 도움이 많이 됐다.

 

단일 인덱스와 다중 컬럼 인덱스 차이

단일 인덱스 테이블

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name),
    key idx_address(address)
)

다중 컬럼 인덱스 테이블

CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    key idx_name(name, address)    
)

SELECT * FROM table1 WHERE name='홍길동' AND address='경기도';

단일 인덱스는 각각 컬럼에 인덱스를 거는 것이고 조회 시 MySQL이 각 인덱스 중 어떤 컬럼이 더 빠르게 검색되는지 판단 후 빠른 쪽을 검색 후 다른 컬럼을 검색하는 방식이 적용된다고 한다.

다중 컬럼 인덱스는 인덱스 적용한 컬럼들을 같이 색인해놓기 때문에 검색 시에 '홍길동경기도'로 검색하는 방식이다.

그렇기때문에 위와 같은 쿼리를 검색할 경우 다중컬럼인덱스가 더 빠르다.

 

다중 컬럼 인덱스 조회 시 주의사항

위와 같은 차이점으로 다중 컬럼 인덱스를 적용하기로 했다.

- 첫번째 컬럼을 무조건 조건에 포함하기

SELECT * FROM table2 WHERE address='경기도';

다중 컬럼 인덱스의 경우 이런 쿼리는 아예 인덱스를 타지 않는다.

조회에서 컬럼을 뺀다고 다 인덱스를 타지 않는 것은 아니고 다중 컬럼의 첫번째 컬럼을 무조건 조건에 포함해야 인덱스를 탄다.

따라서 조건절에 자주 활용되는 컬럼을 첫번째에 배치하는 것이 좋다.

- 컬럼의 순서 중요

equal 조건 같이 개수가 적은 데이터를 조회하는 컬럼을 앞쪽에 두고, 범위 조건 같이 개수가 많은 데이터를 조회하는 컬럼을 뒤쪽에 설정하는 것이 좋다.

범위 조건 이후의 컬럼은 인덱스를 타지 않기 때문에 범위 조건에 쓰이는 컬럼은 맨 뒤에 두는 게 좋다.

 

결과

인덱스 적용 전 캡쳐본이 없어서 인덱스 몇 개만 ignore index해서 조회해봤다.

이렇게 느리진 않았는데;;;;;

인덱스 적용 전

인덱스 적용 후

1분 25초에서 0.1초대로 개선이 되었다.

 

참조

https://sddev.tistory.com/157

INDEX에 대한 상식

https://spiderwebcoding.tistory.com/6

[MySQL] 인덱스(INDEX) 정리(효과적인 INDEX 설계)