MySQL 8 버전에서 사용되는 Index(인덱스) 기능에 대해 자세히 알아보겠습니다. 이 내용은 MySQL 8 버전을 기준으로 하며, InnoDB 스토리지 엔진을 주로 다룰 것입니다.
1. InnoDB의 Index 알고리즘은 B-Tree 알고리즘을 사용한다.
InnoDB 스토리지 엔진에서는 기본적으로 B-Tree 알고리즘을 사용합니다. 이 알고리즘은 트리 형식으로 데이터를 정렬하며, 원하는 값을 찾을 때 상단부터 시작하여 작으면 왼쪽으로, 크면 오른쪽으로 이동하면서 탐색하는 방식입니다.
2. 인덱스 압축 기능을 비활성화 할 수 있다.
InnoDB는 인덱스 페이지를 압축하여 디스크 공간을 절약하는 기능을 제공합니다. 그러나 이 기능은 CPU 리소스를 사용하기 때문에 추가, 변경, 삭제 작업이 많을 때 부하를 줄 수 있습니다. 이런 경우에는 "ALTER TABLE tablename ROW_FORMAT=DYNAMIC;" 명령을 사용하여 인덱스 페이지 압축을 비활성화할 수 있습니다.
3. 클러스터링 인덱스를 사용할 수 있다.
클러스터링 인덱스는 일반 인덱스와는 다르게 인덱스 키로 정렬된 데이터를 직접(정렬된 상태로) 저장합니다. index 페이지를 사용하지 않는 방법으로 이렇게 하면 메모리 사용량을 줄이고, 물리적으로 데이터를 정렬하여 추가적인 정렬 작업을 줄일 수 있습니다. 단, 클러스터링 인덱스는 PRIMARY KEY에만 적용됩니다.
4. 문자열 검색보다 숫자 검색이 빠르다.(비교적)
문자열 검색은 B-Tree 구조에 의해 한 글자씩 검색됩니다. 예를들어 "abC09"라는 값을 index를 활용해서 검색해보겠습니다. B-Tree 구조에 의해 'a', 그 다음에 'b', 그 다음에 'C', 그리고 나머지 문자들이 순서대로 정렬됩니다.
숫자 검색은 숫자 자체를 검색하기 때문에 비교적 빠릅니다.
5. `like`문에 index를 태우는 방법.
InnoDB는 가능하면 index를 활용하려 합니다.
LIKE 문을 사용할 때, 와일드카드 문자(%, _)로 시작하는 경우 인덱스를 활용할 수 없습니다. 하지만 와일드카드가 뒤에 있는 경우에는 인덱스를 활용할 수 있습니다.
- 'abc%'와 같은 패턴은 3번의 인덱스를 활용할 수 있습니다.
- 'a%bc'와 같은 패턴은 1번의 인덱스를 활용할 수 있습니다.
- '%abc'와 같은 패턴은 인덱스를 활용할 수 없습니다.
6. `in`절에 너무 많은 인수가 들어가면 full scan 방식으로 변경된다.
IN 절에 너무 많은 인수가 포함되면 Full Scan 방식으로 변경될 수 있습니다.
방금 MySQL 8.2.0에서 테스트 해 본 결과 30개까지는 무난하게 인덱스가 사용되었습니다. 이는 버전에 따라 변경될 수 있으므로 꼭 테스트를 해보시는게 중요할 것 같습니다.
7. 데이터가 적다면 Index가 오히려 독이 된다.
데이터 양이 적은 경우에는 Index를 사용하는 대신 Full Scan 방식을 사용하는 것이 효율적일 수 있습니다. 삽입/삭제 index페이지 저장 등의 작업에 Index를 유지하기 위한 비용이 발생하므로 데이터의 크기와 사용 패턴을 고려해야 합니다.
8. Index가 능사는 아닐 수 있다. (개인 견해)
업무 중에 있었던 일입니다. A, B, C, D라는 컬럼 중 A, B, C 각각을 검색해 batch 를 돌리는 업무였습니다. 처음에는 A, B, C 각 열에 인덱스를 생성하면 되는 아주 교과서 적인 문제라고 생각했는데,, 막상 데이터를 검색해보니 200~300ms정도가 걸리더군요.. 30ms가 제가 원하는 속도의 마지노선이였고,
실제로 인덱스를 생성한 후에 실행해보니 애플리케이션 실행에 30시간이 걸렸습니다. 이런 문제는 데이터가 600만 건 이상이며 DB 서버의 사양이 좋지 않았기 때문에 발생한 것으로 생각됩니다.
그런데, 저는 이 문제를 해결하기 위해 Redis를 사용하여 8분만에 실행할 수 있도록 변경했습니다.이게 최선의 방법인지는 확신할 수 없지만, 인덱스가 항상 최적의 솔루션이 아님을 알았습니다.
긴 글 읽어주셔서 감사합니다.
'Database' 카테고리의 다른 글
| 안정성과 성능 최적화를 위한 Java와 데이터베이스에서의 Lock 전략 (1) | 2024.10.13 |
|---|---|
| 데이터베이스 프로시저, 함수, 뷰: 각각의 역할과 사용법 (2) | 2023.12.26 |
| 트랜잭션 격리 수준의 종류와 성능 최적화 전략 (0) | 2023.09.02 |
| Redis 트랜잭션: MULTI부터 EXEC까지 트랜잭션 처리 과정 (0) | 2023.08.04 |
| Redis에서 데이터 영구 보존: 스냅샷과 AOF로 안전한 복구 시스템 구축 (0) | 2023.08.04 |