데이터 엔지니어로서 효율적인 DB관리는 매우 중요하다. 이 중 인덱스 튜닝을 해서 DB 성능을 높이고자 하는데 직방에서도 이와 같이 SQL의 비효율을 제거하는 고민을 가졌다. 인덱스는 테이블의 특정 열에 대한 검색 및 정렬 속도를 향상시키는 데 사용된다. 따라서 인덱스 튜닝은 DB 성능을 향상시키는 중요한 역할이므로 어떻게 해결해나갔는지 알아보자.
기술 블로그 출처
https://medium.com/zigbang/mysql-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%ED%8A%9C%EB%8B%9D-18e183e9246d
MYSQL 인덱스 튜닝
안녕하세요. 호갱노노BE팀 폴 입니다.
medium.com
SQL 튜닝 개요
SQL 튜닝은 동일한 쿼리 결과를 더 빠르고 더 효율적으로 얻기 위한 모든 작업을 의미한다. 이는 CPU 시간이나 Black I/O등과 같은 성능 지표를 통해 평가된다. 즉, 동일한 결과를 얻는 데 DBMS가 사용한 자원이 얼마나 적은지가 성능 좋은 쿼리의 기준이 된다.
SQL 튜닝의 주요 포인트로는 인덱스 튜닝, 조인 튜닝, 소트 튜닝, DML 튜닝 등이 있다. 이 중 인덱스 튜닝에 대해 다뤄보도록 한다.
인덱스 튜닝 세부 분류
- 인덱스를 타지 않는 경우
- 인덱스가 없을 때
- 인덱스 생성
- 인덱스가 있을 때
- 인덱스 컬럼 가공으로 인한 성능저하 여부 확인
- 인덱스 손익분기점 확인 (테이블 풀스캔이 더 효율적인지 확인)
- 인덱스가 없을 때
- 인덱스를 타는 경우
- 인덱스 스캔 비효율 (인덱스 스캔량이 최종 rows보다 훨씬 많을 때)
- 인덱스 필터링으로 동작하는 인덱스 스캔 개선
- 인덱스 스캔 비효율은 없지만, 테이블 엑세스가 많을 때
- 인덱스만 읽고 멈추도록 개선
- 인덱스 스캔 비효율 (인덱스 스캔량이 최종 rows보다 훨씬 많을 때)
- 불필요한/중복인 인덱스 제거 or 통합
- DML 성능 향상
- 인덱스 다이브 성능 개선
이 중 아래 4가지 항목에 대한 사례와 함께 살펴보도록 한다.
- 인덱스가 있는데도 인덱스를 안 타는 상황 → 인덱스 손익 분기점
- 인덱스를 타지만 성능이 안 나오는 상황 → 인덱스 스캔 비효율
- 인덱스 스캔 비효율은 없지만 성능이 안 나오는 상황 → 테이블 랜덤 엑세스 최소화
- 인덱스가 너무 많은 상황 → 중복 인덱스 최적화
1. 인덱스가 있는데도 안 타는 상황 → 인덱스 손익 분기점
인덱스 스캔에서 테이블 풀 스캔으로의 변경은 주로 옵티마이저가 실행 계획을 선택할 때 발생하는 현상이다.
이 상황과 관련하여 Mysql 공식문서에서는 아래와 같이 4가지 상황을 가이드하고 있다.
- 테이블이 작을 경우 테이블 스캔이 키 조회보다 빠를 수 있음
- 인덱스된 열에 유용한 제한 사항이 없음
- 상수가 테이블의 큰 부분을 포함하여 인덱스 키 조회보다 테이블 스캔이 더 빠름
- 열의 카디널리티가 낮은 키를 다른 열을 통해 사용할 때 인덱스 키 조회보다 테이블 스캔이 더 빠르다고 가정함
인덱스 손익 분기점이란?
인덱스 스캔보다 테이블 풀스캔 비용이 더 낮아지는 지점
테이블 풀 스캔의 비용은 일정하지만, 인덱스 스캔은 조회 건수에 비례하여 쿼리 구조나 시스템 상황에 따라 변동된다. 이런 변화로 옵티마이저는 어느 지점에서는 인덱스를 사용하지 않는 실행계획을 선택하게 된다.
인덱스 스캔은 소량의 데이터를 찾는 목적에서 잘 동작한다. 많은 양의 데이터를 조회하는 쿼리에서는 적합하지 않으므로 다른 조회 전략을 찾는게 유리할 수 있다. 즉, 인덱스 스캔에서는 인덱스트리를 탐색하는 비용과 최종 인덱스 리프노드에서 뽑은 clustered index key로 테이블 로우를 읽는 비용을 합한 비용이 발생하고, 테이블 풀 스캔에서는 전체 테이블 로우를 다 읽는 비용만 발생한다.
따라서 인덱스로 많은 대상을 찾으면 테이블 풀스캔보다 인덱스를 찾는 비용이 더 많이 발생할 수 있으며, 강제로 인덱스를 사용하도록 힌트를 지정하는 것이 오히려 더 많은 IO 비용을 발생시킬 수 있다. 테이블 풀 스캔이 항상 나쁜 것은 아니니 상황에 따라 최적의 스캔 방법을 선택하는 것이 중요하다.
2. 인덱스를 타는데 성능이 안 나오는 상황 → 인덱스 스캔 비효율
인덱스 스캔 자체에서 비효율이 발생하는 경우 인덱스를 잘 타도 성능이 떨어질 수 있다. 복합 인덱스에서는 인덱스 구성 컬럼에서 범위조건 이 사용되면, 해당 컬럼 이후의 컬럼들은 인덱스 스캔이 '액세스'가 아닌 '필터링'으로 동작한다.
예를 들어, 동일 테이블 동일 인덱스에서 아래와 같이 동작할 수 있다.
- 100건 스캔 → 5건 필터링 → 5건 테이블 엑세스
- 20건 스캔 → 5건 필터링 → 5건 테이블 엑세스
복합 인덱스의 경우 인덱스 선행컬럼이 조건절에 없거나 = 조건이 아니면, 인덱스 스캔 과정에 비효율이 발생한다. 인덱스 스캔은 액세스 조건과 필터 조건으로 구분되어 동작한다.
- 인덱스 엑세스 조건 : 인덱스 스캔 범위 결정
- 인덱스 필터 조건 : 테이블 엑세스 여부 결정
- 테이블 필터 조건 : 최종 결과집합 포함 여부 결정
인덱스 스캔으로 동작하지만 실제 비효율적인 동작이 있는지 explain analyze 를 통해 한번 더 확인해본다면 예상하지 못한 성능저하를 예방할 수 있다.
3. 인덱스 스캔 비효율은 없지만 성능이 안 나오는 상황 → 테이블 랜덤 액세스 최소화
데이터베이스의 인덱스 스캔이 효율적이지만 성능이 안 나오는 경우가 있다. 이는 실제 데이터 스캔이 많은 상황에서 발생하는데, 이 경우 SQL 외적인 방법보다는 SQL 레벨에서 성능을 개선하는 것이 유용할 수 있다.
쿼리가 인덱스만으로 처리되어 테이블 랜덤 액세스가 사라지면 관련 비용이 줄어든다. 커버링 인덱스를 사용하여 실제 io를 확인해보면, 더 많은 io를 줄일 수 있다. 그러나 과도한 복잡한 인덱스 생성은 다른 문제를 야기할 수 있으니 조심해야 한다.
* 커버링 인덱스: 쿼리 실행에 필요한 모든 컬럼을 인덱스에 포함하는 특수한 종류의 인덱스이다. 이러한 인덱스는 쿼리의 필요한 데이터를 인덱스 자체에서만 가져와서 처리할 수 있기 때문에 추가적인 테이블 액세스가 필요하지 않다. 즉, 인덱스만을 스캔하여 쿼리를 완료할 수 있다.
데이터베이스는 인덱스를 사용하여 쿼리 결과를 가져오기 때문에 테이블 전체를 스캔하는 대신 인덱스를 스캔하면 쿼리 성능을 향상 시킬 수 있다. 그러나 몇 가지 고려해야 할 사항이 있다.
- 커버링 인덱스는 인덱스의 크기를 증가시킬 수 있으며, 이는 인덱스의 업데이트 및 관리 비용이 증가할 수 있다.
- 쿼리의 필요한 모든 컬럼을 포함하는 인덱스를 만들어야 하므로 인덱스의 디자인을 고려해야 한다.
- 모든 쿼리에 대해 커버링 인덱스를 만들 수 있는 것은 아니며, 쿼리의 패턴과 요구사항에 따라 적절한 인덱스를 선택해야 한다.
4. 인덱스가 너무 많이 있는 상황 → 중복 인덱스 최적화
인덱스를 줄이는 것도 인덱스 튜닝이 될 수 있다. 너무 많거나 불필요한 인덱스는 DML 작업의 성능을 저하시킬 수 있다. 또한 저장 공간을 낭비하고 의도하지 않은 인덱스 선택으로 인해 부작용이 발생할 수 있다. 즉, 중복된 인덱스를 제거하고 최적화된 인덱스를 사용하는 테이블과의 쿼리 프로파일을 비교하여 이러한 튜닝 작업의 효과를 확인할 수 있다.
- 불필요한 인덱스로 인해 DML 쿼리에서 상당한 비효율이 발생할 수 있음
- DML 성능 뿐만 아니라, 저장공간 낭비도 발생할 수 있음
인덱스는 꼭 필요한 조회경로에 대해서만 생성하는 것이 좋다. 특히 중복 인덱스의 경우, 조회/갱신 양쪽 모두에서 불필요한 성능저하가 발생할 수 있기 때문에 중복 인덱스가 있다면 인덱스 정리를 고려해보는 것이 좋다.
튜닝의 핵심
튜닝의 핵심은 이슈 상황에 따라 어떤 지점에서 어떤 비용이 많이 발생하는 지 파악하는 것이 가장 중요하다.
- explain, explain analyze 등을 통해 쿼리가 어떻게 동작하는 지 확인
- 쿼리 프로파일링을 통해 실제 시스템에서 cpu, block io등이 얼마나 발생하는 지 확인
- 비효율을 제거할 수 있는 포인트 검색
- 해당 지점을 효율적으로 동작하도록 개선
