인덱스 성능 최적화 👑

인덱스가 무엇인가요?

RDBMS에서 테이블의 검색 속도를 높이기 위해 사용하는 자료 구조입니다. 테이블 내의 하나 또는 여러 개의 열을 기반으로 생성되며, 특정 열에 대해 데이터를 정렬하거나 검색 효율을 높이는 역할을 합니다. 인덱스를 사용하면 Full Table Scan을 피하고, 필요한 데이터를 더 빠르게 찾을 수 있습니다. 주로 B-Tree, Hash 등의 알고리즘을 활용하여 구현되며, 검색 속도 향상뿐만 아니라 레코드 접근의 효율성을 높이는 기초를 제공합니다.

•  Full Table Scan: 테이블의 모든 행을 확인

인덱스가 동작하는 원리

데이터베이스에서 특정 열을 검색할 때, 쿼리 옵티마이저(Query Optimizer)가 인덱스를 사용할지를 판단합니다. 옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 수 있는 최적 경로(Execution Plan)를 찾는 엔진입니다. 만약 인덱스를 활용한다면, Full Table Scan 대신 인덱스 자료 구조를 통해 테이터를 조회하게 됩니다. 이로 인해 검색 속도가 빨라지고, 동시에 CPU와 I/O 자원의 소모가 감소합니다.

인덱스의 단점

검색 성능을 향상하는 데 큰 도움이 되지만, 데이터 변경 작업이 잦은 열에는 비효율적일 수 있습니다. 인덱스는 항상 정렬 상태를 유지해야 해서 삽입, 삭제, 수정 시 인덱스를 갱신하는 추가 작업이 발생합니다. 이로 인해 데이터 변경 작업의 처리 속도가 저하될 수 있습니다. 즉 인덱스는 조회(Query)가 빈번한 열에 사용하는 것이 적합하고 변경 작업이 많은 열에는 적용하지 않는 것이 바람직합니다.

카디널리티

특정 열에서 유니크한 값의 개수를 의미합니다. 인덱스 성능은 열의 카디널리티에 큰 영향을 받기 때문에 일반적으로 카디널리티가 높은 열에 인덱스를 적용하는 것이 좋습니다. 예를 들어, 주민등록번호처럼 고유한 값이 많은 열은 카디널리티가 높고, 사용여부처럼 "사용/미사용"의 두 값만 있는 열은 카디널리티가 낮습니다. 즉 높으면 인덱스 효과가 크고 낮으면 효과가 미미합니다.

요약

인덱스는 조회 성능을 크게 개선할 수 있다. 하지만 데이터 변경이 잦은 열에는 비효율적이다. 옵티마이저는 인덱스를 활용하여 효율적인 검색을 수행하게 돕는다.

---

인기 있는 콘서트 목록 조회

콘서트에서 어떤 시나리오가 자주 조회하는 쿼리이면서 복잡할까 고민.. 기존에 있는 콘서트 예약 가능 날짜/ 좌석 조회는 자주 조회 되는 쿼리가 될 수 있지만 복잡한 쿼리라 생각되지 않는다. 💡 "인기 있는 콘서트 목록 조회" 예상 시나리오를 만들어서 성능을 최적화해 보자!

테이블 명	데이터 수
예약	2,800,000
스케줄	1,800
콘서트	4,000

쿼리 프로파일링

쿼리가 수행 시 각 단계별로 작업 시간이 얼마나 걸리는지 확인할 수 있도록 MariaDB/MySQL에서 제공하는 기능이고 MySQL 5.1부터 사용 가능합니다.

테스트할 쿼리

ANALYZE select * from CONCERT_RESERVATION where reservation_status = '결제취소' and reservation_date ='2024-10-20 19:00:00';
ANALYZE select * from CONCERT_RESERVATION where reservation_status = '예약완료' and reservation_date ='2024-10-20 19:00:00';
ANALYZE select * from CONCERT_RESERVATION where reservation_status = '예약중' and reservation_date ='2024-10-20 19:00:00';
ANALYZE select * from CONCERT_RESERVATION where reservation_status = '예약대기'  and reservation_date ='2024-10-20 19:00:00';
ANALYZE select * from CONCERT_RESERVATION where reservation_status = '예약취소'  and reservation_date ='2024-10-20 19:00:00';

CREATE INDEX idx_reservation_status_date ON hhplus.CONCERT_RESERVATION (reservation_status, reservation_date);

쿼리 속도 비교

인덱스 미적용

인덱스 적용

인덱스	미적용	적용
결제취소	0.6365	0.0035
예약완료	0.2895	0.0042
예약중	0.6539	0.0012
예약대기	0.0021	0.0047
예약취소	0.0234	0.0203

"예약대기" 외 나머지는 속도가 향상된 것을 확인할 수 있습니다.

---

EXPLAIN과 ANALYZE의 차이

1. EXPLAIN
   • 쿼리 실행 계획을 예측하여 보여줍니다.
   • 쿼리를 실제로 실행하지 않음.
   • 예상되는 실행 방식, 인덱스 사용 여부 등을 확인 가능.
2. ANALYZE
   • 쿼리를 실제로 실행하여 실행 계획과 함께 실제 실행 결과 데이터를 제공합니다.
   • 실행 후 r_rows(읽은 행의 수)와 r_filtered(조건 적용 후 남은 비율) 등의 정보를 확인 가능.

항목	EXPLAIN	ANALYZE
실행여부	실행하지 않음	쿼리를 실제로 실행함
데이터 유형	예상 실행 데이터	실제 실행 데이터
활용 목적	실행 계획 예측 및 분석	실행 계획 + 실제 성능 분석

ANALYZE의 장점

1. 정확성:
   • 실제 실행 데이터를 포함하므로 EXPLAIN보다 정확한 성능 분석 가능.
2. 인덱스 성능 확인:
   • 인덱스를 사용할 경우, r_rows 값이 줄어들어 성능 개선 효과를 확인할 수 있음.
3. 실제와 예상 비교:
   • 쿼리 실행 계획(EXPLAIN)과 실제 실행 결과를 비교하여 문제점을 진단 가능.

ANALYZE 선택한 이유

ANALYZE는 EXPLAIN의 기능을 포함하고 실행 계획과 실제 실행 결과를 비교하여 정확한 성능 분석을 제공하기 때문입니다.

예약 중, 예약대기 분석

예약중 인덱스 미적용

테이블의 데이터 수(r_rows)는 2,800,000이고 where 조건에 맞는 데이터 수는 50,100으로 r_filtered의 결과는 1.79%

50100 / 2800000 * 100 = 1.79%

예약중 인덱스 적용

where조건을 만족하는 데이터만 인덱스를 통해 직접 조회했기 때문에 데이블의 데이터 수가 50,100로 조회되어 결과는 100%

50100 / 50100 * 100 = 100%

예약대기 인덱스 미적용

테이블의 데이터 수는 2,800,000이고 where조건에 맞는 데이터는 수는 1,236,675으로 44.17%

1236675/2800000 * 100 = 44.17%

예약대기 인덱스 적용

where 조건을 만족하는 데이터만 인덱스를 통해 직접 조회했기 때문에 데이블의 데이터 수가 1,236,675로 100%

1236675/1236675 * 100 = 100%

1236675/1236675 * 100 = 100%

인덱스의 핵심

• 필요한 데이터만 효율적으로 검색: 인덱스 적용으로 불필요한 스캔을 줄여 CPU와 I/O 자원을 절약.
• Full Table Scan 방지: 테이블 전체를 순차적으로 스캔하지 않으므로 속도와 성능 모두 향상.
• 조건에 맞는 데이터만 조회: r_filtered 비율을 100%로 만들며, 정확성과 효율성을 동시에 달성.

---

비교 분석 중 궁금했던 내용 🙋

예약대기 속도

속도 분석을 했을 때 "예약대기"는 별 차이가 없었을까?

아래 쿼리문을 분석해 보니 결과는 매우 놀라웠습니다.

예약대기

"예약대기" 데이터 값이 전체 데이터의 약 44.17%를 차지하여 비중이 높기 때문에 인덱스가 있더라도 성능 차이가 크지 않아 적용되지 않았습니다.

 

예약 중 속도

예약중

"예약 중" 속도 분석 했을 때 0.6539에서 0.0012로 99%로 향상되었는 이유는 인덱스가 적용되었기 때문입니다. 즉 인덱스는 데이터 비중에 따라 성능 향상 효과가 달라집니다.

---

인기 있는 콘서트 조회 

인덱스를 적용하여 조회 속도가 75% 향상된 걸 확인할 수 있습니다.

인기 있는 콘서트 조회

 

---

마지막으로 기존 쿼리와 수정된 쿼리 성능 테스트

-- 기존 Query
SELECT * FROM CONCERT_RESERVATION 
WHERE reservation_status = '예약중' 
AND reservation_date ='2024-10-20 19:00:00' 
LIMIT 0, 200;

이 쿼리는 고정된 단일 시간값을 사용하여 최대 200건의 데이터를 조회합니다. 그러나 이 쿼리는 범위 조건이나 정렬을 포함하지 않기 때문에, 인덱스가 제대로 작동하는지 확인하는 데는 한계가 있었습니다. 따라서 인덱스 적용 후 성능은 향상되었지만, 범위 조건이나 정렬 인덱스가 인덱스 성능에 미치는 영향을 테스트하기 어려웠습니다.

-- 수정된 Query문
SELECT * FROM CONCERT_RESERVATION 
WHERE reservation_status = '예약중' 
AND reservation_date BETWEEN '2024-10-20 19:00:00' AND '2024-10-20 23:59:59' 
ORDER BY reservation_date DESC
LIMIT 10;

수정된 쿼리는 범위 조건과 정렬 조건을 추가하여, 인덱스가 정렬과 범위 검색에서 올바르게 작동하는지 검증할 수 있게 했습니다. 이로 인해 인덱스 성능을 보다 잘 확인할 수 있었고, 그 결과 성능이 약 99.7% 향상되었습니다.

 

수정된 Query문으로 인덱스 비교 결과

핵심 차이점 및 결론

기존 쿼리는 단순히 고정된 시간값을 기준으로 조회하는 방식이었기 때문에, 범위 조건이나 정렬 인덱스를 테스트하는 데는 한계가 있었습니다. 반면, 수정된 쿼리는 범위 조건과 정렬을 포함하여 인덱스가 제대로 작동하는지 검증할 수 있는 조건을 제공하며 성능을 크게 개선할 수 있었습니다. 

 

결론적으로, 두 쿼리 모두 성능 개선이 있었지만, 수정된 쿼리는 범위 조건과 정렬을 포함하여 더 복잡한 시나리오에 대한 인덱스 효율성을 검증할 수 있다는 점에서 더 유용한 테스트가 되었습니다‼️

---

내 생각 정리

인덱스는 쿼리 성능을 크게 향상할 수 있지만, 데이터 변경이 잦은 열에는 오히려 성능 저하를 초래할 수 있습니다. 카디널리티가 높은 열에 인덱스를 적용하면 효과가 크고, 범위 조건이나 정렬을 포함한 쿼리에서 성능 개선을 실현할 수 있습니다. 따라서 인덱스는 조회 성능을 최적화하는 중요한 도구지만, 사용 시 데이터의 특성을 고려해야 합니다.

 

끝.