인덱스란?
내가 대답 잘 못해서 떨어진 인덱스란 놈에 대해서 조금 더 알아보자…
- 기본 개념
- 인덱스는 테이블의 특정 컬럼 또는 여러 컬럼에 대해 정렬된 데이터를 유지하는 구조로, 조회 성능을 향상시킨다.
- 인덱스를 사용하면 DB는 전체 테이블을 스캔하지 않고도 필요한 데이터를 빠르게 찾을 수 있게 된다.
- 장점
- 조회 성능 향상: SELECT 쿼리 시 WHERE, JOIN, ORDER BY, GROUP BY 절에 자주 사용되는 컬럼에 인덱스를 생성하면 성능이 대폭 향상될 수 있다.
- 단점
- 쓰기 성능 저하: 인젝스가 추가될수록 INSERT, UPDATE, DELETE 작업 시에는 성능이 저한된다. 각 작업이 일어날 때마다 인덱스를 갱신해야 하기 때문이다.
- 단일 인덱스 VS 복합 인덱스
- 단일 인덱스: 한 컬럼에 대해 인덱스를 생성하는 방식이다.
- 복합 인덱스: 두 개 이상의 컬럼에 대해 인덱스를 묶어서 생성하는 방식이다. 예를 들어, name, email 컬럼에 복합 인덱스를 생성하면, name과 email 모두를 기준으로 한 검색에서 빠르게 결과를 찾을 수 있다. 복합 인덱스는 앞 부분에 위치한 컬럼부터 차례로 최적화가 적용되므로 컬럼 순서를 신중히 선택해야 한다.
CREATE INDEX idx_name_age ON user (name, age);
- 이런 식으로 복합 인덱스를 작성한 경우 name으로 검색하거나 name, age를 함께 검색할 때 성능이 향상되지만, age만으로 검색하는 경우에는 인덱스가 사용되지 않을 수 있다.
- 인덱스 설계 시 고려 사항
- 자주 사용하는 컬럼에만 인덱스를 생성
- 인덱스를 생성하면 읽기 성능은 향상되지만, 쓰기 성능은 저하될 수 있기 때문에 자주 사용되지 않는 컬럼에 대해 인덱스를 만드는 것은 오히려 성능에 악영향이다.
- 인덱스는 주로 WHERE에 자주 들어가는 컬럼, JOIN, GROUP BY, ORDER BY에 자주 사용되는 컬럼에 대해 생성하는 것이 좋다.
- 선행 컬럼의 중요성
- 복합 인덱스를 사용할 떄는 첫 번째 컬럼이 매우 중요하다. 인덱스는 첫 번째 컬럼에 대한 정렬 순서를 기준으로 생성되므로, 주로 많이 검색에 사용되는 컬럼을 첫 번째로 두어야 한다.
- 쿼리 패턴 분석
- 어떤 쿼리가 자주 실행되는지, 그 쿼리가 어떤 컬럼을 사용하는지 분석한 후, 적절한 인덱스를 설계해야 한다. 인덱스를 만들기 전에 자주 실행되는 쿼리를 확인하고, 인덱스를 어디에 생성할지 계획하는 것이 좋다.
- EXPLAIN 명령어를 사용하여 쿼리 실행 계획을 분석하고 인덱스가 실제로 사용되는지 확인하는 것도 중요하다.
EXPLAIN SELECT * FROM user WHERE name = "pure"
;- 카디널리티
- 인덱스의 성능은 카디널리티(유니크한 값의 수)와 밀접한 관계가 있다. 인덱스가 효과를 발휘하려면 해당 컬럼의 값이 다양해야 한다. 예를 들어 성별처럼 값이 ‘남성’, ‘여성’ 두 개밖에 없다면, 이 컬럼에 인덱스를 생성해도 성능이 크게 개선되지 않는다. 반면에 유니크한 값이 많은 컬럼은 인덱스의 효율이 높다.
- 예를 들어, 가입 유저 검색 시에 핸드폰 번호나 이메일에 인덱스를 거는 것이 좋아 보인다.
- 클러스터형 인덱스 vs 비 클러스터형 인덱스
- 클러스터형 인덱스: 테이블의 실제 데이터가 인덱스에 따라 물리적으로 정렬되는 방식이다. 보통 기본 키에 클러스터형 인덱스가 사용된다.
- 비 클러스터형 인덱스: 데이터는 인덱스 순서대로 정렬되지 않고, 인덱스에서 데이터 페이지로의 포인터를 통해 검색하는 방식이다. 보조 인덱스로 사용된다.
- 복합 인덱스를 활용
- 여러 개의 단일 인덱스보다는 복합 인덱스를 활용하는 것이 효과적일 때가 많다. 대신 컬럼의 순서를 신경써서 정해야 한다.
- 커버링 인덱스(Covering Index)
- 쿼리에서 필요한 모든 데이터를 인덱스 자체가 가지고 있을 경우, 디스크에서 테이블 데이터를 읽을 필요 없이 인덱스만으로도 쿼리를 처리할 수 있다. 이를 커버링 인덱스라고 하며, 성능 향상에 큰 도움이 될 수 있다. (마치 카운팅 정렬)
- 인덱스 재구성 및 통계 갱신
- 데이터가 대규모로 변경되면 인덱스가 비효율적으로 변할 수 있다. 이 경우 인덱스를 재구성하거나 DB에서 자동으로 인덱스를 최적화하도록 설정할 수 있다.
REINDEX INDEX idx_name_age;
- 인덱스가 무조건 성능을 개선해 주는 것은 아니다. 잘못된 인덱스 설계는 오히려 성능을 저하시킬 수 있으며, 지나치게 많은 인덱스는 데이터 변경 작업의 성능을 떨어뜨린다. 따라서 데이터베이스 성능을 최적화할 때는 쿼리 패턴을 분석하고, 적절한 인덱스를 선택하는 것이 중요하다.
4. 인덱스 최적화 팁
CREATE INDEX idx_name_age_salary ON user (name, age, salary); SELECT name, age, salary FROM user WHERE name = 'John';
5. 인덱스의 한계
인덱스 최적화 with JPA
나는 DB를 설계할 때 DB 자체의 무언가를 하기보다는 JPA를 사용하다 보니 보통 ddl-auto로 자동생성해서 사용했다.
또한 대용량 트래픽 처리가 필요하지 않은 환경만 개발하다 보니 더욱 더 이 부분에 대해 생각을 못 했다.
요즘 면접을 다니다 보니 이런 부분에 대해 기본적으로 알고 있지 않으면 어딘가에 취업할 수가 없겠다는 생각이 들었다.
그래서 인덱스 최적화를 조금 찾아봤다.
JPA를 사용하면서도 직접 인덱스를 만들어 줄 수 있다는 것을 알게 되었다.
예를 들어 User 엔티티가 있으면 아래와 같이 직접 지정할 수 있다.
@Entity @Table(name = "user", indexes = { @Index(name = "idx_user_name", columnList = "name"), @Index(name = "idx_user_email", columnList = "email") }) public class User { // 필드 및 메서드 정의 @Column(name = "name") private String name; @Column(name = "email") private String email; }
@Index 어노테이션을 이용해서 인덱스 이름을 지정하고 어떤 컬럼에 인덱스를 걸지 지정할 수 있다.
위의 케이스는 name컬럼, email컬럼에 각각 단일 인덱스를 건 경우이다.
@Entity @Table(name = "user", indexes = { @Index(name = "idx_user_name_email", columnList = "name, email") }) public class User { // 필드 및 메서드 정의 @Column(name = "name") private String name; @Column(name = "email") private String email; }
이렇게 columnList에 컬럼을 여러개 넣는 경우에는 복합 인덱스를 생성할 수 있다.