MySQL 성능 최적화 섹션 1 ~ 3

들어가기 앞서

인프런 강의 MySQL 성능 최적화 강의를 들으면서 들었던 생각, 궁금한 내용 등을 정리한 글입니다

https://www.inflearn.com/course/mysql-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94/dashboard

MySQL 성능 최적화 강의 - 인프런

Q: 공부하면서 제가 궁금함을 느꼈던 부분입니다

P: 전에 알고 있었던 개념 / 키워드 등에 대해 다시 찾아보고 정리한 부분입니다

Multiple-Column Indexes For Optimization

다중 컬럼 인덱스: 복합 인덱스
- 복합 인덱스 설계시 칼럼 순서에 따라서 쿼리 성능이 급격하게 차이난다
- B-Tree의 한계로 복합 인덱스의 경우, 맨 앞 컬럼에 대해서 인덱스 태울 수 없으면 인덱스 못탄다. 인덱스 스킵 스캔이라는 것도 있긴 하다

복합 인덱스 설계 기준: 카디널리티
- 카디널리티가 높은 칼럼을 선행 칼럼으로 둔다.
- 그렇다고 카디널리티만 고려해서 인덱스 설계해도 되는 것은 아니다
  - 자주 사용하는 쿼리는?
  - JOIN에도 인덱스가 사용되는가?
   - Q. JOIN 마다 인덱스 사용될 수 있지만, 여러 INDEX 태우면 버퍼 풀을 많이 차지해서?
   - P. 인덱스 버퍼 풀: 메인 메모리 내에서 인덱스 데이터가 접근될 때 해당 데이터를 캐시하는 영역. 데이터를 page 단위로 관리한다. 버퍼 풀 내 페이지는 linked list로 관리되고 LRU에 의해 접근되지 않는 페이지는 캐시에서 제거한다
     - 대량의 데이터가 들어오면 자주 접근되는 데이터도 old tail으로 이동할 가능성이 있음
     - 버퍼 풀의 크기가 크면 일종의 In-memory DB처럼 동작한다고 볼 수 있음
  - 인덱스의 선행 칼럼이 범위 기반의 쿼리로 많이 이용되는가?
     - Q. 범위 쿼리의 경우 INDEX 우선순위가 높다? => 많은 범위에 대해 INDEX 탐색해서 선행 칼럼으로 두면 비효율적으로 탐색할 것
  - 이렇게 인덱스 설계하면 슬로우 쿼리가 발생하지 않을까?
  - Q. 카디널리티 낮더라도 루스 인덱스 스캔같은 방법을 선택할 수도 있지 않나?
  
 칼럼의 카디널리티 확인하는 법
 - 이미 생성된 인덱스: 인덱스 통계 테이블(innodb_index_stats)
 - 인덱스 생성하지 않았다면 MySQL 8.0 기준 히스토그램으로 확인
   - ANALYZE TABLE your_table UPDATE HISTOGRAM ON your_column WITH 100 BUCKETS;
   - P. 히스토그램: 칼럼 데이터 분포도 참조해서 실행계획 확인할 수 있도록 돕는 기능. 8.0에 추가되었고, 그 이전에는 실제 인덱스 일부 페이지 랜덤으로 샘플링해와서 계산했었다. 테이블과 인덱스에 대한 통계정보 만으로 부족한 예측을 돕는다.(아마 spike나 outlier에 대해서 잘 잡는다고 볼 수도 있을 듯?)
    - Q. 히스토그램이 들어오면서, MySQL은 대규모 쿼리에 대해 강해진 걸까? 예전에는 확실히 가벼운 쿼리만 다룰 수 있다는 느낌이었다고 들었는데 => index dive 개선하기 위해?
    - Q. 히스토그램은 매번 UPDATE해야 하는 것인가?
      - 히스토그램에 대한 통계 정보는 자동으로 수집되지 않기 때문에 ANALYZE TABLE로 주기적으로 확인해 주어야 함
    - Q. ANALYZE(통계?)와는 무슨 차이가 있을까?
      - 히스토그램의 bucket 칼럼을 통해 데이터 분포도 확인 가능하다
    - https://hoing.io/archives/1051
    - 샘플링 비율이 높을수록 히스토그램은 정확해지지만, 반대로 부하가 높아진다.
    - P. 인덱스 다이브: 실행계획 수립시 옵티마이저가 사용 가능한 인덱스로부터 조건절에 일치하는 레코드 건수를 예측하는 과정. 실제 인덱스의 B-Tree를 샘플링해서 살펴본다.
    - 변수 사용시 히스토그램 쓰기 어려움. 실제로 어떤 값이 들어올지 모르는 상태기 때문.
- Q. 인덱스 많으면 대규모 환경에서의 쿼리 수행시 index dive 과정이 길어지는 걸까? 그래서 쿼리 힌트를 적용해야 하는 경우가 생기는 걸까?

 복합 인덱스가 너무 많은 칼럼으로 구성되어 성능이 잘 안 나올 때?
 - hashed 칼럼을 인덱스로 만들어 극복할 수 있다
 - SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(val1, val2)) AND col1=val1 AND col2=val2;
 
EXPLAIN ANALYZE는 실제로 어떻게 처리되는지, 얼마나 걸렸는지
 
 type range => index scan
 rows => 옵티마이저가 읽어야 하는 데이터 수. 낮으면 좋은 것.
 filtered: 10 => 10퍼센트만 유효할 것(비효율적). 높으면 좋은 것.
 
 결론: 범위 쿼리에는 인덱스 안 타도록 하자. 인덱스 효율 나빠짐
 - Q. 범위 쿼리에 대해선 인덱스 효율이 왜 나빠지지?
 

Covering Index For Optimization

 

Q: covering index는 clustered index에만 유효한 것일까?
- Q. non-clustered-index인 경우는 어떤 경우가 있고 장점이 무엇일까?

커버링 인덱스 사용하는 경우
- 특정 컬럼 자주 조회: 특정 칼럼 때문에 테이블 접근 비용이 크게 느껴질 때 유용
- 조인 연산 비용 줄이기: 여러 테이블 연결할 때 발생 비용 줄일 수 있음
- 읽기 성능 필요한 경우: 인덱스 레벨에서만 필터링해서 읽기 성능이 필요한 경우에 적용할 수 있음
 
 커버링 인덱스 적용시 고려해야할 점
 - 인덱스 크기: 인덱스 블록에 들어갈 수 있는 데이터 수가 줄어든다
 - 쓰기 비용: 인덱스에 추가된 컬럼이 자주 업데이트되는 경우, 추가적인 쓰기 비용이 발생할 수 있다
 - 컬럼 크기: 큰 크기의 컬럼은 인덱스로 추가하기 비효율적일 수 있다(당연하지만 생각 못했던 내용)
 - 카디널리티: 카디널리티 낮은 값의 컬럼을 인덱스로 추가하면 읽기 성능이 향상되지 않을 수 있다.
  - Q. 그래도 Random Disk IO는 줄어드는 효과가 있는 것 아닌가?
 - Q. 버퍼풀 크기에 대한 내용과 상관관계?
 
 type: ref : 레코드를 동등 조건으로만 검색했다는 의미 (효율적으로 접근하고 있다는 의미)
 - Q. 동등 조건으로 검색한다는게 무슨 의미인지? 
 

ORDER BY Optimization

 ORDER BY 인덱스 없으면 filesort 하는데 sort buffer에 넣고 정렬한다. 만약 여기 넣을 수 없을만큼 크다면 데이터를 나누어 disk에 저장한 후 각각 정렬하고 합치는 식으로 전개한다
 - Q. merge sort?
 - extra 칼럼에 filesort 있으면 개선할 여지
 
 LIMIT과의 궁합이 특히 좋다. index가 없는 정렬시 전체 파일 정렬하고, 그 중에서 데이터 뽑아야 하기 때문에 비효율적
 
 filesort 이용하고 있는 경우 최적화 방법
 - sort_buffer_size 튜닝
  - filesort는 임시 테이블에 기록하고 Sort Buffer를 통해 정렬한다
  - 정렬해야하는 데이터가 많다면 디스크 수준에서 정렬할 것이라 성능이 잘 나오지 않는다
  - Q. 여기서 말하는 디스크 수준에서의 정렬의 의미는? 정렬하려면 어쨌든 메모리에 올려야 하고 그 공간은 sort buffer가 아닐까? 위에서 말한 데이터를 각각 정렬하고 합칠 때 sort buffer를 사용하지 않는다는 건가? 내 생각에는 sort buffer size로 나누어 정렬을 수행할 것이라 생각했음. 이때는 공간복잡도 낮은 선택정렬 하려나? 아니면 시간복잡도 낮은 퀵정렬 하려나? 아니면 중간 타협한 병합 정렬 하려나? 일단 optimizer의 상태에 따라 다르겠지
  - sort_buffer_size를 키우면 디스크 정렬 최소화 가능
  - sort_merge_passes 변수를 통해 디스크 정렬 or 메모리 정렬 수행했는지 알 수 있다
  - Q. sort_merge_passes?
 - Single-Pass에서 Two-Pass로 튜닝
  - filesort는 Single-Pass와 Two-Pass 방식으로 정렬 수행할 수 있다
  - Single-Pass: Sort Buffer에 데이터를 모두 넣어 정렬 수행
  - Two-Pass: 정렬하는 칼럼과 PK만 넣어 정렬 수행. 이후 나머지 데이터와 병합
  - Two-Pass는 sort buffer에서 수행 안 하는 듯. 
  - 일반적으로 Single-Pass가 성능이 좋으나, 데이터 크기가 크면 Two-Pass가 성능적으로 우위
  - 의도적으로 Single-Pass에서 Two-Pass로 변경하고 싶다면 max_length_for_sort_data 변수 값 조절해야 한다. (MySQL 8.0.20 이전 경우만 해당함.) => 이후부터는 옵티마이저가 알아서 잘해줌
 - 문자열 정렬 튜닝
- 값 전체 정렬하지 않고, max_sort_length 값 만큼만 잘라서 정렬하도록 수행한다
- 필요하다면 문자열 정렬시 max_sort_length 값 줄이면 정렬 결과가 빨라질 수 있다

ORDER BY 절에 대해 
- ORDER BY절에 대해 PK를 추가하면 순서를 보장할 수 있으며, 성능 하락도 없다. (모든 세컨더리 인덱스는 내부적으로 PK를 포함하기 때문. 몰랐던 사실!)

as-is
SELECT * FROM ratings ORDER BY category LIMIT 5;

to-be
SELECCT * FROM ratings ORDER BY category, id LIMIT 5; (이것 때문에 ORDER BY 순서 보장이 안 되고 있던 건가...?)
=> index는 category에만 가지고 있지만, 성능 손해 안 본다

EXPLAIN SELECT * FROM product
ORDER BY created_at DESC, price ASC
LIMIT 10;
=> 여기서 rows가 1_000_000 나오면? 10개의 데이터를 뽑기 위해 100만건에 접근한다는 의미. 낭비 심하다

sort_buffer_size는 세션 단위로도, 글로벌 단위로도 변경 가능
- Q. 모든 변수들이 다 그럴까?
- Q. 트랜잭션 격리 레벨도 그럴까?
- sort_buffer_size는 session 단위로 늘리는 것이 좋다