MySQL 전체 구조는 위처럼 되어 있는데, 대략적으로 MySQL 엔진, 스토리지 엔진, 디스크 파일 크게 이러한 구조로 되어 있다는 것을 보면 좋을 것 같습니다.
MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있습니다.
EXPLAIN SELECT * FROM Test WHERE id > 100 ORDER BY id desc;MySQL 8.0.18 버전부터는 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가되었습니다.
EXPLAIN SELECT * FROM Test WHERE id > 100 ORDER BY id desc;
저의 사이드 프로젝트 DB에서 임의의 쿼리로 EXPLAIN 확인해보면 위와 같이 나오는데요. 하나씩 어떤 의미를 가지고 있는지 확인 해보겠습니다.
쿼리에서 서브 쿼리를 사용할 수도 있고 여러 개의 SELECT가 존재할 수 있기 때문에 id 컬럼은 SELECT 쿼리 별로 부여하는 식별자 값입니다.
각 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼입니다.
- UNION 이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 SELECT TYPE은 SIMPLE로 표시됩니다.
- UNION 이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리
DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미합니다.- 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 컬럼의 값이 DERIVED인 것이 있는지 확인해야 한다.
서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장합니다.
table 컬럼에 <derived N> 또는 <union M,N>과 같이 <>로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미합니다.
| id | select_type | table | -- |
|---|---|---|---|
| 1 | PRIMARY | derived 2 | |
| 1 | PRIMARY | e | |
| 2 | DERIVED | dept_emp |
- 첫 번째 라인의 테이블이
<derived2>라는 것으로 보아 이 라인보다 id 값이 2인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비되어야 한다는 것을 알 수 있다. - 세 번째 라인(id 값이 2인 라인)을 보면 select_type 컬럼의 값이
DERVIED로 표시되어 있다. 즉, 이 라인은 table 컬럼에 표시된 dept_emp 테이블을 읽어서 파생 테이블을 생성하는 것을 알 수 있다. - 첫 번째 라인과 두 번째 조인은 같은 id 값을 가지고 있기 때문에 조인되는 쿼리이다.
<derived2>테이블이 e 테이블보다 먼저 윗라인에 표시 되었기 때문에<derived2>테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.
테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며 반드시 1건을 반환하는 쿼리의 처리 방식을 const 라고 합니다.
- eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다고 합니다.
- 조인에서 처음 읽은 테이블의 컬럼 값을, 그 다음 읽어야 할 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때를 가리켜 eq_ref 라고 한다.
인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방식이 사용됩니다.
const: 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 컬럼에 대해 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)eq_req: 조인에서 첫 번째 읽은 테이블의 컬럼 값으로 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동급 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)ref: 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)
세 가지 모두 매우 좋은 접근 방법으로 인덱스의 분포도가 나쁘지 않다면 성능상의 문제를 일으키지 않는 접근 방법입니다.
- range는 인덱스 레인지 스캔 형태의 접근 방법입니다.
- range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능의 성능이 보장된다고 볼 수 있습니다.
- 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미합니다.
- index 접근 방법은 테이블을 처음부터 끝가지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같지만, 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적입니다.
- 테이블 풀 스캔하는 것을 의미합니다.
- 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환합니다.
- 풀 테이블 스캔은 지금까지 정리한 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법입니다.
- InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페에지를 읽어들이는 기능을 제공합니다. InnoDB에서는 이 기능을 위해
리드 어헤드(Read Ahead)라고 하며, 한 번에 여러 페이지를 읽어서 처리할 수 있습니다.
MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 방법을 고려하고 그 중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해 쿼리를 실행합니다.
possible_keys 컬럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스 목록일 뿐입니다.
실행 계획을 확인할 때 possible_keys 컬럼은 특별한 경우를 제외하고는 그냥 무시해도 됩니다.
possible_keys 컬럼의 인덱스가 사용 후보였던 반면, key 컬럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미합니다.
- key가 PRIMARY 라면 프라이머리 키를 사용한다는 의미
쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개 컬럼까지 사용했는지를 의미합니다. 즉, 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값입니다.
실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줍니다. 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상 값이라서 정확하지는 않습니다.
옵티마이저는 각 테이블에서 일치하는 레코드 개수를 가능하면 정확히 파악해야 좀 더 효율적인 실행 계획을 판단할 수 있습니다.
filter 컬럼의 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미합니다.
만약 filter 컬럼의 값이 50% 라면 옵티마이저는 100건 중에서 50건은 버려지고 최종 남은 50건이 반활될 것으로 예측했다는 것을 의미합니다.
쿼리가 실행되면서 스토리지 엔진과 MySQL 엔진에서 레코드를 읽고 필터링 해서 레코드를 버리게 되는지 그림에서 확인할 수 있습니다.
order by를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 레코드를 다시 한번 정렬해야 합니다.
order by 처리가 인덱스를 사용하지 못할 때만 Using filesort가 표시 됩니다.
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 컬럼에 Using Index가 표시됩니다.
MySQL 옵티마이저가 인덱스 컨디션 푸시 다운 최적화를 사용할 때 나타나는 값입니다.
각 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할을 하며, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행합니다.
WHERE 절의 조건을 MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에서 Using Where 값이 나타납니다.