MySQL 쿼리 튜닝 기초
- 1. 기본키를 변형하는 쿼리
- 2. 사용이 불필요한 함수를 포함하는 쿼리
- 3. 형변환으로 인덱스를 활용하지 못하는 쿼리
- 4. 열을 결합하여 사용하는 쿼리
- 5. 습관적으로 중복을 제거하는 쿼리
- 6. 다수 쿼리를 UNION 연산자로 합치는 쿼리
- 7. 인덱스 고려 없이 열을 사용하는 쿼리
- 8. 엉뚱한 인덱스를 사용하는 쿼리
- 9. 동등 조건으로 인덱스를 사용하는 쿼리
- 10. 작은 테이블이 먼저 조인에 참여하는 쿼리
- 11. 메인 테이블에 계속 의존하는 쿼리
- 12. 불필요한 조인을 수행하는 쿼리
- 정리
업무에 바로 쓰는 SQL 튜닝 책에 있는 내용을 기반으로 해당 글을 작성한다.
테스트를 위한 데이터는 책에서 제공한
https://github.com/7ieon/SQLtune 을 사용하였다.
우선 튜닝 기초로서 스키마나 인덱스를 수정하지 않고,
단순 쿼리 수정을 통한 쿼리 튜닝을 진행하려고 한다.
1. 기본키를 변형하는 쿼리
사원번호 1100으로 시작하면서 사원번호가 5자리인 사원을 출력하는 쿼리
개선 전
SELECT *
FROM 사원
WHERE SUBSTRING(사원번호,1,4) = 1100
AND LENGTH(사원번호) = 5;
-> 10 rows in set (0.15 sec)
실행 계획
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | ALL | NULL | NULL | NULL | NULL | 299822 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
원인 : 좌변 가공이 일어나 인덱스를 제대로 타지 못하고 full scan이 발생
개선 후
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009;
-> 10 rows in set (0.00 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 10 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
개선 : 사원번호 인덱스(pk)를 탈 수 있도록 쿼리 개선
2. 사용이 불필요한 함수를 포함하는 쿼리
사원 테이블에서 성별마다 몇 명의 사원이 있는지 출력하는 쿼리
개선 전
SELECT IFNULL(성별, 'NO DATA') AS 성별, COUNT(1) 건수
FROM 사원
GROUP BY IFNULL(성별, 'NO DATA');
-> 2 rows in set (0.28 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
| 1 | SIMPLE | 사원 | NULL | index | I_성별_성 | I_성별_성 | 51 | NULL | 299822 | 100.00 | Using index; Using temporary |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
원인 : IFNULL 함수를 사용하여 임시테이블 사용
개선 후
SELECT 성별, COUNT(1) 건수
FROM 사원
GROUP BY 성별;
-> 2 rows in set (0.08 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | index | I_성별_성 | I_성별_성 | 51 | NULL | 299822 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
개선 : 성별은 not null 컬럼으로 불필요한 함수의 사용을 제거
3. 형변환으로 인덱스를 활용하지 못하는 쿼리
급여 테이블에서 유효한 급여 정보를 조회하고자 count를 출력하는 쿼리
개선 전
SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = 1;
-> 1 row in set (0.41 sec)
실행 계획
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+--------------------------+
| 1 | SIMPLE | 급여 | NULL | index | I_사용여부 | I_사용여부 | 4 | NULL | 2838065 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+---------+----------+--------------------------+
원인 : 사용여부는 char(1) 컬럼이나 정수형 값으로 조회하여 내부에서 형변환이 발생
개선 후
SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = '1';
-> 1 row in set (0.02 sec)
실행 계획
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+-------+----------+-------------+
| 1 | SIMPLE | 급여 | NULL | ref | I_사용여부 | I_사용여부 | 4 | const | 82824 | 100.00 | Using index |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+-------+----------+-------------+
개선 : 형변환이 발생하지 않도록 문자형인 ‘1’로 조회
4. 열을 결합하여 사용하는 쿼리
사원 중 성별과 성을 결합한 결과가 ‘M Radwan’인 데이터를 조회하는 쿼리
개선 전
SELECT *
FROM 사원
WHERE CONCAT(성별,' ',성) = 'M Radwan';
-> 102 rows in set (0.14 sec)
실행 계획
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | ALL | NULL | NULL | NULL | NULL | 299822 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
원인 : 좌변 가공을 하여 full table scan이 발생
개선 후
SELECT *
FROM 사원
WHERE 성별 = 'M'
AND 성 = 'Radwan';
-> 102 rows in set (0.01 sec)
실행 계획
+----+-------------+--------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | ref | I_성별_성 | I_성별_성 | 51 | const,const | 102 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------------+------+----------+-------+
개선 : 좌변 가공을 하지않게 변경하여 인덱스 사용
5. 습관적으로 중복을 제거하는 쿼리
부서 관리자의 정보를 중복 제거하여 조회하는 쿼리
개선 전
SELECT DISTINCT 사원.사원번호, 사원.이름, 사원.성, 부서관리자.부서번호
FROM 사원
JOIN 부서관리자
ON 사원.사원번호 = 부서관리자.사원번호;
-> 24 rows in set (0.01 sec)
실행 계획
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+------------------------------+
| 1 | SIMPLE | 부서관리자 | NULL | index | PRIMARY | I_부서번호 | 12 | NULL | 24 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | 사원 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | tuning.부서관리자.사원번호 | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+------------------------------+
원인 : distinct 키워드는 나열된 열들을 정렬 후 중복 데이터를 삭제함.
따라서 distinct를 사용하는 것만으로도 정렬 작업이 포함됨.
사원 테이블의 사원번호는 pk로 중복된 데이터가 없음.
또한 실행계획에서 type이 eq_ref를 확인할 수 있으므로 distinct를 사용할 필요가 없다.
개선 후
SELECT 사원.사원번호, 사원.이름, 사원.성, 부서관리자.부서번호
FROM 사원
JOIN 부서관리자
ON 사원.사원번호 = 부서관리자.사원번호;
-> 24 rows in set (0.00 sec)
실행 계획
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+-------------+
| 1 | SIMPLE | 부서관리자 | NULL | index | PRIMARY | I_부서번호 | 12 | NULL | 24 | 100.00 | Using index |
| 1 | SIMPLE | 사원 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | tuning.부서관리자.사원번호 | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+--------+---------------+----------------+---------+-------------------------------------+------+----------+-------------+
개선 : distinct를 제거하여 임시 테이블 사용하지 않게 변경
6. 다수 쿼리를 UNION 연산자로 합치는 쿼리
성이 Baba이면서 성별이 M인 사원 + 성이 Baba이면서 성별이 F인 데이터를 조회
개선 전
SELECT 'M' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'M'
AND 성 = 'Baba'
UNION
SELECT 'F' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'F'
AND 성 = 'Baba';
-> 226 rows in set (0.00 sec)
실행 계획
+----+--------------+------------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------+
| 1 | PRIMARY | 사원 | NULL | ref | I_성별_성 | I_성별_성 | 51 | const,const | 135 | 100.00 | Using index |
| 2 | UNION | 사원 | NULL | ref | I_성별_성 | I_성별_성 | 51 | const,const | 91 | 100.00 | Using index |
| 3 | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+--------------+---------+-------------+------+----------+-----------------+
원인 : 중복된 데이터가 없으나 UNION을 사용하여 중복제거를 위한 임시테이블을 사용
개선 후
SELECT 성별, 사원번호
FROM 사원
WHERE 성 = 'Baba';
-> 226 rows in set (0.00 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+-------+----------+----------------------------------------+
| 1 | SIMPLE | 사원 | NULL | range | I_성별_성 | I_성별_성 | 51 | NULL | 29982 | 100.00 | Using where; Using index for skip scan |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+-------+----------+----------------------------------------+
개선 : 책에서는 성 단일 인덱스가 없어 성별_성 인덱스를 활용하여 UNION 대신 UNION ALL로 조회하지만
성별이 M, F밖에 존재하지않고 조회하려는 성이 동일하므로 단일 쿼리로 조회하는 것이 더 좋다고 판단함
7. 인덱스 고려 없이 열을 사용하는 쿼리
성과 성별 순서로 그루핑하여 몇 건의 데이터가 있는지 구하는 쿼리
개선 전
SELECT 성, 성별, COUNT(1) AS 카운트
FROM 사원
GROUP BY 성, 성별;
-> 3274 rows in set (0.38 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
| 1 | SIMPLE | 사원 | NULL | index | I_성별_성 | I_성별_성 | 51 | NULL | 299822 | 100.00 | Using index; Using temporary |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+------------------------------+
원인 : 현재 인덱스가 (성별, 성)으로 생성되어 있는 상태이나 반대로 group by를 조회하여 임시테이블 사용
개선 후
SELECT 성, 성별, COUNT(1) AS 카운트
FROM 사원
GROUP BY 성별, 성;
-> 3274 rows in set (0.09 sec)
실행 계획
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | index | I_성별_성 | I_성별_성 | 51 | NULL | 299822 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+--------+----------+-------------+
개선 : 인덱스 순으로 group by를 해도 결과가 변하지 않으므로 순서를 변경하여 인덱스를 제대로 탈 수 있게 함.
8. 엉뚱한 인덱스를 사용하는 쿼리
입사일자가 1989년이면서 사원번호가 100,000번 넘어가는 사원 조회
개선 전
SELECT 사원번호
FROM 사원
WHERE 입사일자 LIKE '1989%'
AND 사원번호 > 100000;
-> 20001 rows in set (0.09 sec)
실행 계획
+----+-------------+--------+------------+-------+------------------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY,I_입사일자 | PRIMARY | 4 | NULL | 149911 | 11.11 | Using where |
+----+-------------+--------+------------+-------+------------------------+---------+---------+------+--------+----------+-------------+
원인 : 입사일자로 필터링시 28394/300024건.
사원번호로 필터링시 210024/300024건으로 입사일자로 먼저 필터링하는게 좋아 보이는 상황
개선 후
SELECT 사원번호
FROM 사원 USE INDEX (I_입사일자)
WHERE 입사일자 LIKE '1989%'
AND 사원번호 > 100000;
-> 20001 rows in set (0.17 sec)
실행 계획
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+----------------------------------------+
| 1 | SIMPLE | 사원 | NULL | range | I_입사일자 | I_입사일자 | 7 | NULL | 99930 | 100.00 | Using where; Using index for skip scan |
+----+-------------+--------+------------+-------+----------------+----------------+---------+------+-------+----------+----------------------------------------+
원인 : 하지만 실제 쿼리 실행 시, pk를 타는 것보다 더 오래걸리는 현상이 발생
개선 후2
SELECT 사원번호
FROM 사원
WHERE 입사일자 BETWEEN '1989-01-01' AND '1989-12-31'
AND 사원번호 > 100000;
-> 20001 rows in set (0.02 sec)
실행 계획
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY,I_입사일자 | I_입사일자 | 7 | NULL | 49824 | 50.00 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------------+----------------+---------+------+-------+----------+--------------------------+
개선 : date컬럼을 like검색 하지않고 between이나 부등호 조건을 사용하여 성능을 향상
9. 동등 조건으로 인덱스를 사용하는 쿼리
B 출입문으로 출입한 이력이 있는 정보를 조회하는 쿼리
개선 전
SELECT *
FROM 사원출입기록
WHERE 출입문 = 'B';
-> 300000 rows in set (0.35 sec)
실행 계획
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
| 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 |
+----+-------------+--------------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
원인 : 인덱스는 제대로 타고 있으나 전체 데이터 중 약 50%를 조회하여 랜덤 액세스가 다수 발생하는 상황
개선 후
SELECT *
FROM 사원출입기록 IGNORE INDEX (I_출입문)
WHERE 출입문 = 'B';
-> 300000 rows in set (0.28 sec)
실행 계획
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 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 |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
개선 : 이 경우 오히려 table full scan이 랜덤 액세스가 발생하지 않아 더 빠른경우
10. 작은 테이블이 먼저 조인에 참여하는 쿼리
부서사원_매핑과 부서 테이블을 조인하여 부서 시작일자가 2002-03-01 이후인 사원을 조회
개선 전
SELECT 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';
-> 1341 rows in set (0.37 sec)
실행 계획
+----+-------------+--------+------------+-------+----------------+----------------+---------+----------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------+----------------+---------+----------------------------+-------+----------+-------------+
| 1 | SIMPLE | 부서 | NULL | index | PRIMARY | UI_부서명 | 122 | NULL | 9 | 100.00 | Using index |
| 1 | SIMPLE | 매핑 | NULL | ref | I_부서번호 | I_부서번호 | 12 | tuning.부서.부서번호 | 41191 | 33.33 | Using where |
+----+-------------+--------+------------+-------+----------------+----------------+---------+----------------------------+-------+----------+-------------+
원인 : 우선 매핑 테이블에 시작일자 인덱스가 없고,
매핑 테이블은 33만건, 부서 테이블은 9건으로
이 경우 사이즈가 적은 부서가 driving 테이블이 되어버림
개선 후
SELECT STRAIGHT_JOIN 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';
-> 1341 rows in set (0.10 sec)
실행 계획
+----+-------------+--------+------------+--------+----------------+---------+---------+----------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+----------------+---------+---------+----------------------------+--------+----------+-------------+
| 1 | SIMPLE | 매핑 | NULL | ALL | I_부서번호 | NULL | NULL | NULL | 329534 | 33.33 | Using where |
| 1 | SIMPLE | 부서 | NULL | eq_ref | PRIMARY | PRIMARY | 12 | tuning.매핑.부서번호 | 1 | 100.00 | Using index |
+----+-------------+--------+------------+--------+----------------+---------+---------+----------------------------+--------+----------+-------------+
개선 : 하지만 where문으로 필터링이 진행되면 1341건으로 데이터 건수가 많이 줄어들어 이 경우는 매핑 테이블이 driving이 되는게 좋은 경우.
위와 같이 straight_join 힌트를 사용하면 from절에 명시된 순서대로 조인에 참가하도록 고정할 수 있다.
11. 메인 테이블에 계속 의존하는 쿼리
사원번호가 450,000보다 크고 최대 연봉이 100,000보다 큰 데이터 출력
개선 전
SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원
WHERE 사원번호 > 450000
AND ( SELECT MAX(연봉) FROM 급여 WHERE 사원번호 = 사원.사원번호 )
> 100000;
-> 3155 rows in set (0.20 sec)
실행 계획
+----+--------------------+--------+------------+-------+---------------+---------+---------+----------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+-------+---------------+---------+---------+----------------------------+--------+----------+-------------+
| 1 | PRIMARY | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 104330 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | 급여 | NULL | ref | PRIMARY | PRIMARY | 4 | tuning.사원.사원번호 | 9 | 100.00 | NULL |
+----+--------------------+--------+------------+-------+---------------+---------+---------+----------------------------+--------+----------+-------------+
원인 : 해당 쿼리는 의존성을 가진 서브쿼리
개선 후
SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원, 급여
WHERE 사원.사원번호 > 450000
AND 사원.사원번호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000;
-> 3155 rows in set (0.17 sec)
실행 계획
+----+-------------+--------+------------+-------+-------------------------------------+---------+---------+----------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------------------+---------+---------+----------------------------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY,I_입사일자,I_성별_성 | PRIMARY | 4 | NULL | 104330 | 100.00 | Using where |
| 1 | SIMPLE | 급여 | NULL | ref | PRIMARY | PRIMARY | 4 | tuning.사원.사원번호 | 9 | 100.00 | NULL |
+----+-------------+--------+------------+-------+-------------------------------------+---------+---------+----------------------------+--------+----------+-------------+
개선 : 조인으로 풀어서 처리
12. 불필요한 조인을 수행하는 쿼리
A출입문으로 출입한 사원이 몇명인지 구하는 쿼리
개선 전
SELECT COUNT(DISTINCT 사원.사원번호) as 데이터건수
FROM 사원, (SELECT 사원번호 FROM 사원출입기록 WHERE 출입문 = 'A') 기록
WHERE 사원.사원번호 = 기록.사원번호;
-> 1 row in set (0.33 sec)
실행 계획
+----+-------------+--------------------+------------+--------+---------------+-------------+---------+----------------------------------------+--------+----------+-------------+
| 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 | Using index |
| 1 | SIMPLE | 사원 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | tuning.사원출입기록.사원번호 | 1 | 100.00 | Using index |
+----+-------------+--------------------+------------+--------+---------------+-------------+---------+----------------------------------------+--------+----------+-------------+
원인 : 내부적으로 join으로 풀려서 처리하나 조인이 데이터 존재확인만을 위해 사용
개선 후
SELECT COUNT(DISTINCT 사원.사원번호) as 데이터건수
FROM 사원
WHERE EXISTS
(SELECT 1
FROM 사원출입기록 기록
WHERE 출입문 = 'A'
AND 기록.사원번호 = 사원.사원번호);
-> 1 row in set (0.22 sec)
실행 계획
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------------+--------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | index | PRIMARY | I_입사일자 | 3 | NULL | 299822 | 100.00 | Using index |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 4 | tuning.사원.사원번호 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | 기록 | NULL | ref | I_출입문 | I_출입문 | 4 | const | 329467 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------------+--------+----------+-------------+
개선 : EXISTS를 활용하여 개선
정리
정리하자면 좋은 쿼리를 위해 다음을 고려해보자.
- 좌변 가공을 하지말자.
- UNION 보다는 UNION ALL을 사용하자.
- 데이터 타입을 같게 하여 형변환이 일어나지 않게 하자.
- 불필요한 작업을 제거하자. (not null 컬럼에 ifnull, unique에 distinct 등)
- group by는 인덱스 순서를 맞춰서 사용하자.
- 가능하면 like보다는 범위 조건을 사용하자.
- 카디널리티에 따라 인덱스를 사용하지 않거나, 힌트를 사용하는 것도 고려하자.
