웹 서비스와 같은 일반적인 온라인 트랜잭션 처리 환경의 데이터 베이스에서는 INSERT, UPDATE 같은 작업은 레코드 단위로 발생하기 때문에 성능 이슈가 발생하는 경우는 매우 적다.

하지만 SELECT는 여러개의 테이블로 부터 데이터를 조합해서 빠르게 가져와야 하는 경우가 많아 여러개의 테이블을 어떻게 읽느냐에 따라 성능 이슈가 자주 발생하게 된다.

SELECT 절의 처리 순서

1
2
3
4
5
6
7
8
9

SELECT s.emp_no, COUNT(DISTINCT e.first_name) AS cnt
FROM salaries s
    INNER JOIN employees e ON e.emp_no=s.emp_no
WHERE s.emp_no IN (100001, 100002)
GROUP BY s.emp_no
HAVING AVG(s.salary) > 1000
ORDER BY AVG(s.salary)
LIMIT 10
;

SELECT 쿼리에서 어느 절이 먼저 실행될지 예측하지 못할 때가 있는데, 어느 절이 먼저 실행되는지 모른다면 처리 내용, 처리 결과를 예측할 수 없다.

flowchart LR 
    subgraph FIRST ["WHERE 적용 및 조인 실행"]
        direction TB
        A(드라이빙 테이블) 
        -.-> B(드리븐 테이블 1)
        -.-> C(드리븐 테이블 2)
    end
    FIRST --> D("GROUP BY") --> E("DISTINCT") --> F("HAVING 조건 적용") --> G("ORDER BY") --> H("LIMIT")

대부분 위 순서대로 쿼리가 실행된다. 또한 SQL에는 ORDER BY나 GROUP BY 절이 있더라도 인덱스를 이용해 처리할 때는 그 단계 자체가 불필요하므로 생략된다.

flowchart LR 
    subgraph FIRST ["WHERE 적용"]
        A(드라이빙 테이블)
    end
    
    subgraph SECOND ["조인 실행"]
        direction TB
        B(드리븐 테이블 1)
        -.-> C(드리븐 테이블 2)
    end
    
    FIRST --> G("ORDER BY") --> SECOND --> H("LIMIT")

GROUP BY 절이 없이 ORDER BY만 사용된 쿼리같은 경우 첫 번째 테이블만 읽어 정렬을 수행한 뒤 나머지 테이블을 읽는다.

위의 실행 순서를 벗어나는 쿼리가 필요하다면 서브쿼리로 작성된 인라인 뷰(Inline View)를 사용해야 한다. 예를 들어 예시 쿼리에서 LIMIT를 먼저 적용하고 ORDER BY를 실행하기 위해 다음과 같이 쿼리를 작성할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT emp_no,cnt
FROM (
    SELECT s.emp_no
        ,COUNT(DISTINCT e.first_name) AS cnt
        ,MAX(s.salary) AS max_salary
    FROM salaries s
        INNER JOIN employees e ON e.emp_no=s.emp_no
    WHERE s.emp_no IN (100001, 100002)
    GROUP BY s.emp_no
    HAVING MAX(s.salary) > 1000
    LIMIT 10
) temp_view
ORDER BY max_salary
;

LIMIT를 GROUP BY전 실행하고자 할때도 서브쿼리로 인라인 뷰를 만들어, 뷰 안에서 LIMIT 를 정용하고 바깥 쿼리에서 GROUP BY, ORDER BY를 적용해야 한다.

인라인 뷰가 사용되면 임시 테이블이 사용되기 때문에 주의가 필요하다.

인덱스 사용을 위한 기본 규칙

인덱스는 B-Tree에 컬럼의 값을 아무런 변환 없이 정렬하여 저장하기 때문에 원본 값을 이용할 때만 인덱스를 사용하게 된다.

따라서 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야 한다.

1
2
3
4
5

/* 인덱스 사용 불가*/
SELECT * FROM salaries WHERE salary*10 > 150000;

/* 인덱스 사용 가능 */
SELECT * FROM salaries WHERE salary > 150000/10;

복잡한 연산을 수행한다거나 MD5 함수와 같이 해시 값을 만들어 비교해야 하는 경우라면 미리 계산된 값을 저장하도록 MySQL 가상 컬럼(Virtual Column)을 추가하고 그 컬럼에 인덱스를 생성하거나 함수 기반의 인덱스를 사용하면 된다.

저장하고자 하는 값의 타입에 맞춰 컬럼의 타입을 선정하고, 쿼리 작성시 데이터의 타입에 맞춰 비교 조건을 사용해야하며, 데이터 타입이 조금이라도 다른 경우 최적화되지 못하는 현상은 MySQL 서버의 버전이 업그레이드된다고 해서 해결될 수 있는 부분이 아니므로 주의가 필요하다.

WHERE

WHERE 조건이 인덱스를 사용하는 방법은 크게 작업 범위 결정 조건과 체크 조건 두 가지 방식으로 구분할 수 있다.

그 중 작업 범위 결정 조건은 동등 비교 조건이나 IN으로 구성된 조건에 사용된 컬럼들이 인덱스의 컬럼 구성과 좌측에서부터 비교했을 때 얼마나 일치하는가에 따라 달라진다.

flowchart TB
    subgraph SA [인덱스 컬럼 순서]
        direction LR 
        saa(COL_1) ~~~ sab(COL_2) ~~~ sac(COL_3) ~~~ sad(COL_4)
    end
    subgraph SB [WHERE 조건절 컬럼 순서]
        direction LR
        sba(COL_2 = ?) ~~~ sbb(COL_4 = ?) ~~~ sbc(COL_3 > ?) ~~~ sbd(COL_1 = ?)
    end
    SA ~~~ SB
%%    sba --> sab
%%    sbb -.-> sad
%%    sbc --> sac
%%    sbd --> saa

WHERE절에서 조건절에 나열된 순서가 인덱스와 다르더라도 옵티마이저는 인덱스를 사용할 수 있는 조건들을 뽑아서 최적화를 수행하기 때문에 사용된 조건의 순서는 실제 인덱스 사용과 무관하다.

• 동등 비교 조건: COL_1, COL_2
• 범위 비교 조건: COL_3
• 체크조건: COL_4
  • 직전 컬럼인 COL_3가 동등 비교 조건이 아니라 범위 비교 조건으로 사용되었기 때문에 작업범위 결정 조건으로 사용하지 못한다.

WHERE 절의 조건은 GROUP BY, ORDER BY와 달리 순서를 변경해도 결과의 차이가 없다.
인덱스를 구성하는 컬럼에 대한 조건 존재 유무가 중요하다.

WHERE 조건에서 OR 을 통해 조건을 사용할 경우 처리 방식이 바뀐다.

1
2
3
4
5

SELECT *
FROM employees
WHERE first_name = 'Kebin'
    OR last_name = 'Poly'
;

• first_name컬럼에 인덱스가 있고, last_name 컬럼에 인덱스가 없는 경우
  • first_name의 인덱스를 활용할 경우 (풀 테이블 스캔) + (인덱스 레인지 스캔) 보다 (풀 테이블 스캔)이 작업량이 적으므로 옵티마이저는 풀 테이블 스캔을 선택한다.
• 조건에 사용된 모든 컬럼에 인덱스가 있는 경우
  • index_merge 접근 방법으로 쿼리가 실행될 수 있어 풀 테이블 스캔보다 빠르지만, 인덱스 하나를 레인지 스캔하는 것 보다는 느리다.

WHERE 조건절에서 AND로 연결된다면 읽어와야 할 레코드 건수를 줄이지만, OR로 연결되면 비교해야 할 레코드가 늘어나기 때문에 사용에 주의가 필요하다.

GROUP BY

GROUP BY 절의 각 컬럼은 비교 연산자를 가지지 않으므로 작업 범위 결정 조건이나 체크 조건과 같이 구분해서 생각할 필요는 없다.

쿼리에 GROUP BY가 사용될 때 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 인덱스를 사용할 수 있다.

flowchart TB
    subgraph SA [인덱스 컬럼 순서]
        direction LR 
        saa(COL_1) --> sab(COL_2) --> sac(COL_3) --> sad(COL_4)
    end
    subgraph SB [WHERE 조건절 컬럼 순서]
        direction LR
        sba(COL_1) --> sbb(COL_2) --> sbc(COL_3)
    end
    SA ~~~ SB
%%    sba --> saa
%%    sbb -.-> sab
%%    sbc --> sac
%%    sbd --> saa

• GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 위치가 같아야한다.
• 인덱스를 구성하는 컬럼 중에서 뒤쪽에 있는 컬럼은 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스 앞쪽에 있는 컬럼이 명시되지 않으면 인덱스를 사용할 수 없다.
• GROUP BY 절에 명시도니 컬럼이 하나라도 익덱스에 없으면 전혀 인덱스를 사용하지 못한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

## 인덱스를 활용하지 못하는 GROUP BY 예시

# ix_col1234 (col_1, col_2, col_3, col_4)

## 1
... GROUP BY COL_2, COL_1

## 2
... GROUP BY COL_1, COL_3, COL_2

## 3
... GROUP BY COL_1, COL_3

## 4
... GROUP BY COL_1, COL_2, COL_3, COL_4, COL_5

• 1,2: 인덱스를 구성하는 컬럼 순서와 불일치
• 3: COL_3 앞 COL_2 없음
• 4: 인덱스가 없는 COL_5 사용

1
2
3
4
5
6
7
8

## 인덱스를 활용하는 GROUP BY 예시

# ix_col1234 (col_1, col_2, col_3, col_4)

... GROUP BY COL_1
... GROUP BY COL_1, COL_2
... GROUP BY COL_1, COL_2, COL_3
... GROUP BY COL_1, COL_2, COL_3, COL_4

WHERE 조건절에 COL_1이나 COL_2같이 인덱스 순서가 앞인 컬럼에 대해 동등 비교 조건으로 사용된다면 상수로 비교하기 때문에 해당 컬럼을 명시하지 않아도 인덱스를 활용할 수 있다.

1
2
3
4
5
6

## GROUP BY에 컬럼을 명시하지 않아도 인덱스를 활용하는 예시

# ix_col1234 (col_1, col_2, col_3, col_4)

... WHERE COL_1 = '상수' ... GROUP BY COL_2, COL_3
... WHERE COL_1 = '상수' AND COL_2 = '상수' ... GROUP BY COL_3, COL_4

ORDER BY

MySQL에서 ORDER BY와 GROUP BY는 처리 방법이 상당히 비슷하여 인덱스 사용 여부 또한 거의 흡사하다.

다른 점은 정렬되는 각 컬럼의 오름차순 및 내림차순 옵션이 인덱스와 같거나 정반대인 경우에만 사용할 수 있어 모든 컬럼이 오름차순이나 내림차순일 때만 인덱스를 사용할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

## 인덱스를 활용하지 못하는 ORDER BY 예시

# ix_col1234 (col_1, col_2, col_3, col_4)

## 1
... GROUP BY COL_2, COL_3

## 2
... GROUP BY COL_1, COL_3, COL_2

## 3
... GROUP BY COL_1, COL_2 DESC, COL_3

## 4
... GROUP BY COL_1, COL_3

## 5
... GROUP BY COL_1, COL_2, COL_3, COL_4, COL_5

1. COL_1 누락
2. 컬럼 순서 불일치
3. COL_2 내림차순
4. COL_2 누락
5. 인덱스에 존재하지 않는 COL_5 사용

WHERE + (ORDER BY or GROUP BY)

쿼리가 WHERE + (ORDER BY 또는 GROUP BY) 형식으로 구성되어 있는 경우 각각 다른 인덱스를 사용할 수 없기 때문에 3가지 방식 중 한가지 방법으로만 인덱스를 이용한다.

• 동시에 같은 인덱스를 이용:
  • 정렬 대상 컬럼이 모두 하나의 인덱스에 연속해서 포함되어 있는 경우 사용
  • 나머지 방식보다 훨씬 빠르다.
• WHERE 절만 인덱스를 이용:
  • ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정을 거친다(Using Filesort)
  • WHERE 절의 조건에 일치하는 레코드 건수가 많지 않을 때 효율적이다.
• ORDER BY 절만 인덱스를 이용
  • ORDER BY 절의 순서대로 인덱스를 읽으면서 레코드를 한 건씩 WHERE 절의 조건에 일치하는지 비교하여 아닌경우 버리는 형식으로 처리된다.
  • 아주 많은 레코드를 조회해서 정렬해야 하는 경우 이러한 형태로 튜닝하기도 한다.

또한 WHERE 절에서 동등 비교 조건으로 비교된 컬럼과 ORDER BY 절에 명시된 컬럼이 순서대로 빠짐없이 인덱스 컬럼의 왼쪽부터 일치해야 한다.

• 비교 조건에 사용된 컬럼과 ORDER BY 절의 컬럼의 중복은 상관 없다.
• 중간에 빠지는 컬럼이 있으면 모두 인덱스를 사용할 수 없어 주로 WHERE절만 인덱스를 활용한다.

flowchart LR
    subgraph SAB [" "]
        direction TB
        SA ~~~ SB
    end
    subgraph SA [WHERE 절]
        direction TB 
        saa(COL_1 = ?) 
    end
    subgraph SB [ORDER BY 절]
        direction TB
        sba(COL_2) ~~~ sbb(COL_3)
    end
    subgraph SC [인덱스]
        direction TB
        sca(COL_1 ASC) ~~~
        scb(COL_2 ASC) ~~~
        scc(COL_3 ASC) ~~~
        scd(COL_4 ASC)
    end
    subgraph SDE [" "]
        direction TB
        SD ~~~ SE
    end
    subgraph SD [WHERE 절]
        direction TB
        sda(COL_1 = ?) ~~~
        sdb(COL_2 = ?) ~~~
        sdc(COL_3 > ?)
    end
    subgraph SE [ORDER BY 절]
        direction TB
        sea(COL_3 DESC) ~~~ 
        seb(COL_4 DESC)
    end
    SA ~~~ SB
    SAB ~~~ SC ~~~ SDE
    

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 인덱스 사용 가능: 등등 비교로 사용된 컬럼
SELECT *
FROM tb_test
WHERE COL_1 = 10
ORDER BY COL_2, COL_3
;

# 위와 같은 방식으로 처리된다.
SELECT *
FROM tb_test
WHERE COL_1 = 10
ORDER BY COL_1, COL_2, COL_3
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 범위 조건으로 사용되었지만 모든 컬럼을 명시하여 인덱스 사용 가능
SELECT *
FROM tb_test
WHERE COL_1 > 10
ORDER BY COL_1, COL_2, COL_3
;

# 인덱스 사용 불가
SELECT *
FROM tb_test
WHERE COL_1 > 10
ORDER BY COL_2, COL_3
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 인덱스 사용 불가

SELECT *
FROM tb_test
WHERE COL_1 = 10
ORDER BY COL_3, COL_4
;

SELECT *
FROM tb_test
WHERE COL_1 > 10
ORDER BY COL_2, COL_3
;

SELECT *
FROM tb_test
WHERE COL_1 IN (1, 2, 3, 4)
ORDER BY COL_2
;

GROUP BY + ORDER BY

GROUP BY와 ORDER BY 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해 처리되려면 명시된 컬럼의 순서와 내용이 모두 일치해야 한다.

MySQL 5.7 버전까지는 GROUP BY는 사용된 컬럼에 대한 정렬까지 함께 수행하는 방식이었으나 MySQL 8.0 부터는 컬럼의 정렬을 보장하지 않아 정렬이 필요한 GROUP BY라면 ORDER BY를 모두 명시해야 한다.

WHERE + GROUP BY + ORDER BY

아래 질문을 기본으로 하여 흐름을 확인한다.

1. WHERE 절이 인덱스를 사용할 수 있는가?
2. GROUP BY 절이 인덱스를 사용할 수 있는가?
3. GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?

flowchart LR
    A(START) -->|Y| B{WHERE} 
    
    B -->|Y| C{GROUP BY}
    B -->|N| D{GROUP BY}
    
    C -->|Y| E{ORDER BY}
    C -->|N| RB[Only WHERE]
    E -->|Y| RA[WHERE, GROUP BY, ORDER BY]
    E -->|N| RB
    
    D -->|N| F{GROUP BY}
    F -->|Y| G{ORDER BY}
    F -->|N| RD[인덱스 사용 불가]
    G -->|Y| RC[GROUP BY, ORDER BY]
    G -->|N| RD

WHERE 절 비교 조건 사용시 주의사항

WHERE절에 사용되는 비교 조건의 표현식은 상당히 중요하다. 쿼리가 최적으로 실행되려면 적합한 인덱스와 함께 비교 조건의 표현식을 적절하게 사용해야 한다.

NULL 비교

다른 DBMS와 다르게 MySQL 에서는 NULL 값이 포함된 레코드도 인덱스로 관리한다.

하지만 SQL 표준에서 NULL의 정의는 비교할 수 없는 값이며, 이에 따라 두 값이 모두 NULL을 가진다고 하더라도 이 두 값이 동등한지 비교하는 것은 불가능하다.

쿼리에서 IS NULL 또는 <=>연산자를 사용하는 방법 외 컬럼의 값이 NULL인지 알 수 있는 방법은 없다.

문자열이나 숫자 비교

문자열 컬럼이나 숫자 컬럼을 비교할 때는 반드시 그 타입에 맞는 상수값을 사용할 것을 권장한다.

1
2
3
4

SELECT * FROM employees WHERE emp_no=1001;
SELECT * FROM employees WHERE first_name='smith';
SELECT * FROM employees WHERE emp_no='1001';
SELECT * FROM employees WHERE first_name=1001;

• 1, 2번 쿼리는 컬럼의 타입과 비교하는 상수값이 동일한 타입으로 사용되어 인덱스를 적절히 이용할 수 있다.
• 3번 쿼리는 emp_no 컬럼이 숫자 타입이므로 문자열 상수값을 숫자로 타입 변환해서 사용하므로 특별한 성능 저하는 발생하지 않는다.
• 4번 쿼리는 first_name이 문자열 컬럼이지만 비교되는 상수값이 숫자 타입이므로 옵티마이저는 우선순위를 가지는 숫자 타입으로 비교를 수행하려고 실행 계획을 수립한다.
  • first_name 컬럼의 문자열을 숫자로 변환하여 비교를 수행하게 되어 인덱스를 활용하지 못한다.

컬럼 타입에 맞게 상수 리터럴을 비교 조건에 사용하는 것이 중요하다.

날짜 비교

DATE or DATETIME 문자열 비교

• DATE, DATETIME 타입 갑과 문자열을 비교할 때는 문자열 값을 자동으로 변환해서 비교를 수행하므로 명시적으로 변환하는 처리를 거치지 않아도 동일하게 처리된다.
• DATE, DATETIME 타입 컬럼을 변경하는 경우 인덱스를 효율적으로 이용할 수 없으므로 상수를 변형하는 형태로 처리하는 것이 좋다.
  • 문자열로 변경
  • 날짜 계산 처리 등

DATE 와 DATETIME 비교

• 따로 변환하지 않고 비교해도 내부적으로 DATE 타입을 YYYY-MM-DD 00:00:00 형태로 변환하여 비교를 수행한다.
• 해당 타입 비교에서 타입 변환은 인덱스 사용여부에 영향을 미치지 않으므로 성능보다는 쿼리의 결과에 주의하여 사용한다.

DATETIME 와 TIMESTAMP 비교

DATE, DATETIME 값을 TIMESTAMP 값을 변환 없이 비교하면 문제없이 작동하고 실행 계획도 인덱스 레인지 스캔을 사용해서 처리하는 것 처럼 보이지만 그렇지 않다.

TIMESTAMP값은 내부적으로 단순 숫자 값으로 원하는 결과를 얻지 못하기 때문에 비교 대상 컬럼 타입에 맞게 변환해서 사용해야한다.

Short-Circuit Evalutation

1
2
3
4
5

boolean in_transaction;

if ( in_transaction && has_modified() ) {
  commit();
}

위 처럼 여러 개의 표현식이 AND, OR 논리 연산자로 연결된 경우 선행 표현식의 결과에 따라 후행 표현식을 평가할지 결정하는 최적화를 Short-circuit Evaluation 이라고 하며, MySQL 서버에서도 이러한 방식으로 쿼리의 성능 최적화를 수행한다.

MySQL 서버는 쿼리의 WHERE 절에 나열된 순서대로 Short-circuit Evaluation 방식으로 평가하여 해당 레코드를 반환해야 할지 결정하지만, 나열된 조건 중 인덱스를 활용할 수 있는 조건이 있다면 해당 조건을 최우선으로 사용한다.

그렇기 때문에 WHERE 조건절에서 나열된 순서가 인덱스의 사용 여부를 결정하지는 않게된다.

인덱스 사용 불가

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

# 0번: 2,844,047건
SELECT COUNT(*) FROM salaries;


# 1번: 2,442,943건
SELECT COUNT(*)
FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01'
;

# 2번: 0건
SELECT COUNT(*)
FROM salaries
WHERE to_date < '1985-01-01'
;

# 3번(1 AND 2 조건): 0건
SELECT COUNT(*)
FROM salaries
WHERE CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01'
  AND to_date < '1985-01-01'
;

# 4번(2 AND 1 조건): 0건
SELECT COUNT(*)
FROM salaries
WHERE to_date < '1985-01-01'
  AND CONVERT_TZ(from_date, '+00:00', '+09:00') > '1991-01-01'
;

위 쿼리에서 사용된 두 개의 조건은 모두 인덱스를 사용하지 못하기 때문에 풀 테이블 스캔이 발생한다. 그리고 2번 조건의 결과 레코드 건수가 0 이므로 1번 조건과 2번 조건이 AND 연결된 3번, 4번 쿼리의 결과도 0건이다.

1번 조건은 전체 레코드에 대해 CONVERT_TZ 함수를 실행 후 그 결과에 대해 비교 작업을 수행해야하지만, 2번 조건은 비교 작업만 전체 레코드에 대해 수행한다.

이러한 상황에서 Short-circuit Evaluation 최적화를 수행하기 위해 조건의 결과가 0건인 2번 조건을 선행하면 1번 쿼리가 실행되지 않기 때문에, 3번 쿼리보다 더 좋은 성능을 보인다.

인덱스 사용 가능

1
2
3
4
5
6
7

# ix_firstname(first_name)
SELECT * 
FROM employees
WHERE last_name='Aamodt'
  AND first_name='Matt'
  AND MONTH(birth_date)=1
;

위 쿼리는 인덱스를 사용 가능한 first_name을 먼저 평가하고, 이후 나열된 순서대로 조건들을 평가한다.

DISTINCT

특정 컬럼의 유니크한 값을 조회하기 위해 SELECT 절에 DISTINCT를 사용하는데, 많은 사용자가 조인을 하는 경우 레코드 중복을 막기 위해 남발하는 경향이 있다.

이러한 남용은 성능적인 문제도 있지만 쿼리의 결과도 의도한 바와 달라질 수 있으므로 주의해야한다.

여러 테이블을 조인하는 쿼리에서는 조인 조건에 따라 레코드가 몇 배씩 불어나기도 하는데, 테이블간 업무적인 연결 조건을 이해하지 못하고 쿼리를 작성하는 경우 이러한 남용이 발생하게되므로 1:1. 1:N 조인인지 업무적인 특성을 잘 이해하는 것이 중요하다.

LIMIT n

LIMIT는 쿼리 결과에서 지정된 순서에 위치한 레코드만 가져오고자 할 때 사용한다.

오라클과 같은 RDBMS와는 다르게 MySQL의 LIMIT는 WHERE 조건이 아니기 때문에 항상 쿼리의 가장 마지막에 실행된다.

1
2
3
4
5
6

SELECT *
FROM employees
WHERE emp_no BETWEEN 10001 AND 10010
ORDER BY first_name
LIMIT 0, 5
;

1. employees 테이블에서 WHERE 절의 검색 조건에 일치하는 레코드를 전부 읽어온다.
2. 1번에서 읽어온 레코드를 first_name 컬럼 값에 따라 정렬한다.
3. 정렬된 결과에서 상위 5건만 반환한다.

MySQL에서 LIMIT는 쿼리에서 모든 레코드의 정렬이 완료되지 않았다고 하더라도 필요한 레코드 건수만 준비되면 즉시 쿼리를 종료한다.

하지만 쿼리에 포함된 ORDER BY, GROUP BY 절에서 수행하는 정렬 작업이 인덱스를 적절히 이용하지 못하는 경우 처리가 완료된 후에 LIMIT가 적용되므로 성능 개선에 큰 의미가 없을 수 있다.

풀 테이블 스캔

1

SELECT * FROM employees LIMIT 0, 10;

풀 테이블 스캔을 수행하지만 LIMIT 조건으로 인해 MySQL이 스토리지 엔진으로 부터 10개의 레코드를 읽어 들이는 순간 읽기 작업을 멈추게된다.

정렬, 그루핑, DISTINCT가 없는 쿼리에서 LIMIT 조건을 사용하면 쿼리가 상당히 빨라질 수 있다.

GROUP BY

1

SELECT * FROM employees GROUP BY first_name LIMIT 0, 10;

인덱스를 사용하지 못하는 GROUP BY는 그루핑과 정렬의 특성을 모두 가지고 있기 때문에 GROUP BY 작업이 완료되고 나서야 LIMIT 처리를 수행할 수 있다.

LIMIT이 GROUP BY와 함께 사용되는 경우 실질적인 서버의 작업 내용을 크게 줄여주지는 못한다.

DISTINCT

1

SELECT DISTINCT first_name FROM employees LIMIT 0, 10;

정렬이 필요없는 DISTINCT는 유니크한 그룹만 만들어 내면 된다.

MySQL은 스토리지 엔진을 통해 풀 테이블 스캔으로 employees 테이블 레코드를 읽어 들임과 동시에 DISTINCT를 위한 중복 제거 작업(임시 테이블 사용)을 작업을 반복적으로 처리하다가 유니크한 레코드가 LIMIT 건수 만큼 채워졌다면 쿼리를 멈춘다.

이렇게 DISTINCT 와 함께 사용된 LIMIT가 실질적인 중복 제거 작업 범위를 줄여주게 되므로 작업량도 줄이는 효과를 가진다.

WHERE + ORDER BY

1
2
3
4
5
6

SELECT *
FROM employees
WHERE emp_no BETWEEN 10001 AND 11000
ORDER BY first_name
LIMIT 0, 10
;

employees 테이블로부터 조건에 일치하는 레코드를 읽은 후 first_name 컬럼 값으로 정렬을 수행한다.

정렬을 수행하면서 필요한 10건을 채우면 나머지 작업을 멈추고 결과를 사용자에게 반환한다.

이 때 정렬을 수행하기 전에 WHERE 조건에 일치하는 모든 레코드를 읽어와야 하지만, 읽어온 결과가 전부 정렬돼야 쿼리가 완료되는 것이 아니라 필요한 만큼만 정렬하면 된다.

하지만 이 쿼리도 GROUP BY 와 사용했을 때 처럼 크게 작업량을 줄여주지는 못한다.

OFFSET

실제 쿼리의 성능은 사용자의 화면에 레코드가 몇 건 출력되느냐보다 MySQL 서버가 그 결과를 만들어내기 위해 어떠한 작업들을 했는지가 중요한데, LIMIT에 offset을 사용하여 페이징과 같은 처리를 할 때 큰 영향을 미칠 수 있다.

1
2
3
4
5

SELECT * 
FROM salaries
ORDER BY salary
LIMIT 2_000_000, 10
;

위 쿼리 처리는 salaries 테이블을 처음부터 읽으면서 2,000,010건의 레코드를 읽은 후 2,000,000건은 버리고 마지막 10건만 사용자에게 반환하는데, 사용자 화면에 보여주는 레코드는 10건이지만 실제 2,000,010건의 레코드를 읽어야 하므로 매우 느려질 수 있다.

이처럼 LIMIT 조건의 페이징이 처음 몇 개 페이지 조회로 끝나지 않을 가능성이 높다면 WHERE 조건절로 읽어야 할 위치를 찾고 그 위치에서 10개만 읽는 형태의 쿼리를 사용하는 것이 좋다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

# 첫 페이지
SELECT * FROM salaries ORDER BY salary LIMIT 0, 10;

# 두 번째 페이지(첫 페이지의 마지막 salary 값과 emp_no 값 이용)
SELECT * FROM salaries
WHERE salary >= 38864
  AND NOT (salary = 38864 AND emp_no <= 274049)
ORDER BY salary 
LIMIT 0, 10
;

# 마지막 페이지
SELECT *
FROM salaries
WHERE salary >= 154888
  AND NOT (salary = 154888 AND emp_no <= 109334)
ORDER BY salary
LIMIT 0, 10
;

salary와 같이 중복이 발생할 수 있는 컬럼을 사용하면 조건에 따라서 데이터가 누락, 중복 등으로 의도하지 않은 데이터를 받아올 수 있으므로 주의해야 한다.

COUNT()

COUNT() 한수는 결과 레코드의 건수를 반호나하는 함수이다. 컬럼이나 표현식을 인자로 받으며, 특별한 형태로 *을 받을 수 있다.

• COUNT(*)처럼 함수 내에 사용된 *는 SELECT와 달리 레코드 자체를 의미하는 것으로 pk, 1 과 같은 값을 인자로 주지 않아도 동일한 성능을 보여준다.

MyISAM 스토리지 엔진을 사용하는 테이블은 테이블의 메타 정보에 전체 레코드 건수를 관리하므로, 조건이 없는 경우 COUNT 처리는 매우 빠르게 처리되지만, 조건이 있는 경우 레코드를 읽지 않는 이상 알 수 없으므로 일반적인 DBMS와 같이 처리된다.

InnoDB 스토리지 엔진을 사용하는 테이블에서는 조건이 없는 COUNT(*) 쿼리라고 하더라도 직접 데이터나 인덱스를 읽어야만 레코드 건수를 가져올 수 있기 때문에 큰 테이블에서 COUNT() 함수를 사용하는 작업에 주의해야 한다.

COUNT(*) 쿼리에서 가장 많이 하는 실수는 ORDER BY 구문이나 체크 조건을 가지지 않는 LEFT JOIN과 같은 레코드 건수를 가져오는 것과는 무관한 작업을 포함하는 것이다.

COUNT(*) 쿼리에서 ORDER BY 절은 어떠한 경우에서 필요하지 않으며, LEFT JOIN 또한 레코드 건수의 변화가 없거나 아우터 테이블에서 별도 체크를 하지 않아도 되는 경우 모두 제거해야한다.

많은 사용자가 일반적으로 컬럼의 값을 SELECT 하는 쿼리보다 COUNT(*) 쿼리가 훨씬 빠르게 실행될 것으로 생각하지만, 인덱스를 제대로 사용하지 못한 COUNT쿼리는 페이징해서 데이터를 가져오는 쿼리보다 매우 느리게 실행 될 수 있다.

COUNT() 함수에 컬럼명이나 표현식이 인자로 사용되면 그 컬럼이나 표현식의 결과가 NULL이 아닌 레코드 건수만 반환한다. 따라서 NULL이 될 수 있는 컬럼에 COUNT 함수를 사용할 때는 위도대로 쿼리가 작동하는지 확인이 필요하다.

JOIN

JOIN의 순서와 인덱스

일반적으로 인덱스를 이용해서 쿼리하는 작업에서는 가져오는 레코드 건수가 소량이기 때문에 인덱스 스캔 작업은 부하가 작지만 특정 인덱스키를 찾는 인덱스 탐색 작업은 상대적으로 부하가 높은편이다.

조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 그 이후부터는 스캔만 실행한다.

하지만 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복하므로 드라이빙 테이블과 드리븐 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 더 큰 부하를 차지한다.

이에 따라 드라이빙 테이블과 드리븐 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 더 큰 부하를 차지하므로 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

1
2
3
4

SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no
;

• 두 컬럼 모두 인덱스가 있는 경우
  • 어느 테이블을 드라이빙으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다.
  • 이러한 경우 옵티마이저가 통계 정보를 이용해 적절히 드라이빙 테이블을 선택하게 된다.
    • 레코드 건수 등
  • 이러한 경우 옵티마이저가 선택하는 방법이 최적인 경우가 많다.
• e.emp_no에만 인덱스가 있는 경우
  • dept_emp 테이블이 드리븐 테이블로 선택되면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔 해야만 조건에 일치하는 레코드를 찾을 수 있다.
  • 따라서 옵티마이저는 항상 dept_no 테이블을 드라이빙 테이블로 선택한다.
  • e.emp_no=1001 같이 employees 테이블을 아주 효율적으로 접근할 수 있는 경우에도 같다.
• de.emp_no에만 인덱스가 있는 경우
  • employees 테이블이 반복된 풀 스캔을 피하기 위해 드라이빙 테이블로 선택된다.
• 두 컬럼 모두 인덱스가 없는 경우
  • 어느 테이블을 드라이빙으로 선택하더라도 풀 스캔이 발생하므로 옵티마이저가 드라이빙 테이블을 적절히 선택한다.
    • 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 훨씬 효율적이다.
  • 조인 조건을 빠르게 처리할 적절한 인덱스가 없는 경우 MySQL 8.0.18 이전 버전은 블록 네스티드 루프 조인, 이후 버전은 해시 조인을 사용한다.

JOIN 컬럼의 데이터 타입

WHERE 절에 사용되는 조건에서 표현식의 데이터 타입을 동일하게 사용해야 하는 것과 마찬가지로 조인 조건에서도 동일하게 조인 컬럼 간의 비교에서 각 컬럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 사용할 수 없다.

비교 조건에서 양쪽 항이 상수이든 테이블의 컬럼이든 관계없이 데이터 타입이 다르다면 대상 컬럼에 대한 타입 변환 후 비교를 수행하므로 인덱스를 활용할 수 없게된다.

따라서 옵티마이저는 드리븐 테이블이 인덱스 레인지 스캔을 사용하지 못하고, 드리븐 테이블의 풀 테이블 스캔이 필요하게 된다는것을 미리 알기 때문에 조인 버퍼를 이용하여 작업을 수행한다.

예외 상황

인덱스 사용에 영향을 미치는 데이터 타입은 변환이 필요한 경우이며, 변환을 하지 않고도 비교할 수 있는 경우 인덱스를 활용할 수 있다.

• CHAR, VARCHAR
• INT, BIGINT, SMALLINT
• DATE, DATETIME

대표적으로 아래와 같은 패턴은 문제가 될 가능성이 높다.

• CHAR, INT 비교 같이 데이터 타입의 종류가 완전히 다른 경우
• 같은 CHAR 타입이라도 문자 집합이나 콜레이션이 다른 경우
• 같은 INT 타입이더라도 부호의 존재 여부가 다른 경우

이러한 상황을 예방하기 위해 컬럼의 문자 집합, 콜레이션을 통일하는 등 데이터베이스 모델에 대한 표준화 규칙을 수립하고, 규칙을 기반으로 설계를 진행해야 한다.

OUTER JOIN 성능과 주의사항

INNER JOIN은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환한다. 이러한 특성 때문에 아우터 조인으로만 조인을 실행하는 쿼리들도 자주 보이는데 일부 문제가 발생할 수 있는 여지가 있다.

• MySQL 옵티마이저는 절대로 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못하기 때문에 성능이 떨어지는 실행 계획을 수립할 수 있다.
  • 이너 조인으로 사용할 수 있는 쿼리를 아우터 조인으로 작성하면 옵티마이저가 조인 순서를 변경하며 수행할 수 있는 최적화 기회를 빼앗는 결과를 만들 수 있다.
  • 필요한 데이터가 조인되는 테이블 간의 관계를 명확히 파악해서 꼭 필요한 경우가 아니라면 이너 조인을 사용해야 한다.
• 아우터 조인으로 조인되는 테이블에 대한 조건을 WHERE 절에 함께 명시하는 것 이다.
  • 아우터 조인 드리븐 테이블 컬럼에 IS NULL이외의 조건이 있는 경우 옵티마이저거 INNER JOIN으로 변경한다.

JOIN과 외래키(FOREIGN KEY)

외래키를 생성하는 주목적은 데이터의 참조 무결성을 보장하기 위해서이며, 외래키는 조인 처리와 아무 관계가 없다.

• SQL로 테이블 간의 조인은 전혀 무관한 컬럼을 조인 조건으로 사용해도 문법적으로 문제가 되진 않는다.
• 하지만 데이터 모델링을 할 때는 각 테이블간의 관계를 표현하지만 외래키로 생성하지 않는 경우가 더 많다.
• 테이블 간의 조인을 사용하기 위해 외래키가 필요한 것은 아니다.

지연된 조인(Delayed Join)

지연된 조인이란 조인이 실행되기 이전에 GROUP BY, ORDER BY를 처리하는 방식을 의미한다.

조인을 사용해서 데이터를 조회하는 쿼리에 GROUP BY, ORDER BY를 사용할 때 각 처리 방법에서 인덱스를 사용한다면 이미 최적으로 처리되고 있을 가능성이 높지만, 그렇지 않다면 MySQL 서버는 모든 조인을 실행한 후 GROUP BY, ORDER BY를 처리한다.

조인은 대체로 실행되면 될수록 레코드 건수가 늘어나므로 GROUP BY, ORDER BY가 수행될 때는 조인 전 레코드에 수행할 때 보다 많은 레코드를 처리해야한다.

이를 개선하기 위한 최적화이며, 주로 LIMIT와 사용될 때 큰 효과를 얻을 수 있다.

지연된 조인 미적용

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

EXPLAIN
SELECT e.*
FROM salaries s, employees e
WHERE e.emp_no = s.emp_no
  AND s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10
;

/*
+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | range | PRIMARY           | PRIMARY | 4       | NULL               | 3000 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | NULL       | ref   | PRIMARY,ix_salary | PRIMARY | 4       | employees.e.emp_no |    9 |   100.00 | NULL                                         |
+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
*/

1. employees 테이블을 드라이빙 테이블로 선택하여 조건에 만족하는 레코드를 읽고, salaries 테이블을 조인한다.
2. 조인의 결과 12,000건의 레코드를 임시 테이블에 저장하고 GROUP BY 처리를 통해 3000건으로 줄인다.
3. ORDER BY 처리 후 상위 10건을 반환한다.

지연된 조인 적용

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

EXPLAIN
SELECT e.*
FROM 
  (SELECT s.emp_no
   FROM salaries s
   WHERE s.emp_no BETWEEN 10001 AND 13000
   GROUP BY s.emp_no
   ORDER BY SUM(s.salary) DESC
   LIMIT 10) x, 
   employees e
WHERE e.emp_no = x.emp_no
;

/*
+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys     | key     | key_len | ref      | rows  | filtered | Extra                                        |
+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL              | NULL    | NULL    | NULL     |    10 |   100.00 | NULL                                         |
|  1 | PRIMARY     | e          | NULL       | eq_ref | PRIMARY           | PRIMARY | 4       | x.emp_no |     1 |   100.00 | NULL                                         |
|  2 | DERIVED     | s          | NULL       | range  | PRIMARY,ix_salary | PRIMARY | 4       | NULL     | 56844 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+

-> Nested loop inner join  (cost=5 rows=0)
    -> Table scan on x  (cost=2.5..2.5 rows=0)
        -> Materialize  (cost=0..0 rows=0)
            -> Limit: 10 row(s)
                -> Sort: sum(s.salary) DESC, limit input to 10 row(s) per chunk
                    -> Stream results  (cost=17107 rows=56844)
                        -> Group aggregate: sum(s.salary)  (cost=17107 rows=56844)
                            -> Filter: (s.emp_no between 10001 and 13000)  (cost=11423 rows=56844)
                                -> Index range scan on s using PRIMARY over (10001 <= emp_no <= 13000)  (cost=11423 rows=56844)
    -> Single-row index lookup on e using PRIMARY (emp_no=x.emp_no)  (cost=0.26 rows=1)
*/

지연된 조인으로 개선된 쿼리는 임시 테이블을 한 번 더 사용하기 때문에 느라다고 예상할 수도 있지만, 임시 테이블에 저장할 레코드가 적으므로 메모리를 이용해 빠르게 처리된다. 실행 계획상으로 보면 지연된 조인으로 변경된 쿼리가 느릴 것 같지만 실제로는 3~4배 빠르다.

지연된 쿼리의 원리를 정확히 이해하지 못한 상태로 작성하면 오히려 역효과가 날 수도 있지만 잘 튜닝된 지연된 쿼리는 원래 쿼리보다 훨씬 개선될 수 있다.

하지만 모든 쿼리를 지연된 쿼리 형태로 개선할 수 있는것이 아니며 아래 조건을 만족해야한다.

• LEFT (OUTER) JOIN
  • 드라이빙 테이블과 드리븐 테이블은 1:1, M:1 관계여야 한다.
• INNER JOIN
  • 드라이빙 테이블과 드리븐 테이블은 1:1, M:1 관계여야 한다.
  • 드라이빙 테입르에 있는 레코드는 드리븐 테이블에 모두 존재해야 한다.
    • 두 번째와 세번째 조건은 드라이빙 테이블을 서브쿼리로 만들고 이 서브쿼리에 LIMIT를 추가해도 최종 결과의 건수가 변하지 않는다는 보증을 해주는 조건이기 때문에 반드시 정확히 확인한 후 적용해야 한다.

지연된 조인은 조인의 개수를 줄이는 것뿐만 아니라 GROUP BY, ORDER BY 처리가 필요한 레코드의 전체 크기를 줄이는 역할도 한다.

래터럴 조인(Lateral Join)

MySQL 버전부터는 래터럴 조인이라는 기능을 이용해 특정 그룹별로 서브쿼리를 실행해서 그 결과와 조인하는 것이 가능해졌다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT *
FROM employees e
  LEFT JOIN LATERAL (
    SELECT *
    FROM salaries s
    WHERE s.emp_no = e.emp_no
    ORDER BY s.from_date DESC LIMIT 2
  ) s2 ON s2.emp_no = e.emp_no
WHERE e.frist_name = 'Matt'
;                     

위 쿼리는 employees 테입르에서 이름이 Matt인 사원에 대해 사원별로 가장 최근 급여 변경 내역을 최대 2건씩만 반환한다.

래터럴 조인에서 가장 중요한 부분은 FROM 절에 사용된 서브쿼리에서 외부 쿼리의 FROM 절에 정의된 테이블의 컬럼을 참조할 수 있다는 점이다. FROM 절에 사용하는 서브쿼리가 외부 쿼리의 컬럼을 참조하기 위해서는 LATERAL 키워드를 명시해야하며 누락된다면 오류를 발생시킨다.

LATERAL 키워드를 가진 서브쿼리는 조인 순서상 후순위로 밀리고, 외부 쿼리의 결과 레코드 단위로 임시 테이블이 생성되기 때문에 꼭 필요한 경우에만 사용해야 한다.

실행 계획으로 인한 정렬 흐트러짐

MySQL 8.0 이전 버전 까지는 알고리즘 특성상 테이블에서 읽은 순서가 유지되는 네스티드-루프 방식의 조인만 가능했지만, 정렬 순서가 바뀌는 해시 조인이 도입되면서 선택되는 정렬 알고리즘에 따라 순서가 보장되지 않을 수 있다.

실행 계획은 옵티마이저에 의해 달라질 수 있으므로 정렬된 결과가 필요한 경우라면 드라이빙 테이블의 순서에 의존하지 말고 ORDER BY절을 사용하는 것이 좋다.