인덱스
책의 색인처럼 데이터를 빠르게 찾기 위한 도구입니다. 인덱스가 없으면 테이블 전체를 처음부터 끝까지 검색해야 합니다.
데이터의 검색 (Select문) 속도를 높이는 것이 목적이다.
데이터의 값과 디스크에 저장된 위치를 key, value값으로 저장해둔다. 이때 정렬된 상태를 유지한다.
인덱스 종류
1. B-Tree 인덱스
•
쉽게 말해: 가장 일반적이고 널리 사용되는, 책의 찾아보기 같은 인덱스예요 .
•
자세히:
◦
데이터를 정렬된 상태로 유지하는 트리 구조를 가져요 (루트-브랜치-리프 노드) .
◦
컬럼 값을 변형하지 않고 원래 값으로 인덱스를 만들어요
◦
정확히 일치(=), 범위 검색(>, <, BETWEEN), 앞부분 일치(LIKE '시작값%') 검색에 효율적이에요 .
◦
다중 컬럼 인덱스에서는 컬럼 순서가 매우 중요해요. 왼쪽 컬럼 조건 없이는 뒤쪽 컬럼만으로 효율적인 검색이 어려워요 .
◦
데이터 저장/수정/삭제 시 정렬을 유지해야 해서 비용이 들어요 .
2. R-Tree 인덱스
쉽게 말해: 지도에서 주변 장소를 찾는 것처럼 2차원 공간 데이터 검색에 특화된 인덱스예요 .
•
자세히:
◦
위도, 경도 같은 위치 정보(POINT, LINE, POLYGON 등)를 효율적으로 검색해요 .
◦
MBR(Minimum Bounding Rectangle, 도형을 감싸는 최소 사각형) 개념을 사용해서 공간 포함 관계를 빠르게 찾아요 .
◦
주로 ST_Contains(), ST_Within() 같은 포함 관계 비교 함수와 함께 사용돼요 .
3. 전문 검색 (Full-Text) 인덱스
쉽게 말해: 긴 글(문서) 안에서 특정 단어나 구문을 빠르게 찾는 인덱스예요 .
•
자세히:
◦
문서 내용을 단어(토큰) 단위로 분석해서 인덱스를 만들어요 .
◦
불용어(a, the 등 검색 가치 없는 단어) 처리, 어근 분석(단어 원형 찾기) 또는 n-gram(글자를 n개씩 잘라 인덱싱) 같은 알고리즘을 사용해요 .
◦
일반 LIKE '%검색어%' 보다 훨씬 빠르고 효율적이에요.
◦
반드시 MATCH() AGAINST() 구문을 사용해야 인덱스가 활용돼요 .
4. 함수 기반 인덱스 (MySQL 8.0+)
•
쉽게 말해: 컬럼 값을 계산하거나 변형한 결과에 대해 인덱스를 만드는 기능이에요 .
•
자세히:
◦
예를 들어 first_name과 last_name을 합친 full_name에 대해 인덱스를 만들 수 있어요.
◦
가상 컬럼(Virtual Column)을 추가하고 그 컬럼에 인덱스를 만들거나 ,
◦
테이블 구조 변경 없이 함수 표현식 자체를 인덱스 정의에 직접 사용할 수 있어요 .
◦
WHERE 절에 인덱스 생성 시 사용한 표현식이 그대로 사용되어야 인덱스를 타요 .
5. 멀티 밸류 인덱스 (MySQL 8.0+)
•
쉽게 말해: 하나의 데이터 안에 들어있는 여러 값(예: 취미 목록, 태그 목록) 각각에 대해 인덱스를 만드는 기능이에요 .
•
자세히:
◦
주로 JSON 데이터 타입의 배열 필드 내 원소들을 인덱싱할 때 사용돼요 .
◦
예를 들어 {"tags": ["mysql", "database", "index"]} 같은 데이터에서 "mysql", "database", "index" 각각으로 해당 데이터를 찾을 수 있게 해줘요.
◦
MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() 같은 전용 함수를 사용해야 인덱스가 활용돼요 .
6. 클러스터링 인덱스 (InnoDB)
•
쉽게 말해: InnoDB 테이블에서 프라이머리 키(PK) 자체가 데이터의 물리적인 저장 순서를 결정하는 방식이에요 .
•
자세히:
◦
PK 값이 비슷한 레코드끼리 디스크에 묶여서 저장돼요 .
◦
PK 기반 검색(특히 범위 검색)이 매우 빨라요.
◦
세컨더리 인덱스(PK 외 다른 인덱스)는 데이터 주소 대신 PK 값을 가져요. 그래서 세컨더리 인덱스로 검색 시, PK로 다시 한번 검색하는 과정이 필요해요 .
◦
PK 값이 변경되면 데이터의 물리적 위치가 바뀌어야 해서 PK 변경이나 INSERT 성능은 상대적으로 느릴 수 있어요 .
◦
테이블당 하나만 존재해요.
7. 유니크 인덱스
•
쉽게 말해: 특정 컬럼(들)에 중복된 값이 들어가지 못하도록 막는 제약 조건 역할 + 인덱스 역할이에요 .
•
자세히:
◦
프라이머리 키(PK)는 기본적으로 유니크 인덱스 + NOT NULL 제약이에요.
◦
읽기 성능은 일반 B-Tree 인덱스와 거의 차이가 없어요 (어차피 찾는 값이 하나뿐) .
◦
쓰기 성능은 중복 값 체크 과정이 추가로 필요해서 일반 인덱스보다 느려요. 특히 InnoDB의 체인지 버퍼 같은 쓰기 최적화 기능을 사용하지 못해요 .
◦
NULL 값은 여러 개 저장될 수 있어요 (NULL은 특정 값이 아니므로).
◦
꼭 필요한 경우가 아니라면 성능상 이점은 크지 않으므로 남용하지 않는 것이 좋아요 .
클러스터드 vs 논클러스터드
클러스터드 인덱스
•
데이터가 인덱스 순서대로 물리적으로 정렬됨
•
InnoDB에서 PRIMARY KEY가 자동으로 클러스터드 인덱스
•
테이블당 1개만
•
데이터 자체가 정렬되어 있어 빠름
논클러스터드 인덱스 (세컨더리 인덱스)
•
데이터는 그대로, 인덱스만 따로 존재
•
일반 INDEX, UNIQUE가 여기 해당
•
여러 개 생성 가능
•
인덱스 → PRIMARY KEY → 실제 데이터 순으로 접근
예상 질문
무조건 인덱스를 많이 거는 것이 좋을까?
인덱스의 단점으로 Create, Update, Delete 속도가 느려진다.
B-Tree자료구조를 위해 추가적인 디스크 공간을 필요로 하기 때문에 공간 낭비가 발생할 수 있다.
인덱스를 걸었지만 인덱스를 타지 않고, Full Scan되는 경우?
Full Scan : 인덱스를 사용하지 않고 모든 레코드를 탐색하는 것으로 실행계획의 type에 ALL로 표시됨.
1. 데이터가 적을 때
•
테이블 크기가 작으면 인덱스 사용보다 풀스캔이 더 빠름
•
테이블 데이터 페이지가 1페이지인 경우, 옵티마이저가 자동으로 판단
2. 옵티마이져 자체 판단
•
이유: 인덱스를 읽는 것도 비용(I/O)입니다. 인덱스에서 위치를 찾고 다시 실제 데이터 블록을 가져오는 과정보다, 그냥 처음부터 끝까지 데이터를 쭉 읽는(Full Scan) 것이 더 빠르다고 판단되면 인덱스를 버립니다. 보통 전체 데이터의 20~25% 이상을 가져올 때 이런 현상이 발생합니다.
3. 인덱스 컬럼을 가공한 경우
WHERE YEAR(created_at) = 2024 -- 풀스캔
WHERE created_at >= '2024-01-01' -- 인덱스 사용
SQL
복사
•
이유: 인덱스는 컬럼의 원래 값을 기준으로 정렬되어 저장됩니다. 값을 함수로 가공해버리면, 정렬된 인덱스 구조 내에서 해당 결과값이 어디에 위치하는지 MySQL이 알 수 없게 됩니다.
4. OR 조건 사용
WHERE name = 'Kim' OR age = 30 -- 인덱스가 따로 있어도 풀스캔 가능
SQL
복사
•
이유: WHERE A = 1 OR B = 2에서 A에 인덱스가 있어도 B에 없다면, 어차피 B 조건을 확인하기 위해 전체 테이블을 읽어야 합니다. 옵티마이저는 "어차피 전체를 읽을 거라면 인덱스를 타는 수고를 하지 말자"고 결정하게 됩니다.
5. LIKE 패턴이 앞에서 시작 안 할 때
WHERE name LIKE '%Kim' -- 풀스캔
WHERE name LIKE 'Kim%' -- 인덱스 사용
SQL
복사
•
이유: MySQL의 B-Tree 인덱스는 왼쪽부터 오른쪽 순서로 문자열을 정렬합니다. 뒷부분이 %인 경우(접두사)는 정렬된 순서를 따라갈 수 있지만, 앞부분이 %이면 어디서 시작할지 판단할 기준점이 없어 전체를 뒤져야 합니다.
6. 부정 조건 사용
WHERE name != 'Kim'
WHERE age NOT IN (10, 20)
SQL
복사
•
이유: 인덱스는 "이 값이 어디에 있다"를 빠르게 찾기 위한 지도입니다. 반대로 "이 값이 아닌 것들"은 지도의 거의 모든 곳에 흩어져 있기 때문에, 특정 지점을 타격해서 찾는 방식인 인덱스 탐색(Index Seek)이 불가능합니다
7. 복합 인덱스의 첫 컬럼을 안 쓸 때
-- INDEX(name, age)일 때
WHERE age = 30 -- 풀스캔 (name이 없음)
SQL
복사
•
이유: 복합 인덱스는 첫 번째 컬럼으로 먼저 정렬된 후, 그 안에서 두 번째 컬럼이 정렬되는 계층적 구조입니다. 첫 번째 기준(A)이 없으면 두 번째 기준(B)은 전체 데이터에 걸쳐 무작위로 섞여 있는 것이나 다름없어 인덱스 효과가 사라집니다.
참고자료
