10.3.5 type 컬럼 MySQL 서버가 각 테이블의 레코드를 어떻게 읽었는지 알 수 있다. ALL: 풀 스캔 -> 가장 성능이 안좋음 index_merge 를 제외하고 모두 하나의 인덱스를 탄다. 12가지 타입이 있다. [1] system InnoDB에는 없다. 레코드 0, 1 인 테이블을 읽는 방식 [2] const 테이블 레코드 수, 조인 순서와 관계없이 유니크 키(PK) 컬럼을 이용해서 동등 비교로 조회할 때 사용하는 방식. 한 건의 레코드를 반환해서 const 라고 한다. [3] eq_ref 여러 테이블의 조인에서 표시되는 타입. 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 조회된다는 보장이 있을 때 사용 가능한 방식. 첫번째 읽은 컬럼 값이 두번째 테이블의 유니크 키 또는 PK가..
10.3 실행 계획 분석 [2] select_type 컬럼 각 단위 SELECT 쿼리가 어떤 타입인지 표시. SIMPLE: UNION, SUBQUERY 없는 단순 SELECT PRIMARY: UNION, SUBQUERY에서 가장 바깥쪽의 쿼리 UNION: UNION 결합하는 쿼리에서 첫 번째 쿼리를 제외한 나머지 쿼리들 UNION RESULT: UNION 결과를 담아두는 테이블. 임시 테이블이고 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id값은 없다. SUBQUERY: select_type 컬럼에서는 FROM 절 이외에서 사용되는 서브 쿼리만을 이 타입으로 표시한다. DERIVED: 단위 쿼리의 실행 결과가 메모리나 디스크에 임시 테이블을 생성할 때 표시되는 타입. 옵티마이저가 똑똑해졌지만 한계가..
10.2 실행 계획 확인 실행 계획은 DESC, EXPLAIN 명령으로 확인 가능 8.0 버전부터 EXPLAIN에 추가할 수 있는 새로운 옵션이 생겼다. - 출력 포맷 조절 옵션 및 쿼리 실행 시간 확인 옵션 10.2.1 실행 계획 출력 포맷 FORMAT 옵션으로 JSON, TREE, 단순 테이블 형태로 결과 포맷을 설정할 수 있다. 10.2.2 쿼리의 실행 시간 확인 EXPLAIN ANALYZE 기능으로 실행 계획과 단계별 소요 시간을 확인할 수 있다. 항상 결과를 TREE 포맷으로 반환하기 때문에 FORMAT 옵션과 함께 사용 불가능. EXPLAIN ANALYZE 기능은 실제로 쿼리를 실행하기 때문에 아주 오래 걸리는 쿼리라면 그만큼 시간이 걸린다. EXPLAIN으로 먼저 실행 계획만 확인해서 어느정..
10.1 통계 정보 정확한 통계 정보는 옵티마이저 실행 계획에 매우 중요하다. 1억건 있는 테이블이 10건 밖에 없다는 정보가 있으면 풀스캔 해버릴 수 있다. 10.1.1 테이블 및 인덱스 통계 정보 10.1.1.1 MySQL 서버의 통계 정보 InnoDB 스토리지 엔진을 사용하는 테이블의 통계정보는 영구적 보관 가능 -> 서버가 재시작돼도 기존의 통계 정보 활용 가능 -> 의도하지 않은 통계 정보 변경을 막을 수 있게 됨 10.1.2 히스토그램 컬럼의 데이터 분포도 10.1.2.1 히스토그램 정보 수집 및 삭제 버킷의 단위에 따라 2타입이 있다 - 싱글톤: 컬럼값 개별로 관리 (버킷의 단위는 컬럼 유닛 단위) - equi-height: 범위를 균등하게 나누어 개수로 관리 삭제 가능. 삭제하지 않고 그냥..
9.4.2 옵티마이저 힌트 [1] 종류 영향 범위에 따라 4가지로 나뉜다. - 인덱스: 특정 인덱스의 이름을 사용할 수 있는 힌트 - 테이블: 특정 테이블의 이름을 사용할 수 있는 힌트 - 쿼리 블록: 특정 쿼리 블록에만 영향 - 글로벌: 모든 쿼리에 영향 (쿼리 블록? SELECT로 시작하는 서브쿼리 영역) 힌트의 사용위치는 모두 동일하다. [2] MAX_EXECUTION_TIME 유일하게 쿼리 실행 계획에 영향이 없는 힌트. 단순히 쿼리의 최대 실행시간을 설정하는 힌트이다. 지정된 시간을 초과하면 쿼리는 실패한다. [3] SET_VAR SET_VAR 힌트로 쿼리 실행 계획에 영향을 주는 시스템 변수를 조정할 수 있다. 하지만 모든 시스템 변수를 조정할 수는 없다. 기억해 둘 힌트. [4] SEMIJO..
9.3.2 조인 최적화 알고리즘 조인하는 테이블이 많아질 수록 실행 계획을 세우기 힘들어지고, 수립하는 시간도 더 오래 걸린다. 왜그럴까 이유를 알아보자 조인 최적화 알고리즘 2개 [1] Exhaustive 검색 알고리즘 예전 버전부터 사용되온 알고리즘으로 FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 1개를 찾는 알고리즘(테이블이 n개면 n!). 굉장히 오래걸리는 알고리즘 [2] Greedy 검색 알고리즘 5.0 부터 도입된 알고리즘. n개 테이블이 있다면 모든 테이블의 조합에 대해 전부 계산하는게 아니라 optimizer_search_depth 시스템 설정 변수에 정의된 테이블 만큼의 조합을 계산해서 최적의 실행계획으로 맨 앞에 오는 테이블을 고정한다. 그다음 남..
9.3.1.15 컨디션 팬아웃 여러 테이블을 조인해야하는 상황에서 옵티마이저는 조건에 일치하는 레코드 건수가 적은 순서대로 테이블을 조인한다. condition_fanout_filter 최적화가 활성화되면, 옵티마이저는 WHERE 조건절에 사용된 컬럼이 인덱스, 히스토그램을 가지고 있는 경우 조건을 만족하는 레코드의 비율을 계산할 수 있다. 9.3.1.16 파생 테이블 머지 FROM 절에 서브쿼리가 사용된 경우 예전 버전에서는 임시테이블을 생성해서 FROM 절에 해당하는 쿼리 결과를 저장하고 그 임시테이블과 외부 테이블로 결과를 반환했다. 서브 쿼리의 조회결과가 많아지면 임시테이블을 디스크에 저장하게 되고 다시 읽어야하는 오버헤드가 생긴다. 5.7 버전 이후로 FROM 절의 서브쿼리를 외부 쿼리로 병합하..
9.3.1.10 테이블 풀-아웃 세미 조인의 서브쿼리에 사용된 테이블을 밖으로 꺼내서 조인하는 형식으로 쿼리를 재작성하는 최적화. 서브쿼리 최적화가 도입되기전 수동으로 쿼리를 튜닝하던 대표적인 방법이었다. 테이블 풀 아웃 최적화가 일어났는지 확인하는 법은 실행 계획에서 해당 테이블들의 id 컬럼 값이 같은지 비교해보는 것이 가장 간단하다. 세미 조인의 서브쿼리에만 사용가능하고 서브쿼리 부분이 유니크 인덱스나 PK key loop up으로 결과가 1건인 경우에만 사용가능하는 등의 제한조건이 있다. 9.3.1.11 퍼스트 매치 실행 계획의 Extra 컬럼에 FirstMatch(e)라는 문구로 출력된다. 조건과 일치하는 조인된 레코드가 1건이 나오는 순간 쿼리가 반환된다. 레인지 스캔하며 조건에 맞는 레코드 ..
9.3 고급 최적화 옵티마이저는 통계정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 세운다. 옵티마이저 옵션 - 조인 관련 옵션 / 스위치. 두가지가 있다 9.3.1 옵티마이저 스위치 옵션 옵티마이저 스위치 마다 default, on, off 중 하나로 설정할 수 있다. 글로벌, 세션별 혹은 쿼리별로 설정 가능하다. 각 스위치 별로 간략하게 정리하면 다음과 같다. [1] mrr(multi-range read) & batched_key_access 지금까지 MySQL에서 지원하던 조인 방식은, 드라이빙 테이블의 레코드를 기준으로 드리븐 테이블을 스캔하며 조건에 맞는 레코드를 조인하는 방식(nested loop join)이었다. 이는 스토리지 엔진이 드리븐 테이블을 읽고 있을 때는 아무런 최적화도 못하..