열심히 정리해본 Real MySQL 10장. 실행 계획

그동안 책을 많이 읽었지만.. 따로 정리하면 시간이 오래 걸려서 읽기만 했다. 이해하기보단 따라 적는 것에 의의를 두게 되는 것 같기도 하고ㅠ

하지만 리마큐 10장은 오랜만에 열심히 노션에 정리해 보았다. 유익한 내용들~

10.1 통계 정보

: 쿼리 최적화를 위해 MySQL 옵티마이저가 참조하는 메타데이터, 테이블마다 존재하는 통계 정보를 별도 테이블에 모아둔 것

• 통계 정보가 정확하지 않다면 엉뚱한 방향으로 쿼리를 실행할 수 있기데 통계 정보는 매우 중요함
  • ex. 통계 정보는 10건 미만이래서 풀스캔 했는데 사실 1억건이 있는 경우
• MySQL 5.6 이후부터는 각 테이블의 통계 정보를 Innodb_index_stats, Innodb_table_stats 테이블로 관리할 수 있게 개선됨 → 재시작해도 유지 가능

 

통계 정보의 칼럼들

• 인덱스가 가진 유니크한 값의 개수
  • 카디널리티가 높은(값이 다양한) 인덱스 효율이 높음 → 우선 선택
• 인덱스의 리프 노드 페이지 개수
  • 리프 페이지가 많으면 디스크I/O가 많이 필요함
• 인덱스 트리의 전체 페이지 개수
  • 트리 깊이를 고려함 (깊을수록 성능 저하)
• 테이블의 전체 레코드 개수
  • 풀스캔 비용 추정 → 인덱스 스캔과 비교
• 프라이머리 키의 크기 (InnoDB 페이지 개수)
  • = 클러스터 인덱스의 전체 크기
  • pk기반 테이블 접근 비용을 계산함
• 프라이머리 키를 제외한 인덱스의 크기 (InnoDB 페이지 개수)
  • = 보조 인덱스의 크기
  • 여러 인덱스 중 선택하는 기준

통계 정보	쿼리 최적화에서의 역할
인덱스 유니크 값 개수 (카디널리티)	선택도 판단, 인덱스 선택 우선순위 결정
인덱스 리프 노드 페이지 수	인덱스 범위 스캔 비용 추정
인덱스 트리 전체 페이지 수	인덱스 트리 탐색 깊이 추정
테이블 전체 레코드 수	풀 스캔 vs 인덱스 스캔 비교 판단
프라이머리 키의 크기	전체 데이터 접근 비용 추정
보조 인덱스 크기	인덱스 스캔 비용 및 커버링 인덱스 여부 판단

 

히스토그램

• 8.0부터 칼럼의 데이터 분포를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 됨
  • 싱글톤 히스토그램
    • 칼럼값 개별로 버킷 할당 → 카디널리티가 낮은 경우 사용됨
  • 높이 균형 히스토그램
    • 개수가 균등한 칼럼값의 범위별로 버킷 할당
    • 범위가 균등한게 아니라 개수가 균등하도록 할당 → 기울기 차이로 분포를 판단할 수 있음
• 실제로 데이터는 항상 균등한 분포가 아닌데, 기존 통계 정보는 균등함을 가정하고 예측함 → 이를 보완하기 위해 히스토그램 등장!
  • 각 범위(버킷)별로 레코드 건수, 유니크한 값의 개수 정보를 가짐 → 훨씬 정확한 예측

 

p406.

히스토그램이 없을 경우

• 테이블 전체 레코드 건수, 크기 등의 단순 정보만으로 조인의 드라이빙 테이블을 결정하게 됨

SELECT /*+ JOIN_ORDER(s, e) */
FROM salaries s
JOIN employees e ON e.emp_no = s.emp_no
AND e.birth_date BETWEEN '1950-01-01' AND '1950-02-01'
WHERE s.salary BETWEEN 40000 AND 70000;

• s.salary BETWEEN 40000 AND 70000; : 해당 row가 엄청 많음
• e.birth_date BETWEEN '1950-01-01' AND '1950-02-01' : 상대적으로 적음

따라서 salary 먼저 읽고 조인하면 조인 횟수가 많아짐

하지만 employee 먼저 읽고 조인하면 훨씬 적은 조인 + 버퍼 풀 적중률 높아짐(4000-7000이니까)

근데 히스토그램이 없으면 salary의 4000-7000 구간이 밀도가 높다는걸 몰라서 salary부터 읽을 수 있다는 것

 

p407.

[인덱스 다이브]

: 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행계획을 선택하기 위해 → 실제 인덱스의 B-Tree를 샘플링해서 살펴보는 것

• 인덱스된 칼럼을 검색조건으로 사용하는 경우 → 칼럼의 히스토그램을 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용함
  • 더 정확한 결과를 기대할 수 있기 때문
  • 하지만 비용이 필요함

 

코스트 모델

: 쿼리에 대해 다양한 작업들이 얼마나 필요한지 예측 → 전체 작업 비용 계산 → 최적의 실행 계획을 찾는 과정에서 필요한 단위 작업들의 비용

구분 cost_name default_value 설명

구분	cost_name	default_value	설명
engine_cost	io_block_read_cost	1.00	디스크 데이터 페이지 읽기
engine_cost	memory_block_read_cost	0.25	메모리 데이터 페이지 읽기
engine_cost	disk_temptable_create_cost	20.00	디스크 임시 테이블 생성
engine_cost	disk_temptable_row_cost	0.50	디스크 임시 테이블의 레코드 읽기
server_cost	key_compare_cost	0.05	인덱스 키 비교
server_cost	memory_temptable_create_cost	1.00	메모리 임시 테이블 생성
server_cost	memory_temptable_row_cost	0.10	메모리 임시 테이블의 레코드 읽기
server_cost	row_evaluate_cost	0.10	레코드 비교

 

• row_evaluate_cost는 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업을 의미하는데,
  • 증가 → 풀스캔같이 많은 레코드를 처리하는 쿼리 비용이 높아지고, 레인지 스캔같은 적은 레코드를 처리하는 비용이 낮아짐
    • ?? 그냥 상대적 표현인거겠지
• EXPLAIN 명령을 통해 cost 확인 가능

 

중요한건 각 단위 작업에서 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용/저비용으로 바뀌는지 파악하는 것

• key_compare_cost 증가 → 정렬을 안할 가능성이 높아짐
• row_evaluate_cost 증가 → 풀스캔 안하고 인덱스 레인지 스캔할 가능성이 높아짐
• io_block_read_cost 증가 → 가능하면 innodb 버퍼 풀에 데이터 페이지가 많이 적재된 인덱스를 사용하려는 계획을 선택할 가능성이 높아짐
  • 자주 쓰는 pk인덱스 선택?
• memory_block_read_cost 증가 → innodb 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적어도 그 인덱스를 사용할 가능성이 높아짐
  • 메모리에 있어도 어차피 비싸다 → 버퍼 풀 적중률이 낮더라도 그 인덱스가 유리하면 선택한다는 뜻
  •  

10.2 실행 계획 확인

• 테이블 포맷
• 실행시간 확인하는 법
• EXPLAIN ANALYZE : 실제 쿼리를 실행 → 사용된 실행 계획과 소요 시간을 보여주는 것
• EXPLAIN : 쿼리 실행 안하고 계획만 추출

 

10.3 실행 계획 분석

✅ 실행계획 (EXPLAIN 결과 테이블 포맷)

EXPLAIN
SELECT first_name, last_name
FROM employees
WHERE emp_no = 10001;

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	NULL

 

id 칼럼

: select 키워드 단위로 구분한 ‘단위 쿼리’별로 부여되는 식별자

• 하나의 select 안에 있으면 다른 테이블이라도 같은 id
• id칼럼이 접근 순서를 의미하진 않음
  • 접근 순서는 테이블에서 위에 있는 순서대로
  • EXPLAIN FORMAT=TREE로 정확하게 확인 가능

 

select_type 칼럼

: 어떤 타입의 쿼리인지

• SIMPLE
  • union, 서브쿼리를 사용하지 않는 단순 쿼리
• PRIMARY
  • UNION, 서브쿼리를 사용하는 select 쿼리의 가장 바깥쪽 쿼리
• UNION
  • UNION으로 결합하는 select 중 두 번째부터 UNION
    • 첫 번째 단위 쿼리는 DERRIVED(임시 테이블)로 표기
• DEPENDENT UNION
  • UNION, UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향 받는 경우
• UNION RESULT
  • UNION, UNION DISTINCT의 경우 여전히 임시 테이블에 결과를 버퍼링함 → 임시테이블을 가리키는 타입
  • 그러면 왜 UNION의 첫 번째 단위 쿼리는 DERIVED인가?DERIVED : 서브쿼리의 결과를 임시 테이블로 만들어 사용하는 경우
    • 결과를 계속 Union all 해서 엮는 경우? union 자체가 서브 쿼리에 있는 경우
    UNION RESULT : UNION 결과를 임시 테이블로 버퍼링하여 사용하는 경우
    • select A union distinct select B 이런식인 경우
  • 다르다고 한다.
• SUBQUERY
  • from절 이외에서 사용되는 서브쿼리 (from절은 DERIVED)
  • 처음 한 번 실행해서 그 결과를 캐시함
• DEPENDENT SUBQUERY
  • 서브쿼리가 바깥 select 쿼리에서 정의된 칼럼을 사용하는 경우 (as cnt)
  • 바깥 쿼리의 칼럼 값 단위로 캐시가 만들어지는 방식
• DERIVED
  • 단위 select 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것
    • from절 안에서 테이블처럼 쓰이는 서브쿼리
• DEPENDENT DERIVED
  • 래터럴 조인 → 서브쿼리에서도 외부 칼럼을 참조하는 경우
  • from절 서브쿼리도 가능
• UNCACHEABLE SUBQUERY
  • 서브쿼리에 사용자 변수, rand() 같이 캐시가 불가능한 요소가 담겨있는 경우
• UNCACHEABLE UNION
• MATERIALIZED
  • DERIVED랑 비슷

 

table 칼럼

: 실행계획은 table 기준으로 표시되니까

• <> : 임시 테이블
  • <derived 2> : id 2인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비되어야 하는구나
  • …

 

partitions 칼럼

: 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 partitions 칼럼에 표시

• types는 ALL (파티션은 물리적으로 개별 테이블처럼 별도의 공간을 갖기 때문)

 

type 칼럼

: 각 테이블의 레코드를 어떻게 읽었는지 → 반드시 체크해야할 중요한 정보

성능 빠른 순서대로

• system
• const
  • pk or unique key를 이용하는 where 조건절 ⇒ 반드시 1건을 반환하는 쿼리의 처리 방식
  • pk, unique key 둘 다 동등 조건절 ⇒ 이것도 const 처리
  • 옵티마이저가 최적화 시점에 쿼리를 통째로 상수화하므로 상수(constant)
• eq_ref
  • 조인에서 처음 읽은 테이블의 칼럼값을, 그다음 읽을 테이블의 pk or unique key의 검색조건에 사용할 때를 가리킴
  • pk / unique key로 동등 조건 검색당하는 테이블에 eq_ref
• ref
  • 조인 순서와 관계없음, pk / unique key 관계없음
  • 하지만 동등 조건이어야 함

const, eq_ref, ref 모두 동등 비교 연산자여야 함 → 성능 ㄱㅊ 크게 신경쓰지 않아도 됨

• fulltext
  • MySQL 서버의 전문 검색 인덱스(FULLTEXT KEY)를 사용해 레코드를 읽는 접근 방법
  • match (..) against (..) 반드시 해당 테이블에 전문 검색 인덱스가 준비되어야 동작함
  • 근데 막상 쓰면 레인지 스캔이 더 빠른 경우가 많다고 함;
• ref_or_null
  • ref랑 같은데 NULL 비교가 추가된 형태
  • 많이 안쓰지만 not bad
• unique_subquery
  • 서브쿼리에서 유니크값만 반환할 때 사용
• index_subquery
  • 중복값이 있을 수 있지만 인덱스를 이용해 제거할 수 있음
    • 이런 경우 첫번째 값을 찾으면 바로 탐색 끝 (중복이 몇개든 상관 없음)
    • SELECT * FROM employees WHERE emp_no IN ( SELECT emp_no FROM dept_emp );
• range
  • 익히 알고 있는 인덱스 레인지 스캔 형태 > 일반적으로 가장 많이 사용하는 접근 방법
  • <, >, IS NULL, BETWEEN, IN, LIKE 등
  • const, ref, range 모두 묶어서 인덱스 레인지 스캔이라고 부름
• index_merge
  • 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만듦 → 결과를 병합해서 처리하는 방식
  • 항상 2개 이상의 집합이 있음 > 교집합, 합집합, 중복 제거 등 부가적 작업 필요
  • OR 연산자로 연결된 경우
• index
  • 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔
  • 그냥 풀스캔이랑 비교하면
    • 크기가 작고
    • 정렬의 장점을 이용하므로 훨씬 빠름
  • 인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
  • 인덱스를 이용해 정렬이나 그루핑이 가능한 경우 사용됨
• ALL
  • 풀스캔
  • 리드 어헤드 : 한 번에 여러 페이지를 읽어서 처리하는 기능을 제공함
    • 인접한 페이지가 연속해서 몇 번 읽히는 경우 백그라운드로 읽기 스레드가 최대 64개의 페이지씩 한꺼번에 디스크에서 읽어오도록 동작함

 

possible_keys 칼럼

: 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던, 사용될 뻔 했던 인덱스 목록

 

key 칼럼

: 최종 선택된 실행계획에서 사용된 인덱스

 

key_len 칼럼

: 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줌

→ 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려줌

 

ref 칼럼

: equal 비교 조건으로 어떤 값이 제공되었는지 보여줌 (where절 조건)

 

rows 칼럼

: 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수 → 통계 정보를 참조해 옵티마이저가 산출한 예상값이라 정확하진 않음

 

filtered 칼럼

: 필터링되고 남은 레코드의 비율 = 조건을 만족하는 레코드 비율

→ 이 칼럼에 표시되는 값이 얼마나 정확히 예측될 수 있느냐에 따라 조인의 성능이 달라짐

 

extra 칼럼

: 성능과 관련된 중요한 내용이 자주 표시됨

• const row not found
  • 레코드 1건도 없음
• Deleting all rows
  • 모든 레코드를 삭제하는 핸들러 api를 호출함
• Distinct
  • distinct 처리를 위해 필요한 레코드만 읽음
• FirstMatch
  • 첫 번째로 일치하는 한 건만 검색함
• Full scan on NULL key
  • co1 in null 에서 col1이 Null인 경우 서브쿼리에 사용된 테이블에 대해 풀스캔해서 col1을 알아냄
• Impossible HAVING
• Impossible WHERE
• LooseScan
  • LooseScan 조인 최적화 기법 사용
• No matching min/max row
• No matching row in const table
• No matching rows after partition pruning
  • 대상 파티션이 없음
  • 단순히 삭제할 레코드 없음 XX 아예 파티션이 없음
• No table used
  • from절이 없거나 from dual이 사용된 경우
• Not exists
  • outer join을 이용해 안티 조인 (A엔 있는데 B엔 없는 값 조회)를 수행한 경우
    • 해당 테이블의 레코드가 존재하는지 아닌지만 판단한다는 것
• Plan isn’t ready yet
  • 실행계획 수립 중
  • explain forr connection 8; 다른 커넥션의 실행계획을 엿볼 수 있음
• Range checked for each record(index map:N)
  • 레코드마다 인덱스 레인지 스캔을 체크한다는 뜻
    • emp_no가 작으면 풀스캔
    • emp_no가 크면 인덱스 레인지 스캔
  • index map : 0x1
    • 0x1를 이진수로 변환 > 1
    • 1번째 인덱스를 사용할지 or 풀스캔할지를 결정한다는 뜻
  • index map : 0x19
    • 0x19 > 11011
    • 1, 2, 4, 5번째 인덱스를 사용 가능한 인덱스 후보로 정했다는 뜻
• Recursive
  • 재귀 쿼리 > 내부 임시 테이블 생성하여 저장 > 필요할 때 해당 임시 테이블을 풀스캔
• Rematerialize
  • 래터럴 조인되는 테이블 > 선행 테이블의 레코드별로 서브쿼리 실행 > 결과를 임시테이블에 저장함
    • 일단 조인된 결과를 임시 테이블에 저장하고
    • 그 다음 임시 테이블과 조인된 결과를 새로운 내부 임시 테이블로 저장
• Select tables optimized away
  • 오름/내림차순으로 1건만 읽는 형태의 최적화
• Start temporary, End temporary
  • Duplicate Weed-out 최적화 전략 사용시
  • 조인의 첫 테이블 Start temporary
  • 조인이 끝나는 부분에 End temporary
• unique row not found
  • pk / unique key로 outer join하는데 없을 때
• Using filesort
  • order by를 처리하는데 적절한 인덱스를 찾지 못했을 때
  • 조회된 레코드를 정렬용 메모리 버퍼에 복사 → 퀵 소트 / 힙 소트 알고리즘을 이용해 정렬 수행
• Using index (커버링 인덱스)
  • 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있는 경우
  • 인덱스 이용하는 쿼리에서 가장 큰 부하를 차지하는 부분은 → 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업인데
    • 커버링 인덱스는 이 작업이 필요 없음
  • 그래서 인덱스 레인지 스캔을 사용하지만 성능이 별로라면 → 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경하는 것도 방법이다
  • innodb는 클러스터링 인덱스 특성 때문에 인덱스 하나만 만들어도 pk가 포함됨 → 커버링 인덱스로 처리될 가능성이 높아짐
    • 세컨더리 인덱스엔 데이터 레코드를 찾아가기 위한 주소부분에 pk를 저장 → 추가 칼럼을 하나 더 갖는 효과를 얻음** Extra칼럼의 Using index(커버링 인덱스)와 type칼럼의 index는 성능상 반대되는 개념
• Using index condition
  • 옵티마이저가 인덱스 컨디션 푸시 다운 최적화를 사용한 경우
• Using index for group-by
  • 루스 인덱스 스캔을 사용한 경우 (group by 처리에 인덱스 사용 → 정렬 안해도됨)
  • 타이트 인덱스 스캔을 통한 GROUP BY
    • avg(), sum(), count()같은 경우 듬성듬성 읽을 수 없음
  • 루스 인덱스 스캔을 통한 GROUP BY
    • 듬성듬성 가능
    • 하지만 손익분기점을 판단하여 풀스캔 하기도
• Using index for skip scan
  • 루스 인덱스 스캔 확장버전
• Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)
  • 옵티마이저는 조인되는 두 테이블의 각 칼럼에서 인덱스 조사 → 인덱스가 없는 테이블부터 먼저 읽어서 조인을 실행함 (드리븐 테이블은 검색 위주인데 인덱스 없으면 안되니까)
  • 근데 드리븐 테이블에 인덱스가 없다면 → nested loop join / hash join 사용
    • 조인 버퍼 사용
    • 카다시안 조인을 하는 경우엔 항상 조인 버퍼 사용
      • 항상 풀스캔 → 버퍼에 올려 반복 사용해야함
• Using MRR (Multi Range Read 최적화)
  • MySQL 엔진이 여러 개의 키값을 한 번에 스토리지 엔진으로 전달 → 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화함
• Using sort_union(..), Using union(..), Using intersect(..)
  • index merge 결과를 어떻게 병합했는지 좀 더 상세하게 설명하기 위함
  • 둘 다 충분히 인덱스를 사용할 수 있는 조건이 OR 연산자로 연결된 경우
    • union() : 동등비교처럼 일치하는 레코드 건수가 많지 않은 경우 > 합집합 추출
    • sort union() : 일치하는 레코드 건수가 많은 경우
  • intersect() : AND 연산자 > 교집합
• Using temporary
  • 디스크 or 메모리에 임시 테이블
• Using where
  • MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우 표시됨
  • where emp_no between 100 and 200 and gender = ‘F’
    • 여기서 gender = ‘F’가 아닌 레코드를 버리는 과정이 using where
    • filtered값이랑 같이 보면서 성능이슈를 확인 가능
• Zero limit
  • 데이터가 아닌 메타데이터만 필요한 경우 > 쿼리 마지막에 LIMIT 0 사용하면 레코드를 전혀 읽지 않음