해당 글은 관계형 데이터베이스 실전 입문을 정리한 내용입니다.
1장에서도 다뤘지만 NULL이 포함돼 있으면 관계형 모델이 무너진다. 따라서 테이블의 모든 행, 모든 카럼은 구체적인 값을 가져야한다. 하지만 테이블에 NULL이 포함되지 않게 하려면 단순하게 NOT NULL 제약을 걸어서 되는 문제가 아니다. 다른 값으로 대체하는 정도로 대체하려면 NOT NULL 제약을 걸지 않는 편이 낫다.
관계형 모델에서는 의미가 있는 한 묶음의 데이터를 한 단위로 취급해야 한다. 예를 들어 메일 주소를 분해해버리면 그 부분의 문자열에는 의미가 없어지게 된다. 이처럼 값을 조금 더 세세한 단위로 분해하지 않고 전체가 응용 프로그램에서 의미가 있는 것이 아니면 속성의 값이 될 수 없다.
후보키는 그 릴레이션에 포함된 튜플의 값을 고유하게 하는 속성의 집합으로 기약(Irreducible)을 말한다. 기약이라는 더는 속성을 줄일 수 없는, 즉 여분의 속성이 없는 상태를 말한다 후보키라고 불리는 이유는 이 처럼 튜플의 값을 고유하게 요구하는 속성의 집합이 한 종류밖에 없는 것은 아니기 때문이다.
SQL은 테이블에 기본키가 있지만, 관계형 모델에서는 기본키라는 개념이 없다. 릴레이션에 여러 개의 후보키가 있는 경우 이 키들을 어떤 것도 의미적으로나 기능적으로나 차이가 없고 어떤 후보키를 기본키로 정할지는 주관적인 문제다. 따라서 정규화를 올바르게 설정한다면 기본키라는 단어는 나오지 않는다.
후보키는 추가 가속성이 없는 속성의 집합이지만, 후보키의 슈퍼셋(후보키의 조합), 즉 추가 속성을 가지는 키를 슈퍼키라고 한다. 슈퍼키도 튜플의 값이 고유하다는 저은 후보키와 같다.
2NF ~ BCNF는 함수 종속성에 관한 정의이며 전부 함수 종속성을 이용해 설명할 수 있다. 그리고 릴레이션 내의 함수 종속성을 최대한 배제한 것이 BCNF다. 따라서 2NF, BCNF가 될 때까지 정규화를 수행하자.
함수 종속성 정의
어떤 릴레이션 R이 있고, 제목의 부분집합 두 개를 A, B라고 하자, 릴레이션 R의 모든 튜필이 A 값이 같다면 B의 값도 같은 경유가 있을 수 있다. 이 경우 한해서 B는 A에 함수종속한다고 한다. 이런 관계성을 A -> 라고 기술한다.
함수 종성성을 제거 이전
| 고객ID | 주문번호 | 고객기업명 | 고객기업 규모 |
|---|---|---|---|
| CA | 001 | A 상사 | 대규모 |
| CA | 002 | A 상사 | 대규모 |
| CB | 003 | B 건설 | 중규묘 |
예를 들어 A -> B 라는 함수 족성을 가지고 있으면 A는 그 릴레이션 키가 아니므로 반복해서 나타난다. (CA, CA 반복). 따라서 A의 값이 같다면 B의 값도 같다는 것이 숨겨진 키(자명하지 않은 함수 종속성)의 성질을 나타낸다. 2NF ~ BCNF에 관한 정규하는 이처럼 자명하지 않은 함수 종석을을 없애는 작업이다.
함수 종성성을 제거 이후
| 고객기업ID | 주문번호 |
|---|---|
| CA | 001 |
| CA | 002 |
| CB | 003 |
| 고객기업ID | 고객기업명 | 고객기업 규모 |
|---|---|---|
| CA | A 상사 | 대규모 |
| CB | B 건설 | 중규묘 |
결합 종속성은 프로젝션으로 나눈 릴레이션을 다시 결합하면 원래의 릴레이션으로 돌아가는 성질이다. 이를 무손실 분해라고 한다. 무손실 분해가 가능한것은 결합 종속성이라고 하므로 무손실 분해가 가능 한것은 당연하다.
무손실 분해가 가능한 것을 결합 종속성이라고 하므로 함수 종속성도 당연히 결합 종속성의 일종이다.
사실 4NF 이후의 정규화를 하는 경우는 거의 없다. BCNF까지 정규화하면 자동적으로 5NF의 조건을 만족하는 경우가 많기 때문이다
정리하면 다음과 같을때 BCNF는 자동적으로 5NF의 요건을 만족하게 된다.
- 키가 아닌 속성이 존재할 때 (FK 값으로 존재)
- 키에 포함되는 속성이 1개 뿐일 때 (FK 값은 하나)
NULL은 칼럼의 값을 모를 때 사용하는 마커로 값이 존재하지 않거나 값이 분명하지 않음을 나타낸다. [NULL 값] 이라는 표현을 사용하지만 이 단어는 잘못된 용법이다. NULL이 들어 있는 칼럼값은 실제로 존재하지 않으며 NULL은 값조차 없기 때문이다. NULL은 어디까지나 마커다. 값이 존재하지 않음을 나타내는 NULL과는 다른 개념이기 때문에 NULL 값이라고 하는 것은 부적절하다. SQL에서는 NULL인지 아닌지에 대한 판단은 IS NULL or IS NOT NULL로한다. NULL은 값이 아니라 값처럼 비교할 수가 없기 때문이다.
NULL + 1
CONCAT('ABC', NULL)
NULL > 100
연산 결과는 무엇일까? NULL(알 수없는 값)에 무엇을 더해도 대답은 NULL(알 수 없다). 수치 연산뿐만 아니라 문자열의 조작도 마찬가지이다. NULL 크기 비교도 TRUE, FALSE도 아니고 NULL(알 수 없음)이다. 미지의 값을 비교해서 결과를 얻을 수는 없다.
SELECT * FROM user WHERE age <> 20해당 쿼리는 나이가 20살이 아닌 모든 사용자라는 의미인데 조건에는 나이가 NULL로되어 있는 모두 해당 되지 않는다. 실제로는 나이를 알 수 있고 나이가 20살이 아닌 사람이 되어야 한다.
SELECT * FROM user WHERE age <> 20 or age IS NULL이처럼 칼럼에 NULL이 포함됐을 때는 (또는 NULL이 될 가능성이 있을 때)와 NULL일 때는 어떻게 대처해야하는가 ?라는 논리가 필요해진다.
NULL이 포함된 식은 NULL이 되어버린다 NULL이 있는 덕분에 마치 논리값이 3개가 된것처럼 보인다. 이처럼 TRUE, FALSE, Unknown의 세 가지 논리값에 의해서 판정이 이뤄지는 논리 시스템을 3치 논리 3VL 이라고 한다.
3VL은 2VL 보다 복잡하기는 해도 논리학적으로 잘못된것은 없다 그런데도 SQL에서 3VL을 사용하는 게 문제가 되는 이유는 3VL이 현실적으로 제대로 표현할 수 없기 때문이다. 관계형 모델은 현실 세계를 잘 표현하기 위한 모델이다. 3VL의 도입 때문에 모델의 의미를 읽어버리고 원점으로 돌아가게 된다.
Unknown 이란 무엇일까? 현실 세계에서는 사람의 나이를 모를 경우 구체적이 나이는 알 수 없어도 그 사람의 외모에 및 기타 적인것으로 예상이 가능하다. 그러나 Unknown는 이 모든 정보를 버리고 Unknown 이라는 이름표를 붙여 놓은 것이다.
NULL 논리적인 측면 뿐만아니라 쿼리의 실행 계획인 옵티마이저의 구현에도 큰 악영향이 있다. 옵티마이저의 가장 큰 역할은 쿼리의 시랳ㅇ이 최적의 성능을 내도록 바꿔주는 것이다. 옵티마이저에 의한 내부적인 쿼리의 재작성은 당연하지만 쿼리의 결과가 등가가 되게 수학적으로 증명할 수 있는 조합 중의 하나를 선택하지 않으면 안된다. 쿼리의 성능을 개선해도 결과가 다르면 의미가 없다.
그런데 NULL이 존재하는 경우에 등가가 되면 수학적으로 증명할 수 있는 조합은 많이 줄어든다. 그 결과 옵티마이저는 별다른 일도 못하고 결국 사람이 노력해서 쿼리를 튜닝해야 한다.
NULL이 옵티마이저의 판단을 둔하게 하는 것은 쿼리의 재작성 뿐만이 아니다. 쿼리의 비용 계산에도 양향을 있다. NULL이 있는 인덱스 항목은 인덱스 상에도 그 인덱스의 제일 앞 또는 제일 뒤에서 몰아서 배치된다.
IS NULL을 해결하려면 이처럼 인덱스의 제일 앞부분이나 제일 뒷부분을 스캔해야한다. 어느정도 NULL이 아닌 값이 드문드문 분산돼도(카디날리티가 높아도) 인덱스 상의 칼럼이 NULL인 모든 행은 같이 취급된다. 이처럼 행이 늘어나면 늘어날수록 IS NULL을 위해 스캔하는 시간이 걸린다.
NULL에 의해 톱티마이저가 최적의 실행 계획을 선택할 기회를 일게 되는 문제가 있다.
NULL을 제거하는 가장 정통적인 방법은 테이블을 적절하게 정규화하는 것이다. 테이블이 1NF의 요건을 만족하려면 NULL이 포함돼서는 안된다.
| 이름 | 학년 | 클럽 |
|---|---|---|
| 오민혁 | 1 | 검도부 |
| 정은오 | 2 | 유도부 |
| 한혜리 | 4 | NULL |
| 이름 | 학년 |
|---|---|
| 오민혁 | 1 |
| 정은오 | 2 |
| 한혜리 | 4 |
| 이름 | 클럽 |
|---|---|
| 오민혁 | 검도부 |
| 종은오 | 유도부 |
자주 하는 실수 중의 하나가 칼럼을 NOT NULL로 정의하는 대신에 기본값에 NULL과 같은 의미가 있는 값을 정의흐는 것이다.
SELECT * FROM T WHERE age < 20age라는 칼럼에 -1를 NULL 값처럼 사용한다면 미성년자를 추출하기 위한 쿼리문에서 age = -1 가진 행도 추출된다.
NULL을 피하고자 편의상 NULL이 아닌 NULL과 같은 의미가 있는 기본값을 사용하는 것은 테이블 설계의 질을 향상시키는 커녕 상황을 더욱 악화시킬 뿐이라는 것을 기억하자.
소프트웨어 중에서는 공백문자(길이가 0인 문자열)과 NULL을 같은 의미로 보기도한다. 하지만 공집합이 [존재하지 않은 집합]이 아니고 [요소가 0개인 실재하는 집합]인 것과 마차가지로 빈 문자열은 길이가 0일 뿐 실재하는 문자열로 간주한다. 빈 문자열과 NULL은 본래 명확하게 구별해야 한다.
빈 문자열과 NULL이 같은 뜻이라고 정하는 경우 어떻게 대처하면 좋을까? 불행하게도 필자는 그 대답을 모른다. 대응 수단이 없다 이 개갞끼야...
인덱스는 색인과 가깝다. 색인에는 키워드가 문자 순서로 정렬돼 있고 키워드를 조사해보면 그 키워드가 있는 페이지의 번호가 기록되어 있다. 색인을 검색해보면 그 키워드가 게재된 페이지를 빠르게 찾아 볼 수 있다. 색인이 커지면 키워드를 찾는데도 시간이 걸릴것이다.
B+트리의 검색은 루트 노드에서 어떤 리프 노드에 이르는 한 개의 결로만 검색하면 되므로 굉장히 효율적이다. 이 성질은 아무리 트리의 크기가 크거나 계층이 증가해도 변하지 않는다.
B+트리 인덱스에 있는 항목의 수가 테이블 행 수와 똑같다는 점이다. 따라서 테이블의 행이 늘어나면 인덱스도 커진다 .책의 끝부분에 단어의 일부만 살려 있는 색인과는 다르다.
B+트리 인덱스 등가 비교와 범위 검색에 사용 할 수 있다. 구체적으로 WHERE key = 123 처럼 같은 (=equal)으로 검색한다. 범위 검색은 부등호나 BETWEEN 절 을 사용해 범위를 지정한다. WHERE BETWEEN 100 AND 200
LIKe 검색도 범위 검색이다. WHERE key LIKE 'a%'를 검색하면 'a' 이상 'b' 미만의 범위에 있는 항목이 검색된다. 그러나 LIKE 절을 이용한 검색은 와일드카드를 배치하는 위치가 문제가 된다.
인덱스를 사용해 검색할 때 와이드카드의 지정은 전방 일치로 와일드카드가 구체적인 문자 열 뒤에 놓어야 한다. 전방 일치여야 하는 이유는 B+ 트리의 물리적 구조 때문이다.
B+트리는 왼쪽의 문자부터 순서대로 정렬돼 있다. 따라서 왼쪽 이외의 문자로 인덱스를 검색하는 것은 구조적으로 불가능하다.
WHERE name LIKE '가%'
조건으로 검색하는 경우 맨 앞문자를 알고 있으므로 논리프 노드를 보면 어떤 자식 노드에 해당하는 항목이 있는지 알 수 있다.(제일 왼쪽의 리프 노드에 모두 있기 때문에)
WHERE name LIKE '%가'
그런데 후방 일치일 경우 앞에 와일드카드를 사용해버리면 논리프 노드를 봐도 해당하는 항목이 오디에 있는지 알 수 없다. 값을 검색하려면 인덱스를 스캔해야한다.
WHERE name LIKE '%가%'
중간 일치도 마찬가지이다. 양쪽에 와일드카드를 둘 때도 스캔하지 않으면 값을 검색할 수 없다. B+트리 인덱스는 왼쪽부터 순서대로 값을 지정하지 않으면 소용 없다는 것을 기억하자
대부분 기본키의 값은 한번 데이터가 삽입되면 다음에 수정하는 경우가 어의 없다. 그런데 보조 인덱스는 갱신이 자주 일어날 수 있다. 인덱스를 갱신하는 비용이 비싸다는 것을 기억하자. 인덱스가 늘어나면 늘어날수록 각종 갱신에 드는 오버해드는 증가하게 된다. 인덱스는 편리하지만 아무렇게나 추가해서는 안된다.
인덱스가 구현되있는지 RDB 제품에 따라 다르기 때문에 해당 제품을 잘숙지해야한다. 아래 있는 인덱스도 모든 RDB에서 지원하는 것은아니다.
해시 테이블을 이용한 인덱스다. 해시값을 사용하므로 범위 검색에는 사용할 수 없다. 따라서 해시 인덱스를 사용할 수 있다는 것은 등가 비교에 의한 검색 뿐이지만 검색 속도가 굉장히 빠르다는 특성이 있다.
해시 인덱스는 범위 검색을 할수 없어서 B+트리 인덱스와 같이 키의 왼쪽 끝을 지정해서 사용하는 방법은 쓸수 없다는 점에 주의하자.
B+트리 인덱스는 후방 일치나 중간 일치 등을 잘 처리 못한다 이러한 요구 때문에 전문 검색(풀 텍스트) 인덱스이다.
일반적으로 WHERE year(date_col) = 2013과 같은 칼럼 값에 함수를 사용한 WHERE 절은 B+트리 인덱스를 사용한 검색을 할 수 없다. 함수를 적용한 쿼리에도 인덱스를 사용하고 싶을 때 편리한 것이 함수 인덱스이다.
정규화되지 않은 테이블은 칼럼이 매우 많다. 그 결과 필요한 인덱스도 늘어나게 된다. 최악의 경우에는 한 개의 테이블에 수십 개의 보조 인덱스가 있는 경우도 생긴다. 또한 정규호된 테이블에는 적어도 한 개의 후보키가 존재하므로 그 테이블에는 명시적으로 기본키를 작성 할 수 있다.
또 한가지 정규화하지 않았을 때 문제가 되는 것이 NULL의 존재다. 1NF 되어 있지 않은 테이블에는 NULL이 나올 가능성이 있다. NULL은 관계형 모델에게는 천적일 뿐만 아니라 인덱스와도 궁합이 좋지 않다. SQL에서 NULL 끼리의 비교는 할수 없고(NULL = NULL은 TRUE가 되지 않는다.) 인덱스의 구현에도 NULL과 같은 값을 가진 항목은 인덱스의 맨 앞 또는 맨 뒤에서 모아서 놓이게 된다.
인덱스의 검색을 빠르게 하려면 검색조건에 적합한 인덱스가 필요하다. 조건에 맞는 인덱스가 없으면 테이블을 스캔해서 검색하는 방법밖에 없다. 테이블의 크기가 크면 스캔에 드는 비용도 커진다. 인덱스가 늘어 날 수록 테이블을 갱시할 때의 오버헤드도 커지고 필요한 디스크 공간도 늘어난다. 데이터 사이즈가 증가해 버퍼풀의 히트율도 낮아질 것이다. 검색에 필요한 칼럼이 포함된 인덱스 조합 중에 가장 효율이 높은 것을 찾는 것이 중요하다.
인덱스가 필요한 이유는 테이블 스캔을 피하기 위해서지만 테이블 스캔이 실행 계획상 반드시 나쁜 선택이라는 할 수 없다. 다음과 같은 경우에는 테이블 스캔을 하는 편이이 좋다.
- 어떤 인덱스를 사용한 쿼리의 실행 빈도가 매우 낮을때 (1일 1회 등)
- 테이블 사이즈가 매우 작을 때 (100행)
- 검색 결과가 매우 많은 행을 가져올 때
WHERE 절에 칼럼이 등호나 부등호로 비교하고 있으면 인덱스로 발라질 가능성이 있다. 같은 테이블의 여러 개의 칼럼이 WHERE 절로 지정돼 있다면 해당 칼럼에 대해서 다중 칼럼 인덱스를 사용하면 가장 좋은 성능을 얻을 가능성이 크다.
JOIN에서 중요한 것은 결합하는 테이블의 순서다. 내부 테이블에 대한 엑세스는 인덱스가 사용된다. 이때 중요한것은 JOIN의 결합 조건 이외의 검색 이다.
B+트리는 키의 순서로 정렬되어 저장돼 있음으로 인덱스의 순서로 항목을 읽으면 정렬의 고속화에 도움이된다.
- B+트리 인덱스의 기능
- B+트리 이외의 인덱스 종류
- 인덱스 설계에 앞서 제대로 정규화를 실시
- SQL의 어떤 부분에 인덱스가 필요한지?
- 칼럼의 정렬 순서만 다른 인덱스가 있어야 하는지 ?
- 최대공약수의 카디널리티가 높은 칼럼만 포함한 인덱스를 작성
- 필요한 인덱스의 조합을 늘어놓는 방법
트랜잭션은 여러 개의 작업이 이뤄지는 경우가 대부분이지만 트랜잭션에 포함된 모든 작업이 성공 or 실패 하는 성질을 원자성아라고 한다. 실패할 경우 SQL에서 롤백이라고 하고 트랜잭션 이론에서 Abort라고 한다.
응용프로그램에서 트랜잭션 실행후에 두 개의 상태만 고려하면 된다. 트래낵션은 반드시 성공한다고 보장하는 것이 아니라 실패하면 취소된다는 것을 보장하는 것에 지나지 않는다. 바꾸어 말하면 원자성은 트랜잭션이 실패했을 때 롤백 할 수 있다는 것이다.
ACID에 있어 일관성이란 트랜잭션을 실시한 전후에는 데이터의 일관성이 손상되지 않아야 한다는 성질이다.
RDB에서 트랜잭션 실행 전의 DB의 상태는 일관성이 있는 것이며, 트랜잭션 실행 후에 데이터의 변경은 있지만, 일관성이 유지되고 있다. 바꾸어 말하면 트랜잭션 실행하면 DB는 일관성이 있는 상태에서 다른 일관성이 있는 상태로 전환된다고 할 수 있다.
격리성(독립성)이란 동시에 실행하는 여러 개의 트랜잭션이 서로 영향을 주지 않은 성질이다. 바꾸어 말하면 개별 트랜잭션의 실행 결가는 트랜잭션을 직렬로 실행했을 때와 결과 같다야 하는다는 것이다.
영속성이란 일단 커밋이 완료된 트랜잭션이 손상되지 않은 성질이다. 확정된 트랜잭션을 취소할 수 없게 로직을구현하는 것은 물론이고, 시스템이 크래시 됐을 때 재기동 한다음에 복구하여 데이터를크래시 전의 상태로 복원할 수 있다는 점이 중요허다.
크래시 복구 후에는 크래시 전에 커밋한 데이터만 남게된다. 커밋이 완료된 시점에는 DB의 일관성이 보장돼 있으므로 크래시 복구가 완료되면 일관성에 대해 걱정할 필요가 없다.
어떤 트랜잭션의 실행 결과가 다른 트랜잭션의 실행 결과에 영향을 주면 트랜잭션이 읽은 데이터의 정합성이 보장되지 않는다. 이를 모순적 판독 이라고 한다.
더티 리드란 아직 커밋되지 않은 데이터를 읽음으로써 발생하는 비정상는 비정상 상태다. 만일 어떤 트랜잭션1이 갱신된 다음에 아직 커밋되지 않은 데이터를 다른 트랜잭션2이 읽는 경우 트랜잭션1이 어떤 이유로 Abort(Rollback)해 버리면 트랜잭셔2가 읽은 디어는 올바르지 않은 데이터가 된다.
논 리피터블 리드란 한 개의 티랜잭션에 같은 데이터 항목을 여려번 읽을 때 트랜잭션이 쓰기를 하지 않은데도 값을 바꾸어 버리는 현상이다.
팬텀 리드란 SQL과 같은 범위 검색이 있는 시스템에서 줄 일어나는 문제다. 데이터 항목 값에 이상을 이르키는 것이 아니라 과거에 없었던 데이터가 나오는 것이다. 트랜잭션1은 원래 1행만 읽어야 하지만 다른 트랜잭션2에 의해 새로운 행이 삽입된 다음에는 그 행도 읽어 버리게 된다.
앞서 언급한 비정상 상태는 모두 동시성 문제다. 각 처리를 순서대로 수행하면 문제가 발생하지 않는다. 스케쥴이라는 관점에서 보면 앞에서 언급한 비정상 상태가 발생하는 스케쥴은 원래 실행하면 안되는 것이다. 따라서 스케쥴이 발생하지 않도록 트랜잭션의 스케쥴을 결정해야 한다.
쿼리의 실행에 따라 트랜잭션 내에 데이터의 일관성이 무너지지 않도록 작업 대상이 되는행에 대해 그 작업이 수행되기 전에 잠금을 거는 방법이 사용되고 있다.
트랜잭션이 필요로 하는 데이터 항목을 순서대로 잠그는는 아키텍처는 교착 상태라는 문제가 있다. RDB는 행 수준의 잠금 혹은 페이지 수준의 잠금으로 구현돼 이으며 이러한 현상이 이 일어난다.
교착 상태가 발생했다는 것은 만약에 잠금을 하지 않고 그대로 처리했다면 데이터의 부정합이 일어난다는 의미다. 이런 스케쥴은 허용할 수 없으므로 교착 상태에 빠진 트랜잭션은 롤백 해야한다.
| 격리수준 | 터디리드 | 모순 판독 | 갱신 분실 | 팬텀리드 |
|---|---|---|---|---|
| READE-UNCOMMITTED | O | O | O | O |
| READE-COMMITTED | X | O | O | O |
| REPEATABLE-READ | X | X | O | O |
| SERIALIZABLE | X | X | X | X |
SERIALIZABLE가 가장 격리성이 높고 트랜잭션을 실행한 결과가 직렬화된 스케쥴과 같은 결과를 보장한다. 하지만 격리 수준이 높을 수록 성능에 대해서 낮은 경향이 있기 때문에 신중하게 선택해야한다.
1NF의 테이블애 NULL이 포함돼 있어서는 안된다. NULL이 포함되지 않게 표현하려면 NOT NULL 제약을 사용한다. 정규화를 할 때는 반드시 사용하자
테이블에 어떤 칼럼의 조합에 중복이 없음을 보장하려면 고유성 제약을 사용한다. 후보키가 될 수 있는 칼럼의 조합 모두에 기본 키 또는 유니크 인덱스를 작성하자
SQL에 있는 테이블 사이의 데이터 장합성을 확인하는 유일한 수단이 외부키다. 외부키 제약을 사용하면 자식 테이블에 있는 키와 같은 값의 키가 부모가 테이블에 있다는 제약을 표현할 수 있다.