Study 9 Chapter 옵티마이저와 힌트 1

[InJun2]

진행내용

7월 14일 RealMySQL 9챕터 옵티마이저와 힌트 스터디 진행

• 해당 챕터의 내용이 많아 나누었으며 283p ~ 334p 인덱스 머지까지 진행

하고싶은 이야기 및 문제를 답변으로 작성해주세요

[kmw2378]

인덱스를 사용했음에도 풀 테이블 스캔이 일어나는 경우

• 테이블의 레코드 건수가 너무 작은 경우
• WHERE 절이나 ON 절에 사용할 수 있는 인덱스가 없는 경우
• 인덱스를 사용할 수 있어도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
  • 인덱스를 통해 레코드를 가져오는 비용이 크므로

병렬 처리용 스레드가 CPU 코어보다 클 때 성능이 저하되는 이유

• 컨텍스트 스위칭이 자주 발생하므로

MySQL에서 정렬 비용을 최소화하는 방법

• ORDER BY 절에 사용할 수 있는 인덱스를 생성
• 소트 버퍼 크기를 적절히 설정
  • 만약, 특정 쿼리가 소트 버퍼가 많이 필요하다면 해당 세션의 소트 버퍼만 일시적으로 늘리자

BLOB이나 TEXT 컬럼이 있는 테이블은 싱글 패스 정렬 방식을 사용할 수 없는 이유

• 상대적으로 크기가 크므로 메모리에 올리면 비효율적이기 때문

SELECT 절에서 꼭 필요한 컬럼만 조회해야 하는 이유

• 싱글 패스 방식은 SELECT 절에 포함된 컬럼들을 전부 담아 정렬을 수행한다. 따라서, 정렬 시 불필요한 컬럼까지 포함하여 정렬을 수행하여 성능이 저하될 수 있다.
• JOIN, DISTINCT 등으로 인해 임시 테이블이 만들어지는 경우 컬럼이 많아져 내부 임시 테이블이 디스크에 생성될 수 있다.

임시 테이블이 디스크에 생성되지 않도록 하는 방법

• ORDER BY, GROUP BY 에 사용될 인덱스를 생성
• UNION (중복 X) 대신 UNION ALL 을 사용
• tmp_table_size, max_heap_table_size 시스템 변수를 조정

임시 테이블이 디스크에 생성되면 I/O 비용이 발생하여 성능이 크게 저하될 수 있다. 이를 최소화하자.

JDBC의 쿼리 처리 방법과 그 이유

• 버퍼링 방식을 사용
• 전체 처리(Throughput) 시간이 짧고 DB 서버와 통신 횟수가 적어 자원 소모가 줄어들기 때문

MySQL은 스트리밍 방식을 사용하지만 JDBC가 버퍼링을 사용

[kmw2378]

드라이빙/드리븐 테이블

• 옵티마이저가 판단하여 적은 비용이 발생하는 테이블을 드라이빙 테이블을 선정
  • 선정 기준
    • 테이블 크기가 작은 것
    • 인덱스 사용 가능 여부
    • 조건 필터링이 적은 테이블

Using filesort가 출력되는 경우

• 정렬 기준이 너무 많아 인덱스를 생성하기 어려운 경우
• GROUP BY, DISTINCT 의 처리 결과를 정렬할 때
• UNION 결과와 같이 임시 테이블의 결과를 다시 정렬하는 경우
• 랜덤하게 결과 레코드를 가져와야 하는 경우
• 적은 데이터를 빠르게 정렬할 때 사용

중요

인덱스가 없다면 LIMIT가 있어도 Chunk를 사용할 수 없다.