큰 범위 조회 시 EXPLAIN의 rows 값이 정확하지 않은 이유가 궁금합니다.

Question

안녕하세요 강사님. [실행 계획에서 type 의미 분석하기 (const, range, ref)] 강의에서 " Index Range Scan할 때 조회 범위가 크면 성능 저하의 원인이 되기도 한다. "라는 내용을 듣고 정말인지 궁금해져서 EXPLAIN을 한번 돌려봤습니다. CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT ); -- 높은 재귀(반복) 횟수를 허용하도록 설정 -- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.) SET SESSION cte_max_recursion_depth = 1000000; -- 더미 데이터 삽입 쿼리 INSERT INTO users (name, age) WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수 ) SELECT CONCAT('User', LPAD(n, 7, '0')), -- 'User' 다음에 7자리 숫자로 구성된 이름 생성 FLOOR(1 + RAND() * 1000) AS age -- 1부터 1000 사이의 난수로 나이 생성 FROM cte; CREATE INDEX idx_age ON users (age); EXPLAIN SELECT id FROM users WHERE id BETWEEN 1 AND 100000; -- type : range 결과 창에서 rows 가 100,000이 아니라 110,836으로 나오더라고요. 옵티마이저가 id 는 PK이라서 중복이 없을 거라는 것을 알고 Auto Increment가 적용되어 있어서 순차적으로 데이터가 들어갔음도 알텐데 왜 10만 개로 딱 떨어지게 예측하지 못하는지 직관적으로 잘 이해가 가지 않습니다. 감사합니다.

jscode · Answer

안녕하세요 선우님 ! 질문 너무 잘 해주셨어요 ~ 질문해주신 내용에 답변 드려볼게요 ! 우선 EXPLAIN의 rows 값은 정확한 수치가 아닌 추정값이예요 ~ 옵티마이저가 실제로 데이터를 전부 세어보는 게 아니라, 인덱스의 통계 정보를 바탕으로 빠르게 추정하는 방식으로 동작하거든요 ! 옵티마이저가 사용하는 통계 정보는 테이블 전체를 매번 스캔해서 만드는 게 아니라, 일정 시점에 샘플링된 데이터를 기반으로 만들어져요 ! 그래서 PK처럼 중복이 없고 순차적으로 들어간 컬럼이더라도 통계 정보 자체에 오차가 포함될 수 있어요 ! 정확한 count를 미리 계산해두지는 않거든요 ~ 결국 EXPLAIN의 rows는 옵티마이저가 실행 계획을 세우기 위한 참고용 추정치라고 이해하시면 돼요 ! 중요한 건 이 값이 크게 줄었는지 늘었는지를 보면서 SQL 튜닝의 방향을 잡는 것이고, 정확한 수치를 기대하는 용도로는 사용하지 않는 게 좋아요 :) 추가로 궁금하신 점 있으시면 언제든 편하게 질문 남겨주세요~~

communityai8509 · Answer

안녕하세요, 인프런 AI 인턴입니다. 박신형님! 질문 주신 부분은 SQL 쿼리 실행 계획의 주요 내역 중 하나로, EXPLAIN 을 사용해서 조회한 결과에서 rows 값이 예상보다 더 많은 경우에 대해 설명드리겠습니다. EXPLAIN 의 rows 값은 MySQL이 특정 쿼리를 통해 처리할 예상 레코드 수를 제공합니다. 이 값은 실제 데이터베이스 통계와 분석을 바탕으로 산출됩니다. 그러나 이러한 예상치는 현실과 약간의 편차가 있을 수 있으며, 이를 이해하는 데 있어 몇 가지 고려 사항이 필요합니다. 통계 기반 예측 : EXPLAIN 의 rows 값은 데이터베이스 엔진이 테이블의 통계를 바탕으로 내부 알고리즘을 통해 추정한 결과입니다. 이는 인덱스되는 키의 분포와 해당 키 값의 변경 상태에 따라 변동이 있을 수 있습니다. 샘플링 결과 : 데이터베이스 시스템은 일부 쿼리 실행 계획에서 통계적 샘플링을 사용하는데, 이때 항상 정확한 샘플 결과를 반영하지 않을 수 있습니다. 이러한 샘플링은 대규모 데이터에서 빨리 결과를 도출하기 위한 방법이며, 이로 인해 실제와 예상 수치 사이에 차이가 발생할 수 있습니다. 옵션 및 설정 상태 : MySQL의 내부 설정이나 데이터베이스 버전에 따라 통계 정보의 정확성이 차이가 날 수 있으며, 이는 실행 계획의 예측 정확성에 영향을 줍니다. 질문하신 문제의 본질은 데이터베이스 통계의 불확실성과 데이터를 다루는 방식의 차이에 따라 발생하는 흔한 현상입니다. 이와 관련하여 비슷한 문제를 다룬 유사 질문과 설명을 참고하시기를 권장합니다. ORDER BY 튜닝관련 문의 : 이 게시물에서는 EXPLAIN의 예측치를 설명하고 실제 실행 결과와의 차이에 대한 이해를 돕습니다. 저는 질문자님의 이해를 돕고자 이런 유사한 답변 링크를 참고하여 추천해 드리고 있음을 알려드립니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.