인덱스 설계를 통한 쿼리속도 튜닝하기

인덱스는 데이터베이스 성능을 향상시키고 쿼리 속도를 향상시키는 중요한 역할을 합니다. 인덱스를 설계할때 주의할 점에 대해 짚어보겠습니다.

 

1. 선택적 인덱스 사용

가장 뻔한 이야기긴 하지만 인덱스를 설계할 때, 자주 사용되는 열을 선택적으로 인덱싱해야 합니다. 불필요한 인덱스는 성능 저하의 원인이 될 수 있습니다.

나쁜 예시	좋은 예시
모든 열에 인덱스를 추가하면 인덱스 크기가 커져 디스크 공간 낭비와 성능 저하 발생 가능.	주문 테이블에서 주문일자 열에만 인덱스를 추가하면 주문일자로의 검색에 최적화.

 

2. 복합 인덱스 활용

복합 인덱스는 여러 열을 함께 인덱싱하는 것으로, 여러 조건을 동시에 사용하는 쿼리를 최적화할 수 있습니다.

나쁜 예시	좋은 예시
각각 단일 열에 별도의 인덱스 생성 => 이름 열과 성별 열에 각각 단일 인덱스를 만들 경우, 복합 검색 성능이 저하.	이름과 성별 열을 복합 인덱스로 만들면 이름과 성별로의 검색에 효과적일수 있음

 

3. 인덱스 크기 고려

인덱스 크기가 크면 디스크 공간을 차지하고 I/O 비용을 증가시킬 수 있습니다. 필요한 열만 인덱싱해야 합니다

나쁜 예시	좋은 예시
긴 텍스트 열에 전체 텍스트 인덱스 생성 => 전체 텍스트 인덱스를 만들면 인덱스 크기가 매우 커져서 성능에 좋지않음	긴 텍스트열에서 자주 검색이 필요한 부분을 가공하여 저장 후 인덱스 생성 긴 문자에 대한 검색이 꼭 필요한 경우에는 인덱스가 아닌 elastic search 와 같이 다른 방법을 고려해보는 것이 좋을 수 있음

 

4. 인덱스 유형 선택

• MySQL은 다양한 인덱스 유형(B-tree index, hash index...)을 지원하며, 데이터의 특성에 따라 적절한 인덱스를 선택해야 합니다.
• 인덱스 유형에 대한 설명을 보고싶다면 아래 접은 글 참조

해시 인덱스 (Hash Index)

해시 인덱스는 빠른 검색을 제공하는 데 사용되는 인덱스 유형입니다. 해시 함수를 사용하여 데이터를 해시값으로 변환하고 해당 해시값을 기반으로 데이터를 저장 및 검색합니다.

특징

1. 빠른 검색: 해시 인덱스는 일반적으로 빠른 검색을 제공하며, 데이터베이스 테이블의 크기에 관계없이 일정한 검색 속도를 유지합니다.
2. 등값 검색: 해시 인덱스는 등값 검색 (Equality Search)에 효과적이며, 주로 식별자와 같이 유일한 값을 검색하는 데 사용됩니다.
3. 해시 충돌: 해시 충돌이 발생할 수 있으며, 두 개 이상의 데이터가 같은 해시값을 가질 때 문제가 될 수 있습니다.

예시:

• 사용자 테이블에서 사용자 ID에 대한 해시 인덱스를 생성하고, 특정 사용자를 ID를 기반으로 검색할 때 빠른 검색 제공.

 

B-트리 인덱스 (B-Tree Index)

B-트리(균형 트리) 인덱스는 데이터를 정렬된 트리 구조로 저장하는 인덱스 유형입니다. B-트리는 많은 데이터베이스 관리 시스템에서 기본 인덱스 유형으로 사용됩니다.

특징

1. 범위 검색: B-트리 인덱스는 범위 검색 (Range Search)에 효과적이며, 정렬된 데이터에서 범위를 검색할 때 유용합니다.
2. 균형된 트리: B-트리는 균형된 트리 구조로 데이터를 저장하므로 검색과 삽입이 균형적으로 이루어집니다.
3. 인덱스 크기: B-트리 인덱스는 해시 인덱스보다 크기가 크며, 디스크 공간을 더 차지합니다.

예시:

• 주문 테이블에서 주문 일자에 대한 B-트리 인덱스를 생성하고 특정 기간 내의 주문을 검색할 때 효과적으로 범위 검색을 수행.

요약:

• 해시 인덱스는 빠른 등값 검색에 유용하며 해시 충돌에 주의해야 합니다.
• B-트리 인덱스는 범위 검색과 정렬된 데이터에 효과적이며 인덱스 크기가 크다는 점에 주의해야 합니다.
• 데이터베이스의 사용 사례와 쿼리 패턴에 따라 해시 인덱스 또는 B-트리 인덱스를 선택해야 합니다.

나쁜 예시

정렬된 열에 해시 인덱스를 사용하면 범위 검색 시 성능 저하.

 

5. 인덱스의 순서 고려:

• 인덱스의 열 순서는 중요합니다. 쿼리의 조건에 따라 가장 선택적인 열을 앞에 두는 것이 효과적일 수 있습니다.
• 인덱스 탐색시 인덱스 순서에 따라 스캔할 데이터 범위를 좁히는 방식으로 동작하므로 가장 앞에 스캔범위를 줄일 수 있는 컬럼을 넣는것이 좋습니다.

나쁜 예시

복합 인덱스 순서를 역으로 생성하면 사용하는 쿼리 성능 저하. => (고객 ID, 주문일자) 또는 (고객ID)로 검색하는 경우인데 (주문일자, 고객 ID) 로 인덱스를 만들어놓으면 비효율적

 

6. 주기적인 인덱스 최적화:

• 테이블에 적재된 데이터 성격과 상황, 쿼리에 따라 옵티마이저가 설정하는 실행계획이 달라지는 경우가 있습니다.
• 데이터의 변경에 따라 인덱스도 최적화되어야 합니다. 주기적으로 인덱스 재구축 또는 최적화를 수행해야 합니다.

 

7. 테스트와 성능 모니터링:

• 인덱스를 설계한 후에는 실제 데이터로 테스트하고 성능을 모니터링하여 조정이 필요한 경우 적절히 대응해야 합니다.
• 추가로 대용량 데이터베이스에 새로운 인덱스를 추가했다면 내가 의도한대로 인덱스가 동작하는지 반드시 확인해봐야 합니다.

 

8. SQL 쿼리 작성 최적화:

• 인덱스를 활용하기 위해 SQL 쿼리를 최적화해야 합니다. 불필요한 조인, 서브쿼리, 데이터 형변환을 피해야 합니다.

 

 

인덱스 설계는 데이터베이스 성능에 큰 영향을 미치므로 주의 깊게 고려해야 합니다.

데이터의 특성과 쿼리 패턴을 고려하여 최상의 성능을 얻을 수 있는 인덱스를 설계하고 유지 보수해야 합니다.