db

MySQL 옵티마이저 실행계획

키위먹고싶다 2023. 6. 24. 01:24

옵티마이저 개요

MySQL 서버로 요청된 쿼리의 결과가 동일해도 내부적으로 결과를 만들어내는 방법은 매우 다양하다. 여러 방법 중 어떤 방법이 최적이고 최소의 비용이 드는지 결정해야 하는데 옵티마이저는 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업을 한다. MySQL에서는 EXPLAIN이라는 명령어로 쿼리의 실행 계획을 확인할 수 있다.

쿼리 실행 절차

MySQL에서 쿼리가 실행되는 과정은 세 단계로 나뉜다.

  1. 사용자로부터 요청된 쿼리 문장을 잘개 쪼개서 서버가 이해할 수 있는 수준으로 분리한다.
    1. 해당 과정을 파싱이라고 하는데 SQL 파서라는 모듈로 처리되고, 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다. 분리된 문장을 파스 트리라고 한다.
  2. 이전 단계에서 생성된 파싱 정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 해당 단계는 옵티마이저가 처리한다. 이 단계가 이루어지면 쿼리의 실행계획이 만들어진다.
    1. 불필요한 조건 제거 및 복잡한 연산의 단순화
    2. 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
    3. 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
    4. 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
  3. 이전 단계에서 결정된 실행계획의 정보인 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
    1. 레코드를 조인하거나 정렬하는 작업이 수행된다.

풀 테이블 스캔 vs 풀 인덱스 스캔

풀 테이블 스캔을 사용할 경우는 다음과 같다.

  1. 테이블이 페이지 1개로 구성되는 경우와 같이 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 빠를 경우
  2. WHERE절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  3. 인덱스 스캔을 사용할 수 있더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(통계 정보 기준)

Read ahead

풀 테이블 스캔이나 풀 인덱스 스캔을 사용 할 경우 한꺼번에 여러 페이지를 읽어오는 기능을 가지고 있다. InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기전에 미리 디스크에서 읽어 InnoDB 버퍼 풀에 가져다 두는 것을 의미한다.

select count(*) from member;

해당 쿼리는 풀 테이블 스캔을 사용할 것 같지만 풀 인덱스 스캔을 사용할 가능성이 높다. 단순히 레코드 건수만 필요하기 때문에 테이블의 2 -3개의 컬럼으로 이루어진 인덱스를 선택하는것이 효율적이기 때문이다.

select * from member;

그러나 인덱스에 없는 컬럼의 건수가 필요할 경우 풀 테이블 스캔을 사용한다.

ORDERBY

레코드 한 두건을 가져오는 쿼리를 제외하고 대부분의 조회 쿼리는 정렬을 필수적으로 사용한다. 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

장점 단점

인덱스 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 빠르다. insert, update, delete 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 갯수가 늘어날 수록 버퍼풀을 위한 메모리가 더 많이 필요하다.
Filesort 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

항상 Filesort를 거쳐야하는 것은 아니지만 정렬기준이 너무 많아서 모든 인덱스를 생성하는 것이 불가능하거나, GROUPBY나 DISTINCT같은 처리 결과를 정렬해야 하거나, UNION같이 임시 테이블의 결과를 다시 정렬해야 하는 경우는 인덱스를 사용할 수 없다.

SORT BUFFER

정렬이 수행되기 위해 할당받아야 하는 메모리 공간을 의미한다.

만약 할당받은 메모리보다 정렬해야 할 레코드가 더 많다면 레코드를 여러 조각으로 나눠서 처리하는데 이때 임시저장을 위해 디스크를 사용한다. ‘소트버퍼에서 정렬수행 → 임시 디스크에 기록’ 과정을 반복하면서 병합하는데 이 병합 작업을 멀티 머지라고 한다. 머지 횟수도 누적해서 집계된다. 작업모두 디스크 읽기, 쓰기를 유발하고 레코드 건수가 많을 수록 당연히 작업의 횟수도 증가한다. 그렇다고 소트버퍼의 크기를 크게 설정한다고해서 성능적으로 더 좋아지는 것은 아니다.

마지막으로 소트버퍼는 글로벌메모리영역이 아니라 로컬 메모리 영역이다. 즉, 소트버퍼는 클라이언트가 공유해서 사용할 수 있는 영역이 아니다. 따라서 커넥션이 증가할 수록 소트 버퍼로 소비되는 메모리 공간이 증가한다. 따라서 메모리 부족 현상이 나타날 경우 서버가 강제종료 될 수 있다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱클패스, 투 패스 2가지 정렬 모드로 나뉜다.

select member_seq, first_name, last_name
from member
orderby first_name;
  1. 싱글 패스 정렬 방식
    1. 처음 레코드에서 select절에 필요한 3가지 컬럼을 모두 조회한다
    2. 소트버퍼에서 정렬을 실행한다.
    3. 임시파일에 머지한다.
    4. 결과를 사용자에게 반환한다.
  2. 투 패스 정렬 방식
    1. 기본키인 memberSeq와 obderBy절에 필요한 first_name을 조회한다.
    2. 소트버퍼에서 정렬을 실행한다.
    3. 임시 파일에 머지한다.
    4. 멀티 머지가 종료되면 나머지 last_name을 디스크에서 조회한다.
    5. 결과를 사용자에게 반환한다.

투 패스는 테이블을 두번 읽어야하기 때문에 불합리하지만, 싱글 패스는 더 많은 소트 공간이 필요하다. 그런데 레코드 크기가 시스템 변수값보다 크거나 BLOB이나 TEXT타입의 컬림이 SELECT 대상에 포함되면 싱글패스를 사용하지 못하고 투 패스를 사용해야 한다. 싱글패스는 레코드 크기나 건수가 작을 때 빠른성능을 보이며, 투 패스 정렬 방식은 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.

<aside> 💡 select 쿼리에서 꼭 필요한 컬럼만 조회하는게 성능적으로 좋은 이유는 정렬버퍼때문이기도 하지만 임시테이블이 필요한 쿼리에서도 영향을 미친다.

</aside>

정렬처리 방법

쿼리에서 ORDER BY가 사용되면 반드시 3가지 처리 방법 중 하나로 정렬된다. 아래로 갈 수록 처리 속도가 떨어지는데 옵티마이저가 정렬 처리를 위해 인덱스를 이용할 수 있는지 판단해서 인덱스를 사용할 수 있다면 Filesort는 사용하지 않고 순서대로 인덱스를 읽어서 처리한다. 그런데 인덱스를 사용할 수 없다면 WHERE조건에 일치하는 레코드를 검색해 정렬 버퍼(sort buffer)에 저장하면서 정렬을 처리한다.

  1. 인덱스를 사용하는 방법
    1. 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고, OBDER BY의 순서대로 생성된 인덱스가 있어야 한다.
    2. WHERE절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
    select *
from  member m, address a
where m.memberSeq = a.memberSeq ---> 두번째 조건 만족
and   m.memberSeq between 100020 and 100040
order by m.memberSeq; ---> 첫번째 조건 만족

중요한점은 ORDER BY가 없어도 정렬이 되는 이유는 MEMBER테이블의 프라이머리 키 값을 읽고
다음으로 ADDRESS 테이블을 조인했기 때문이다.
  2. 조인에서 드라이빙 테이블만 정렬
  3. select * from member m, address a where m.memberSeq = a.memberSeq and m.memberSeq between 100020 and 100040 order by m.lastName; 1. 인덱스를 이용해 m.memberSeq between 100020 and 100040 조건을 만족하는 레코드를 검색 2. 검색 결과를 lastName 컬럼으로 정렬을 수행(FileSort) 3. 정렬된 결과를 순서대로 읽으면서 address 테이블과 조인을 수행해 최종 결과를 가져옴.
  4. 조인 결과를 임시 테이블로 저장 후 정렬
  5. select * from member m, address a where m.memberSeq = a.memberSeq and m.memberSeq between 100020 and 100040 order by a.zipCode 임시 테이블을 사용하지 않았을 땐 orderBy가 이미 조인전에 정렬결과를 사용하면 됐는데 address테이블이 필요하기 때문에 조인 전에 정렬이 수행되어야 하므로 조인된 데이터를 가지고 정렬을 수행할 수 밖에 없다. 1. 인덱스를 이용해 m.memberSeq between 100020 and 100040 조건을 만족하는 레코드를 검색 2. address와 조인 3. 조인된 결과를 임시 테이블로 저장 4. 임시 테이블 정렬 5. 최종 결과를 가져옴.

GROUPBY

GROUPBY절에선 사용되는 HAVING절은 필터링을 수행한다. 따라서 GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다. GROUP BY절도 ORDER BY와 마찬가지로 인덱스를 사용하는 방법과 그렇지 않은 경우로 나뉜다.

  1. 인덱스 스캔을 사용하는 GROUP BY
  2. ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY컬럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다. 이땐 이미 정렬된 인덱스를 읽는 것이므로 쿼리 실행 시점에 추가적인 정렬작업이나 임시테이블은 필요 없지만 그룹 함수의 그룹값을 처리해야 할 때는 별도로 임시 테이블이 필요할 수 있다.
  3. 루스 인덱스 스캔을 이용하는 GROUP BY
    1. 루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다.
    EXPLAIN
select emp_no
from salaries
where from_date='1985-03-01'
group by emp_no;
    id table type key Extra
    1 salaries range primary Using where; Using index for group-by
    salaries테이블은 (emp_no, from_date)로 생성되어 있다고 가정하자. 해당 쿼리 문장은 where절때문에 인덱스 레인지 스캔할 수 없다. 그런데 실행계획을 살펴보면 type이 range고 group by절까지 인덱스를 사용했다는것을 알 수 있다.인덱스 레인지 스캔에서는 유니크한 값이 수가 많을 수록 성능이 좋아진다. 그러나 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다. 루스 인덱스 스캔으로 처리되는 쿼리는 별도의 임시 테이블이 필요하지 않다.
  4. //min()과 max()이외의 집합 함수가 사용됐기 때문애 루스 인덱스 스캔은 사용 불가. select col1, sum(col2) from tb_test group by col1; // group by에 사용된 컬럼이 인덱스 구성 컬럼의 왼쪽부터 일치하지 않기 때문에 사용 불가. select col1, col2 from tb_test group by col2, col3; // select절의 컬럼이 group by와 일치하지 않기 때문에 사용불가 select col1, col3 from tb_test group by col1, col2;
  5. (emp_no, from_date) 인덱스를 차례로 스캔하면서 from_date값이 일치하는지 찾는다. 루스 인덱스 스캔방식은 단일 테이블에 대해 수행되는 GROUP BY절에서만 사용할 수 있다.
  6. 임시 테이블을 사용하는 group by
    1. 인덱스를 전혀 사용하지 못할 때 사용하는 방식.
    explain
select e.last_name, avg(s.salary)
from employees e, salaries s
where s.emp_no = e.emp_no
group by e.last_name;

//Extra : Using temporary
    group by가 필요한 경우 내부적으로 group by 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 합수 연산을 수행한다. 그리고 조인의 결과를 한 건씩 가져와 임시 테이블에서 주복 체크를 하면서 insert update작업을 수행한다. 이땐 정렬 작업을 수행하지 않는다.
  7. 그러나 group by와 order by가 같이 사용되면 명시적으로 정렬 작업을 실행하는데 이땐 Extra컬럼에 Using filesort도 추가된다.

DISTINCT처리

DISTINCT는 MIN(), MAX()또는 COUNT()같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우 2가지로 처리된다.

  1. SELECT DISTINCT… 
    //두 쿼리의 실행 결과는 동일하다. 
SELECT DISTINCT emp_no from salaries;
SELECT emp_no from salaries group by emp_no;
    또 SELECT절에 사용된 DISTINCT는 조회되는 모든 컬럼에 영향을 미치기 때문에 주의해서 사용해야 한다. 아래 두 쿼리의 실행결과는 동일하다.
  2. SELECT DISTINCT(first_name), last_name FROM employess; SELECT DISTINCT first_name, last_name FROM employess;
  3. 단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우 GROUP BY와 동일한 방식으로 처리된다.
  4. 집합 함수와 함께 사용된 DISTINCT

COUNT(), MIN, MAX()같은 함수 내에서 DISTINCT를 사용할 수 있다.

EXPLAIN 
SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

id table type key rows Extra

1 e range primary 100 Using where; Using index
1 s ref primary 10 NULL

위 쿼리는 employess와 salaries를 조인한 결과에서 salary컬럼의 값만 저장하기 위해 임시 테이블을 만들어서 사용한다.

 

실행 계획 개요

DBMS는 많은 데이터를 안전하게 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회 할 수 있게 해주는 것이 주목적이다. 이러한 목적을 달성하려면 옵티마이저가 사용자의 쿼리를 최적으로 처리 될 수 있게 하는 쿼리의 실행 계획을 수립할 수 있어야 한다. EXPLAIN명령어로 옵티마이저가 수립한 실행계획을 확인할 수 있다.

통계정보와 히스토그램

테이블 컬럼의 값들이 실제 어떻게 분포돼 있는지에 대한 정보가 없으면 실행계획의 정확도가 떨어진다. 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보와 테이블 및 인덱스에 대한 통계정보등을 가지고 실행계획을 수립할 수 있다.

실행 계획 분석

  1. id 컬럼 
    EXPLAIN
select e.emp_no, e.first_name, s.from_date, s.salary
from employess e, salaries s
where e.emp_no = s.emp_no limit 10;
    id select_type table type key ref rows Extra
    1 SIMPLE e index ix_firstname null 300252 Using index
    1 SIMPLE s ref primary employees.e.emp_no 10 NULL
    하나의 select문장에서 조인되는 여러 테이블은 갯수만큼 실행 레코드가 생성되지만, 같은 id값이 부여된다.id select_type table type key ref rows Extra
    1 PRIMARY NULL NULL NULL NULL NULL No tables used
    3 SUBQUERY departments index ux_deptname NULL 9 Using index
    2 SUBQUERY employees index ix_hiredate NULL 300252 Using index
    반대로 쿼리 문장이 3개 단위 select쿼리로 구서오대 있으므로 각 레코드의 id가 다른 값을 지닌다.
  2. EXPLAIN select ((select count(*) from emplyoees) + (select count(*) from departments)) as total_count
  3. 하나의 SELECT 문장은 다시 1개 이상의 하위 SELECT문을 포함할 수 있다. 실행계획에서 가장 왼쪽에 표시되는 id컬럼은 단위 select로 부여되는 식별자 값이다.
  4. select_type컬럼
    1. SIMPLE : UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT인 경우. 조인이 있어도 단순 SELECT일 경우. 아무리 문장이 복잡하더라도 SIMPLE은 하나만 존재.
    2. PRIMARY : UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥족에 있는 단위. SIMPLE과 마찬가지로 PRIMARY는 하나만 존재.
    3. SUBQUERY : FROM절 이외에서 사용되는 서브쿼리.
    4. DERIVED :
    5. DEPENDENT SUBQUERY : 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우id select_type table type key rows Extra
      1 PRIMARY e ref ix_frstname 231 Using index
      2 DEPENDENT SUBQUERY de ref ix_empno_fromdate 1 Using index
      2 DEPENDENT SUBQUERY dm ref PRIMARY 2 Using index
    6. select e.first_name (select count(*) from dept_emp de, dept_manager dm where dm.dept_no = de.dept_no and de.emp_no = **e.emp_no**) as cnt from employees **e** where e.first_name = 'Matt';
  5. table 컬럼 : 테이블 이름에 별칭이 부여된 경우 별칭이 표시.
  6. type 컬럼 : 실행계획에서 type이후의 컬럼은 각 테이블 레코드를 어떤 방식으로 읽었는지 나타내는데 type은 각 테이블의 접근 방법이며 12가지 방법이 있다. 그 중 ‘All’을 제외한 모두 인덱스를 사용하는 접근 방법이다.
    1. system : MyISAM, MEMORY엔진에서 레코드가 한건만, 또는 빈 테이블일때 접근 방식. InnoDB일 경우 All이나 index로 표시될 가능성이 큼.
    2. const : 테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 where 조건절을 가지며 반드시 1건을 반환하는 쿼리 처리 방식. 유니크 인덱스 스캔이라고도 함.
    3. ref : 1건을 반환한다는 보장이 없음. 유니크키가 두개 이상의 컬럼을 사용하고 그 중 일부만 where절에 명시 될 때.
    4. select * from member where member_seq = 3; ---> type const primaryKey : member_seq select * from dept_emp where dept_no = 'a001'; ---> type ref primaryKey : (dept_no, emp_no)
    5. all : 풀테이블 스캔.
  7. possible_keys 컬럼 : 인덱스가 실행계획을 위해 사용될뻔한 인덱스 목록. 사용된것은 아님.
  8. key 컬럼 : 최종으로 선택된 실행계획에서 사용된 인덱스를 나타낸다.
  9. key_len컬럼 : 다중 컬럼으로 만들어진 인덱스에서 몇개의 컬럼까지 사용됐는지 알려준다. 만약 CHAR(4)라면 4 * 4 16바이트, INTEGER(5) 라면 4 * 5 20바이트로 표시된다.
  10. ROWS 컬럼 : 실행계획의 효출성 판단을 위해 예측했던 레코드 건수를 보여주며 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출한 예상값이라서 실제 결과와 다를 수 있다. 해당 컬럼의 값은 구체적으로 옵티마이저가 이 쿼리를 처리하기 위해 얼마만큼의 값을 읽어야하는지를 예상하는 값. 만약 이 값이 테이블 전체 레코드 수와 비슷하다면 인덱스가 있더라도 풀테이블 스캔을 사용할 것이며, 적다면 인덱스 스캔을 사용할 것이다.
저작자표시