개요 인덱스는 데이터베이스 성능 최적화의 출발점임을 부정하기 어려움 MySQL은 스토리지 엔진에 따라 인덱싱과 검색 방식이 달라질 수 있어 기본 개념을 정확히 이해하는 것이 쿼리 튜닝의 기반이 됨 또한 디스크 접근 특성인 랜덤 I/O와 순차 I/O의 차이는 실무 성능에 직접적인 영향을 주므로 반드시 짚고 가야 함 디스크 I/O가 성능을 좌우하는 이유 CPU와 메모리 성능은 가파르게 개선됐지만 디스크 장치는 물리적 한계로 개선 속도가 상대적으로 느림 DB 성능 튜닝의 많은 부분이 디스크 I/O를 얼마나 줄이느냐로 귀결됨 ...
개요 테이블 검색 성능을 끌어올리는 1차 수단은 인덱스 구축임 인덱스는 단일 컬럼 또는 다중 컬럼 기준으로 생성 가능하며, 다음과 같은 기본 생성 규칙이 작동함 Primary Key 지정 시 클러스터드 인덱스 생성 Unique 제약은 보조 인덱스(논클러스터드 인덱스, InnoDB에선 Secondary Index)로 생성 MySQL의 InnoDB는 B+Tree 기반 인덱스를 사용하며, 데이터 저장 구조와 접근 패턴이 인덱스 유형별로 상이함 핵심 개념 정리 클러스터드 인덱스 테이블 데이터가 인덱스 키 순서로 정리되는 구조 테이블당 하나만 존재 리프 노드가 실제 레코드(전체 컬럼)를 보유 InnoDB에서 Primary Key가 곧 클러스터드 인덱스가 됨 PK가 없으면 첫 번째 유니크 not null 인덱스를 사용, 그것도 없으면 내부적으로 보이지 않는 6바이트 Row ID를 생성해 클러스터링 키로 사용 논클러스터드 인덱스(보조 인덱스, Secondary Index) ...
InnoDB에서 여러 컬럼을 묶어 복합 인덱스를 만들면 정의된 순서대로 값을 결합해 B-Tree에 저장됨. 예를 들어 (colA, colB, colC) 인덱스면 colA 기준으로 정렬되고 같은 colA 그룹 내에서 colB, 이어서 colC 순으로 정렬됨. 이 구조 때문에 왼쪽 접두사(Left-most prefix) 원칙이 성립하며, 인덱스 정의의 선두 컬럼부터 조건이 주어질수록 효율이 높아짐 개념과 배경 복합 인덱스는 정의 순서대로 키를 묶어 B-Tree에 저장 InnoDB의 보조 인덱스는 리프에 보조키와 함께 PK를 포함함. PK 길이가 길면 보조 인덱스 크기와 I/O에 직접 영향 left-most prefix 원칙이 핵심. 인덱스의 선두 컬럼부터 연속해서 매칭될 때 탐색 범위가 급격히 줄어듦 동작 방식 예시 (colA, colB) 인덱스 구조 정렬 키 순서: colA → colB → 내부적으로 PK 참조 왼쪽 컬럼 우선 필터링 WHERE colA = ? 또는 WHERE colA = ? AND colB = ? 형태는 인덱스를 온전히 사용 가능 WHERE colB = ?처럼 선두 컬럼 없이 후행 컬럼만 조건일 때는 인덱스 이용이 제한되거나 범위가 매우 넓어짐. 최신 버전에서 옵티마이저가 제한적으로 우회 전략을 쓰는 경우가 있으나 일반적인 기대값으로 두기 어려움 왼쪽 접두사 부분 사용 (colA, colB)에서 colA만 조건으로 사용해도 인덱스 사용 가능 colA와 colB를 모두 조건으로 사용하면 탐색 범위가 더 좁아짐 colB만 조건이면 left-most prefix가 깨져 효과가 급감할 수 있음 정렬과 결합 사용의 기본 인덱스 키 순서와 같은 ORDER BY colA, colB는 추가 정렬 비용 감소에 도움 WHERE 조건이 인덱스 선두를 건너뛰거나 정렬 순서가 키 정의와 다르면 이점이 줄어듦 여러 컬럼을 묶는 이유 다중 조건 검색 성능 향상. 예: WHERE colA = ? AND colB BETWEEN ? AND ?는 (colA, colB)로 빠른 범위 스캔 가능 커버링 인덱스 활용. 예: SELECT colA, colB FROM T WHERE colA = ? AND colB = ?에서 테이블 추가 접근 없이 인덱스만으로 결과 충족 가능 쿼리 패턴 최적화. 실제 WHERE 절에서 자주 함께 쓰는 컬럼을 순서까지 고려해 묶는 설계가 유효. 드물게 쓰이거나 고카디널리티 이점이 없는 컬럼을 무리하게 포함하면 쓰기 비용만 증가 주의할 점 인덱스 정의 순서가 성능을 좌우. 자주 필터링되는 컬럼, 카디널리티가 높은 컬럼을 선두에 두는 것이 일반적 권장 너무 많은 컬럼을 묶으면 인덱스 폭과 관리 비용 증가. 보통 2~3개, 많아도 4개 이하로 목적에 맞게 제한 (A, B, C)일 때 WHERE B = ?처럼 선두가 빠지면 인덱스 사용이 제한. 패턴에 따라 (B, A) 같은 보완 인덱스 검토 InnoDB 보조 인덱스는 PK를 함께 가진다는 점을 고려. PK가 길면 복합 인덱스 크기와 캐시 효율, 쓰기 비용에 불리 ORDER BY, GROUP BY와 결합 시 인덱스 정의 순서와 일치할 때 이점. 중간에 범위 조건이 끼거나 순서가 어긋나면 효과 하락 범위 조건 이후 컬럼 활용 제한 가능. 예를 들어 colA BETWEEN ...가 선두에 오면 그 뒤 colB는 정렬이나 추가 필터에서 기대만큼 활용되지 않을 수 있음 마무리 복합 인덱스는 왼쪽부터 순차적으로 정렬되는 구조이며 left-most prefix 원칙이 적용됨 다중 컬럼 조건, 범위 스캔, 정렬이나 그룹 연산에 유용하지만 인덱스 정의 순서가 쿼리 패턴과 맞지 않으면 이점을 잃음 불필요하게 많은 컬럼을 포함하면 쓰기 부담과 스토리지 사용량만 증가. 실제 트래픽 패턴과 카디널리티를 근거로 최소한의 조합과 올바른 순서를 선택하는 것이 핵심 참고자료 https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html https://dev.mysql.com/doc/refman/8.0/en/covering-indexes.html
개요 InnoDB 버퍼 풀은 데이터와 인덱스 페이지를 메모리에 캐싱하는 영역임 디스크 I/O를 획기적으로 줄여 지연 시간을 낮추는 게 목적임 InnoDB 스토리지 엔진(트랜잭션, MVCC, 행 단위 락 지원) 성능의 심장부라 할 수 있음 쉽게 말해, 자주 쓰는 데이터와 인덱스를 디스크 대신 메모리에 올려두고 처리하는 구조임 버퍼 풀 구성 요소 버퍼 풀에는 주로 이런 페이지(기본 16KB)가 올라옴 데이터 페이지: 실제 테이블 로우(Row)가 저장된 페이지 인덱스 페이지: B-Tree 인덱스 노드 페이지 (PK 및 세컨더리 인덱스 포함) 기타 관리 페이지: UNDO 페이지, 트랜잭션/MVCC 관리에 필요한 메타데이터 등 핵심 개념 페이지 캐싱 InnoDB는 디스크 데이터를 페이지 단위로 다룸 클라이언트가 특정 로우를 읽고 싶어 하면, 그 로우가 속한 페이지 전체를 버퍼 풀로 가져옴 이후 같은 페이지에 있는 다른 로우를 읽을 때는 디스크를 다시 보지 않고 버퍼 풀(메모리)에서 바로 조회함 ...
개념과 배경 InnoDB는 데이터를 클러스터형 인덱스 기준으로 저장하는 스토리지 엔진임 대부분의 테이블에서 이 클러스터형 인덱스는 프라이머리 키가 담당함 사용자가 명시적으로 PK를 정의하지 않은 경우에도 InnoDB는 테이블에 클러스터 키를 반드시 갖도록 함 이때 결정 규칙이 존재함 먼저 NOT NULL 제약을 가진 유니크 인덱스가 있으면 그것을 클러스터형 인덱스로 사용 그런 인덱스가 없으면 내부 6바이트 row_id를 생성해 숨김 PK로 사용 세컨더리 인덱스는 항상 클러스터 키를 포함하여 룩업을 수행함 따라서 명시적 PK가 없더라도 내부적으로는 클러스터 키가 존재하며 저장과 탐색 경로의 기준으로 동작함 ...