SlowQuery 해결하기

0. 인덱스의 개념

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조이다. 인덱스로 데이터의 위치를 빠르게 찾아주는 역할이고 쉽게 예를 들어보면 책 뒤편에 '색인'이 인덱스의 역할과 동일하다고 볼 수 있다.

 

인덱스가 설정되지 않았다면 Table Full Scan이 일어나 성능이 저하되거나 치명적인 장애가 발생한다.

 

조회속도는 빨라지지만 UPDATE, INSERT, DELETE의 속도는 저하된다는 단점이 있다. (Table의 index 색인 정보를 갱신하는 추가적인 비용 소요) , 때문에 효율적인 인덱스 설계로 단점을 최대한 보완하는 방법을 생각해 볼 수 있다.

 

1. SlowQuery 문제

서버를 운영하는 환경에서 쿼리를 조회할때 수 분 이상 걸렸던 문제가 발생하여 서비스가 힘든 상황이 생겼다.

 

해당 테이블에는 약 수십억건의 데이터가 들어있다.

운영을 시작하고 초기에는 문제없었지만, 운영을 하고 3개월 이상이 지난 지금 데이터들이 수십억 건이 쌓여 속도가 점점 느려지는 것이었다.

 

※ 문제가 되는 쿼리 예시

   - 데이터가 수십억건이 있기 때문에 조회를 할 때 인덱스를 사용하고 있었다.

   - 기존에 index가 있었지만, explain으로 실행계획을 확인해 보니 index를 타지 않고 seq scan을 하는 것을 발견하였다.

 

---

기존 쿼리 및 Index 살펴보기

select
COALESCE(a, 0),
COALESCE(b, 0),
from (select lon, lat, a, b
        from data
        where id = (
                        SELECT id
                        FROM set 
                        WHERE pred_time = to_timestamp('20220113','yyyymmddhh24')		
                          )
         ) as tBase
order by lon desc, lat asc;

data

인덱스명	인덱스를 구성하는 키
pk	id, lat, lon
inx_data	1: id 2: lat 3. lon 4. idx_x 5. idx_y
spatial	공간

set

인덱스명	인덱스를 구성하는 키
pk	id
idx_set	id

 

---

 

[복합 인덱스]

→ 첫 번째 키로 데이터들을 정렬한 다음 다음 첫번째 키가 같은 컬럼은 두 번째 키로 정렬하는 식으로 인덱스를 구성

* 복합 인덱스를 만드는 순서가 중요.

 

※ 아래와 같이 정렬한다고 이해하면 될 것이다.

 

 

인덱스의 특징

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. (단일 여러 개 또는 여러컬럼을 묶어 복합인덱스)

참고로 WHERE절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없다.

 

ORDER BY와 GROUP BY에 대한 INDEX

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.

• ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
• WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
• ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
• ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

 

다중 컬럼 인덱스

다중 컬럼 인덱스는 두 개 이상의 필드를 조합해서 생성한 INDEX이다. 1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX 해서 사용한다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)

 

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야 한다.

때문에 가급적 UPDATE가 안 되는 값을 선정해야 한다.

 

또한 순서도 중요하기 때문에 고려해서 만들어야 한다.

 

 

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

Table1(단일 인덱스)

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)
)

Table2(다중 컬럼 인덱스)

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)    
)

QUREY문

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

 

table1의 경우에 각각 컬럼(name),(address)에 INDEX가 걸려있기 때문에 MySQL은 name컬럼과 address컬럼을 보고 둘 중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단 후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 된다.

 

table2의 경우 바로 원하는 값을 찾는데 그 이유는 INDEX를 저장할 때 name과 address를 같이 저장하기 때문이다. 즉, name과 address의 값을 같이 색인하고 검색에서도 '홍길동경기도'로 검색을 시도하게 된다. 이렇게 사용할 경우 table1보다 table2의 경우가 더 빠른 검색을 할 수 있다.

 

그렇지만 다중 컬럼 인덱스를 아래와 같이 사용하면 INDEX를 타지 않는다.

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

 

이 경우에는 다중 컬럼 인덱스로 설정되어 있던 name이 함께 검색이 되지 않으므로 INDEX의 효과를 볼 수가 없다.

다중 컬럼 인덱스를 사용할 때는 INDEX로 설정해 준 제일 왼쪽컬럼이 WHERE절에 사용되어야 한다.

 

★ 설계방법

• 무조건 많이 설정하지 않는다. (한 테이블당 3~5개가 적당 목적에 따라 상이)
• 조회 시 자주 사용하는 컬럼
• 고유한 값 위주로 설계
• 카디널리티가 높을수록 좋다 (= 한 컬럼이 갖고 있는 중복의 정도가 낮을 수록 좋다.)
• INDEX 키의 크기는 되도록 작게 설계
• PK, JOIN의 연결고리가 되는 컬럼
• 단일 인덱스 여러 개 보다 다중 컬럼 INDEX 생성 고려
• UPDATE가 빈번하지 않은 컬럼
• JOIN시 자주 사용하는 컬럼
• INDEX를 생성할 때 가장 효율적인 자료형은 정수형 자료(가변적 데이터는 비효율적)

 

 

인덱스를 만들 때는 최대한 용도를 잘 생각해서 신중하게 만들고 만들었다면 최대한 잘 활용하기 위한 쿼리를 짜기 위해 노력하자

 

또, 쿼리를 짜고 나면 explain을 걸어 내가 짠 쿼리를 옵티마이저가 어떻게 실행하고자 하는지 쿼리 실행계획을 꼭 한 번씩 확인하는 습관을 가지자!