MySQL·Oracle 의 통계 처리 차이를 파고든 기록

이 글은 Christophe Pettus 의 How the Other Half Counts 를 읽고, MySQL 과 Oracle 이 Optimizer 통계를 다루는 방식이 왜 이렇게 다른지 파고들며 정리한 기록입니다. 리터럴(바인드 변수가 아닌) 변수 조건을 전제로 합니다.

들어가며

비용 기반 Optimizer 는 결국 같은 문제를 풉니다. 통계를 읽어 선택도를 곱해 나가고, 몇 가지 조인 전략의 비용을 계산한 뒤 하나를 고르는 일입니다. 핵심은 통계에 대한 수집 및 활용, 즉 통계를 어디서 가져오고, 얼마나 오래된 것까지 허용하며, 애초에 어떤 플랜 형태를 선택지로 놓느냐입니다.

원문은 PostgreSQL 을 가운데 두고 일곱 개 데이터베이스를 늘어놓지만, 저는 그중 MySQL 과 Oracle 두 엔진만 떼어내어 좁게 들여다보고 싶었습니다. 특히 같은 단어로 부르는 두 가지 통계, 컬럼 히스토그램과 인덱스 통계를 두 엔진이 정반대로 다루는 지점이 마음에 걸렸습니다. 그 차이를 따라가다 보니 단순히 “기능이 있고 없고”의 문제가 아니라, Optimizer 가 어디서 숫자를 얻느냐에 대한 설계 철학의 차이라는 데까지 닿게 되었습니다.

두 엔진은 통계를 어떻게 다루나

먼저 원문이 두 엔진을 어떻게 정리했는지부터 요약해 두겠습니다.

MySQL 의 통계는 인덱스 경계를 따라 둘로 나뉩니다. 한쪽은 InnoDB 가 디스크에 영구적으로 보관하는 통계(persistent statistics)입니다. 인덱스 접근을 담당하고, 5.6.6 부터 서버를 재시작해도 사라지지 않고 유지되며 기본으로 켜져 있습니다. 인덱스 페이지를 샘플링해(innodb_stats_persistent_sample_pages, 기본 20장) 인덱스별 카디널리티를 저장하고, 테이블의 약 10% 가 바뀌면 자동으로 다시 수집합니다. 다른 한쪽은 컬럼 히스토그램입니다. 8.0 에 와서야 추가된 나중에 덧붙인 절반이고, 그 티가 납니다. ANALYZE TABLE ... UPDATE HISTOGRAM 으로 사람이 직접, 자기가 정한 일정으로 영원히 다시 돌려야 합니다. 게다가 Optimizer 는 인덱스가 있는 컬럼의 히스토그램은 대체로 무시하고 인덱스 자체의 통계를 선호합니다. 그래서 히스토그램은 주로 인덱스 없는 컬럼을 위한 도구로, 통계의 전반적인 수준을 끌어올린다기보다 빠진 구멍을 메우는 쪽에 가깝습니다.

Oracle 은 정반대인 Maximalist 입니다. 수집기인 DBMS_STATS 가 야간 유지보수 윈도우 동안 오래된 통계를 다시 모으고, 19c 부터는 기본 15분마다 오래된 객체를 다시 들여다보는 고빈도 자동 수집까지 더해졌습니다. 컬럼 히스토그램은 네 종류(frequency, top-frequency, height-balanced, hybrid)가 있고 Optimizer 가 데이터를 보고 어느 걸 만들지 고릅니다. 추정이 틀리면 스스로 메모를 남기고(SQL 플랜 디렉티브), 그래도 틀리면 실행 도중에 플랜을 다시 배선하는(적응형 플랜) 장치까지 갖추고 있습니다.

같은 두 가지 통계를 두고 두 엔진이 어디서 갈라지는지 한자리에 모으면 이렇습니다.

항목	MySQL	Oracle
인덱스 통계	InnoDB 디스크 보관 통계, 자동 갱신	DBMS_STATS, 자동 수집
컬럼 히스토그램	수동(ANALYZE TABLE), 8.0 에 추가	워크로드 기반 자동 수집
인덱스 컬럼의 히스토그램	Optimizer 가 대체로 무시	인덱스 유무와 독립적으로 사용
런타임 적응	없음	적응형 플랜 / 커서 공유

표의 세 번째 줄, 인덱스가 있는 컬럼에서 히스토그램을 어떻게 취급하느냐가 이 글의 출발점입니다.

히스토그램과 인덱스 통계는 다른 질문에 답합니다

MySQL 만 보면 “인덱스가 있으면 히스토그램은 안 쓴다”는 동작이 자연스러워 보입니다. 그런데 Oracle 로 넘어오면 이 전제가 통째로 흔들립니다. Oracle 에서 히스토그램은 컬럼 수준 통계의 일부이고, 인덱스 유무와 무관하게 Optimizer 의 카디널리티 계산에 직접 들어갑니다. MySQL 처럼 “인덱스 있으면 히스토그램 무시”하는 동작 자체가 없습니다.

Optimizer 가 히스토그램을 참조하는 방식을 보면 이유가 분명해집니다. Equal 조건(예: last_name = 'Smith')의 선택도는 기본적으로 그 컬럼의 고유값 개수 n 의 역수, 즉 1/n 로 잡힙니다. 히스토그램이 없을 때의 균등 분포 가정입니다. 그런데 히스토그램이 있으면 이 공식 자체가 바뀝니다. Optimizer 는 고유값 개수 대신 히스토그램을 사용하고, 컬럼 안 값들의 실제 분포를 반영한 더 나은 선택도를 내놓습니다.

원문서의 고전적 예시가 명확합니다. 캘리포니아 서점이 책의 95% 를 캘리포니아로, 4% 를 오리건으로, 1% 를 네바다로 보낸다고 합시다. 주문 테이블은 30만 행이고, 오리건으로 보낸 책 수를 조회합니다. 히스토그램이 없으면 Optimizer 는 균등 분포를 가정해 300000 / 3 으로 10만 행을 추정하고, 그 추정치 위에서 풀 테이블 스캔을 고릅니다. 반대로 히스토그램이 있으면 책의 4% 가 오리건으로 간다고 계산해 인덱스 스캔을 고릅니다.

여기가 중요한 대목이라고 생각합니다. 이 예시에서 컬럼에 인덱스가 있어도 히스토그램은 그대로 참조되며, 오히려 히스토그램이 인덱스 접근 경로를 선택하게 만드는 정보가 됩니다. 인덱스 통계(blevel, clustering factor 등)와 컬럼 히스토그램은 서로 다른 목적의 입력으로, Optimizer 가 둘 다 씁니다.

히스토그램은 필터의 선택도(몇 행이 나오나)를 알려주고, 인덱스 통계는 그 행들을 인덱스로 가져오는 비용이 얼마인가를 알려줍니다.

이 구분을 잡고 나면, MySQL 과 Oracle 이 갈라지는 지점이 더 또렷하게 보입니다. 예를 들어 Oracle 은 유니크 컬럼이 Equal 조건에만 쓰인다면 거기에는 히스토그램을 자동 생성하지 않습니다. 히스토그램이 무의미하기 때문입니다. MySQL 이 “인덱스 있으면 무시”하는 것과 표면적으로 비슷해 보이지만 메커니즘이 다릅니다. MySQL 은 인덱스의 존재 자체로 히스토그램을 배제하고, Oracle 은 분포가 균등한지 또는 조건에 쓰이는지로 판단합니다. 그래서 인덱스가 있어도 데이터가 치우쳐 있으면 Oracle 은 히스토그램을 만듭니다.

판단 근거도 자동입니다. Oracle 은 SYS.COL_USAGE$ 에 기록된 컬럼 사용 정보(WHERE 절 조건에 쓰인 컬럼들)와 데이터 치우침의 존재를 바탕으로 어느 컬럼에 히스토그램이 필요한지 스스로 판단합니다. 기본 설정인 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' 가 이 로직입니다. MySQL 이 사람의 ANALYZE TABLE 호출을 기다리는 것과는 출발선부터 다른 셈입니다.

MySQL 은 왜 히스토그램을 적극적으로 쓰지 않나

그렇다면 MySQL 은 히스토그램을 Oracle 처럼 적극적으로 쓰지 못하는 걸까요, 아니면 일부러 안 쓰는 걸까요. 결론부터 적으면 일부러 안 쓰는 쪽인데, 그 배경에는 기술적 한계도 함께 깔려 있습니다. 둘 다 맞다고 생각합니다.

MySQL 이 인덱스 컬럼에서 히스토그램을 무시하는 것은 추측이 아니라 매뉴얼에 명시되어 있습니다. Optimizer 는 범위 Optimizer 의 행 추정치를 히스토그램 통계보다 선호하고, 범위 Optimizer 가 적용된다고 판단하면 히스토그램을 쓰지 않습니다. 인덱스가 있는 컬럼에서는 Equal 조건절의 행 추정치를 인덱스 다이브로 얻을 수 있는데, 이 경우 인덱스 다이브가 더 나은 추정치를 낼 수 있으므로 히스토그램이 꼭 유용한 것은 아니라는 논리입니다.

여기서 핵심이 인덱스 다이브입니다. MySQL 이 인덱스 컬럼에서 히스토그램을 비켜 두는 이유는, 인덱스가 있으면 히스토그램보다 더 정확한 방법을 쓸 수 있기 때문입니다. 인덱스 다이브는 쿼리를 계획하는 그 순간에 B-tree 를 직접 타고 내려가서, 이 값(state = 'OR')에 해당하는 범위에 실제로 몇 개 행이 있는지를 B-tree 구조에서 직접 읽어옵니다. 미리 저장해 둔 통계가 아니라 그 쿼리의 그 값에 대해 실시간으로 트리를 더듬어 얻는 실측치에 가깝습니다. 샘플링 기반의 근사치인 히스토그램보다 정확할 수 있다는 것이 MySQL 의 입장입니다.

그래서 MySQL 의 히스토그램은 원문 표현대로 구멍 메우기입니다. 인덱스가 없어서 인덱스 다이브를 할 수 없는 컬럼, 거기만 히스토그램이 담당합니다. 의도적인 분업인 셈입니다.

그러면서도 “여건 자체가 안 되는 것 아니냐”는 의심도 일부는 맞다고 봅니다. 두 가지 한계가 깔려 있습니다.

• 히스토그램이 늦깎이 기능입니다. 8.0(2018)에 와서야 추가됐고, 그 전 20년간 MySQL 은 히스토그램 없이 인덱스 카디널리티만으로 살아왔습니다. 그래서 Optimizer 의 기존 의사결정 경로가 전부 인덱스 통계와 인덱스 다이브 중심으로 짜여 있고, 히스토그램은 나중에 옆에 끼워 넣은 모듈입니다. 원문이 쓴 “나사로 덧붙인(bolted-on)”이라는 표현이 이걸 가리킵니다.
• 자동 수집이 안 됩니다. Oracle 은 워크로드를 보고 알아서 히스토그램을 만들지만, MySQL 은 ANALYZE TABLE ... UPDATE HISTOGRAM 을 사람이 직접 영원히 쳐야 합니다. 히스토그램을 일급 통계로 자동 운영하도록 인프라가 설계되어 있지 않습니다.

정리하면, 인덱스 컬럼에서 안 쓰는 것은 인덱스 다이브라는 더 나은 대안이 있어서 일부러 그러는 것이고, 전반적으로 소극적인 것은 늦게 추가됐고 자동화 인프라가 없어서 그렇게밖에 못 쓰는 것입니다.

Oracle 은 왜 인덱스 통계에 기대지 않나

질문을 뒤집어 보면 더 흥미롭습니다. Oracle 이 바인드 변수 때문에 히스토그램을 못 쓰는 상황이라면, 왜 MySQL 처럼 인덱스 통계나 인덱스 다이브로 넘어가지 않을까요.

먼저 “바인드 변수면 무조건 균등 분포 가정”은 절반만 맞습니다. Oracle 에는 그 상황을 메우는 두 장치가 있습니다. bind peeking 은 커서가 처음 호출될 때 바인드 값을 엿보고 마치 리터럴처럼 카디널리티를 잡습니다. Adaptive Cursor Sharing 은 실행 간 플랜 효율을 비교해 차선책이 감지되면 값에 따라 다른 플랜을 허용합니다.

다만 두 장치 모두 한계가 뚜렷해서 늘 기댈 수 있는 것은 아닙니다. bind peeking 은 하드 파싱 때 엿본 첫 값이 이후 실행에 그대로 굳어집니다. 첫 값이 'CA'(95%)였으면 풀스캔 플랜이 박혀서, 나중에 'NV'(1%)로 실행돼도 풀스캔을 씁니다. Adaptive Cursor Sharing 은 그 문제를 사후에 교정하는 반응형 장치라, 충분히 실행되어 학습하기 전까지는 잡지 못하고 플랜이 출렁이는 부작용도 있습니다. Oracle 이 공격적인 적응 기능을 기본적으로 꺼 두는(OPTIMIZER_ADAPTIVE_STATISTICS = FALSE) 것도 같은 맥락인 것 같습니다.

그래서 진짜 질문에 답하면, Oracle 은 인덱스 컬럼이어도 인덱스 다이브를 계획 시점에 하지 않기 때문입니다. MySQL 의 다이브는 값으로 매번 트리를 실시간으로 더듬는 반면, Oracle 의 선택도 계산은 미리 수집해 둔 정적 통계(NDV, density, 히스토그램)에 값을 대입하는 방식입니다. 그래서 바인드라 값을 모르고 위 두 장치도 아직 듣지 않는 상황이면, 히스토그램이 있어도 넣을 값이 없어 density 같은 평균 통계로 갈 수밖에 없습니다. 값으로 실시간 실측하는 대안이 애초에 없기 때문입니다.

인덱스 다이브는 어디서 오나

여기까지 오면 자연스럽게 한 가지 의문이 따라옵니다. MySQL 은 인덱스 다이브를 하고 Oracle 은 안 하는 것이, 혹시 MySQL 의 스토리지 엔진 분리 구조 때문일까. 그리고 다이브가 스토리지에 직접 들어가 보는 일이라면 I/O 가 발생할 텐데, 그 비싼 연산을 MySQL 은 어떻게 실행 계획에 포함하는 걸까. 두 의문을 따라가다 보니, 전제 두 가지를 먼저 교정해야 진짜 이유가 보였습니다.

1) 스토리지 엔진 구조 때문이 아닙니다

스토리지 엔진 분리 아키텍처는 인덱스 다이브와 거의 무관합니다. 다이브가 가능한 진짜 이유는 더 단순합니다. MySQL 의 Optimizer 가 계획 단계에서 B-tree 인덱스를 직접 탐색할 수 있는 권한과 인터페이스를 갖고 있기 때문입니다. Optimizer 가 핸들러 API 를 통해 “이 값으로 트리를 타고 내려가 양 끝 위치를 잡아라”고 InnoDB 에 요청할 수 있습니다.

이건 스토리지 엔진이 별도 계층이라서가 아니라, Optimizer 와 스토리지 사이의 인터페이스가 그런 계획 시점 트리 탐색을 노출하도록 설계됐기 때문입니다. Oracle 도 단일 엔진이지만 Optimizer 가 같은 일을 하도록 설계하지 않았을 뿐입니다. 아키텍처 분리 여부의 문제가 아니라 설계 선택의 문제라고 보는 게 맞을 것 같습니다.

2) 다이브는 실행 계획에 들어가는 연산이 아닙니다

두 번째 교정이 더 중요합니다. “I/O 비용이 큰데 왜 계획에 포함하나”라는 의문은 인덱스 다이브의 비용을 쿼리 실행 비용과 헷갈린 데서 옵니다. 둘은 다른 층위입니다.

인덱스 다이브는 실행 계획에 박히는 연산이 아닙니다. 실행 계획을 만드는 과정에서, Optimizer 가 카디널리티를 추정하려고 잠깐 트리를 더듬는 행위입니다. 결과물인 플랜에 “인덱스 다이브”라는 단계가 남는 게 아니라, 플랜을 고르기 위한 계산 도구로 쓰이고 버려집니다. 그러니 “비싼 다이브를 실행 계획에 포함한다”는 말 자체가 성립하지 않습니다. 포함되는 게 아니라 계획 시간에 소모됩니다.

그래서 비용 구조도 직관과 반대입니다. 실제 I/O 측면에서 다이브는 보통 쌉니다. B-tree 의 양 끝 두 지점만 짚으면 되니 루트에서 리프까지 경로 몇 페이지뿐이고, 대개 버퍼 풀에 이미 떠 있습니다. Equal 조건절 하나면 다이브 두 번입니다.

진짜로 MySQL 이 걱정한 비용은 I/O 가 아니라 다이브 횟수의 누적이었습니다. 매뉴얼도 명확합니다. 인덱스 다이브는 정확한 행 추정치를 주지만 비교 값의 개수가 늘어날수록 추정치 생성에 걸리는 시간이 길어지고, 인덱스 통계는 정확도는 떨어지지만 큰 값 목록에 대해 더 빠르다는 것입니다. 문제가 터진 실제 사례가 이를 잘 보여줍니다. IN 절에 값이 1만 개면 다이브가 2만 번이고, 실행은 0.38초인데 계획에만 3초가 걸린 버그 리포트가 있었습니다. 그래서 MySQL 이 eq_range_index_dive_limit 을 도입했습니다. IN-list 값 개수가 이 limit 을 넘으면 Optimizer 가 개별 다이브를 포기하고 저장된 인덱스 통계로 후퇴하는 변수입니다. 커뮤니티 피드백으로 기본값이 10 에서 200 으로 올라갔습니다.

이 후퇴 지점, 그러니까 다이브를 포기하고 균등 분포를 가정한 인덱스 통계로 넘어가는 순간이 실은 또 다른 정확도 손해의 출발점이기도 합니다. 저는 이 한계를 두고 IN-list 의 일부만 다이브해 그 평균을 전체에 곱하는 샘플링 방식을 MySQL Optimizer Team 에 제안해 본 적이 있는데, 그 배경과 패치 과정은 Bug #120021 제안 기록에서 따로 다뤘습니다.

3) Oracle 은 못 해서가 아니라 안 하는 것

기술적으로 Oracle 이 트리를 못 타는 것은 아닙니다. 인덱스 범위 스캔할 때 늘 타니까요. 안 하는 것은 철학과 캐싱 모델의 차이 때문이라고 생각합니다.

Oracle 의 설계 전제는 한 번 파싱한 플랜을 Shared Pool 에 캐싱해 수천 번 재사용한다는 것입니다. 이 모델에서 계획 단계는 가능한 한 가볍고 결정론적이어야 합니다. 매 하드 파싱마다 트리를 더듬으면 계획 시간이 들쭉날쭉해지고, 같은 SQL 이라도 그때그때 트리 상태에 따라 다른 추정이 나와 플랜 안정성이 흔들립니다. 그래서 Oracle 은 미리 수집한 정적 통계를 읽어 계산하는 쪽을 택했습니다. 계획은 통계 조회만으로 끝나고, 무거운 일은 DBMS_STATS 가 미리 비동기로 다 해 둡니다. 대신 그 정적 모델의 약점(통계가 오래되거나 바인드 값을 모르는 경우)은 앞서 본 bind peeking, Adaptive Cursor Sharing, dynamic sampling, SQL 플랜 디렉티브, 적응형 플랜 같은 런타임 적응과 사후 학습으로 메웁니다.

MySQL 은 정반대 철학입니다. 플랜을 Shared Pool 에 캐싱해 재사용하는 모델이 전통적으로 약했고, 매 쿼리를 비교적 가볍게 새로 계획하는 편입니다. 그러니 계획할 때마다 트리를 잠깐 더듬는 비용을 감당할 수 있습니다. 어차피 매번 계획하니, 그 김에 실측하면 가장 정확합니다. 다만 그 실측이 폭증하는 경우(IN 수천 개)만 통계로 후퇴하면 됩니다.

같은 “인덱스 컬럼 + 데이터 치우침”이라는 상황을, MySQL 은 계획 시점의 실시간 실측(인덱스 다이브)으로, Oracle 은 미리 모아 둔 정적 통계와 런타임 적응으로 푸는 것 같습니다. 표면적으로 닮은 “인덱스 있으면 히스토그램은 거든다”는 동작 뒤에, Optimizer 가 어디서 숫자를 얻느냐에 대한 정반대의 선택이 있다고 생각합니다.