실행 계획 1

MySQL Engine 의 Optimizer는 사용자의 쿼리를 최적으로 처리할 수 있게 계획을 수립하지만,

매번 옵티마이저가 최적의 쿼리 실행 계획을 수립하는것은 아니다. 

 

때문에 Explain 명령어로 쿼리 사용자가 옵티마이저가 수립한 쿼리 실행 계획을 확인 할 수 있다. 

 

비용 기반 최적화에서 가장 중요한것은 통계 정보다. 

통계 정보란 만약 1억건의 데이터가 담겨져 있는 테이블이 존재하지만, 

1억건이란 데이터의 통계 정보가 갱신되지않아 옵티마이저가 10건 미만의 테이블로 인식한다면 

 

옵티마이저는 해당 테이블을 10건 미만의 테이블로 인식하고 실행 계획을 수립하게 된다.

( 이럴 경우 인덱스를 활용하지않고 테이블 풀스캔으로 성능 저하가 우려 된다.  부정확한 통계 정보로인해 0.1초만에 끝낼 쿼리가 1시간이 소요 될 수도 있게된다... )

 

MySQL 5.5 까지는 메모리에 통계 정보를 저장했기때문에 mysql 서버가 종료된다면 통계 정보 또한 같이 휘발됐다. 

5.6 버전부터 innodb_index_stat , innodb_table_stat 으로 각각에 테이블에 통계 정보를 디스크에 저장하기에 서버가 재시작되도 기존의 통계 정보를 유지할 수 있게 됬다.

 

히스토그램:  특정 컬럼값의 분포도

특정 범위의 데이터가 많고 적음을 히스토그램을 통해 확인이 가능하다. 

이는 조인을 통한 조회시 큰 영항을 끼친다

예를 들어...

1억건의 데이터를 가진 A 테이블과 100건의 데이터를 가진 B테이블이 존재할 때 

A 와 B를 조인해 어떠한 데이터를 조회한다 치자 

 

조인의 순서가 A 를 조회한 뒤 조인 조건에맞는 B 를 조회해 조인 진행을 하는 케이스와 

데이터 양이 적은 B를 먼저 조회한 뒤 조인 조건에 맞는 A 를 조회했을 케이스는 큰 조회 속도 차이가 난다. 

( 당연히 후자의 경우가 조회 속도가 빠르다. )

 

이렇게 옵티마이저는 히스토그램의 데이터 분포도를 참고해 조인의 순서를 조정한다. 빠르게 조회할 수 있도록

 

실행 계획 확인

MySQL 의 실행 계획은 DESC 또는 EXPLAIN 으로 확인할 수 있다. 

 

EXPLAIN에 옵션을주어 Tree, JSON 포맷으로도 확인이 가능하다.

 

EXPLAIN ANALYZE 로 단계별 소요된 시간 정보를 확인할 수 있다.

이 경우 포맷은 Tree로 고정된다.

 

Tree 포맷에서 들여쓰기는 호출 순서를 의미한다. 

• 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
• 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

1. Table scan on c  (cost=0.45 rows=2) (actual time=0.0564..0.0648 rows=2 loops=1)
2. Filter: ((c.content = 'hello') and (c.post_id is not null))  (cost=0.45 rows=1) (actual time=0.0655..0.0725 rows=1 loops=1)
3. Single-row index lookup on p using PRIMARY (id=c.post_id)  (cost=0.35 rows=1) (actual time=0.0437..0.0438 rows=1 loops=1)
4. Nested loop inner join  (cost=0.8 rows=1) (actual time=0.112..0.119 rows=1 loops=1)

 

actual time=0.0564..0.0648 : 첫번째 시간은 첫번째 레코드를 가져오는데 걸린 평균 시간, 두번째 숫자는 마지막 레코드를 가져오는데 걸린 평균시간

rows=2: 테이블에서 읽은 평균 레코드 건수 

loops=1: 조건에 일치하는 레코드를 찾는 작업이 반복된 횟수