전체 글

[MySQL] 트랜잭션 격리 수준별 동시성 실험: 조회 결과와 잠금 차이

데이터베이스를 다루다 보면 한 트랜잭션이 데이터를 수정하는 동안 다른 트랜잭션에서는 무엇이 보여야 하는지 판단해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 기준이 바로 트랜잭션 격리 수준(Transaction Isolation Level)입니다. 격리 수준을 높이면 항상 안전하고 낮추면 항상 빠르다고 생각하기 쉽지만, 실제 동작은 일반 SELECT인지 잠금 읽기인지, 트랜잭션 안에서 첫 읽기가 언제 실행됐는지에 따라 달라집니다.이 글에서는 MySQL 8.4의 InnoDB를 기준으로 READ UNCOMMITTED부터 SERIALIZABLE까지 두 세션으로 재현하는 방법을 살펴보겠습니다. Dirty Read, Non-repeatable Read, Phantom Read가 어느 단계에서 보이는지뿐 아니라..

[MySQL] 파생 테이블 조건 푸시다운 확인하기: 외부 WHERE가 내려가지 않는 경우

데이터베이스를 다루다 보면 파생 테이블(Derived Table)에서 많은 행을 만든 뒤, 외부 쿼리의 WHERE 절로 극히 일부만 남기는 SQL을 만나게 됩니다. 이때 가장 흔하게 기대하는 최적화가 바로 파생 조건 푸시다운(Derived Condition Pushdown)입니다.파생 조건 푸시다운은 외부 조건을 파생 테이블 내부로 옮겨 불필요한 중간 결과를 줄이는 최적화입니다. 하지만 모든 조건이 내려가는 것은 아닙니다. LIMIT처럼 결과의 의미가 달라질 수 있는 연산이 있거나, 조건 자체에 서브쿼리가 포함되거나, 윈도 함수 결과를 필터링하는 경우에는 적용되지 않거나 일부 조건만 내려갑니다. 이번 글에서는 외부 WHERE 조건이 내려가지 않는 경우를 중심으로 기본 동작, 실행 계획 확인법, 버전별 검..

[MySQL] CTE 인라인과 구체화 판단법: WITH 쿼리의 반복 스캔 줄이기

데이터베이스를 다루다 보면 복잡한 서브쿼리를 읽기 쉽게 분리하거나 같은 중간 결과를 여러 곳에서 재사용하기 위해 공통 테이블 표현식(Common Table Expression, CTE)을 사용하게 됩니다. 이때 가장 흔히 하는 실수는 WITH 절을 작성하면 결과가 항상 한 번만 계산되어 저장된다고 생각하는 것입니다.MySQL 옵티마이저는 CTE를 외부 쿼리 블록에 합치는 병합(Merge), 흔히 말하는 인라인 방식으로 처리할 수도 있고, 내부 임시 테이블에 저장하는 구체화(Materialization)를 선택할 수도 있습니다. 어느 쪽이 빠른지는 문법만으로 결정되지 않습니다. CTE 내부 연산의 비용, 외부 조건의 밀어 넣기 가능 여부, 참조 횟수, 구체화된 행 수를 함께 봐야 합니다. 이번 글에서는 M..

[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이 테이블에서 후보 행을 찾는 방식을 설명할 뿐이며, 실제 처리 행 수, 조인 반복 횟수, 조건 선택도, 반환 열의 크기, 임시 테이블과 정렬 여부까지 대신 설명하지는 않습니다. 이번 글에서는 세 접근 방식의 동작 원리부터 실행 계획을 함께 읽어야 하는 기준, 동일 데이터에서 재현하고 측정하는 방..