DB 이것 저것 정리! (feat.SQL 실행순서..)

SQL 쿼리의 개념적인 실행 순서

1. FROM(+JOIN)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

나는 당연히 무조건 위에 순서대로 작동하는 줄 알았지만 그게 아니란 걸 깨달았다..!!

 

=> 위의 순서는 개념적인 실행 순서일 뿐, 실제 데이터베이스에서는 옵티마이저를 사용해서 최적의 실행 계획을 세우기 때문에 위와 순서가 달라질 수 있다!!! 

ex) 많은 테이블과 Join 해야 하는 경우 row가 많다면 당연히 부하가 갈테고 만약 Where절에서 인덱스가 걸린 컬럼을 조건으로 하는 경우, 옵티마이저가 이를 감지하고 Where절을 먼저 처리해서 인덱스를 이용한 필터링 작업을 먼저 수행할 수도 있음!! 

 

=> 최근에 이와 관련된 질문을 받았고, 뒤늦게 기존 작성 내용이 틀렸다는 걸 알게 되어 수정합니다.

옵티마이저가 최적의 실행 계획을 세워도 위의 개념적인 실행 순서는 동일합니다. 다만 그 과정에서 인덱스 사용, 조인 순서 변경 등의 다양한 최적화 방안이 실행됩니다. 

이번 기회를 통해 자료를 해석할 때 독단적인 해석이 위험함을 깨달았습니다. 실력 있는 개발자의 검증이 얼마나 중요한지 알게 된 좋은 기회였던 것 같습니다.

 

 

 

 

---

조인

• =으로 조인하는 것보다 명시적으로 JOIN을 사용하여 조인하는 것이 실수를 줄일 수 있다.
• INNER JOIN의 INNER는 생략 가능
• 마찬가지로 LEFT JOIN은 LEFT OUTER JOIN의 축약형이며, RIGHT JOIN, FULL JOIN도 OUTER를 생략할 수 있다.
• NATURAL JOIN은 조인할 때 조인 조건을 명시하지 않아도 두 테이블간의 join 조건에서 동일한 이름을 갖는 모든 칼럼들에 대해 Equi join을 수행
• CROSS JOIN은 카티션 곱을 생성한다.(모든 데이터의 조합 .. -_-...)
  
  

 

 

---

집계 함수와 그룹화

• 집계 함수는 여러 튜플의 값을 요약한다. NULL은 제외된다.
• COUNT(컬럼)으로 사용할 경우 NULL인 값을 제외하고 카운트한다. 반면 COUNT(*)는 NULL을 포함한 전체 카운트를 한다.
• GROUP BY로 묶은 컬럼은 반드시 SELECT 절에 명시되어야 한다.
• HAVING은 GROUP BY로 묶은 결과에 조건을 걸 때 사용한다.
  
  

---

Isolation(트랜잭션 격리 수준)

 

트랜잭션 격리 수준(Isolation Level)은 동시에 여러 트랜잭션이 실행될 때 데이터베이스에서 각 트랜잭션이 다른 트랜잭션의 작업에 미치는 영향을 제어하는 방법을 나타냄!

 

주요한 격리 수준에는 아래와 같음

1. READ UNCOMMITTED (커밋되지 않은 읽기):
   • 가장 낮은 격리 수준으로, 다른 트랜잭션에서 수정 중인 데이터를 읽을 수 있다. 이는 "dirty read"를 발생시킬 수 있으며, 다른 트랜잭션에서 롤백될 경우 데이터의 일관성을 해치는 문제가 발생할 수 있어서 비추천!
2. READ COMMITTED (커밋된 읽기):
   • 한 트랜잭션이 커밋된 데이터만 읽을 수 있다. 따라서 다른 트랜잭션이 커밋되지 않은 데이터를 읽는 "dirty read" 문제는 발생하지 않는다. 그러나 하나의 트랜잭션이 데이터를 두 번 읽는 동안에도 데이터가 변경될 수 있어 "non-repeatable read"와 같은 문제가 발생할 수 있다!! 
3. REPEATABLE READ (반복 가능한 읽기):
   • 동일한 쿼리를 여러 번 실행하더라도 결과가 일관되게 유지. 트랜잭션 내에서 읽은 데이터가 다른 트랜잭션에 의해 변경되더라도 해당 트랜잭션은 처음에 읽은 데이터를 유지한다. 이로 인해 "phantom read" 문제가 발생할 수 있음!
4. SERIALIZABLE (직렬화 가능한):
   • 가장 엄격한 격리 수준으로, 트랜잭션은 순차적으로 실행되는 것처럼 처리된다. 이것은 동시성을 제한하지만 데이터 일관성을 보장. 여러 트랜잭션이 동시에 실행되더라도 각각의 트랜잭션은 다른 트랜잭션의 작업에 영향을 받지 않는다.

 

 

=> 얼핏 보면 뭐야 안전한 4번 Serializable 좋은 것 아니야? 라고 할 수 있지만 이런 엄격한 격리는 결국 성능 저하로 이어질 수 있다!

데이터의 일관성과 동시성 사이에 트레이드 오프가 발생하는 것!!  

 

참고로

MySQL의 디폴트 격리 수준은 REPEATABLE READ (반복 가능한 읽기)

PostgreSQL의 기본 격리 수준은 READ COMMITTED (커밋된 읽기)이다.

 

이 격리 수준은 변경 가능하지만 PostgreSQL은 READ COMMITED 기능을 제공하지 않는다!!

---

인덱스

인덱스는 데이터베이스의 성능을 향상시키는 데 사용하지만 잘못 사용하면 오히려 성능을 저하시킬 수 있다.

 

만약 너무 많은 인덱스를 생성하면 데이터 변경 작업이 느려지고, 불필요한 저장 공간을 차지할 수 있기 때문에 인덱스를 만들 때에는 신중해야 한다.

 

따라서 인덱스를 만들 때에는 자주 사용되는 컬럼이나 조회가 빈번한 컬럼에 집중하는 것이 중요하며,

데이터 변경이 적은 컬럼보다는 조회가 많은 컬럼에 인덱스를 생성하는 것이 성능 향상에 도움이 된다.

 

* 참고

: 기본 키(primary key)는 자동으로 인덱스가 생성되기 때문에 pk로 조인을 한다거나 조건을 걸면 인덱스를 활용했다고 볼 수 있다!

 

* 참고

: B- 트리를 사용해서 구현 하는 경우가 많다.(이진트리와 달리 여러 개의 자식 노드를 가질 수 있어서 데이터를 빠르게 찾을 수 있다.)

---

파티셔닝과 샤딩

파티셔닝

• 파티셔닝은 테이블을 논리적 또는 물리적으로 여러 조각으로 나누는 것을 말한다.
• 수평 파티셔닝은 테이블을 행 단위로 분할하는 것!
• 수직 파티셔닝은 테이블을 열 단위로 분할하는 것!

샤딩

• 샤딩은 데이터베이스의 부하를 분산시키기 위해 데이터를 여러 서버에 분산하는 것을 말한다.
• 샤딩을 사용하면 데이터베이스의 성능을 향상시킬 수 있다.

-> 어떻게 보면 샤딩은 수평 파티셔닝의 일종이라고 볼 수도..?  차이점은 다만 동일 pc가 아닌 다른 pc에 분산해서 저장한다는 것!!

---

레플리케이션

• 레플리케이션은 데이터베이스의 가용성과 내결함성을 향상시키기 위해 사용!
• 레플리케이션은 마스터 서버에서 변경된 데이터를 여러 개의 슬레이브 서버로 복제하는 것을 의미한다
• 레플리케이션을 사용하면 데이터베이스의 가용성이 향상되고 장애 발생 시 복구가 용이해짐!!

---

커넥션 풀링

• 커넥션 풀링은 데이터베이스와의 연결을 효율적으로 관리하기 위한 기술
• 커넥션을 미리 생성하고 재사용함으로써 성능을 향상시킬 수 있음
• 즉, 커넥션을 생성하고 제거하는 데 필요한 시간을 절약할 수 있다!