MySQL(12)

오늘 한 것

9.3.1.1 MRR과 배치 키 액세스

MRR = Multi-Range-Read 메뉴얼에선 DS-MRR (Disk Sweep mrr)이라고도 한다.

MySQL서버가 지금까지 하던 조인방식은

드라이빙 테이블의 레코드를 하나 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인

이것을 Nested Loop Join 이라고 한다.

내부 구조상 조인 처리는 MySQL 엔진이 처리하지만 실제 레코드를 검색하고 읽는건 스토리지 엔진이 한다.

이때 드라이빙 테이블의 레코드 건 별로 드리븐 테이블의 레코드를 찾으면 스토리지 엔진에서는 아무런 최적화가 안된다.

이런 내용이 있어서

그냥 순수하게 2중 for문 돌아서 비교하는거니까, 최적화 개입의 여지가 없다.

라고 이해햇는데 방향이 맞나? CS공부 사용자설정 Gem

네, 방향은 정확히 맞습니다.

더 명확히 하자면, “MRR이 없을 때의 옛날 방식”이 “최적화 개입의 여지가 없다”는 의미입니다. 그리고 바로 그 문제를 해결하기 위해 MRR이 등장한 것이죠.

‘마트에서 장보기’에 비유하면 아주 쉽게 이해할 수 있습니다.

MRR이 없을 때 (전통적인 Nested Loop Join) 🛒

이는 두서없이 적은 쇼핑 목록을 들고 장을 보는 것과 같습니다.

(드라이빙 테이블) 쇼핑 목록에서 **'사과'**를 본다.

(드리븐 테이블) **'채소 코너'**로 달려가 사과를 카트에 담는다.

(드라이빙 테이블) 목록 다음 줄의 **'우유'**를 본다.

(드리븐 테이블) 이번엔 **'유제품 코너'**로 달려가 우유를 담는다.

(드라이빙 테이블) 목록 다음 줄의 **'상추'**를 본다.

(드리븐 테이블) 다시 **'채소 코너'**로 뛰어간다.

이렇게 매번 필요한 데이터를 찾기 위해 디스크의 여러 장소를 왔다 갔다 하는 것을 랜덤 I/O라고 합니다. 스토리지 엔진은 MySQL 엔진이 주는 요청을 한 건씩 처리할 뿐이라, 이런 비효율적인 동선을 개선할 방법이 없습니다.

MRR을 사용할 때 (최적화된 방식) 🚀

MRR은 쇼핑 목록을 동선에 맞게 재정렬해서 장을 보는 것과 같습니다.

(드라이빙 테이블) 일단 쇼핑 목록 전체('사과', '우유', '상추')를 확인해서 정렬 버퍼에 담아둡니다.

(최적화) 카트에 담기 전에, 목록을 마트 동선에 맞게 재정렬합니다. -> ('사과', '상추'), ('우유') 순으로 바꿉니다.

(드리븐 테이블)

    먼저 **'채소 코너'**에 한 번만 가서 사과와 상추를 모두 담습니다.

    그다음 **'유제품 코너'**로 가서 우유를 담습니다.

MRR은 이처럼 디스크 접근을 최소화하기 위해, 필요한 데이터들의 주소(PK)를 모아 정렬한 뒤 한 번에 디스크에 요청합니다. 그 결과, 비효율적인 랜덤 I/O가 효율적인 순차 I/O로 바뀌면서 성능이 극적으로 향상됩니다. 구분 MRR 없는 조인 MRR 사용 조인 비유 두서없이 장보기 동선에 맞게 정렬해서 장보기 디스크 접근 랜덤 I/O (느림) 순차 I/O (빠름) 핵심 동작 1건씩 요청 → 1건씩 처리 여러 건 모아서 정렬 → 한 번에 처리

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

그럼 인덱스가 없으면 뭐야? CS공부 사용자설정 Gem

인덱스가 없으면 블록 네스티드 루프 조인(Block Nested-Loop Join)이나 MySQL 8.0.18부터 도입된 해시 조인(Hash Join)을 사용합니다. 요즘은 대부분 해시 조인이 사용됩니다.

해시 조인 (Hash Join) - 최신 방식 (8.0.18+)

두 개의 명단을 대조하는 작업에 비유할 수 있습니다.

빌드(Build) 단계: 두 명단 중 더 작은 명단(테이블)을 가져와, 이름(조인 키)을 기준으로 빠르게 찾아볼 수 있는 해시 테이블을 메모리에 만듭니다. (예: '김영수'는 1번 서랍, '이철희'는 2번 서랍)

프로브(Probe) 단계: 이제 큰 명단(테이블)을 처음부터 끝까지 한 번만 읽으면서, 각 이름이 해시 테이블에 있는지 즉시 확인하고 일치하는 사람을 찾습니다.

이 방식은 각 테이블을 단 한 번씩만 읽으면 되므로, 대용량 테이블 조인 시 매우 효율적입니다.

블록 네스티드 루프 조인 (Block Nested-Loop Join) - 옛날 방식

이 방식은 명단 대조 작업을 매우 무식하게 반복하는 것과 같습니다.

블록 생성: 첫 번째 명단에서 한 묶음(블록), 예를 들어 100명의 이름을 가져와 메모리(조인 버퍼)에 올려놓습니다.

전체 스캔: 이제 두 번째 명단 전체를 처음부터 끝까지 읽으면서, 메모리에 올려놓은 100명의 이름과 일치하는 사람이 있는지 비교합니다.

무한 반복: 첫 번째 명단의 다음 100명을 가져와 메모리에 올리고, 다시 두 번째 명단 전체를 처음부터 끝까지 읽는 과정을 반복합니다.

이 방식은 두 번째 테이블을 몇 번이고 반복해서 읽어야 하므로, 데이터가 조금만 커져도 성능이 급격하게 나빠집니다. 해시 조인이 도입되면서 지금은 거의 사용되지 않습니다.

9.3.1.1 MRR과 배치 키 액세스

MRR = Multi-Range-Read 메뉴얼에선 DS-MRR (Disk Sweep mrr)이라고도 한다.

MySQL서버가 지금까지 하던 조인방식은

드라이빙 테이블의 레코드를 하나 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인

이것을 Nested Loop Join 이라고 한다.

내부 구조상 조인 처리는 MySQL 엔진이 처리하지만 실제 레코드를 검색하고 읽는건 스토리지 엔진이 한다.

이때 드라이빙 테이블의 레코드 건 별로 드리븐 테이블의 레코드를 찾으면 스토리지 엔진에서는 아무런 최적화가 안된다.

내가 이해하기로. 그냥 순수하게 2중 for문 돌아서 비교하는거니까, 최적화 개입의 여지가 없다.

그래서 MySQL서버는 드라이빙 테이블의 레코드를 읽어서 조인을 바로 하는게 아니라, 조인 대상을 버퍼링한다. 조인 버퍼가 가득 차면 스토리지 엔진으로 한번에 요청한다. -> 스토리지 엔진은 데이터 페이지에 정렬된 순서로 레코드 접근해서 Disk IO를 최소화 할 수 있다.

물론, 데이터 페이지가 메모리(InnoDB 버퍼 풀)에 있어도 버퍼 풀 접근을 최소화 할 수 있다.

이런 읽기방식을 MRR이라고 하고, 이걸 쓰는 조인방식을 BKA (Batched Key Access) join 이라고 한다.

기본적으로 단점이 있어서 비활성화, 부가적인 정렬작업이 성능에 더 안좋을 수 있다.

일단 여기서 뉘앙스가 뭔가 네스티드 루프 조인은 최적화가 개입할 거리가 없어서 MRR방식의 조인이 더 좋다(블록 네스티드 루프 조인이 더 좋다) 라고 소개할 줄 알았는데, 정작 보니까 NLJ가 안되는 경우(= 드리븐테이블의 인덱스를 쓸 수 없는 경우) 엄청 느리니까 그나마 덜 느린 BNLJ를 한다.

이렇게 이해했는데 이게 맞아? CS공부 사용자설정 Gem

거의 다 맞으셨는데, 가장 중요한 부분에서 약간의 오해가 있습니다.

BKA 조인과 블록 네스티드 루프 조인(BNLJ)은 완전히 다른 상황에서 사용되는, 별개의 최적화 방식입니다. 사용자의 이해가 정확한지 확인하기 위해 MySQL 조인 방식의 전체적인 계층 구조를 살펴보겠습니다.

MySQL 조인 방식 결정 순서

MySQL 옵티마이저는 다음과 같은 순서로 조인 방식을 결정합니다.

조인할 컬럼에 인덱스가 있는가?

    예 → 인덱스 기반의 네스티드 루프 조인(NLJ) 사용

        이때 MRR/BKA 옵션이 활성화되어 있고, 옵티마이저가 판단하기에 유리하면 → BKA 조인 (NLJ의 강화판)

        그렇지 않으면 → 기본 NLJ (전통 방식)

    아니오 → 인덱스 없는 조인 방식 사용

        MySQL 8.0.18 이상이고 해시 조인 조건이 맞으면 → 해시 조인

        그 이전 버전이거나 해시 조인을 쓸 수 없으면 → 블록 네스티드 루프 조인 (BNLJ)

이제 각 방식이 무엇인지 ‘인사팀에서 직원들의 부서 정보 찾기’에 비유해 설명해 드릴게요.

기본 Nested Loop Join (인덱스 O) 👩‍💼

비유: 인사팀 직원이 직원 명단을 한 명씩 보면서, 잘 정리된 부서 목록(인덱스)에서 해당 직원의 부서를 바로바로 찾아 서류에 기입합니다.

설명: 가장 기본적이고 빠른 방식입니다. 드리븐 테이블의 인덱스 덕분에 매번 필요한 데이터를 빠르게 찾을 수 있습니다. 하지만 직원 1000명을 조회한다면, 부서 목록을 1000번 들춰봐야 합니다(1000번의 랜덤 I/O).
BKA Join (인덱스 O + MRR 최적화) 🚀

비유: 똑똑한 인사팀 직원은 이제 한 명씩 찾지 않습니다. 직원 100명의 목록을 먼저 뽑은 뒤, 이 목록을 부서 번호 순으로 재정렬합니다. 그리고 부서 목록 관리자에게 “이 부서 번호 목록에 해당하는 부서 정보 전부 한 번에 주세요”라고 요청합니다.

설명: 이것이 바로 MRR/BKA입니다. 여러 번의 랜덤 I/O를, 데이터를 모아서 정렬한 뒤 한 번에 처리하는 순차 I/O로 바꿔 성능을 극대화합니다. 즉, 기본 NLJ의 강화판입니다.
블록 네스티드 루프 조인 (BNLJ) (인덱스 X) 🐢

비유: 부서 목록이 정리되지 않은 서류 뭉치(인덱스 없음)입니다. 직원은 어쩔 수 없이 직원 100명의 명단을 들고, 부서 서류 뭉치 전체를 처음부터 끝까지 뒤져서 일치하는 정보를 찾습니다. 그리고 다음 직원 100명을 위해, 다시 부서 서류 뭉치 전체를 또 뒤집니다.

설명: 인덱스가 없을 때 사용되던 옛날 방식입니다. 드리븐 테이블을 몇 번이고 반복해서 스캔해야 하므로 매우 비효율적입니다.
해시 조인 (Hash Join) (인덱스 X, 최신 방식) ✨

비유: 인사팀이 최신 시스템을 도입했습니다. 먼저 부서 목록 전체를 스캔해서 메모리에 빠르게 찾아볼 수 있는 해시 테이블을 만듭니다. 그리고 직원 명단을 쭉 읽으면서, 각 직원의 부서 정보를 이 해시 테이블에 즉시 물어봐서 찾아냅니다.

설명: 인덱스가 없을 때 사용하는 최신 방식입니다. 각 테이블을 한 번씩만 읽으면 되므로 BNLJ보다 훨씬 빠릅니다.

결론

주니어님의 이해를 정리해 드리자면,

NLJ는 인덱스가 있을 때 쓰는 좋은 방식입니다.

BKA는 그 NLJ를 MRR로 더 좋게 만든 강화판입니다.

BNLJ는 인덱스가 없을 때 쓰던, 매우 느린 옛날 방식입니다. (지금은 해시 조인이 대체함)

따라서 “NLJ가 안될 때 BNLJ를 쓴다”는 것은 맞지만, BKA(MRR)는 BNLJ와는 전혀 다른, NLJ의 성능을 끌어올리기 위한 최적화 기법이라고 이해하시면 정확합니다.

BKA조인과 BNLJ가 다른거야 CS공부 사용자설정 Gem 대답이 중지되었습니다.

BKA조인과 BNLJ가 다른거야? CS공부 사용자설정 Gem

네, 완전히 다릅니다.

두 조인 방식은 사용되는 전제 조건부터 동작 방식, 목적까지 모든 것이 다릅니다.

가장 큰 차이는 조인할 컬럼에 인덱스가 있느냐 없느냐입니다.

BKA 조인 vs 블록 네스티드 루프 조인(BNLJ)

구분 BKA 조인 (Batched Key Access) 블록 네스티드 루프 조인 (BNLJ) 전제 조건 인덱스 있음 (O) 인덱스 없음 (X) 목적 빠른 NLJ를 더 빠르게 만들기 위한 최적화 인덱스가 없어 느린 조인을 그나마 낫게 하기 위한 방법 핵심 동작 랜덤 I/O를 순차 I/O로 전환 (MRR) 드리븐 테이블 반복 스캔 비유 인덱스가 있는 참고서로 오픈북 시험 보기 인덱스가 없는 참고서로 오픈북 시험 보기 성능 매우 빠름 매우 느림

오픈북 시험 비유 📖

BKA 조인 (인덱스 O)

    시험 문제가 100개 주어졌습니다. 참고서에는 **색인(인덱스)**이 잘 되어 있습니다.

    똑똑한 당신은 1번 문제 찾고, 2번 문제 찾는 식으로 따로따로 찾지 않습니다.

    100개 문제를 참고서 페이지 순으로 재정렬한 뒤, 참고서를 처음부터 끝까지 한 번만 쭉 훑으면서 모든 문제의 답을 순서대로 찾습니다.

    결론: 똑똑한 방법으로 더 빠르게 문제를 풉니다.

블록 네스티드 루프 조인 (BNLJ) (인덱스 X)

    시험 문제가 100개 주어졌는데, 참고서에 색인(인덱스)이 없습니다.

    어쩔 수 없이, 문제 10개를 먼저 집어 들고, 답을 찾기 위해 참고서 전체를 처음부터 끝까지 읽습니다.

    10개 답을 찾은 뒤, 다음 문제 10개를 집어 들고, 답을 찾기 위해 다시 참고서 전체를 처음부터 끝까지 읽습니다.

    결론: 무식한 방법으로 문제를 풀 수밖에 없습니다.

요약하자면, BKA 조인은 ‘빠른 것을 더 빠르게’ 만드는 최적화 기술이고, BNLJ는 ‘답이 없는 상황에서 최악을 피하기 위한’ 고육지책입니다. (현재는 해시 조인으로 대체됨)

여기서 이 말이 무슨말인지 모르겟어, 드라이빙 테이블을 먼저 읽고 조인버퍼에 놓은건데, 왜 “드리븐 테이블을 먼저 읽고” 라는 말이 나온거지? CS공부 사용자설정 Gem

네, 그 문장은 오해의 소지가 다분하며, 사실상 책의 서술 오류 또는 매우 헷갈리는 표현으로 보입니다. 주니어님이 헷갈리시는 것이 당연합니다.

결론부터 말씀드리면, 실제 데이터 접근 순서는 (1) 드라이빙 테이블 → (2) 조인 버퍼 → (3) 드리븐 테이블이 맞습니다.

올바른 처리 순서 (그림 기준)

책의 그림에 나온 번호 순서대로 일어나는 일을 명확히 정리해 드릴게요.

1단계: 드라이빙 테이블(de) 읽기
MySQL은 먼저 드라이빙 테이블인 dept_emp(de)에서 from_date > '1995-01-01' 조건에 맞는 레코드들을 읽습니다.

2단계: 조인 버퍼에 저장
1단계에서 읽은 결과(그림에서는 4건)를 **'조인 버퍼'**라는 메모리 공간에 차곡차곡 쌓아둡니다.

3단계: 드리븐 테이블(e) 읽기
조인 버퍼가 어느 정도 채워지면(또는 드라이빙 테이블을 다 읽으면), 그제서야 드리븐 테이블인 employees(e)를 읽기 시작합니다.

4단계: 비교 및 조인
e 테이블의 레코드를 한 줄씩 읽으면서, 조인 버퍼에 들어있는 모든(4건) 레코드와 조인 조건(emp_no)이 맞는지 비교합니다. 일치하는 결과가 있으면 최종 결과로 보냅니다.

“드리븐 테이블을 먼저 읽고” 라는 말의 정체

책에서 “드리븐 테이블을 먼저 읽고”라고 표현한 부분은, “조인 버퍼가 채워진 후에는, 조인 비교 연산을 위해 드리븐 테이블을 읽는 단계가 시작된다”는 의미를 너무 압축해서 표현하려다 보니 어색해진 것으로 보입니다.

마치 단체 소개팅에 비유할 수 있습니다.

드라이빙 테이블: 남자 그룹

조인 버퍼: 대기실

드리븐 테이블: 여자 그룹

“남자분들이 먼저 대기실에 들어가 계시면, 그다음 여자분들이 들어와서 대기실에 있는 모든 남자분과 한 명씩 대화합니다.” 라고 설명해야 하는데, 책에서는 “남자분들이 대기실에 들어가고, 여자분들을 먼저 확인합니다.” 와 같이 서술 순서가 꼬인 것입니다.

따라서 주니어님이 이해하신 대로 드라이빙 테이블을 먼저 읽어 조인 버퍼에 두는 것이 맞습니다.

해시조인, BNLJ 비교해봐 CS공부 사용자설정 Gem

네, 두 조인 방식은 조인할 컬럼에 인덱스가 없을 때 사용된다는 공통점이 있지만, 동작 방식과 성능에서 큰 차이가 있습니다.

결론적으로 해시 조인은 각 테이블을 한 번만 읽는 현대적이고 빠른 방식인 반면, BNLJ는 내부 테이블을 여러 번 반복해서 읽는 구식의 느린 방식입니다.

한눈에 보는 비교

구분 해시 조인 (Hash Join) 블록 네스티드 루프 조인 (BNLJ) 사용 버전 8.0.18 이후 (최신) 8.0.18 이전 (구식) 핵심 동작 메모리에 해시 테이블 생성 후 비교 조인 버퍼에 채우고 반복 스캔 테이블 읽기 각 테이블을 한 번씩만 읽음 외부 테이블은 한 번, 내부 테이블은 여러 번 읽음 성능 빠름 매우 느림

두 방식의 차이 (명단 대조 비유) 🧑‍💻

두 그룹의 명단(A, B)을 대조해서 같은 이름이 있는지 찾는 작업에 비유할 수 있습니다.

해시 조인 (스마트한 방식)

준비: 두 명단 중 더 짧은 A 명단을 전부 컴퓨터의 **검색 기능(해시 테이블)**에 미리 입력해 둡니다.

대조: 이제 긴 B 명단을 처음부터 끝까지 한 번만 훑으면서, 이름이 나올 때마다 검색 기능에 입력해 즉시 존재하는지 확인합니다.

결과: 각 명단을 딱 한 번씩만 읽고 작업이 끝납니다. 매우 효율적입니다.

블록 네스티드 루프 조인 (무식한 방식)

준비: B 명단에서 이름 **10개(블록)**를 잘라 메모장(조인 버퍼)에 적습니다.

대조: 이제 A 명단 전체를 처음부터 끝까지 읽으면서, 메모장에 적힌 이름 10개와 일치하는 것이 있는지 대조합니다.

반복: B 명단의 다음 이름 10개를 잘라 메모장에 새로 적고, 다시 A 명단 전체를 처음부터 끝까지 읽으면서 대조하는 작업을 반복합니다.

결과: A 명단을 수십, 수백 번이나 반복해서 읽어야 하므로 매우 비효율적입니다.

그럼 BNLJ가 테이블 풀스캔, 인덱스 풀스캔을 피하려고 도입한건데, 이것들이랑은 뭔 차이인거지 CS공부 사용자설정 Gem

아니요, 그 전제는 약간 다릅니다. BNLJ는 테이블 풀 스캔을 피하기 위한 기술이 아니라, 인덱스가 없어 어쩔 수 없이 테이블 풀 スキャン을 해야 할 때, 그나마 최악의 상황을 피하기 위해 사용하는 조인 방식입니다.

‘테이블 풀 스캔’과 ‘BNLJ’는 서로 역할 자체가 다릅니다.

역할의 차이: 부품 vs 완성품

‘백과사전으로 리포트 쓰기’에 비유해 볼게요.

테이블 풀 스캔 / 인덱스 풀 스캔 = ‘책 한 권 다 읽기’ (기초 동작)

이것은 ‘데이터를 읽는 방식’입니다. 그냥 백과사전 한 권을 처음부터 끝까지 다 읽는 행위 자체를 말합니다. 가장 기본적인 데이터 접근법이죠.

블록 네스티드 루프 조인(BNLJ) = ‘두 책 비교하며 리포트 쓰는 전략’

이것은 ‘데이터를 결합하는 알고리즘’입니다. 인덱스가 없는 두 백과사전(A, B)을 비교해 리포트를 쓰는 전략이죠. 그 전략은 다음과 같습니다.

A 백과사전의 한 챕터(블록) 내용을 노트(조인 버퍼)에 베껴 적습니다.

그 노트를 보면서, B 백과사전 전체를 처음부터 끝까지 읽으며(테이블 풀 스캔) 관련된 내용을 찾습니다.

A 백과사전의 다음 챕터를 노트에 베껴 적고, 다시 B 백과사전 전체를 또 읽습니다(테이블 풀 스캔).

보시다시피, BNLJ라는 전략은 ‘책 한 권 다 읽기’(테이블 풀 스캔)라는 기초 동작을 내부적으로 여러 번 반복해서 사용합니다.

한눈에 보는 비교

구분 테이블 풀 스캔 / 인덱스 풀 스캔 블록 네스티드 루프 조인 (BNLJ) 역할 데이터 접근 방식 (What) 조인 알고리즘 (How) 대상 단일 테이블 또는 인덱스 2개 이상의 테이블 목적 데이터를 읽는 기초 동작 데이터를 결합하는 전략 관계 BNLJ가 내부적으로 사용하는 부품 테이블 풀 스캔을 활용하는 완성품

즉, BNLJ는 풀 스캔을 피하는 기술이 아니라, 어쩔 수 없이 풀 스캔을 써야 할 때 그 횟수를 조금이라도 줄이려는 눈물겨운 노력이라고 보시면 됩니다.

이제 다음 주제로 넘어가서,

인덱스 컨디션 푸시다운 이라는 개념을 설명해봐 CS공부 사용자설정 Gem

인덱스 컨디션 푸시다운(ICP)은 불필요한 디스크 접근을 줄이기 위해, 스토리지 엔진 단계에서 조건을 미리 필터링하는 MySQL의 최적화 기술입니다.

‘똑똑한 사서에게 도서 요청하기’에 비유하면 쉽게 이해할 수 있습니다.

ICP가 없을 때 (비효율적인 사서) 🐢

last_name과 first_name으로 구성된 인덱스가 있고, last_name=’김’ 이면서 first_name에 ‘철’이 포함된 사람을 찾는다고 가정해 봅시다.

당신(MySQL 엔진)의 요청: "사서님, 성이 '김'씨인 사람 책 전부 다 가져다주세요."

사서(스토리지 엔진)의 행동:

    색인(last_name 인덱스)을 보고 '김'씨 책이 100권 있는 것을 확인합니다.

    서가로 가서 100권의 책(데이터 레코드)을 전부 다 꺼내서 당신에게 가져다줍니다. (불필요한 디스크 I/O 발생)

당신(MySQL 엔진)의 필터링:

    100권의 책을 일일이 넘겨보며, 이름에 '철'이 들어간 5권만 남기고 95권은 버립니다.

이 방식은 스토리지 엔진이 불필요한 책 95권을 추가로 가져오는 헛수고를 한 셈입니다.

ICP가 있을 때 (똑똑한 사서) 🚀

당신(MySQL 엔진)의 요청: "사서님, 성이 '김'씨인 사람을 찾되, 가져오기 전에 색인 카드에서 이름에 '철'이 들어가는지도 미리 확인해주세요." (first_name 조건까지 함께 전달)

사서(스토리지 엔진)의 행동:

    색인(last_name 인덱스)을 보고 '김'씨 책 100권의 색인 카드를 확인합니다.

    색인 카드에 이름 정보도 있으므로, 카드를 보면서 이름에 '철'이 포함된 5개의 카드만 골라냅니다.

    이제 서가로 가서 정확히 그 5권의 책만 꺼내서 당신에게 가져다줍니다. (디스크 I/O 최소화)

당신(MySQL 엔진)의 필터링:

    전달받은 5권이 최종 결과이므로, 추가 작업이 거의 필요 없습니다.

이처럼 ICP는 MySQL 엔진이 처리할 조건을 스토리지 엔진으로 “내려보내서(Pushdown)”, 스토리지 엔진이 인덱스 정보만으로 1차 필터링을 하도록 만듭니다. 그 결과, 디스크의 원본 데이터에 접근하는 횟수를 획기적으로 줄여 성능을 크게 향상시킵니다.

EXPLAIN 실행 계획에서는 Extra 컬럼에 Using index condition이라는 문구로 ICP가 동작했음을 확인할 수 있습니다.

비유를 줄이고, 건조하게 팩트만 써봐 CS공부 사용자설정 Gem

네, 비유 없이 사실 기반으로 설명해 드리겠습니다.

인덱스 컨디션 푸시다운 (Index Condition Pushdown, ICP)

인덱스 컨디션 푸시다운(ICP)은 WHERE 조건의 일부가 스토리지 엔진에서 직접 평가(evaluate)되도록 하여, 테이블 데이터 접근(랜덤 I/O) 횟수와 서버-엔진 간 데이터 전송량을 줄이는 최적화 기술입니다.

ICP 미적용 시 동작

스토리지 엔진: 인덱스를 사용해 WHERE 조건의 일부(Key Range)에 해당하는 레코드의 주소를 찾습니다.

스토리지 엔진: 찾은 주소를 이용해 테이블에서 전체 레코드를 읽습니다.

스토리지 엔진: 읽어온 전체 레코드를 MySQL 서버로 전송합니다.

MySQL 서버: 전송받은 전체 레코드를 대상으로 WHERE 절의 나머지 조건을 적용하여 필터링합니다.

이 방식은 최종적으로 필터링되어 버려질 레코드까지 불필요하게 디스크에서 읽어오는 I/O 낭비가 발생합니다.

ICP 적용 시 동작

스토리지 엔진: 인덱스를 사용해 WHERE 조건의 일부(Key Range)에 해당하는 인덱스 엔트리를 찾습니다.

스토리지 엔진: WHERE 조건의 나머지 부분을 인덱스에 포함된 컬럼만으로 평가합니다. 이 조건이 참일 경우에만 다음 단계로 진행합니다.

스토리지 엔진: 2번 단계에서 통과한 레코드에 대해서만 테이블에서 전체 레코드를 읽습니다.

스토리지 엔진: 최종적으로 필터링된 레코드만 MySQL 서버로 전송합니다.

핵심 이점

테이블 랜덤 I/O 감소: 스토리지 엔진이 인덱스 정보만으로 1차 필터링을 수행하므로, 불필요한 테이블 데이터 페이지 접근 횟수가 줄어듭니다.

서버-엔진 간 데이터 전송량 감소: 최종적으로 필요한 레코드만 MySQL 서버로 전달되므로 통신 오버헤드가 감소합니다.

확인 방법

쿼리의 EXPLAIN 실행 계획에서 Extra 컬럼에 Using index condition 문구가 표시되면 ICP가 적용된 것입니다.

md로 감싸봐 CS공부 사용자설정 Gem

네, 이전 답변 전체를 Markdown 블록으로 감싸서 드릴게요. Markdown

네, 비유 없이 사실 기반으로 설명해 드리겠습니다.

인덱스 컨디션 푸시다운 (Index Condition Pushdown, ICP)

인덱스 컨디션 푸시다운(ICP) 은 WHERE 조건의 일부가 스토리지 엔진에서 직접 평가(evaluate)되도록 하여, 테이블 데이터 접근(랜덤 I/O) 횟수와 서버-엔진 간 데이터 전송량을 줄이는 최적화 기술입니다.

ICP 미적용 시 동작

스토리지 엔진: 인덱스를 사용해 WHERE 조건의 일부(Key Range)에 해당하는 레코드의 주소를 찾습니다.
스토리지 엔진: 찾은 주소를 이용해 테이블에서 전체 레코드를 읽습니다.
스토리지 엔진: 읽어온 전체 레코드를 MySQL 서버로 전송합니다.
MySQL 서버: 전송받은 전체 레코드를 대상으로 WHERE 절의 나머지 조건을 적용하여 필터링합니다.

이 방식은 최종적으로 필터링되어 버려질 레코드까지 불필요하게 디스크에서 읽어오는 I/O 낭비가 발생합니다.

ICP 적용 시 동작

스토리지 엔진: 인덱스를 사용해 WHERE 조건의 일부(Key Range)에 해당하는 인덱스 엔트리를 찾습니다.
스토리지 엔진: WHERE 조건의 나머지 부분을 인덱스에 포함된 컬럼만으로 평가합니다. 이 조건이 참일 경우에만 다음 단계로 진행합니다.
스토리지 엔진: 2번 단계에서 통과한 레코드에 대해서만 테이블에서 전체 레코드를 읽습니다.
스토리지 엔진: 최종적으로 필터링된 레코드만 MySQL 서버로 전송합니다.

핵심 이점

테이블 랜덤 I/O 감소: 스토리지 엔진이 인덱스 정보만으로 1차 필터링을 수행하므로, 불필요한 테이블 데이터 페이지 접근 횟수가 줄어듭니다.
서버-엔진 간 데이터 전송량 감소: 최종적으로 필요한 레코드만 MySQL 서버로 전달되므로 통신 오버헤드가 감소합니다.

확인 방법

쿼리의 EXPLAIN 실행 계획에서 Extra 컬럼에 Using index condition 문구가 표시되면 ICP가 적용된 것입니다.

9.3.1.4 인덱스 확장

세컨더리 인덱스에 자동으루 추가된 PK를 활용할 수 있게 할지를 결정하는거다.

근데 세컨더리 인덱스에 자동으로 추가된 PK 의 의미와 성능상 장점이 뭐가 있는지 본다.


CREATE TABLE dept_emp (
      emp_no..
      dept_no
      from_date
      to_date
      PK (dept_no, emp_no)
      KEY ix_fromdate (from_date)
)

여기서 한가지 흥미로운 점은, (from_date, dept_no, emp_no)를 하나의 인덱스 처럼 쓸 수 있다는 것이다.

왜? 어차피 세컨더리인덱스-PK인덱스 이므로, 사실상 맨 앞에 from_date가 붙은것과 유사하다.

PK가 세컨더리에 포함되어 있으므로, 이런 정렬작업도 인덱스를 활용해서 처리된다. 아래는 실행계획 확인 결과 Using FileSort가 없었고, 따라서 별도의 정렬 작업 없이 인덱스 만으로 처리한다.


SELECT * 
FROM dept_emp 
WHERE from_date=const 
ORDER BY dept_no;

인덱스 확장 공식문서

10.3.10 인덱스 확장 기능의 사용 (Use of Index Extensions)

InnoDB는 각 세컨더리 인덱스에 프라이머리 키 컬럼들을 덧붙여 자동으로 확장합니다. 다음 테이블 정의를 생각해 봅시다:

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

이 테이블은 (i1, i2) 컬럼에 프라이머리 키를 정의합니다. 또한 (d) 컬럼에 세컨더리 인덱스 k_d를 정의하지만, 내부적으로 InnoDB는 이 인덱스를 확장하여 (d, i1, i2) 컬럼으로 취급합니다.

옵티마이저는 해당 인덱스를 어떻게 사용할지, 또는 사용할지 여부를 결정할 때 확장된 세컨더리 인덱스의 프라이머리 키 컬럼들을 고려합니다. 이는 더 효율적인 쿼리 실행 계획과 더 나은 성능으로 이어질 수 있습니다.

옵티마이저는 ref, range, index_merge 인덱스 접근, Loose Index Scan 접근, 조인 및 정렬 최적화, 그리고 MIN()/MAX() 최적화를 위해 확장된 세컨더리 인덱스를 사용할 수 있습니다.

다음 예제는 옵티마이저가 확장된 세컨더리 인덱스를 사용하는지 여부에 따라 실행 계획이 어떻게 영향을 받는지 보여줍니다. t1에 다음 행들이 채워져 있다고 가정합니다:

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'), (1, 5, '2002-01-01'),
(2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'), (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'), (3, 5, '2002-01-01'),
(4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'), (5, 4, '2001-01-01'), (5, 5, '2002-01-01');

이제 이 쿼리를 생각해 봅시다:

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

실행 계획은 확장된 인덱스를 사용하는지 여부에 따라 달라집니다.

옵티마이저가 인덱스 확장을 고려하지 않을 때, 인덱스 k_d를 (d)로만 취급합니다. 쿼리에 대한 EXPLAIN은 다음과 같은 결과를 생성합니다:

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

옵티마이저가 인덱스 확장을 고려할 때, k_d를 (d, i1, i2)로 취급합니다. 이 경우, 가장 왼쪽 인덱스 접두사 (d, i1)을 사용하여 더 나은 실행 계획을 생성할 수 있습니다:

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

두 경우 모두 key는 옵티마이저가 세컨더리 인덱스 k_d를 사용함을 나타내지만, EXPLAIN 출력은 확장된 인덱스를 사용하여 다음과 같은 개선 사항을 보여줍니다:

key_len이 4바이트에서 8바이트로 증가하여, 키 조회가 d뿐만 아니라 d와 i1 컬럼을 사용함을 나타냅니다.
ref 값이 const에서 const,const로 변경되어, 키 조회가 하나의 키 부분이 아닌 두 개의 키 부분을 사용함을 나타냅니다.
rows 카운트가 5에서 1로 감소하여, InnoDB가 결과를 생성하기 위해 더 적은 행을 검사해야 함을 나타냅니다.
Extra 값이 Using where; Using index에서 Using index로 변경됩니다. 이는 데이터 행의 컬럼을 참조하지 않고 인덱스만 사용하여 행을 읽을 수 있음을 의미합니다.

확장된 인덱스 사용에 대한 옵티마이저 동작의 차이점은 SHOW STATUS로도 볼 수 있습니다:

FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%';

앞의 구문에는 테이블 캐시를 플러시하고 상태 카운터를 지우기 위해 FLUSH TABLES와 FLUSH STATUS가 포함됩니다.

인덱스 확장을 사용하지 않으면, SHOW STATUS는 다음과 같은 결과를 생성합니다:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

인덱스 확장을 사용하면, SHOW STATUS는 다음과 같은 결과를 생성합니다. Handler_read_next 값이 5에서 1로 감소하여 인덱스를 더 효율적으로 사용했음을 나타냅니다:

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

optimizer_switch 시스템 변수의 use_index_extensions 플래그는 옵티마이저가 InnoDB 테이블의 세컨더리 인덱스를 사용하는 방법을 결정할 때 프라이머리 키 컬럼을 고려할지 여부를 제어할 수 있게 합니다. 기본적으로 use_index_extensions는 활성화되어 있습니다. 인덱스 확장 사용을 비활성화하는 것이 성능을 향상시킬 수 있는지 확인하려면 다음 구문을 사용하십시오:

SET optimizer_switch = 'use_index_extensions=off';

옵티마이저의 인덱스 확장 사용은 인덱스의 키 파트 수(16개)와 최대 키 길이(3072바이트)에 대한 일반적인 제한 사항을 따릅니다.

9.3.1.5 인덱스 머지

인덱스를 이용하여 쿼리를 실행할 때, 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 계획을 수립한다. -> 내 생각, 애초에 인덱스 만들때 자주 쓰는거 하는거니까 하나만 쓰는게 당연하다.

그런데 가끔 병합하는게 나은 경우가 분명히 있다.

3가지 최적화가 존재한다.

index_merge_intersection
index_merge_sort_union
index_merge_union

9.3.1.6 인덱스 머지 - 교집합

예를들어, employees테이블의 first_name과 emp_no 모두 각각의 인덱스 ix_firstname , PK를 가진다고 하자. 그럼 다음 쿼리를 보면

SELECT * 
FROM employees
WHERE first_name=const AND emp_no BETWEEN ..

이제 여기서 = const의 갯수가 253개고, BETWEEN 개수가 10000개라고 해보자.

  옵티마이저는 각각의 조건에 일치하는 레코드 건수를 계산해보고 인덱스 하나만 쓰는 경우중 하나를 고를지, 인덱스 merge 교집합을 할지 고민한다.

인덱스 머지가 아니라면,

first_name으로 253건을 검색하고 BETWEEN 일치하는걸 고른다.
emp_no로 10000건을 읽어서 = const와 일치하는 것을 반환한다.

근데 머지 했을때 14건이 나온다면, 이걸로간다.

9.3.1.7 인덱스 머지 - 합집합

이건 WHERE 절의 각 조건이 인덱스를 사용하고 OR로 연결된 경우 사용한다.

예를들어,

SELECT *
FROM employees
WHERE first_name = const1, OR hire_date = const2

여기서 흥미로운게 잇는데, 둘 다 해당하는 경우, 중복되어 들어갈 수가 있다. (const1, const2)면 first_name 인덱스에서 들어가고, hire_date 인덱스에서도 들어가고.

이런 중복제거를 어떻게 할까

-> 검색 결과가 PK로 정렬되어있을거이므로 우선순위 큐로 중복제거한다.

  참고

  AND, OR이 둘 다 인덱스를 못쓰면 동작이 조금 다르다.
  AND에서 하나만 인덱스, 나머지는 못쓰면 인덱스 레인지 스캔으로 처리한다.

  OR은 둘 중 하나라도 인덱스를 못쓰면 풀 테이블 스캔으로밖에 처리하지 못한다.

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

방금 전과 반대로, 인덱스 머지를 하는 중에 결과의 정렬이 필요한 경우, Sort Union 알고리즘을 사용한다.

SELECT * FROM employees
WHERE first_name = 'Matt'
      OR hire_date BETWEEN '...' AND '.A.';

first_name은 PK로 정렬되어 나오지만, 그 아래는 그렇지 않아서 우선순위 큐를 사용하는 것이 불가능하다.

그래서 각 집합을 PK 칼럼으로 정렬하고 중복제거를 수행한다.

MySQL(12)

MySQL(12)

오늘 한 것

9.3.1.1 MRR과 배치 키 액세스

9.3.1.1 MRR과 배치 키 액세스

BKA 조인 vs 블록 네스티드 루프 조인(BNLJ)

ICP 미적용 시 동작

ICP 적용 시 동작

핵심 이점

확인 방법

인덱스 컨디션 푸시다운 (Index Condition Pushdown, ICP)

ICP 미적용 시 동작

ICP 적용 시 동작

핵심 이점

확인 방법

9.3.1.4 인덱스 확장

10.3.10 인덱스 확장 기능의 사용 (Use of Index Extensions)

9.3.1.5 인덱스 머지

9.3.1.6 인덱스 머지 - 교집합

9.3.1.7 인덱스 머지 - 합집합

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

results matching ""

No results matching ""