Skip to content

4조 SQL JOIN

Jump to bottom
김수현 edited this page Jul 15, 2024 · 1 revision

1. MySQL 설치하기

Step1 - Ubuntu에 Mysql 설치하기

sudo apt update 
sudo apt install mysql-server (혹은 mysql도 가능) 
sudo ufw allow mysql

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf # 원격 접속 허용위해서는 파일 내 bind-address = 127.0.0.1 주석처리
sudo systemctl start mysql

  • 최신 안정화 버전인 mysql 8 설치

  • /usr/bin/mysql → mysql 클라이언트 프로그램 mysql e데이터베이스 서버에 접속.

    • mysql-client패키지의 일부

  • systemctl start mysql 명령어는 mysql 서버 데몬인 mysqld를 시작하는 명령어. mysql 데이터베이스 서버 자체

  • 서버 데몬의 역할( 백그라운드에서 실행되어 특정 서비스를 제공하는 프로그램을 의미)

    • 클라이언트의 역할 처리: 연결 요청을 수락하고 관리. 커넥션 풀을 관리?
    • SQL 쿼리 실행
    • 데이터 관리: 데이터베이스 파일을 관리 데이터베이스 무결성과 일관성 유지
    • 쿼리 캐싱 인덱스 등을 사용해서 데이터베이스 성능 최적화

  • install mysql vs install mysql-server의 차이

    • mysql은 메타패키지 Mysql을 설치. 서버와 클라이언트 모두 포함
    • mysql-server는 서버 패키지를 설치. 종속성으로 인해 클라이언트 도구도 자동으로 설치

    ⇒ 둘다 설치 결과는 같다.

  • mysql에 접속시

    • mysql -h 192.158.1.100 -u -p
    • 호스트 생략시 localhost
    • mysql = /usr/bin/mysql 파일을 실행하는 것(command -v mysql로 확인 가능)

  • 왜 sudo로 root 접속시에는 되는데 ubuntu로 접속시에는 access denied 발생?

    • 기본적으로 root @ localhost 계정이 생성됨.
    • root 계정 인증에는 비밀번호가 아닌 auth_socket plugin을 사용한다. root 사용자가 시스템의 Root사용자로 로그인할때만 mysql에 접속하도록 함.

  • 원격 접속을 허용하려면 설정파일(/etc/mysql/mysql.conf.d/mysql/cnf)에 bind-address를 허용해줘야하는 이유.

    • bind-address: mysql서버가 어떤 Ip주소의 접속 요청을 수락할지를 지정하는 것. 기본값은 localhost로 설정되어 있음.
    • → localhost에서만 연결을 수락 0.0.0.0모든 주소에서 접속 허용

Step2 - AWS에서 포트 열어주기

  1. 방화벽 인바운드 규칙 편집
  2. 3306 ::/0, 0.0.0.0/0 허용
    1. (참고) 원래는 IP 범위를 백엔드 서버 IP로 제약하는게 좋음

2. SQL 조인

2-1. SQL 핵심 요소

MySQL의 Join 동작 원리는 복잡하며 여러 요소가 결합되어 작동합니다. 내부적으로 Join을 수행하는 MySQL의 핵심 요소는 다음과 같습니다.

  1. 최적화기 (Optimizer):
  • MySQL의 최적화기는 SQL 쿼리를 분석하고 최적의 실행 계획을 세웁니다.
  • 조인을 최적화할 때, 최적화기는 어떤 테이블이 드라이빙 테이블(Join에서 가장 먼저 읽혀지는 테이블)이 될지 결정하고, 조인의 종류(중첩 루프, 병합, 해시 등)를 결정합니다.
  1. 실행 엔진 (Execution Engine):
  • 최적화기에 의해 생성된 실행 계획을 실행하는 부분입니다. 이 부분에서 실제로 조인 연산이 수행됩니다.
  1. 스토리지 엔진 (Storage Engine):
  • MySQL은 다양한 스토리지 엔진을 지원합니다.
  • InnoDB, MyISAM 등의 엔진이 있으며, 각 엔진이 데이터를 어떻게 저장하고 읽는지에 영향을 미칩니다.

2-2. SQL 조인 기법

Nested Loop Join

  1. Nested Loop Join 두 테이블의 모든 가능한 쌍을 비교하여 조건에 맞는 쌍을 찾아내는 방식
  • 외부 루프: 드라이빙 테이블(외부 테이블)의 각 행을 읽습니다.
  • 내부 루프: 드리븐 테이블(내부 테이블)의 각 행과 외부 루프에서 선택된 행을 비교합니다.
  • 조건 검증: 조인 조건을 검증하고 조건을 만족하면 결과 집합에 추가합니다.

장점

  • 간단함: 구현이 쉽고 이해하기 간단합니다.

  • 유연함: 어떤 조인 조건도 처리할 수 있습니다.

    단점

  • 비효율적임: 두 테이블의 크기가 커질수록 성능이 급격히 저하됩니다.

  • 인덱스 의존: 인덱스가 없으면 매우 비효율적입니다.

  • 반복문처럼 조인함

선행 테이블의 한 행을 스캔해서

후행 테이블에 조인 키가 존재하는지 스캔하는 방식

선행 테이블이란?

먼저 읽는 테이블, SQL에서 순서는 아니고, 옵티마이저가 설정함

선행 테이블이 Full Table Scan을 사용하는지, 인덱스를 사용하는지 여부는 옵티마이저에 의해 결정됨

-- 예시 테이블
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    address VARCHAR(255)
);

-- 인덱스 생성
CREATE INDEX idx_customer_id ON orders(customer_id);

-- JOIN 쿼리
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date = '2024-07-15';

만약 Join SQL 쿼리에서 order의 순서가 먼저 나왔다고 order를 선행 테이블로 선택하는게 아니라

옵티마이저가

  • 테이블 크기
  • 인덱스 유무 및 인덱스의 선택도 (Selectivity)
  • 조건절 (WHERE 조건)
  • 테이블 통계 정보

위의 조건을 알아서 판단하여 order로 결정하게 되는거임

Hash Join

장점

  • 효율적: 대용량 데이터에 대해 빠른 성능을 제공합니다.
  • 순차 읽기: 대개 디스크에서 순차적으로 데이터를 읽으므로 성능이 향상됩니다.

단점

  • 메모리 사용: 해시 테이블을 메모리에 저장하므로 메모리 사용량이 많아질 수 있습니다.
  • 균등 분포 필요: 데이터가 균등하게 분포되어 있지 않으면 성능이 저하될 수 있습니다.

Sort Merge Join 그룹 미션 SQL JOIN

2-3.조인시 성능 최적화 방법

1. ACCESS 하는 속도를 향상

  • 테이블을 Access 할 때 FULL TABLE SCAN이냐 INDEX RANGE SCAN이냐 하는 등 테이블을 Access 하는 방법을 다양한 방법을 통해 최적화시킨다면 SORT MERGE JOIN의 속도도 자연스럽게 최적화할 수 있다.

2. 정렬 속도의 향상

  • SORT MERGE JOIN은 양쪽 테이블에서 조회한 데이터들을 정렬시켜야 한다. 이때 조인 조건 컬럼이 이미 정렬되어 있다면 정렬을 하는 작업을 단축시켜 검색 속도 향상에 도움이 될 것이다.

3. 양쪽의 정렬 완료 시점 맞추기

  • SORT MERGE JOIN은 양쪽 테이블을 ACCESS하고 조회한 데이터들을 정렬할때 어느 한쪽이라도 정렬 작업이 종료되지 않으면 한쪽이 대기 상태가 되고 다른 한쪽의 정렬이 완전히 끝날 때까지 조인이 시작될 수 없다. 그렇기에 두 테이블 ACCESS속도와 정렬 속도를 최대한 비슷하게 맞추어주는 것이 좋다.

4. SORT_AREA_SIZE 최적화

  • SORT MERGE JOIN은 두 테이블 간의 비교가 이루어지기 전에 수행하는 정렬 작업을 위해 별도의 정렬 공간이 필요하며 이 공간은 SORT_AREA_SIZE 크기만큼 메모리를 할당받아 사용하게 되고, 메모리가 부족하다면 Temporary Table Space를 이용하여 정렬을 수행하게 된다. 이때 Temporary Table Space를 사용하면 딜레이가 생기므로 SORT_AREA_SIZE를 적당한 크기로 설정해두는 것이 속도 향상에 도움이 된다.

5. 조인 순서 최적화

  • 조인 되어야할 각 집합의 처리 범위와 순서에 따라 영향을 받는다. 즉, 처리 범위가 가장 좁은 범위를 먼저 처리할 수록 조인의 효율은 증가한다.
  • 드라이빙 테이블. 작은 테이블을 먼저 조회하고 이를 기반으로 큰 테이블과 조인.
    • 드라이빙 테이블: 조인 쿼리 실행시 첫번째로 접근되는 테이블
    • 작은 테이블을 드라이빙 테이블로 선택 → 나머지 조인 과정에서 비교해야할 데이터 양을 줄일 수 있음
    • 옵티마이저가 일반적으로 조인 순서를 자동으로 선택: 테이블의 크기가 작은 것, 조인 컬럼에 인덱스가 있는 테이블, 필터 조건이 많이 적용된 테이블

그룹 미션 SQL JOIN png)

첫 번째 그림( TABLE1 → TABLE2 → TABLE3)의 경우

1) Table1의 처리 범위인 10000 row의 첫번째 row를 읽는다.

- 2)읽혀진 TABLE1의 값에 대응하는 TABLE2의 로우를 찾는다
- 3)TABLE2의 조인 컬럼값과 대응되는 TABLE3의 로우를 찾는다.
- 4)TABLE1의 처리범위가 모두 끝날 때까지 계속한다. => 최소 10,000회 이상ACCESS

2) TABLE3 -> TABLE2 -> TABLE1

- 1)TABLE3의 처리 범위인 2개 로우의 첫번째 로우를 읽는다.
- 2)읽혀진 TABLE3의 값에 대응하는 TABLE2의 로우를 찾는다
- 3)TABLE2의 조인 컬럼값과 대응되는 TABLE1의 로우를 찾는다.
- 4)TABLE3의 두번째 로우를 읽어 위의 작업을 반복한다. => 최대 6회 이하 ACCESS

Nested Loop 조인에서는 가장 먼저 수행되는 집합의 처리범위가 전체 일량을 좌우한다.

따라서 가장 적은 처리 범위를 가진 테이블이 먼저 수행되도록하면 최적의 조인을 구현할 수 있다. 3. 조인 순서 최적화: 4. 조인에 사용되는 컬럼에 인덱스를 생성하면 쿼리 성능이 크게 향상됨 5 . 조인 조건을 where 절에 포함시키면 옵티마이저가 쿼리를 더 효율적으로 처리할 수 있따.

//BAD
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.status = 'active';
-- 모든 조인된 결과를 가져온 후 where 절에서 필터링을 수행함.

//GOOD
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id AND customers.status = 'active';
-- 고객 상태가 active인 경우에만 조인을 수행하므로 조인 전에 필터링이 이루어짐
-- status 컬럼에 인덱스가 있다면 이를 활용해 더 빠르게 데이터 검색 가ㅡㄴㅇ.

6. InnoDB BufferPool사이즈 조절.
데이터와 인덱스를 메모리에 캐싱해서 디스크 I/O를 최소화. 기본 값 128MB. 시스템 메모리의 50 ~ 80%를 버퍼 풀 사이즈로 할당하는 것이 권장됨.

[참고] 조인 조건에 필터 포함보다 조인 후 필터링이 더 효율적인 경우? 조인 조건에 포함된 컬럼에 인덱스가 없는 경우, 필터 조건이 조인 조건과 독립적일때, 필터 조건이 데이터를 크게 줄이지 않는 경우

[참고] STRAIGHT_JOIN 힌트를 사용하면 쿼리에 명시된 순서대로 조인을 강제할 수 있습니다.

👼 개인 활동을 기록합시다.

개인 활동 페이지

🧑‍🧑‍🧒‍🧒 그룹 활동을 기록합시다.

그룹 활동 페이지

🎤 미니 세미나

미니 세미나

🤔 기술 블로그 활동

기술 블로그 활동

📚 도서를 추천해주세요

추천 도서 목록

🎸 기타

기타 유용한 학습 링크

Clone this wiki locally