SQL/실전 SQL Server MVP 53
1. 데이터베이스 설계 및 아키텍처 - (1) Louis와 Paul의 관계형 데이터베이스 디자인 10가지 원칙_2
by Toddler_AD
2025. 2. 6.
6. 커서보다는 집합을 이용하자
- SQL Server 개발에서 자주 범하는 실수 중 하나는 T-SQL 커서 형태로 코드를 작성하는 것이다.
- 거의 대부분의 사람들이 커서가 얼마나 나쁜지에 대해서 여러 차례 들어보았을 것이다.
- 데이터베이스 커서를 사용하는 것은 은행에 가서 수백만원을 입금할 때 한 번에 만원씩 수백번을 입금하는 것과 같다고 할 수 있다.
- 집합 기반의 솔루션 개념으로는 한 번에 수 백만 원을 입금해버리면 된다.
- 동일한 결과(입금한 돈이 헤아려지고 입금되는 것)가 이루어지지만, 매번 입금할 때마다 은행 창구 직원이 얼마가 입금되었다라는 말을 듣지 않아도 된다.
- 성능의 차이는 명백하다.
- 일반적으로 집합 기반의 솔루션이 반복 처리 형태의 솔루션보다 더 좋은 성능을 내며 그 차이도 상당하다.
- 물론 커서를 사용하는 방식이 더 좋을 때도 있다.
- 처리 로직 중에 이전이 결과를 이용해야 한다거나 DDL 코드를 반복해서 처리하는 경우가 이에 해당한다.
- 하지만 "집합 기반의 쿼리가 복잡해서 어떻게 문제를 처리하는지를 이해하기 힘들다."와 같은 주장은 커서를 사용할 합당한 이유가 되지 않는다.
- 안타깝게도 경우에 따라서는 데이터베이스 설계가 너무 엉망이어서 집합 기반 방식으로는 도저히 처리 할 수 없는 경우에 커서 방식으로 사용해야 하는 경우도 있다.
- 커서와 데이터베이스 설계의 중요한 핵심 포인트는 정규화가 잘된 데이터베이스 스키마는 집합 기반의 쿼리를 효과적으로 사용할 수 있지만, 정규화가 제대로 되지 않았거나 또는 전혀 정규화되지 않은 데이터베이스 스키마인 경우에는 집합 기반의 쿼리를 제대로 사용하지 못할 수 있다는 것이다.
- 이러한 점 역시 데이터베이스 스키마의 정규화가 필요한 또 다른 이유이기도 하다.
7. 적절한 데이터 형식을 사용하자
- 모든 문자열을 무조건 VARCHAR(255)로 설정해서는 안 된다.
- 또한 돈과 관련된 모든 열을 무조건 MONEY 형식으로 설정해서도 안 된다.
- 커피 잔에 바비큐 요리를 제공받거나 또는 바비큐 요리 접시에 담긴 커피를 제공받고 싶지는 않을 것이다.
- 적절한 크기의 데이터 형식을 사용해야 한다.
- 단, 데이터의 무결성을 유지할 수 있는 적절한 데이터 형식이어야만 한다.
- 어플리케이션에서는 단지 30자의 문자만 허용하는데 반해, 실제 보고서에는 단지 20자의 데이터만 출력하도록 해놓고 데이터베이스에는 VARCHAR(255) 라고 설정해 놓은 경우가 상당히 많이 있다.
8. 캡슐화를 통하여 확장성을 강화하자
- 아키텍트들은 다양한 문제들을 분리하는 것이 얼마나 유용한지, 그리고 서비스 기반의 구조에서 캡슐화(encapsulation)가 얼마나 중요한지를 잘 알고 있다.
- 그럼, 데이터베이스에서는 왜 캡슐화를 하지 않을까?
- SQL Server 운영 방식 중에서 가장 나쁜 것 중 하나가 바로 어플리케이션에서 Ad-hoc 쿼리를 사용하는 것이다.
- 즉, 어플리케이션에서 필요한 쿼리를 만들어 오브젝트 계층에서 바로 데이터베이스에 대해 실행하도록 하는 것이다.
- 이러한 방식이 왜 나쁠까?
- 견고하게 구성된 데이터베이스라 하더라도 쉽게 망가질 수 있기 때문이다.
- 데이터베이스에 미세한 변경이라도 가해진다면 어플리케이션 코드, 보고서, ETL 프로세스 등에서 사용하는 수백에서 수천 개의 데이터베이스 개체들이 영향을 받을 수 있다.
- 추상 계층을 두고 데이터베이스 개발자들에게 이를 통해 데이터베이스를 변경하도록 한다면, 데이터베이스 API가 변경되지 않는 이상 외부의 세계로부터 받는 영향을 단절시킬 수 있다.
- 모든 개별 계산 요소들은 보호되는 계층 내에 존재하도록 만들어야 하며, 개체 내부에서 수행되는 복잡한 부분들은 캡슐화시켜서 독립적으로 안전하게 만들어야 한다.
9. 스파게티는 음식이지 코드가 아니다
- 스파게티 코딩은 "그때가 좋았지"라는 시절에는 매우 일반적인 코드 구성 방식이었다.
- BASIC이나 COBOL에서, 명령을 실행하는 다음 줄로 넘어가려면 GOTO 1000 이라고만 기술하면 되었다.
- 여기서 1000은 소스 코드의 1,000번째 줄을 의미한다.
- 그 시절에는 매우 근사한 기능이었지만, 이내 프로그래밍은 복잡해지게 되고 감당할 수 없는 수준에 이르게 되었다.
- 결국, 사람들은 미친 듯이 GOTO문을 사용해댔다.
- 스파게티 코드(spaghetti code)라는 용어는 마치 스파게티의 면 가락처럼 코드의 제어가 여기저기를 옮겨 다닌다는 의미이다.
- 아마도 이렇게 생각할 것이다. "우리의 코드에는 GOTO를 사용하지 않아."
- 맞다. 하지만 여기서 말하고자 하는 것은 단순히 GOTO와 같은 방식뿐만 아니라 더욱 더 심각한 방식들이 있다.
- 경우에 따라 다른 사람들이 이해하기 힘들 정도로 불필요한 수준으로 복잡하게 만들어진 동적 쿼리들을 만드는 경우도 있다.
- 또한, 다음과 같이 블록 별로 쿼리를 만드는 경우도 있다.
IF 조건
QUERY
ELSE IF 조건
QUERY
ELSE
EXECUTE procedure -- 또는 이와 같은 또 다른 IF 블록을 반복해서 설정
END
- 이와 같은 스파게티 코딩 방식은 매우 비효율적이다.
- 문제가 발생하게 되면 코드의 어느 부분에서 발생했는지를 바로 찾아내기가 어렵기 때문이다.
- 다음과 같은 예를 살펴보자
- UPDATE를 수행하면 트리거가 호출된다. 트리거에서는 테이블에 데이터를 입력하는 프로시저를 호출하게 되고, 해당 테이블에 설정된 다른 트리거가 호출되며, 복잡한 업무 로직이 포함되어있는 프로시저를 실행하게 된다.
- 저장 프로시저가 다른 프로시저에서 만든 임시 테이블을 사용하는 경우, 임시 테이블은 또 다른 프로시저에서 변경된다.
- 특정 프로시저에서 전역 임시 테이블(## 테이블 형태)을 만든 다음, 다른 세션에서 이를 참조하여 사용한다.
- 프로시저 내에서 전역 커서를 생성한 다음, 다른 프로시저를 호출하여 이 커서를 닫거나 또는 닫지 않은 채로 종료한다.
- 트리거가 악명이 높은 이유 중 하나는 다른 트리거나 프로시저들을 호출하도록 하는 잘못된 방식으로 구현될 수 있다는 점 때문이다.
- 일반적으로 개발자들은 이러한 트리거의 "마법과 같은" 예상하지 못한 행동들을 생각하고 프로그램을 만들지는 않는다.
- 그렇다고 해서 문제가 생겼을 때 개발자들을 비난할 수 있을까?
- 프로세스 측면에서 예측 가능한 결과를 얻는 것은 상당히 중요한 부분이다.
- 트리거가 설정되어 있고, 이 트리거가 프로시저를 호출하며, 프로시저 내에서 커서를 만들어 실행하거나 또 다른 트리거의 실행을 유발하는 등의 작업이 이루어지면 제대로 된 결과를 얻기 힘들 뿐만 아니라 문제가 생겼을 때 제대로 지원하기도 어려워진다.
10. NOLOCK = 일관성 없음
- SQL Server에서 잠금(lock)은 제대로 이해하고 있어야 하는 아주 중요한 부분이다.
- 여러 사용자들이 동시에 동일한 리소스를 문제없이 사용하기 위해서는 잠금이 반드시 필요하다.
- 만약 잠금을 제대로 이해하고 있지 않다면 시스템의 성능 튜닝 작업을 할 때 어려움을 겪을 수 있다.
- 많은 사람들은 다음과 같은 방법이 잠금(lock)으로 인해 발생되는 문제들을 해결할 수 있는 좋은 방법이라고 생각한다.
- 코드 앞 부분에 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 명령을 추가하여 뒤에 나오는 모든 조회 쿼리에서 잠금을 걸지 않도록 설정
- 쿼리의 테이블 뒤에 NOLOCK 또는 READUNCOMMITTED 힌트를 추가하여 해당 테이블을 조회할 때 잠금을 걸지 않도록 설정
- 하지만, 두가지 방법 모두 복잡한 문제를 야기할 수도 있다.
- 잠금을 무시함으로써 아직 저장이 완료되지 않은 채로 처리 중인 데이터를 읽어올 수도 있다.
- 즉, 아직 데이터베이스에 커밋되지 않고 처리중인 데이터를 읽어올 수 있다는 것이다.
- 이에 대한 해결 방법은 무엇일까?
- 우선, 잠금이 얼마나 오랫동안 지속되면 얼마나 많은 데이터에 걸리는지에 대해 알아보자.
- SQL Server의 기본 격리 수준인 READ COMMITTED는 행을 읽어올 때에 한번에 한 행씩만 잠금을 설정한다.
- 트랜잭션 내에 속해있더라도 트랜잭션의 격리 수준에 따라 같은 쿼리가 다른 결과를 만들어 낼 수도 있다.
- 가장 높은 격리 수준인 SERIALIZABLE을 사용하면 반복해서 수행하더라도 동일한 결과가 나오도록 해주며, 스냅샷 격리 수준을 사용하면 쿼리에서 변경된 사항을 임시 공간에 저장함으로써 트랜잭션 내에서 같은 결과가 나오도록 해준다.
- 그러나, 트랜잭션 격리 수준은 서버에서 데이터베이스의 구조로부터 현재 수행하고자 하는 동작을 인식할 수 있다고 생각하고 처리하게 된다.
- 다음과 같은 예를 생각해보자.
UPDATE tableName
SET column = 'Test'
WHERE tableNameKey = 1
- 얼마나 많은 행들이 잠길지를 묻는다면 아마도 하나라고 답할 것이다.
- 하지만, 실제로는 tableNameKey열의 인덱스 설정 여부에 따라 다르다.
- 만약, 인덱스가 없다면 모든 행을 검색할 것이고, 이로 인해 테이블을 읽고자 하는 다른 모든 사용자들이 영향을 받을 것이다.
- 쿼리 프로세서에서 잠가야 하는 행을 정확히 알 지 못하기 때문에 테이블의 모든 행에 대해 잠금을 수행하게 되는 것이다.
- SQL Server 2005에서는 스냅샷(SNAPSHOT) 격리수준이라는 새로운 격리 수준이 추가되었다.
- 스냅샷 격리수준을 사용하면, 트랜잭션을 시작할 때 읽어야하는 데이터들은 트랜잭션이 끝나기 전까지 동일하게 볼 수 있다.
- 동시에 수행되는 다른 프로세스들은 현재 프로세스에 잠금을 설정하지도 않는다.
- 이전 버전의 값을 저장해서 다른 사용자들이 일관된 값을 조회할 수 있도록 해주는 것이다.
- 구현하는 방법도 간단하다.
- 하지만, 데이터를 변경하는 경우에는 고려할 점들이 있다.
- 스냅샷 격리 수준이 쿼리와 어플리케이션에 어떤 영향을 미치는지를 정확히 알아야하기 때문이다.
- 스냅샷 격리 수준은 행 버전 관리 기능을 이용하여 이전의 데이터 이미지를 tempdb에 저장하게 된다.
- 이러한 점 때문에 하드웨어에 대한 영향도를 제대로 파악하는 것도 중요하다.
- 스냅샷 격리 수준을 설정하려면 데이터베이스의 기본 격리 수준을 READ COMMITTED에서 READ_COMMITTED_SNAPSHOT으로 변경해주기만 하면 된다.
- 이렇게 하면 스냅샷 격리 수준을 행 수준에서 사용하기 때문에 쿼리에 대한 결과는 커밋된 결과만을 포함하게 된다.
- 만약 데이터를 조회하고 있는 중에 다른 사람이 해당 데이터를 변경하더라도 변경된 내용은 보여지지 않는다.
- 이러한 방식은 잠금으로 인해 발생할 수 있는 문제에 대해서는 적절한 해결 방법이며, 낙관적인 잠금 처리 방식을 이용하여 다른 사용자들이 데이터를 변경하는 것과는 무관하게 커밋된 데이터만을 보고자 할 때 사용할 수 있는 방법이다.
- 동시성은 쉽게 해결할 수 있는 문제가 아니라는 점은 분명하다.
- 이번 장에서 말하고자 하는 것은 잠금을 무시하는 것이 데이터베이스 시스템을 튜닝하는 것이 아니라는 점이다.
- 즉, 속도를 위해 데이터 일관성을 포기해서는 안 된다는 것이다.
- 만약, 현재 시스템에서 잠금으로 인한 성능 이슈가 크지 않다면 잠금을 그대로 유지하는 것이 더 나을 것이다.
11. 요약
- 이번 장에서는 데이터베이스 어플리케이션을 설계하고 만들 때 직면하게 되는 다양한 문제점들에 대해 살펴보았다.
- 항상 쉬운 것은 아니지만, 기타 여러 컴퓨터 관련 이슈들과는 달리 관계형 데이터베이스를 설계하고 구현하기 위해 필요한 이론들이나 방법들은 수십 년에 걸쳐 어느 정도 확고해졌다고 할 수 있다.
- 이렇게 된 근본적인 이유는 E.F Codd 박사가 관계형 데이터베이스 시스템을 처음 개발하면서 앞서 언급한 여덟 번째 주제인 캡슐화를 고려하고 만들었기 때문이라고 할 수 있다.
- SQL이 상당히 안정적인 상태를 유지하고 있는 것은 데이터베이스 서버를 실행하는 엔진이 더욱 더 강력해지고 있기 때문이다.
- 앞에서 설명한 규칙들을 지키고 데이터 스키마를 정규화하며, 집합 기반의 쿼리를 작성하고 서버에서 수행되는 데이터베이스 중 일부만을 가져올 수 있도록 쿼리를 작성한다면 수십억 건의 데이터가 저장되어 있는 데이터베이스를 사용하더라도 안정된 성능을 기대할 수 있을 것이다.