Real MySQL 9-1장

김재연·2025년 7월 20일

Real MySQL

대략적인 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 위와같이 3단계로 크게 나눌 수 있습니다.

두번째 단계에서 위와 같은 내용을 처리하게 됩니다.

우리가 이번 단원에서 다루게 될 내용들은 대부분 2단계에서 벌어지는 일들에 대한 이야기입니다.

InnoDB 가 연속된 데이터 페이지를 읽을 때 사용하는 방법

MySQL 은 위와 같은 경우 풀 테이블 스캔을 사용하게됩니다.

일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요합니다.

그렇기 때문에 대부분 DBMS 는 풀 테이블 스캔을 실행할 때 한꺼번에 여러개의 블록이나 페이지를 읽어오는 기능을 내장하고 있죠.

이 때, 시스템 변수로 이를 지정하는 것이 없어, 사람들이 많이들 하나씩 디스크에서 가져올 것이라고 생각합니다.

이는 InnoDB 에서는 전혀 틀린말입니다.

특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작되게 됩니다.

앞으로 필요한 데이터들을 판단해 미리 읽어오는 것이죠. (버퍼 풀에 가져다 둠)

그래서 이러한 리드 어헤드가 실행되게끔 하는 임계값을 설정도 가능하답니다. (몇개의 데이터가 연속으로 읽혀야 이를 실행할 것인지)

쿼리 병렬 처리

옛날에도 당연히 여러개의 쿼리를 병렬적으로 처리 하는 것은 가능했지만, 하나의 쿼리를 병렬적으로 처리하는 것은 불가능했습니다.

하지만, 이제는 아래와 같이 가능하죠

CPU 코어의 개수에 유의해서 스레드의 개수를 설정할 수 있도록 노력해야합니다!

안그러면 유의미한 성능 개선을 실행할 수 없기 때문입니다.

Order By 처리

정렬을 처리하는 방법으로는 위에 보이는 두가지의 방법이 존재하며 장점 단점도 각각 위와 같이 존재합니다.

모두 인덱스 정렬을 사용할 수 있다면 정말 좋겠죠

하지만, 위와 같은 경우 인덱스 정렬을 사용할 수 없습니다.

위 경우는 인덱스를 그냥 그대로 읽어서는 정렬할 수 없는 경우를 야기합니다.

또한, FileSort 가 실행되는 경우는 Extra 칼럼에 Using filesort 라고 표시됩니다.

소트 버퍼

소트 버퍼는 MySQL 이 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하게 되는데, 이 공간을 소트 버퍼라고 합니다.

소트 버퍼는 정렬이 필요한 경우에만 할당되게 되고, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만, 최대 사용 가능한 공간은 시스템 변수에 의해 결정됩니다.

근데, 만일 소트버퍼가 정렬해야 하는 데이터의 크기보다 작다면 어떻게 될까요?

정렬과정에서 이를 기록하기 위해서 디스크를 사용하게 될 것입니다.

그러면 디스크 읽기 쓰기 연산이 계속 일어나게 되겠죠.

그러면 소트 버퍼 크기를 무조건 늘리면 괜찮을까요?

그렇지 않습니다.

소트 버퍼의 크기를 변화시키며 위 쿼리를 실행해봤을 때, 위와 같은 결과가 나왔다고 합니다.

무작정 크다고 해서 좋은 것이 아니라, 특정 구간에서 효율이 증가된 것을 볼 수 있죠?

너무 큰 소트 버퍼 할당은 할당 시간 때문에 성능이 더 떨어질 수 있다는 점을 보여주는 것입니다.

정렬 작업이 많은 경우 또한 메모리 부족 현상을 경험할 수도 있죠

그렇기 때문에 무작정 크게 잡는 것은 성능상도 좋지 않을뿐더러, 큰 장애를 일으킬 수도 있습니다.

정렬 알고리즘

정렬할 때 레코드 전체를 소트 버퍼에 담을지 혹은 정렬 기준 칼럼만 담을지에 따라 싱글 패스 와 투 패스 정렬으로 나뉩니다.

한번 살펴볼까요?

위와 같이 정렬 방법을 확인할 수 있습니다.

sort_algorithm 에 stable_sort 라고 표시된 것을 확인할 수 있죠

더 정확히는 MySQL 서버의 정렬 방법은 다음에서 보이듯이 3가지의 정렬방식이 있다고 합니다.

첫번째 방식을 투패스, 두번째, 세번째 방식을 싱글 패스 정렬 방식이라고 명명할 수 있을 것 같습니다.

싱글 패스 정렬 방식

싱글패스 알고리즘은 그림에서 보이듯이 정렬에 필요하지 않은 last_name 칼럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행하게 됩니다.

또한, 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨주는 과정을 볼 수도 있죠.

투 패스 정렬방식

정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 할 칼럼을 가져오는 정렬 방식입니다.

보이는 것처럼 정렬을 진행한 뒤 last_name 칼럼 조회를 하는 것을 볼 수 있습니다.

이는 테이블을 두 번 읽어야 하기 때문에, 상당히 불합리한 부분이 입니다.

하지만, 싱글패스보다 덜 소트 버퍼 공간을 할당할 수 있죠.

그러니, 정렬할 데이터가 적을 수록 싱글 패스 정렬을 사용할 수 있는 것입니다.

정리하면 아래 두 가지의 경우에 싱글 패스 정렬 방식을 사용하지 못합니다.

즉, 레코드가 너무 큰 경우이죠.

정렬 처리 방법

Order By 가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 진행이됩니다.

아래로 갈 수록 성능은 떨어집니다.

솔직히 인덱스를 사용하는 경우는 그냥 읽기만 하면 되니까 그냥 넘어가고

사용하지 못하는 경우를 고려해야 하는데, FileSort 를 사용할 것이고, 또한 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택합니다.

일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나게 됩니다.

그렇기 때문에 가능하다면 드라이빙 테이블만 정렬한 뒤 조인을 수행하는 것이 효과적입니다.

인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해서는 반드시 Order By 에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, Order By의 순서대로 생성된 인덱스가 있어야합니다.

또한 where 절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면, 그 조건과 Order By 는 같은 인덱스를 사용할 수 있어야 합니다.

또한 R-Tree 나 해시 인덱스, 전문 검색 인덱스에서는 사용할 수 없습니다.

여러 테이블이 조인되는 경우에는 네스티드-루프 방식의 조인에서만 이 방식을 사용할 수 있습니다.

order by 가 있든 없든, 동일한 순서대로 출력됩니다.

이유는 프라이머리 키를 먼저 읽고, salaries 테이블을 조인했기 때문입니다.

그리고 아래는 Order By 를 MySQL 은 이제 똑똑하게 처리하니까 성능을 걱정해 제거 하지 말라는 것이다.

조인의 드라이빙 테이블만 정렬

현재 where 절이 위와같은 특징을 가지고 있기 때문에 드라이빙 테이블로 선택할 수 있습니다.

위 과정은 드라이빙 테이블만을 정렬하는 과정을 보여준 것입니다.

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 select 해서 정렬하는 경우라면 임시 테이블은 절대 필요하지 않습니다.

하지만, 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면, 임시 테이블이 필요할 수도 있게 됩니다.

이는 3가지 방법중 정렬해야 할 레코드가 가장 많기 때문에 가장 느립니다.

방금 예제와 order by 절의 칼럼만 제외하고 같은 쿼리입니다.

하지만, 조인 조건이 employee 이기 때문에, 드라이빙 테이블은 employee 테이블이 될 것입니다.

하지만, order by 에 명시된 칼럼은 salaries 의 테이블이죠.

이렇기 때문에 드리븐 테이블이 활용됩니다.

정렬 처리 방법의 성능 비교

또한 Order by 나 Group by 작업은 where 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서 처리할 수 없습니다.

그렇기 때문에, 튜닝을 잘해도 잘못된 order by, group by 때문에 쿼리가 느려지는 경우가 자주 발생하게 되죠.

이제 그 이유에 대해서 쿼리가 처리되는 방법을 "스트리밍 처리" 와 "버퍼링 처리" 라는 2가지 방식으로 구분하여 살펴봅시다.

스트리밍 방식

스트리밍은 우리가 동영상을 보듯이, 그냥 데이터를 한번에 받아오는 것이 아니라 순차적으로 받아오는 것입니다.

우리도 그렇기 때문에 동영상을 켜자말자 바로 볼 수 있는 것이죠.

이는 데이터를 순차적으로 받아오기 때문에 그냥 원하는 숫자의 데이터를 받으면 끊어버리면 됩니다.

그렇기 때문에 LIMIT 으로 최적화가 가능한 것이죠

버퍼링 방식

order by 나 group by 같은 처리는 쿼리의 결과가 스트리밍 되는 것을 불가능하게 합니다.

일단, where 조건에 일치하는 모든 레코드를 가져온 후 정렬하거나 그루핑해서 차례대로 보내야 하기 때문에, 일부만 가져올 수 없는 것이죠.

그렇기 때문에, 이는 LIMIT 으로 성능최적화가 불가능하빈다.

이제 조인과 함께 Order By 절과 LIMIT 절이 사용될 경우 정렬 처리 방법별로 어떤 차이가 있는지 좀 더 자세히 살펴볼까요?

위 쿼리가 수행될 때, 각각의 방법에 대해서 통계를 낸 것입니다.

어느 테이블이 먼저 드라이빙 되어 조인되는지도 중요하지만, 어떤 정렬방식으로 처리되는지는 더 큰 성능 차이를 만들게 됩니다.

가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝방법이라고 할 수 있습니다.

또한 Limit 이 아예 영향을 주지 않는 것은 아니지만, 굉장히 미미하다라는 참고 사항이다.

정렬 관련 상태 변수

각 상태 값은 다음과 같은 의미가 있습니다.

이 값들을 이용해서 지금까지 MySQL 서버가 처리한 정렬 작업 내용을 어느정도 이해할 수 있습니다.

이 예제의 결과를 해석해보면 대략 위와 같은 내용을 알아낼 수 있습니다.

Group By 처리

Group By 절에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 Having 절은 튜닝을 할 수 없다.

근데 뒤에서 또 인덱스 사용한다고 그러네, 인덱스 사용하는 경우는 인덱스를 차례대로 읽는 인덱스 스캔 방식과 인덱스를 건너 뛰면서 읽는 루스 인덱스 스캔이라는 방법을 사용하빈다.

인덱스를 사용하지 못하는 경우는 임시 테이블을 사용하게 되죠

인덱스 스캔을 이용하는 Group By (타이트 인덱스 스캔)

인덱스로 Group By 내용을 읽더라도 집계함수를 추가적으로 사용하는 경우에 임시테이블이 사용될 수 있습니다.

이 때, Extra 칼럼에 별도로 Using Index for group - by 라고 나온고, 정렬 관련 코멘트는 Using temporary, Using filesort 가 표시됩니다.

루스 인덱스 스캔을 이용하는 Group By

위는 루스 인덱스를 사용하게 됩니다.

salaries 테이블의 인덱스는 (emp_no, from_date) 로 생성되어 있으므로 위의 쿼리 문장에서 where 조건은 인덱스 레인지 스캔 접근 방식을 이용할 수 없는 쿼리지만, 루스 스캔을 사용한 것을 볼 수 있습니다.

앞서 인덱스 부분에서 살펴보았듯이, Group By 조건으로 각각의 유니크한 값들을 통해 작업범위를 결정하고, where 로 작업범위 결정을 또 해주어, 점프 점프 하면서 데이터들을 탐색하는 것입니다.

당연히 유니크한 값의 수가 적을수록 성능이 향상됩니다.

보통은 카디널리티가 높아야지 인덱스의 성능이 좋아지지만, 이 경우는 반대입니다.

쩄든, 루스 인덱스 스캔이 사용될 수 있는지는 위와 같은 패턴의 경우에서 사용될 수 있습니다.

사실 사용이 되지 않는 경우가 더 중요한 것 같습니다 허허허!

3 번째 경우에는 커버링 인덱스가 동작하지 않으면, 이 경우 인덱스 테이블을 거의 다 스캔하기 때문에, 옵티마이저가 풀 테이블 스캔을 선택한 것 같습니다.

그렇다고 합니다 허허허

임시 테이블을 사용하는 Group By

이 쿼리의 실행 계획에서는 Extra 칼럼에 Using Temporary 메시지가 표시되었습니다.

임시 테이블이 사용된 이유는 employees 테이블을 풀스캔하기 때문이 아니라 인덱스를 전혀 사용할 수 없는 Group By 이기 때문입니다.

여기서 주의해야 할 점은, Using filesort 가 표시되지 않았다는 점입니다.

옛날에는 Order By 가 없어도, Group By 절에서 묵시적인 정렬을 실행했는데, 이제는 더이상 실행하지 않도록 바뀌게 되었기 때문입니다.

그 이유는 MySQL 8.0 에서는 Group By 가 필요한 경우 내부적으로 Group By 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서, 중복 제거와 집합 함수 연산을 수행하기 떄문입니다.

즉, 쿼리를 처리하기 위해 MySQL 서버는 다음과 같은 임시 테이블을 생성하는 것이죠.

그러고서 조인의 결과를 한 건씩 가져와서 임시 테이블에서 중복 체크를 하면서 Insert 또는 Update 를 실행하고, 그렇기 때문에 별도의 정렬이 필요없어진 것입니다.

이렇게 말이죠

하지만, 당연하게도 order by 를 명시하면 filesort 를 실행합니다.

묵시적인 정렬만 수행하지 않는다는 것이죠!

묵시적인 정렬로 인해서 위와 같은 처리를 옛날에는 해줬다고 합니다.

참 귀찮았겠네요

Disntinct 처리

Distinct 함수가 집합함수와 같이 사용되는 경우와 그렇지 않은 경우 2가지로 구분해서 살펴보겠습니다.

Select Distinct

단순히 Select 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 select distinct 형태의 쿼리 문장을 사용할 것입니다.

이 때, 동작이 Group By 와 동일하게 처리됩니다.

특히나, Group By가 묵시적인 정렬까지 수행하지 않게 되었기 때문에 더더욱이 동일한 동작을 수행합니다.

Distinct 할 때 가장 주의할 점은 Distinct first_name 처럼 명시했다고 해서, first_name 이 유니크한 경우를 가져오는 것이 아니라는 것입니다.

select 하는 전체 값이 유니크한 것을 가져오는 것입니다.

또한 그래서 Distinct 를 함수처럼 사용하는 사람이 있는데, 굉장히 그럴싸해보입니다.

하지만, 이는 서버에서 실행될 때, 저렇게 의미없이 사용된 괄호를 제거해버립니다.

우리는 지금까지 Distinct 가 모든 컬럼에 영향을 준다고 알게되었습니다.

하지만, 지금부터 설명하는 집합 함수와 함께 사용된 경우는 조금 다릅니다.

집합 함수와 함께 사용된 Distinct

Count(), Min(), Max() 와 같은 집합함수 내에서 Distinct 키워드가 사용될 수 있는데, 이 경우는 일반적으로 지금까지 살펴본 경우들과 다른 형태로 해석이 됩니다.

위와 같은 경우, 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들만 가져오기 떄문입니다.

위 쿼리는 내부적으로 COUNT(Distinct s.salary) 를 처리하기 위해 임시테이블을 사용하게 됩니다.

하지만 이 쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지가 표시되지 않습니다.

이는 버그처럼 보이지만 지금까지 모든 버전의 MySQL 서버에서 보여주는 실행 계획에서는 Using Temporary 를 표시하지 않고 있습니다.

위 쿼리는 조인된 결과에서 salary 칼럼의 값만 저장하기 위해 임시테이블을 사용합니다.

즉, 레코드가 많아질수록 느려질 가능성이 굉장히 크다는 것이겠죠

근데, 만일 아래와 같은 쿼리가 존재한다면요?

임시테이블을 두개 사용할 것입니다.

위의 쿼리는 Distinct 를 처리하기 위해 인덱스를 이용할 수 없어서 임시 테이블이 필요했습니다.

하지만, 다음 쿼리와 같이 인덱스된 칼럼에 대해 Distinct 처리를 수행할 떄는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있습니다.

아무래도 중복된 값을 빠르게 거를 수 있기 때문이겠죠 ? (정렬되어 있으니까)

헷갈릴 수 있습니다.

위 쿼리들을 보고 명확하게 이해할 수 있어야 합니다.

내부 임시 테이블 활용

MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨집니다. 물론 아닌 경우도 있지만요.

MySQL 이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 곳에서 사용하는 것도 보는 것도 불가능합니다.

그렇기 때문에 사용자가 생성한 임시 테이블과 달리 쿼리의 처리가 완료되면 자동으로 삭제가 되게 됩니다.

메모리 임시 테이블과 디스크 임시 테이블

옛날에는 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 임시 테이블의 엔진으로 사용하도록 했는데, 현재는 메모리는 TemTable 디스크는 InnoDB 스토리지 엔진을 사용하도록 바뀌었습니다.

이유는 MEMORY 는 문자열 가변 타입을 지원하지 않아 항상 최대 크기로 저장하고 트랜잭션을 지원하지 않아서였습니다.

이제는 모두 개선이 된 것이죠.

또한 임시 테이블의 크기가 1GB 보다 커지는 경우 MySQL 서버는 메모리의 임시 테이블을 디스크로 기록하게 되는데, 이 때 MySQL 서버는 다음의 2가지 디스크 저장 방식 중 하나를 선택합니다.

쨌든 기본적으로 MMAP 으로 전환하게 되어있고, 시스템 변수를 바꾸면 InnoDB 테이블로 전환 가능합니다.

기본값이 MMAP 인 이유는 오버헤드가 적기 떄문입니다.

임시 테이블이 필요한 쿼리

아까 처음부터 디스크로 생성되는 테이블도 있다고 했습니다.

이 때 기본적으로 생성되는 테이블의 종류는 InnoDB 입니다.

물론 이 밖에도 인덱스를 사용하지 못할 때는 내부 임시 테이블을 생성해야 할 때가 많다고 합니다.

임시 테이블이 디스크에 생성되는 경우

그냥 너무 크면 디스크에 생성하는 것이죠

근데 이만큼 개선이 되었다고는 합니다.

임시 테이블 관련 상태 변수

우리는 그냥 실행게획상에서 Using Temporary 가 써져있으면 임시테이블이 사용되었다고 알았습니다.

하지만, 메모리에서 처리되었는지 디스크에서 처리되었는지 알 수가 없었습니다.

하지만 위와 같이 시세틈 변수를 활용하면 확인할 수 있습니다.

테스트 쿼리를 실행하기 전에 Flush Status 명령을 실행해 현재 세션의 상태 값을 초기화하여 보기 쉽게 만듭니다. (시스템 변수가 누적값이기 떄문에)

위를 보면 임시 테이블이 디스크에 만들어졌었음을 알 수 있습니다.

고급 최적화

서버가 옵티마이저로 실행 계획을 수립할 때, 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 됩니다.

아래와 같이 이러한 옵션을 수정할 수도 있죠

상태로는 default, on, off 가 있으며 default 는 기본값이 적용되게 됩니다.

옵티마이저 스위치 옵션을 영역별로 위와 같이 줄 수 있습니다.

또한, 힌트를 주어 현재 쿼리에서만 실행 가능하게끔도 할 수 있습니다.

MRR 과 배치 키 액세스

지금까지 조인을 수행할 때에는 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 것이었습니다.

이는 네스티드 루프 조인이라고 하고, 이 같은 단점을 보완하기 위해 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링 합니다.

드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링 하는 것입니다.

이렇게, 조인 버퍼에 레코드가 가득차면 비로소 MySQL 엔진은 버퍼링 된 레코드를 스토리지 엔진으로 한번에 요청합니다.

이로써, 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있는 것입니다.

이러한 읽기 방식은 MRR 이라고 하며 BKA 조인을 활용하는데, 이는 성능에 오히려 안좋은 영향을 줄 수 있어 기본적으로 비활성화 되어있습니다.

블록 네스티드 루프 조인

MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이지만, 조인의 연결 조건이 되는 칼럼에, 모두 인덱스가 있는 경우 사용되는 조인 방식입니다.

의사 코드를 보면 알 수 있듯이, 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고, 즉시 드리븐 테이블의 레코드를 찾아서 반환한다는 것을 알 수 있습니다.

네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이는 조인 버퍼가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐입니다.

Block 이라는 단어가 사용되면 조인용으로 별도의 버퍼가 사용됐다는 것을 의미합니다.

인덱스를 사용하지 못하는 경우, 드라이빙 테이블의 결과를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리합니다. (그렇지 않으면 드라이빙 테이블을 하나 읽고 드리븐 테이블을 풀 스캔해서 찾아야함 시간 복잡도 O(N ^ 2) 이다)

아래와 같은 쿼리는 카테시안 조인을 활용하게 됩니다.

Join 조건이 없기 때문이죠!

dept_emp 테이블이 드라이빙 테이블이며, employess 테이블을 읽을 때는 조인 버퍼를 이용해 블록 네스티드 루프 조인을 한다는 것을 Extra 칼럼의 내용으로 알 수 있습니다.

아래 그림은 실행계획에서 조인 버퍼가 어떻게 사용되는지 보여줍니다.

즉, 드리븐 테이블을 조인 버퍼에 저장한 후, 레코드를 검색하면서 쭉 캐시된 조인 버퍼와 결합해서 반환하는 것을 볼 수 있다.

일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 기억해야한다.

인덱스 컨디션 푸시다운

위 같은 경우는 작업 범위 결정 조건은 last_name, first_name 은 필터링 조건으로 동작한다.

그렇기 때문에 Extra 부분에 Using Where 가 나온 것을 볼 수 있고, 이는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 where 조건에 일치하는지 검사하는 과정을 의미하게 됩니다.

인덱스를 사용하지 못한다고 하더라도, 그래도 레코드에서 다시 안읽고, 바로 first_name 을 필터링 해서 가져올 수 있지 않았을까?

맞다, MySQL 5.6 버전 이전에는 InnoDB 스토리지 엔진이 이를 수행해주지 않았다. 그렇기 떄문에 불필요하게 두번의 읽기 연산을 진행해주었어야 하는 것이다.

그래서 위와 같이 인덱스 컨디션 푸시다운이라는 개념이 생겨 조건을 만족하게 데이터를 검색할 수 있게 되었습니다.

이제 옵티마이저 옵션을 원래대로 돌려두고 실행계획을 한번 확인해보면 Using where 가 없어지고 Using index condition 이 출력되는 것을 확인할 수 있습니다.

고도의 기술력을 필요로 하는 것은 아니지만, 굉장히 큰 성능향상을 일으킬 수 있는 중요한 개념이라는 것은 알 수 있습니다.

인덱스 확장

세컨더리 인덱스의 마지막 리프노드에는 프라이머리 키 값이 들어있다, 그렇기 때문에 fromdate 의 인덱스는 마치 (from_date, dept_no, emp_no) 와 같은 것이다.

예전에는 이것을 활용하지 못했지만, 현재는 이것을 활용하고 있다.

key_len 칼럼은 이 쿼리가 인덱스를 구성하는 칼럼 중에서 어느 부분까지 사용했는지를 바이트 수로 보여주는데 이 예제에서는 19바이트가 표시된 것을 보면 from_date(3바이트) + dept_no(16바이트) 칼럼까지 사용했다는 것을 알 수 있습니다.

그럼 dept_no='d001' 을 조건을 제거한 쿼리의 실행 계획에서는 key_len 칼럼의 값이 얼마로 표시되는지 살펴보죠!

예상했던 대로 dept_no 칼럼을 사용하지 않으니 from_date 칼럼을 위한 3바이트만 표시된 것을 확인할 수 있습니다.

실제로 프라이머리키를 인덱스에 활용했다는 것이죠 (실제로 from_date 인덱스에 명시되어 있지도 않았었는데)

그 뿐만 아니라 InnoDB 의 프라이머리 키가 세컨더리 인덱스에 포함돼 있으므로 다음과 같이 정렬 작업도 인덱스를 활용해서 처리되는 장점도 존재합니다.

위 쿼리 실행계획에서 Using filesort 가 표시되지 않았다는 것은 인덱스를 활용하여 정렬을 진행했다는 것으로 이해해도 되니까요!

김재연

끊임없이 '성장'하는 개발자 김재연입니다.

이전 포스트

Real MySQL 8.0 8장

다음 포스트