TIL(20240804) [인덱스-쿼리최적화]
💡 쿼리 최적화란 무엇이며 방법은 어떤 것이 있을까?
쿼리최적화는 데이터베이스 성능을 향상시키기 위해 SQL 쿼리를 최적화하는 과정으로 쿼리 실행 시간을 줄이고, 시스템 자원을 효율적으로 사용하여 데이터베이스의 응답성을 높이는 역할을 한다.
- SQL 쿼리문 최적화 목표
- 성능 향상(개선)
- 응답 시간 단축
- 기술 자원 보존
- 비용 절약 이라 할 수 있다.
- 쿼리 최적화 방법은
1) 적절한 인덱스 사용: 자주 조회되는 컬럼에 인덱스를 생성하여 검색 속도를 향상시킨다.
1
2
-- 인덱스 생성
CREATE INDEX idx_customer_name ON customers(name);
2) 쿼리 리팩토링: 복잡한 쿼리를 여러 개의 단순한 쿼리로 분할하여 성능을 향상시킨다. (서브쿼리 최적화, 불필요한 컬럼 제거-> SELECT문에서 불필요한 컬럼 제거)
1
2
3
4
5
6
-- 서브쿼리 최적화
-- Before
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE name = 'John Doe');
-- After
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.name = 'John Doe';
3) 조인 최적화: 일반적으로 작은 테이블을 먼저 조인하는 것이 효율적, 조인 조건을 명확히 해서 불필요한 테이터 스캔 줄이기
4) 쿼리 실행 계획 분석: 데이터베이스가 쿼리를 어떻게 실행하는지 확인하여 실행 계획 분석 후 인덱스를 사용하거나 조인순서, 필터링 조건을 최적화 한다.
-> 아래부분은 좀 더 공부가 필요함
5) 데이터베이스 설정 최적화:** 데이터베이스 캐시 크기를 조정하여 더 많은 데이터를 메모리에 저장하고, 디스크 I/O를 줄이거나 큰 테이블을 파티셔닝하여 쿼리 성능을 향상시킨다. 예를 들어, 날짜 기준으로 데이터를 파티셔닝할 수 있다.
6) 통계 정보 업데이트:** 데이터베이스의 통계 정보를 주기적으로 업데이트하여 쿼리 최적화 도구가 최신 정보를 바탕으로 최적화할 수 있도록 한다.
–JPA 관련 쿼리 최적화 주의사항– JPA 성능 개선팁
–SQL관련 쿼리최적화–
💡 DB 로직 최소화
1) 적절한 캐싱 사용 - 빈번하게 엑세스하는 데이터를 메모리에 캐싱하여 반복적인 DB쿼리를 피할 수 있다. Redis나 Memcached와 같은 인메모리 개시 솔루션을 사용할 수 있다.
2) 인덱싱 및 쿼리최적화 - 데이터베이스에서 쿼리 성능을 향상시키기 위해 인덱스를 적절하게 사용하고, SQL 쿼리를 최적화하고 필요한 필드만 가져오도록 쿼리를 설계한다.
3) 배치 처리 - 대량의 데이터 작업을 처리할 때는 실시간 요청이 아닌 배치 처리 작업을 고려한다. 이렇게 하면 실시간 트래픽에 부하를 주지 않는다.
4) 데드라인 및 타임아웃 설정 - 장기 실행되는 쿼리나 작업에 대한 타임아웃 및 데드라인을 설정하여 시스템 안정성을 유지한다.
5) 데이터 정규화 및 비정규화 - 데이터 정규화는 데이터 중복을 최소화하는 데 도움이 되지만 때로는 비정규화를 고려하여 데이터 검색을 더 빠르게 만들 수 있다.
6) NoSQL 데이터베이스 고려 - 관계형 데이터베이스 이외에 NoSQL 데이터베이스를 고려하여 데이터 모델을 더 유연하게 다룰 수 있다.
7) 데이터 파티셔닝 - 대용량 데이터베이스 테이블을 파티션으로 나누어 쿼리 성능을 최적화 한다.
8) 트랜잭션 관리 - 트랜잭션을 필요한 경우에만 사용하고, 긴 트랜잭션을 피하며 격리수준을 적절히 설정한다.
9) 적절한 데이터 베이스 선택 - 프로젝트의 요구사항에 가장 적합한 데이터베이스 시스템을 선택한다. 관계형 데이터베이스, NoSQL, 그래프 데이터베이스 등을 고려할 수 있다.
10) 백엔드 캐싱 및 프론트엔트 최적화 - 백엔드에서 쿼리 결과를 캐싱하고, 프론트엔드에서 필요한 만큼 데이터를 한번에 로드하여 불필요한 요청을 줄인다.
11) 모니터링과 프로파일링 - 시스템의 성능을 모니터링하고 병목현상을 식별하고 프로파일링 도구를 사용하여 성능문제를 해결한다.
12) 더 나은 하드웨어 및 스케일링 - 필요한 경우 더 강력한 하드웨어를 구입하고, 수직 및 수평 스케일링을 통해 시스템을 확장한다.
정리 : DB 로직 최소화를 위해 캐싱, 쿼리 최적화, NoSQL, 데이터 파티셔닝과 적절한 데이터베이스 선택을 고려하여 성능을 최적화할 수 있다. 또한 모니터링과 프로파일링을 통해 성능 문제를 식별하고 하드웨어 및 스케일링을 개선하여 시스템을 최적화 한다.