MySQL 성능 최적화 - 고성능 시스템 구축을 위한 전략과 최적화 기법 - 07. 고성능을 위한 인덱싱
해당 포스팅은 위의 책을 읽고 정리한 내용입니다.
인덱싱 기본
인덱스는 책의 목차와 같은 역할.
데이터베이스에서 인덱스는 테이블의 데이터를 빠르게 조회하기 위해 사용
인덱스를 잘 설계하면 데이터 검색 속도가 크게 향상되며, 그 결과 전체 시스템의 성능이 개선
1. 클러스터형 인덱스(Clustered Index)
- 역할: 클러스터형 인덱스는 테이블의 데이터 자체가 인덱스의 리프 노드에 포함된 인덱스. InnoDB에서는 프라이머리 키가 클러스터형 인덱스로 사용.
- 구조: 클러스터형 인덱스는 보통 B-트리 구조를 사용. B-트리 구조 내에서 클러스터형 인덱스는 데이터가 물리적으로 정렬되어 저장되며, 이 인덱스를 사용해 데이터를 빠르게 검색할 수 있다.
- 특징: 클러스터형 인덱스의 리프 노드는 실제 데이터 페이지를 가리키며, 해당 데이터가 프라이머리 키 순서에 따라 정렬되어 저장된다.
2. 보조 인덱스(Secondary Index)
- 역할: 보조 인덱스는 클러스터형 인덱스 외의 다른 인덱스로, 테이블의 다른 컬럼에 대해 인덱싱된 구조. 주로 검색 조건으로 자주 사용되는 컬럼에 대해 생성.
- 구조: 보조 인덱스도 주로 B-트리 구조로 구성되지만, 클러스터형 인덱스와 달리 리프 노드에는 프라이머리 키 값이 포함되어 있으며, 실제 데이터가 포함되어 있지 않다. 이를 통해 MySQL은 프라이머리 키를 사용해 다시 클러스터형 인덱스로 접근하여 실제 데이터를 가져온다.
- 특징: 보조 인덱스를 통해 특정 컬럼 값으로 빠르게 검색할 수 있지만, 실제 데이터를 가져오기 위해서는 추가적인 접근(클러스터형 인덱스를 통한)이 필요.
3. 인덱스의 구조 유형과 역할
B-트리 인덱스:
- 구조 유형: 균형 트리(Balanced Tree)의 일종이다. MySQL의 기본 인덱스 구조로, 대부분의 인덱스는 B-트리 구조로 구현. B-트리 구조는 모든 리프 노드가 동일한 깊이에 위치하며, 데이터 검색, 삽입, 삭제 시 일정한 성능을 제공. B-트리 인덱스는 프라이머리 키 또는 인덱스된 컬럼 값을 기준으로 데이터를 정렬하여 저장. 이로 인해 범위 조회(range query)가 매우 효율적.
- https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html 해당 링크를 통해 어떻게 동작하는지 볼 수 있다.
- 예: 클러스터형 인덱스에서 프라이머리 키 기반으로 데이터가 저장되거나, 보조 인덱스에서 특정 컬럼에 대해 인덱싱될 때 사용.
적응형 해시 인덱스(Adaptive Hash Index):
- 구조 유형: InnoDB에서 자동으로 생성되는 해시 기반의 인덱스. 특정한 조건에서 클러스터형 인덱스나 보조 인덱스를 빠르게 조회하기 위해 사용.
- 예: 특정 키에 대해 반복적인 조회가 발생할 때, InnoDB가 자동으로 해시 인덱스를 생성하여 성능을 최적화합니다.
풀 텍스트 인덱스(Full-Text Index):
- 구조 유형: 텍스트 검색을 위한 특수한 인덱스 구조. 긴 텍스트 필드에서 특정 단어나 구를 검색하는 데 사용.
- 예: 블로그 게시물이나 제품 설명 등에서 키워드 검색을 최적화할 때 사용.
커버링 인덱스
커버링 인덱스란, 쿼리가 필요한 모든 데이터를 인덱스에서만 읽을 수 있는 상황을 말한다. 즉, 테이블의 실제 데이터 페이지(즉, 데이터 파일)를 조회하지 않고도 인덱스만으로 쿼리를 처리할 수 있는 경우를 의미.
CREATE TABLE SKU (
id INT PRIMARY KEY,
name VARCHAR(255),
price DECIMAL(10, 2)
);
CREATE INDEX idx_name ON SKU(name);
INSERT INTO SKU (id, name, price) VALUES (1, 'Product A', 10.00);
INSERT INTO SKU (id, name, price) VALUES (2, 'Product B', 15.00);
INSERT INTO SKU (id, name, price) VALUES (2, 'Product C', 20.00);
INSERT INTO SKU (id, name, price) VALUES (2, 'Product D', 25.00);
이렇게 데이터를 세팅 후 아래와 같은 조회를 하면
explain select name from sku where name = "Product B";
explain select id, name from sku where name = "Product B";
커버링 인덱스로 작동한다.
name이라는 필드에 인덱스를 생성했기 때문에 인덱스에 name 존재
보조 인덱스에는 리프 노드에 pk 정보를 가지고 있으므로 인덱스에 id값도 존재
-> 실제 데이터에 접근할 필요 없이 인덱스에 있는 내용만으로 결과 도출
인덱스 스캔을 사용한 정렬
MySQL에서 ORDER BY 절을 처리할 때 인덱스를 사용할지, 아니면 Filesort를 사용할지는 상황에 따라 결정
인덱스를 이용한 정렬은 MySQL이 이미 정렬된 상태로 데이터를 저장하고 있는 인덱스를 활용하여 ORDER BY 절을 처리하는 방식
인덱스를 사용하면, 데이터가 이미 정렬된 상태로 저장되어 있으므로, 데이터를 순서대로 읽기만 하면 됨. 따라서 SELECT 쿼리에서의 정렬 작업이 매우 빠르게 수행. 정렬된 데이터를 순서대로 읽기만 하면 되기 때문에 쿼리 실행 시 추가적인 정렬 작업이 필요 없고, 이는 성능을 크게 향상.
Filesort는 MySQL이 ORDER BY 절을 처리할 때 인덱스를 사용하지 않고, 쿼리가 실행되는 시점에 데이터를 메모리 또는 디스크에서 정렬하는 방식. 별도의 인덱스를 생성하지 않아도 됨. 이는 인덱스를 관리할 필요가 없으므로, 인덱스를 사용할 때의 단점이 장점으로 바뀝니다. Filesort는 쿼리 실행 시점에 정렬을 수행하므로, 정렬해야 할 데이터의 양이 많아질수록 성능이 저하될 수 있다. 특히, 데이터가 메모리에 모두 적재되지 않으면 디스크 I/O가 발생하여 더 큰 성능 저하가 발생할 수 있다.
인덱스를 사용한 정렬에는 몇 가지 한계점이 존재한다.
1) 인덱스 컬럼의 순서:
- ORDER BY 절에 사용된 첫 번째 컬럼이 인덱스의 첫 번째 컬럼과 일치해야 인덱스를 사용할 수 있다. 예를 들어, idx_price_created_at(price, created_at) 인덱스가 있는 경우 ORDER BY price, created_at은 인덱스를 사용할 수 있지만, ORDER BY created_at, price는 인덱스를 사용하지 못한다.
2) 정렬 방향의 일관성:
- MySQL은 복합 인덱스를 사용할 때, 컬럼의 정렬 방향이 모두 동일한 경우에만 인덱스를 효율적으로 사용할 수 있다. 예를 들어, ORDER BY price ASC, created_at ASC는 인덱스를 사용할 수 있지만, ORDER BY price ASC, created_at DESC는 인덱스를 효율적으로 사용할 수 없다.
3) WHERE 절과 ORDER BY의 충돌:
- WHERE 절과 ORDER BY 절에 사용된 컬럼이 일치하지 않거나, 서로 다른 인덱스를 사용할 때 인덱스를 통한 정렬이 제한될 수 있다. 이 경우, MySQL은 Filesort를 사용하여 정렬 작업을 수행할 수 있다.
4) 테이블 조인 시의 한계:
- 여러 테이블을 조인할 때, ORDER BY 절이 조인된 테이블의 컬럼을 기준으로 작동하는 경우 인덱스를 사용한 정렬이 어려울 수 있다. 특히, 조인 순서와 인덱스의 사용 여부에 따라 정렬 성능이 크게 달라질 수 있다.
우선 이정도 까지만 정리를 하고 추후 좀 더 공부를 해 내용을 채워야겠다.