쿼리 실행 절차
- 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리
- SQL 파싱정보(파스트리) 확인하면서 어떤 테이블부터 읽고 어떤 인덱스 쓸지 선택
- 결정된 테이브르이 읽기 순서나 선택괸 인덱스를 이용해 스토리지 엔진으로부터 데이터 가져옴
옵티마이저 종류
- RBO : 규칙 기반 최적화 - 옵티마이저내 우선순위로 계획 수립, 항상 동일 절차 나타남.
- CBO : 비용 기반 최적화 - 통계정보를 이용함.
통계정보 갱신
- ANALYZE TABLE tb_name;
- ALTER TABLE tb_name ANALYZE PARTITION p3;
- MyISAM : Analyze 시 테이블은 읽기만 가능
- InnoDB : Analyze 시 테이블 읽기 쓰기 모두 불가능
실행계획 분석
- explain 명령어를 Query 앞에 입력함
- id 컬럼
+ 단위 SELECT ( 하나의 SELECT 구문으로 분해되는) 쿼리별로 부여되는 식별자.
+ Join 에 의해서는 동일한 ID 가질수 있음
- select_type 컬럼
+ SIMPLE : union 이나 sub-query 사용하지 않는 단순한 SELECT
+ Primary :union 이나 sub-query 가 포함된 SELECT 쿼리의 계획중 가장 바깥쪽(Outer) 의 단위쿼리
+ UNION : union으로 결합하는 단위 Query 중 첫번째를 제외한 두 번째 이후의 단위 SELECT 쿼리
+ Union Result : Union 결과를 담아두는 테이블
+ SubQuery : from 절 이외에서 사용되는 서브 쿼리
+ DERIVED : 서브쿼리가 FROM 절에 사용된 경우
/ 실행 결과를 메모리나 디스크에 임시 테이블 생성함.
- table 컬럼
+ "<>" 로 둘러쌓인 이름은 임시 테이블을 의미
- Type 컬럼
+ 각 테이블의 리코드를 어떤 방식으로 읽었는지 의미.
+ ALL 은 Full Table Scan, 이고 나머지는 전부 Index 사용.
- Possible_keys
+ 사용될 뻔! 한 인덱스 목록
- Key
+ 최종적으로 선택된 실행 계획에서 사용하는 인덱스를 의미
- Key_len
+ 다중 컬럼으로 구성된 인덱스에서 몇개의 컬럼(각 레코드에서 몇 바이트)까지 사용했는지 .
- ref
+ type 이 ref 일 경우, 참조 조건(Equal 비교 조건)으로 어떤 값이 제공되었는지 보여줌.
- rows
+ 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줌.
/ 통계정보를 참조하여 예측한 값으로 정확하지 않음.
- extra
+ 실행 계획에서 성능에 관련된 중요한 내용.
- explain extended
+ 추가적으로 Filtered 컬럼 생성
+ 예측된 rows 수에서 실제 최종적으로 남은 rows 수의 비율.
- explain partitions
+ partition 테이블의 실행 계획 확인 가능.
MySQL 주요 처리 방식
- Full Table Scan
------------------------------------------번외 추가적 실행계획-------------------------------------------------
MySQL의 실행계획을 살펴보면 정확히 알 수는 없지만 대략적으로 짐작이 가능한 요소들이 존재한다.이번에는 이러한 짐작 가능한 요소들에 대해서 확실히 알아보도록 하겠다.확실한 실행계획에 대한 이해가 성능이 최적화된 쿼리를 작성할 수 있게 해준다.EXPLAIN
select * from tableA;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tableA ALL (null) (null) (null) (null) 361269
* ID
id는 실행계획의 순서를 나타낸 것이다.
* SELECT_TYPE
select 수행시의 select type을 표현하는 정보이다. 여러가지가 존재하며 이 중 하나의 값으로 정해진다.
SIMPLE - 단순 select, union이나 subquery를 사용하지 않는다.
PRIMARY - 가장 바깥쪽의 select
UNION - union에서 두번째 혹은 이후의 select
DEPENDENT UNION - union에서 두번째 혹은 이후의 select, outer 쿼리에 의존적
UNION RESULT - union의 결과
SUBQUERY - subquery의 첫번째 select
DEPENDENT SUBQUERY - subquery의 첫번째 select, outer 쿼리에 의존적
DERIVED - select로 추출된 테이블(from 절의 subquery)
* TABLE
해당행의 대상이 되는 table
* TYPE
join의 타입
SYSTEM - 테이블에 단 하나의 행만 존재한다. const join 타입의 특별한 경우.
CONST - 매칭되는 행이 하나만 존재하기 때문에, 옵티마이저에 의해 상수로 인식될 수 있다.
PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값과 비교할 때 사용된다.
SELECT * FROM tbl_name WHERE primery_key=1;
EQ_REF - system과 const를 제외한 가장 좋은 조인의 타입이다.
PRIMARY KEY 혹은 UNIQUE NOT NULL 인덱스를 이용하며 조인을 수행하는 경우이다.
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
REF - eq_ref와 다른 점은 PRIMARY KEY 혹은 UNIQUE 인덱스가 아니라면 사용된다.
(위와같은 키값에 기반하였으나 단일 행을 select 할 수 없는 경우)
SELECT * FROM ref_table WHERE key_column=expr;
REF_OR_NULL - ref와 유사하지만 null 값을 가지는 행에 대해서도 검색을 한다.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
INDEX_MERGE - 인덱스 병합의 최적화가 사용된다.
UNIQUE_SUBQUERY - IN 서브쿼리에 대해서 ref를 대체한다.
value IN (SELECT primary_key FROM single_table WHERE some_expr)
INDEX_SUBQUERY - IN 서브쿼리를 대체하지만, non-unique 인덱스에 대해서도 동작을 한다.
value IN (SELECT key_column FROM single_table WHERE some_expr)
RANGE - 주어진 범위에 들어 있는 행만을 추출, 행 선택은 인덱스를 사용한다.
(=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN,IN을 사용하는 상수와 비교할 때 사용한다.)
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
INDEX - ALL과 동일하지만 인덱스 트리만을 스캔한다는 점이 차이이다. 단일 인덱스의 일부분인 컬럼만을 사용할 때 사용된다.
ALL - 풀스캔을 의미한다. 아주 느린 성능.
가장 좋은 SYSTEM 부터 가장 나쁜 ALL까지 순서대로 나열되어 있다.
* POSSIBLE_KEYS
해당 테이블에서 데이터를 찾기 위해 MySQL이 선택한 인덱스를 가리킨다.
이 중 어떤 것들은 테이블 순서를 만드는 과정에서는 사용되지 않을수도 있음을 의미한다.
* KEY
MySQL이 실제로 사용할 예정인 키(인덱스)를 가리킨다.
* KEY_LEN
MySQL이 사용하기로 결정한 키의 길이를 나타낸다.
* REF
테이블에서 행을 선택하기 위해 key 컬럼 안에 명명되어 있는 인덱스를 어떤 컬럼 혹은 상수와 비교하는지 보여준다.
* ROWS
쿼리를 실행하기 위해 조사해야 하는 행의 숫자
* EXTRA
쿼리에 관한 추가적인 정보
Distinct - 매칭되는 행을 찾게되면 검색을 중단한다.
Not exists - LEFT JOIN을 수행시 매치되는 행을 찾으면 검색을 중단한다.
range checked for each record - 사용할 인덱스는 찾지 못했으나, 이전 테이블에서 찾은 컬럼으로 range 또는 index_merge 접근방식이
가능한지 검사한다. 그리 빠른 방법은 아니지만 인덱스를 전혀사용하지 않는것보다는 빠르다.
Using filesort - 정렬은 조인타입과 정렬 키 및 where 구문과 매치가 되는 모든 행에 대한 행 포인터를 사용해서 모든 행에 걸쳐 진행된다.
후에 그 키는 저장이되고 행은 저장 순서에 따라서 추출된다.
Using index - 인덱스 트리의 정보만 가지고 컬럼정보를 추출한다.
Using temporary - 쿼리를 해석하기 위해, 결과를 저장할 임시 테이블을 생성한다.(group by 혹은 order by 사용시)
Using where - where 구문은 다음 테이블에 대한 행 매치 또는 클라이언트에 보내지는 행을 제한하기 위해 사용된다.
Using sort_union, Using union, Using intersect - 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합되는지 표현한다.
Using index for group-by - 실제 테이블을 추가적으로 검색하지 않고서도, group by 또는 distinct 쿼리의 모든 컬럼을
추출하기위해 사용될 수 있는 인덱스를 찾았음을 가리킨다.
