정리중-5

우리가 관계형DB를 사용하면서 가장 중요한 것은 옵티마이저(SQL Optimizer)가 쿼리를 어떻게 실행하는지이다.

 

물론 우리가 작성한 SQL문을 실행계획으로 만드는건 옵티마이저의 역할이지만, 우리는 최적의 실행 계획이 사용 될 수 있도록 옵티마이저에게 힌트를 줄 수 있는 쿼리를 작성해야 한다.

 

📌 MySQL 엔진에서 쿼리가 실행되는 과정

1. 사용자로 부터 요청된 SQL 문장을 잘게 쪼개 문법을 확인하고, Parse 트리로 만든다.
2. 만들어진 Parse 트리를 확인하며 어떤 테이블부터 읽고, 어떤 인덱스를 이용할지 선택한다.
   + 불필요한 조건 제거 및 연산을 단순화 한다.
   + 여러 테이블 조인을 하는 경우, 효율적인 순서를 결정한다.
   + 인덱스를 결정한다. 각 테이블에서 사용된 조건과 통계 정보를 사용한다.
   + 가져온 레코드들을 임시 테이블에 넣은 경우, 다시 한번 가공해야하는지 결정한다.
3. 결정된 읽기순서, 선택된 인덱스를 이용해 Storage 엔진에서 가져온다. (Handler API 이용)
   
   

 

📌 최적화 한다는 것은

풀 테이블 스캔을 최소화하고 만들어둔 인덱스를 이용해 값을 한번에 찾도록 만들어서 성능을 끌어 올린다는 말이다.

 

다만 앞장에서 이야기했지만, 인덱스를 사용하는게 반드시 빠르다는 법은 없다. 조회를 제외한 삽입/삭제등은 인덱스에도 같이 반영해줘야 하기에 더 느려진다.

 

또한 조회라고 하더라도 아래와 같은 상황에서는 전체 테이블을 조회하는 경우 인덱스를 거치는게 더 느릴 수 있다.

• 테이블의 레코드 건수가 너무 작아서 그냥 풀 테이블 스캔을 하는게 더 빠른 경우 (테이블 페이지가 1개인 경우)
• Where 절이나 On 절에 이용할 만한게 없어서 인덱스를 만들 수 없는 경우
• 인덱스 레인지 스캔이 가능하나, 일치 레코드 수가 너무 많은 경우 (통계적으로 전체 테이블의 20% 이상)

 

📌 Optimizer의 동작방식

이전에는 대부분 규칙 기반(Rule-Base)의 옵티마이저 였지만, 최근에는 비용 기반(Cost-Base)의 옵티마이저가 사용된다.

즉 쿼리 대상의 레코드 수, 선택도 등의 통계정보를 바탕으로 실행계획을 생성한다.

규칙 기반 최적화는 DB에 내장된 규칙에 따라 실행계획을 만드는 것을 의미한다. 다만 DB 데이터 구조는 서비스마다 크게 다르기 때문에 정말 오래된 DBMS가 아닌 이상 거의 사용하지 않는 방법이다.

 

다른 말로하면 통계 정보가 정확할 수록 좋은 실행 계획을 만들 확률이 높다는 이야기.

특정 쿼리의 실행계획은 EXPLAIN 키워드로 확인할 수 있다. 이는 통계정보를 기반으로 한 실행계획을 제공하는 것이다.

mysql> SELECT count(*) FROM t1 JOIN t2 ON (t1.c1 = t2.c2);
+----------+
| count(*) |
+----------+
|  1953125 |
+----------+

mysql> EXPLAIN format=tree --mysql 8.0부터는 옵션을 주면 Json이나 Tree 형태로 볼 수 있다.
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: 
  -> Inner hash join (t2.c2 = t1.c1)  (cost=976879.74 rows=976563)
  -> Table scan on t2  (cost=0.01 rows=3125)
  -> Hash
      -> Table scan on t1  (cost=314.75 rows=3125)

 

MySQL 엔진는 통계 정보는 인덱스를 통해 서비스중에도 계속 수집, 수정한다. 참고로 MySQL 8.0부터는 인덱스뿐만 아니라 일반 칼럼에도 통계 정보(Histogram)을 수집한다. 실제로 innodb_stats_sample_pages 설정값을 이용하면 통계 정보를 위해 분석할 인덱스 페이지 개수를 지정해 줄 수 있다.

 

당연히 분석할 페이지 수가 많으면 정보가 더 정확하겠지만, 수집 중에는 테이블 읽기/쓰기가 되지 않으므로 DB 성능에 큰 영향이 간다. 그래서 이 값은 많아도 16~24 페이지 정도만 사용하기를 권장한다.

 

참고로 MySQL 8.0.18은 새로운 기능으로 EXPLAIN ANALYZE를 제공한다. 해당 기능은 실제 수행했을 때( actual ) 소비되는 시간, 연산별 처리 건 수, 예상 실행비용등을 함께 보여준다.

mysql> EXPLAIN ANALYZE 
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: 
  -> Inner hash join (t2.c2 = t1.c1)  (cost=976879.74 rows=976563) (actual time=1.229..74.317 rows=1953125 loops=1)
  -> Table scan on t2  (cost=0.01 rows=3125) (actual time=0.006..2.386 rows=3125 loops=1)
  -> Hash
        -> Table scan on t1  (cost=314.75 rows=3125) (actual time=0.017..0.983 rows=3125 loops=1)

 

 

📌 실행계획 분석하기 (Explain)

EXPLAIN <쿼리> 를 입력하면 기본적으로 아래와 같은 실행계획 테이블을 제공해준다.

다만 굳이 외울려고 하기보다는, 자주 보다보면 어떤게 중요한 값인지 자연스럽게 알아가는걸 권장한다.
예를 들어 인덱스 사용방식을 나타내는 type과 부가정보를 나타내는 Extra는 중요하지만
후보 인덱스 키를 보여주는 possilbe_keys 같은 건 별 쓸모가 없기 때문에 다 외우는건 쓸모 없는 짓이다.

참고로 mysql 서버 콘솔에 그냥 EXPLAIN을 찍으면 아래와 같이 나온다.

UI만 다를 뿐, 콘솔에서 텍스트로 똑같은 실행계획 테이블을 제공해준다.

📌 id (select_id) 칼럼

SELECT 항목을 구분하는 번호, 여러 테이블을 조회하더라도 Join으로 연결되어 있다면 다른 동일한 ID가 부여된다.

📌 select_type 칼럼

쿼리가 어떤 타입인지 나타낸다. 쿼리 타입만 보더라도 대충 어떻게 개선해야 하는지 알 수 있다.
✔ SIMPLE : 서브쿼리나 Union을 사용하지 않은 단순 Select 쿼리를 의미한다. (Join도 단순 쿼리로 취급한다.)
✔ PRIMARY : 서브쿼리나 Union가 포함된 쿼리. 가장 바깥쪽 Select가 PRIMARY로 표기된다.
생각해보면 당연한거지만, 한 개의 쿼리 실행 계획에서 SIMPLE 과 PRIMARY 는 반드시 하나만 존재한다.
✔ SUBQUERY : From절 이외에 사용된 서브 쿼리를 의미한다.

✔ DERIVED : From절에 사용된 서브 쿼리를 의미한다. 메모리나 디스크에 임시 테이블을 만든다.
✔ UNION : Union을 사용한 경우, 두 번째 이후 쿼리는 UNION으로 표시된다. 첫번째 쿼리는 Union 결과를 대표하는 DERIVED 타입을 갖게 된다.

✔ DEPENDENT UNION : 내부 쿼리가 외부의 값을 참조할 때 DEPENDENT 키워드가 추가된다. Union으로 결합된 쿼리가 외부 쿼리에 의해 영향을 받는다는 의미.
✔ DEPENDENT SUBQUERY : From 절 이외에 사용된 서브쿼리가 바깥쪽 Select 쿼리(외부 값)을 사용하는 경우 
✔ DEPENDENT DERIVED : 기존에는 From 절의 서브 쿼리에 외부 칼럼을 사용할 수 없었으나, MySQL 8.0부터는 가능하다. 그래서 Lateral Join으로 From 절의 서브 쿼리가 외부 칼럼을 참조했을 때 해당 타입이 사용된다. 

✔ UNION RESULT : Union의 결과를 담는 임시테이블을 의미한다. 참고로 MySQL8.0 부터는 Union ALL을 사용할 때 임시 테이블을 생성하지 않는다.
✔ UNCACHEABLE SUBQUERY : SUBQUERY를 사용했는데 특정 조건 때문에 캐시를 이용할 수 없을 때 표기된다.
이는 사용자 변수나 UUID, RAND() 같은 값이 서브쿼리에 들어 갔음을 의미한다.
✔ MATERIALIZED : From 절이나 IN (subqurey) 에서 서브쿼리 최적화를 위해 사용된다.
보통 이런 경우 서브 쿼리는 재껴두고 외부 쿼리의 테이블을 먼저 읽어서 비효율적으로 실행되는데, 이를 최적화 시켜 서브 쿼리의 내용을 임시테이블로 구체화(Materialzed)한 후 외부 테이블과 조인하는 형태로 실행된다.
다만 결국 임시테이블을 추가로 사용하므로 보통 극적인 성능 향상은 기대하기 어렵다. 그냥 조금 더 좋은 정도

기본적으로 DEPENDENT는 외부 쿼리에 의존성이 있으므로 성능이 느리다.

또한 DERIVED 같이 임시 테이블을 사용하는 것들은 디스크에 임시테이블이 만들어질 위험이 있다. 그래서 Join으로 쿼리를 수정해 임시테이블을 사용하지 않도록 제거하는 것이 좋다.

 

 

📌 table 칼럼

보면 알겠지만 실행계획을 확인 할 때, SELECT 쿼리로 분류하는게 아니라 테이블 단위로 분류해서 결과가 나온다.
그래서 table 칼럼을 통해 어떤 테이블에 대한 정보인지를 나타낸다. 참고로 <>는 임시 테이블을 의미한다.
table 항목이 <DERIVED2>인 경우, ID:2를 수행한 후 진행된다는 의미.

 

 

📌 partitions 칼럼

파티셔닝(partitioning)하여 테이블을 관리할 때 어떤 파티션을 읽었는지 알려주는 정보이다. 
MYSQL 8.0부터는 기본으로 보이는데, 이전 버전에서는 EXPLAIN PARTITION 옵션을 줘야 볼 수 있다.

 

📌 type 칼럼

실행계획 튜닝에 핵심적인 칼럼이다. 실행 계획이 어떻게 인덱스를 참조했는지를 나타낸다.
참고로 앞의 숫자 (1~12)는 평균적인 인덱스 효율을 적어논 것. 1. ALL이 가장 느리고 12. system 가 가장 빠르다.

1. ALL : 풀 테이블 스캔이다. 인덱스에 값이 없어 B-TREE 의 끝까지 탐색하여 디스크 페이지를 읽어서 찾은 경우.
2. index : 가지고 있는 인덱스를 처음~끝까지 다 읽은 경우. 두가지 조건에서 발생한다
- 인덱스 스캔 (range, const, ref)가 불가능 + 인덱스에 포함된 칼럼만으로 처리 가능한 경우
- 인덱스 스캔 (range, cosnt, ref)가 불가능 + 인덱스만 이용해 정렬이나 그룹핑이 가능한 경우

3. index_merge : 2개 이상의 인덱스를 이용해 결과를 합치는 방식. 우선 순위가 range보다 높지만 효율적이진 않다.
4. range : 인덱스 레인지 스캔, 값이 아니라 인덱스 범위로 검색하는 방법이다. < > IS NULL BETWEEN LINK IN...
다만 통상적으로 인덱스를 탔다고 하면 range, const, ref를 묶어서 칭한다.

5. index_subquery : IN 연산자를 사용할 때 조건에 나오는 값이 유니크하지 않은 경우(=이상한 쿼리) 인덱스를 이용하여 중복을 제거하는 용도로 해당 인덱스가 사용된다.
6. unique_subqurey : where 조건에 In (subqurey) 형태가 있을 때 사용된다. 서브쿼리에서 유니크한 값만 가지고 있는 경우를 해당 인덱스가 사용된다.

7. ref_or_null : ref + IS NULL 의 형태. ref보다 느리다. 보통 실제 서비스의 실행 계획에서는 볼일이 거의 없다.
8. fulltext : 전문(full) 검색 인덱스를 사용해서 레코드에 접근하는 방법. 전문 검색할 칼럼에 인덱스가 있어야한다.
MATCH ... AGAINST .. 구문을 사용하는데 당장 사용할일은 없으니 일단 넘어가자.

9. ref : JOIN 순서에 상관없이 사용된다. PK나 유니크 인덱스등 제약도 상관없이 사용되며 동등 조건(equal)이 사용될 때 이용하는 인덱스 접근 방식이다. 상당히 빠르다.
10. eq_ref : 여러 테이블이 JOIN 되는 쿼리에만 발생한다. 조인에서 처음 읽을 테이블의 칼럼 값이 나중에 또 읽는 테이블의 PK, 유니크 인덱스 칼럼의 동등 조건에서 사용될 때 사용되는 방법이다. 
즉 ref에서 반드시 1건만 존재한다는 보장있는 경우 사용되는 타입이라고 이해하면 된다. 더 빠르다.

11. const : 유니크 인덱스 스캔, 쿼리에 PK/유니크 칼럼을 이용하는 where 조건이 있고, 그 결과가 1건일 때 사용한다. 단 실행 전 결과가 1개밖에 없음을 DBMS가 예측할 수 있는 경우에만 사용된다.

12. system: 테이블에 레코드가 1건 이하일 때 접근방법. MySQL의 InnoDB에서는 사용하지 않는 방법이다.

 

📌 possible_keys 칼럼

옵티마이저가 사용을 고려한 후보 인덱스 목록이다. 

 

📌 key 칼럼

실제 사용한 인덱스이다. (possible_keys중 하나) 인덱스를 사용하지 않았다면 null을 반환한다.

 

📌 key_len 칼럼

인덱스를 다중 칼럼으로 사용했을 때, 인덱스 중 몇 바이트까지 사용했는지 알려준다.
예를 들어 SELECT * FROM table WHERE no=2; 에서 key_len의 값이 4가 나왔다면 이는 Integer(=4바이트)한 개만 사용했음을 알 수 있다. 즉 다중칼럼 인덱스를 조회할 때 모든 값을 사용했는지 여부를 판단할 수 있다.

 

📌 ref 칼럼 (type - ref일 때)

type에서 ref (동등조건이 사용될 때 인덱스 접근방법, 1개임을 보장은 못함)가 사용됬다면 ref 칼럼이 추가로 제공된다.
이는 어떤 칼럼이 조건에 사용되었는지를 보여주며, 만약 func라고 표시된다면 가공된 칼럼이 사용됬음을 의미한다.

 

📌 row 칼럼

옵티마이저가 예측해본 사용할 레코드 수이다. 통계에 의한 값이라서 실제 레코드 수와 일치하지는 않는다.

까먹었을까봐 한번 더 보여주는 EXPLAIN (아무 쿼리) 실행계획 테이블

 

📌 Extra 칼럼

MySQL이 SQL문을 어떻게 실행계획으로 만들었는지에 대한 부가 정보이다. 여러 개가 동시에 나올 수 있다.
외우기에는 Extra 종류가 너무 많다. 다만 문구로 나오므로 대충 읽어보면 무슨 의미인지 추측 가능하다.

- const row not found : const 방식으로 읽었지만 레코드가 0개입니다.
- distinct : 필요한 것만 조인하고 중복된 값을 제거하였습니다.
- Full scan on Null key : where 조건에 null이 들어갈 수 있는 값을 사용했음. null 뜨면 풀 스캔으로 바꾸겠습니다.
- Impossible Having : having조건에 만족되는 레코드가 없습니다. 잘못된 쿼리입니다.
- Impossible Where : where 조건이 항상 False입니다. 잘못된 쿼리입니다.
- Impossible Where noticed after reading const table : 쿼리 실행해보니 Where이 항상 false입니다. 잘못된 쿼리.
- No matching min/max row : Where 조건에 일치하는 레코드가 없어서 min() max()가 불가능합니다.
- No matching row in const table : const 방식으로 접근할 때 일치하는 레코드가 없습니다.
- No tables used : from 절이 없습니다. (from dual으로 더미 테이블 조회한 경우)
- Not exist : 두 테이블 A, B에서 에서 한쪽에만 값이 존재합니다. ➡ 이 경우 2가지 조인을 사용한다.
1. Anti-Join : NOT IN (subqurey) 또는 NOT EXISTS 으로 현재 테이블에 없는 값을 조인하는 방법
2. Left Outer Join : 레코드가 많을 때 안티 조인 대신 레코드 존재 여부만 판단(NOT EXISTS)하도록 최적화.

SELECT * 
FROM dept_emp de LEFT JOIN departments d ON de.dept_no = d.dept_no 
WHERE d.dept_no IS NULL; --NOT EXISTS

- Range checked for each record (index map : N) : 두 테이블 조인시, Where 조건에 변수가 여러개 사용되어 계속 값이 바뀌는 경우. 레코드마다 [인덱스 사용/ 풀스캔]이 달라지는 쿼리가 발생했다는 의미.

- Scanned N databases : 데이터베이스의 메타정보를 조회한 경우 
- Select tables optimized away : MIN() MAX() 등이 인덱스를 이용해 단 1건의 조회로 찾을 수 있게 잘 최적화 된 경우.
- unique row not found : 두개의 테이블을 PK나 유니크 칼럼으로 아우터 조인할 때, 일치하는 레코드가 아예 없는 경우
- Using filesort : ORDER BY를 인덱스로 처리하지 못한 경우. MySQL에서 값을 Sort buffer로 복사해서 정렬했음을 경고
흔히 볼 수 있는 비효율적인 케이스이다. 정렬을 사용해서 SQL을 튜닝하자.

 

- Using index : 커버링 인덱스, 데이터 파일을 아예 안읽고 처리 가능한 경우. (* type 칼럼의 index는 풀스캔을 의미함.)
- Using index for group-by : 루즈 인덱스 스캔. Group By 처리를 위해 정렬된 인덱스를 사용하는 경우.
- Using Join Buffer : 조인 버퍼를 사용했음을 의미한다. 일반적으로 조인에 사용되는 칼럼은 인덱스를 생성한다.
두 테이블을 Join 했을 때, 먼저 읽은 테이블을 드라이빙(Driving), 나중에 읽은 테이블은 드리븐(Driven)이라 한다.
나중에 읽은 드리븐 테이블에 Join 칼럼 인덱스가 없다면 조인 버퍼를 만들어서 잠깐 저장해둔다. 이 경우 사용한다.
+유효한 조인조건을 적지 않아서, 모든 경우의 수 레코드가 만들어지는 Cartesian Product 조인도 조인버퍼를 사용한다.

 

- Using union, Using intersect, Using sort_union,  : 2개 이상의 다중컬럼 인덱스를 사용할 때 (type=index_merge) 결과가 어떻게 합쳐졌는지를 알려준다. 조건이 OR이라 합집합으로 추출했는지, AND라 교집합으로 추출했는지 알려준다.

단 sort_union은 합집합 추출과 같지만 연결된 레코드가 대량의 range를 필요하는 조건이라 한번에 처리할 수 없는 경우 PK만 먼저 읽어서 정렬 ➡ 병합하여 반환했음을 의미한다.

- Using temporary : 임시테이블을 사용되었음을 의미한다. 다만 사용했는데 Using temporary가 없는 경우도 종종있다.
참고로 임시테이블이 생성되는 경우는 대충 아래와 같다.
✔ FROM 절에 서브쿼리를 쓰면 무조건 임시테이블(Derived table)이 생성된다.
✔ "COUNT(DISTINCT column1)" 같은 쿼리도 인덱스를 사용할 수 없는 경우에 임시 테이블이 만들어진다.
✔ UNION, UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용한다.
✔ 인덱스를 사용하지 못하는 정렬 작업도 임시 테이블을 사용한다.

- Using where : MySQL엔진이 쿼리를 가공/필터링 한 경우 나타난다. 체크 조건은 MySQL 엔진에서 처리한다.

범위 조건은 스토리지 엔진에서 처리 후 레코드를 리턴하지만, 체크 조건은 Using where이 가능하다.

SELECT * FROM table WHERE man_no BETWEEN 0 AND 10 AND gender = 'M';
-- 범위조건 BETWEEN은 스토리지 엔진에 의해 10개의 레코드가 리턴되고 
-- 가져온 10개중에 체크 조건 gender='M' 인 것을 MySQL엔진에 의해 필터링되어 Using Where 등장

 

- zero limit

간혹 쿼리 데이터는 필요없고, 메타데이터만 필요한 경우가 있다.

이 때 Limit 0등을 사용하면 실행계획에 zero limit이 나오는데, 이는 아예 데이터 조회를 하지 않았음을 의미한다.

 

 

📌 Filtered 칼럼 (Extra - Using Where 알 때)

MySQL8.0에서는 기본으로 나오고, 이전 버전에서는 EXPLAIN EXTENDED를 사용해야 나온다.
Extra 칼럼에서 Using where이 뜬 경우 (= MySQL이 별도의 필터링, 가공을 한 경우)
얼마나 많은 레코드가 필터링 되었는지 나타낸다. 다만 이도 실제 결과가 아니라, 통계에 기반한 예측 값이다.