4. 테이블 조인 설정 변경으로 착한 쿼리 만들기

1) 작은 테이블이 먼저 조인에 참여하는 나쁜 SQL
2) 메인 테이블에 계속 의존하는 나쁜 SQL
3) 불필요한 조인을 수행하는 나쁜 SQL

1) 작은 테이블이 먼저 조인에 참여하는 나쁜 SQL

SELECT 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑,부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';

* 결과
+----+-------------+-------+------------+-------+---------------+------------+---------+----------------------+-------+----------+-------------+
| 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.부서.부서번호 | 41392 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+------------+---------+----------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• 인덱스 있는 테이블이 후행테이블
• 하지만 둘다 부서번호에 인덱스가 있기 때문에(부서=부서번호(pk), 부서사원_매핑=부서번호(index)) 기본적으로 작은 테이블 먼저 드라이빙
• count 해보면 부서<부서사원_매핑
• 따라서 STRAIGHT_JOIN 으로 드라이빙 테이블 강제해줌

SELECT STRAIGHT_JOIN 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';

* 결과
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------------+
|  1 | SIMPLE      | 매핑  | NULL       | ALL    | I_부서번호    | NULL    | NULL    | NULL                 | 331143 |    33.33 | Using where |
|  1 | SIMPLE      | 부서  | NULL       | eq_ref | PRIMARY       | PRIMARY | 12      | tuning.매핑.부서번호 |      1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

---

2) 메인 테이블에 계속 의존하는 나쁜 SQL

SELECT 사원.사원번호, 사원.이름, 사원.성
  FROM 사원
 WHERE 사원번호 > 450000
   AND ( SELECT MAX(연봉)
           FROM 급여
          WHERE 사원번호 = 사원.사원번호
       ) > 100000;
       
* 결과
+----+--------------------+-------+------------+-------+---------------+---------+---------+----------------------+--------+----------+-------------+
| 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        |
+----+--------------------+-------+------------+-------+---------------+---------+---------+----------------------+--------+----------+-------------+
2 rows in set, 2 warnings (0.01 sec)

• 쿼리 목적 : 사원번호 450000 이상, 최대 연봉이 100000원 이상 데이터
• 후행테이블이 DEPENDENT (외부 테이블에서 조건절을 받아 처리되어야함) 는 튜닝 대상
• 성능 : 서브쿼리 < 조인
• 서브쿼리를 조인으로 변경

SELECT 사원.사원번호,
       사원.이름,
       사원.성
  FROM 사원,
       급여
 WHERE 사원.사원번호 > 450000
   AND 사원.사원번호 = 급여.사원번호
 GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000;

* 결과
+----+-------------+-------+------------+-------+------------------------------+---------+---------+----------------------+--------+----------+-------------+
| 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        |
+----+-------------+-------+------------+-------+------------------------------+---------+---------+----------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

---

3) 불필요한 조인을 수행하는 나쁜 SQL

SELECT COUNT(DISTINCT 사원.사원번호) as 데이터건수
  FROM 사원,
       ( SELECT 사원번호
           FROM 사원출입기록 기록
          WHERE 출입문 = 'A'
       ) 기록
 WHERE 사원.사원번호 = 기록.사원번호;
 
* 결과
+----+-------------+-------+------------+--------+---------------+----------+---------+----------------------+--------+----------+-------------+
| 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 |
+----+-------------+-------+------------+--------+---------------+----------+---------+----------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• distinct 는 튜닝요소
• 사원출입기록 테이블의 사원번호는 사원 테이블과 조인을 수행하는 과정 중에 값의 존재 여부만 알면 됨

SELECT COUNT(1) as 데이터건수
  FROM 사원
 WHERE EXISTS (SELECT 1
                 FROM 사원출입기록 기록
                WHERE 출입문 = 'A'
                AND 기록.사원번호 = 사원.사원번호);
				
* 결과
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+--------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                  | rows   | filtered | Extra                    |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+--------+----------+--------------------------+
|  1 | SIMPLE       | 사원        | NULL       | index  | PRIMARY             | I_입사일자          | 3       | NULL                 | 299157 |   100.00 | Using where; 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              |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+----------------------+--------+----------+--------------------------+
3 rows in set, 2 warnings (0.00 sec)