- Primary Key(index) = NOT NULL, 유일성O
= Unique Key + NOT NULL (=> 하지만 PK 사용이 원칙)- Unique Key(index) = NULLABLE, 유일성O
- 일반 Key(index) = 유일성X
4.2 장
SQL문 단순 수정으로 착한 쿼리 만들기
1) 기본키를 변형하는 나쁜 SQL문
2) 사용하지 않는 함수를 포함하는 나쁜 SQL문
3) 형변환으로 인덱스 활용하지 못하는 나쁜 SQL문
4) 열을 결합하여 사용하는 나쁜 SQL문
5) 습관적으로 중복을 제거하는 나쁜 SQL문
6) 다수 쿼리를 UNION 연산자로만 합치는 나쁜 SQL문
7) 인덱스 고려 없이 열을 사용하는 나쁜 SQL문
8) 엉뚱한 인덱스를 사용하는 나쁜 SQL문
9) 동등 조건으로 인덱스를 사용하는 나쁜 SQL문
SQL문 단순 수정으로 착한 쿼리 만들기
1) 기본키를 변형하는 나쁜 SQL문
튜닝 전
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)튜닝 후
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문
튜닝 전
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)변환으로 인덱스 활용하지 못하는 나쁜 SQL문
튜닝 전
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)튜닝 후
- 컬럼 확인 결과 사용여부 컬럼 char 형식
- where 사용여부 = 1 절로 인해 묵시적인 형변환 발생 =>
I_사용여부인덱스 제대로 활용하지 못하고 전체 데이터 스캔
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문
튜닝 전
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)튜닝 후
- concat 으로 문자열 결합하면 인덱스를 제대로 타지 못함
- 따로 따로 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문
튜닝 전
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)튜닝 후
- 사원 테이블 확인 결과 사원번호 컬럼은 기본키(primary key)임
- 굳이 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 사용하지 않고 UNION ALL 사용
7) 인덱스 고려 없이 열을 사용하는 나쁜 SQL문
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)튜닝 후
- index(성별,성) 으로 이루어져있기 때문에 위 쿼리로는 인덱스 못탐
- 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문
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)- 1989 년 에 해당하는 사원을 뽑을 때 like 조건을 사용하는 것이 최선이었는지 생각
- 1989-01-01 ~ 1990-01-01 로 범위 명시
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)- 전체 데이터의 50%에 달하는 데이터를 조회하기 위해 인덱스를 활용하는 것이 효율적인지 고민
- 풀스캔이 더 효율적인 예시
- 강제로 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)
열심히 굽고 있어요🍞