미녀비

안녕하세요!

오늘은 데이터베이스의 검색 방법중 JOIN에 대해서 자세히 알아보겠습니다.

조인

조인은 데이터베이스에서 여러 테이블에 저장된 데이터를 연결하거나 결합해서 조회할 때 사용하는 SQL의 기능 입니다.

각 테이블의 특정 관계 (PK, 외래 키)를 통해 연결하여 데이터를 합칠 수 있습니다.

조인의 종류

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

INNER JOIN

SELECT A.id, B.name FROM 기준테이블 A
INNER JOIN 조인테이블 B
ON A.id = B.id;

두 비교 대상 A,B 테이블에서 지정한 ON 구문의 필드값이 공통으로 중복된 값, 즉 교집합에 해당하는 값을 가져옵니다. 두 테이블 간에 매칭되지 않은 행은 검색 결과에서 제외됩니다.

가장 기본적인 조인으로서 INNER JOIN 대신 JOIN만 작성해도 INNER JOIN이 적용됩니다.

LEFT OUTER JOIN

SELECT A.id, B.name FROM 기준테이블 A
LEFT OUTER JOIN 조인테이블 B
ON A.id = B.id;

기준테이블 A의 모든 데이터의 값과 연결로 지정된 필드가 공통으로 중복된 값에 해당하는 결과값을 보여줍니다.

이때 기준테이블의 모든 행을 반환하고 조인 테이블에 매칭되지 않는 값들은 NULL로 채워집니다.

LEFT JOIN으로 구문을 작성해도 LEFT OUTER JOIN이 적용됩니다.

위 처럼 기준테이블에만 존재하는 값을 가져오고자 할때는 조건문을 통해 구현이 가능합니다.

SELECT A.id FROM 기준테이블 A
LEFT OUTER JOIN 조인테이블 B
ON A.id = B.id
WHERE B.id IS NULL;

이렇게 조건을 넣어주면 기준테이블과 조인테이블이 매칭되지 않은 기준테이블 A 의 값들에 대해서만 조회가 가능합니다.

RIGHT OUTER JOIN

LEFT OUTER JOIN과 내용은 동일하기 때문에 넘어가지만

RIGHT JOIN은 기준테이블의 값을 가져오는게 아닌 조인테이블의 값을 베이스로 가져오게 됩니다.

FULL OUTER JOIN

SELECT A.id, B.name FROM 기준테이블 A
FULL OUTER JOIN 조인테이블 B
ON A.id = B.id;

두 테이블의 모든 데이터를 결과값으로 가져옵니다.

다만 상호 매칭되지 않은 값에 대해서는 NULL로 채워집니다.

FULL JOIN으로 구문을 실행해도 FULL OUTER JOIN으로 적용됩니다.

CROSS JOIN

SELECT A.id, B.name FROM 기준테이블 A
CROSS JOIN 조인테이블 B

크로스 조인은 A,B 두 집합의 각요소를 조합하여 모든 가능한 쌍을 만드는 카테시안 곱으로도 불리는 조인입니다.

조인하려는 두 테이블의 모든 경우의수를 생성하도록 동작하여 N x M개의 결과값을 반환합니다.

SELF JOIN

SELF JOIN은 말 그대로 자기 자신 테이블과 조인하는 것인데요 같은 테이블의 행들간의 관계를 나타내는데 주로 사용합니다.

위와 같은 형태의 테이블이 있을 때 사원들의 관리자는 id 코드로 관리되어 있어 명확히 찾기가 어렵습니다.

SELECT e.name AS name, m.name AS manager
FROM employee e
LEFT JOIN employee m
ON e.manager_id = m.id;

SELF JOIN은 다른 키워드는 없이 JOIN문을 자신의 테이블에 거는것을 이야기 하는데요 위 처럼 SQL을 통해 사원별 관리자 이름을 확인하고자 했고 LEFT JOIN을 통해 관리자가 없는 직원의 경우는 null 값으로 표기하도록 했습니다.

[SQL결과]

이렇게 셀프 조인을 통해 같은 테이블 간에 정보를 확실하게 확인할 수 있겠네요

USING VS ON

두 테이블을 조인할 때 조인 조건 지정방법으로 USING과 ON이 사용 가능합니다.

USING절은 비교하려는 테이블에서 열의 이름이 동일할 경우에만 사용이 가능하며 결과집합에서 중복이 제거되어 하나의 컬럼으로 나타납니다.

반면 ON의 경우 두 테이블의 열 이름이 달라도 사용이 가능하며 명시적으로 열을 선택하여 결과 값 출력이 가능합니다. 또한 연산 조건문등의 사용이 가능합니다.

안녕하세요!

오늘은 데이터베이스의 정규화 관련해서 자세히 알아보겠습니다.

SQL자격증, 정보처리기사, 컴퓨터 활용능력 3개의 자격을 취득하면서 DB 부분에서 항상 저를 괴롭히고 작성하는 현재까지도 잘 이해하지 못하고 있는 정규화 입니다. 시작하겠습니다!

정규화(Normalization)

정규화는 데이터베이스를 체계적이고 효율적으로 관리하기 위해서 사용되는 과정인데요 정규화를 실행함으로써 데이터베이스는 데이터 중복을 줄이고 데이터의 무결성과 일관성을 확보하게 됩니다.

정규화는 6개의 단계로 나뉘고 단계별로 데이터 구조를 개선하는데 초점이 있습니다.

도부이결다조

아주 익숙하죠 ㅎㅎ 저만 그런지는 모르겠지만 공부할땐 시간에  쫓겨 이해를 포기하고 암기를 하게됩니다..

저는 정규형을 볼 때마다 초면인 느낌이였지만 그래도 이거 하나는 외웠습니다. '도부이결다조' 6단계별 수행되는 키워드를 앞글자만 따서 만들었는데요 하나씩 봐보겠습니다.

도 : 도메인이 원자값을 갖는다.

부 : 부분 함수 종속 제거

이 : 이행함수종속 제거

결 : 결정자가 후보키 아닌 것 분해

다 : 다치종속 제거

조 : 조인종속 제거

자 각 단계별 정규형인데요 다시봐도 정말 말이 어렵습니다. 도메인이 원자값..? 이행함수종속 제거?? 정말 신선한 언어들이에요 이제 이 정규형들을 하나씩 봐보겠습니다.

제 1정규형 : 한 셀에는 한개의 데이터만

자 왼쪽의 학생 테이블을 먼저 보겠습니다. 학생의 이름과 수강하고 있는 과목이 보이는데요 구조상 보이는 장점으로는

우선은 학생별로 1개의 row만 사용하기때문에 저장공간을 많이 차지하지는 않겠네요 그리고 한 학생의 데이터만 조회할 경우에는 쉽게 데이터를 볼 수 있겠습니다.

하지만 '수학을 수강하는 학생을 찾아주세요' 나 '홍길동 학생의 수강과목에서 수학을 빼주세요' 라는 요청이 있을 때
어떻게 해야할까요? Select를 사용한 조건이나 과목 수정이 꽤나 까다로워 보입니다. 이때 사용하는것이 제1 정규형 입니다.

제 1정규형은 '한 컬럼에는 한 개의 값'을 넣자 입니다. 1정규화를 적용한 오른쪽 테이블을 확인해보면 보다 테이블의 구조가 단순해졌고, 데이터를 검색하거나 정렬하는 작업을 보다 쉽게 할 수 있겠네요

제 2정규형 : PK의 일부와만 관련있는 정보는 분리하자

제2 정규형은 우선 제1 정규형을 만족하는 테이블에서 시작합니다. 또한 우리는 부분 함수 종속 제거 라는 이름으로 알고 있습니다. 

왼쪽의 학생테이블에서 기본키는 어떻게 사용할 수 있을까요? 학생 ID와 이름은 중복됐으니 (학생ID + 과목)의 복합키로 

구성될텐데 이 때 강사이름은 핵심 주제인 학생과는 전혀 관련이 없으며 복합키 중 하나의 부분집합인 '과목'에 의해 결정되는 것이죠 이 경우를 부분함수종속을 제거하여 완전 함수 종속을 만족하는 제2 정규화 라고 합니다.

제2 정규형은 '주제와 상관없는 내용은 분리하자' 입니다. 2정규화를 적용한 오른쪽 테이블을 보면 데이터구조가 훨신 명확해진것을 확인할 수 있습니다.

하지만 단점으로는 어쨌든 관리해야할 테이블이 더 늘어났고 이제 학생 테이블 만으로는 강사를 알수 없으니 join문을 통해학생별 강사를 확인할 수 있게 되어 데이터 조회 성능이 저하될 수 있겠습니다.

제 3정규형 : 정말 필요없는 내용 없애기

제 3정규형은 2정규형을 만족한 테이블에서 이행 함수 종속을 제거한다고 알고 있는데요 

위 테이블을 예시로 성별 테이블은 우선 과목 테이블의 PK인 과목명에 의해 결정되지 않으며 기본키가 아닌 속성 (=강사)에 의존하고 있는것을 볼 수 있습니다. 

즉 일반 컬럼이 PK가 아닌 일반 컬럼에 종속되어있는 '이행 종속'을 제거하는 것입니다.

제3 정규형은 '정말 필요없는 내용은 분리하자' 입니다. 과목 테이블의 성별은 과목 테이블과는 관련도 없지만 강사 컬럼에 의존하고 있으니 이는 분리해서 더 명확한 구조로 관리하는것이 핵심 입니다.

이것도 2정규형과 단점이 비슷한데요 성별에 대한 정보는 과목 강사별 성별 정보는 join문을 통해 확인해야하니 데이터 조회 성능이 저하될 수 있습니다.

BCNF ~ 제5 정규화

사실 이 BCNF 이상의 정규형부터는 실행되는 경우가 별로 없습니다. 대부분 3정규형 까지 실행하면 충분히 구조를 세우고 문제를 해결할 수 있고 과한 정규화는 오히려 성능적 문제를 야기하기 때문에 성능 최적화가 우선되는 실무에서는 잘 사용하지 않습니다.

그래도 간단하게 설명해보겠습니다

BCNF 정규화 : 후보 키가 아닌 값이 결정자는 없애자

 => 후보키에 해당하지 않는 컬럼이 테이블의 컬럼 값을 결정하고 있을 경우 따로 분리한다.

제 4정규형 : 같은 키로 여러 값이 묶일경우 분리하자

 => 하나의 키에 여러 독립적인 값이 엮여있을 경우 각각의 테이블로 분리하자

제 5정규형 : 조인하지 않아도 되도록 데이터를 분리하자

 => 조인을 통해서 데이터를  확인할 수 있는 A,B,C 데이터 가 있을 때 AB, BC, AC 형태로 테이블을 분리시켜 데이터를 확인할 수 있다.

오늘은 데이터베이스의 정규화에 대해 알아봤습니다.

감사합니다!

안녕하세요 오늘은 데이터베이스의 Index와 관련해서 자세히 알아보는 시간을 갖게습니다.

먼저 인덱스라는 단어를 들으면 어렴풋이 알기로는 책에 첫장에 있는 목차 정도로 알고있습니다. 

우리는 책을 읽을때 목차를 통해 특정 내용의 페이지 수를 확인할 수 있죠.

DB에서의 인덱스도 크게 다르지 않습니다. 데이터를 빠르게 검색하고 조회 성능을 향상시키기 위한 자료구조 입니다.

우리가 프로그래밍을 하면서 Stack, Queue와 같은 자료구조를 이용해서 데이터를 효율적으로 사용하는 것처럼 DB에서도Index를 통해 내부 데이터를 효율적으로 관리할 수 있습니다.

FULL TABLE SCAN

위와 같은 테이블이 있고 "나이가 10살인 사람을 찾아줘~" 라고 했을때 

SELECT name FROM 테이블 WHERE age = 10

우리는 위와 같은 SQL문을 통해 데이터를 요청하게 됩니다. 이 때 풀 테이블 스캔은 테이블 전체 모든 데이터를 스캔하게 됩니다. 이 경우 최악의 상황이라면 5개의 데이터를 모두 검사한 후에야 원하는 데이터를 찾아오겠네요. 데이터의 개수가 많아질 수록 검색하는 시간이 많아지겠죠.

이렇게 테이블에 별도의 인덱스를 만들어주지 않는다면 기본적으로 풀 테이블 스캔 방식으로 작동하게 됩니다.

그렇다면 모든 테이블은 그냥 인덱스를 지정해서 빠르게 빠르게 검색되면 좋은거 아니야? 라고 할 수 있지만 이 방식의 이점도 분명 존재합니다. 위 예시처럼 데이터가 많지 않을경우 오히려 더 효율적으로 작동할 수 있고, age > 0 처럼 조건이 대부분 참일 때 굳이 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하는게 성능적으로 더 빠를 수 있습니다.

INDEX SCAN

방대한 양의 데이터가 있을 때 풀 테이블 스캔은 효율이 떨어진다고 했습니다. 그래서 우리는 index를 통해 정보를 빠르게 검색하려고 합니다. 검색조건인 age 컬럼에 인덱스를 지정하게 되면 데이터베이스는 age컬럼을 복사하여 별도의 인덱스스토리지 영역에 저장하게 되고 이 때 대부분 컬럼의 값이 정렬된 상태로 저장 됩니다. index 영역에서 전재준의 나이인 10은 정렬되어 첫 번째 순서에 있겠네요!

이제 개발자가 SQL을 전송하면 DB는 이 인덱스를 참조하여 값을 가져오게 되고 이를 인덱스 스캔이라고 하는것이죠

DB는 인덱스를 탐색하여 원하는 데이터 컬럼을 찾고 여기에 저장되어있는 물리주소를 통해 데이터에 접근하는 방식입니다. 

인덱스 스캔은 전체 테이블을 탐색하지 않아 검색 속도가 향상되고 불필요한 데이터를 같이 읽지 않아도 되기때문에 I/O 성능도 더 뛰어납니다. 

인덱스의 단점은 위에서 말했듯 별도의 스토리지 영역에 데이터 사본을 저장하는것이기에 자원을 소모한다는 단점이 발생하고 기본적으로 정렬된 데이터를 관리하기 위해서 테이블에 삽입/삭제/수정 작업이 일어났을때  인덱스영역도 그에맞춰 데이터 작업이 이루어져야 하니 해당 작업이 빈번할 경우 오히려 성능이 떨어지는 상황이 발생할 수도 있습니다.

INDEX 동작방식

자 그럼 인덱스가 내부적으로 어떻게 관리되고 동작하는지 확인해 보겠습니다.

B Tree 방식

먼저 가장 흔하게 사용되는 B Tree 방식을 확인해 보겠습니다. 인덱스는 이렇게 트리 형태로 자료를 가지고 있는데 우리가 위에서 찾으려던 age = 10 을 찾으려면 루트 노드인 20에서 2번의 검색으로 찾으려는 데이터인 10에 접근하고 내부에 저장된 물리주소를 통해 우리가 찾으려는 테이블의 컬럼을 보여주게 됩니다.

오른쪽에 보이는 트리는 비슷해보이지만 조금 다릅니다 B-Tree가 변형된 구조로 범위 검색에 강점이 있는 자료구조에요

데이터는 마지막인 리프 노드들에만 저장이되고 내부 노드나 루트 노드는 리프노드를 가르키는 포인터만 제공합니다.

또 리프노드들 끼리는 연결이된 모습을 확인할 수 있습니다.

B+Tree가 가지는 강점은 범위검색에서 나옵니다. 왼쪽에 B-Tree를 이용했을때 나이가 17~25인 데이터를 찾으려 하면 트리를 다시 역으로 올라가야하는 경우가 발생하지만 B+Tree의 경우 리프노드들 간에 연결이 돼있어 범위 검색에서 보다 쉽게 접근이 가능한 것이죠!

HASH 방식

해쉬 방식은 위의 트리 방식과 다르게 데이터 정렬을 사용하지 않습니다. 내부적으로 키-값을 가진 해쉬테이블을 기반으로 작동합니다. 인덱스는 컬럼 키 값을 받아 해쉬함수를 통해 새로운 값을 생성해내고 이 값을 저장하여 특정 데이터와 1:1로 매핑되는 구조입니다. 

해쉬방식은 데이터가 1:1로 매핑되기때문에 굉장히 빠른 검색속도를 가지고 있다는것이 장점이지만 인덱스에 정렬작업이 되어있지 않기에 범위 검색과 정렬작업이 필요한 쿼리에는 적합하지 않습니다.

그러므로 정확한 값을 검색하는 Equal Search의 경우에 사용하는것이 좋습니다.

오늘은 인덱스에 대한 내용과 내부 동작방식 또 B Tree, Hash같은 내부 자료구조또한 확인해봤습니다.

감사합니다!