(6) 7장. 복수의 테이블 다루기

(6) 7장. 복수의 테이블 다루기

31~33강 집합 연산 및 결합

---

31강 집합 연산

• UNION; 합집합 A∪B 연산자로 키워드 다음에 DISTINCT 또는 ALL이라는 키워드 추가해서 중복 레코드에 대한 처리 방법 선택 가능 (기본 선택은 DISTINCT), UNION은 두 집합의 합을 만들어 내기 위해 버퍼 역할을 하는 임시 테이블을 사용

• UNION ALL; 두 개의 집합에서 중복된 레코드에 대해 별도의 처리 과정을 거치지 않고 바로 반환 (단순히 임시 테이블만 사용)

• UNION DISTINCT; 두개의 집합에서 중복된 레코드를 제거한 후 (두 집합의 레코드 가운데 중복된 레코드 중 하나는 버림) 합집합을 사용자에게 반환

  • → UNION을 수행해야할 대상은 이미 임시 테이블로 만들어졌으며, 이 임시 테이블에는 중복 체크의 기준이 될 PK가 없다 ∴집합 연산에서 중복 레코드 비교를 위해 임시 테이블의 모든 컬럼을 비교
  • → 집합의 모든 컬럼을 이용해 unique 인덱스를 생성
  • ※ UNION 연산은 대상 레코드 건 수가 많아지거나, 비교해야 하는 컬럼 값의 길이가 길어지면 > 성능↓

• ALL과 DISTINCT의 차이는 단순히 unique 인덱스를 가지냐 아니냐의 차이지만 실제로 이로 인한 성능 차이가 작지 않다

  

  ∴ 두 집합에서 중복된 결과가 있을 수 없다는 것이 보장된다면 UNION ALL 연산자를 이용해 MySQL 서버가 불필요하게 중복 제거 작업을 하지 않고 빨리 처리되게 할 수 있다

• ORDER BY가 사용된 쿼리를 UNION/UNION ALL로 결합하는 경우 각 쿼리를 괄호로 감싸고 그 결과를 UNION/UNION ALL로 처리해주면 됨

• 합집합의 결과를 정렬하고 싶은 경우, 가장 마지막에 order by구를 지정해주면 됨

• 그렇다면 UNION은 언제 사용하는가?

  • DBSHARD 로 분산되어있을 때(full 방지 및 접근성을 위해) 전체 유저 결과를 보고싶을때
  • 위와 같은 경우 통폐합 시 겹치는게 있는지 볼 때
  • 일별, 월별 등.. 로그 모아서 확인할 때

• INTERSECT; 두 개의 집합에서 교집합 A∩B 부분만을 가져오는 쿼리로 사실상 INNER JOIN과 동일하다

  • MySQL에서는 지원하고 있지 않음 select 되는 튜플들의 컬럼을 inner join의 조건으로 포함시켜 작성

    

• EXCEPT (MINUS); 차집합 A-B > 첫번째 결과 집합에서 교집합 부분만 제거하는 결과를 반환 (차집합의 결과가 공집합인지 아닌지에 따라 두 개의 집합이 동일한지 아닌지를 알 수 있음)

  • MySQL에서는 지원하고 있지 않음 ↑ ANTI JOIN

    

  • http://intomysql.blogspot.com/2011/01/mysql-minus-intersect.html

---

32강. 테이블 결합

• JOIN의 종류

  • 조건을 어떻게 명시하느냐에 따라
    • CARTESIAN JOIN (FULL JOIN 또는 CROSS JOIN)
    • NATURAL JOIN
  • INNER JOIN
  • OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
    • FULL OUTER JOIN

• CROSS JOIN(교차결합FULL JOIN); 곱집합(카티전곱) A×B

  

  • → from 구에 복수의 테이블을 지정하면 교차결합을 함

• UNION 연결과 결합 연결의 차이

  • UNION으로 합집합을 구했을 경우에는 세로 방향으로 더해지게 됨

  • FROM구로 테이블을 결합할 경우에는 가로 방향으로 더해지게됨

    

• 레코드 건수가 많아지면 조인의 결과 건수가 기하급수적으로 늘어나므로 MySQL 서버 자체를 응답 불능 상태로 만들어버릴 수도 있음

• 조인되는 테이블이 많아지고 조인 조건이 복잡해질수록 의도하지 않은 카테시간 조인이 발생할 가능성이 크기 때문에 주의해야함

• 레코드 한 건만 조회하는 여러 개의 쿼리(전혀 연관이 없는)를 하나의 쿼리로 모아서 실행하기 위해 사용되기도 함 ↓

  

  → 카테시안 조인으로 묶은 2개의 단위 쿼리가 반환하는 레코드가 항상 1건이 보장되지 않으면 아무런 결과도 못가져오거나 기대했던 것 보다 훨씬 많은 결과를 받게될 수 도 있으므로 주의

• MySQL에서 CROSS JOIN은 INNER JOIN과 같은 조인 방식을 의미 ∴ INNER JOIN처럼 ON절이나 WHERE절에 조인 조건을 부여하는 것이 가능 < 이렇게 작성된 쿼리는 INNER JOIN과 같은 방식으로 동작

• MySQL에서 카테시안 조인과 이너 조인은 문법으로 구분되는 것이 아니기 때문에

  • 조인으로 연결되는 조건이 있다 > 이너 조인으로 처리
  • 연결 조건이 없다 > 카테시안 조인으로 처리
    ∴둘을 특별히 구분해서 사용할 필요 없음

• INNER JOIN과 같은 결과를 가져오지만 조인 조건을 명시하지 않아도 된다는 편리함이 있음

  • → 각 테이블의 컬럼 이름에 의해 쿼리가 자동으로 변경될 수도 있다는 문제가 있음
  • → 같은 컬럼명을 사용할 때 자동으로 조인의 조건으로 사용돼버릴 수 있다는 점을 항상 고려해야 하며, 테이블의 구조를 변경할 때도 NATURAL JOIN으로 조인되는 테이블이 있는지 그 테이블의 컬럼과 비교하면서 같은 컬럼명이 존재하는지 확인이 필요함 > 유지보수 비용 높이는 역효과... NATURAL JOIN 지양하는게 좋음

• 내부결합; 교차결합으로 계산된 곱집합에서 원하는 조합을 검색하는 것

  

  • FROM 구에 테이블을 복수 지정해 가로 방향으로 테이블을 결합할 수 있다
  • 교차결합을 하면 곱집합으로 계산된다
  • WHERE 조건을 지정해 곱집합에서 필요한 조합만 검색할 수 있다

• INNER JOIN

  

• 자가 결합

  

• MySQL에서 조인은 네스티드-루프 방식만 지원

• 네스티드-루프; 일반적으로 프로그램을 작성할 때 두 개의 for나 while 같은 반복 루프 문장을 실행하는 형태로 조인 처리되는 것

  

  • → 아우터 테이블을 먼저 읽어야 하며, 조인에서 주도적인 역할을 함(아우터테이블;driving table // 이너테이블;driven table) → 중첩된 반복 루프에서 최종적으로 선택될 레코드가 이너테이블에 의해 결정되는 경우를 inner join이라고 함 ∴ if문 안에 있는 조건을 만족하는 레코드만 조인의 결과로 가져옴

  • → 드라이빙 테이블에만 존재하는 레코드는 조인 결과에 포함되지 않음

    (ex) 3개의 테이블을 조인하는 경우

    

  • → 제일 먼저 d 테이블이 읽히고, de 테이블 그리고 e 테이블이 읽혔다

  • → de 테이블과 e테이블이 읽힐 때 어떤 값이 비교 조건으로 들어왔는지를 ref 컬럼에 표시하고 있다

  • → 3번 중첩이 되긴 했지만 전체적으로 반복 루프는 1개로, 반복루프를 돌면서 레코드 단위로 모든 조인 대상 테이블을 차례대로 읽는 방식! → 드라이빙 테이블을 읽은 순서대로 레코드가 정렬되어 변환 되는 것

  • → 드리븐 테이블들은 단순히 드라이빙 테이블의 레코드를 읽는 순서대로 lookup(검색)만 할 뿐

  • ∴ 드라이빙 테이블은 한 번에 쭉 읽게 되지만, 드리븐 테이블은 여러번 읽는다 > 드리븐 테이블의 조인 조건이 인덱스를 사용할 수 없다면 계속 풀스캔을 해야함 > 성능저하

• 조인 버퍼를 이용한 조인

  • 만약 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리함
  • 이때 사용되는 메모리의 캐시 → 조인 버퍼(join buffer)
    • join_buffer_size라는 시스템 설정 변수로 크기를 제한할 수 있음
    • 조인이 완료되면 조인 버퍼는 바로 해제 됨
  • where 조건의 비교 컬럼의 양쪽 항 데이터 타입이 일치하지 않는 경우 인덱스를 타지 못하기 때문에 조인버퍼를 사용하여 풀 스캔
    • CHAR 타입과 INT타입의 비교 처럼 데이터 타입의 종류가 완전히 다른 경우
    • 같은 CHAR 타입이더라도 문자셋이나 콜레이션(utf8-general-ci, 이모지 등 뭐시기)이 다른 경우
    • 같은 INT 타입이어도 부호가 있는지 여부가 다른 경우

• OUTER JOIN

  • 교차결합으로 결합 조건을 지정하여 검색한다는 기분적인 사고방식은 같으나 '어느 한쪽에만 존재하는 데이터행을 어떻게 다룰지'를 변경할 수 있는 결합 방법

    

  • inner join에서 else부분이 join_record_found(record1.*, NULL); 로 바뀜
    → 아우터 테이블의 레코드가 이너 테이블에 일치하는 레코드가 없다고 해서 버려지지 않고 null값으로 채워짐

  • 조인의 결과를 결정하는 아우터 테이블이 조인 어디에 위치하고 있는지에 따라 LEFT JOIN 또는 RIGHT JOIN, OUTER JOIN으로 나뉨 (보통 혼용을 줄이기 위해 LEFT OUTER JOIN으로 통일하는 것이 일반적)

  • LEFT OUTER JOIN에서 주의해야하는 부분

    • MySQL의 실행계획은 이너 조인을 사용했는지 아우터 조인을 사용했는지를 알려주지 않으므로 아우터 조인을 의도한 쿼리가 이너조인으로 실행되지는 않는지 주의해야함

    • 아우터 조인에서 레코드가 없을 수도 있는 쪽의 테이블에 대한 조건은 반드시 LEFT JOIN의 ON절에 모두 명시 < 그렇지 않으면 옵티마이저는 아우터 조인을 내부적으로 이너조인으로 변형시켜 처리해버릴 수도 있음

      → ON절에 명시되는 조건은 조인되는 레코드가 있을 대만 적용됨
      → 반면 WHERE절에 명시되는 조건은 아우터조인이든 이너조인이든 관계없이 조인된 결과에 대해 모두 적용되기 때문에
      ※ 아우터 조인으로 연결되는 테이블이 있는 쿼리에서는 가능하면 모든 조건을 ON절에 명시하는 습관을..!

    • 사실 OUTER JOIN과 INNER JOIN은 실제 가져와야 하는 레코드가 같다면 쿼리의 성능은 거의 차이가 없다 ∴테이블의 구조와 데이터의 특성을 분석해 어떤 것을 사용할지 결정해야함
      → 그렇지 않고 OUTER JOIN을 사용한다면 잘못된 결과가 화면에 나타날 수도 있음

• OUTER JOIN과 COUNT(*)

  • 페이징 처리를 위해 조건에 일치하는 레코드의 건수를 가져오는 쿼리에서 자주 함께 사용됨
  • 이때 불필요하게 연결되는 테이블이 자주 있는데 아래 두 조건을 만족하면 조인에서 불필요한 테이블이므로 제거하는 것이 빠른 결과 추출에 도움
    1. 드라이빙 테이블과 드리븐 테이블의 관계가 1:1 또는 M:1인 경우
    2. 드리븐 테이블에 조인 조건 이외의 별도 조건이 없는 경우

• JOIN의 순서와 인덱스

  • 인덱스 레인지 스캔으로 레코드를 읽는 순서
    ① 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다 > index seek(인덱스 탐색)
    ② 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 죽 읽는다 > index scan(인덱스 스캔)
    ③ 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다

    • 인덱스 풀 스캔 or 테이블 풀 스캔; ①이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다
    • 인덱스 레인지 스캔; 가져오는 레코드의 건수가 소량이기 때문에 ② 과정은 부하가 작지만 특정 인덱스 키를 찾는 ① 과정이 상대적 부하가 높은 편

  • 조인 작업에서 드라이빙 테이블을 읽을 때는 ①을 단 한번만 수행하고 이후부터는 ②만 실행하면 됨~!

  • 하지만.. 드리븐 테이블에서는 ①과 ② 작업을 드라이빙 테이블에서 읽은 레코드 건수 만큼 반복.......
    → 드라이빙 테이블과 드리븐 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 큰 부하를 차지......
    ∴ 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다

    

    • e.emp_no칼럼과 de.emp_no칼럼 모두 각각 인덱스가 있는 경우
      • 어느 테이블을 드라이빙으로 선택하든 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있음
      • 각 테이블의 통계 정보에 있는 레코드 건수에 따라 e가 드라이빙 테이블이 될수도, de가 될수도있다
      • 뭐가 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다
    • e.emp_no컬럼에만 인덱스가 있는 경우
      • de 테이블이 드리븐 테이블로 선택된다면 e테이블의레코드 건수 만큼 de테이블을 풀 스캔해야만 where조건에 일치하는 레코드를 찾을수 있으므로
      • 옵티마이저는 항상 de 테이블을 드라이빙 테이블로, e테이블은 드리븐 테이블로 선택
    • de.emp_no컬럼에만 인덱스가 있는 경우
      • 위와 반대로 처리된다
    • 두 컬럼 모두 인덱스가 없는 경우
      • 어느 테이블을 드라이빙으로 선택하든 드리븐 테이블의 풀 스캔은 피할 수 없으므로
      • 옵티마이저가 적절히 드라이빙 테이블을 선택(레코드 건수가 적은 놈을 드리븐 테이블로 선택하는게 효율적)
      • 드리븐 테이블을 읽을 때 조인 버퍼가 사용되기 때문에 extra에 using join buffer가 표시됨

• FULL OUTER JOIN 구현

  • MySQL에서는 FULL OUTER JOIN을 제공하지 않음

  • 두개의 쿼리 결과를 UNION으로 결합하여 사용

    

  • UNION의 내부적인 임시테이블 생성으로 버퍼링 성능이 걱정되는 경우 뮤텍스(Mutex) 테이블을 사용하면 됨

  • 뮤텍스(Mutex) 테이블; copy_t라고도 많이 알려져 있는 단순히 n개 만큼 복제하는 역할을 하는 테이블

• JOIN과 FOREIGN KEY

  • FOREIGN KEY와 조인은 아무런 연관이 없다
  • FOREIGN KEY를 생성하는 주 목적은 데이터의 무결성을 보장하기 위해서..!
  • FOREIGN KEY와 연관된 무결성을 참조 무결성이라고 표현한다
  • SQL로 테이블간의 조인을 수행하는 것은 전혀 무관한 칼럼을 조인 조건으로 사용해도 문법적으로는 문제가 되지 않는다
  • 데이터 모델링을 할 때는 각 테이블 간의 관계는 필수적으로 그려 넣어야하지만 그 데이터 모델을 생성할 때는 그 테이블 간의 관계는 FOREIGN KEY로 생성하지 않을 때가 더 많음

---

33강. 관계형 모델

• 데이터베이스의 기반이되는 이론적 개념으로 사용되는 용어는 SQL과 일치하지 않음

• 관계형 모델의 기본적인 요소는 릴레이션(Relation) == 테이블

  

• 릴레이션은 튜플의 집합이며, 릴레이션에 대한 연산이 집합의 대한 연산에 대응된다는 이론을 '관계대수'라고 함

• 관계대수의 기본 규칙

  • 하나 이상의 관계를 바탕으로 연산한다
  • 연산한 결과, 반환되는 것 또한 관계이다
  • 연산을 중첩 구조로 실행해도 상관없다

• 릴레이션의 연산 방법

  • 합집합(UNION); 릴레이션끼리의 덧셈
  • 차집합(EXCEPT); 릴레이션끼리의 뺄셈
  • 교집합(INTERSECT); 릴레이션끼리의 공통 부분(교집합)
  • 곱집합(CROSS JOIN); 릴레이션끼리의 대전표를 조합하는 연산
  • 선택(WHERE); 튜블(행)의 추출 / 선택의 제한
  • 투영(SELECT); 속성(열)의 추출
  • 결합(JOIN); 교차 결합해 계산된 곱집합에서 결합조건을 만족하는 튜플(행)을 추출하는 연산 - 내부결합과 외부결합 존재

---