search

FeGwan-Training / FeGwan

0 stars 0 forks source link

[업무에 바로 쓰는 SQL 튜닝 4장] 악성 SQL 튜닝으로 초보자 탈출하기 #73

Open MyeoungDev opened 7 months ago

MyeoungDev commented 7 months ago

Discussed in https://github.com/FeGwan-Training/FeGwan/discussions/72

Originally posted by **MyeoungDev** January 30, 2024

4장 악성 SQL 튜닝으로 초보자 탈출하기

⚠️ 각 테이블과 인덱스는 책을 참조⚠️

참고 사항 (3장 내용)

(4장의 내용을 이해하기 위해서는 최소 3장에서 이정도는 알아야 된다고 생각해서 공부하고 작성함.)

쿼리 튜닝을 고려할 때 현재 해당 쿼리가 어떤 실행 계획을 통해 실행되는지를 알아야 한다.

**EXPLAIN, DESCRIBE , DESC** 3가지 키워드를 사용해서 쿼리의 실행 계획을 확인할 수 있다.

EXPLAIN SQL문;
DESCRIBE SQL문;
DESC SQL문;

-- EXPLAIN 실행문
EXPLAIN
SELECT 사원.사원번호, 사원.이름. 사원.성, 급여.연봉,
    (SELECT MAX(부서번호)
        FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
FROM 사원, 급여
WHERE 사원.사원번호 = 10001
    AND 사원.사원번호 = 급여.사원번호;

-- EXPLAIN 실행 결과
id | select_type | table | partitions | type | possible_keys -- | -- | -- | -- | -- | -- 1 | PRIMARY | 사원 | NULL | const | PRIMARY 1 | PRIMARY | 급여 | NULL | ref | PRIMARY 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL

id

실행 순서를 표시.

ID의 숫자가 작을수록 먼저 수행. ID가 같은 값이라면 두 개 테이블의 조인이 이루어짐.

select_type

SQL 문을 구성하는 SELECT 문의 유형을 표시.

서브 쿼리가 없는 단순 SIMPLE, 서브 쿼리가 존재한는 쿼리의 주 쿼리, UNION 쿼리 등을 표시.

table

테이블 명을 표시

partitions

데이터가 저장된 논리적인 영역을 표시.

사전에 정의한 특정 파티션에 선택적으로 접근하는 것이 성능이 좋음.

만약, 너무 많은 영역의 파티션에 접근하면 파티션 튜닝 고려.

type

테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목.

  • system
    • 테이블에 데이터가 없거나 하나만 존재하는 경우
    • 성능상 최고
  • const
    • 조회되는 데이터가 단 1건일때
    • 성능 매우 우수, 지향해야 할 타입
  • eq_ref
    • 조인이 수행될 때 드리븐 테이블의 데이터에 접근하여 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회할 때의 타입.
    • 조인 수행시 가장 성능 좋음
  • ref
    • 조인이 수행될 때 드리븐 테이블 데이터 접근 범위가 2개 이상일 경우
    • 즉, 1 : N 관계에서 많이 나타남. 또는 인덱스 열을 통한 비교 연산자(< > =)에서도 나타남.
    • 드리븐 테이블의 데이터가 많을 경우 성능 저하 고려 됨.
  • ref_or_null
    • **IS NULL 구문**에 대해 인덱스를 활용하는 최적화 방식
    • **NULL 데이터 양이 적다면 효율적, 데이터 양이 많다면 튜닝 대상.**
  • range
    • 비교연산자, BETWEEN, IN 연산을 통한 범위 스캔을 수행하는 방식.
    • 스캔 범위가 넓으면 성능 저하의 요인 튜닝 대상.
  • fulltext
    • 텍스트 검색 처리를 위해 Full Text Index 를 사용하는 경우.
  • index_merge
    • 특정 테이블에 생성된 두 개 이상의 인덱스가 병합되어 동시에 적용되는 경우.
  • index
    • 인덱스 풀 스캔.
  • ALL
    • 테이블 풀 스캔
    • 사용할 수 있는 인덱스가 없거나, 옵티마이저 판단에 인덱스가 비효율일 경우.
    • 인덱스 변경 & 추가로 튜닝 가능
    • 전체 테이블에서 10 ~ 20% 이상 분량의 데이터를 조회할 때는 오히려 성능상 유리할 수 있음.

possible_keys

옵티마이저가 최적화에 사용할 수 있는 인덱스 목록을 출력.

다만, 실제 해당 쿼리문 실행에 사용된 인덱스가 아니다. 그러므로 튜닝할 때 별로 도움은 안된다고 한다.

key

옵타마이저가 최적화에 사용한 기본 키 또는 인덱스명을 의미.

비효율적인 인덱스 사용 혹은 인덱스 자체를 사용하지 않을 경우 튜닝 대상.

key_len

사용된 키본 키 또는 인덱스의 byte 수.

ex) 사원 번호(INT) + 날짜(VARCHAR(50) = 4 + (50 + 1) * 3 = 159

ref

조인의 조건문

rows

SQL문 수행에서 해당 테이블에 접근한 모든 데이터 수.

경우에 따라 데이터는 달라지기 때문에 완전한 신뢰는 불가능.

그러나, 해당 결과가 너무 크게 차이가 난다면 불필요한 데이터에 너무 많이 접근하고 가져온다는 뜻. 튜닝 대상.

filtered

DB 엔진에 넘어온 데이터가 얼마나 필터링 됐는지에 대한 퍼센트 수치.

ex) 100개의 데이터 넘어옴, 결과 10개 ⇒ filtered = 10.00

extra

SQL 문을 어떻게 수행할 것인지에 관한 추가 정보들을 표시.

종류가 개많음. 여러가지 중복 출력 가능. 난 내가 많이 접할것 같은것만 작성할 예정

  • Distinct
    • **DISTINCT** , **UNION** 구문이 포함되어 중복이 제거되어 유일한 값을 찾을 때.
  • Using where
    • **WHERE 절의 필터 조건을 사용**할 때.
  • Using temporary
    • 데이터의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미.
    • **GROUP BY** , **ORDER BY** 정렬 작업 혹은 중복 제거 작업에 사용.
  • Using index
    • 물리적인 데이터를 읽지 않고 인덱스만 읽어서 요청을 처리하는 경우. (Covering Index)
    • 적은 양의 데이터에 접근할 경우 성능 우수.

4.2 SQL 문 단순 수정으로 착한 쿼리 만들기

튜닝 이전의 쿼리와 튜닝 이후의 쿼리에 EXPLAIN 문을 사용해서 어떤점이 변경되었는지 확인하면서 하면 도움이 많이 됩니다.

기본 키를 변경하는 나쁜 SQL 문

-- 키본 키가 변경되어 기본 키 인덱스 안타는 경우
SELECT *
FROM 사원
WHERE SUBSTRING(사원번호, 1, 4) = 1100
    AND LENGTH(사원번호) = 5;

-- 30만건 데이터 0.23초

-- 튜닝 결과
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009

-- 30만건 데이터 0.00초

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

-- NOT NULL 컬럼에 IFNULL을 사용해서 NULL Check 하는 경우
-- IFNULL () 함수를 처리하기위해 Using Temporary로 DB내에 임시 테이블을 만들어 연산하는 과정 추가됨
SELECT IFNULL(성별, 'NO DATA') AS 성별, COUNT(1) 건수
FROM 사원
GROUP BY IFNULL(성별, 'NO DATA')

-- 30만건 데이터 0.77초

-- 튜닝 결과
SELECT 성별, COUNT(1) 건수
FROM 사원
GROUP BY 성별

-- 30만건 데이터 0.10초
-- 즉, 무의미한 함수와 데이터 처리는 지양하자.

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

-- 여기서 사용여부 라는 컬럼의 타입은 VARCHAR(1) 이다.
-- DB에서 묵시적 형변환이 발생하여 사용여부 인덱스를 활용하지 못함.
SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = 1;

-- 약 5만건 데이터 0.15초

-- 튜닝 결과
SELECT COUNT(1)
FROM 급여
WHERE 사용여부 = '1'

-- 약 5만건 데이터 0.01초

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

-- 성별 과 성을 묶어 놓은 인덱스가 이미 존재.
-- 그러나 CONCAT 연산을 통해 새로운 문자열이 되었으므로 해당 인덱스 사용 불가능.
SELECT *
FROM 사원
WHERE CONCAT(성별, ' ', 성) = 'M Radwan'
--약 100건의 데이터 추출 0.25초

-- 튜닝 결과
SELECT *
FROM 사원
WHERE 성별 = 'M'
    AND 성 = 'Radwan'
-- 동일한 결과 0.01초

습괍적으로 중복을 제거하는 나쁜 SQL문

-- 중복된 데이터가 없는데 DISTINCT를 사용하여 중복제거를 하려고 한 경우.
SELECT DISTINCT 사원.사원번호, 사원.이름, 사원.성, 부서관리자.부서번호
FROM 사원
    JOIN 부서관리자
        ON(사원.사원번호 = 부서관리자.사원번호)

-- EXPLINE 실행 계획에서 type = eq_ref 로 사원번호라는 기본 키를 사용해서 
-- 단 1건의 데이터 조회가 일어난다고 확인할 수 있음
-- DISTINCT 키워드는 내부적으로 결과를 정렬한 뒤 중복 제거하는 과정이 추가됨.
-- 인덱스를 이용할 경우 이미 정렬되어 있으며, 1건의 데이터 조회이기 때문에 중복이 존재하지 않음.

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

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

-- UNION 연산의 경우 두개의 쿼리의 결과를 합쳐서 중복을 제거하는 과정을 거친다.
-- 그 과정은 메모리 내에서 임시 테이블을 만들어서 연산이 진행된다.
-- 그러나, 해당 쿼리에서는 중복에 대한 제거가 불필요하다.
-- 따라서, 단순히 결과를 합치는 연산을 하는 UNION ALL을 사용하면 된다.
SELECT 'M' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'M'
    AND 성 = 'Baba'

UNION

SELECT 'F', AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'F'
    AND 성 = 'Baba'

-- 튜닝 결과
SELECT 'M' AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'M'
    AND 성 = 'Baba'

UNION ALL

SELECT 'F', AS 성별, 사원번호
FROM 사원
WHERE 성별 = 'F'
    AND 성 = 'Baba'

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

-- 성별 + 성으로 인덱스가 잡혀있다.
-- 즉, 성별을 통해 정렬이 된 후 성으로 정렬이 되어있다.
-- 이 경우는 인덱스를 통해서만 조회가 가능한 커버링 인덱스로 수행된다.
-- 그러나, 성 + 성별의 경우 정렬이 다른 방식으로 진행되기 때문에 임시 테이블을 생성하여 그루핑되게 된다.
SELECT 성, 성별, COUNT(1) as 카운트
FROM 사원
GROUP BY 성, 성별

-- 튜닝 결과
SELECT 성, 성별, COUNT(1) AS 카운트
FROM 사원
GROUP BY 성별, 성

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

-- 처음 해당 쿼리를 보았을때 이게 뭐가 문제인거지? 
-- 기본 키 인덱스를 사용하고있고 괜찮은거 아닌가??라는 생각이 들었다.
-- 총 30만명의 사원에서 입사일자가 1989년인 경우는 총 2만건, 사원번호가 10만보다 큰 경우는 20만건이 존재한다.
-- 이 경우는 스토리지 엔진에 접근할때 입사일자를 통해 먼저 접근하는게 더 낫다.
SELECT 사원번호
FROM 사원
WHERE 입사일자 LIKE '1989%'
    AND 사원번호 > 100000;

-- 튜닝 결과
SELECT 사원번호
FROM 사원
WHERE 입사일자 >= '1989-01-01' AND 입사일자 < '1990-01-01' USE INDEX(I_입사일자)
    AND 사원번호 > 100000;

-- 0.13초의 결과가 0.02

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

-- 가정: 총 60만건 데이터, 결과는 총 30만건 
-- 실행 결과 출입문 컬럼의 인덱스 스캔으로 약 3.7초
-- 전체 데이터의 약 50%에 달하는 데이터 조회에 인덱스를 활용하는게 더 안 좋은 상황이 있다.
-- 전체 테이블의 10 ~ 20% 가 넘어가는 데이터를 조회할 경우 테이블 풀 스캔이 성능이 좋을 수 있다.
SELECT *
FROM 사원출입기록
WHERE 출입문 = 'B'

-- 튜닝 결과
SELECT *
FROM 사원출입기록 IGNORE INDEX(I_출입문)
WHERE 출입문 = 'B'

-- 튜닝 실행 결과 0.85초

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

-- 인덱스를 이용한 Index Range Scan 이다. 굳.
-- 그러나, 총 30만건의 사원 데이터에서 해당 범위의 데이터는 약 5만건이다. 실행 시간 1.21초
-- 총 데이터의 17퍼센트에 해당됨.
-- 이 경우 인덱스 스캔으로 랜덤 액세스의 부하가 발생하도록 하기보다는 
-- 테이블 풀 스캔 방식을 고정적으로 설정하는 것을 고려
SELECT 이름, 성
FROM 사원
WHERE 입사일자 BETWEEN STR_TO_DATE('1994-01-01', '%Y-%m-%d')
    AND STR_TO_DATE('2000-12-31', '%Y-%m-%d')

-- 튜닝 결과
SELECT 이름, 성
FROM 사원
WHERE YEAR(입사일자) BETWEEN '1994' AND '2000'
-- 인덱스를 버리고 테이블 풀 스캔. 실행 시간 0.2초

왜 테이블 풀 스캔이 더 효율적일까?

가정: 전체 테이블의 데이터 중 상당량의 데이터(10 ~ 20%가 넘는 데이터)를 찾아오는 경우.

  1. 인덱스를 이용하는 경우.

인덱스 스캔으로 랜덤 액세스의 부하가 발생.

디스크 오버헤드 증가.

전체 데이터 중 많은 양의 데이터에 필요한 인덱스에 접근 후 디스크에 접근하는 비용.

  1. 인덱스를 사용하지 않고 테이블 풀 스캔을 하는 경우.

인덱스를 통한 랜덤 액세스가 발생하지 않아 디스크 오버헤드가 적음.

인덱스에 접근한뒤 테이블에 접근하는 과정 없이, 바로 테이블에 접근.

테이블 풀 스캔의 경우 인덱스 없이 테이블에 직접 접근하며 한 번에 다수의 페이지에 접근하므로 더 효율적인 SQL 문이 실행될 수 있음.

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

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

-- 가정: 부서 = 9, 부서사원_매핑 = 약 30만, 매핑.시작일자 >= '2002-03-01' = 약 1천 3백 = 0.4%, 
-- 약 1천3백의 실행 결과 13.2초 실행시간 소요.
-- 드라이빙 테이블 = 부서, 드리븐 테이블 = 부서사원_매핑. 중첩 루프 조인 실행됨.
-- 즉, 부서 9개 가지고 30만개 랜덤 액세스 해서 데이터 가져오고 거기서 필터링 진행.
-- 인덱스를 이용하더라도 드리븐 테이블의 데이터가 이렇게 많은 경우 랜덤 액세스 비용이 비싸다.
-- 그렇다면 조건절을 먼저 이용해서 드리븐 테이블 데이터 30만 -> 1천 3백으로 줄인다면?
SELECT 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
    AND 매핑.시작일자 >= '2002-03-01';

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

-- 실행결과 0.17초
-- 튜닝 후 실행 계획
-- 상대적으로 대용량인 부서사원_매핑 테이블 -> 테이블 풀 스캔으로 처리. Type: index -> ALL
-- 부서 테이블 -> 기본 키로 반복 접근 Type: ref -> eq_ref

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

-- 메인인 사원 테이블로부터 조건을 전달받아 수행해야 하는 의존성을 가진 서브쿼리이다.
-- select_type: DEPENDENTY SUBQUERY
-- 실행 계획의 select_type 항목에 DEPENDENT 라는 키워드가 있으면,
-- 외부 테이블에서 조건절을 받은 뒤 처리되어야 하므로 튜닝 대상으로 고려할 수 있다.
SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원
WHERE 사원번호 > 450000
    AND ( SELECT MAX(연봉)
                FROM 급여
                WHERE 사원번호 = 사원.사원번호
            ) > 100000;

-- 튜닝 결과
SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원
WHERE 사원번호 > 450000
    AND 사원.사워넌호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 1000000

-- 0.57 -> 0.11초

저자의 팁 서브쿼리 vs 조인

저자는 10년 경력의 DBA 라고 한다.

저자의 그간 경험상 서브쿼리보다는 조인으로 수행하는 편이 성능 측면에서 유리할 가능성이 높다고 한다.

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

-- 실행 시간: 22.5초
-- 이 경우 사원입출잉기록에서 출입문 A에 관한 기록이 있는 사원이 몇명인지만 알면 된다.
-- 따라서, 사원출입기록과 조인하여 EXIST 구문을 통해 집계하면 된다.
SELECT COUNT(DISTINCT 사원.사원번호) AS 데이터건수
FROM 사원,
    ( SELECT 사원번호
        FROM 사원출입기록 AS 기록
        WHERE 출입문 = 'A'
    )
WHERE 사원.사원번호 = 기록.사원번호

-- 튜닝 결과
SELECT COUNT(1) AS 데이터건수
FROM 사원
WHERE EXISTS ( 
                SELECT 1
                FROM 사원출입기록 AS 기록
                WHERE 출입문 = 'A'
                    AND 기록.사원번호 = 사원.사원번호
            )
-- 실행 시간: 0.5초

FROM 절 서브쿼리(인라인 뷰)는 옵티마이저에서 어떻게 처리되나?

FROM 절의 인라인 뷰는 옵티마이저에 의해 조인 방식이 뷰 병험(View Merging)으로 최적화 되어 아래와 같이 실행된다.

SELECT COUNT(DISTINCT 기록.사원번호) AS 데이터건수
FROM 사원, 사원입출입기록 AS 기록
WHERE 사원.사원번호 = 기록.사원번호
    AND 출입문 = 'A'