전체 글

[MySQL] 세미조인 전략 비교: FirstMatch·LooseScan·Materialization 재현과 실측

데이터베이스를 다루다 보면 부모 행에 대응하는 자식 행이 하나라도 존재하는지를 확인하기 위해 EXISTS 또는 IN 서브쿼리를 사용하게 됩니다. 이때 MySQL은 상관 서브쿼리를 바깥 행마다 그대로 반복 실행하는 대신, 조건을 만족하면 세미조인(SemiJoin)으로 변환할 수 있습니다.하지만 세미조인은 하나의 고정된 실행 방식이 아닙니다. 첫 번째 일치에서 탐색을 끝내는 FirstMatch, 인덱스에서 중복 키를 건너뛰는 LooseScan, 서브쿼리 결과를 먼저 만들어 사용하는 Materialization 등 여러 전략이 있으며 데이터 분포와 인덱스에 따라 처리량이 달라집니다. 따라서 “EXISTS가 IN보다 항상 빠르다”거나 “세미조인으로 변환되면 무조건 빠르다”라고 단정해서는 안 됩니다.이번 글에서는..

[MySQL] optimizer_trace로 인덱스가 선택되지 않은 비용과 거부 사유 확인하기

데이터베이스를 다루다 보면 EXPLAIN의 possible_keys에는 인덱스가 표시되는데, 실제 key에는 다른 인덱스가 나오거나 아예 NULL이 표시되는 상황을 만나게 됩니다. 사용 가능한 인덱스가 존재한다는 사실과 그 인덱스를 사용하는 것이 가장 저렴하다는 판단은 서로 다르기 때문입니다.이때 가장 흔히 확인하는 것이 실행 계획이지만, 일반적인 EXPLAIN 결과만으로는 후보 인덱스마다 계산된 행 수와 비용, 거부 사유를 모두 볼 수 없습니다. 구체적인 계산은 바로 옵티마이저 트레이스(Optimizer Trace)의 range_analysis, 특히 analyzing_range_alternatives 아래에서 확인해야 합니다. 이번 글에서는 트레이스를 수집하는 절차부터 후보 인덱스의 rows, cos..

[MySQL] 함수 기반 인덱스 적용법: LOWER와 날짜 표현식 검색 최적화

데이터베이스를 다루다 보면 저장된 원본 값이 아니라 소문자로 변환한 이메일이나 날짜 부분만을 기준으로 검색해야 할 때가 많습니다. 이때 흔히 작성하는 조건이 바로 LOWER(email) = ? 또는 DATE(created_at) = ?입니다. 결과는 정확해 보이지만, 컬럼에 함수를 적용하면 기존 일반 인덱스(Index)를 그대로 사용하기 어려워 대량 테이블에서 전체 스캔으로 이어질 수 있습니다.그렇다면 컬럼에 함수를 적용한 검색 조건도 인덱스를 사용하게 만들 수 있을까요? 결론부터 말하면 MySQL 8.0.13 이상에서는 함수 기반 인덱스의 함수 키 부분(Functional Key Part)을 정의하여 가능합니다. 하지만 표현식이 일치해야 하고, 모든 함수를 사용할 수 있는 것도 아니며, 날짜 조건은 원..

[MySQL] Invisible Index로 운영 인덱스 삭제 전 안전하게 검증하는 방법

데이터베이스를 다루다 보면 오래된 인덱스나 서로 겹치는 복합 인덱스를 정리해야 할 때가 많습니다. 인덱스는 조회 성능을 높여 주지만 INSERT, UPDATE, DELETE가 발생할 때마다 함께 갱신되며 저장 공간과 버퍼 풀도 사용합니다. 그렇다고 사용 횟수가 적어 보이는 인덱스를 운영 환경에서 바로 삭제하면 특정 배치나 월말 작업의 실행 계획이 갑자기 테이블 전체 조회로 바뀔 수 있습니다.이때 가장 안전하게 사용할 수 있는 것이 바로 보이지 않는 인덱스(Invisible Index)입니다. 인덱스 구조와 갱신은 그대로 유지하면서 기본 옵티마이저의 실행 계획 후보에서만 제외하므로, 실제 삭제와 비슷한 쿼리 영향을 먼저 시험할 수 있습니다. 문제가 확인되면 인덱스를 다시 생성하지 않고 가시성만 복구할 수 ..

[MySQL] 커버링 인덱스 효과와 비용: 테이블 접근 제거 전후 측정 가이드

데이터베이스를 다루다 보면 WHERE 조건에 맞는 행을 빠르게 찾도록 인덱스를 만들었는데도 기대만큼 조회 시간이 줄지 않는 경우가 있습니다. 인덱스에서 대상 행을 찾은 뒤 SELECT 절에 필요한 나머지 열을 읽기 위해 테이블의 클러스터드 인덱스 페이지에 다시 접근하기 때문입니다. 이때 가장 흔하게 검토되는 것이 바로 커버링 인덱스(Covering Index)입니다.커버링 인덱스는 특정 쿼리가 요구하는 열을 인덱스만으로 모두 제공할 수 있게 설계한 인덱스입니다. 테이블 접근을 제거할 수 있어 읽기 성능에 유리하지만, 인덱스가 커지고 INSERT·UPDATE·DELETE 작업의 유지 비용도 증가합니다. 따라서 “몇 배 빨라진다”는 고정된 답은 없습니다. 반환 행 수, 버퍼 풀 적중률, 레코드 폭, 저장 장..

[MySQL] 복합 인덱스 컬럼 순서 결정법: 등가·범위·정렬 조건 함께 보기

데이터베이스를 다루다 보면 여러 조건으로 데이터를 좁힌 뒤 최신 순서나 점수 순서로 일부 행만 조회해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 것이 바로 복합 인덱스(Composite Index)입니다. 하지만 WHERE 절에 등장한 컬럼을 적당히 나열하거나 선택도가 높은 컬럼부터 배치하는 것만으로는 올바른 순서를 결정할 수 없습니다.예를 들어 특정 쇼핑몰과 주문 상태를 등가 조건으로 조회하고, 생성 일시를 범위로 제한하면서 최신 주문부터 가져오는 쿼리를 생각해 보겠습니다. 등가 조건, 범위 조건, ORDER BY가 한 쿼리에 섞이면 어느 조건을 먼저 지원할지에 따라 스캔 범위와 정렬 비용이 달라집니다. 따라서 복합 인덱스의 순서는 등가 조건 → 범위 조건 → 정렬 조건이라는 문구를 기계적으로 적..

[MySQL] range, ref, eq_ref 비교: 실행 계획 type만 보고 성능을 판단하면 안 되는 이유

데이터베이스를 다루다 보면 같은 인덱스를 사용하는 쿼리인데도 실행 계획의 type 열에 range, ref, eq_ref처럼 서로 다른 값이 표시될 때가 많습니다. 이때 가장 흔히 하는 실수는 eq_ref > ref > range라는 순서를 그대로 쿼리의 속도 순위로 받아들이는 것입니다.결론부터 말하면, 실행 계획의 접근 방식(Access Type)만 보고 쿼리 성능을 판단해서는 안 됩니다. type은 MySQL이 테이블에서 후보 행을 찾는 방식을 설명할 뿐이며, 실제 처리 행 수, 조인 반복 횟수, 조건 선택도, 반환 열의 크기, 임시 테이블과 정렬 여부까지 대신 설명하지는 않습니다. 이번 글에서는 세 접근 방식의 동작 원리부터 실행 계획을 함께 읽어야 하는 기준, 동일 데이터에서 재현하고 측정하는 방..

[MySQL] Using temporary 진단 체크리스트: GROUP BY 임시 테이블 줄이는 개선 순서

데이터베이스를 다루다 보면 단순 조회보다 매출 합계, 사용자별 접속 횟수, 상태별 주문 건수처럼 데이터를 그룹으로 묶어 집계해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 것이 바로 GROUP BY입니다. 하지만 실행 계획의 Extra 열에서 Using temporary를 발견하면 임시 테이블이 곧바로 성능 장애를 의미한다고 판단하거나, 반대로 결과만 빠르게 나오면 아무 문제도 없다고 넘기는 경우가 있습니다.Using temporary는 MySQL이 쿼리 처리 과정에서 중간 결과를 저장할 내부 임시 테이블(Internal Temporary Table)이 필요하다고 판단했다는 의미입니다. 가장 중요한 점은 임시 테이블의 존재 자체보다 그 안에 몇 행이 들어가며, 메모리에서 처리되는지 디스크 저장소까지 ..

[MySQL] Using filesort는 언제 문제인가: 정렬 행 수로 판단하는 실행 계획 기준

데이터베이스를 다루다 보면 ORDER BY가 포함된 쿼리의 실행 계획에서 Using filesort를 만나게 됩니다. 이름에 file이 들어가 있으니 디스크 파일로 정렬한다는 뜻으로 받아들이거나, 이 표시를 발견하자마자 인덱스를 추가해야 한다고 판단하기 쉽습니다.하지만 Using filesort는 기본적으로 MySQL이 정렬 순서에 맞는 인덱스를 그대로 읽지 못하고 별도의 정렬 단계를 수행한다는 의미입니다. 정렬이 메모리에서 끝날 수도 있으며, 표시 자체만으로 느린 쿼리라고 단정할 수 없습니다. 가장 중요한 것은 결과 건수가 아니라 정렬 단계에 들어간 행 수, 정렬 과정의 자원 사용량, 그리고 정렬을 없애기 위해 포기해야 하는 필터링 효율입니다.이번 글에서는 Using filesort의 동작 원리부터 정..

[MySQL] EXPLAIN FORMAT=JSON 해석법: cost_info·attached_condition·used_columns 진단

데이터베이스를 다루다 보면 일반 EXPLAIN의 한 행만으로는 옵티마이저가 왜 특정 인덱스를 선택했는지, 조건이 어느 단계에서 적용되는지 판단하기 어려울 때가 많습니다. 이때 가장 유용하게 사용할 수 있는 것이 바로 EXPLAIN FORMAT=JSON입니다.JSON 실행 계획에는 접근 방식뿐 아니라 예상 비용, 읽을 행 수, 테이블에 적용되는 조건, 쿼리 처리에 필요한 컬럼이 계층 구조로 표시됩니다. 하지만 query_cost가 크다는 이유만으로 쿼리가 느리다고 단정하거나, attached_condition에 조건이 있다는 이유만으로 인덱스를 사용하지 못했다고 해석하면 잘못된 결론에 도달할 수 있습니다.이번 글에서는 cost_info, attached_condition, used_columns로 무엇을 ..