Mysql - 실행 계획

MySQL 서버의 통계 정보

---

MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로 관리할 수 있도록 개선되었습니다. MySQL 5.5 버전까지는 각 테이블의 통계 정보가 메모리에만 관리되고, SHOW INDEX 명령으로만 테이블의 인덱스 컬럼의 분포도를 볼 수 있었습니다. 이처럼 통계 정보가 메모리에 관리될 경우 MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라집니다. MySQL 5.6 버전부터는 각 테이블의 통계 정보를 mysql 테이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있도록 개선되었습니다. 이렇게 통계 정보를 테이블에 관리함으로써 MySQL 서버가 재시작되어도 기존의 통계 정보를 유지할 수 있습니다.

 

히스토그램

---

MySQL 5.7 버전까지의 통계 정보는 단순히 인덱스된 컬럼의 유니크한 값의 개수 정도만 가지고 있었는데, 이는 옵티마이저가 최적의 실행 계획을 수립하기에는 부족함이 많았습니다. 그래서 옵티마이저는 이러한 부족함을 채우기 위해 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용했습니다. 8.0 버전으로 업그레이드되면서 MySQL 서버도 드디어 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게되었습니다.

 

히스토그램 정보 수집 및 삭제

• MySQL 8.0 버전에서 히스토그램 정보는 컬럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE... UPDATE HISTOGRAM 명령으로 실행해 수동으로 수집 및 관리됩니다. 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드합니다.

히스토그램 타입

💡 싱글톤 히스토그램

• 컬럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불립니다.
• 히스토그램의 버킷단위에서 컬럼이 가지는 값별로 버킷이 할당됩니다.

💡 높이 균형 히스토그램

• 컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로 Height-Balanced 히스토그램이라고도 불립니다.
• 히스토그램의 버킷단위에서 개수가 균등한 컬럼값의 범위별로 하나의 버킷이 할당됩니다.

 

실행 계획 분석

---

출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥(Outer) 부분이거나 먼저 접근해야하는 테이블입니다. 아래쪽에 출력된 결과일수록 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당합니다.

 

mysql> EXPLAIN 
       SELECT * FROM USER AS U 
       INNER JOIN COMPANY AS C ON U.IDX = C.USER_IDX 
       WHERE U.IDX = 47;

 

id 컬럼

• id 컬럼은 조인하는 경우는 같은 숫자가 출력이 되며 네스티드 쿼리를 사용하는 경우에는 각 다른 id값이 부여됩니다.
• 한가지 주의할 점은 실행 계획의 id 컬럼이 테이블의 접근 순서를 의미하지는 않습니다.

# 조인을 이용한 쿼리 id값 동일 => 1
mysql> EXPLAIN 
       SELECT * FROM USER AS U 
       INNER JOIN COMPANY AS C ON U.IDX = C.USER_IDX 
       WHERE U.IDX = 47;
       
# 네스티드 쿼리 id값 => 1, 2, 3
mysql> EXPLAIN SELECT 
        (SELECT COUNT(*) FROM COMPANY WHERE USER_IDX = 1), 
        (SELECT COUNT(*) FROM BOARD WHERE USER_IDX = 1) 
        FROM USER;

 

select_tpye 컬럼

• 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼입니다. select_type 컬럼에 표시될 수 있는 값은 다음과 같습니다.

💡 SIMPLE

• UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type은 SIMPLE로 표현됩니다.
• 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 하나만 존재하며, 일반적으로 가장 바깥 SELECT 쿼리가 해당됩니다.

💡 PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 PRIMARY로 표현됩니다.

💡 UNION

• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표현됩니다.
• UNION의 첫번째 단위 SELECT는 select_type이 UNION이 아니라 UNION되는 쿼리 결과들을 모아서 저장되는 임시 테이블로 표현됩니다.

EXPLAIN SELECT * FROM (
		(SELECT IDX FROM COMPANY) UNION ALL 
        (SELECT IDX FROM BOARD) UNION ALL 
        (SELECT IDX FROM USER) 
		) x;

 

💡 UNION RESULT

• UNION RESULT는 UNION 결과를 담아두는 테이블을 의미합니다. MySQL 8.0 이전 버전에서는 UNION ALL이나 UNION(또는 UNION DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했는데 MySQL 8.0 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선되었습니다. 하지만 UNION 또는 UNION DISTINCT는 MySQL 8.0 버전에서도 여전히 임시 테이블에 결과를 버퍼링합니다.

EXPLAIN SELECT IDX FROM COMPANY
		UNION DISTINCT
		SELECT IDX FROM USER

 

💡 SUBQUERY

• 중첩된 쿼리(Nested Query) : SELECT되는 컬럼에 사용된 서브쿼리를 네스티드 쿼리라고 합니다.
• 서브쿼리(SubQuery) : WHERE 절에 사용된 경우에는 일반적으로 그냥 서브쿼리하고 합니다.
• 파생 테이블 : FROM 절에 사용된 서브쿼리를 MySQL 에서는 파생 테이블이라고 하며, 일반적으로는 인라인 뷰라고 합니다.
• 스칼라 서브쿼리 : 레코드 1건만 반환하는 쿼리
• 로우 서브쿼리 : 컬럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리

 

💡 DEPENDENT SUBQUERY

• 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우 표시됩니다.
• 안쪽의 서브쿼리 결과가 바깥쪽 SELECT 컬럼에 의존적이기 때문에 DEPENDENT라는 키워드가 붙습니다. 또한 DEPENDENT UNION과 같이 DEENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다는 속도가 느릴때가 많습니다.

EXPLAIN SELECT U.NAME,
		(SELECT COUNT(*) FROM COMPANY C WHERE C.USER_IDX = U.IDX)
		FROM USER U
		WHERE U.NAME = "홍길동"

 

💡 DERIVED

• DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미합니다.
• select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 합니다. 또한 MySQL 5.5 버전까지는 파생 테이블에 인덱스가 전ㄴ혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많습니다. 그러므로 MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화되었습니다.
• MySQL 서버에서 인라인 뷰 형태의 서브 쿼리는 임시 테이블로 만들어서 처리됩니다. 그렇기 때문에 최대한 조인으로 푸는게 성능상 유리합니다.

 

💡 DEPENDENT DERIVED

• MySQL 8.0 이전 버전에서는 FROM 절의 서브쿼리는 외부 컬럼을 사용할 수 없었는데 MySQL 8.0 버전부터는 레터럴 조인 기능이 추가되면서 FROM절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게되었습니다.

EXPLAIN SELECT * FROM USER U
		LEFT JOIN LATERAL
		(SELECT * FROM COMPANY C 
		 WHERE C.USER_IDX = U.IDX ORDER BY C.IDX DESC LIMIT 2
		) AS C2 ON C2.USER_IDX = U.IDX

 

type 컬럼

💡 const

• 테이블 레코드 건수와 상관없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 합니다.
• WHERE 조건에 들어가는 키가 만약 복합키인 경우에는 const가 아닌 ref로 표시됩니다.

EXPLAIN SELECT * FROM USER WHERE IDX = 47;

 

💡 eq_ref

• eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시됩니다. 조인에서 처음 읽는 테이블의 컬럼값을 그 다음 읽어야할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 떼를 가리켜 eq_ref라고 합니다. 이때 두번째 이후에 읽는 테이블의 type 컬럼에 eq_ref가 표시됩니다.
• 또한 두 번째 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NN이어야 하며, 복합키로 만들어진 프라이머리 키나 유니크 인덱스라면 모든 컬럼이 비교 조건에 사용돼야만 eq_ref 접근 방법이 사용될 수 있습니다. 즉 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법입니다.

 

💡 ref

• ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키등의 제약 조건도 없습니다.
• 인덱스의 종류와 관계없이 동등 조건으로 검색할 때는 ref 접근 방법이 사용됩니다.

✔️ 정리

• const - 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 컬럼에 대해 동등 조건으로 검색시 반드시 1건의 레코드만 반환
• eq_ref - 조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등 조건으로 검색시 두번째 테이블은 반드시 1건의 레코드만 반환
• ref - 조인의 순서와 인덱스의 종류에 관계없이 동등 조건으로 검색시 1건의 레코드만 반환된다는 보장이 없어도 됨

 

💡 range

• 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법입니다.
• 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색하는 경우 사용됩니다.

EXPLAIN SELECT * FROM USER AS U
        WHERE IDX IN (3, 4, 5, 6)

 

💡 index_merge

• 지금까지 설명한 다른 접근 방법과는 달리 index_merge 접근 방법은 2개 이상의 인덱스를 이영해 각각의 검색 결과를 만들어낸 후 그 결과를 병합해서 처리하는 방식입니다.
• 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어집니다.
• 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않습니다.
• index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요합니다.

 

💡 index

• index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미합니다. range 접근 방법과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것을 의미하는 것은 아니라는 점에 주의해야 합니다.
• index 접근 방법은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교 하는 건수는 같습니다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적으라 할 수 있습니다.

✔️  index를 사용할 수 있는 경우(1, 2 조건을 충족하거나 1, 3번째 조건을 충족하는 경우)

• range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
• 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(즉 데이터 파일을 읽지 않아도 되는 경우)
• 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우(즉 별도의 정렬 작업을 피할 수 없는 경우)

EXPLAIN SELECT * FROM USER ORDER BY IDX DESC LIMIT 5;

 

💡 ALL

• 우리가 흔히 알고 있는 풀 테이블 스캔을 의미합니다. 
• 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 경우 리드 어헤드작업이 발생합니다.

EXPLAIN SELECT * FROM USER

 

possible_keys 컬럼

• MySQL 옵티마이저는 쿼리를 처리하기 위해 여러가지 처리 방법을 고려하고 그 중에서 비용이 가장 낮을 것으로 예상되는 실행 계획을 선택해 쿼리를 실행합니다. possible_keys 컬럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐입니다. 즉 사용될 뻔한 인덱스의 목록입니다.

key 컬럼

• key 컬럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미합니다. 그러므로 쿼리를 튜닝할 때는 key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요합니다.
• key 컬럼에 표시되는 값이 PRIMAARY인 경우에는 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름입니다.

key_len 컬럼

• 실행 계획의 key_len 컬럼의 값은 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 우리에게 알려줍니다. 더 정확히는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값입니다.

ref 컬럼

• 접근 방법이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여줍니다. 상수값을 지정했다면 ref 컬럼에는 const로 표시되고, 다른 테이블의 컬럼값이면 그 테이블명과 컬럼명이 표시됩니다.

EXPLAIN SELECT * FROM USER U
		INNER JOIN COMPANY C ON C.USER_IDX = U.IDX

• ref 컬럼의 값이 func라고 표시가 될 때가 있습니다. 이는 펑션의 줄임말로 참조용으로 사용되는 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미합니다.

EXPLAIN SELECT * FROM USER U
		INNER JOIN COMPANY C ON (C.USER_IDX - 1) = U.IDX

 

rows 컬럼

• 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여줍니다. 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값이여서 정확하지는 않습니다.
• 또한 rows 컬럼에 표시되는 값은 반환되는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미합니다.