4.3 장
테이블 조인 설정 변경으로 착한 쿼리 만들기
1) 작은 테이블이 먼저 조인에 참여하는 나쁜 SQL
2) 메인 테이블에 계속 의존하는 나쁜 SQL
3) 불필요한 조인을 수행하는 나쁜 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)
) 기본적으로 작은 테이블 먼저 드라이빙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)
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)
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)
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)
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)