with절과 서브쿼리

최근 실리콘벨리에서 날아온 데이터엔지니어링이라는 교육을 들으면서 과제나 실습등을 하는데
쿼리문중에서 서브쿼리 같은데 with를 쓰면서 별칭을 따로 하는것을 발견했습니다.
그런데 서브쿼리를 써도 되는데 왜 이렇게 따로 만들어서 쓰는것일까라는 궁금증이 생겼습니다.

🦾 WITH절의 개념

• Common Table Expressions 즉 CTE라고도 불리며 재사용 가능한 일시적인 결과 집합을 정의하는데 사용됩니다.
• 이러한 결과집합은 현재 실행중인 SQL쿼리만큼 지속됩니다.

장점

• 쿼리의 가독성을 높여준다
• 코드를 효율적으로 조직화한다
• 중복계산을 피할수 있다.

이러한 정보들을 보면 서브쿼리를 한번만 사용할거라면 서브쿼리를 사용하는것이 좋겠지만
동일한 서브쿼리를 여러번 사용하는 경우 서브쿼리보단 WITH절을 사용하는것이 좋겠습니다.
즉, WITH절 자체가 서브쿼리의 팩토링 방식으로 제기되었다는 것이고
WITH절이 서브쿼리를 개선하고 모듈화하기 위해 도입되었다는 의미입니다.

---

🦿 쿼리의 차이

서브쿼리

1. SELECT col1, (SELECT * FROM ...)
2. FROM (SELECT * FROM ..) AS 
3. WHERE col1 =(SELECT * FROM ..)

1. 스칼라 서브쿼리로 불리며 하나의 컬럼처럼 행동합니다.
2. 인라인 뷰 이며 하나의 테이블 처럼 사용합니다.
3. 보통의 서브쿼리이며 하나의 변수처럼 사용, 서브쿼리의 결과에 따라 달라집니다.

WITH절

WITH 별칭1 AS(    )
WITH 별칭2 AS(    )

SELECT * 
FROM 별칭1

---

👏 왜 올라가는걸까?

연산의 횟수

WITH절을 사용한다면 데이터베이스가 동일한 서브쿼리를 한번만 계산하고 결과를 버퍼캐시에 저장하여 여러 번 재사용할 수 있기 떄문입니다.
따라서 연산의 낭비를 줄이고 효율성과 성능이 향상 됩니다.

퍼버 캐시?

• 디스크와 메모리 간의 데이터전송을 최적화하고 데이터 처리 성능을 향상시키기 위한 캐시입니다.
• 메인 메모리의 한 부분으로, 자주 엑세스하는 데이터와 인덱스 페이지 정보를 저장하고 유지하는 역할을 합니다.

데이터베이스에서 데이터를 읽거나 쓸때, 바로 디스크에 직접 접근하는 것보다 메모리의 버퍼캐시를 사용하면 성능이 크게 향상됩니다.
이는 디스크 엑세스보다 메모리 엑세스가 빠르기 때문입니다

버퍼캐시 동작의 원리

버퍼캐시는 Redis와 같은 캐시데이터베이스의 읽기전략중 Look Aside Cache패턴과 같이 진행이 됩니다.

1. 데이터의 요청이 들어오면 우선적으로 캐시에서 필요한 데이터를 검색합니다.
2. 요청한 데이터가 버퍼캐시에 있다면(캐시히트), 캐시에 저장된 데이터를 바로 사용합니다.
3. 요청한 데이터가 버퍼캐시에 없다면(캐시미스), 디스크에서 데이터를 가져와서 버퍼에 캐시로 로드합니다.
   이후 이 데이터에 대한 재요청이 들어온다면 빠르게 엑세스가 가능합니다.
4. 데이터의 변경이 발생한다면 먼저 버퍼 캐시에서 변경이 이루어지고, 나중에 변경 내용이 디스크에 업데이트됩니다. 이렇게 함으로서 디스크에 자주 접근하는 것을 줄이고, 성능 향상을 기대할수 있습니다.

버퍼 캐시를 사용해서 메모리에서 데이터를 읽고 쓰는 작업이 더 빠르게 수행되기 때문에 성능향상이 됩니다.
하지만 메모리 용량이 제한되어 있기 때문에 버퍼 캐시의 크기를 적절하게 관리하는것 또한 중요합니다.
효율적인 캐시 교체 알고리즘인 LRU(Least Recently Used), MRU(Most Recently Used), Clock등을 사용해서 캐시의 용량을 잘 관리해야합니다.