4-2. SQL문 단순 수정으로 착한 쿼리 만들기

1) 기본키를 변형하는 나쁜 SQL문
2) 사용하지 않는 함수를 포함하는 나쁜 SQL문
3) 형변환으로 인덱스 활용하지 못하는 나쁜 SQL문
4) 열을 결합하여 사용하는 나쁜 SQL문
5) 습관적으로 중복을 제거하는 나쁜 SQL문
6) 다수 쿼리를 UNION 연산자로만 합치는 나쁜 SQL문
7) 인덱스 고려 없이 열을 사용하는 나쁜 SQL문
8) 엉뚱한 인덱스를 사용하는 나쁜 SQL문
9) 동등 조건으로 인덱스를 사용하는 나쁜 SQL문
10) 범위 조건으로 인덱스를 사용하는 나쁜 SQL 문

1) 기본키를 변형하는 나쁜 SQL문

튜닝 전 (SUBSTRING)

• SUBSTRING의 WHERE 조건절에 사용 => TYPE : ALL(전체테이블 탐색)이 됨

SELECT *
FROM 사원
WHERE SUBSTRING(사원번호,1,4) = 1100
AND LENGTH(사원번호) = 5;

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

 

튜닝 후 (BETWEEN)

• WHERE에 SUBSTRING을 사용하지 않고 BETWEEN을 사용 => TYPE : RANGE

select * from 사원 
where 사원번호 between 11000 and 11009  
and length(사원번호)=5;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

2) 사용하지 않는 함수를 포함하는 나쁜 SQL문

튜닝 전

• 이미 NOT NULL인 곳에 IFNULL을 사용 => extra : Using temporary

SELECT IFNULL(성별,'NO DATA') AS 성별, COUNT(1) 건수
FROM 사원
GROUP BY IFNULL(성별,'NO DATA');

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

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)

튜닝 후

• 성별 컬럼은 이미 NOT NULL 이기 때문에 IFNULL 함수를 사용할 필요가 없어서 없앰 => Using Temporary 사라짐

SELECT 성별, COUNT(1) 건수
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 | 299157 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

3) 변환으로 인덱스 활용하지 못하는 나쁜 SQL문

튜닝 전

• char 형식 column에  where int 절로 필터링함 => 묵시적인 형변환 발생 => index 활용안하고 전체 데이터 스캔

SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = 1;

* 결과
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | 급여  | NULL       | index | I_사용여부    | I_사용여부 | 4       | NULL | 2838731 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

 

튜닝 후

• where char절로 필터링함 => extra : Using where 사라짐

desc 급여;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| 사원번호 | int     | NO   | PRI | NULL    |       |
| 연봉     | int     | NO   |     | NULL    |       |
| 시작일자 | date    | NO   | PRI | NULL    |       |
| 종료일자 | date    | NO   |     | NULL    |       |
| 사용여부 | char(1) | YES  | MUL |         |       |
+----------+---------+------+-----+---------+-------+
5 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         |      298323 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 급여  |          0 | PRIMARY    |            2 | 시작일자    | A         |     2838731 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| 급여  |          1 | I_사용여부 |            1 | 사용여부    | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)


# 튜닝 후
SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = '1';

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

• 컬럼 형식 자체를 CHAR(1) => INT 로 변환할 수는 있지만 테이블의 DDL 문을 수행해야하는 부담과 수행 시의 데이터 잠김 현상으로 동시성 저하 문제 발생 가능

---

4) 열을 결합하여 사용하는 나쁜 SQL문

튜닝 전

• concat 으로 문자열 결합하면 인덱스를 제대로 타지 못함

SELECT *
FROM 사원
WHERE CONCAT(성별,' ',성) = 'M Radwan';

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

튜닝 후

• 따로 따로 and 조건으로 나열해서 조회

SELECT *
FROM 사원
WHERE 성별 = 'M'
AND 성 =  'Radwan';

+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
| 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  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

---

5) 습관적으로 중복을 제거하는 나쁜 SQL문

튜닝 전

• 사원번호 컬럼은 기본키(primary key)인데, 굳이 DISTINCT 로 중복을 제거함

SELECT DISTINCT 사원.사원번호, 이름, 성, 부서번호
FROM 사원
JOIN 부서관리자
ON (사원.사원번호 = 부서관리자. 사원번호);

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

 

튜닝 후

• DISTINCT 제거

SELECT 사원.사원번호, 이름, 성, 부서번호
FROM 사원
JOIN 부서관리자
ON (사원.사원번호 = 부서관리자. 사원번호);

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

---

6) 다수 쿼리를 UNION 연산자로만 합치는 나쁜 SQL문

• 튜닝 전 : 중복이 없는데도, UNION을 사용해서 중복을 제거하고 있음
• 튜닝 후 : UNION 대신 UNION ALL 사용해서 중복 제거 과정을 스킵함

---

7) 인덱스 고려 없이 열을 사용하는 나쁜 SQL문

튜닝 전 

• index(성별,성) 으로 이루어져있는데 GROUP BY 성,성별해서 인덱스를 못탐 => extra : Using temporary 발생

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 | 299157 |   100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

튜닝 후

• Group BY 성별, 성 으로 수정

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 | 299157 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

8) 엉뚱한 인덱스를 사용하는 나쁜 SQL문

튜닝 전

like를 사용함 => extra : 인덱스를 안사용함

SELECT 사원번호
FROM 사원
WHERE 입사일자 LIKE '1989%'
AND 사원번호 > 100000;

* 결과
+----+-------------+-------+------------+-------+--------------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys      | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | 사원  | NULL       | range | PRIMARY,I_입사일자 | PRIMARY | 4       | NULL | 149578 |    11.11 | Using where |
+----+-------------+-------+------------+-------+--------------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

튜닝 후

• like 대신 크기 비교를 사용함 => extra : Using index

SELECT 사원번호
FROM 사원
WHERE 입사일자 >= '1989-01-01' AND 입사일자 < '1990-01-01'
AND 사원번호 > 100000;

+----+-------------+-------+------------+-------+--------------------+------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key        | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------+------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | 사원  | NULL       | range | PRIMARY,I_입사일자 | I_입사일자 | 7       | NULL | 49820 |    50.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------+------------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

---

9) 동등 조건으로 인덱스를 사용하는 나쁜 SQL문

튜닝 전

• 데이터 분포도를 고려하지 않고 인덱스를 활용함

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.02 sec)

튜닝 후

• 출입문 종류의 분포도를 확인

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

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

• 분포도가 좁아서 풀스캔이 더 효율적인 예시
• 강제로 IGNORE INDEX 힌트 사용

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)

 

10) 범위 조건으로 인덱스를 사용하는 나쁜 SQL 문

튜닝 전

• 인덱스를 활용하여 조회 : 전체 데이터의 17%에 해당 되는 데이터를 가져와서 비효율적
• 소요 시간 1.21초

SELECT 이름, 성
FROM 사원
WHERE 입사일자 BETWEEN STR_TO_DATE('1994-01-01','%Y-%m-%d')
		AND STR_TO_DATE('2000-12-31', '%Y-%m-%d');

+----+-------------+----------------+------------+--------+------------------------+
| id | select_type | table  | type  | key        | rows   | Extra |
+----+-------------+--------------+--------------+--------+------------------------+
|  1 | SIMPLE      | 사원   | range | I_입사일자 | 112160 | Using index condition;
|    |             |        |       |            |        | Using MRR
+----+-------------+--------------+--------------+--------+------------------------+
48875 row in set, 1 warning (1.21 sec)

 

튜닝 후

• YEAR()을 사용해서 인덱스를 사용하지 못하도록 함
• 소요시간 1.21sec > 0.20sec

SELECT 이름, 성
FROM 사원
WHERE YEAR(입사일자) BETWEEN '1994' AND '2000';

* 결과
+--------+----------+
| 이름   | 성       |
+--------+----------+
| Saniya | Kalloufi |
|       ...         |
+--------+----------+
48875 rows in set (0.20 sec)

 

튜닝 수 실행 계획

SELECT 이름, 성
FROM 사원
WHERE YEAR(입사일자) BETWEEN '1994' AND '2000';

+----+-------------+----------------+------------+--------+------------------------+
| id | select_type | table  | type  | key        | rows   | Extra  
+----+-------------+--------------+--------------+--------+------------------------+
|  1 | SIMPLE      | 사원   | ALL   | NULL       | 299157 | Using where 
+----+-------------+--------------+--------------+--------+------------------------+
48875 row in set, 1 warning (1.21 sec)

 

 

---

정리

• WHERE절
  • where 절 column명 대신 다른거 사용하지 말자
    • SUBSTRING
    • CONCAT 사용하지 말자
  • WHERE 절로 필터링할 때는 자료형 주의하자 
• LIKE 지양하자 => 전체 테이블을 스캔한다.
• 분포도가 좁은 데이터는 인덱스를 사용하지 말자
• 이미 처리된거 중복 처리하지 말자
  • PRIMARY KEY인데 DISTINCT 사용하지 말자
  • NOT NULL 설정되어 있는데 IFNULL 사용하지 말자
  • 중복이 없는 데이터에 UNION 대신 UNION ALL을 사용하자
• INDEX를 고려하자, 순서 주의하자