테이블
테이블명 | 설명 |
사원 | 해당 기업에 소속된 직원들의 정보를 저장하는 테이블 |
부서 | 조직에서 관리하는 업무 부서에 관한 정보를 저장하는 테이블 |
부서사원_매핑 | 부서 테이블과 사원 테이블 간의 매핑 정보를 저장하는 테이블로, 한 명의 사원은 특정 부서에 소속되므로 그에 해당하는 연결 정보를 저장한다. |
부서관리자 | 부서를 대표하는 관리자 사원의 정보가 저장되는 테이블 |
직급 | 사원이 위치하는 포지션을 나타내는 테이블로, 과거의 직급 정보부터 현재의 직급 정보까지 적재되어 있다. |
급여 | 사원별로 매년 계약한 연봉 정보가 저장되는 테이블로, 과거 급여부터 현재 급여까지 매번 적재되어 있다. |
사원출입기록 | 지역별, 출입문별로 출입한 이려게 관한 시간 정보를 적재하는 테이블 |
인덱스 목록
테이블명 | 키 유형 | 키 구성열 |
급여 | PK | 사원번호 + 시작일자 |
INDEX | 사용여부 | |
부서 | PK | 부서번호 |
UNIQUE INDEX | 부서명 | |
부서관리자 | PK | 사원번호 + 부서번호 |
INDEX | 부서번호 | |
부서사원_매핑 | PK | 사원번호 + 부서번호 |
INDEX | 부서번호 | |
사원 | PK | 사원번호 |
INDEX | 입사일자 | |
INDEX | 성별 + 성 | |
사원출입기록 | PK | 순번 + 사원번호 |
INDEX | 출입문 | |
INDEX | 지역 | |
INDEX | 입출입시간 | |
직급 | PK | 사원번호 + 직급명 + 시작일자 |
기본 키를 변형하는 SQL 문
현황 분석
- 사원번호가 1100으로 시작하면서 사원번호가 5자리인 사원의 정보를 모두 출력하는 쿼리
- Type 항목이 ALL이므로 테이블 풀 스캔 방식이며, 인덱스를 사용하지 않고 테이블에 바로 접근
- 필요한 범위에만 접근하는 대신 처음부터 끝까지 스캔하기 때문에 비효율적
튜닝 수행
- 사원번호 컬럼을 WHERE 절로 작성할 경우 기본 키를 통해 데이터에 빠르게 접근할 수 있지만, 사원번호를 그대로 쓰는 대신 SUBSTRING()과 LENGTH()와 같이 가공하여 작성하는 경우 기본 키를 사용하지 않고 테이블 풀 스캔을 수행한다.
- 따라서 가공된 사원번호 컬럼을 변경하여 기본 키를 사용하도록 조정해야 한다.
튜닝 결과
- 5자리면서 1100으로 시작하는 사원번호를 찾기 위해 BETWEEN 구문으로 범위 검색을 수행하면 사원번호가 변형되지 않아 기본 키나 인덱스를 활용할 수 있다.
- WHERE 절의 BETWEEN 구문에 의해 기본 키의 특정 범위만 스캔한다는 것을 알 수 있으며, 출력하는 사원번호가 10개 이므로 rows 항목에서더 10이라는 값을 예측하는 것을 확인할 수 있다.
사용하지 않는 함수를 포함하는 SQL 문
현황 분석
- 사원 테이블에서 성별 기준으로 몇 명의 사원이 있는지 출력하는 쿼리로, 성별의 값이 NULL이라면 NO DATA를 출력할 수 있도록 IFNULL() 함수를 사용했다.
- 인덱스 풀 스캔 방식으로 수행됬으며, Extra 항목이 Using temporary이므로 임시 테이블을 생성한다는 것을 알 수 있다.
튜닝 수행
- 사원 테이블의 성별 컬럼에는 NOT NULL 속성이 설정되어 있으므로, Null 값이 존재할 수 없다.
- 따라서 IFNULL() 함수를 처리하려고 DB 내부적으로 별도의 임시 테이블을 만들어서 Null 값의 여부를 검사할 필요가 없다.
튜닝 결과
- 성별은 Null 없이 모두 F 또는 M 값으로만 존재하기 때문에, IFNULL() 함수를 제거하고 성별 열만 그대로 사용한다.
- 인덱스 풀 스캔 방식으로 수행되며, Extra 항목이 Using index이므로 임시 테이블 없이 인덱스만 사용하여 데이터를 추출한다.
형변환으로 인덱스를 활용하지 못하는 SQL 문
현황 분석
- 급여 테이블에서 현재 유효한 급여 정보만 조회하고자 사용여부 컬럼 값이 1인 데이터를 출력하는 쿼리
- 인덱스 풀 스캔 방식으로 수행되며, filtered 항목이 10이므로 MySQL 엔진으로 가져온 데이터 중 10%를 추출해서 최종 데이터를 출력할 것임을 알 수 있다.
튜닝 수행
- 튜닝 대상인 SQL 문에서 사용여부 열이 인덱스로 구성되어 있고, WHERE 절의 조건문으로 작성되었음에도 실행 계획에서는 인덱스 풀 스캔으로 수행되고 있다.
- 사용여부 컬럼은 char(1) 데이터 유형으로, 문자 유형인 사용여부 열을 WHERE 절에서 숫자 유형으로 사용해서 데이터에 접근했으므로 DBMS 내부의 묵시적 형변환이 발생했음을 알 수 있다.
- 그 결과 인덱스를 제대로 활용하지 못해 전체 데이터를 스캔한 것이므로, 형변환이 발생하지 않도록 SQL 문을 조정해야 한다.
튜닝 결과
- 현재 유효한 급여 정보만 조회하고자 사용여부 컬럼의 값이 문자 '1'인 경우만 조회한다.
- 조건절이 스토리지 엔진에서 전달되어 필요한 데이터만 가져왔음을 알 수 있다.
컬럼을 결합하여 사용하는 SQL 문
현황 분석
- 사원 테이블에서 성별의 값과 1칸의 공백, 성의 값을 모두 결합한 결과가 'M Radwan'인 데이터를 조회하는 쿼리
- 사원 테이블은 조건절로 데이터에 접근하지만, 테이블 풀 스캔으로 데이터를 처음부터 끝까지 스캔하므로 비효율적
튜닝 수행
- 사원 테이블은 성별 + 성으로 구성된 인덱스를 가지고 있으므로, WHERE 절에서 인덱스를 활용하여 데이터를 빠르게 조회할 수 있다.
튜닝 결과
- 인덱스를 사용해서 사원 테이블에 접근한 결과 액세스 범위가 약 30만 건에서 102건으로 줄어들었음을 확인할 수 있다.
습관적으로 중복을 제거하는 SQL 문
현황 분석
- 부서 관리자의 사원번호와 이름, 성, 부서번호 데이터를 중복 제거하여 조회하는 쿼리
- 드라이빙 테이블인 부서관리자 테이블과 드리븐 테이블인 사원 테이블의 id 값이 같으므로 조인이 발생했음을 알 수 있다.
- 부서관리자 테이블은 인덱스 풀 스캔 방식으로 수행되고, 사원 테이블은 기본 키를 사용해서 단 1건의 데이터를 조회하는 방식으로 조인되고 있다.
- DISTINCT를 수행하기 위해 별도의 임시 테이블을 만들고 있다.
튜닝 수행
- 사원 테이블의 기본 키는 사원번호로, SELECT 절에 작성된 사원.사원번호에는 중복된 데이터가 존재하지 않는다.
- 따라서, DISTINCT 키워드로 정렬 작업을 하고 중복을 제거하는 작업이 필요하지 않다.
튜닝 결과
- 사원 테이블의 사원번호 컬럼은 기본 키로 중복된 데이터가 출력될 수 없으므로, DISTINCT 키워드를 제거한다.
- 임시 테이블에서 정렬과 중복 제거를 수행하지 않아도 되므로 Extra 항목의 Using temporary가 삭제된 것을 확인할 수 있다.
다수 쿼리를 UNION 연산자로만 합치는 SQL 문
현황 분석
- 두 개의 SELECT 문을 UNION 연산자로 합치는 쿼리
- id가 NULL인 행에서 id가 1인 행과 2인 행의 결과를 통합하여 중복을 제거하는 작업을 처리한다.
- 이때 메모리에 임시 테이블을 생성하고 그 내부에서 각 결과의 UNION 연산 작업을 수행하는데, 만약 결과 데이터가 메모리에 상주하기 어려울 만큼 많다면 메모리가 아닌 디스크에 임시 파일을 생성하여 UNION 작업을 수행한다.
튜닝 수행
- 사원 테이블의 기본 키는 사원번호로, 출력되는 SQL 문에서 중복을 제거하는 과정이 필요한지 생각해야 한다.
튜닝 결과
- 두 SELECT 문의 결과 데이터가 중복되지 않으므로 중복을 제거하는 작업이 필요하지 않다.
- 따라서 임시 테이블을 만들고 정렬한 뒤 중복을 제거하는 UNION 연산자 대신, 결과값을 단순히 합치는 UNION ALL 연산자로 변경해야 한다.
- id가 1, 2인 행까지는 튜닝 전 실행 계획과 동일하지만, 정렬하여 중복을 제거하는 작업이 제외되면서 불필요한 리소스 낭비를 방지했다.
인덱스 고려 없이 컬럼을 사용하는 SQL 문
현황 분석
- 성과 성별 순서로 그루핑하여 몇 건의 데이터가 있는지 구하는 쿼리
- 사원 테이블의 인덱스를 활용하고, 임시 테이블을 생성하여 성과 성별을 그루핑해 카운트 연산을 수행한다.
- 인덱스의 구성 열이 GROUP BY 절에 포함되므로, 테이블 접근 없이 인덱스만 사용하는 커버링 인덱스로 수행된다.
튜닝 수행
- I_성별_성 인덱스는 성별 컬럼 기준으로 정렬된 뒤 성 컬럼 기준으로 정렬되어 있다.
튜닝 결과
- 그루핑을 성 + 성별 순으로 수행할 때와 성별 + 성 순으로 수행할 때의 결과는 동일하므로, 이미 존재하는 인덱스를 활용하려면 인덱스 순서대로 그루핑해야 한다.
- 그러면 별도의 임시 테이블을 생성하지 않고도 그루핑과 카운트 연산을 수행할 수 있다.
- 별도의 임시 테이블을 생성하지 않고도 인덱스만으로 그루핑 이후의 정렬 작업까지 수행된다.
엉뚱한 인덱스를 사용하는 SQL 문
현황 분석
- 사원 테이블에서 입사일자가 1989년이면서 사원번호가 100000번을 넘어가는 사원정보를 조회하는 쿼리
- 사원 테이블의 기본키로 범위 스캔을 수행하고 있다.
- 스토피지 엔진으로부터 기본 키를 구성하는 사원번호를 조건으로 데이터를 가져온 뒤, MySQL 엔진에서 남은 필터 조건으로 추출하여 filtered 항목에 11.11%라는 예측값을 출력한다.
튜닝 수행
- 실행 계획을 살펴보면 기본 키인 사원번호로 범위 스캔을 수행하고 있음을 알 수 있다.
- 사원번호가 100000번을 초과하는 데이터가 전체 데이터 건수 대비 70%나 차지하므로, 스토리지 엔진에서 데이터에 접근할 때 사원번호로 구성된 기본 키로 액세스하는 것은 효율적이지 않다.
- 한편 입사일자가 1989년인 데이터는 전체 데이터 대비 약 10%를 차지하므로, 아래와 같이 입사일자 컬럼을 데이터 액세스 조건으로 활용하는 것을 검토해 볼 수 있다.
- 입사일자 인덱스로 테이블을 스캔하지만, 인덱스 루스 스캔 방식에 의해 인덱스를 스킵하는 오버헤드가 발생할 수 있다.
- 입사일자 컬럼의 데이터 유형은 date 타입으로, 조건문에서 입사일자 컬럼에 대해 부분검색이 목적인 LIKE 절을 사용하는 것은 좋지 않다.
튜닝 결과
- LIKE 절보다 부등호 조건절이 우선하여 인덱스를 사용하므로 데이터 접근 범위를 줄일 수 있다.
- 사원 테이블에서 인덱슬르 활용하여 범위 스캔을 수행하여, 테이블에 접근하지 않고 인덱스만 사용하여 최종 결과를 출력한다.
- 스토리지 엔진으로부터 입사일자 인덱스에 있는 데이터를 가져온 뒤 MySQL 엔진에서 사원 번호에 대한 필터 조건으로 데이터를 추출한다.
동등 조건으로 인덱스를 사용하는 SQL 문
현황 분석
- B 출입문으로 출입한 이력이 있는 정볼르 모두 조회하는 쿼리
- 사원출입기록 테이블은 출입문 인덱스를 사용하여 데이터에 접근한다.
- 이때 출입문 B에 대한 명확한 상수화 조건으로 데이터 접근 범위를 줄였으므로, ref 항목이 const로 출력된다.
튜닝 수행
- 출입문 인덱스로 인덱스 스캔을 수행할 때, 인덱스에 접근한 뒤 테이블에 랜덤 액세스하는 방식을 사용한다.
- 출입문 B는 전체 데이터의 약 50%를 차지하므로 인덱스를 활용하는 것이 효율적인지 생각해봐야 한다.
튜닝 수행
- MySQL의 옵티마이저 내부 알고리즘은 완벽하지 않기 때문에, 처음 의도한 대로 SQL 문이 수행되지 않는다면 강제로 힌트를 추가해야 한다.
- 대량의 데이터를 인덱스 스캔으로 조회하는 쿼리에 대해, 내부 실행되는 인덱스를 무시하게 하려면 IGNORE INDEX라는 힌트를 사용한다.
- 사원출입기록 테이블이 테이블 풀 스캔 방식으로 수행되므로, 인덱스를 사용하지 않고 전체 데이터를 가져와 조건절로 필요한 데이터를 추출한다.
- 따라서 랜덤 액세스가 발생하지 않아 더 효율적으로 SQL 문을 수행할 수 있다.
범위 조건으로 인덱스를 사용하는 SQL 문
현황 분석
- 입사일자가 1994년 1월 1일부터 2000년 12월 31일까지인 사원들의 이름과 성을 출력하는 쿼리
- 사원 테이블에서 입사일자 인덱스로 범위 스캔을 수행한다.
- 또한, Using index condition을 통해 스토리지 엔진에서 입사일자의 조건절로 인덱스 스캔을 수행함을 알 수 있고, Using MRR을 통해 인덱스가 랜덤 액세스가 아닌 순차 스캔으로 최적화하여 처리됨을 확인할 수 있다.
튜닝 수행
- SQL 문의 결과 데이터가 전체 데이터의 약 17%에 해당되므로, 인덱스를 사용하는 것이 효율적인지 인덱스 없이 테이블에 바로 접근하는 방식이 효율적인지 생각해 봐야 한다.
- 또한 입사일자 기준으로 매번 수 년에 걸친 데이터를 조회하는 경우가 잦다면, 인덱스 스캔으로 랜덤 액세스의 부하가 발생하도록 하기보다는 테이블 풀 스캔 방식을 고정적으로 설정하는 것이 낫다.
튜닝 결과
- 입사일자 열로 생성한 인덱스를 사용하지 않도록 의도적으로 인덱스 열을 변형한다.
- 사원 테이블은 테이블 풀 스캔 방식으로 데이터에 접근한다.
- 인덱스 없이 테이블에 직접 접근하며 한 번에 다수의 페이지에 접근하므로 더 효율적으로 SQL 문을 수행한다.
'Computer Science > SQL Tuning' 카테고리의 다른 글
테이블 JOIN 튜닝 (0) | 2022.02.22 |
---|---|
SQL 프로파일링 (0) | 2022.02.21 |
MySQL과 MariaDB 실행 계획 (0) | 2022.02.21 |
인덱스와 인덱스 스캔 (0) | 2022.02.19 |