MySQL의 동작 방식

MySQL의 주요 동작 방식

MySQL이 일련의 쿼리를 수행할 때 동작하는 방식에 대해서 이해해본다.

세세한 부분까지 다 파악한다기보다 일반적인 쿼리 사용자 레벨에서 주의를 기울여야 하는 부분만 알아본다.

풀 스캔

인덱스를 타지 않고 스토리지 엔진이 처음부터 끝까지 전부 다 읽어서 요청된 작업을 처리하는 방법이다.

MySQL은 언제 풀 테이블 스캔 방식을 실행계획으로 선택하는가?

• 테이블의 레코드 건 수가 너무 적어서 인덱스를 통해 읽는 것 보다 풀 테이블 스캔이 빠를 것으로 예상되는 경우
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 경우더라도 조건에 일치하는 레코드 건수가 너무 많을 것으로 예상되는 경우

ORDER BY 처리(Using filesort)

레코드 여러 건을 가져오는 경우 대부분 정렬(ORDER BY절)이 들어가기 마련이다.

레코드를 정렬하기 위해서 생성된 인덱스를 사용하는 방법과 MySQL 서버에서 정렬하는 방법(filesort) 두 가지로 나뉜다.

• 인덱스를 사용하는 방법
  • 인덱스를 사용해서 정렬이 가능한 쿼리여야 하는 문제가 있지만 인덱스가 생성되어있다는 것은 자동으로 인덱스 컬럼을 기준으로 정렬이 되어있기에 별도의 정렬을 위한 처리가 필요 없어서 성능적으로 좋다.
• MySQL 서버에서 정렬하는 방법(filesort)
  • 레코드를 읽어온 후 MySQL 서버에서 정렬을 하는데 이때 사용하는 메모리 공간(소트 버퍼)을 이용하여 정렬을 한다.
    • 소트 버퍼의 크기는 가변적으로 증가하고 최대 사이즈는 시스템 변수(sort_buffer_size)로 설정 가능하다.
  • 문제는 이 소트 버퍼의 크기보다 더 큰 레코드 결과를 정렬해야할 때다. 이 때는 부분적으로 나눠서 소트 버퍼에서 정렬을 수행하는데 잠시 결과를 저장해야 할 테이블로 임시 테이블이 필요하게 된다.
    • 임시 테이블은 메모리에 생성될 수도 있고 디스크에 생성될 수 있는데 디스크에 생성되었을 때는 정렬을 위해 메모리에 비해 성능이 훨씬 떨어지는 디스크 I/O가 빈번하게 일어나게되어 오래 걸린다는 얘기다.

쿼리 최적화를 위해서 모든 쿼리의 정렬을 인덱스를 사용하는 방법으로 유도하면 좋을 것 같지만 인생이 그렇게 내 뜻대로 되지는 않는다.

다시말해 그렇게 튜닝이 불가능한 경우도 있다. 그 유형은 아래와 같다.

• 정렬 기준이 너무 다양하여 정렬 기준 별로 인덱스를 모두 생성하는 것이 불가능한 경우
• GROUP BY의 결과 또는 DISTINCT처럼 처리의 결과를 정렬해야 하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야하는 경우
• 랜덤하게 결과 레코드를 가져와야 하는 경우

---

정렬 알고리즘

• 싱글 패스(single pass) 알고리즘
  • SELECT 되는 모든 컬럼을 소트 버퍼에 담아서 정렬을 수행하는 방법(5.0버전 이후에 도입된 방법)
• 투 패스(two pass) 알고리즘
  • 정렬 대상 컬럼과 프라이머리 키 컬럼만 소트 버퍼에 담아서 정렬을 수행한 후, 정렬 순서대로 프라이머리 키를 통해 레코드를 읽어오는 방법
  • 예전 방법이지만 모든 컬럼을 소트 버퍼에 담기 어려운 특정 조건에서는 이 정렬 방법을 사용하기도 한다.
    • 특정 조건 1 : 레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때
    • 특정 조건 2 : BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

투 패스 알고리즘이 더 효율적인 경우는 레코드의 건 수가 상.당.히 많을 경우에 효율적이고 정렬 레코드의 크기나 건수가 작은 경우에는 싱글 패스 알고리즘이 빠르다.

🔸 SELECT 쿼리에서 모든 컬럼(*)으로 조회하는 방식으로 개발하는 경우가 많은데 filesort가 예상된다면 필요한 컬럼만 가져오는 게 무조건 이득이다. 그 많은 컬럼을 다 소트 버퍼로 가져온다고 생각해보면 비효율적이라는 것을 알 수 있다.

쿼리에 ORDER BY가 들어가면 반드시 아래 3가지의 처리 방법 중 하나로 정렬이 처리된다.

1. 인덱스를 사용한 정렬
   • ORDER BY 컬럼 순서대로 인덱스가 반드시 생성되어 있어야 한다.
   • JOIN이 있는 경우 제일 먼저 읽는 테이블 즉, 드라이빙 테이블에 JOIN 컬럼이 있어야 한다.
2. 드라이빙 테이블만 정렬(+조인이 없는 경우) = Extra : Using filesort
   • JOIN을 하는 경우 대부분 레코드가 몇 배로 뻥튀기된다. 그래서 드라이빙 테이블을 먼저 정렬하고 JOIN 하는 것이 인덱스를 사용할 수 없는 경우에 그나마 최선이다.
3. 조인 결과를 임시 테이블로 저장한 후 임시 테이블에서 정렬 = Extra : Using temporary; Using filesort

GROUP BY 처리

❌ GROUP BY절에는 추가로 HAVING을 쓸 수 있는데 GROUP BY로 나온 결과에 대해 필터링하기에 튜닝할 방법이 없다고 보는 게 맞고 GROUP BY가 잘 튜닝되어 결과를 만들기를 기대해야 한다.

GROUP BY는 기본적으로 인덱스를 사용할 수 있을 때와 사용할 수 없을 때로 구분할 수 있다.

• 일반적인 인덱스 스캔을 이용하는 경우
  • JOIN시 드라이빙 테이블에 속한 컬럼의 인덱스가 있다면 인덱스 스캔을 이용하며, 레코드를 조회할 때는 인덱스를 쓰지만 SUM() 같은 집계 함수를 쓸 때는 임시 테이블을 사용하기도 한다.
• 루스 인덱스 스캔을 이용하는 경우
  • 루스 인덱스 스캔은 인덱스를 사용하긴 하지만 필요한 부분만 띄엄띄엄 읽어오는 것을 말한다.
  • 루스 인덱스 스캔은 '단일' 테이블에서만 가능하다.
• 인덱스 사용 불가한 경우 (=임시 테이블을 사용하는 경우)
  • ORDER BY에서 "Using filesort"로 본 것과 같이 임시테이블 사용하여 처리되어 "Using temporary" 메시지가 Extra 컬럼에 표기된다.
  • 8.0 이전 버전에는 GROUP BY 컬럼을 기준으로 자동으로 ORDER BY 정렬도 했었다. 그러나 8.0부터는 암묵적으로 수행하던 정렬이 제거되었다.
    • 8.0이전 버전에서 수행되던 암묵적인 정렬을 제거하는 방법으로 ORDER BY NULL을 붙이는 방법이 있다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하기 위해서 SELECT 쿼리에 DISTINCT 키워드를 붙이기도 한다.

DISTINCT는 MIN(), MAX() 등의 집계 함수와 함께 사용되는 경우와 아닌 경우로 나눠서 볼 수 있다.

집계 함수와 DISTINCT 키워드가 함께 사용되는 경우에 DISTINCT가 인덱스를 타지 못하는 경우 항상 임시 테이블이 필요하다.

그러나 이 경우에 Extra 컬럼에 "Using temporary" 메시지가 출력되지 않는다.

➡️ 집계 함수 없이 DISTINCT 가 사용된 경우

GROUP BY와 거의 같은 방식으로 처리된다.

다만 DISTINCT의 경우 정렬이 보장되지 않는다.

DISTINCT는 하나의 컬럼에만 적용되는게 아니라 모든 컬럼에 대해서 적용된다.

➡️ 집계 함수와 같이 DISTINCT 가 사용된 경우

SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
    AND e.emp_no BETWEEN 10001 AND 10010;

집계 함수와 함께 쓰인 DISTINCT는 집계 함수 인자로 전달한 컬럼에만 적용된다.

이것을 처리하기 위해서 내부적으로 임시 테이블을 사용한다.

임시 테이블

MySQL 엔진이 사용하는 임시 테이블은 처음에 메모리에 생성되었다가 크기가 커지면 디스크로 옮겨지고, 특정 예외 케이스에서는 디스크에 바로 만들어지기도 한다.

결론부터 말하면 임시 테이블을 사용하면 대부분의 경우 성능이 안 좋다고 보면 된다.

• 임시 테이블이 필요한 쿼리
  • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
  • ORDER BY와 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 컬럼이 UNION RESULT인 경우)
  • UNION ALL이 사용된 쿼리(8.0 이후부터는 임시 테이블 생성하지 않음)
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

마지막 3개는 "Using temporary"라고 나오지도 않는데 임시 테이블을 사용한다.

• 임시 테이블이 디스크에 생성되는 경우(MyISAM 스토리지 엔진 사용)
  • 임시 테이블에 저장해야 하는 컬럼 중 BLOB, TEXT 같은 대용량 컬럼이 존재하는 경우
  • 레코드 전체 크기가 UNION, UNION ALL 에서 찾은 레코드의 컬럼이 512바이트 이상인 경우
  • GROUP BY, DISTINCT 컬럼에 512바이트 이상 크기의 컬럼이 있는 경우
  • 임시 테이블의 저장 데이터의 전체 바이트 크기가 tmp_table_size 또는 max_heap_table_size를 넘는 경우

테이블 조인

조인의 구분

• INNER JOIN
• OUTER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

조인할 때 어떤 테이블을 먼저 읽을지를 결정하는 것은 매우 중요하다.

INNER JOIN은 어느 테이블을 먼저 읽든지가 상관이 없다. (MySQL 옵티마이저가 최적화할 것이다.)

OUTER JOIN은 OUTER가 되는 테이블을 먼저 읽어야 하기 때문에 조인 순서를 옵티마이저가 최적화할 수 없다. 그래서 순서를 잘 작성해주는 게 중요하다.

OUTER JOIN은 조인할 때 조인 조건에 일치하지 않는 레코드가 있더라도 OUTER가 되는 테이블에 있는 레코드를 읽어오겠다는 것이다.

MySQL에서는 FULL OUTER JOIN을 지원하지 않는다. (우회해서 INNER JOIN과 OUTER JOIN을 섞어서 구현할 수는 있다.)

✔️ OUTER JOIN에서 주의사항

옵티마이저가 OUTER JOIN을 INNER JOIN으로 바꿔 처리할 수도 있다.

LEFT JOIN의 ON절에 레코드가 없을 수도 있는 쪽의 테이블의 조건을 모두 적어야 한다.

SELECT *
FROM employees e
    LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no
WHERE s.salary > 5000;

OUTER JOIN에서 연결되는 테이블(위의 예시에서는 salaries테이블의 s.salary > 5000)의 조건이 ON이 아니라 WHERE 조건에 있어서 옵티마이저는 INNER 조인으로 처리해버린다.

---

❗ JOIN 알고리즘이나 쿼리 힌트에 대한 부분 등은 생략했습니다.