2차 기술세미나 정리(2024.03.07 발표)

주제 : SQL 성능 최적화 전략

 

1. 인덱스 최적화

2. 파티셔닝 최적화

3. 쿼리 튜닝

으로 나누어 최적화 전략를 세웠다.

 

1. 인덱스 최적화

• 인덱스 정의 
  • 데이터 베이스 테이블의 성능 향상. 검색 및 정렬 작업을 빠르게 수행하기 위한 데이터의 논리적 순서를저장하는 데이터 구조
• 인덱스 장단점
  • 장점 : 데이터 검색 속도 향상, 정렬된 데이터 접근 용이, 데이터 집합 연산 최적화
  • 단점 : 스토리지 공간 요구, 데이터 변경에 따른 오버헤드, 인덱스 선택과 관리의 복잡성
• 인덱스 종류(저장 방식)
  • 클러스터형 인덱스 
    1. 테이블 당 1개만 존재 가능
    2. 데이터 정렬에 따른 영향
    3. 물리적으로 레코드 정렬
  • 보조 인덱스 
    1. 테이블 당 여러개 존재 가능
    2. 데이터 정렬에 영향 미치지 않음.
    3. 다양한 쿼리 유형 지원
• 인덱스 생성 조건
  • 카디널리티(cardinality)가 높은 컬럼에 지정 (=데이터 중복이 적은 컬럼)
    • 카디널리티란? 데이터 베이스에서 특정 열이나 인덱스의 고유한 값들의 개수
  • 검색빈도(활용도) : 자주 검색되는 필드일수록 인덱스 생성하는 것이 좋음.
  • 필터링과 정렬 : WHERE절에서 필터링이나 ORDER BY 절에서 정렬에 사용되는 필드.
• Excution Plan(실행 계획)

mysql workbench에서 확인 가능

 

• 비교하는 데에 사용한 데이터
  • Mysql 샘플 데이터 employees 테이블 300,024건

• 인덱스 생성에 따른 성능 차이

1. (인덱스 생성 전)

 

다음과 같은 쿼리문을 작성했을 때

걸리는 시간 : 0.109 sec

쿼리 비용 : 30165.75

 

2. (인덱스 생성 : (birth_date, first_name) 순)

 

이렇게 쿼리 작성하여 인덱스 생성

 

 

인덱스 제대로 생성 되었다는 것을 볼 수 있음.

 

 

똑같은 쿼리문 실행시켰을 때

걸리는 시간 : 0.016 sec

쿼리 비용 : 0.35

 

3. 속도 비교, 쿼리 비용 비교

 

• 인덱스 컬럼 순서에 따른 성능 차이
  
  1. 앞의 내용처럼 (birth_date, first_name) 순으로 인덱스 생성

               다음의 쿼리문 실행했을 때

인덱스 순서 원하는대로 생성되었음을 알 수 있음.

걸리는 시간 : 0.234 sec

쿼리 비용 : 30165.75

 

     2. 컬럼순서가 반대인 (first_name, birth_date) 순으로 인덱스 생성

원하는대로 인덱스 순서 맞춰 잘 생성됨.

           위의 쿼리를 똑같이 실행시키면

걸리는 시간 : 0.015 sec

쿼리 비용 : 113.91

 

   3. 속도 비교, 쿼리 비용 비교

 

   4. 인덱스 컬럼 순서에 따라 차이가 나는 이유

        - 기존 (birth_date, first_name) 인덱스

          birth_date != ‘1953-09-02’ & first_name = ‘Georgi’  :

          ‘1953-09-02’가 아닌 모든 데이터에 대해 first_name = ‘Georgi’ 비교를 수행해야 함.

 

        -  (first_name , birth_date) 인덱스

          birth_date != ‘1953-09-02’ & first_name = ‘Georgi’  :

          first_name = ‘Georgi’ 인 것만 발견 => 이름이 달라지는 부분이 나타나기 전까지만 데이터 스캔이 필요

 

        - (결론)

           다중 컬럼 인덱스 내 컬럼의 순서는 성능에 큰 영향을 미칠 수 있음

            => 어떤 방식의 조회가 자주 일어나는지, 필터링 방식 고려해봐야 함.

 

• 다중 컬럼 인덱스의 사용 가능 여부

     

(first_name, birth_date)순으로 인덱스 생성 후

       다음과 같은 쿼리문을 실행했을 때

쿼리 실행 계획을 살펴보면

 

 

존재하는 인덱스 idx_name_date 사용하지 않고,

거의 모든 row 스캔했음 알 수 있음.

=> name으로 먼저 정렬한 후, date 컬럼으로 정렬되었기 때문.

 

  (결론)

  선행하는 컬럼에 대한 조건 없는 경우  ‘작업 범위 결정 조건‘ 정하지 못해 인덱스 사용 못함.

 

2. 파티셔닝 최적화

• 파티셔닝 정의 : 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것 

       => 데이터 베이스 성능 향상, 데이터 관리 간소화, 백업 및 복구 시간 줄어듦

• 파티셔닝 유형
  • RANGE 파티셔닝 : 연속적인 값 범위 기반으로 데이터 분할
  • LIST 파티셔닝 : 명시적으로 정의된 목록 기반으로 데이터 분할
  • HASH 파티셔닝 : 해시함수 이용해서 데이터 분할
• 파티셔닝 최적화 팁
  • 적절한 파티션 키 선택 : WHERE절에 자주 사용되는 열이어야함.
  • 적절한 파티션 크기 선택 : 너무 많거나 적으면 활용 어려움.
  • 파티션 유지 관리 : 정기적으로 유지 및 관리 해야함.(삭제, 추가)
  • 인덱스 사용 : 파티션 키와 함께 사용되는 열에 인덱스 생성하면 쿼리 성능 향상됨.

          * WHERE절에 자주 사용되는 열 얼마나? 

             전체 쿼리 중 해당 열을 사용하는 비율이 70% 이상일 때(CHAT GPT참고)

 

          * 너무 많거나 적으면 활용 어려움. 어느정도가 많고, 작은 것인가?

            파티션의 수가 지나치게 많으면, 각 파티션의 크기가 너무 작아져서 시스템 자원을 비효율적으로 사용하게 됨.

오버헤드 증가와 관리의 복잡성을 초래함.

            => 1TB 이상의 데이터베이스에서는 파티션의 크기를 최소 1GB 이상으로 설정하는 것이 좋음.

 

            반대로 파티션이 너무 크면, 한 파티션 내의 데이터를 스캔하는 데 시간이 오래 걸릴 수 있음.

            => 파티션의 크기가 10~100GB를 넘지 않도록 설정하는 것이 좋음.

 

• 파티셔닝 속도, 성능 차이

 

출생년도 별로 세 개의 파티션으로 구분.

part1 : 1953년 미만

part2 : 1953년 ~ 1960년 미만

part3 : 1960년 이상

 

파티션 정보 확인하기 위한 쿼리 실행

 

          - 1953년 이전 출생한 직원 수 검색

               (1) 파티션이 없을 때

             

               (2) 파티션이 있지만, 적용되지 않았을 때

파티션이 part1만 사용되어야 하는데 다 사용된 것을 보아 제대로 적용되지 않았음을 알 수 있음.

                    (결론)

               파티션을 만들더라도 쿼리를 제대로 작성하지 않으면 속도, 쿼리 비용에 도움되지 않음. 

 

               (3) 파티션 생성, 제대로 적용될 때

 

다음의 쿼리문을 작성했을 때, 파티션이 제대로 사용되고 있음을 알 수 있음.

 

파티션이 없었을 때와 파티션이 제대로 적용되었을 때 속도, 비용 비교해보면

(파티션이 없었을 때)             

 

(파티션이 제대로 적용되었을 때)

 

                                                                                  ↓

 

  

               (4) 쿼리 문에 따라 파티션이 적용되고, 적용되지 않는 이유

 

'birth_date'의 년도를 추출하여 비교하기 때문에 파티션이 적용되지 않음.

 

'birth_date' 자체를 직접 비교하는 조건이기 때문 → 파티션 적용됨.

 

3. 쿼리 튜닝

• 'EXPLAIN' 명령어 확인
  • 쿼리 실행 계획을 확인

 

 

여기에서 type, key, rows, Extra 를 확인해보면 쿼리가 효율적으로 실행되는지 알 수 있음.

 

id	아이디(id)로 SELECT 구분하는 번호. 실행 순서 표시
select_type	SQL문을 구성하는 SELECT문의 유형 출력(SIMPLE, PRIMARY, UNION 등)
table	테이블 명(별명)
type	테이블의 데이터를 어떻게 찾을지에 관한 정보 제공. 조인 혹은 조회 타입
possible_keys	데이터 조회할 때 DB에서 사용할 수 있는 인덱스 리스트
key	실제로 옵티마이저가 최적화 검색에 사용한 인덱스가 표시되는 필드
key_len	선택된 인덱스의 길이
ref	key 컬럼에 나와 있는 인덱스에서 값 찾기 위해 선행 테이블의 어떤 컬럼이 비교되었는지
rows	원하는 행 찾기 위해 얼마나 많은 행을 읽어야 할 지에 대한 예측값
extra	옵티마이저가 동작하는 방식에 대한 부가정보

 

- type

현재 실행되는 쿼리의 테이블의 접근 방법

 (Best → Worst 순)

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

• system : 단일 행을 반환하는 쿼리에 대한 최적화된 방식

• const : 기본 키(Primary) 또는 고유 인덱스 값으로 검색하는 경우

• eq_ref : JOIN 시에 기본 키(Primary) 또는 고유 인덱스를 사용하는 경우, 하나의 레코드만 검색

• range : 인덱스(Index)를 범위로 검색하는 경우

• Index : 인덱스(Index)를 Full Scan하는 경우

• all : 테이블을 Full Scan 하는 경우

 

- key

MySQL 옵티마이저가 사용하기로 결정한 인덱스(Index) 정보, NULL일 경우 인덱스 사용 X

 

- rows

MySQL 옵티마이저가 쿼리 실행 시 예상되는 총 행의 수, 이 값이 적을수록 성능 높음.

 

- extra

쿼리 실행과 관련된 추가 정보 (Best -> Worst 순)

•using index : 인덱스(Index)를 사용해 데이터 추출

•using where : where 조건으로 데이터 추출

•using temporary

  - MySQL에서 임시 테이블 생성해 추출

  - ORDER BY, GROUP BY등의 연산, Sub Query 시 사용 가능

  - 디스크 저장 → I/O 비용으로 인한 성능 하락 가능

• using filesort : ORDER BY 수행 시, 인덱스 X 정렬하는 경우 사용됨. 성능 하락 가능.

 

(한계)

분석의 정확성 떨어질 수 있음. ( 통계 정보 부족, 불일치 때문) → 'ANALYZE' 명령어 사용

 

---

느낀점

1차 기술세미나에서 성능, 속도를 비교하는 것이 흥미롭게 느껴졌었다.

IT 많은 분야에서 쓰이는 MySQL에서도 성능을 향상시키는 요소들이 있을까하는 궁금증에서 발표를 준비하게 되었다.

평소에 쿼리문을 작성하고, 원하는 데이터를 뽑아내는 것만 해보다가 최적화에 대해 생각하는 게 흥미로웠다.

우선, 쿼리를 잘 다루는 것이 중요하다는 것을 알기 때문에 쿼리를 다루는 실력을 키우고, 이후에 SQL 성능 최적화까지 고려하여 효율적으로 사용할 수 있는 개발자로 성장하고 싶다.