인덱스와 관련된 몇 가지 SQL 튜닝 예제 (결합 인덱스, 인덱스 사용할 때 느린 경우, 인덱스가 없는 경우 등)

이 글은 책 업무에 바로 쓰는 SQL 튜닝 4,5 장 Github repo 예제, 하단 reference 참고했습니다. 저작권 문제 발생 시 게시물이 비공개될 수 있습니다. 잘못된 내용이 있다면 편하게 말씀해주세요.

이전 글 MySQL 실행 계획과 결과 컬럼 설명 (MySQL EXPLAIN Output Format)에서 MySQL 실행 계획과 결과를 알아보았다. 이번 글에선 실행 계획을 활용하여 인덱스와 관련된 몇 가지 SQL 튜닝 예제를 살펴보고자 한다.

Case 1 : 결합 인덱스 순서 고려 없이 사용하는 SQL문

사원 테이블과 index 구성은 아래와 같다.

사원 테이블에서 성(Family Name)과 성별(Sex) 순서로 그루핑하여 몇 건의 데이터가 있는지 구분하고 싶다. 다시 말해 성이 김씨인 여자는 몇 명이고 이씨의 남자는 몇 명인지 확인하고 싶다. 특별한 점은 인덱스 I_성별_성 는 결합 인덱스로 이를 활용할 수 있다.

desc 사원;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| 사원번호     | int           | NO   | PRI | NULL    |       |
| 생년월일     | date          | NO   |     | NULL    |       |
| 이름        | varchar(14)   | NO   |     | NULL    |       |
| 성         | varchar(16)   | NO   |     | NULL    |       |
| 성별        | enum('M','F') | NO   | MUL | NULL    |       |
| 입사일자     | date          | NO   | MUL | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

show index from 사원;

+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name       | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 사원   |          0 | PRIMARY        |            1 | 사원번호     | A         |      299335 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원   |          1 | I_입사일자     |            1 | 입사일자     | A         |        4895 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원   |          1 | I_성별_성      |            1 | 성별         | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원   |          1 | I_성별_성      |            2 | 성           | A         |        3385 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

성(Family Name)과 성별(Sex) 순서로 그루핑하여 몇 건의 데이터가 있는지 구분하는 SQL은 아래와 같다. 총 3274 건이 출력되었고 0.82초가 걸렸다. 문제가 없어 보인다. 실행 계획을 살펴보자.

SELECT 성, 성별, COUNT(1) as 카운트
  FROM 사원
 GROUP BY 성, 성별;
+-----------+--------+-----------+
| 성        | 성별   | 카운트    |
+-----------+--------+-----------+
| Aamodt    | M      |       120 |
| Aamodt    | F      |        85 |
생략
| Zykh      | M      |        87 |
| Zykh      | F      |        61 |
+------------------+--------+-----------+
3274 rows in set (0.82 sec)

튜닝 전 실행계획

EXPLAIN
SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성, 성별;

+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows   | filtered | Extra                        |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
|  1 | SIMPLE      | 사원   | NULL       | index | I_성별_성     | I_성별_성    | 51      | NULL | 299335 |   100.00 | Using index; Using temporary |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

table : 사원 -> 사원 테이블을 사용한다

type : index -> 인덱스 풀스캔을 사용한다.

key : I_성별_성 -> I_성별_성 인덱스를 사용한다.

Extra : Using index -> 테이블 접근 없이 인덱스만 읽어서 SQL문을 처리한다. (커버링 인덱스라고 함)

Extra : Using temporary -> 임시 테이블을 생성한다.

사원 테이블의 I_성별_성 인덱스를 활용하고, 임시테이블(Using temporary)을 생성하여 성, 성별을 그루핑하여 카운트 연산한다.

I_성별_성 인덱스의 구성열이 GROUP BY 절에 포함되므로, 테이블 접근 없이 인덱스만 사용하는 커버링 인덱스(Using index)로 수행된다.

개선점

I_성별_성 인덱스를 활용하여 좋아 보인다. 하지만 임시 테이블을 꼭 생성해야 할지 고민해야 한다. 인덱스만으로 카운트 연산을 수행할 수 없는지 확인해야 한다.

현재 I_성별_성 인덱스는 1. 성별 2. 성 열 순으로 생성된 인덱스이다. 다시 말해 해당 인덱스는 성별 열 기준으로 정렬된 뒤 성 열 기준으로 정렬되었다. 현재는 성을 먼저 정렬하고 난 뒤 성별을 정렬하기에 온전히 인덱스를 사용하지 못하고 있다.

튜닝 후

기존 Group BY 문의 GROUP BY 성, 성별 -> GROUP BY 성별, 성 순서로 변경하였다. I_성별_성 인덱스 순서대로 그루핑했다.

3274 건의 동일한 결과로, 0.82초 -> 0.11초로 시간 단축되었다.

SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성별, 성;

+-----------+--------+-----------+
| 성        | 성별   | 카운트    |
+-----------+--------+-----------+
| Aamodt    | M      |       120 |
| Aamodt    | F      |        85 |
생략
| Zykh      | M      |        87 |
| Zykh      | F      |        61 |
+------------------+--------+-
3274 rows in set (0.11 sec)

튜닝 후 실행계획

EXPLAIN
SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성별, 성;

+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 사원   | NULL       | index | I_성별_성     | I_성별_성    | 51      | NULL | 299335 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Extra를 살펴보면 더 이상 임시 테이블(Using temporary)을 만들지 않는 것을 볼 수 있다.

한 줄 요약 : 임시 테이블은 성능을 나쁘게 만들 수 있다. 결합 인덱스 사용할 때 순서를 고려하자.

Case 2 : 인덱스를 사용할 때 오히려 더 느린 SQL 문

사원출입기록 테이블과 인덱스는 아래와 같다. 이때 사원출입기록 테이블에서 출입문이 B인 모든 정보를 조회하고 싶다. 특별한 점은 출입문 컬럼은 I_출입문 인덱스로 지정되어있다.

desc 사원출입기록;
+-----------------+-----------+------+-----+-------------------+-------------------+
| Field           | Type      | Null | Key | Default           | Extra             |
+-----------------+-----------+------+-----+-------------------+-------------------+
| 순번            | int       | NO   | PRI | NULL              | auto_increment    |
| 사원번호         | int       | NO   | PRI | NULL              |                   |
| 입출입시간        | timestamp | NO   | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| 입출입구분        | char(1)   | NO   |     | NULL              |                   |
| 출입문           | char(1)   | YES  | MUL | NULL              |                   |
| 지역            | char(1)   | YES  | MUL | NULL              |                   |
+-----------------+-----------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

show index from 사원출입기록;
+--------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table              | Non_unique | Key_name    | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| 사원출입기록       |          0 | PRIMARY    |            1 | 순번            | A         |      658935 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원출입기록       |          0 | PRIMARY    |            2 | 사원번호        | A         |      658935 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원출입기록       |          1 | I_지역      |            1 | 지역            | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| 사원출입기록       |          1 | I_시간      |            1 | 입출입시간      | A         |      633298 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 사원출입기록       |          1 | I_출입문     |            1 | 출입문          | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

사원출입기록 테이블에서 출입문이 B인 모든 정보를 조회하는 쿼리는 아래와 같다. 해당 쿼리는 30만건이 출력되었고 0.95초가 걸렸다.

사원출입기록 테이블의 모든 레코드는 660000개이다. 출입문이 B인 정보는 300000개이다. 실행계획을 살펴보자.

SELECT *
FROM 사원출입기록
WHERE 출입문 = 'B';

+--------+--------------+---------------------+-----------------+-----------+--------+
| 순번   | 사원번호     | 입출입시간          | 입출입구분      | 출입문    | 지역   |
+--------+--------------+---------------------+-----------------+-----------+--------+
| 983026 |       110022 | 2020-05-26 11:16:28 | I               | B         | b      |
생략
| 1329630 |       299629 | 2020-08-15 02:21:12 | O               | B         | b      |
+---------+--------------+---------------------+-----------------+-----------+--------+
300000 rows in set (0.95 sec)


SELECT COUNT(1) 
FROM 사원출입기록;
+----------+
| count(1) |
+----------+
|   660000 |
+----------+

SELECT 출입문, COUNT(1)
FROM 사원출입기록
GROUP BY 출입문;

+-----------+----------+
| 출입문    | COUNT(1) |
+-----------+----------+
| A         |   250000 |
| B         |   300000 |
| C         |    10000 |
| D         |   100000 |
+-----------+----------+
4 rows in set (0.22 sec)

튜닝 전 실행계획

EXPLAIN
SELECT *
FROM 사원출입기록
WHERE 출입문 = 'B';

+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| id | select_type | table              | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | 사원출입기록       | NULL       | ref  | I_출입문      | I_출입문    | 4       | const | 329467 |   100.00 | NULL  |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.01 sec)

table : 사원출입기록 -> 사원출입기록 테이블을 사용한다.

type : ref -> WHERE 조건절의 = 비교 연산자를 사용하여 접근한다.

key : I_출입문 -> 인덱스를 사용한다.

ref : const -> 상수 조건으로 데이터 접근 범위를 줄였다.

개선 점

현재 I_출입문 인덱스를 사용해 출입문이 "B"인 레코드를 검색했다. 문제가 없어 보인다. 하지만사원출입기록 테이블의 데이터는 66만 건 중 B출입문은 30만 건이다. 약 45%의 데이터를 I_출입문 인덱스 스캔을 사용하여 데이터를 가져온다. 이는 랜덤 액세스 방식이다.

조회할 데이터가 많을 때 랜덤 액세스가 아니라 테이블 풀 스캔으로 변경하면 더 빨리 조회할 수 있다.

튜닝 후

IGNORE INDEX(I_출입문) 힌트를 사용하여 인덱스를 사용하지 않고 풀 스캔하도록 한다. 동일한 결과가 나왔고 기존 0.95초 -> 0.47초로 단축시켰다.

SELECT *
FROM 사원출입기록 IGNORE INDEX(I_출입문)
WHERE 출입문 = 'B';

+--------+--------------+---------------------+-----------------+-----------+--------+
| 순번   | 사원번호     | 입출입시간          | 입출입구분      | 출입문    | 지역   |
+--------+--------------+---------------------+-----------------+-----------+--------+
| 983026 |       110022 | 2020-05-26 11:16:28 | I               | B         | b      |
생략
| 1329630 |       299629 | 2020-08-15 02:21:12 | O               | B         | b      |
+---------+--------------+---------------------+-----------------+-----------+--------+
300000 rows in set (0.47 sec)

튜닝 후 실행 계획

EXPLAIN
SELECT *
FROM 사원출입기록 IGNORE INDEX(I_출입문)
WHERE 출입문 = 'B';

+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 사원출입기록       | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 658935 |    10.00 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

옵티마이저가 IGNORE INDEX 힌트를 보고 인덱스를 사용하지 않았다.

type : ALL -> 테이블 풀 스캔

key : NULL -> 인덱스 사용 X

한 줄 요약 : 검색할 데이터양이 많을 땐 인덱스 스캔(랜덤 액세스) 보다 테이블 풀 스캔이 더 빠를 수 있다.

Case 3 : 인덱스 없이 작은 규모 데이터 조회하는 SQL 문

상황

한 테이블에서 작은 범위의 데이터를 조회한다고 가정하자. 이때 WHERE 조건의 데이터가 인덱스가 없다.

테이블 풀 스캔으로 검색된다.

개선 점

이때 검색되는 데이터가 약 10% 이하이고 자주 반복되는 쿼리라면 해당 검색 조건 열에 인덱스를 만들면 빠를 수 있다.

Case 4 : 인덱스 하나만 사용하는 사용하는 SQL 문

상황

한 테이블에서 두 개 열을 특정 조건에 맞는(WHERE 조건) 데이터를 조회한다고 가정하자. 이때 하나의 열에만 인덱스가 걸려있다. 이때 검색되는 데이터가 약 10% 이하이고 하나 있는 인덱스를 안탈 수 있다.

개선 점

이때 인덱스가 없는 열에 대해 인덱스 또는 결합 인덱스를 만들어 검색 효율을 높일 수 있다.

Case 5 : 큰 규모의 데이터 변경(update) 할 때 변경하는 열에 인덱스가 걸려있는 SQL 문

상황

한 테이블(e.g 사원)의 레코드가 300만 개라고 가정하자. 이때 특정 열(e.g. 조직)의 특정 데이터('마케팅')를 다른 데이터('Data 마케팅 분석') 으로 변경하고자 한다. 이때 변경할 데이터가 100만 개라고 한다. 특정 열(조직)에는 세컨더리 인덱스가 걸려있다. UPDATE 쿼리로 (e.g UPDATE 사원 SET 조직 = 'DATA 마케팅 분석' WHERE 조직 = '마케팅')으로 데이터를 변경한다.

개선 점

현재 문제는 두 가지가 있고 개선할 수 있다.

1. autocommit 이 꺼져 있는지 확인해야 한다. update마다 commit 하면 오래 걸릴 수 있다. set autocommit=0으로 옵션을 변경할 수 있다.

2. 변경하고자 하는 열에 인덱스가 걸려있다. 이는 insert, update 할 때마다 DB page의 raw data를 바꾸고, 인덱스 걸려있는 값까지 변경해야 한다. 인덱스가 업데이트에 영향을 미친다. 해당 인덱스를 지워 빠르게 큰 규모의 데이터를 변경할 수 있다.